スキマノート

AIメーカーなどのwebサービスを作っています。ここでは気になった技術、話題などをまとめたり、雑記を書いたりします。

話題のツイートを探せる「ツイレポ」に用いられている技術について（MySQL -> BigQueryに移行した）

webサービス開発 ツイレポ

この記事は個人開発サービスに用いられている技術 Advent Calendar 2018 の19日目です。

昨日は@isaitoiさんの「Microsoftの技術でもちゃんとしたサービスを作れるんだぞ！！」でした。

こんにちは、2z（Twitter： @2zn01 ）です。

普段は会社員でWeb系の開発エンジニアとして働き、週末に趣味で個人開発をしています。

僕自身、普段あまり時間がない中でどう情報収集したらよいか困っているということもあり、エンジニアらしく技術で何とか解決するべく、ツイッターを活用し、エンジニアの情報収集を助けるキュレーションサービスを作ってみました。

作ったもの

ツイッターで話題になっている情報をジャンルごとにまとめる「ツイレポ」というサービスを作りました。

■ツイレポ
https://twirepo.com/

f:id:no2z1:20181219074909p:plain

画面イメージはこんな感じです。

twirepo_screenshot1.png

「ツイレポ」では以下のジャンルごとに話題となっているツイートを見ることができます。

  • トップ（ツイート）
  • ニュース
  • 画像
  • 動画
  • ライブ配信
  • ネットメディア
  • 面白メディア
  • エンタメ
  • ライフスタイル
  • 女性向けメディア
  • マーケティング
  • スタートアップ
  • ブログ
  • イラスト
  • いらすとや
  • 漫画
  • 本
  • アニメ/ドラマ/映画
  • 音楽
  • アプリ
  • 料理
  • 飲食店
  • イベント
  • まとめサイト
  • プレスリリース
  • はてブ
  • NewsPicks
  • note / cakes
  • Voicy
  • bouncy
  • YouTube
  • TikTok
  • Amazon
  • bosyu
  • エンジニア求職
  • GitHub
  • Revue
  • 技術論文
  • スライド
  • Advent Calendar
  • やってみた
  • 歌ってみた
  • 演奏してみた
  • 弾いてみた
  • 叩いてみた
  • 踊ってみた
  • 描いてみた
  • 作ってみた
  • リリースした

技術以外のジャンルもありますが、サービスを作り上げるエンジニアたる者、色んなジャンルにアンテナを張って様々な情報に触れる必要があると思うわけです。

僕自身もアンテナを張りつつ、常に個人開発のサービスのアイディアに繋げられないかを考えていますので、このサービスによってその情報収集を楽にしたいと考えました。

また、ジャンルごとに以下基準で並び替えを行い、ランキング形式で情報を確認することができます。

  • リツイート数
  • いいね数
  • 引用数
  • リプライ数
  • フォロワーのうち、リツイートしてくれた割合（リツイート数 / フォロワー数）
  • フォロワーのうち、いいねしてくれた割合（いいね数 / フォロワー数）
  • フォロワーのうち、引用してくれた割合（引用数 / フォロワー数）
  • フォロワーのうち、リプライしてくれた割合（リプライ数 / フォロワー数）

twirepo_screenshot_sort.png

何で作ったの？

皆さんは情報収集って何でしていますか？

GIGAZINE、ITmediaなどの様々なネットメディア、それからはてなブックマーク、技術記事であればQiita、はてなブログ、等々、あげると切りがありません。

忙しいエンジニアにとっては、情報収集のためにすべてに目を通すのは難しいです。

きちんと情報収集されている方だと、RSSリーダーなどを使って自分の欲しい情報を効率的に得ている人もいるかと思います。

ただ、僕の場合、そこまでのことはやっておらず、はてなブックマークで話題になっている情報にさっと目を通して終わりというのがほとんどでした。

少ない時間の中でいかに効率良く情報収集できるか、を解決するために、今回自分でサービスを作ってみることにしました。

僕が目をつけたのは、ツイッターです。

ツイッターは自分がフォローした人がつぶやいた情報を見ることができますが、タイムラインではすべてを追うのは難しいです。 しかし、ツイートにはリツイート、いいね、リプライといった、他の人がつけた評価みたいなものがつけられています。

リツイート数やいいね数が多いものほど、話題になっている情報ととらえることができます。 この話題となっているツイートをキュレーションすることで、効率的に情報収集ができるのでは！？ と考え、このサービスを作り始めました。

使用技術

Linux

クラウドのホスティングはGoogle Cloud Platformで、Google Compute Engine（GCE）でサーバを立てています。 サーバのOSはLinuxでCentOSの7系を使っています。

Apache

webサーバはApacheを使っています。 Nginxもありますが、Apacheは普段から使っており、設定も把握していたため、いつも通りの安定の選択としました。

MySQL

Amazon RDSを使ってMySQLを立てています。

BigQuery

Google Cloud PlatformのBigQueryを使うようにしました。

リリース後2週間ほどツイートを収集したら100万レコードを超えてしまい、応答速度的にも今後ヤバいな...ってなったので、BigQueryへデータを移行しました。

BigQueryへの完全移行ではなく、MySQLとBigQueryのハイブリッド構成としました。

移行については、この後ちょっと触れます。

PHP

サーバ側のプログラムはPHPを使って実装しました。

jQuery

クライアント側のJavaScriptは、jQueryを使って実装しました。

ツイッターAPI

ツイートの情報を取得するために使用しています。 使い方については、後述させて頂きます。

cron

cronを使って定期的にバッチを走らせてツイートを収集しています。

MySQL -> BigQueryへのデータ移行

mysql-to-google-bigquery を使って、移行しました。

要件としてはPHP 7以上である必要がありますが、使い方は超簡単です。

①composerを使ってライブラリをダウンロード

$ composer require memeddev/mysql-to-google-bigquery

②プロジェクト直下に .env ファイルを用意し、以下の環境変数を設定する

BQ_PROJECT_ID=bigquery-project-id
BQ_KEY_FILE=google-service-account-json-key-file.json
BQ_DATASET=bigquery-dataset-name

DB_DATABASE_NAME=mysql-database-name
DB_USERNAME=mysql_username
DB_PASSWORD=mysql_password
DB_HOST=mysql-host

③MySQL -> BigQueryへのデータ同期を実行する

$ vendor/bin/console sync table-name --create-table

これだけでMySQLからBigQueryへデータを移行できちゃいます。

MySQLとBigQueryのハイブリッド構成

BigQueryにデータはすべて移行したんですが、BigQueryは保存しているデータ量およびクエリの検索対象となったデータ量をもとに課金され、検索対象のデータ量による課金が今後ヤバくなるのは目に見えているため、MySQLも使ったハイブリッド構成にしました。

ツイートをジャンルごとにリツイート数などでソートして一覧を取得する部分は比較的DBに負荷がかかるため、BigQueryを使用しています。 なお、ユニークIDからの単一のツイート取得はそれほどDBに負荷がかからないため、MySQLを使用しています。

とはいえ、毎回一覧を取得するたびにBigQueryを呼び出すのも高くなっちゃうので、「ジャンルごと」 × 「ソートのオーダーごと」 × 「日ごと」でjsonファイルで分けて結果をキャッシュファイルとして保存するようにしました。

jsonファイルがある場合はそのファイルからデータを取得し、jsonファイルがない場合はBigQueryを呼び出して結果を受け取り、jsonファイルへ保存するといった具合にしています。こうすることで、BigQueryによる検索量による課金地獄を回避しています（汗）、、クラウド破産こわい...＞＜

また、jsonファイルを保存する際には、単一のディレクトリに保存するのではなく、ファイル名をハッシュ化して先頭の2文字をとって、ディレクトリの階層を2つ作って保存しています。

以下のような感じです。

  • ①news_retweet_20181219.json の場合、 「news_retweet_20181219」の文字をハッシュ化
  • ②ハッシュ化された先頭2文字がa, bだった場合、a/b/ の階層でディレクトリを作成
  • ③ a/b/news_retweet_20181209.json へjsonファイルを保存

これは一つのディレクトリに1000個を超える大量のファイルを置くと、読み込み等のパフォーマンスに悪影響が出てしまうからです。

バズったツイートの可視化

ツイートがバズった要因のようなものが少しでも可視化できたらと思い、以下に挙げたグラフを確認できるようにしてみました。

各ジャンルごとに下部メニューのグラフアイコンをクリック、または各ツイートのインフォメーションアイコンをクリックするとみることができます。

f:id:no2z1:20181219081940p:plain

リツイート数、いいね数の分布

ランキング100位までのツイートのリツイート数、いいね数を分布図で可視化しました。 バズったツイートのリツイート数、いいね数の傾向を見ることができます。

f:id:no2z1:20181219074948p:plain

ツイートの時間帯

ランキング100位までのツイートの時間帯を棒グラフで可視化しました。 バズったツイートがどの時間帯でツイートされたかを見ることができます。

f:id:no2z1:20181219075035p:plain

ツイートの曜日・時間帯

ランキング100位までのツイートの曜日、時間帯をバブルチャートで可視化しました。 バズったツイートがどの曜日、時間帯でツイートされたかを見ることができます。

f:id:no2z1:20181219075145p:plain

使われているワード

ランキング100位までのツイートで使われているワードをワードクラウドで可視化しました。 バズったツイートでは、どんなワードが使われているかを見ることができます。

f:id:no2z1:20181219075159p:plain

エンゲージメント数

当該ツイートのエンゲージメント数を棒グラフで可視化しました。

f:id:no2z1:20181219075221p:plain

エンゲージメント率

当該ツイートのエンゲージメント率をアクティビティゲージグラフで可視化しました。 フォロワーのうちの何％が反応してくれているかを見ることができます。

f:id:no2z1:20181219075230p:plain

ツイッターAPI

ツイートはツイッターAPIを使って取得しています。

エンドポイント

GET
https://api.twitter.com/1.1/search/tweets.json

リクエストパラメータ

パラメータ 説明
q ツイートを検索するワード。検索演算子も利用できます
geocode 緯度,経度,範囲(半径)を指定。
lang 検索対象とする言語を指定。
locale 検索に使用する言語を指定。
result_type ツイート結果をpopular(話題のツイート)、recent(最新のツイート)、mixed(すべてのツイート)の中から指定。
count ツイートを取得する数を指定。
until yyyy-mm-ddの形式で検索期間のエンドを指定。
since_id 検索対象とするツイートIDのスタートを指定。
max_id 検索対象とするツイートIDのエンドを指定。
include_entities レスポンス値にentitiesプロパティを含めるかを指定

また、上記のqパラメータには以下の検索演算子を使うことができます。 これはツイッターの検索でも同様に使用できますので、ぜひお試しください。

検索演算子 説明
min_retweets:○○ リツイート数が○○以上のツイートを取得できます。
min_faves:○○ いいね数が○○以上のツイートを取得できます。
min_replies:○○ リプライ数が○○以上のツイートを取得できます。
@○○ ○○(ユーザーID)さんに関するツイートを（ユーザーがしたツイートも、ユーザーへのリプライ、メンションも含め）取得できます。
from:○○ ○○(ユーザーID)さんがしたツイートを取得できます。
to:○○ ○○(ユーザーID)さんへのツイート（リプライ、メンション）を取得できます。
since:yyyy-mm-dd yyyy年mm月dd日以降のツイートを取得できます。
until::yyyy-mm-dd yyyy年mm月dd日までのツイートを取得できます。
filter:images 画像が含まれるツイートを取得できます。
filter:videos 動画が含まれるツイートを取得できます。
filter:links リンクが含まれるツイートを取得できます。
filter:news ニュースに関するツイートを取得できます。
filter:periscope ライブ配信に関するツイートを取得できます。
filter:verified 認証アカウントのツイートを取得できます。
filter:safe 不適切でないツイートを取得できます。
source:○○ ツイートの投稿元（クライアント）を指定して取得することができます。○○には「Instagram」などが使えるようです。
near:○○ within:××km ○○の場所、半径××km以内でツイートされたものを取得できます。
geocode:○○,△△,××km 緯度○○、経度△△、半径××km以内でツイートされたものを取得できます。
lang：○○ ツイートの言語を指定して検索することができます。日本語の場合は「ja」を指定します。
"○○ ××" 「○○ ××」に完全一致したツイートのみ取得できます。
:) ポジティブなツイートを取得できるみたいです。
:( ネガティブなツイートを取得できるみたいです。
- 条件の先頭に「-」をつけると否定の条件にすることができます。これを使うことで除外したツイートを取得できます。
OR 通常は半角スペースで複数のワードを入力するとAND検索となりますが、ORを使うことでOR検索をすることができます。

ツイート収集対象と条件

ツイートを取得する対象と条件について、以下にまとめてみます。 なお、以下は各ジャンルごとの条件で、これに以下の流れで条件を追加して収集しています。

    1. リツイート数が 10000 または いいね数が 10000 のツイート収集（すべて収集したら次へ）

　↓

    1. リツイート数が 1000 または いいね数が 1000 のツイート収集（すべて収集したら次へ）

　↓

    1. リツイート数が 100 または いいね数が 100 のツイート収集（すべて収集したら次へ）

　↓

    1. リツイート数が 10 または いいね数が 10 のツイート収集（すべて収集したら終わり）

　　※リツイート数といいね数がともに10未満のツイートは収集していません

トップ

lang:ja -filter:verified

日本語のツイートをすべて取得します。 ただし、フォロワー数が多く、リツイート数やいいね数が多くつく認証アカウントのツイートは除外しています。

ニュース

filter:news lang:ja

ニュースに関するツイートをすべて取得します。 ニュースでは信頼性の高い認証アカウントのツイートも含めています。

画像

filter:images lang:ja -filter:verified

画像が含まれるツイートをすべて取得します。 フォロワー数が多く、リツイート数やいいね数が多くつく認証アカウントのツイートは除外しています。

動画

filter:videos lang:ja -filter:verified

動画が含まれるツイートをすべて取得します。 フォロワー数が多く、リツイート数やいいね数が多くつく認証アカウントのツイートは除外しています。

ライブ配信

filter:periscope lang:ja -filter:verified

ライブ配信に関するツイートをすべて取得します。 フォロワー数が多く、リツイート数やいいね数が多くつく認証アカウントのツイートは除外しています。

ネットメディア

(gigazine.net OR gizmodo.jp OR nlab.itmedia.co.jp OR rocketnews24.com OR getnews.jp OR wired.jp OR itmedia.co.jp OR ggsoku.com) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

面白メディア

(omocoro.jp OR labaq.com OR dailyportalz.jp OR curazy.com) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

エンタメ

(oricon.co.jp OR mantan+web.jp OR nikkan*spa.jp OR wpb.shueisha.co.jp OR narinari.com OR news.mynavi.jp OR cinra.net OR rockinon.com) filter:links lang:ja

※半角ハイフン「-」がURLに含まれると、うまく検索できないケースがあったため、「+」や「*」に置き換えているものがあります。

以下メディアのリンクが含まれたツイートを取得します。

ライフスタイル

(nanapi.jp OR enuchi.jp OR locari.jp OR sheage.jp) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

女性向けメディア

(men-joy.jp OR mainichikirei.jp OR otajo.jp OR youpouch.com OR mdpr.jp OR japan.techinsight.jp) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

マーケティング

(liginc.co.jp OR bazubu.com OR ferret-plus.com OR seojapan.com OR webtan.impress.co.jp OR liskul.com OR gaiax-socialmedialab.jp OR seolaboratory.jp OR smmlab.jp OR dmlab.jp OR promonista.com OR cont+hub.com) filter:links lang:ja

※半角ハイフン「-」がURLに含まれると、うまく検索できないケースがあったため、「+」や「*」に置き換えているものがあります。

以下メディアのリンクが含まれたツイートを取得します。

スタートアップ

(japan.cnet.com OR jp.techcrunch.com OR thebridge.jp OR techwave.jp OR techable.jp OR thestartup.jp OR turnyourideasintoreality.com) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

ブログ

(hatenablog.com OR hatenablog.jp OR hateblo.jp OR hatenadiary.com OR hatenadiary.jp OR ameblo.jp OR blog.livedoor.jp OR medium.com OR blogos.com OR blogger.com OR exblog.jp OR cocolog-nifty.com) filter:links lang:ja

以下ブログのリンクが含まれたツイートを取得します。

イラスト

(#pixiv OR pixivision) filter:images lang:ja

以下ワードのリンクが含まれたツイートを取得します。

  • pixiv

  • pixivision

いらすとや

irasutoya.com filter:images lang:ja

以下サイトの画像が含まれたツイートを取得します。

漫画

(mangaz.com OR sukima.me OR bookstore.yahoo.co.jp OR comic-walker.com OR manga-bang.com OR #pixivコミック OR manga-zero.com OR cmoa.jp OR sokuyomi.jp OR renta.papy.co.jp OR mangabox.me OR comico.jp OR dokuha.jp OR comic-meteor.jp OR mangag.com OR comic-polaris.jp)

以下サイトのリンクが含まれたツイートを取得します。

本

(bookmeter.com OR booklog.jp OR honz.jp OR sinkan.jp OR flierinc.com OR bijodoku.com OR bizpow.bizocean.jp OR bukupe.com OR bookvinegar.jp OR book-smart.jp OR booklog.kinokuniya.co.jp) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

アニメ/ドラマ/映画

(gyao.yahoo.co.jp OR anime.nicovideo.jp OR abema.tv OR fod.fujitv.co.jp OR cu.ntv.co.jp OR videomarket.jp OR tver.jp OR freshlive.tv) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

音楽

spotify.com filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

アプリ

(itunes.apple.com OR play.google.com) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

料理

(cookpad.com OR kurashiru.com) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

飲食店

(tabelog.com OR gnavi.co.jp OR retty.me OR hotpepper.jp OR hitosara.com) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

イベント

(doorkeeper.jp OR connpass.com OR peatix.com OR atnd.org) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

まとめサイト

(togetter.com OR matome.naver.jp) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

プレスリリース

prtimes.jp filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

はてブ

b.hatena.ne.jp filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

NewsPicks

newspicks.com filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

note / cakes

(note.mu OR cakes.mu) filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

Voicy

voicy.jp filter:links lang:ja

以下メディアのリンクが含まれたツイートを取得します。

bouncy

bouncy.news filter:videos lang:ja

以下メディアの動画が含まれたツイートを取得します。

YouTube

(youtu.be OR youtube.com) filter:links lang:ja

以下メディアの動画が含まれたツイートを取得します。

TikTok

TikTok filter:videos lang:ja

以下ワードの動画が含まれたツイートを取得します。

  • TikTok

Amazon

(amazon.co.jp OR amzn.to) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

bosyu

bosyu.me filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

エンジニア求職

#hiyokonitsuduke OR #Twitter転職 lang:ja

以下ワードのツイートを取得します。

  • hiyokonitsuduke

  • Twitter転職

GitHub

(github.com OR c) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

Revue

getrevue.co filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

技術論文

arxiv.org filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

技術記事

(qiita.com OR qrunch.io OR #Crieit) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

スライド

(slideshare.net OR speakerdeck.com) filter:links lang:ja

以下サイトのリンクが含まれたツイートを取得します。

Advent Calendar

Advent Calendar filter:links lang:ja

以下ワードのリンクが含まれたツイートを取得します。

  • Advent Calendar

やってみた

(やってみた OR やってみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • やってみた
  • やってみました

歌ってみた

(歌ってみた OR 歌ってみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • 歌ってみた
  • 歌ってみました

演奏してみた

(演奏してみた OR 演奏してみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • 演奏してみた
  • 演奏してみました

弾いてみた

(弾いてみた OR 弾いてみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • 弾いてみた
  • 弾いてみました

叩いてみた

(叩いてみた OR 叩いてみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • 叩いてみた
  • 叩いてみました

踊ってみた

(踊ってみた OR 踊ってみました) filter:videos lang:ja -filter:verified

以下ワードの動画が含まれたツイートを取得します。

  • 踊ってみた
  • 踊ってみました

描いてみた

(描いてみた OR 描いてみました) filter:images lang:ja -filter:verified

以下ワードの画像が含まれたツイートを取得します。

  • 描いてみた
  • 描いてみました

作ってみた





以下ワードのリンク、画像、動画が含まれたツイートを取得します。



    

  • 作ってみた
    • 

  • 作ってみました
    • 

  • 作りました
    • 

  • つくってみた
    • 

  • つくってみました
    • 

  • つくりました
    • 





リリースした



(リリースしました OR 開発しました OR Webサービス OR webサービス) (filter:links OR filter:media) lang:ja -filter:verified




以下ワードのリンク、画像、動画が含まれたツイートを取得します。



    

  • リリースしました
    • 

  • 開発しました
    • 

  • Webサービス
    • 

  • webサービス
    • 





まとめ



今回は各ジャンルごとに話題のツイートを収集し、ランキングで表示するところまで実装しました。



twirepo_screenshot1.png



作ってみて感じたのは、ジャンルによってはまだまだ収集したツイートの精度があまり良くなかったことです。
ツイートにつけられたリツイート数やいいね数での評価だけでは、自分の望む精度の高い情報にはまだ及びませんでした。



今後の課題としては、情報の精度をリツイート数やいいね数といったツイートの評価の他に「ツイレポ」のサービス内で改善できそうな点を考えていきたいと思います。
また、今回の実装でリツイート数やいいね数は収集してるので、これを使ってツイートがバズる要因や傾向なども分析していければと考えています。



なお、今回、ジャンル分けやその収集対象は自分の独断と偏見で決めてしまっていますので、これを入れたらいいんじゃないか、これを入れて欲しいなどのご要望がありましたら、「ツイレポ」のご意見・ご要望よりご連絡を頂ければ幸いです。



twirepo_screenshot_contact.png



本サービスに少しでも興味をもって頂けましたら、ぜひ 本記事のいいね や Twitterのフォロー、いいねやリツイートをして頂けると嬉しいです！












ぜひこのサービスを使って情報収集してみてください！



■ツイレポ  

https://twirepo.com/






明日は@Fujiyama_Yutaさんの「今作っているサービスを、この日までにリリースして記事を書きます！」です。



お楽しみに！