TL;DR
かつては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)
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",
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でそのスレッドを辿れるな、とか思いつつ取り敢えずここまで。