-- 创建内部表
begin;
create table [if not exists] [schema_name.]table_name(
id bigint primary key,
b text,
ds text,
)
partition by list(ds);
commit;
-- 创建子表
create table if not exists table_name_date_202101 partition of table_name for values in ('202101');
-- 创建外部表
CREATE FOREIGN TABLE odps_students (
id bigint,
b text,
ds text,
)
SERVER odps_server
OPTIONS (project_name 'public_data', table_name 'oracle2odps_cust');
-- 删除表
DROP TABLE [ IF EXISTS ] table_name [, ...];
-- 删除外部表
DROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
-- 修改表名
ALTER TABLE table_name RENAME to new_table_name;
-- 修改外部表名
ALTER FOREIGN TABLE [ IF EXISTS ] name RENAME TO new_name;
-- 增加列
ALTER TABLE IF EXISTS table_name ADD COLUMN new_column_name data_type;
ALTER TABLE IF EXISTS table_name ADD COLUMN col_add_1 data_type, ADD COLUMN col_add_2 TEXT IF NOT EXISTS col_add_2 data_type;
-- 给表增加注释
COMMENT ON TABLE table_name IS 'my comments on table table_name.';
-- 给列增加注释
COMMENT ON COLUMN table_name.col1 IS 'This my first col1';
-- 给外部表增加注释
COMMENT ON FOREIGN TABLE foreign_table IS ' comments on my foreign table';
-- 查询执行计划
explain querysql;
-- 查询表所在的组名
select property_value from hologres.hg_table_properties where table_name = '表名' and property_key = 'table_group';
-- 查询对应表组的shard count
select * from hologres.hg_table_group_properties where tablegroup_name = '组名' and property_key = 'shard_count';
-- 获取当前用户Account ID
SELECT current_user;
-- 获取当前用户的阿里云显示名称
SELECT user_display_name(current_user);
-- 获取所有用户的阿里云显示名称
SELECT user_display_name(rolname) FROM pg_roles;
-- UNION
返回位于一个或者两个结果集中的全部行
-- INTERSECT
返回同时位于两个结果集中的所有行
-- EXCEPT
返回位于第一个结果集但不在第二个结果集中的行
注：在所有三种情况下，重复行都会被消除（除非指定ALL）。
call set_table_property('table1','clustering_key','a');orcall set_table_property('tbl','clustering_key','a:desc,b:asc');
对建立索引数据进行排序，建立聚簇索引能够加速用户在索引列上的range和filter查询
例：selectsum(a)from table1 where a >100and a <200;
注：desc和asc表名构建索引时的排序方式，默认为asc。clustering key创建的时候数据类型不能为float/double，每个表最多只有一个clustering key
2.3 分段键Segment Key
call set_table_property('table1','segment key','ds');
分段键帮助Hologres进行一些文件的快速筛选和跳过。指定分段键，当查询条件包含分段列时，查询可以通过segment key快速找到相应数据对应的存储位置。
例：select sum(a) from table1 where ts > '2020-01-01' and ts < '2020-03-02'
注：segment key要求按照数据输入自增，一般只有时间类型的字段（timestamptz）适合设置为segment key，其他场景基本不需要设置。只有列存表支持分段键设置。
2.4 比特编码列bitmap columns
call set_table_property('table1','bitmap key','ds');
指定比特编码列使Hologres会在这些列上构建比特编码，相当于把数据与对应的行号做一个映射
例：select * from table1 where a = 100;
注：bitmap可以对segment内部的数据进行快速过滤，因此建议把filter条件的数据建成比特编码。目前Hologres会默认所有text列都会被隐藏式地设置到bitmap_columns中。但是只有列存表支持比特编码列
2.5 字典编码列设置
call set_table_property('table1','dictionary_encoding_columns','b');
字典编码可以将字符串的比较转换成数字的比较，加速group by查询
例：select sum(a) from table1 group by b;
注：不建议将基数高的列建为字典编码列，会导致查询性能变差。目前Hologres会默认所有text列都会被隐藏式地设置到bitmap_columns中。但是只有列存表支持比特编码列
2.6 分部键distribution key
call set_table_property('table1','distribution key','b');
指定分布列，数据将按照指定列，将数据shuffle到各个shard，同样的数值肯定会在同样的shrad中。
例：select count(1) from tmp1 join tmp2 on tmp1.a = tmp2.b
注：对于有pk的表，其分布键默认就是pk，如果不想pk字段作为分布键，可以指定pk字段的子集，但是不能随意指定。
可以通过shard_count来指定表的shard数，如果不指定的话每个数据库都有一个默认的shard数，一旦指定了一个表的shard数，其他的表如果想要和这个表做local join，就必须指定colcate with这个表。