@RyuseiSato (SAI)posted at 2022-01-17

Fluentdを利用したツイート収集で日付が表示されない

Q&A

解決したいこと

EC2のAmazon Linux2とRDSのデータベースを使い、Fluentdを経由してMySQLにツイートデータを格納しました。
結果、datetime（日付）が表示されません。解決方法を教えて下さい。

発生している問題・エラー

*************************** 1. row ***************************
        id: 1483824177358516225
   user_id: 935588737437847552
      text: fente eh serio eu vou no show do harry dessa vez e ja se preparem pra asisstor mt live minha
created_at: 0000-00-00 00:00:00
*************************** 2. row ***************************
        id: 1483824177375477760
   user_id: 1447017520590835714
      text: 톨비쉬에게 암흑을 다루는 법을 가르쳐준 사람은 누구일까... ... ... 그러니까 구체적으로 표현하자면 그의 (이하 암기 잡퀘 스포)에 대해 알고 싶다
created_at: 0000-00-00 00:00:00
*************************** 3. row ***************************
        id: 1483824177350344706
   user_id: 1478651379551924224
      text: RT @xBlueSide94: @BTSTrendSongs @BTS_twt • BUTTER FOR SEVEN •
 #BTS #BTS_Butter #Butter  @BTS_twt
created_at: 0000-00-00 00:00:00
*************************** 4. row ***************************
以下省略…

以下がMySQLのサーバ設定です。
Amazon Linux2上で
mysql -h （エンドポイント） -P 3306 -u （ユーザー名） -p
でログインしてテーブルを作成しました。

CREATE DATABASE textdata;
USE textdata;
CREATE TABLE IF NOT EXISTS tweet (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  text TEXT,
  created_at DATETIME,
  KEY id (id),
  KEY idx_user_id (user_id),
  KEY idx_created_at (created_at)
  ) DEFAULT CHARSET=utf8;

Fluentdの設定ファイル
td-agent.confの追記内容です。

<source>
  @type twitter
  consumer_key 〇〇〇〇
  consumer_secret 〇〇〇〇
  access_token 〇〇〇〇
  access_token_secret 〇〇〇〇
  tag input.twitter
  timeline sampling
  lang ja
  output_format flat
</source>

<match input.twitter>
  @type mysql_bulk
  host 〇〇〇〇
  database textdata
  column_names id,user_id,text,created_at
  sql INSERT INTO tweet (id, user_id, text, created_at) VALUES (?, ?, ?, STR_TO_DATE(?, "%H:%i:%s +0000 %Y %b %d"))
  table tweet
  username 〇〇〇〇
  password 〇〇〇〇
  <buffer>
   flush_interval 10s
  </buffer>
</match>

4Answer

@eudika posted at 2022-01-20

STR_TO_DATEが怪しいです。
created_at列をTEXT型にするか、別の列を作るかして、生のタイムスタンプを確認してみてはいかがでしょうか。

0Like

Comments

@RyuseiSato
Questioner
回答ありがとうございます。
created_at列をTEXT型にすると以下のエラーが起こってしまいました。
ERROR 1170 (42000): BLOB/TEXT column 'created_at' used in key specification without a key length
またfluentdの設定ファイルを以下に変更して実行しても変わらなかったです。
sql INSERT INTO tweet (id, user_id, text, created_at) VALUES (?, ?, ?, ?)

すみません、質問なのですが、生のタイムスタンプを確認するにはどうしたら良いでしょうか？

@eudika posted at 2022-01-21

ERROR 1170はメッセージの通りで、TEXTのような不定長の型にインデックスを付ける際の制限に由来するエラーです。
created_atにはすでにインデックスが作られているので、新規列を作った方がよかったですね。

生のタイムスタンプを確認するにはどうしたら良いでしょうか？

すでにDB出力はできているので既述の方法が手軽かと思いましたが、
それ以外ですと、out_fileプラグインあたりが簡単でしょうか。

<match input.twitter>
    @type file
    path /tmp/twitter
</match>

環境がないので試してみることができないのですが、もし日付の形式がAPIリファレンスに記載の通り、
Wed Oct 10 20:19:24 +0000 2018の形式であれば、次で変換できると思います。

STR_TO_DATE(?, '%a %b %d %H:%i:%s +0000 %Y')

~~SELECT STR_TO_DATE(?, '%a %b %d %H:%i:%s +0000 %Y');~~

UTCと定義されているので+0000の部分はハードコードしてます。
日本時間にしたければ、これをさらにDATE_ADDしてください。

0Like

Comments

@RyuseiSato
Questioner
ご指摘の通り、新たにtestという列を作り、datetime型で作成しました。
fluentdの設定ファイルは
sql INSERT INTO tweet (test, id, user_id, text, created_at) VALUES ((STR_TO_DATE(?, '%a %b %d %H:%i:%s +0000 %Y'), ?, ?, ?, STR_TO_DATE(?, '%a %b %d %H:%i:%s +0000 %Y'))に設定し、
以下のコードで実行したのですが、NULLで返されました。

mysql> SELECT STR_TO_DATE(test,'%a %b %d %H:%i:%s +0000 %Y') from tweet LIMIT 10\G
*************************** 1. row ***************************
STR_TO_DATE(test,'%a %b %d %H:%i:%s +0000 %Y'): NULL
*************************** 2. row ***************************
STR_TO_DATE(test,'%a %b %d %H:%i:%s +0000 %Y'): NULL

以下略...

この場合、そもそもTwitterから日付が取得できていないのでしょうか？
@eudika
STR_TO_DATEの例にSELECTが付いていたせいで混乱させてしまった気がします。
（手元で動作確認していたときの名残です）
そうではなく、INSERT文内のSTR_TO_DATEをそっちに書き換えよという意図でした。

新しい回答を加えましたので、まずはそちらを参考にしてください。

@eudika posted at 2022-01-22

私が余計な情報を含ませすぎたのか、意図が伝わっていなかったようです。

対応には、次の順序で当たってください。

生のタイムスタンプを確認し、どのようなフォーマットか、そもそも取得できているかを確認する。
確認したフォーマットに合わせてSTR_TO_DATEの第2引数を調整する。

まず1.単品を試さないと、「そもそもTwitterから日付が取得できているか」は切り分けられません。
DATETIME型の列を作ったり、STR_TO_DATEを云々するのはそれができてからです。

それで、1.のための手段として、次の2つを提示しました。

A. 新しい文字列型の列を用意し、そちらにINSERTする。
B. out_fileなど、別のプラグインを試す。

仮にA.の方針を採る場合、具体的な作業はこうです。

A-1. TEXT型の新しい列を作る。

ALTER TABLE tweet ADD raw_created_at TEXT;

A-2. 新しく作った列にタイムスタンプをINSERTするように、Fluentdの設定を書き変える。

- sql INSERT INTO tweet (id, user_id, text, created_at) VALUES (?, ?, ?, STR_TO_DATE(?, "%H:%i:%s +0000 %Y %b %d"))
+ sql INSERT INTO tweet (id, user_id, text, raw_created_at) VALUES (?, ?, ?, ?)

fluentdの設定ファイルは
sql INSERT INTO tweet (test, id, user_id, text, created_at) VALUES ((STR_TO_DATE(?, '%a %b > %d %H:%i:%s +0000 %Y'), ?, ?, ?, STR_TO_DATE(?, '%a %b %d %H:%i:%s +0000 %Y'))に設定し、

column_namesがid,user_id,text,created_atの4つであるのに対し、
INSERT文のパラメータが5つあるので、そもそもエラーになっていると思います。

まずは、上記の作業を試してみてください。

また、こういった場合は、Fluentdのログ（デフォルトなら/var/log/td-agend/）にエラーが記載されていると思いますので、そちらも参考にしてください。

0Like

Comments

@RyuseiSato
Questioner
すみません理解していなかったようです。ありがとうございます。

A-1で列を作り、余計な列を削除しました。
テーブルですが以下の通りです。
mysql> show columns from tweet;
+----------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------+------+-----+---------+-------+
| id | bigint | NO | MUL | NULL | |
| user_id | bigint | NO | MUL | NULL | |
| text | text | YES | | NULL | |
| raw_created_at | text | YES | | NULL | |
+----------------+--------+------+-----+---------+-------+

column_namesは４つに設定し、A-2で設定を書き換えました。

sudo systemctl restart td-agent.serviceで再起動をかけ実行しました。

結果ですが、NULLのままでした。

mysql> SELECT * FROM tweet LIMIT 10\G
*************************** 1. row ***************************
id: 1484567697434181632
user_id: 72384568
text: Quitar implantes es mi talento oculto
raw_created_at: NULL
*************************** 2. row ***************************
以下略...

Fluentdのログを確認したところ、

2022-01-22 20:28:06 +0900 [warn]: [output_td] Use different plugin for secondary. Check the plugin works with primary like secondary_file primary="Fluent::Plugin::TreasureDataLogOutput" secondary="Fluent::Plugin::FileOutput"

2022-01-22 20:28:06 +0900 [warn]: parameter 'sql' in <match input.twitter>

エラーではないですが、警告文が出ていました。

@eudika posted at 2022-01-24

何度もすみません。

警告メッセージを見てようやく気づいたのですが、お使いのプラグインはmysql_bulkですね。
このプラグインでは**sqlパラメータはサポートされていないようです**。
この設定値は無視されており、単にcolumn_namesで指定した列に、対応する値がINSERTされています。

投稿者様が参考にされた記事では同じ作者のmysqlプラグインを用いていますが、こちらはすでに非推奨となっています。

遠回りさせてしまい申し訳ありません。

対応はいくつか考えられます。

A. DATETIME型を諦めて、文字列として保存する。
B. 古いmysqlプラグインを用いる。
C. mysql_bulkプラグインに与える前に日付の形式を変換する。

A.をやりかけていたので、こちらについて改めて説明します。

テーブル側でcreate_at列の型を変えてしまうのが結果的に簡単です。
（ERROR 1170は回避してください）

あるいはraw_created_atのように、列名がメッセージのキーと異なる場合は、
mysql_bulkではcolumn_namesとkey_namesをそれぞれ指定します。
（前回は、これをしなかったため失敗しました。）

column_names id,user_id,text,raw_created_at
key_names id,user_id,text,created_at

長くなってしまいましたので、B.やC.を試される場合は改めてお尋ねいただければ、可能な限りお力になりたいと思います。

何を試されるにしても、生の出力を見ることは調査に役立ちます。
Fluentdにはデバッグ機能もあるのですが、一番単純なのは次の設定です。

<match input.twitter>
    @type stdout
</match>

これらの行を、既存のinput.twitterへの設定をコメントアウトしたうえで設定ファイルに追加します。
そうすると、Fluentd自体のログに出力されます。

（ファイルを指定したい場合はout fileプラグインを、DBとファイル双方に書きたい場合はcopyプラグインを参照）

0Like

Comments

@RyuseiSato
Questioner
何度もありがとうございます。

前回の続きで

column_names id,user_id,text,raw_created_at
key_names id,user_id,text,created_at

に変更したのですが、以前NULLのままでした。

>テーブル側でcreate_at列の型を変えてしまうのが結果的に簡単です。
>（ERROR 1170は回避してください）

これは現在設定してあるTEXT型ではNULLだったのですが、他でも可能なのでしょうか？

これから以下の記事を参考に単純計算によるテキストマイニングを行いたいのですが、
https://datumstudio.jp/blog/%e3%80%90%e7%89%b9%e5%88%a5%e9%80%a3%e8%bc%89%e3%80%91-%e3%81%95%e3%81%81%e3%80%81%e8%87%aa%e7%84%b6%e8%a8%80%e8%aa%9e%e5%87%a6%e7%90%86%e3%82%92%e5%a7%8b%e3%82%81%e3%82%88%e3%81%86%ef%bc%81-2/

既存のプログラムをあまりいじらないとなると、Bの方が良いでしょうか、、、
@eudika
そうですか……

こちらでもFluentdとMySQLを構築したのですが、sourceからのメッセージ形式が想定通りならば、上記設定でINSERTが行われることを確認しました。
（twitterプラグインは記事の手順とAPI側の認証方式が変わっているようなのでやめました）

次のコマンドを試してください。

echo '{"id": 1000, "user_id": 2000, "text": "Hello", "created_at": "2022-01-02 03:04:05"}' | /opt/td-agent/embedded/bin/fluent-cat input.twitter

これでDBにレコードができなければ、設定の問題である可能性が高いです。

レコードができれば、Twitterプラグイン側のフォーマットの問題なので、生の出力を確認ください。
@RyuseiSato
Questioner
eudikaさん日付取得できました。

MySQLの設定で元々設定していた

KEY idx_created_at (created_at)
を削除し、以下の設定にしました。

CREATE TABLE IF NOT EXISTS tweet (
id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
text TEXT,
raw_created_at TEXT,
KEY id (id),
KEY idx_user_id (user_id)
) DEFAULT CHARSET=utf8;

結果
mysql> SELECT * FROM tweet LIMIT 5\G
*************************** 1. row ***************************
id: 1485944507086835718
user_id: 1042074786745262080
text: #初リプ・初絡み大歓迎 #おやツイ
#藍井エイル

raw_created_at: Tue Jan 25 11:55:52 +0000 2022
*************************** 2. row ***************************
id: 1485944511302107136
user_id: 1177086436782166021
text: やっと帰ったー
raw_created_at: Tue Jan 25 11:55:53 +0000 2022
*************************** 3. row ***************************

出力されました。

これで生の出力を確認することができました。
ありがとうございます。

長くなってしまったので続きの内容は新しく質問を作りたいと思います。
もしよろしければまたご回答いただけるとありがたいです。

Are you sure you want to delete the question?

Fluentdを利用したツイート収集で日付が表示されない

解決したいこと

発生している問題・エラー

4Answer

Comments

Comments

Comments

Comments

Your answer might help someone💌