Loading from Bucket

When data files are stored in an object storage bucket, such as Amazon S3, it is possible to load them directly into TiDB Cloud Lake using the COPY INTO command. 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.

This tutorial uses Amazon S3 bucket as an example and offers a detailed, step-by-step guide to help you effectively navigate the process of loading data from files stored in a bucket.

Tutorial: Loading from Amazon S3 Bucket

Before You Begin

Before you start, make sure you have completed the following tasks:

1. Download and save the sample file books.parquet to a local folder. The file contains two records:

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

2. Create a bucket in Amazon S3 and upload the sample file to the bucket. For how to do that, refer to these links:

• Creating a bucket: https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html
• Uploading objects: https://docs.aws.amazon.com/AmazonS3/latest/userguide/upload-objects.html

For this tutorial, a bucket named lake-toronto was created in the region US East (Ohio) (ID: us-east-2).

Step 1. Create Target Table

Create a table with the following SQL statements in TiDB Cloud Lake:

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

Step 2. Copy Data into Table

1. Load data into the target table with the COPY INTO command:

   COPY INTO books
   FROM 's3://lake-toronto/'
   CONNECTION = (
       ACCESS_KEY_ID = '<your-access-key-id>',
       SECRET_ACCESS_KEY = '<your-secret-access-key>'
   )
   PATTERN = '.*[.]parquet'
   FILE_FORMAT = (
       TYPE = 'PARQUET'
   );
   

2. Check the loaded data:

SELECT * FROM books;

---
title                       |author             |date|
----------------------------+-------------------+----+
Transaction Processing      |Jim Gray           |1992|
Readings in Database Systems|Michael Stonebraker|2004|