Tablestore:Configure a Presto server and execute sample SQL statements

Configure the schema in dynamic configuration mode based on a metadata table

• If you use this mode, you do not need to configure a schema before you run the Presto server.

• After you connect PrestoDB to Tablestore and run the SQL CLI, you need to execute theCREATE SCHEMA and CREATE TABLE statements to dynamically create a schema and a mapping table. For more information, see the Create a schema and Create a mapping table sections of this topic.

Configure the schema based on a local static file

The path of the local static file must be the same as the full path that is specified by the tablestore.schema-file parameter in the catalog configuration file.

You can create a file on the Presto server and configure the file based on the following sample code:

{
	"account" : {
		"accessId" : "xxxxxxxx", ----- <Required> The AccessKey ID of your Alibaba Cloud account or a RAM user. 
		"accessKey" : "xxxxxxxxxxxxxxx", ----- <Required> The AccessKey secret of your Alibaba Cloud account or a RAM user. 
	},
	"instances" : {
		"mydb" : { ----- <Required> The name of the schema in PrestoDB. 
			"instanceName" : "myinstance", ----- <Required> The name of the connected Tablestore instance. 
			"endpoint" : "http://myinstance.cn-hangzhou.ots.aliyuncs.com", ----- <Required> The endpoint of the connected Tablestore instance. 
			"tables": {
				"mytable" : { ----- <Required> The name of the table in PrestoDB. 
					"originName" : "SampleTable", ----- <Optional> The name of the Tablestore table that is mapped to the table in PrestoDB. The name is case-sensitive. If you do not specify this parameter, the name of the Tablestore table is the same as the name of the table in Presto. 
					"columns" : [  ----- <Required> The metadata of the table in PrestoDB. The metadata must contain all the primary key columns. The order of the primary key columns in the metadata must be the same as that in the table. 
						{"name" : "gid", "type" : "bigint"},
						{"name" : "uid", "type" : "bigint"},
						{"name" : "c1", "type" : "boolean", "originName" : "col1"},
						{"name" : "c2", "type" : "bigint", "originName" : "col2"},
						{"name" : "C3", "type" : "varchar", "originName" : "col3"}
					]
				},
				"anotherTable" : {
					"originName" : "sampleTable2",
					"columns" : [
						{"name" : "gid", "type" : "bigint"},
						{"name" : "uid", "type" : "bigint"},
						{"name" : "a", "type" : "varchar"},
						{"name" : "b", "type" : "varchar"},
						{"name" : "c", "type" : "boolean"},
						{"name" : "d", "type" : "bigint"},
						{"name" : "e", "type" : "varchar"}
					]
				}
			}
		}
	}
}

Query a list of schemas

Execute the following SQL statement to query a list of schemas:

show schemas;

Sample response:

       Schema       
--------------------
 information_schema 
 testdb             
 testdb1            
(3 rows)

Use a schema

Use a schema to perform subsequent operations on tables in the schema.

The following SQL statement provides an example on how to use the testdb schema.

use testb;

Sample response:

USE

Query the tables in a specific schema

Execute the following SQL statement to query the tables in a used schema:

show tables;

Sample response:

       Schema       
--------------------
 information_schema 
 testdb             
 testdb1            
(3 rows)

View the metadata of a specific table

The following SQL statement provides an example on how to view the metadata of the exampletable table.

describe exampletable;

Sample response:

      Table      
-----------------
 exampletable    
 sampletable     
 sampletabletest 
 table1          
 testtable       
(5 rows)

View the description of a table

The following SQL statement provides an example on how to view the description of the exampletable table.

describe exmapletable;

Sample response:

Column |  Type   | Extra | Comment 
--------+---------+-------+---------
 pk     | varchar |       |         
 c1     | double  |       |         
 c2     | varchar |       |         
 c3     | varchar |       |         
(4 rows)

Delete a mapping table

The following SQL statement provides an example on how to delete the mapping table named table1.

drop table table1;

Sample response:

DROP TABLE

Write data

• Insert a row of data

  The following SQL statement provides an example on how to insert a row of data to the exampletable table.

  insert into exampletable values('101', 22.0, 'Mary', '10002');

  Sample response:

  INSERT: 1 row

• Import multiple data records at a time

  Important

  Before you import multiple data records at a 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 SQL statement provides an example on how to copy the rows whose value of the c1 column is less than 50 from the exampletable table to the sampletable table:

  insert into sampletable select pk, c1, c2, c3 from exampletable where c1 < 50; 

  Sample response:

  INSERT: 3 row

Read data

• Query all data in a table

  The following SQL statement provides an example on how to read data from the exampletable table.

  select * from exampletable;

  Sample response:

  pk | c1 | c2 | c3   
  ----- +------+------+------+-------
   100 | 11.0 | Lily | 10001 
   101 | 22.0 | Mary | 10002  
   102 | 12.0 | Jim | 10003   
  (3 rows)

• Query data that meets specified conditions in a table

  The following sample SQL statement provides an example on how to query the rows whose value of the c1 column is less than 15 and value of the c3 column is equal to 10001 in the exampletable table.

  select * from exampletable where c1 < 15 and c3 = '10001';

  Sample response:

  pk | c1 | c2 | c3   
  ----- +------+------+------+-------
   100 | 11.0 | Lily | 10001 
  (1 row)

Analyze data.

• Calculate the average of values of a specific column in a table

  The following SQL statement provides an example on how to calculate the average of values of the c2 column in the exampletable table.

  select avg(c1) as Average from exampletable;

  Sample response:

  Average 
  ---------
      15.0 
  (1 row)

• Calculate the number of rows in a table

  The following SQL statement provides an example on how to calculate the number of rows in the exampletable table.

  select count(*) as total from exampletable;

  Sample response:

  total 
  -------
       3 
  (1 row)