Step 2: Connect to an ApsaraDB for SelectDB instance and create a database and a table - ApsaraDB for SelectDB

After you create an ApsaraDB for SelectDB instance, you must connect to the instance before you perform operations, such as creating databases and tables. This topic describes how to use the visualization tool SQL Editor to connect to an SelectDB instance and create databases and tables. The information helps you quickly use SelectDB instances. If you prefer to connect to an SelectDB instance by using another client, such as Data Management (DMS) or a MySQL client, see this topic.

Prerequisites

An SelectDB instance is created. For more information, see Step 1: Create an ApsaraDB for SelectDB instance.

Procedure

Step 1: Change the password for the admin account

When you create an SelectDB instance, the system automatically creates an admin account. Before you use the admin account to log on to the instance, you must change the password for the admin account.

Log on to the ApsaraDB for SelectDB console and go to the Instances page. In the top navigation bar, select the region in which your ApsaraDB for SelectDB instance resides. Then, find the ApsaraDB for SelectDB instance that you want to manage and click its ID.
On the Instance Details page, click Reset Password in the upper-right corner.
In the Reset Password dialog box, enter a new password and click OK.

Step 2: Connect to the ApsaraDB for SelectDB instance

After you complete the preceding step, the system remains on the Instance Details page.

On the Instance Details page, click Data Development and Management in the upper-right corner.
Note
If you use the tools of Data Development and Management for the first time, a message appears and prompts you to add the public IP address of your machine to the IP address whitelist named webui_whitelist of the instance. Read the message and click OK.
Select SQL Editor from the Data Development and Management drop-down list to go to the SQL Editor page.
If the SQL Editor page appears, the connection to the SelectDB instance is successful.
Note
- If you use SQL Editor for the first time and you have not logged on to the WebUI system, you are automatically navigated to the WebUI logon page. You must log on to the WebUI system by using the admin account. After you log on, the SQL Editor page appears.
- If you have used SQL Editor before and you have logged on to the WebUI system, the SQL Editor page appears after you select SQL Editor.

Step 3: Create a database

On the SQL Editor page, enter and execute the following statement:

CREATE DATABASE test_db;

Enter the statement in the SQL editor on the right side of the page.
Click Run (Limit 1000).
If the "Execute Successfully" message appears in the Query Result section in the lower part of the page, a database is created. You can also click the Refresh icon in the upper-right corner of the Data tab in the left-side pane to view the created database.

Step 4: Create a table

On the SQL Editor page, enter and execute the following statement:

CREATE TABLE test_tb
(
    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;

Specify the database and cluster in which you want to execute the statement.
In the top toolbar on the right side of the page, select a database from the second drop-down list and select a cluster from the third drop-down list.
Enter the statement in the SQL editor on the right side of the page.
Click Run (Limit 1000).
If the "Execute Successfully" message appears in the Query Result section in the lower part of the page, a table is created. You can also click the Refresh icon in the upper-right corner of the Data tab in the left-side pane to view the created table.

Step 5: (Optional) Import and query data

SelectDB supports multiple data import methods. In this example, the Stream Load method is used to import data in the CSV format to the test_tb table that is created in the previous step. For more information about Stream Load, see Import data by using Stream Load.

Add your IP address to an IP address whitelist.
In the left-side navigation pane of the Instance Details page, click Data Security. On the Data Security page, click Create Whitelist. In the panel that appears, add the public IP address of your machine to an IP address whitelist of the ApsaraDB for SelectDB instance.
Prepare the data to be imported.
Prepare a CSV file on your machine. For more information, see test_tb.csv.

Import the data.

Open a command-line tool on your machine and run a curl command to initiate a Stream Load job to import the data.

Syntax

curl --location-trusted -u <username>:<password> [-H ""] -H "expect:100-continue" -T <file_name> -XPUT http://<host>:<port>/api/<db
_name>/<table_name>/_stream_load

Parameters

Parameter	Description

Parameter	Description
`--location-trusted`	Specifies that the `username` and `password` are passed to the server to which the request is redirected if authentication is required.
`-u`	The username and password that are used to connect to the ApsaraDB for SelectDB instance.
`-H`	The request header. The following parameters are supported: `label`: the unique identifier of the Stream Load job. `column_separator`: the column delimiter of the file to be imported. Default value: `\t`. You can also use a combination of multiple characters as a column delimiter. If you want to specify a non-printable character as the column delimiter, add `\x` as the prefix and specify the delimiter in hexadecimal.
`-T`	The path of the file to be imported.
`-XPUT`	The method of the HTTP request. In this example, the PUT method is used. You must specify the URL of the ApsaraDB for SelectDB instance into which data is imported. The following parameters are supported: `host`: the virtual private cloud (VPC) endpoint or public endpoint of the ApsaraDB for SelectDB instance. Note If the machine on which you run commands resides in a VPC different from the VPC in which your SelectDB instance resides, you must use the public endpoint. For more information about how to apply for a public endpoint, see Apply for or release a public endpoint. If you run commands in an Alibaba Cloud service that resides in the same VPC as your SelectDB instance, we recommend that you use the VPC endpoint. `port`: the HTTP port number of the ApsaraDB for SelectDB instance. Default value: 8080. Note You can view the endpoint and port number of an ApsaraDB for SelectDB instance on the Instance Details page of the ApsaraDB for SelectDB instance. `db_name`: the name of the database. `table_name`: the name of the table.

Sample code

curl --location-trusted -u admin:07b1**** -T C:\Users\****\Downloads\test_tb.csv -H "label:test123" -H "expect:100-continue" -H"column_separator:," selectdb-cn-vc****-public.selectdbfe.rds.aliyuncs.com:8080/api/test_db/test_tb/_stream_load

View the import results.
On the SQL Editor page, enter and execute the following statement:
```
SELECT * FROM test_tb;
```
Click Run (Limit 1000). If the imported data appears in the Query Result section in the lower part of the page, the sample data is imported.

What to do next

This topic describes how to connect to an SelectDB instance, how to use SQL Editor to create a database and a table, and how to import data by using a Stream Load job. If you want to create databases and tables that meet your business requirements, we recommend that you learn the key points in the database and table design of an SelectDB instance. This helps you quickly select correct database and table properties to prevent adverse effects on your business.

For more information, see Step 3: Learn the key points in database and table design.

References

If you need to use another account to connect to the instance, you can create an account after you log on to the instance by using the admin account, grant the cluster management permissions to the account, and then use the account to reconnect to the instance. For more information about how to create an account and grant permissions to the account, see Manage the basic permissions of users and Manage the permissions to access clusters.
For more information about the SQL syntax of SelectDB, see SQL reference.
Data Development and Management provides visualization tools that are integrated into the WebUI system for SelectDB. SQL Editor is one of the visualization tools. For more information about the tools of Data Development and Management, see Data development and management.
If you want to use other clients to connect to the SelectDB instance, make sure that the database connectors of the clients are compatible with MySQL 5.7. For more information, see the following topics:
- Connect to an ApsaraDB for SelectDB instance by using DMS
- Connect to an ApsaraDB for SelectDB instance by using a MySQL client