データをロード

LOAD DATAステートメント バッチは、データを TiDB テーブルにロードします。

TiDB v7.0.0 以降、 LOAD DATA SQL ステートメントは次の機能をサポートします。

• S3およびGCSからのデータのインポートをサポート
• 新しいパラメータFIELDS DEFINED NULL BY追加する

概要

LoadDataStmt ::=
    'LOAD' 'DATA' LocalOpt 'INFILE' stringLit DuplicateOpt 'INTO' 'TABLE' TableName CharsetOpt Fields Lines IgnoreLines ColumnNameOrUserVarListOptWithBrackets LoadDataSetSpecOpt

LocalOpt ::= ('LOCAL')?

Fields ::=
    ('TERMINATED' 'BY' stringLit
    | ('OPTIONALLY')? 'ENCLOSED' 'BY' stringLit
    | 'ESCAPED' 'BY' stringLit
    | 'DEFINED' 'NULL' 'BY' stringLit ('OPTIONALLY' 'ENCLOSED')?)?

パラメータ

LOCAL

LOCALを使用して、インポートするクライアント上のデータ ファイルを指定できます。ファイル パラメーターは、クライアント上のファイル システム パスである必要があります。

TiDB Cloud を使用している場合、 LOAD DATAステートメントを使用してローカル データ ファイルをロードするには、 TiDB Cloudに接続するときに接続文字列に--local-infileオプションを追加する必要があります。

• 以下は、 TiDB Cloud Serverless の接続文字列の例です。

  mysql --connect-timeout 15 -u '<user_name>' -h <host_name> -P 4000 -D test --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/cert.pem -p<your_password> --local-infile
  

• 以下は、 TiDB Cloud Dedicated の接続文字列の例です。

  mysql --connect-timeout 15 --ssl-mode=VERIFY_IDENTITY --ssl-ca=<CA_path> --tls-version="TLSv1.2" -u root -h <host_name> -P 4000 -D test -p<your_password> --local-infile
  

S3 および GCSstorage

LOCAL指定しない場合は、ファイル パラメータは外部storageで詳述されているように、有効な S3 または GCS パスである必要があります。

データ ファイルが S3 または GCS に保存されている場合は、個々のファイルをインポートするか、ワイルドカード文字*を使用してインポートする複数のファイルを一致させることができます。ワイルドカードはサブディレクトリ内のファイルを再帰的に処理しないことに注意してください。次に例をいくつか示します。

• 1つのファイルをインポートする: s3://<bucket-name>/path/to/data/foo.csv
• 指定されたパス内のすべてのファイルをインポート: s3://<bucket-name>/path/to/data/*
• 指定されたパスの下にある.csvで終わるすべてのファイルをインポートします: s3://<bucket-name>/path/to/data/*.csv
• 指定されたパスの下にあるfooで始まるすべてのファイルをインポートします: s3://<bucket-name>/path/to/data/foo*
• 指定されたパスの下にある、先頭がfooで末尾が.csvのすべてのファイルをインポートします: s3://<bucket-name>/path/to/data/foo*.csv

Fields 、 Lines 、 Ignore Lines

FieldsおよびLinesパラメータを使用して、データ形式の処理方法を指定できます。

• FIELDS TERMINATED BY : データ区切り文字を指定します。
• FIELDS ENCLOSED BY : データの囲み文字を指定します。
• LINES TERMINATED BY : 特定の文字で行を終了する場合に、行末文字を指定します。

DEFINED NULL BY使用して、データ ファイル内で NULL 値をどのように表現するかを指定できます。

• MySQL の動作と一致して、 ESCAPED BY null でない場合、たとえばデフォルト値\が使用されると、 \N NULL 値と見なされます。
• DEFINED NULL BY 'my-null'のようにDEFINED NULL BY使用すると、 my-null NULL 値と見なされます。
• DEFINED NULL BY ... OPTIONALLY ENCLOSED使用する場合、 DEFINED NULL BY 'my-null' OPTIONALLY ENCLOSED 、 my-null 、 "my-null" ( ENCLOSED BY '"と想定) は NULL 値と見なされます。
• DEFINED NULL BYやDEFINED NULL BY ... OPTIONALLY ENCLOSED使用せず、 ENCLOSED BY '"'などのENCLOSED BYを使用する場合、 NULL NULL 値と見なされます。この動作は MySQL と一致しています。
• それ以外の場合は、NULL 値とは見なされません。

次のデータ形式を例に挙げます。

"bob","20","street 1"\r\n
"alice","33","street 1"\r\n

bob 、 20 、 street 1を抽出する場合は、フィールド区切り文字を',' 、囲み文字を'\"'に指定します。

FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'

上記のパラメータを指定しない場合、インポートされたデータはデフォルトで次のように処理されます。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

IGNORE <number> LINESパラメータを設定すると、ファイルの最初のnumber行を無視できます。たとえば、 IGNORE 1 LINES設定すると、ファイルの最初の行は無視されます。

例

次の例では、 LOAD DATAを使用してデータをインポートします。フィールド区切り文字としてコンマが指定されています。データを囲む二重引用符は無視されます。ファイルの最初の行は無視されます。

ERROR 1148 (42000): the used command is not allowed with this TiDB version表示された場合は、トラブルシューティングについてはエラー 1148 (42000): 使用されたコマンドはこの TiDB バージョンでは許可されていませんを参照してください。

LOAD DATA LOCAL INFILE '/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv' INTO TABLE trips FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);

Query OK, 815264 rows affected (39.63 sec)
Records: 815264  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA 、 FIELDS ENCLOSED BYおよびFIELDS TERMINATED BYパラメータとして、16 進 ASCII 文字式またはバイナリ ASCII 文字式の使用もサポートします。次の例を参照してください。

LOAD DATA LOCAL INFILE '/mnt/evo970/data-sets/bikeshare-data/2017Q4-capitalbikeshare-tripdata.csv' INTO TABLE trips FIELDS TERMINATED BY x'2c' ENCLOSED BY b'100010' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (duration, start_date, end_date, start_station_number, start_station, end_station_number, end_station, bike_number, member_type);

上記の例では、 x'2c' ,文字の 16 進表現であり、 b'100010' "文字の 2 進表現です。

MySQL 互換性

LOAD DATAステートメントの構文は、解析されても無視される文字セット オプションを除き、MySQL の構文と互換性があります。構文の互換性に違いが見つかった場合は、 バグを報告する実行できます。

注記：

• TiDB v4.0.0 より前のバージョンでは、20000 行ごとにLOAD DATAコミットが行われ、これを構成することはできません。
• TiDB v4.0.0 から v6.6.0 までのバージョンでは、TiDB はデフォルトで 1 つのトランザクションですべての行をコミットします。ただし、固定数の行ごとにLOAD DATAステートメントでコミットする必要がある場合は、必要な行数をtidb_dml_batch_sizeに設定できます。
• TiDB v7.0.0 以降では、 tidb_dml_batch_size LOAD DATAには影響しなくなり、TiDB は 1 つのトランザクションですべての行をコミットします。
• TiDB v4.0.0 以前のバージョンからアップグレードすると、 ERROR 8004 (HY000) at line 1: Transaction is too large, size: 100000058発生する可能性があります。このエラーを解決するには、 tidb.tomlファイルのtxn-total-size-limit値を増やすことをお勧めします。
• トランザクションでコミットされる行数に関係なく、明示的なトランザクションのROLLBACKのステートメントによってLOAD DATAロールバックされることはありません。
• LOAD DATAステートメントは、TiDB トランザクション モードの構成に関係なく、常に楽観的トランザクション モードで実行されます。

参照

• 入れる
• TiDB 楽観的トランザクションモデル
• TiDB 悲観的トランザクションモード