CData Software Blog

クラウド連携のCData Software の技術ブログです。

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

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

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

f:id:cdatasoftware:20181226144046p:plain
SQL で片思われJJUG ツイートを抽出

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

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

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

f:id:cdatasoftware:20181226110521p:plain
CData JDBC Driver for Twitter
https://www.cdata.com/jp/drivers/twitter/jdbc/

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

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

https://www.cdata.com/jp/drivers/twitter/download/jdbc/

f:id:cdatasoftware:20181226110700p:plain

Java IDEJDBCTwitter に接続

お好みのJava IDE (EclipseIntelliJNetBeans など)やJDBC でDB を叩けるツールを選んで、TwitterJDBC 経由で接続してみます。

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

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

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

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

    f:id:cdatasoftware:20181226111039p:plain
    新規JDBC ドライバー作成

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

次に「新規接続」を作成します。登録したドライバを選択して、JDBC URL に認証パラメータを入れます。

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

f:id:cdatasoftware:20181226111612p:plain
JDBC 接続設定

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

お題のクエリを作る

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

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

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

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

f:id:cdatasoftware:20181226112809p:plain
片思われリスト抽出

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

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

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

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

f:id:cdatasoftware:20181226113357p:plain
JJUG に関するツイートを抽出

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 検索の制限としては、直近のツイートしか取れない、という点があります。これはTwitterAPI の制約です。

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

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

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

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

まとめ

このようにTwitterAPI 情報を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;

f:id:cdatasoftware:20181226153503p:plain
片思われフォロワーID で抽出