GA

2019/12/23

Twitterの過去のツイート履歴をMySQLに取り込む in 2019

TL;DR

  • 過去のツイート履歴がCSVからJSに変わったっぽい
  • でも大丈夫、俺達には JSON_TABLE関数 があるから
かつてはCSVだったツイート履歴、最近ダウンロードしてみたらJSファイルになっていた。しかもでかい。
$ mkdir work
$ cd work
$ unzip ../twitter-2019-12-20-ce0bbf92f327035a47c135f037e0568f6166df65f5f1011bc9d0bc2b6b9b6c3f.zip
..

$ du -sh .
239M    .

$ ll -h tweet.js
-rwxrwxrwx 1 yoku0825 yoku0825 47M Dec 20 09:00 tweet.js
しかもこのJS、よしなにpretty printされていてグレッパビリティが悪い。
$ head tweet.js
window.YTD.tweet.part0 = [ {
  "retweeted" : false,
  "source" : "<a href=\"https://mobile.twitter.com\" rel=\"nofollow\">Twitter Web App</a>",
  "entities" : {
    "hashtags" : [ ],
    "symbols" : [ ],
    "user_mentions" : [ {
      "name" : "坂井 恵(SAKAI Kei)",
      "screen_name" : "sakaik",
      "indices" : [ "0", "7" ],
今まで通り俺はテキトーにgrepしてtweet_idを引きたいんだ…！
という訳で、このtweet.jsをMySQLに突っ込むことにした。
先頭の window.YTD.tweet.part0 = はJSONとしてはInvalidになるのが目に見えているので、ここはあらかじめ削り取ってしまう。
$ jq . tweet.js > /dev/null
parse error: Invalid numeric literal at line 1, column 23
$ cp -ip tweet.js{,.orig}
$ sed -i 's/window.YTD.tweet.part0 = //' tweet.js
$ jq . tweet.js > /dev/null
取り敢えずMySQLは使い捨てで良いのでMySQL ShellのdeploySandboxInstanceでいっこ起動する。
$ mysqlsh
 MySQL  JS > dba.deploySandboxInstance(3306)
..
 MySQL  JS > \c root@localhost:3306
 MySQL  localhost:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:3306 ssl  SQL > CREATE DATABASE d1;
Query OK, 1 row affected (0.0291 sec)
取り敢えずガワになる d1 スキーマを作ったあと、せっかくなので util.importJSON を使おうかと思ってJSモードに戻ってから叩いてみる。
 MySQL  localhost:3306 ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  localhost:3306 ssl  JS > util.importJson('./tweet.js')
Util.importJson: An X Protocol session is required for JSON import. (RuntimeError)
Xプロトコルじゃないと使えないらしいので、root@localhost:33060に接続しなおしてもう一度。
 MySQL  localhost:3306 ssl  JS > \c root@localhost:33060

 MySQL  localhost:33060+ ssl  JS > util.importJson('./tweet.js')
Util.importJson: There is no active schema on the current session, the target schema for the import operation must be provided in the options. (RuntimeError)
no active schema ってことは USE d1 的なものを押し込まないといけなさそうだけど、JSモードでやる方法がわからないのでSQLモードで USE d1 してからJSモードに戻る。
わかんなかったSQLでやれるのは良いところなのかも知れない、マイエスキューエルシェル。
 MySQL  localhost:33060+ ssl  JS > \sql use d1
Query OK, 0 rows affected (0.0007 sec)

 MySQL  localhost:33060+ ssl  d1  JS > util.importJson('./tweet.js')
Importing from file "./tweet.js" to collection `d1`.`tweet` in MySQL Server at localhost:33060

Processed 0 bytes in 0 documents in 0.0031 sec (0.00 documents/s)
Total successfully imported documents 0 (0.00 documents/s)
Util.importJson: Input does not start with a JSON object at offset 0 (ArgumentError)
失敗…。
 MySQL  localhost:33060+ ssl  d1  JS > \sql
Switching to SQL mode... Commands end with ;

 MySQL  localhost:33060+ ssl  d1  SQL > SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| tweet        |
+--------------+
1 row in set (0.0020 sec)

 MySQL  localhost:33060+ ssl  d1  SQL > DESC tweet;
+-------+---------------+------+-----+---------+------------------+
| Field | Type          | Null | Key | Default | Extra            |
+-------+---------------+------+-----+---------+------------------+
| doc   | json          | YES  |     | NULL    |                  |
| _id   | varbinary(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+---------------+------+-----+---------+------------------+
2 rows in set (0.0021 sec)

 MySQL  localhost:33060+ ssl  d1  SQL > SELECT * FROM tweet;
Empty set (0.0010 sec)
なんかざっと見た感じ、 _id がgenerated columnでPRIMARY KEY(= NOT NULL)なので、そういうカラムが存在しないとダメな気がする。
ドキュメント を流し読んでも、 _id のカラム名を別のもの(tweet_idにできればいいような気がするのよね) に変える方法がパッと見えなかったのでさっさと諦めてSQLでやることにした。
 MySQL  localhost:33060+ ssl  d1  SQL > DROP TABLE tweet;
Query OK, 0 rows affected (0.0746 sec)

 MySQL  localhost:33060+ ssl  d1  SQL > CREATE TABLE t1_tmp (val LONGTEXT);
Query OK, 0 rows affected (0.1394 sec)

 MySQL  localhost:33060+ ssl  d1  SQL > INSERT INTO t1_tmp SELECT LOAD_FILE('/home/yoku0825/down/work/tweet.js');
Query OK, 1 row affected (0.0223 sec)

Records: 1  Duplicates: 0  Warnings: 0

 MySQL  localhost:33060+ ssl  d1  SQL > SELECT * FROM t1_tmp LIMIT 1\G
*************************** 1. row ***************************
val: NULL
1 row in set (0.0005 sec)
ん、しまった、 secure_file_priv があるから LOAD_FILE がNULLになってる。
 MySQL  localhost:33060+ ssl  d1  SQL > SET GLOBAL secure_file_priv= '';
ERROR: 1238: Variable 'secure_file_priv' is a read only variable

 MySQL  localhost:33060+ ssl  d1  SQL > SET PERSIST_ONLY secure_file_priv= '';
ERROR: 1238: Variable 'secure_file_priv' is a non persistent read only variable
できないのかYO…。
おとなしく my.cnf をいじって secure_file_priv = "" を押し込んで再起動。
 MySQL  localhost:33060+ ssl  d1  SQL > TRUNCATE t1_tmp;
Query OK, 0 rows affected (0.1484 sec)

 MySQL  localhost:33060+ ssl  d1  SQL > INSERT INTO t1_tmp SELECT LOAD_FILE('/home/yoku0825/down/work/tweet.js');
Query OK, 1 row affected (4.8416 sec)

Records: 1  Duplicates: 0  Warnings: 0

 MySQL  localhost:33060+ ssl  d1  SQL > \pager head -1000c
Pager has been set to 'head -1000c'.

 MySQL  localhost:33060+ ssl  d1  SQL > SELECT * FROM t1_tmp\G
*************************** 1. row ***************************
val: [ {
  "retweeted" : false,
  "source" : "<a href=\"https://mobile.twitter.com\" rel=\"nofollow\">Twitter Web App</a>",
  "entities" : {
    "hashtags" : [ ],
    "symbols" : [ ],
    "user_mentions" : [ {
      "name" : "坂井 恵(SAKAI Kei)",
      "screen_name" : "sakaik",
      "indices" : [ "0", "7" ],
      "id_str" : "3677561",
      "id" : "3677561"
    } ],
    "urls" : [ ]
  },
  "display_text_range" : [ "0", "27" ],
  "favorite_count" : "0",
  "in_reply_to_status_id_str" : "1207929574333403136",
  "id_str" : "1207929799047385088",
  "in_reply_to_user_id" : "3677561",
  "truncated" : false,
  "retweet_count" : "0",
  "id" : "1207929799047385088",
  "in_reply_to_status_id" : "1207929574333403136",
  "created_at" : "Fri Dec 20 07:44:41 +0000 2019",
  "favorited" : false,
  "full_text" : "@sakaik なるほどミニ丸の方ですか、見てみます！",
  "lang" : "ja",
  "in_reply_to_screen_name" : "sakaik",
OK、入った。
ここからが本丸、 JSON_TABLE 使う。
JSON_TABLE関数に、既にテーブルに入った値を使いたい時は、 FROM t1_tmp, JSON_TABLE(..) の形式でJOINっぽく書いてやる。
PATHの書き方がつらい感じだけどマニュアル首っ引きでやると
 MySQL  localhost:33060+ ssl  d1  SQL > SELECT tweet_id FROM t1_tmp, JSON_TABLE(val, '$[*]' COLUMNS (tweet_id BIGINT UNSIGNED PATH '$.id')) AS json LIMIT 10;
+---------------------+
| tweet_id            |
+---------------------+
| 1207929799047385088 |
| 1207929689987153920 |
| 1207928245233971200 |
| 1207927537533251584 |
| 1207926165190504448 |
| 1207897765130326016 |
| 1207897362191941632 |
| 1207897143224098819 |
| 1207895949948768257 |
| 1207853754973749249 |
+---------------------+
10 rows in set (1.4292 sec)
よし、あたかもテーブル状に、1ツイートが1行の状態で手に入れられそう。
generated columnでパーツ分けはするとして、生JSONの状態で行に分けられればいいのでJSON_TABLEはこんな感じか。
 MySQL  localhost:33060+ ssl  d1  SQL > SELECT tweet_json FROM t1_tmp, JSON_TABLE(val, '$[*]' COLUMNS (tweet_json JSON PATH '$')) AS json LIMIT 1\G
*************************** 1. row ***************************
tweet_json: {"id": "1207929799047385088", "lang": "ja", "id_str": "1207929799047385088", "source": "<a href=\"https://mobile.twitter.com\" rel=\"nofollow\">Twitter Web App</a>", "entities": {"urls": [], "symbols": [], "hashtags": [], "user_mentions": [{"id": "3677561", "name": "坂井 恵(SAKAI Kei)", "id_str": "3677561", "indices": ["0", "7"], "screen_name": "sakaik"}]}, "favorited": false, "full_text": "@sakaik なるほどミニ丸の方ですか、見てみます！", "retweeted": false, "truncated": false, "created_at": "Fri Dec 20 07:44:41 +0000 2019", "retweet_count": "0", "favorite_count": "0", "display_text_range": ["0", "27"], "in_reply_to_user_id": "3677561", "in_reply_to_status_id": "1207929574333403136", "in_reply_to_screen_name": "sakaik", "in_reply_to_user_id_str": "3677561", "in_reply_to_status_id_str": "1207929574333403136"}
1 row in set (1.8848 sec)
よし、じゃあテーブルに突っ込もう。
 MySQL  localhost:33060+ ssl  d1  SQL > CREATE TABLE t1 (tweet_json JSON);
Query OK, 0 rows affected (0.0843 sec)
 MySQL  localhost:33060+ ssl  d1  SQL > INSERT INTO t1 SELECT tweet_json FROM t1_tmp, JSON_TABLE(val, '$[*]' COLUMNS (tweet_json JSON PATH '$')) AS json;
Query OK, 55925 rows affected (4.3389 sec)

Records: 55925  Duplicates: 0  Warnings: 0
generated columnでテキトーに切り分けていくますよ。
 MySQL  localhost:33060+ ssl  d1  SQL > ALTER TABLE t1 ADD tweet_id BIGINT UNSIGNED AS (tweet_json->>'$.id'),                ADD full_text TEXT AS (tweet_json->>'$.full_text'),                ADD created_at VARCHAR(50) AS (tweet_json->>'$.created_at');
Query OK, 0 rows affected (0.0508 sec)

Records: 0  Duplicates: 0  Warnings: 0
 MySQL  localhost:33060+ ssl  d1  SQL >
 MySQL  localhost:33060+ ssl  d1  SQL >
 MySQL  localhost:33060+ ssl  d1  SQL > SELECT tweet_id, full_text, created_at FROM t1 LIMIT 3;
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+
| tweet_id            | full_text                                                                                                                                                                                                  | created_at                     |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+
| 1207929799047385088 | @sakaik なるほどミニ丸の方ですか、見てみます！                                                                                                                                                             | Fri Dec 20 07:44:41 +0000 2019 |
| 1207929689987153920 | @sakaik 違うOS向けのバイナリを動かそうとよく出るやつっぽいので、インストールするパッケージが間違っていないか気になります

RHEL 7.x用のパッケージを無理矢理RHEL 6.xにインストールするとかそんなかんじです！ | Fri Dec 20 07:44:14 +0000 2019 |
| 1207928245233971200 | @sakaik パッケージものだとすると、サポート対象外になってglibcのバージョンが引きあがったとかありそうですが

https://t.co/MipGicihkr                                                                         | Fri Dec 20 07:38:30 +0000 2019 |
+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
created_atはDATETIME型にキャストしようとしたらできなかったので取り敢えずVARCHARで我慢するとして、これで取り敢えず今まで通り1行1ツイートでゴニョゴニョできるようになった。
in_reply_to_status_idとか切り出せば、再帰CTEでそのスレッドを辿れるな、とか思いつつ取り敢えずここまで。
​
投稿者 時刻:
ラベル: ,

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )