【AlteryxTips】MySQLに高速書き込みを行う方法

2024年5月28日 2024年5月28日 6分25秒

AkimasaKajitani

MySQLに高速書き込みを行う方法をご紹介します

Alteryx ACEのAkimasaKajitaniです。 AlteryxからMySQLへデータを書き込む際は、通常はデータ出力ツールを用いて行います。データを大量に書き込む場合は、通常バルク保存を行いますが、AlteryxではMySQLに対してバルク保存が用意されていません。

今回は、AlteryxからMySQLに高速に書き込む方法についてご紹介します。

MySQLの高速書き込みオプション

MySQLでは一般的に、通常の書き込み以外に3つの高速書き込みのオプションが存在します。

Bulk Insert
LOAD DATA
mysqlimport

１、２はSQLベースの方法です。３は外部のコマンドラインツールを使った方法です。また、２、３はCSVファイルをそのまま取り込む方法になります。結論として一番扱いやすいのは２のLOAD DATAかと思います。１のBulk Insertは、一度に投入するデータがSQL文の最大長を超えるような場合では分割して実行する必要があるので１度に大量にデータを投入するには若干弱いです。また、思ったほどは早くない、ということがあげられます（それでも、一行ずつINSERT INTOするよりは圧倒的に早いです）。なお、MySQLのSQL文の最大長は以下のコマンドで確認することができます。

show variables like 'max_allowed_packet';

上のコマンド例としては以下のような形になります。

２，３の方法はMySQLのサーバー側のローカルデータのロードオプションをオンにしていないと利用できないのでご注意ください。３はMySQLがインストールされた環境にある「mysqlimport」というコマンドラインツールを持ってこないといけないので、少々不便かと思います。２については、SQLベースなので外部ツールに頼らなくて良いのがGoodポイントです。いずれにしても、どの方法でもAlteryxで利用することが可能です。状況に応じて使い分けていただければと思います。

LOAD DATAをAlteryxでやってみる

今回は、一番使い勝手の良さそうなLOAD DATAを試してみたいと思います。

LOAD DATAは通常のSQL文ですが、以下のような書式となります。

LOAD DATA LOCAL INFILE 'インポート用CSVファイル名'
INTO TABLE テーブル名
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
(カンマ区切りでフィールド名);

実際のサンプルとしては、以下のようになります。ファイルとして「insert_data.csv」というファイル名で「Benchmark3」というテーブルにデータを入れます。このオプションの決め方がなかなか強敵で、投入しようとしているデータに依存する部分があります。色々と試した感じですと、以下のオプションで安定して入るようです。

LOAD DATA LOCAL INFILE 'insert_data.csv'
INTO TABLE Benchmark3
FIELDS 
 TERMINATED BY ','
 ENCLOSED BY '"'
 ESCAPED BY '"'
(Field1,Field2,Field3,Field4,Field5,RowCount);

投入時は、このSQL文とCSVで保存したファイルが必要です。そのため、Alteryx内で以下のような手順を踏む必要があります。

データをCSV形式で保存する
ファイル名、フィールド名を入手し、SQL文にする
SQL文を実行する

１は、データ出力ツールで保存するだけですが、Nullが空白に置き換わってしまうため、あらかじめNullは「NULL」とおきかえておく必要があります。２は、テーブルのメタ情報をフィールド情報ツールで取得し、SQL文に組み立てます（簡単なデータ加工ですね）。３は、PreSQLを使ってSQLを実行します（ここはバッチマクロが必要になります）。実際のワークフローは以下のとおりです。特にSQLの部分が、シングルクォテーションとダブルクォテーション両方使うので少しややこしいです。

SQL構築の部分は少々面倒な計算式となっています。

"LOAD DATA LOCAL INFILE '__insert_data.csv'
INTO TABLE "+[TABLENAME]+"
FIELDS 
 TERMINATED BY ','
 ENCLOSED BY '"+'"'+"'"+"
 ESCAPED BY '"+'"'+"'"+"
"+[Concat_Name]+";"

LOAD DATA LOCAL INFILEマクロは以下のようになります。