こっちがフォロバしてなくて、XXXに関するツイートをしたことがある人を抽出-Twitter JDBC Driver

今回の記事のお題はこちら： 「こっちがフォロバしてなくて、JJUGに関するツイートをしたことがある人、みたいなのを抽出」

こっちがフォロバしてなくて、JJUGに関するツイートをしたことがある人、みたいなのを抽出したいんですが、やり方ありますかね。フォロバ漏れをなくしていきたい。

— 谷本 心 / CERO-METAL (@cero_t) 2018年12月25日

せっかくJava コミュニティの方のリクエストですので、CData JDBC Driver for Twitter での抽出方法を解説します。

CData JDBC Driver for Twitter の説明と設定方法

この製品は、Twitter のAPI をJDBC で（SQL で）叩けるようにするライブラリです。Java アプリケーション内でのTwitter 連携はもちろん、JDBC インターフェースに対応しているETL ツール（ASTERIA WARP、Talend、DataSpider など）や、BI・帳票ツールからでもTwitter データを標準SQL で利用できるようになります。

扱えるTwitter 内の項目も、ツイート、メッセージ、フォロワー、フォロー先、List などの他に、広告系の項目も取れるので便利です。

JDBC Driver for Twitter のダウンロードとインストール

まずは、Twitter JDBC Driver をダウンロードして、インストールします（30日間フル機能を使える評価版あり）。

Download JDBC Driver for Twitter. 30-Day Free Trial

Java IDE でJDBC でTwitter に接続

お好みのJava IDE (Eclipse、IntelliJ、NetBeans など）やJDBC でDB を叩けるツールを選んで、Twitter にJDBC 経由で接続してみます。

まずは新規JDBC ドライバーを登録します。

• ドライバ・ファイル：デフォルトでインストールするとこちらのディレクトリにドライバの.jar ファイルがあります　C:\Program Files\CData\CData JDBC Driver for Twitter 2018J\lib

• ドライバ・クラス：cdata.jdbc.twitter.TwitterDriver

• 名前：任意です。CDataTwitter などと付けます。

  

これで「OK」を押すとドライバが登録されます。

次に「新規接続」を作成します。登録したドライバを選択して、JDBC URL に認証パラメータを入れます。 追記：新しくなったTwitter JDBC Driver では組み込みOAuth 認証がサポートされているので、OAuth=GETANDREFRESH だけを設定して「接続」でブラウザで認証してくれます。個別にTwitter アプリを作成してOAuth 系の認証情報を入力する必要はありません。

URL 例：jdbc:twitter:InitiateOAuth=GETANDREFRESH;OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://127.0.0.1/;OAuthAccessToken=MyOAuthAccessToken;OAuthAccessTokenSecret=MyOAuthAccessTokenSecret;

細かい接続方法はヘルプご参照： http://cdn.cdata.com/help/GTD/jdbc/pg_connectionj.htm

接続テストをして、接続が確認できたら、OK を押して接続を確立します。

お題のクエリを作る

「こっちがフォロバしてないユーザー」を抽出

「片思われ」リストと呼ばれるものですね。Twitter Driver にはFollower ビューがあります。自分のアカウントをフォローしているアカウントのリストです。ここに「Following」というカラムがあり、ブール値で「自分がフォローしているか、していないか」という情報があります。

以下のSQL でフォローされているけど、フォローバックしていないアカウントリストが取得できます：

SELECT ID, Screen_Name, Following FROM Followers where Following = 'false'

「JJUG に関するツイート」を抽出

Tweets テーブルでは、ツイートをレコード形式で取得できます。SearchTerms という疑似カラムをつかって、特定の文字列を含むツイートを取得できます。全ツイートを検索する訳ではないので、Twitter のAPI リミットに引っかからず目的の文字列を含むツイートだけを抽出できるのがポイントです。

SELECT From_User_ID, From_User_Name, Text FROM Tweets where SearchTerms = 'jjug'

もし、これでもリミットを超過する場合にはSQL 文にLIMIT 10,000 などを付けましょう。

https://www.cdata.com/jp/drivers/twitter/excel/ でやれば、VLOOKUP でできちゃう気もします。

二つのSQL をつなげる

SELECT * FROM 
(
SELECT Tweets.From_User_ID,Tweets.From_User_Name, Tweets.From_User_Screen_Name,Followers.Following, Tweets.Text
FROM Tweets 
LEFT OUTER JOIN Followers ON Tweets.From_User_ID = Followers.ID
WHERE Tweets.SearchTerms = 'jjug'
) 
WHERE Following = false; // true だと フォローしているひと。　is null だと フォローもフォロワーでも無いけれど、JJUGのツイートをしているひと

制約事項

デフォルトでは直近7日のツイートのみ

Tweets テーブルのSearchTerms は上述の通り、多くのツイートの中から特定の文字列だけを抜き出すことができるため便利です。これをツイート本文である「Text」カラムにLike 条件を入れると、タイムラインをフルスキャンしてしまって一発でレートリミットがオーバーになります。「Hashtags」も同様です。レコード数にLIMIT を書けるとタイムラインの上からLIMIT 数までを一度取得して、そこからText のLike 条件やHashtags 条件を掛けるので、すごく少ない数の結果しか返ってきません。

Tweets テーブルのSearchTerms 検索の制限としては、直近のツイートしか取れない、という点があります。これはTwitter のAPI の制約です。

デフォルトでは、Twitter は最大7日分の検索からのツイートのみを返すことをサポートしています。ただし、プレミアムアカウントをお持ちのお客様は、30日または全アーカイブ検索を実行することで、これより古いツイートを取得することができます。

JJUG ツイートはイベント近辺に固まると思いますので、イベント後落ち着いたら日を空けずクエリしてください。もしくは7日おきにクエリするプログラムを組むとか。

SearchTerms はUser_Id などの項目と組み合わせてクエリできない

今回のケースでは、Follower のユーザーId と組み合わせてのTweets クエリをしたかったのですが、同時にクエリがかけられない仕様です。SearchTerms の結果セットが数千ぐらいであれば、全件取得してもTwitter のAPI リミットにかからないので、全件取得した上で、Followers カラムをOUTER JOIN しています。

まとめ

このようにTwitter のAPI 情報をSQL で処理するといろいろなことができます。

是非、試用版でお試しください。

「こんな抽出できる？」というご相談もお寄せ下さい。できる範囲で回答します。

追記： フォロワーID ベースでDistinct で見たい場合はこちら：

SELECT * FROM
 (
SELECT Tweets.From_User_ID,Tweets.From_User_Name, Tweets.From_User_Screen_Name, Followers.Following FROM Tweets 
LEFT OUTER JOIN Followers ON Tweets.From_User_ID = Followers.ID
WHERE Tweets.SearchTerms = 'jjug'
GROUP BY Tweets.From_User_ID
) 
WHERE Following = false;

関連コンテンツ