Skip to main content

Load Data From Local File System

image

This tutorial explains how to use the HTTP API v1/streaming_load to load data from a local file (in the CSV, JSON, NDJSON, or Parquet format) into Databend.

Note: CSV file should be UTF-8 character encoded if you have extra character set.

Before You Begin​

Step 1. Data Files for Loading​

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

Step 2. Create Database and Table​

mysql -h127.0.0.1 -uroot -P3307
CREATE DATABASE book_db;
USE book_db;
CREATE TABLE books
(
    title VARCHAR,
    author VARCHAR,
    date VARCHAR
);

Step 3. Load Data into the Target Tables​

Request
curl -XPUT 'http://root:@127.0.0.1:8081/v1/streaming_load' -H 'insert_sql: insert into book_db.books format CSV' -H 'skip_header: 0' -H 'field_delimiter: ,' -H 'record_delimiter: \n' -F 'upload=@"./books.csv"'
Response
{
  "id": "f4c557d3-f798-4cea-960a-0ba021dd4646",
  "state": "SUCCESS",
  "stats": {
    "rows": 2,
    "bytes": 157
  },
  "error": null
}
tip

  • http://127.0.0.1:8081/v1/streaming_load

    • 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

  • skip_header: Number of lines at the start of the file to skip

  • field_delimiter: One character that separate fields

  • record_delimiter: One character that separate records

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

    • Your books.csv file location

Step 4. Verify the Loaded Data​

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

Step 5. Congratulations!​

You have successfully completed the tutorial.