Create a database and write data

Prerequisites

You are connected to your ApsaraDB for SelectDB instance by using the MySQL protocol. For more information, see Connect to an instance.

Usage notes

• Each ApsaraDB for SelectDB instance supports up to 256 databases.

• ApsaraDB for SelectDB is compatible with standard SQL syntax. For details, see the SQL reference.

Procedure (CLI)

1. Run the CREATE DATABASE statement to create a database.

   CREATE DATABASE test_db;

2. Create a table named test_table in the test_db database:

   USE test_db;
   
   CREATE TABLE test_table
   (
       k1 TINYINT,
       k2 DECIMAL(10, 2) DEFAULT "10.05",
       k3 CHAR(10) COMMENT "string column",
       k4 INT NOT NULL DEFAULT "1" COMMENT "int column"
   )
   COMMENT "my first table"
   DISTRIBUTED BY HASH(k1) BUCKETS 16;

3. Save the following sample data to a local file named data.csv.

   1,0.14,a1,20
   2,1.04,b2,21
   3,3.14,c3,22
   4,4.35,d4,23

4. Load data into the test_table table by using Stream Load, which sends import requests over HTTP. This example uses the curl command to submit a request. For more information, see Stream Load.

   curl --location-trusted -u admin:admin_123 -H "label:123" -H "column_separator:," -T data.csv http://<host>:<port>/api/test_db/test_table/_stream_load

   Parameters:

   host: The VPC endpoint or public endpoint of the ApsaraDB for SelectDB instance. To apply for a public endpoint, see Apply for or release a public endpoint.

   port: The HTTP port number of the ApsaraDB for SelectDB instance.

   Note

   The endpoint and HTTP port number for your ApsaraDB for SelectDB instance are available on the instance details page.

5. Query data in the test_table table:

   SELECT * FROM test_table;

   The query returns the following result:

   +------+------+------+------+
   | k1   | k2   | k3   | k4   |
   +------+------+------+------+
   |    1 | 0.14 | a1   |   20 |
   |    2 | 1.04 | b2   |   21 |
   |    3 | 3.14 | c3   |   22 |
   |    4 | 4.35 | d4   |   23 |
   +------+------+------+------+