全部产品
Search
文档中心

表格存储:Presto Server配置和SQL示例

更新时间:Jan 29, 2024

配置PrestoDB对接Tablestore时,您需要根据实际进行Presto Server的Catalog配置和Schema配置。对接完成后,您可以参考常见SQL示例进行数据操作。

Server配置

根据需要对Presto Server进行Catalog配置和Schema配置。

  • Catalog配置主要包括连接器、Schema配置模式等配置。其中Schema配置模式包括本地静态文件配置和Meta表动态配置两种方式。

  • Schema配置主要包括本地静态文件配置。当使用Meta表动态配置的Schema配置模式时,在启动Presto Server前无需进行Schema配置,只需要在运行SQL CLI后进行Schema和映射表的创建。

Catalog配置

Catalog配置文件中包括连接器、Schema配置等配置。配置文件保存路径为PrestoDB安装目录下的etc/catalog/tablestore.properties

Catalog配置文件的配置示例如下:

connector.name=tablestore
tablestore.schema-mode=meta-table
#tablestore.schema-mode=file
tablestore.schema-file=/users/test/tablestore/presto/tablestore.schema
tablestore.meta-instance=metastoreinstance
tablestore.endpoint=http://metastoreinstance.cn-hangzhou,ots.aliyuncs.com/
tablestore.accessid=****************
tablestore.accesskey=**************************
tablestore.meta-table=meta_table
tablestore.auto-create-meta-table=true

具体配置项说明请参见下表。

配置项

示例

是否必选

说明

connector.name

tablestore

连接器名称。此项必须配置tablestore。

tablestore.schema-mode

meta-table

Schema模式配置。取值范围如下:

  • meta-table:Meta表动态配置方式,需要配置用于存储Schema的Metastore信息。

  • file:本地静态文件配置方式,需要配置本地静态文件完整路径和本地静态文件。

tablestore.schema-file

/users/test/tablestore/presto/tablestore.schema

是,有条件

只有配置当tablestore.schema-modefile时才需要配置此项。

本地静态文件完整路径。具体静态文件配置请参见Schema配置

tablestore.meta-instance

metastoreinstance

是,有条件

只有当配置tablestore.schema-modemeta-table时才需要配置此项。

Tablestore中用于存储元数据的实例,请根据实际修改。更多信息,请参见实例

重要

请确保配置的实例已在阿里云账号中存在。

tablestore.endpoint

http://metastoreinstance.cn-hangzhou,ots.aliyuncs.com/

是,有条件

只有当配置tablestore.schema-modemeta-table时才需要配置此项。

Tablestore中用于存储元数据的实例的访问地址,请修改实际修改。更多信息,请参见服务地址

tablestore.accessid

****************

是,有条件

只有当配置tablestore.schema-modemeta-table时才需要配置此项。

具有存储元数据的实例访问权限的用户AccessKey ID和AccessKey Secret。

tablestore.accesskey

**************************

是,有条件

tablestore.meta-table

meta_table

是,有条件

只有当配置tablestore.schema-modemeta-table时才需要配置此项。

Tablestore中用于存储元数据的表名,请根据实际修改。

tablestore.auto-create-meta-table

true

只有当配置tablestore.schema-modemeta-table时才需要配置此项。

是否需要自动创建元数据表。默认值为true,表示创建Schema时会自动创建元数据表。

Schema配置

Schema配置模式支持本地静态文件配置和Meta表动态配置两种配置方式。推荐使用Meta表动态配置方式进行Schema配置。

Meta表动态配置

  • 运行Presto Server前,无需配置任何Schema信息。

  • 完成对接并运行SQL CLI后,需要通过create schemacreate table来动态创建Schema和映射表。更多信息,请参见创建Schema创建映射表

本地静态文件配置

本地静态文件所在的路径必须与Catalog配置中的tablestore.schema-file指定的文件完整路径一致。

您可以在服务器中创建一个文件,在文件中进行如下配置:

{
	"account" : {
		"accessId" : "xxxxxxxx", ----- <必选>阿里云账号或RAM用户的AccessKey ID。
		"accessKey" : "xxxxxxxxxxxxxxx", ----- <必选>阿里云账号或RAM用户的AccessKey Secret。
	},
	"instances" : {
		"mydb" : { ----- <必选>PrestoDB中Schema名称。
			"instanceName" : "myinstance", ----- <必选>Tablestore实例的名称。
			"endpoint" : "http://myinstance.cn-hangzhou.ots.aliyuncs.com", ----- <必选>Tablestore实例的Endpoint。
			"tables": {
				"mytable" : { ----- <必选>PrestoDB中表的名称。
					"originName" : "SampleTable", ----- <可选>对应tablestore中表的名称(注意大小写),如果不配置,则与Presto中表名一致。
					"columns" : [  ----- <必选> 表的元数据,必须包含所有的主键列,且主键列顺序必须与表的主键顺序一致。
						{"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"}
					]
				}
			}
		}
	}
}

配置项

是否必选

说明

account

accessId

阿里云账号或者RAM用户的AccessKey ID和AccessKey Secret

accessKey

instances(map(<schema_name> -> <schema_info>))

包含Schema名称和Schema信息的JSON Map配置。

重要

Schema名称在PrestoDB中大小写不敏感。

<schema_info>

instanceName

Schema对应到Tablestore中实际的实例名称。更多信息,请参见实例

endpoint

Tablestore实例的服务地址。更多信息,请参见服务地址

tables(map(<table_name> -> <table_info>))

挂载到PrestoDB中的表列表。

重要

<table_name>在PrestoDB中大小写不敏感。

<table_info>

originName

对应到Tablestore中实际的表名称。

  • 如果未配置此参数,则connector会自动在Tablestore内找到与映射表名对应的Tablestore实际表名称。

  • 如果配置了此参数,则connector会以配置的实际表名称为准。

如果connector在Tablestore中未找到对应的表,则无法对该表进行任何读写操作。

columns(list([<column_info>]))

表的Meta信息,需要包含所有定义的列。

重要
  • 列的定义必须要包含Tablestore表的所有主键列,并且主键列的名称和顺序与Tablestore表的实际定义一致。

  • 如果Meta信息有误,则之后对该表的读写操作均会报错。

<column_info>

name

列的名称。

重要

列名在PrestoDB中大小写不敏感。

type

列的类型。目前PrestoDB中对应到Tablestore中的列类型仅支持Bigint、Varchar、Varbinary、Double和Boolean类型。

重要

PrestoDB中定义的列类型必须与Tablestore中的列类型相匹配。

originName

对应到Tablestore中实例的列名称。

常用SQL示例

使用PrestoDB操作表格存储的SQL常见示例包括创建Schema、创建映射表、Schema相关操作、映射表相关操作和数据操作。

创建Schema

创建Schema用于配置要访问的Tablestore实例信息以及进行用户鉴权。

创建一个访问Tablestore myinstance实例的Schema,Schema名称为testdb。

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

具体参数配置说明请参见下表。

参数

示例

是否必选

说明

endpoint

https://myinstance.cn-hangzhou.ots.aliyuncs.com

Tablestore实例的访问地址。更多信息,请参见服务地址

instance_name

myinstance

Tablestore实例名称。更多信息,请参见实例

access_id

************************

阿里云账号或者RAM用户的AccessKey ID。

access_key

********************************

阿里云账号或者RAM用户的AccessKey Secret。

创建映射表

创建映射表用于对应到Tablestore实例中的表,

重要

创建映射表时,请注意如下事项:

  • 请确保映射表中的字段类型和表格Tablestore数据表中的字段类型相匹配更多信息,请参见字段类型映射

  • 映射表名称必须与Tablestore中实际的表名称相同。

  • SQL语句CREATE TABLE中的table_name用于映射到Tablestore中实际的表。您可以为Tablestore中的同一个数据表创建多个不同的映射表。

  • 映射表中必须包括数据表的所有主键列,但是支持只包括部分属性列。

  • 映射表中主键列的名称和顺序必须与Tablestore数据表中主键列的名称和顺序保持一致。映射表中每一个属性列可通过指定 origin_name参数来映射到Tablestore中实际表内的列名。

以下SQL示例用于为Tablestore数据表exampletable创建一个同名映射表。

CREATE TABLE if not exists exampletable
(
   pk varchar,
   c1 double with (origin_name = 'col1'),
   c2 varcha with (origin_name = 'col2'),
   c3 varchar with (origin_name = 'col3')
) WITH (
   table_name = 'exampletable'
);

Schema相关操作

创建Schema后,您可以根据需要执行获取Schema列表和使用Schema操作。

获取Schema列表

获取已创建的Schema列表。

show schemas;

返回示例如下:

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

使用Schema

使用Schema用于后续操作Schema中的表。

重要

只有使用Schema后才能操作Schema中的表。

以下示例用于使用testdb Schema。

use testb;

返回示例如下:

USE

映射表相关操作

创建映射表后,您可以根据需要执行获取指定Schema中表列表、查看指定表的元数据和删除映射表操作。

获取Schema中表列表

使用Schema后,获取已创建的表列表。

show tables;

返回示例如下:

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

查看表的描述信息

以下示例用于查看exampletable的描述信息。

describe exmapletable;

返回示例如下:

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

删除映射表

以下示例用于删除映射表table1。

drop table table1;

返回示例如下:

DROP TABLE

数据操作

创建映射表后,写入数据到Tablestore表中以及查询和分析Tablestore表中数据。

重要

当前不支持通过PrestoDB更新或者删除Tablestore表中数据。

写入数据

  • 插入一行数据

    以下示例用于写入一行数据到exampletable。

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

    返回示例如下:

    INSERT: 1 row
  • 批量导入数据

    重要

    批量导入数据前,请确保已创建目标表,且目标表的表结构与源数据表的表结构保持一致。

    以下示例用于将exampletable中满足c1小于50的行数据拷贝到sampletable表中。

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

    返回示例如下:

    INSERT: 3 row

读取数据

  • 查询表中所有数据

    以下示例用于读取exampletable表中数据。

    select * from exampletable;

    返回示例如下:

     pk  |  c1  |  c2  |  c3   
    -----+------+------+-------
     100 | 11.0 | Lily | 10001 
     101 | 22.0 | Mary | 10002  
     102 | 12.0 | Jim  | 10003   
    (3 rows)
  • 查询表中满足指定条件的数据

    以下示例用于查询exampletable表中满足c1小于15且c3等于10001的行数据。

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

    返回示例如下:

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

分析数据

  • 计算表中指定列的平均值

    计算exampletable表中c2列的平均值。

    select avg(c1) as Average from exampletable;

    返回结果如下:

    Average 
    ---------
        15.0 
    (1 row)
  • 统计表的总行数

    以下示例用于统计exampletable表的总行数。

    select count(*) as total from exampletable;

    返回示例如下:

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