Skip to main content

Load Data From Databend Stages

image

Before You Begin​

Step 1. Create Stage Object​

Execute CREATE STAGE to create a named internal stage.

mysql -h127.0.0.1 -uroot -P3307 
CREATE STAGE my_int_stage;
DESC STAGE my_int_stage;
+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+
| name         | stage_type | stage_params                                                                                                                                                | copy_options                                  | file_format_options                                                                                                | comment |
+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+
| my_int_stage | Internal   | StageParams { storage: S3(StageS3Storage { bucket: "", path: "", credentials_aws_key_id: "", credentials_aws_secret_key: "", encryption_master_key: "" }) } | CopyOptions { on_error: None, size_limit: 0 } | FileFormatOptions { format: Csv, skip_header: 0, field_delimiter: ",", record_delimiter: "\n", compression: None } |         |
+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+

Step 2. Stage the Data Files​

Download the sample data file(Choose CSV or Parquet), the file contains two records:

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

Download books.csv

Upload books.csv into stages:

Request /v1/upload_to_stage
curl -H "stage_name:my_int_stage"\
 -F "upload=@./books.csv"\
 -XPUT http://root:@localhost:8081/v1/upload_to_stage
Response
{"id":"50880048-f397-4d32-994c-ce3d38af430f","stage_name":"my_int_stage","state":"SUCCESS","files":["books.csv"]}
tip

  • http://127.0.0.1:8081/v1/upload_to_stage

    • 127.0.0.1 is http_handler_host value in your databend-query.toml
    • 8081 is http_handler_port value in your databend-query.toml

  • -F \"upload=@./books.csv\"

    • Your books.csv file location

Step 3. List the Staged Files (Optional)​

mysql -h127.0.0.1 -uroot -P3307 
LIST @my_int_stage;
+---------------+------+------+-------------------------------+--------------------+
| name          | size | md5  | last_modified                 | creator            |
+---------------+------+------+-------------------------------+--------------------+
| books.csv     |   91 | NULL | 2022-06-10 12:01:40.000 +0000 | 'root'@'127.0.0.1' |
| books.parquet |   91 | NULL | 2022-06-10 12:01:40.000 +0000 | 'root'@'127.0.0.1' |
+---------------+------+------+-------------------------------+--------------------+

Step 4. Creating Database and Table​

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

Step 5. Copy Data into the Target Tables​

Execute COPY to load staged files to the target table.

COPY INTO books FROM @my_int_stage files=('books.csv') file_format = (type = 'CSV' field_delimiter = ','  record_delimiter = '\n' skip_header = 0);
tip

  • files = ( 'file_name' [ , 'file_name' ... ] )

    Specifies a list of one or more files names (separated by commas) to be loaded.

  • file_format
ParametersDescriptionRequired
record_delimiterOne characters that separate records in an input file. Default '\n'Optional
field_delimiterOne characters that separate fields in an input file. Default ','Optional
skip_headerNumber of lines at the start of the file to skip. Default 0Optional

Step 6. Verify the Loaded Data​

SELECT * FROM books;
+------------------------------+----------------------+-------+
| title                        | author               | date  |
+------------------------------+----------------------+-------+
| Transaction Processing       |  Jim Gray            |  1992 |
| Readings in Database Systems |  Michael Stonebraker |  2004 |
+------------------------------+----------------------+-------+

Step 7. Congratulations!​

You have successfully completed the tutorial.