データテキストインポートMySQLまとめ

シーンの説明
最近の仕事でmysqlにデータを導く必要があることに遭遇しました.データはtxtファイル形式で与えられ、約80 Gのサイズがあり、統計すると3千万行以上、つまり表に3千万件以上のデータが導入されます.具体的にはどうすればいいですか?プログラムを1行1行書いてテキストを読み、SQL文をつなぎ合わせてデータベースに挿入(一括挿入)するのは時間がかかりすぎて面倒だと否定した.テキストからmysqlに直接データを渡すツールはありますか?mysqlクライアントには、インポートツールmysqlimportが用意されており、mysql自体がLOAD DATA SQL文をサポートしています.最終的にmysqlのLOAD DATAコマンドを選択してこれらのデータをインポートしました.このシーンでは、mysqlテキストインポートの使い方について説明します.
テキストプリプロセッシング
データテキストは'01'で区切られた1行の文字列で構成されています.ここの'01'をmysqlテキストインポートでサポートされている区切り文字に置き換える必要があります.ここでは'tt'に置き換え、テキストディレクトリの下でコマンドを実行します.

sed -i 's/\x01/\t\t/g' ./*

sedの実行速度はまだ速く、千六百万行のテキストは5分ほどで置き換えられます.置換されたテキストは次のとおりです.データは前処理され、インポートに使用できます.
データのインポート
前述したように、mysqlのコマンドを選択してテキストデータをインポートします.まずmysqlのデータインポート構文を見てみましょう.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

パラメータを具体的に説明します.

file_name file_nameは、ローカルでインポートするテキストデータ

である必要があります.

CHARACTER SET CHARACTER SETインポートしたファイルの文字フォーマットを指定する

FIELDS|COLUMNSデータのインポート時にインポートしたテキストを分割する必要がある場合は、FIELDTERMINTED BY','構文を追加する必要があります.

IGNORE number[LINES|ROWS]IGNOREの後に数字を付けることは、データのインポート時にテキストを無視する最初の行を示す.

(col_name1,col_name2...)フィールドリストの各行のテキストは1つのフィールドに区切られていますが、これらのデータをインポートするときに、各フィールドがテーブルのどのフィールドに対応するかは、ここで指定します.データテキストのフィールドは、そのフィールドリストで指定されたフィールドに順番にインポートされます.テキストのフィールドがmysqlテーブルのフィールドよりも多い場合、または各行のデータをインポートしたいフィールドがある場合は、次のように、フィールドリストにテキストをインポートするフィールドの順序でユーザー変数を追加できます.

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

これは、インポートされたテキストの一部のフィールド値を直接破棄することに相当します.

SET col_name=exprテーブルの一部のフィールドが特定の値であることを望む場合は、ここで直接設定できます.

具体的なインポートsql文は次のとおりです.

LOAD DATA  INFILE '/opt/data/backup/xx_01_xx  
INTO TABLE db_abc.t_info_xxx CHARACTER SET utf8  FIELDS TERMINATED BY '\t\t'  IGNORE 1  LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,
@dummycolb,@dummycolc,col12,col13,@dummycold,col14,col15,
@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,
col21,col22,col23,col24,col25,col26,col27);

まず一部のデータテストを取り、百六十万行のデータを導入するのに2 minぐらいかかりましたが、速度はまあまあです.実行後、sqlコマンドSHOW WARNINGSでデータインポート中の警告を確認することができます.私のインポートでは、フィールドの幅が足りない場合があります.調整、テストを通じて、テーブル構造に間違いがないことを保証し、正式にこれらのデータをインポートすることができます.
私たちのこれらのデータは10以上のテキストから構成されているため、mysqlクライアントコマンドラインで各ファイルのインポートを実行するのは面倒で、スクリプトを書いて実行するのは便利です.私たちのスクリプトは以下の通りです.

#!/bin/bash

mysql=/usr/bin/mysql
host="192.168.0.1"
port=1234
username=xxx
passwd="xxx"
dbname=db_abc
tbname=t_info_xxx

time0=`date +%s`
echo "------------------start to import data into mysql...-----------------------"
echo -e "
"
for i in `seq 1 12`
do
    num=`printf "%.2d" $i`
    filename="xxx_${num}_xxx"
    echo "import file:${filename}..."
    time1=`date +%s`
    mysql -h${host} -P${port} -u${username} -p${passwd} -e "
use ${dbname};
LOAD DATA LOCAL INFILE '/opt/data/backup/${filename}'  INTO TABLE ${dbname}.${tbname} CHARACTER SET utf8 
FIELDS TERMINATED BY '\t\t'  IGNORE 1 LINES
(@dummycola,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,@dummycolb,
@dummycolc,col12,col13,@dummycold,col14,col15,@dummycole,col16,col17,col18,col19,@dummycolf,col19,col20,col21,col22,col23,col24,col25,col26,col27);
quit
"
    echo "import file:${filename} success!"
    time2=`date +%s`
    comp1=`expr $time2 - $time1`
    echo "import file:${filename} has cost ${comp1} seconds!"
    echo -e "
"
done
timen=`date +%s`
comp2=`expr $timen - $time0`
echo "The whole data import cost ${comp2} seconds!"
echo -e "
"
echo "-----------------import data into mysql end!-----------------------"

注意:

は社内データであるため、具体的なフィールド名、データベース名などが処理されているが、このスクリプトの全体構造は同じである.

最後のsql文のフィールドリストには、テキストに多く出てくるフィールドを解決するための@dummycolx列があります.これらのユーザー変数は、データベース・テーブルに対応するフィールドがないか、テキストのフィールドが必要ありません.これらの@dummycolx列をここに置くと、これらのフィールドを破棄し、必要なフィールドだけをインポートすることに相当します.

最終的に2000万件のデータを導入し、合計10分以上かかりました.

さらに
mysqlimportツールは使いやすく、実際にmysqlimportツールの内部にはLOAD DATA INFILE文がパッケージされています.使用する場合は、具体的にはこちらを参考にしてください:mysqlimport
リファレンス

LOAD DATA INFILE Syntax