TiDB Lightning Data Sources

TiDB Lightning supports importing data from multiple data sources to TiDB clusters, including CSV, SQL, and Parquet files.

To specify the data source for TiDB Lightning, use the following configuration:

  1. [mydumper]
  2. # Local source data directory or the URI of the external storage such as S3. For more information about the URI of the external storage, see https://docs.pingcap.com/tidb/v6.6/backup-and-restore-storages#uri-format.
  3. data-source-dir = "/data/my_database"

When TiDB Lightning is running, it looks for all files that match the pattern of data-source-dir.

FileTypePattern
Schema fileContains the CREATE TABLE DDL statement${db_name}.${table_name}-schema.sql
Schema fileContains the CREATE DATABASE DDL statement${db_name}-schema-create.sql
Data fileIf the data file contains data for a whole table, the file is imported into a table named ${db_name}.${table_name}${db_name}.${table_name}.${csv|sql|parquet}
Data fileIf the data for a table is split into multiple data files, each data file must be suffixed with a number in its filename${db_name}.${table_name}.001.${csv|sql|parquet}
Compressed fileIf the file contains a compression suffix, such as gzip, snappy, or zstd, TiDB Lightning will decompress the file before importing it. Note that the Snappy compressed file must be in the official Snappy format. Other variants of Snappy compression are not supported.${db_name}.${table_name}.${csv|sql|parquet}.{compress}

TiDB Lightning processes data in parallel as much as possible. Because files must be read in sequence, the data processing concurrency is at the file level (controlled by region-concurrency). Therefore, when the imported file is large, the import performance is poor. It is recommended to limit the size of the imported file to no greater than 256 MiB to achieve the best performance.

Rename databases and tables

TiDB Lightning follows filename patterns to import data to the corresponding database and table. If the database or table names change, you can either rename the files and then import them, or use regular expressions to replace the names online.

Rename files in batch

If you are using Red Hat Linux or a distribution based on Red Hat Linux, you can use the rename command to batch rename files in the data-source-dir directory.

For example:

  1. rename srcdb. tgtdb. *.sql

After you modify the database name, it is recommended that you delete the ${db_name}-schema-create.sql file that contains the CREATE DATABASE DDL statement from the data-source-dir directory. If you want to modify the table name as well, you also need to modify the table name in the ${db_name}.${table_name}-schema.sql file that contains the CREATE TABLE DDL statement.

Use regular expressions to replace names online

To use regular expressions to replace names online, you can use the pattern configuration within [[mydumper.files]] to match filenames, and replace schema and table with your desired names. For more information, see Match customized files.

The following is an example of using regular expressions to replace names online. In this example:

  • The match rule for the data file pattern is ^({schema_regrex})\.({table_regrex})\.({file_serial_regrex})\.(csv|parquet|sql).
  • Specify schema as '$1', which means that the value of the first regular expression schema_regrex remains unchanged. Or specify schema as a string, such as 'tgtdb', which means a fixed target database name.
  • Specify table as '$2', which means that the value of the second regular expression table_regrex remains unchanged. Or specify table as a string, such as 't1', which means a fixed target table name.
  • Specify type as '$3', which means the data file type. You can specify type as either "table-schema" (representing the schema.sql file) or "schema-schema" (representing the schema-create.sql file).
  1. [mydumper]
  2. data-source-dir = "/some-subdir/some-database/"
  3. [[mydumper.files]]
  4. pattern = '^(srcdb)\.(.*?)-schema-create\.sql'
  5. schema = 'tgtdb'
  6. type = "schema-schema"
  7. [[mydumper.files]]
  8. pattern = '^(srcdb)\.(.*?)-schema\.sql'
  9. schema = 'tgtdb'
  10. table = '$2'
  11. type = "table-schema"
  12. [[mydumper.files]]
  13. pattern = '^(srcdb)\.(.*?)\.(?:[0-9]+)\.(csv|parquet|sql)'
  14. schema = 'tgtdb'
  15. table = '$2'
  16. type = '$3'

If you are using gzip to back up data files, you need to configure the compression format accordingly. The matching rule of the data file pattern is '^({schema_regrex})\.({table_regrex})\.({file_serial_regrex})\.(csv|parquet|sql)\.(gz)'. You can specify compression as '$4' to represent the compressed file format. For example:

  1. [mydumper]
  2. data-source-dir = "/some-subdir/some-database/"
  3. [[mydumper.files]]
  4. pattern = '^(srcdb)\.(.*?)-schema-create\.(sql)\.(gz)'
  5. schema = 'tgtdb'
  6. type = "schema-schema"
  7. compression = '$4'
  8. [[mydumper.files]]
  9. pattern = '^(srcdb)\.(.*?)-schema\.(sql)\.(gz)'
  10. schema = 'tgtdb'
  11. table = '$2'
  12. type = "table-schema"
  13. compression = '$4'
  14. [[mydumper.files]]
  15. pattern = '^(srcdb)\.(.*?)\.(?:[0-9]+)\.(sql)\.(gz)'
  16. schema = 'tgtdb'
  17. table = '$2'
  18. type = '$3'
  19. compression = '$4'

CSV

Schema

CSV files are schema-less. To import CSV files into TiDB, you must provide a table schema. You can provide schema by either of the following methods:

  • Create files named ${db_name}.${table_name}-schema.sql and ${db_name}-schema-create.sql that contain DDL statements.
  • Manually create the table schema in TiDB.

Configuration

You can configure the CSV format in the [mydumper.csv] section in the tidb-lightning.toml file. Most settings have a corresponding option in the LOAD DATA statement of MySQL.

  1. [mydumper.csv]
  2. # The field separator. Can be one or multiple characters. The default is ','.
  3. # If the data might contain commas, it is recommended to use '|+|' or other uncommon
  4. # character combinations as a separator.
  5. separator = ','
  6. # Quoting delimiter. Empty value means no quoting.
  7. delimiter = '"'
  8. # Line terminator. Can be one or multiple characters. Empty value (default) means
  9. # both "\n" (LF) and "\r\n" (CRLF) are line terminators.
  10. terminator = ''
  11. # Whether the CSV file contains a header.
  12. # If `header` is true, the first line is skipped and mapped
  13. # to the table columns.
  14. header = true
  15. # Whether the CSV file contains any NULL value.
  16. # If `not-null` is true, all columns from CSV cannot be parsed as NULL.
  17. not-null = false
  18. # When `not-null` is false (that is, CSV can contain NULL),
  19. # fields equal to this value will be treated as NULL.
  20. null = '\N'
  21. # Whether to parse backslash as escape character.
  22. backslash-escape = true
  23. # Whether to treat `separator` as the line terminator and trim all trailing separators.
  24. trim-last-separator = false

If the input of a string field such as separator, delimiter, or terminator involves special characters, you can use a backslash to escape the special characters. The escape sequence must be a double-quoted string ("…"). For example, separator = "\u001f" means using the ASCII character 0X1F as the separator.

You can use single-quoted strings ('…') to suppress backslash escaping. For example, terminator = '\n' means using the two-character string, a backslash (\) followed by the letter n, as the terminator, rather than the LF \n.

For more details, see the TOML v1.0.0 specification.

separator

  • Defines the field separator.

  • Can be one or multiple characters, but must not be empty.

  • Common values:

    • ',' for CSV (comma-separated values).
    • "\t" for TSV (tab-separated values).
    • "\u0001" to use the ASCII character 0x01.
  • Corresponds to the FIELDS TERMINATED BY option in the LOAD DATA statement.

delimiter

  • Defines the delimiter used for quoting.

  • If delimiter is empty, all fields are unquoted.

  • Common values:

    • '"' quotes fields with double-quote. The same as RFC 4180.
    • '' disables quoting.
  • Corresponds to the FIELDS ENCLOSED BY option in the LOAD DATA statement.

terminator

  • Defines the line terminator.
  • If terminator is empty, both "\n" (Line Feed) and "\r\n" (Carriage Return + Line Feed) are used as the line terminator.
  • Corresponds to the LINES TERMINATED BY option in the LOAD DATA statement.

header

  • Whether all CSV files contain a header row.
  • If header is true, the first row is used as the column names. If header is false, the first row is treated as an ordinary data row.

not-null and null

  • The not-null setting controls whether all fields are non-nullable.

  • If not-null is false, the string specified by null is transformed to the SQL NULL instead of a specific value.

  • Quoting does not affect whether a field is null.

    For example, in the following CSV file:

    1. A,B,C
    2. \N,"\N",

    In the default settings (not-null = false; null = '\N'), the columns A and B are both converted to NULL after being imported to TiDB. The column C is an empty string '' but not NULL.

backslash-escape

  • Whether to parse backslash inside fields as escape characters.

  • If backslash-escape is true, the following sequences are recognized and converted:

    SequenceConverted to
    \0Null character (U+0000)
    \bBackspace (U+0008)
    \nLine feed (U+000A)
    \rCarriage return (U+000D)
    \tTab (U+0009)
    \ZWindows EOF (U+001A)

    In all other cases (for example, \"), the backslash is stripped, leaving the next character (") in the field. The character left has no special roles (for example, delimiters) and is just an ordinary character.

  • Quoting does not affect whether backslash is parsed as an escape character.

  • Corresponds to the FIELDS ESCAPED BY '\' option in the LOAD DATA statement.

trim-last-separator

  • Whether to treat separator as the line terminator and trim all trailing separators.

    For example, in the following CSV file:

    1. A,,B,,
    • When trim-last-separator = false, this is interpreted as a row of 5 fields ('A', '', 'B', '', '').
    • When trim-last-separator = true, this is interpreted as a row of 3 fields ('A', '', 'B').

  • This option is deprecated. Use the terminator option instead.

    If your existing configuration is:

    1. separator = ','
    2. trim-last-separator = true

    It is recommended to change the configuration to:

    1. separator = ','
    2. terminator = ",\n" # Use ",\n" or ",'\r\n" according to your actual file.

Non-configurable options

TiDB Lightning does not support every option supported by the LOAD DATA statement. For example:

  • There cannot be line prefixes (LINES STARTING BY).
  • The header cannot be skipped (IGNORE n LINES) and must be valid column names.

Strict format

TiDB Lightning works best when the input files have a uniform size of around 256 MiB. When the input is a single huge CSV file, TiDB Lightning can only process the file in one thread, which slows down the import speed.

This can be fixed by splitting the CSV into multiple files first. For the generic CSV format, there is no way to quickly identify where a row starts or ends without reading the whole file. Therefore, TiDB Lightning by default does not automatically split a CSV file. However, if you are certain that the CSV input adheres to certain restrictions, you can enable the strict-format setting to allow TiDB Lightning to split the file into multiple 256 MiB-sized chunks for parallel processing.

  1. [mydumper]
  2. strict-format = true

In a strict CSV file, every field occupies only a single line. In other words, one of the following must be true:

  • Delimiter is empty.
  • Every field does not contain the terminator itself. In the default configuration, this means every field does not contain CR (\r) or LF (\n).

If a CSV file is not strict, but strict-format is wrongly set to true, a field spanning multiple lines may be cut in half into two chunks, causing parse failure, or even quietly importing corrupted data.

Common configuration examples

CSV

The default setting is already tuned for CSV following RFC 4180.

  1. [mydumper.csv]
  2. separator = ',' # If the data might contain a comma (','), it is recommended to use '|+|' or other uncommon character combinations as the separator.
  3. delimiter = '"'
  4. header = true
  5. not-null = false
  6. null = '\N'
  7. backslash-escape = true

Example content:

  1. ID,Region,Count
  2. 1,"East",32
  3. 2,"South",\N
  4. 3,"West",10
  5. 4,"North",39

TSV

  1. [mydumper.csv]
  2. separator = "\t"
  3. delimiter = ''
  4. header = true
  5. not-null = false
  6. null = 'NULL'
  7. backslash-escape = false

Example content:

  1. ID    Region    Count
  2. 1     East      32
  3. 2     South     NULL
  4. 3     West      10
  5. 4     North     39

TPC-H DBGEN

  1. [mydumper.csv]
  2. separator = '|'
  3. delimiter = ''
  4. terminator = "|\n"
  5. header = false
  6. not-null = true
  7. backslash-escape = false

Example content:

  1. 1|East|32|
  2. 2|South|0|
  3. 3|West|10|
  4. 4|North|39|

SQL

When TiDB Lightning processes a SQL file, because TiDB Lightning cannot quickly split a single SQL file, it cannot improve the import speed of a single file by increasing concurrency. Therefore, when you import data from SQL files, avoid a single huge SQL file. TiDB Lightning works best when the input files have a uniform size of around 256 MiB.

Parquet

TiDB Lightning currently only supports Parquet files generated by Amazon Aurora or Apache Hive. To identify the file structure in S3, use the following configuration to match all data files:

  1. [[mydumper.files]]
  2. # The expression needed for parsing Amazon Aurora parquet files
  3. pattern = '(?i)^(?:[^/]*/)*([a-z0-9\-_]+).([a-z0-9\-_]+)/(?:[^/]*/)*(?:[a-z0-9\-_.]+\.(parquet))$'
  4. schema = '$1'
  5. table = '$2'
  6. type = '$3'

Note that this configuration only shows how to match the parquet files exported by Aurora snapshot. You need to export and process the schema file separately.

For more information on mydumper.files, refer to Match customized file.

Compressed files

TiDB Lightning currently supports compressed files exported by Dumpling or compressed files that follow the naming rules. Currently, TiDB Lightning supports the following compression algorithms: gzip, snappy, and zstd. When the file name follows the naming rules, TiDB Lightning automatically identifies the compression algorithm and imports the file after streaming decompression, without additional configuration.

Import data from Amazon S3 - 图1

Note

  • Because TiDB Lightning cannot concurrently decompress a single large compressed file, the size of the compressed file affects the import speed. It is recommended that a source file is no greater than 256 MiB after decompression.
  • TiDB Lightning only imports individually compressed data files and does not support importing a single compressed file with multiple data files included.
  • TiDB Lightning does not support parquet files compressed through another compression tool, such as db.table.parquet.snappy. If you want to compress parquet files, you can configure the compression format for the parquet file writer.
  • TiDB Lightning v6.4.0 and later versions only support the following compressed data files: gzip, snappy, and zstd. Other types of files cause errors. If an unsupported compressed file exists in the directory where the source data file is stored, this will cause the task to report an error. You can move those unsupported files out of the import data directory to avoid such errors.
  • The Snappy compressed file must be in the official Snappy format. Other variants of Snappy compression are not supported.

Match customized files

TiDB Lightning only recognizes data files that follow the naming pattern. In some cases, your data file might not follow the naming pattern, and thus data import is completed in a short time without importing any file.

To resolve this issue, you can use [[mydumper.files]] to match data files in your customized expression.

Take the Aurora snapshot exported to S3 as an example. The complete path of the Parquet file is S3://some-bucket/some-subdir/some-database/some-database.some-table/part-00000-c5a881bb-58ff-4ee6-1111-b41ecff340a3-c000.gz.parquet.

Usually, data-source-dir is set to S3://some-bucket/some-subdir/some-database/ to import the some-database database.

Based on the preceding Parquet file path, you can write a regular expression like (?i)^(?:[^/]*/)*([a-z0-9\-_]+).([a-z0-9\-_]+)/(?:[^/]*/)*(?:[a-z0-9\-_.]+\.(parquet))$ to match the files. In the match group, index=1 is some-database, index=2 is some-table, and index=3 is parquet.

You can write the configuration file according to the regular expression and the corresponding index so that TiDB Lightning can recognize the data files that do not follow the default naming convention. For example:

  1. [[mydumper.files]]
  2. # The expression needed for parsing the Amazon Aurora parquet file
  3. pattern = '(?i)^(?:[^/]*/)*([a-z0-9\-_]+).([a-z0-9\-_]+)/(?:[^/]*/)*(?:[a-z0-9\-_.]+\.(parquet))$'
  4. schema = '$1'
  5. table = '$2'
  6. type = '$3'
  • schema: The name of the target database. The value can be:
    • The group index obtained by using a regular expression, such as $1.
    • The name of the database that you want to import, such as db1. All matched files are imported into db1.
  • table: The name of the target table. The value can be:
    • The group index obtained by using a regular expression, such as $2.
    • The name of the table that you want to import, such as table1. All matched files are imported into table1.
  • type: The file type. Supports sql, parquet, and csv. The value can be:
    • The group index obtained by using a regular expression, such as $3.
  • key: The file number, such as 001 in ${db_name}.${table_name}.001.csv.
    • The group index obtained by using a regular expression, such as $4.

Import data from Amazon S3

The following examples show how to import data from Amazon S3 using TiDB Lightning. For more parameter configurations, see URI Formats of External Storage Services.

  • Use the locally configured permissions to access S3 data:

    1. tiup tidb-lightning --tidb-port=4000 --pd-urls=127.0.0.1:2379 --backend=local --sorted-kv-dir=/tmp/sorted-kvs \
    2.     -d 's3://my-bucket/sql-backup'

  • Use the path-style request to access S3 data:

    1. tiup tidb-lightning --tidb-port=4000 --pd-urls=127.0.0.1:2379 --backend=local --sorted-kv-dir=/tmp/sorted-kvs \
    2.     -d 's3://my-bucket/sql-backup?force-path-style=true&endpoint=http://10.154.10.132:8088'

  • Use a specific AWS IAM role ARN to access S3 data:

    1. tiup tidb-lightning --tidb-port=4000 --pd-urls=127.0.0.1:2379 --backend=local --sorted-kv-dir=/tmp/sorted-kvs \
    2.     -d 's3://my-bucket/test-data?role-arn=arn:aws:iam::888888888888:role/my-role'

  • Use access keys of an AWS IAM user to access S3 data:

    1. tiup tidb-lightning --tidb-port=4000 --pd-urls=127.0.0.1:2379 --backend=local --sorted-kv-dir=/tmp/sorted-kvs \
    2.     -d 's3://my-bucket/test-data?access_key={my_access_key}&secret_access_key={my_secret_access_key}'

  • Use the combination of AWS IAM role access keys and session tokens to access S3 data:

    1. tiup tidb-lightning --tidb-port=4000 --pd-urls=127.0.0.1:2379 --backend=local --sorted-kv-dir=/tmp/sorted-kvs \
    2.     -d 's3://my-bucket/test-data?access_key={my_access_key}&secret_access_key={my_secret_access_key}&session-token={my_session_token}'

More resources