データを読み込む

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を使用して、インポートするクライアント上のデータ ファイルを指定できます。この場合、file パラメーターはクライアント上のファイル システム パスである必要があります。

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

• 以下は、TiDB サーバーレスの接続文字列の例です。

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

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

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

S3 および GCSstorage

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

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

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

• 単一のファイルをインポートする: 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 'my-null' OPTIONALLY ENCLOSEDなどのDEFINED NULL BY ... 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 、およびstreet 1 20抽出する場合は、フィールド区切り文字を','に、囲み文字を'\"'に指定します。

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 バージョンでは許可されていませんを参照してください。

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 文字式または 2 進 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 つのトランザクションですべての行をコミットします。
• TiDB v4.0.0 以前のバージョンからアップグレードした後、 ERROR 8004 (HY000) at line 1: Transaction is too large, size: 100000058が発生する可能性があります。このエラーを解決する推奨方法は、 tidb.tomlファイルのtxn-total-size-limit値を増やすことです。この制限を増やすことができない場合は、 tidb_dml_batch_sizeから20000に設定することで、アップグレード前の動作を復元することもできます。 v7.0.0 以降、 tidb_dml_batch_size LOAD DATAステートメントに影響しなくなることに注意してください。
• トランザクションでコミットされた行の数に関係なく、明示的なトランザクションのROLLBACKステートメントによってLOAD DATAロールバックされません。
• LOAD DATAステートメントは、TiDB トランザクション モードの構成に関係なく、常に楽観的トランザクション モードで実行されます。

注記：

• TiDB v4.0.0 より前のバージョンでは、20000 行ごとにLOAD DATAコミットされます。
• TiDB v4.0.0 から v6.6.0 までのバージョンの場合、TiDB はデフォルトで 1 つのトランザクションですべての行をコミットします。
• TiDB v7.0.0 以降、バッチでコミットされる行数はLOAD DATAステートメントのWITH batch_size=<number>パラメーターによって制御され、デフォルトではコミットあたり 1000 行になります。
• TiDB v4.0.0 以前のバージョンからアップグレードした後、 ERROR 8004 (HY000) at line 1: Transaction is too large, size: 100000058が発生する可能性があります。このエラーを解決するには、 tidb_dml_batch_sizeを20000に設定することで、アップグレード前の動作を復元できます。
• トランザクションでコミットされた行の数に関係なく、明示的なトランザクションのROLLBACKステートメントによってLOAD DATAロールバックされません。
• LOAD DATAステートメントは、TiDB トランザクション モードの構成に関係なく、常に楽観的トランザクション モードで実行されます。

こちらも参照

• 入れる
• TiDB 楽観的トランザクションモデル
• TiDB ペシミスティックトランザクションモード

• 入れる
• TiDB 楽観的トランザクションモデル
• TiDB ペシミスティックトランザクションモード