ADB支持通过CREATE TABLE
创建表,也支持通过CTAS
将查询到的数据写入新表中。
语法
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
| table_constraints}
[, ... ] )
table_attribute
[partition_options]
[storage_policy]
[AS] query_expression
COMMENT 'string'
column_attributes:
[DEFAULT default_expr]
[AUTO_INCREMENT]
column_constraints:
[{NOT NULL|NULL} ]
[PRIMARY KEY]
table_constraints:
[{INDEX|KEY} [index_name] (column_name,...)]
[PRIMARY KEY [index_name] (column_name,...)]
[CLUSTERED KEY [index_name] (column_name,...)]
table_attribute:
DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST
partition_options:
PARTITION BY
{VALUE(column_name) | VALUE(date_format(column_name, ?))}
LIFECYCLE N
storage_policy:
STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]
参数
参数 | 说明 |
---|---|
table_name |
表名。
表名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。 支持 |
column_name |
列名。
列名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。 |
column_type |
要添加的列的数据类型。
AnalyticDB for MySQL支持的数据类型请参见基础数据类型。 |
column_attributes |
|
column_constraints |
|
table_constraints |
INDEX|KEY :倒排索引。
AnalyticDB for MySQL默认为表创建全索引,一般情况下无须手动创建索引。 |
PRIMARY KEY |
主键索引。
|
CLUSTERED KEY |
聚集索引,定义表中的排序列,聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。
例如, 聚集索引会将该列或者多列进行排序,保证与该列相同或者相近的数据存储在磁盘的相同或相近位置。当以聚集列做为查询条件时,查询结果存储在磁盘的相同位置,这样可以减少磁盘的IO,提高查询性能。 如何判断是否需要聚集索引:查询一定会携带的字段可以作为聚集索引。例如,SAAS类应用中,用户通常只访问自己的数据,用户ID可以定义为聚集索引,保证数据的局部性,提升数据查询性能。 聚集列有以下限制:
|
DISTRIBUTED BY HASH(column_name,...) |
在普通表中定义表的分布键,按照column_name 的HASH值进行分区。
AnalyticDB for MySQL支持将多个字段作为分区键。 |
DISTRIBUTED BY BROADCAST |
用于定义维度表,维度表会在集群的每个节点存储一份数据,因此建议维度表的数据量不宜太大。 |
partition_options |
普通表中定义分区。
AnalyticDB for MySQL通过 例如, |
storage_policy |
说明 目前仅弹性模式集群版(新版)实例支持冷热数据分层存储功能。
指定热(HOT)、冷(COLD)、或混合(MIXED)的存储策略。默认值为HOT。 不同存储策略下数据读写性能不同,存储成本不同。为了降低数据存储成本,同时还要保证查询性能,您可以选择将查询频度高的数据(称为热数据)存储在SSD介质;将查询频度低的数据(称为冷数据)存储在HDD介质。 根据业务需求,您还可以按表粒度、表的二级分区粒度独立选择冷、热存储介质。例如,指定这个表数据全部存储在SSD,或者全部存储在HDD,或者指定这个表的一部分二级分区存储在SSD,另一部分二级分区存储在HDD。
|
hot_partition_count=N | 指定MIXED存储策略时热分区的个数。表示按分区键的值的大小倒序排列,最大N个分区为热分区,其他分区为冷分区。
|
注意事项
- 创建表时,AnalyticDB for MySQL集群默认编码格式为utf-8,相当于MySQL中的utf8mb4编码,暂不支持其他编码格式。
- 目前AnalyticDB for MySQL集群支持创建的最大表数目如下所示:
- 集群版:min(节点组数量*256,10000)。
- 基础版:
- T8,500。
- T16和T32,1500。
- T52,2500。
示例
- 新建TEST表。
create table test ( id bigint auto_increment, name varchar, value int, ts timestamp ) DISTRIBUTED BY HASH(id)
TEST为普通表,
id
为自增列,分布键为id
,按照id
值进行HASH分区。 - 新建CUSTOMER表。
CREATE TABLE customer ( customer_id bigint NOT NULL COMMENT '顾客ID', customer_name varchar NOT NULL COMMENT '顾客姓名', phone_num bigint NOT NULL COMMENT '电话', city_name varchar NOT NULL COMMENT '所属城市', sex int NOT NULL COMMENT '性别', id_number varchar NOT NULL COMMENT '身份证号码', home_address varchar NOT NULL COMMENT '家庭住址', office_address varchar NOT NULL COMMENT '办公地址', age int NOT NULL COMMENT '年龄', login_time timestamp NOT NULL COMMENT '登录时间', PRIMARY KEY (login_time,customer_id,phone_num) ) DISTRIBUTED BY HASH(customer_id) PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30 COMMENT '客户信息表';
CUSTOMER表为普通表,
customer_id
为分布键,login_time
为分区键,login_time
、customer_id
、phone_num
为组合主键。
MySQL语法兼容性说明
AnalyticDB for MySQL标准建表语法中必须包含DISTRIBUTED BY ...
,而MySQL建表语法中没有DISTRIBUTED BY ...
。AnalyticDB for MySQL默认兼容MySQL建表语法,您可以根据实际情况通过以下两种方式处理DISTRIBUTED BY ...
不兼容问题。
- 如果MySQL表含有主键,AnalyticDB for MySQL默认将主键作为
DISTRIBUTED BY COLUMN
。mysql> create table t (c1 bigint, c2 int, c3 varchar, primary key(c1,c2)); Query OK, 0 rows affected (2.37 sec) mysql> show create table t; +-------+-------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------+ | t | Create Table `t` ( `c1` bigint, `c2` int, `c3` varchar, primary key (c1,c2) ) DISTRIBUTED BY HASH(`c1`,`c2`) INDEX_ALL='Y' | +-------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec)
- 如果MySQL表不含主键,AnalyticDB for MySQL将添加一个
__adb_auto_id__
字段作为主键和DISTRIBUTED BY COLUMN
。mysql> create table t (c1 bigint, c2 int, c3 varchar); Query OK, 0 rows affected (0.50 sec) mysql> show create table t; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t | Create Table `t` ( `c1` bigint, `c2` int, `c3` varchar, `__adb_auto_id__` bigint AUTO_INCREMENT, primary key (__adb_auto_id__) ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec)
创建表时指定冷热数据存储策略
您可以在创建表时指定冷热存储策略。
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
| table_constraints}
[, ... ] )
table_attribute
[partition_options]
[storage_policy]
[AS] query_expression
COMMENT 'string'
storage_policy:
STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]
- 示例:创建表时指定冷(COLD)存储策略
Create Table test_table ( L_ORDERKEY bigint NOT NULL, L_LINENUMBER int NOT NULL, L_SHIPDATE date NOT NULL, dummy varchar, primary key (l_orderkey,l_linenumber,l_shipdate) ) DISTRIBUTE BY HASH(l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' STORAGE_POLICY='COLD';
- 示例:创建表时指定热(HOT)存储策略
Create Table test_table ( L_ORDERKEY bigint NOT NULL, L_LINENUMBER int NOT NULL, L_SHIPDATE date NOT NULL, dummy varchar, primary key (l_orderkey,l_linenumber,l_shipdate) ) DISTRIBUTE BY HASH(l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' STORAGE_POLICY='HOT';
- 示例:创建表时指定混合(MIXED)存储策略,同时指定热分区数量为16个
Create Table test_table ( L_ORDERKEY bigint NOT NULL, L_LINENUMBER int NOT NULL, L_SHIPDATE date NOT NULL, dummy varchar, primary key (l_orderkey,l_linenumber,l_shipdate) ) DISTRIBUTE BY HASH(l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;
创建带向量索引的表
ann index [index_name] (col_name,...)] [algorithm=HNSW_PQ ] [dis_function=SquaredL2]
CREATE TABLE fact_tb (
xid bigint not null,
cid bigint not null,
uid varchar not null,
vid varchar not null,
wid varchar not null,
short_feature array < smallint >(32),
float_feature array < float >(32),
ann index short_feature_index(short_feature),
ann index float_feature_index(float_feature),
PRIMARY KEY (xid, cid, vid)
) DISTRIBUTE BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4
- short_feature/float_feature:向量列的名称,用户自定义。
array<float>(32)
:向量列的数据类型和向量的维数,用户自定义。必须要指定向量的维度,支持的类型包括: float、byte、short三种数组类型。表示对feature_data的列类型定义为512,示例如下:feature_data` array<float>(512)
- ann:系统关键字。
- index:系统关键字。
- short_feature_index/float_feature_index:索引名,用户自定义。对feature_data这一列创建向量索引:
ann index ecnn_index(`FEATURE_DATA`) algorithm=HNSW_PQ dis_function=SquaredL2
algorithm、dis_function定义可以省略,默认值分别为HNSW_PQ、SquaredL2。
- algorithm:向量距离计算公式使用的算法。ADB MySQL支持的向量距离计算公式算法如下表所示。
算法 适用场景 适用数据类型 HNSW_PQ 适用于单表数据量在百万级别到千万级别之间,对向量维度敏感的中等规模数据量场景。 short[]、byte[]、float[] - dis_function:向量距离计算公式,默认值为SquaredL2。ADB MySQL支持的向量距离计算公式如下表所示。
距离计算公式 计算公式 适用数据类型 SquaredEuclidean(简称SquaredL2) (x1-y1)^2+(x2-y2)^2+… byte[]、short[]或者float[]
- 查询与向量'[1,1,1,1]' 距离最近的5条记录:
select id, l2_distance(short_feature, '[1,1,1,1]') from fact_tb order by 2 limit 5;
- 带向量索引查询与向量'[1,1,1,1]' 距离最近的5条记录,按距离排序:
select id, l2_distance(short_feature, '[1,1,1,1]') from fact_tb where xid = 1 and cid = 0 order by 2 limit 5;
- 带向量索引查询与向量'[1,1,1,1]' 距离最近的5条记录,按距离排序,并控制距离最大范围:
select id, l2_distance(float_feature, '[1.2,1.5,2,3.0]') from fact_tb where l2_distance(float_feature, '[1.2,1.5,2,3.0]') < 50.0 and xid = 2 order by 2 limit 5;
在文档使用中是否遇到以下问题
更多建议
匿名提交