Loading from Remote File

To load data from remote files into TiDB Cloud Lake, the COPY INTO command can be used. This command allows you to copy data from a variety of sources, including remote files, into TiDB Cloud Lake with ease. With COPY INTO, you can specify the source file location, file format, and other relevant parameters to tailor the import process to your needs. Please note that the files must be in a format supported by TiDB Cloud Lake, otherwise the data cannot be imported. For more information on the file formats supported by TiDB Cloud Lake, see Input & Output File Formats.

Loading with Glob Patterns

TiDB Cloud Lake facilitates the loading of data from remote files through the use of glob patterns. These patterns allow for efficient and flexible data import from multiple files that follow a specific naming convention. TiDB Cloud Lake supports the following glob patterns:

Set Pattern

The set pattern in glob expressions enables matching any one of the characters within a set. For example, consider files named data_file_a.csv, data_file_b.csv, and data_file_c.csv. Utilize the set pattern to load data from all three files:

COPY INTO your_table
FROM 'https://your-remote-location/data_file_{a,b,c}.csv' ...

Range Pattern

When dealing with files named data_file_001.csv, data_file_002.csv, and data_file_003.csv, the range pattern becomes useful. Load data from this series of files using the range pattern like this:

COPY INTO your_table
FROM 'https://your-remote-location/data_file_[001-003].csv' ...

Tutorial - Load from a Remote File

This tutorial demonstrates how to import data into TiDB Cloud Lake from a remote CSV file. The sample file books.csv contains two records:

Transaction Processing,Jim Gray,1992
Readings in Database Systems,Michael Stonebraker,2004

Step 1. Create Table

CREATE TABLE books
(
    title VARCHAR,
    author VARCHAR,
    date VARCHAR
);

Step 2. Load Data into Table

COPY INTO books
FROM 'https://lakesql-bin.tidbcloud.com/datasets/books.csv'
FILE_FORMAT = (
    TYPE = 'CSV',
    FIELD_DELIMITER = ',',
    RECORD_DELIMITER = '\n',
    SKIP_HEADER = 0
);

Step 3. Verify Loaded Data

SELECT * FROM books;

┌──────────────────────────────────┬─────────────────────┬───────┐
│ title                            │ author              │ date  │
├──────────────────────────────────┼─────────────────────┼───────┤
│ Transaction Processing           │ Jim Gray            │ 1992  │
│ Readings in Database Systems     │ Michael Stonebraker │ 2004  │
└──────────────────────────────────┴─────────────────────┴───────┘