TL;DR

LOAD DATA INFILE ステートメントで、CSVなりTSVなりのフィールドの並び順とテーブルのカラムの並び順が一緒じゃない時にほげる方法とか
読み取った値を加工してからテーブルに突っ込む方法とか

Twitter からダウンロードできる tweets.csv はこんなフォーマットをしている。

"tweet_id","in_reply_to_status_id","in_reply_to_user_id","timestamp","source","text","retweeted_status_id","retweeted_status_user_id","retweeted_status_timestamp","expanded_urls"
"878118626489802752","","","2017-06-23 05:12:49 +0000","<a href=""http://twitter.softama.com/"" rel=""nofollow"">ツイタマ+ for Android</a>","宇宙の 法則が 乱れる！","","","",""

先頭1行がヘッダ行になっていて、フィールドは全てダブルクォートされ、タイムスタンプはUTCで、UTCであることが +0000 でわかるようになっている（聞こえますかMySQLのみなさん）

ちなみに expanded_urls が複数ある場合、特に断りもなくコンマ区切りで後ろに要素が続く（10フィールドより多くフィールドがある場合がある、ばかな）

これをこんな感じのテーブルに、MySQLだけでパースシテ何とか上手いことLOAD DATA INFILEしたいとする。

mysql80 23> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `tweet_id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `source` text COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `text` text COLLATE utf8mb4_ja_0900_as_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)

まずはわかりやすくするために、全フィールドの内容を変数に受け取るステートメント。

mysql80 23> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (@tweet_id,
    ->    @in_reply_to_status_id,
    ->    @in_reply_to_user_id,
    ->    @timestamp,
    ->    @source,
    ->    @text,
    ->    @retweeted_status_id,
    ->    @retweeted_status_user_id,
    ->    @retweeted_status_timestamp,
    ->    @expanded_urls)
    -> ;
Query OK, 41123 rows affected (0.50 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0

FILEDSとかIGNOREとかのフォーマット指定の後に (いつも忘れる) 、変数のリストを並べることで、それぞれのフィールドをそれぞれの変数に受け取ることができる。

（↑のLOAD DATA INFILEは変数にセットするだけでカラムに何も値をセットしていないので、恐ろしく空っぽな行がt1テーブルにロードされている…）

この変数を使って、SET句でカラムに値を指定することができる。

mysql80 23> TRUNCATE t1;
Query OK, 0 rows affected (0.09 sec)

mysql80 25> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (@tweet_id,
    ->    @in_reply_to_status_id,
    ->    @in_reply_to_user_id,
    ->    @timestamp,
    ->    @source,
    ->    @text,
    ->    @retweeted_status_id,
    ->    @retweeted_status_user_id,
    ->    @retweeted_status_timestamp,
    ->    @expanded_urls)
    ->   SET
    ->     tweet_id = @tweet_id,
    ->     timestamp = TIMESTAMPADD(HOUR, 9, REGEXP_SUBSTR(@timestamp, '\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}')),
    ->     source = @source,
    ->     text = @text
    -> ;
Query OK, 41123 rows affected (1.33 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0

折角なので REGEXP_SUBSTRで遊んでみた時のを活かして、タイムスタンプを正規表現でゴニョっている。

基本的な仕組みはこんなところで、余計なフィールドを変数で受けずに捨てるための変数( @dummy )に受けたりカラムに直接渡したりなんてことをすると最終的に

mysql80 25> TRUNCATE t1;
Query OK, 0 rows affected (0.04 sec)

mysql80 25>
mysql80 25>
mysql80 25> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (tweet_id,
    ->    @dummy,
    ->    @dummy,
    ->    @timestamp,
    ->    source,
    ->    text,
    ->    @dummy,
    ->    @dummy,
    ->    @dummy,
    ->    @dummy
    ->   )
    ->   SET
    ->     timestamp = TIMESTAMPADD(HOUR, 9, REGEXP_SUBSTR(@timestamp, '\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}'))
    -> ;
Query OK, 41123 rows affected (1.05 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0

mysql80 25> SELECT * FROM t1 LIMIT 3;
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
| tweet_id           | timestamp           | source                                                                               | text                                                                           |
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
| 878118626489802752 | 2017-06-23 14:12:49 | <a href="http://twitter.softama.com/" rel="nofollow">ツイタマ+ for Android</a>       | 宇宙の 法則が 乱れる！                                                         |
| 878116753091448832 | 2017-06-23 14:05:23 | <a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a> | RT @xaicron: @yoku0825 MySQLは時を超える...                                    |
| 878116497389797377 | 2017-06-23 14:04:22 | <a href="http://twitter.softama.com/" rel="nofollow">ツイタマ+ for Android</a>       | @xaicron (  д ) ﾟ  ﾟ ホントだ！！ ありがとうございます！                       |
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

こんな感じになる。

って感じでどうでしょう？ :)

MySQLのLOAD DATA INFILEでint DEFAULT NULL列にcsv上で空列なデータを投入しようとするとワーニングになる件、回避方法がわからないまま、本日は一旦終了。
— atsuizo (@atsuizo) 2018年2月6日

日々の覚書

GA

2018/02/08

LOAD DATA INFILEステートメントの中でカラムの順番とかをゴニョる

TL;DR

0 件のコメント :

コメントを投稿