Skip to content

sql-statement-load-data.md

Latest commit

 

History

History
230 lines (152 loc) · 15.8 KB

sql-statement-load-data.md

File metadata and controls

230 lines (152 loc) · 15.8 KB
Raw
title LOAD DATA | TiDB SQL Statement Reference
summary TiDB データベースの LOAD DATA の使用法の概要。

データをロード {#load-data}

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

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

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

警告：

新しいパラメータFIELDS DEFINED NULL BYと S3 および GCS からのデータインポートのサポートは実験的です。本番環境での使用は推奨されません。この機能は予告なく変更または削除される可能性があります。バグを発見した場合は、GitHub で問題報告してください。

注記：

LOAD DATA INFILEステートメントの場合、 TiDB Cloud Dedicated は Amazon S3 または Google Cloud Storage のLOAD DATA LOCAL INFILE 、 LOAD DATA INFILEサポートしますが、 TiDB CloudスターターとTiDB Cloudエッセンシャル LOAD DATA LOCAL INFILEのみをサポートします。

概要 {#synopsis}

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

LocalOpt ::= ('LOCAL')?

DuplicateOpt ::=
    ( 'IGNORE' | 'REPLACE' )?

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

パラメータ {#parameters}

LOCAL {#code-local-code}

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

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

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

    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

REPLACEとIGNORE {#code-replace-code-and-code-ignore-code}

REPLACEとIGNORE使用して、重複データの処理方法を指定できます。

  • REPLACE : 既存のデータは上書きされます。
  • IGNORE : 重複行は無視され、既存のデータは保持されます。

デフォルトでは、重複したデータはエラーの原因となります。

S3とGCSストレージ {#s3-and-gcs-ストレージ}

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

LOCAL指定しない場合は、 外部ストレージで詳述されているように、ファイル パラメータは有効な 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 {#code-fields-code-code-lines-code-and-code-ignore-lines-code}

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設定すると、ファイルの最初の行が無視されます。

例 {#examples}

次の例では、 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 進表現です。

次の例は、 LOAD DATA INFILEステートメントを使用して Amazon S3 からTiDB Cloud Dedicated クラスターにデータをインポートする方法を示しています。

LOAD DATA INFILE 's3://<your-bucket-name>/your-file.csv?role_arn=<The ARN of the IAM role you created for TiDB Cloud import>&external_id=<TiDB Cloud external ID (optional)>'
INTO TABLE <your-db-name>.<your-table-name>
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

MySQLの互換性 {#mysql-compatibility}

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値を増やすことをお勧めします。
  • TiDB v7.6.0 より前のバージョンでは、トランザクションでコミットされる行数に関係なく、明示的なトランザクションのROLLBACKステートメントによってLOAD DATAロールバックされることはありません。
  • TiDB v7.6.0 より前のバージョンでは、TiDB トランザクション モードの構成に関係なく、 LOAD DATAステートメントは常に楽観的トランザクション モードで実行されます。
  • v7.6.0 以降、TiDB は他の DML ステートメントと同じ方法でLOAD DATA in トランザクションを処理します。
    • LOAD DATAステートメントは、現在のトランザクションをコミットせず、新しいトランザクションを開始しません。
    • LOAD DATAステートメントは、TiDB トランザクション モード設定 (楽観的または悲観的トランザクション) の影響を受けます。
    • トランザクション内のLOAD DATAのステートメントは、トランザクション内のROLLBACKのステートメントによってロールバックできます。

注記：

  • TiDB v4.0.0 より前のバージョンでは、20000 行ごとにLOAD DATAコミットが実行され、これは構成できません。
  • TiDB v4.0.0 から v6.6.0 までのバージョンでは、TiDB はデフォルトですべての行を 1 つのトランザクションでコミットします。ただし、 LOAD DATAステートメントで一定数の行をコミットする必要がある場合は、必要な行数をtidb_dml_batch_sizeに設定できます。
  • 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 Cloudサポート連絡してtxn-total-size-limit値を増やすことができます。
  • TiDB v7.6.0 より前のバージョンでは、トランザクションでコミットされる行数に関係なく、明示的なトランザクションのROLLBACKステートメントによってLOAD DATAロールバックされることはありません。
  • TiDB v7.6.0 より前のバージョンでは、TiDB トランザクション モードの構成に関係なく、 LOAD DATAステートメントは常に楽観的トランザクション モードで実行されます。
  • v7.6.0 以降、TiDB は他の DML ステートメントと同じ方法でLOAD DATA in トランザクションを処理します。
    • LOAD DATAステートメントは、現在のトランザクションをコミットせず、新しいトランザクションを開始しません。
    • LOAD DATAステートメントは、TiDB トランザクション モード設定 (楽観的または悲観的トランザクション) の影響を受けます。
    • トランザクション内のLOAD DATAのステートメントは、トランザクション内のROLLBACKのステートメントによってロールバックできます。

参照 {#see-also}