阿里云DLA工具 查询tablestore数据
OTS和DLA元信息映射逻辑
字段的映射关系
OTS | DLA
- | -|
INTEGER(8bytes) | bigint(8bytes)
STRING | varchar
BINARY | varbinary
DOUBLE | double
BOOLEAN | boolean
创建自己的DLA库
CREATE DATABASE `ecommerce`
WITH DBPROPERTIES (
catalog = 'ots',
location = 'https://dt-ecommerce.cn-shanghai.ots-internal.aliyuncs.com',
instance = 'dt-ecommerce'
)
#hangzhou_ots_test ---请注意库名,允许字母、数字、下划线
#catalog = 'ots', ---指定为ots,是为了区分其他数据源,比如oss、rds等
#location = 'https://xxx' ---ots的endpoint,从实例上可以看到
#instance = 'hz-tpch-1x-vol' ---指定instance名,因为endpoint可以不带实例名;最终映射到DLA的schema
创建DLA表,映射到OTS的表:
字段必须与Table Store中的主键字段顺序、名称相同。
CREATE EXTERNAL TABLE `t_alipay_mdeduct` (
`PK_baseInfoId` varchar(100) not null,
`PK_userId` bigint not null,
`PK_agreementNumber` varchar(100) not NULL ,
`agreementNumber` varchar(100),
`agreementPartner` varchar(255),
`baseInfoId` bigint,
`date` bigint DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`userId` bigint,
PRIMARY KEY (`PK_baseInfoId`,PK_userId,PK_agreementNumber)
)
CREATE EXTERNAL TABLE `t_alipay_messager` (
`pk_baseinfoid` varchar(100) NOT NULL COMMENT '',
`pk_userid` bigint NOT NULL COMMENT '',
`pk_uniquesign` varchar(100) NOT NULL COMMENT '',
`baseinfoid` bigint NULL COMMENT '',
`contentdesc` varchar(255) NULL COMMENT '',
`statusdesc` varchar(100) NULL COMMENT '',
`title` varchar(255) NULL DEFAULT 'NULL' COMMENT '',
`titledesc` varchar(255) NULL DEFAULT 'NULL' COMMENT '',
`uniquesign` varchar(100) NULL COMMENT '',
`userid` bigint NOT NULL COMMENT '',
PRIMARY KEY (`pk_baseinfoid`, `pk_userid`, `pk_uniquesign`)