Import CSV Files from Cloud Storage into TiDB Cloud Dedicated

This document describes how to import CSV files from Amazon Simple Storage Service (Amazon S3), Google Cloud Storage (GCS), or Azure Blob Storage into TiDB Cloud Dedicated.

Tip

For TiDB Cloud Starter or TiDB Cloud Essential, see Import CSV Files from Cloud Storage into TiDB Cloud Starter or Essential.

Limitations

To ensure data consistency, TiDB Cloud allows importing CSV files into empty tables only. To import data into an existing table that already contains data, you can use TiDB Cloud to import the data into a temporary empty table by following this document, and then use the INSERT SELECT statement to copy the data to the target existing table.
If a TiDB Cloud Dedicated cluster has a changefeed or has Point-in-time Restore enabled, you cannot import data to the cluster (the Import Data button will be disabled) because the current data import feature uses the physical import mode. In this mode, the imported data does not generate change logs, so the changefeed and Point-in-time Restore cannot detect the imported data.

Step 1. Prepare the CSV files

If a CSV file is larger than 256 MiB, consider splitting it into smaller files, each with a size of around 256 MiB.
TiDB Cloud supports importing very large CSV files but performs best with multiple input files around 256 MiB in size. This is because TiDB Cloud can process multiple files in parallel, which can greatly improve the import speed.
Name the CSV files as follows:
- If a CSV file contains all data of an entire table, name the file in the ${db_name}.${table_name}.csv format, which maps to the ${db_name}.${table_name} table when you import the data.
- If the data of one table is separated into multiple CSV files, append a numeric suffix to these CSV files. For example, ${db_name}.${table_name}.000001.csv and ${db_name}.${table_name}.000002.csv. The numeric suffixes can be inconsecutive but must be in ascending order. You also need to add extra zeros before the number to ensure all the suffixes are of the same length.
- TiDB Cloud supports importing compressed files in the following formats: .gzip, .gz, .zstd, .zst, and .snappy. If you want to import compressed CSV files, name the files in the ${db_name}.${table_name}.${suffix}.csv.${compress} format, in which ${suffix} is optional and can be any integer such as '000001'. For example, if you want to import the trips.000001.csv.gz file to the bikeshare.trips table, you need to rename the file as bikeshare.trips.000001.csv.gz.
Note
- You only need to compress the data files, not the database or table schema files.
- To achieve better performance, it is recommended to limit the size of each compressed file to 100 MiB.
- The Snappy compressed file must be in the official Snappy format. Other variants of Snappy compression are not supported.
- For uncompressed files, if you cannot update the CSV filenames according to the preceding rules (for example, the CSV file links are also used by your other programs), you can keep the filenames unchanged and deselect Use TiDB file naming conventions for automatic mapping in the Destination Mapping step of Step 4 to manually map your source files to a single target table.

Step 2. Create the target table schemas

Because CSV files do not contain schema information, before importing data from CSV files into TiDB Cloud, you need to create the table schemas using either of the following methods:

Method 1: In TiDB Cloud, create the target databases and tables for your source data.
Method 2: In the Amazon S3, GCS, or Azure Blob Storage directory where the CSV files are located, create the target table schema files for your source data as follows:
1. Create database schema files for your source data.
  If your CSV files follow the naming rules in Step 1, the database schema files are optional for the data import. Otherwise, the database schema files are mandatory.
  Each database schema file must be in the ${db_name}-schema-create.sql format and contain a CREATE DATABASE DDL statement. With this file, TiDB Cloud will create the ${db_name} database to store your data when you import the data.
  For example, if you create a mydb-schema-create.sql file that contains the following statement, TiDB Cloud will create the mydb database when you import the data.
```
CREATE DATABASE mydb;
```
2. Create table schema files for your source data.
  If you do not include the table schema files in the Amazon S3, GCS, or Azure Blob Storage directory where the CSV files are located, TiDB Cloud will not create the corresponding tables for you when you import the data.
  Each table schema file must be in the ${db_name}.${table_name}-schema.sql format and contain a CREATE TABLE DDL statement. With this file, TiDB Cloud will create the ${db_table} table in the ${db_name} database when you import the data.
  For example, if you create a mydb.mytable-schema.sql file that contains the following statement, TiDB Cloud will create the mytable table in the mydb database when you import the data.
```
CREATE TABLE mytable (
ID INT,
REGION VARCHAR(20),
COUNT INT );
```
  Note
  Each ${db_name}.${table_name}-schema.sql file should only contain a single DDL statement. If the file contains multiple DDL statements, only the first one takes effect.

Step 3. Configure cross-account access

To allow TiDB Cloud to access the CSV files in the Amazon S3 bucket, GCS bucket, or Azure Blob Storage container, do one of the following:

If your CSV files are located in Amazon S3, configure Amazon S3 access.
You can use either an AWS access key or a Role ARN to access your bucket. Once finished, make a note of the access key (including the access key ID and secret access key) or the Role ARN value as you will need it in Step 4.
If your CSV files are located in GCS, configure GCS access.
If your CSV files are located in Azure Blob Storage, configure Azure Blob Storage access.

Step 4. Import CSV files to TiDB Cloud

To import the CSV files to TiDB Cloud, take the following steps:

Open the Import page for your target cluster.
1. Log in to the TiDB Cloud console and navigate to the Clusters page of your project.
  Tip
  You can use the combo box in the upper-left corner to switch between organizations, projects, and clusters.
2. Click the name of your target cluster to go to its overview page, and then click Data > Import in the left navigation pane.
Click Import data from Cloud Storage.
On the Import Data from Cloud Storage page, provide the following information:
- Storage Provider: select Amazon S3.
- Source URI:
  - When importing one file, enter the source file URI in the following format s3://[bucket_name]/[data_source_folder]/[file_name].csv. For example, s3://mybucket/myfolder/TableName.01.csv.
  - When importing multiple files, enter the source folder URI in the following format s3://[bucket_name]/[data_source_folder]/. For example, s3://mybucket/myfolder/.
- Credentials: you can use either an AWS Role ARN or an AWS access key to access your bucket. For more information, see Configure Amazon S3 access.
  - AWS Role ARN (recommended): enter the AWS Role ARN value. If you do not have a Role ARN yet, click Click here to create new one with AWS CloudFormation and follow the instructions on the screen, or expand Having trouble? Create Role ARN manually in the dialog to get the TiDB Cloud Account ID and TiDB Cloud External ID for your cluster, and then create the IAM role manually.
  - AWS Access Key: enter the AWS access key ID and AWS secret access key.
Click Next.
In the Destination Mapping section, specify how source files are mapped to target tables.
When you specify a directory in Source URI, TiDB Cloud selects the Use TiDB file naming conventions for automatic mapping option by default.
Note
When you specify a single file in Source URI, TiDB Cloud does not display the Use TiDB file naming conventions for automatic mapping option and automatically populates the Source field with the file name. In this case, you only need to enter the target database and table for data import.
- To let TiDB Cloud automatically map all source files that follow the TiDB file naming conventions to their corresponding tables, keep this option selected and select CSV as the data format. If your source folder includes schema files (such as ${db_name}-schema-create.sql and ${db_name}.${table_name}-schema.sql), TiDB Cloud uses them to create the target databases and tables when they do not already exist.
- To manually configure the mapping rules to associate your source CSV files with the target database and table, deselect this option, and then fill in the following fields:
  - Source: enter the file name pattern in the [file_name].csv format. For example, TableName.01.csv. You can also use wildcards to match multiple files. TiDB Cloud only supports the * and ? wildcards.
    - my-data?.csv: matches all CSV files that start with my-data followed by a single character, such as my-data1.csv and my-data2.csv.
    - my-data*.csv: matches all CSV files that start with my-data, such as my-data-2023.csv and my-data-final.csv.
  - Target Database and Target Table: enter the target database and table to import the data to.
If necessary, click Edit CSV Configuration to configure the options according to your CSV files. You can set the separator and delimiter characters, specify whether to use backslashes for escaped characters, and specify whether your files contain a header row.
Click Next. TiDB Cloud scans the source files.
Review the scan results, check the data files found and corresponding target tables, and then click Start Import.
When the import progress shows Completed, check the imported tables.

Open the Import page for your target cluster.
1. Log in to the TiDB Cloud console and navigate to the Clusters page of your project.
  Tip
  You can use the combo box in the upper-left corner to switch between organizations, projects, and clusters.
2. Click the name of your target cluster to go to its overview page, and then click Data > Import in the left navigation pane.
Click Import data from Cloud Storage.
On the Import Data from Cloud Storage page, provide the following information:
- Storage Provider: select Google Cloud Storage.
- Source URI:
  - When importing one file, enter the source file URI in the following format gs://[bucket_name]/[data_source_folder]/[file_name].csv. For example, gs://mybucket/myfolder/TableName.01.csv.
  - When importing multiple files, enter the source folder URI in the following format gs://[bucket_name]/[data_source_folder]/. For example, gs://mybucket/myfolder/.
- Google Cloud Service Account ID: TiDB Cloud provides a unique Google Cloud Service Account ID on this page (such as example-service-account@your-project.iam.gserviceaccount.com). Grant this Service Account ID the necessary IAM permissions (such as Storage Object Viewer) on your GCS bucket within your Google Cloud project. For more information, see Configure GCS access.
Click Next.
In the Destination Mapping section, specify how source files are mapped to target tables.
When you specify a directory in Source URI, TiDB Cloud selects the Use TiDB file naming conventions for automatic mapping option by default.
Note
When you specify a single file in Source URI, TiDB Cloud does not display the Use TiDB file naming conventions for automatic mapping option and automatically populates the Source field with the file name. In this case, you only need to enter the target database and table for data import.
- To let TiDB Cloud automatically map all source files that follow the TiDB file naming conventions to their corresponding tables, keep this option selected and select CSV as the data format. If your source folder includes schema files (such as ${db_name}-schema-create.sql and ${db_name}.${table_name}-schema.sql), TiDB Cloud uses them to create the target databases and tables when they do not already exist.
- To manually configure the mapping rules to associate your source CSV files with the target database and table, deselect this option, and then fill in the following fields:
  - Source: enter the file name pattern in the [file_name].csv format. For example, TableName.01.csv. You can also use wildcards to match multiple files. TiDB Cloud only supports the * and ? wildcards.
    - my-data?.csv: matches all CSV files that start with my-data followed by a single character, such as my-data1.csv and my-data2.csv.
    - my-data*.csv: matches all CSV files that start with my-data, such as my-data-2023.csv and my-data-final.csv.
  - Target Database and Target Table: enter the target database and table to import the data to.
If necessary, click Edit CSV Configuration to configure the options according to your CSV files. You can set the separator and delimiter characters, specify whether to use backslashes for escaped characters, and specify whether your files contain a header row.
Click Next. TiDB Cloud scans the source files.
Review the scan results, check the data files found and corresponding target tables, and then click Start Import.
When the import progress shows Completed, check the imported tables.

Open the Import page for your target cluster.
1. Log in to the TiDB Cloud console and navigate to the Clusters page of your project.
  Tip
  You can use the combo box in the upper-left corner to switch between organizations, projects, and clusters.
2. Click the name of your target cluster to go to its overview page, and then click Data > Import in the left navigation pane.
Click Import data from Cloud Storage.
On the Import Data from Cloud Storage page, provide the following information:
- Storage Provider: select Azure Blob Storage.
- Source URI:
  - When importing one file, enter the source file URI in the following format https://[account_name].blob.core.windows.net/[container_name]/[data_source_folder]/[file_name].csv. For example, https://myaccount.blob.core.windows.net/mycontainer/myfolder/TableName.01.csv.
  - When importing multiple files, enter the source folder URI in the following format https://[account_name].blob.core.windows.net/[container_name]/[data_source_folder]/. For example, https://myaccount.blob.core.windows.net/mycontainer/myfolder/.
- Connectivity Method: select how TiDB Cloud connects to your Azure Blob Storage:
  - Public (default): connects over the public internet. Use this option when the storage account allows public network access.
  - Private Link: connects through an Azure private endpoint for network-isolated access. Use this option when the storage account blocks public access or when your security policy requires private connectivity. If you select Private Link, you also need to fill in the additional field Azure Blob Storage Resource ID. To find the resource ID:
    1. Go to the Azure portal.
    2. Navigate to your storage account and click Overview > JSON View.
    3. Copy the value of the id property. The resource ID is in the format /subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.Storage/storageAccounts/<account_name>.
- SAS Token: enter an account SAS token to allow TiDB Cloud to access the source files in your Azure Blob Storage container. If you do not have one yet, click Click here to create a new one with Azure ARM template and follow the instructions on the screen, or manually create an account SAS token. For more information, see Configure Azure Blob Storage access.
Click Next.
If you selected Private Link as the connectivity method, TiDB Cloud creates a private endpoint for your storage account. You need to approve this endpoint request in the Azure portal before the wizard can proceed:
1. Go to the Azure portal and navigate to your storage account.
2. Click Networking > Private endpoint connections.
3. Find the pending connection request from TiDB Cloud and click Approve.
4. Return to the TiDB Cloud console. The import wizard proceeds automatically once the endpoint is approved.
Note
If the endpoint is not yet approved, TiDB Cloud displays a message indicating that the connection is pending approval. Approve the request in the Azure portal and retry.
In the Destination Mapping section, specify how source files are mapped to target tables.
When you specify a directory in Source URI, TiDB Cloud selects the Use TiDB file naming conventions for automatic mapping option by default.
Note
When you specify a single file in Source URI, TiDB Cloud does not display the Use TiDB file naming conventions for automatic mapping option and automatically populates the Source field with the file name. In this case, you only need to enter the target database and table for data import.
- To let TiDB Cloud automatically map all source files that follow the TiDB file naming conventions to their corresponding tables, keep this option selected and select CSV as the data format. If your source folder includes schema files (such as ${db_name}-schema-create.sql and ${db_name}.${table_name}-schema.sql), TiDB Cloud uses them to create the target databases and tables when they do not already exist.
- To manually configure the mapping rules to associate your source CSV files with the target database and table, deselect this option, and then fill in the following fields:
  - Source: enter the file name pattern in the [file_name].csv format. For example, TableName.01.csv. You can also use wildcards to match multiple files. TiDB Cloud only supports the * and ? wildcards.
    - my-data?.csv: matches all CSV files that start with my-data followed by a single character, such as my-data1.csv and my-data2.csv.
    - my-data*.csv: matches all CSV files that start with my-data, such as my-data-2023.csv and my-data-final.csv.
  - Target Database and Target Table: enter the target database and table to import the data to.
If necessary, click Edit CSV Configuration to configure the options according to your CSV files. You can set the separator and delimiter characters, specify whether to use backslashes for escaped characters, and specify whether your files contain a header row.
Click Next. TiDB Cloud scans the source files.
Review the scan results, check the data files found and corresponding target tables, and then click Start Import.
When the import progress shows Completed, check the imported tables.

When you run an import task, if any unsupported or invalid conversions are detected, TiDB Cloud terminates the import job automatically and reports an importing error. You can view details in the Status field.

If you get an importing error, do the following:

Drop the partially imported table.
Check the table schema file. If there are any errors, correct the table schema file.
Check the data types in the CSV files.
Try the import task again.

Troubleshooting

Resolve warnings during data import

If the Pre-check step shows a warning such as can't find the corresponding source files, resolve it by providing the correct source file, renaming the existing one according to Naming Conventions for Data Import, or returning to the Destination Mapping step and switching to manual mapping rules.

After resolving these issues, return to the wizard and run the import again.

Zero rows in the imported tables

After the import progress shows Completed, check the imported tables. If the number of rows is zero, it means no data files matched the source URI that you entered. In this case, resolve this issue by providing the correct source file, renaming the existing one according to Naming Conventions for Data Import, or returning to the Destination Mapping step and switching to manual mapping rules. After that, import those tables again.