Tablestore:Use PrestoDB to access Tablestore

Step 1: Connect PrestoDB to Tablestore

After you install PrestoDB, you must configure the catalog and schema of PrestoDB to connect PrestoDB to Tablestore. The catalog configuration file is stored in the etc/catalog/tablestore.properties path.

Step 2: Run SQL CLI of Presto

After Tablestore is connected to PrestoDB, you can start the Presto server and execute SQL statements.

1. Obtain the program that can be executed to install the PrestoDB client.

   1. Download the installation package of the PrestoDB client.

      In this example, the presto-cli-0.280-executable.jar package is downloaded.

   2. Save the installation package of the PrestoDB client to the bin directory under the directory in which PrestoDB is installed.

   3. Run the following commands in the bin directory to obtain the program that can be executed to install the PrestoDB client.

      # Rename the installation package of the PrestoDB client to presto. Replace presto-cli-0.280-executable.jar with the actual version name of the PrestoDB client. 
      mv presto-cli-0.280-executable.jar presto
      # Grant the user the permissions to perform operations on the presto file. 
      chmod +x presto

      The following figure shows the directory in which the program that can be executed to install the PrestoDB client is located.

      

2. Run the following commands in the bin directory to start the Presto server.

   Important

   When you start the Presto server, you must separately start the coordinator and at least one worker.

   # You can run the Presto server in foreground or background. If you run the Presto server in foreground, you can view operation logs in an easier way. 
   # Method 1: Run the Presto server in background
   ./launcher start
   # Method 2: Run the Presto server in foreground
   ./launcher run

3. Run the following command in the bin directory to start SQL CLI.

   Note

   • By default, PrestoDB runs on port 8080. If you want to modify the port configuration, change the value of the http-server.http.port parameter in the etc/config.properties file.

   • After you modify specific configurations, you must restart the Presto server for the new configurations to take effect.

   • ./presto in the command indicates the relative path of the program that can be executed to install the PrestoDB client in the bin directory.

   ./presto --server localhost:8080 --catalog tablestore --schema default

   The following table describes the parameters in the command.

   Parameter	Example	Required	Description
   --server	localhost:8080	Yes	The URI of the Presto server. Replace the value with the actual URI of your Presto server. The value of the parameter must be the same as the value of the discovery.uri parameter in the etc/config.properties file in the directory in which PrestoDB is installed.
   --catalog	tablestore	Yes	The name of the catalog configuration file. In this example, the parameter is set to tablestore.
   --schema	default	Yes	The schema configuration. Retain the default configuration.

Step 3: Create a schema and mapping table

If you configure a schema in dynamic configuration mode based on a metadata table, you must manually create and use the schema to configure the Tablestore instance to which PrstoDB is connected and authenticate user permissions. Then, you can create a mapping table for the Tablestore table to query and analyze data.

1. Execute the following statement to create a schema:

   The following sample SQL statement provides an example on how to create a schema named testdb that is used to access the Tablestore instance named myinstance.

   CREATE SCHEMA tablestore.testdb
   WITH (
     endpoint = 'https://myinstance.cn-hangzhou.ots.aliyuncs.com',
     instance_name = 'myinstance',
     access_id = '************************',
     access_key = '********************************'
   );

   The following table describes the parameters.

   Parameter	Example	Required	Description
   endpoint	https://myinstance.cn-hangzhou.ots.aliyuncs.com	Yes	The endpoint of the Tablestore instance. For more information, see Endpoints.
   instance_name	myinstance	Yes	The name of the connected Tablestore instance. For more information, see Instance.
   access_id	************************	Yes	The AccessKey ID of your Alibaba Cloud account or a RAM user.
   access_key	********************************	Yes	The AccessKey secret of your Alibaba Cloud account or a RAM user.

2. Execute the use <SCHEMA_NAME>; statement to use the created schema.

   Replace <SCHEMA_NAME> with the name of the created schema. You can execute the show schemas; statement to view a list of schemas.

3. Execute the following statement to create a mapping table for the Tablestore table.

   Important

   When you create a mapping table, take note of the following items:

   • Make sure that the data types of columns in the mapping table are mapped to those in the Tablestore table. For more information, see the Data type mappings section of the "Access Tablestore by using PrtestoDB" topic.

   • The name of the mapping table must be the same as the actual name of the Tablestore table.

   • The table that is specified by the table_name parameter in the CREATE TABLE statement is mapped to the actual table in Tablestore. You can create multiple mapping tables for the same Tablestore table.

   • The mapping table must contain all primary key columns of the Tablestore table, but only specific attribute columns.

   • The names and order of the primary key columns in the mapping table must be the same as those in the Tablestore table. You can map each attribute column in the mapping table to a column name in the actual Tablestore table by specifying the origin_name parameter.

   In this example, the main_table Tablestore table that contains two primary key columns named gid and uid and three attribute columns named col1, col2, and col3 is used.

   The following sample SQL statement provides an example on how to create a mapping table with the same name as the main_table Tablestore table:

   CREATE TABLE if not exists main_table
   (
      gid bigint,
      uid bigint,
      c1 boolean with (origin_name = 'col1'),
      c2 bigint with (origin_name = 'col2'),
      c3 varchar with (origin_name = 'col3')
   ) WITH (
      table_name = 'main_table'
   );

Step 4: Execute SQL statements to perform operations on data

Execute SQL statements to query schema configurations and metadata of the mapping table, and read data from and write data to the Tablestore table. For more information about sample SQL statements, see the "Common sample SQL statements" section of the Configure a Presto server and execute sample SQL statements topic.

1. Query schema configurations.

   • Query a list of schemas

     show schemas;

   • Query a list of tables in a specific schema

     show tables;

2. Query the metadata of a specific table

   Replace <TABLE_NAME> with the actual name of a table.

   describe <TABLE_NAME>;

3. Perform operations on data.

   Read and write Tablestore data.

   • Write data

     • Insert a row of data

       The following sample SQL statement provides an example on how to insert a row of data into the main_table table:

       insert into main_table values(10001,10001,true,100,'hangzhou');

     • Import multiple data records at the same time

       Important

       Before you import multiple data records at the same time, make sure that a destination table is created and the schema of the destination table is the same as that of the source table.

       The following sample SQL statement provides an example on how to import the gid, uid, c1, c2, and c3 columns of the rows in which the value of the gid column is greater than 0 and the value of the uid column is less than 10000 from the main_table table to the sampletable table:

       insert into sampletable select gid, uid, c1, c2, c3 from main_table where gid > 0 and uid < 100000;

   • Read data

     The following sample SQL statement provides an example on how to query the rows in which the value of the gid column is greater than 0, the value of the uid column is less than 10, and the value of the c1 column is true in the sampletable table:

     select * from sampletable where gid > 0 and uid < 10 and c1 = true;