Use Tablestore with PrestoDB

Step 1: Connect PrestoDB to Tablestore

After you install PrestoDB, you must configure the PrestoDB catalog and schema to connect to Tablestore. The catalog configuration file is located at etc/catalog/tablestore.properties.

Step 2: Run the Presto SQL CLI

After the connection is established, you can start the Presto Server and run SQL statements.

1. Obtain the client executable file.

   1. Download the PrestoDB client.

      This topic uses presto-cli-0.280-executable.jar as an example.

   2. Save the PrestoDB client to the bin directory of the PrestoDB installation folder.

   3. In the bin directory of the PrestoDB installation folder, run the following command to obtain the client executable file.

      # Rename the PrestoDB client file to presto. Replace presto-cli-0.280-executable.jar with the name of your client version.
      mv presto-cli-0.280-executable.jar presto
      # Grant the user permissions to operate the presto file.
      chmod +x presto

      The following figure shows the directory where the client executable file is located.

      

2. In the bin directory of the PrestoDB installation folder, run the command to start the Presto Server.

   Important

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

   # You can start the server in the foreground or background. The foreground mode is more convenient for viewing operational logs.
   # Method 1: Run in the background
   ./launcher start
   # Method 2: Run in the foreground
   ./launcher run

3. In the bin directory of the PrestoDB installation folder, run the following command to start the SQL CLI.

   Note

   • By default, PrestoDB runs on port 8080. To change the port, modify the http-server.http.port setting in the etc/config.properties file.

   • After you modify configuration settings, you must restart the server for the changes to take effect.

   • In the command, ./presto is the relative path of the client executable file in the bin directory of the PrestoDB installation folder.

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

   The following table describes the configuration items.

   Configuration item	Example	Required	Description
   --server	localhost:8080	Yes	The URI of the Presto Server. Replace the value as needed. This setting must be the same as the discovery.uri parameter in the etc/config.properties file in the PrestoDB installation directory.
   --catalog	tablestore	Yes	The name of the catalog configuration file. Set this to `tablestore`, which is the same as the name of the created catalog configuration file.
   --schema	default	Yes	The schema configuration. Keep the default value.

Step 3: Create a schema and a mapping table

When you use the dynamic schema configuration mode with a meta table, you must manually create a schema to configure the connected Tablestore instance and perform user authentication. Then, you must create a mapping table for the Tablestore table to query and analyze data.

1. Run the following command to create a schema.

   The following example shows how to create a Schema named testdb to access the Tablestore instance myinstance.

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

   For a description of the parameters, see the following table.

   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 Tablestore instance. For more information, see Instances.
   access_id	************************	Yes	The AccessKey ID of the Alibaba Cloud account or RAM user.
   access_key	********************************	Yes	The AccessKey secret of the Alibaba Cloud account or RAM user.

2. Run the use <SCHEMA_NAME>; command to use the schema that you created.

   Replace <SCHEMA_NAME> with the name of the schema you created. You can run the show schemas; command to obtain a list of schemas.

3. Run the following command to create a mapping table for the Tablestore data table.

   Important

   Note the following points when you create a mapping table:

   • Make sure the field types in the mapping table match the field types in the Tablestore data table. For more information, see Field type mapping.

   • The mapping table name must match the table name in Tablestore.

   • In the CREATE TABLE SQL statement, the table_name property maps the Presto table to the actual table in Tablestore. You can create multiple mapping tables with different names for the same Tablestore data table.

   • The mapping table must include all primary key columns of the data table, but it can include only some of the attribute columns.

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

   Assume that the data table is named main_table and includes two primary key columns, gid and uid, and three attribute columns, col1, col2, and col3.

   The following SQL example creates a mapping table with the same name for the Tablestore data table main_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: Use SQL to operate on data

You can use SQL statements to query schema configurations and table metadata, and to read and write data in Tablestore tables. For more SQL examples, see Common SQL examples.

1. Query schema configurations.

   • Obtain a list of schemas

     show schemas;

   • Obtain a list of tables in a specified schema

     show tables;

2. View the metadata of a specified table

   Replace <TABLE_NAME> with the actual table name.

   describe <TABLE_NAME>;

3. Data operations.

   Read and write Tablestore data.

   • Write data

     • Insert a row of data

       The following example inserts a row of data into main_table.

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

     • Import data in batches

       Important

       Before you import data in batches, make sure that the destination table is created and its table schema is the same as that of the source data table.

       The following example imports data from the gid, uid, c1, c2, and c3 columns of main_table into sampletable, where gid is greater than 0 and uid is less than 10000.

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

   • Read data

     The following example queries for rows in sampletable where the gid column value is greater than 0, the uid column value is less than 10, and c1 is true.

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