Tablestore:Presto Server configuration and SQL examples

Dynamic configuration with a meta table

• No Schema configuration is needed before starting the Presto Server.

• After connecting and starting the SQL CLI, use the create schema and create table commands to create the Schema and mapping tables. For more information, see Create a Schema and Create a mapping table.

Local static file configuration

The path to the local static file must match the full path specified for tablestore.schema-file in the Catalog configuration.

Create a file on the server with the following content:

{
	"account" : {
		"accessId" : "xxxxxxxx", ----- <Required> The AccessKey ID of the Alibaba Cloud account or RAM user.
		"accessKey" : "xxxxxxxxxxxxxxx", ----- <Required> The AccessKey secret of the Alibaba Cloud account or RAM user.
	},
	"instances" : {
		"mydb" : { ----- <Required> The Schema name in PrestoDB.
			"instanceName" : "myinstance", ----- <Required> The name of the Tablestore instance.
			"endpoint" : "http://myinstance.cn-hangzhou.ots.aliyuncs.com", ----- <Required> The endpoint of the Tablestore instance.
			"tables": {
				"mytable" : { ----- <Required> The table name in PrestoDB.
					"originName" : "SampleTable", ----- <Optional> The name of the corresponding table in Tablestore (case-sensitive). If not configured, it is the same as the table name in Presto.
					"columns" : [  ----- <Required> The metadata of the table. It must include all primary key columns, and the order of the primary key columns must be the same as the primary key order of 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"}
					]
				}
			}
		}
	}
}

List all Schemas

show schemas;

Sample response:

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

Use a Schema

Select a Schema before performing operations on its tables.

The following example selects the testdb Schema.

use testdb;

Sample response:

USE

List tables in a Schema

After selecting a Schema, list its tables.

show tables;

Sample response:

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

Describe a table

The following example describes the exampletable table.

describe exampletable;

Sample response:

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

Delete a mapping table

The following example deletes the table1 mapping table.

drop table table1;

Sample response:

DROP TABLE

Write data

• Insert a single row

  The following example inserts a row into exampletable.

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

  Sample response:

  INSERT: 1 row

• Batch import data

  Important

  Before importing data in a batch, make sure the destination table exists and its schema matches the source table.

  The following example copies rows where c1 < 50 from exampletable to sampletable.

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

  Sample response:

  INSERT: 3 rows

Read data

• Query all rows

  The following example reads all data from exampletable.

  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 rows with conditions

  The following example queries rows in exampletable where c1 < 15 and c3 = '10001'.

  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 column average

  The following example calculates the average of the c1 column in exampletable.

  select avg(c1) as Average from exampletable;

  Sample response:

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

• Count total rows

  The following example counts all rows in exampletable.

  select count(*) as total from exampletable;

  Sample response:

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