华为云用户手册

多个子查询 with t1 as(select name,max(price) as maxprice from fruit group by name), t2 as(select name,avg(price) as avgprice from fruit group by name) select t1.*,t2.* from t1 join t2 on t1.name = t2.name;

描述 该语法是使用SELECT查询结果创建物化视图。物化视图是一个数据库对象，它包含了一个查询的结果，例如：它可以是远程数据的本地副本，单表查询或者多表join后查询的结果的行或列、行和列的子集，也可以是使用聚合函数的汇总表。 物化视图通常基于对数据表进行聚合和连接的查询结果创建。物化视图支持“查询重写”，这是一种优化技术，它将以原始表编写的用户查询转换为包括一个或多个物化视图的等效请求。 语法支持的属性包括： storage_table：指定存储表表名。 need_auto_refresh： 管理计算实例时，预先创建维护实例后，可通过设置need_auto_refresh为true，创建具备自动刷新能力的物化视图，它会自动创建并提交物化视图刷新任务，在此基础上，可对refresh_duration，start_refresh_ahead_of_expiry,refresh_priority等属性做进一步配置来调整自动刷新任务。 mv_validity：物化视图生命周期。0表示永久有效，最短为1分钟。need_auto_refresh设置为false时，mv_validity默认值为0；设置为true时，默认值为24小时。 refresh_duration：物化视图自动刷新任务的最长等待时间。默认为5分钟，取值范围为1分钟到24小时。若自动刷新任务的等待时间超过设定的最长等待时间，自动化任务界面对应的任务状态显示为"timeout"。 start_refresh_ahead_of_expiry：基于mv _validity设置物化视图自动刷新任务的提交时间，表示达到物化生命周期的指定百分比时，提交自动刷新任务，默认值为0.2，最小值为0.05。 refresh_priority：物化视图提交自动刷新任务的优先级。默认值为3，最大值为3，1表示最高优先级。高优先级的任务会有更大机会先被执行。

示例 返回一个1列3行的表： VALUES 1, 2, 3 返回一个2列3行的表： VALUES (1, 'a'), (2, 'b'), (3, 'c') 返回具有列名id、name的表： SELECT * FROM (values (1, 'a'), (2, 'b'),(3, 'c')) AS t (id, name); 创建一个具有列名id、name的新表： CREATE TABLE example AS SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t (id, name);

INTERSECT query INTERSECT [DISTINCT] query INTERSECT仅返回第一个和第二个查询的结果相交的行。以下是最简单的INTERSECT子句之一的示例。它选择值13和42，并将此结果集与选择值13的第二个查询合并。由于42仅在第一个查询的结果集中，因此不包含在最终结果中。 SELECT * FROM (VALUES 13,42) INTERSECT SELECT 13; _col0 ------- 13 (1 row)

EXCEPT query EXCEPT [DISTINCT] query EXCEPT返回在第一个查询结果而不在第二个查询结果中的行。 SELECT * FROM (VALUES 13, 42) EXCEPT SELECT 13; _col0 ------- 42 (1 row) Having子句目前不支持使用列的别名，例如： select count(userid) as num ,dept as aaa from salary group by dept having aaa='d1'; 报错如下： Query 20210630_085136_00024_wc8n9@default@HetuEngine failed: line 1:75: Column 'aaa' cannot be resolved

函数 make_set_digest(x) → setdigest 描述：将所有的输入值X，组合到setdigest中。 SELECT make_set_digest(value) FROM (VALUES 1, 2, 3) T(value); _col0 ------------------------------------------------- 01 10 00 00 00 02 0b 03 00 80 03 44 00 00 58 3d 5b 80 20 08 de 00 20 00 00 03 00 00 00 a8 c0 76 6c a0 20 08 de 4a c4 05 fb b7 03 44 00 0c 8b 48 b2 39 58 3d 5b 01 00 01 00 01 00 (1 row) SELECT make_set_digest(value) FROM (VALUES 'Trino', 'SQL', 'on', 'everything') T(value); _col0 ------------------------------------------------- 01 14 00 00 00 02 0b 04 00 c0 8c 7d 1e c0 75 c9 2d c0 1a 1a 66 03 11 c3 a5 00 20 00 00 04 00 00 00 06 e5 2d 45 05 11 c3 a5 48 85 6b d5 e0 8c 7d 1e b9 1a 8a 39 ff 75 c9 2d 02 ad 0c 7c ed 1a 1a 66 01 00 01 00 01 00 01 00 (1 row) merge_set_digest(setdigest) → setdigest 描述：返回由输入值setdigest聚合组成的setdigest。 cardinality(setdigest) → long 描述：基于内部HyperLogLog组件返回setdigest的基数。 SELECT cardinality(make_set_digest(value)) FROM (VALUES 1, 2, 2, 3, 3,4, 4, 4, 5) T(value); -- 5 intersection_cardinality(x, y) → long 描述：返回两个集合摘要交集的基数估计。其中x,y 都是setdigest类型。 SELECT intersection_cardinality(make_set_digest(v1), make_set_digest(v2)) FROM (VALUES (1, 1), (NULL, 2), (2, 3), (3, 4)) T(v1, v2); -- 3 jaccard_index(x, y) → double 描述：返回两个集合摘要的Jaccard索引估计值。其中x,y 都是setdigest类型。 SELECT jaccard_index(make_set_digest(v1), make_set_digest(v2)) FROM (VALUES (1, 1), (NULL,2), (2, 3), (NULL, 4)) T(v1, v2); -- 0.5 hash_counts(x) 描述：返回一个包含Murmur3Hash128哈希值及其在属于x的内部MinHash结构中出现的计数的Map。其中x是setdigest类型。 SELECT hash_counts(make_set_digest(value)) FROM (VALUES 1, 1, 1, 2, 2) T(value); -- {19144387141682250=3, -2447670524089286488=2}

调优案例 某用户使用Hudi MOR表存储其设备的订单出借信息，可通过订单号查询订单详细信息，每天订单量相对稳定，部分节假日可能存在小高峰，该场景存在以下特点： 订单号作为唯一值，并且80%以上的查询场景使用订单号进行等值查询，SQL形如select * from table where order_id = 'id1'; 每天订单量稳定，可采用天作为分区键。 历史分区更新不频繁，主要数据更新在新分区。 调优建议： 使用Bucket索引建表（Spark-SQL），并且索引键为订单ID， 分区键为日期。 定期使用compaction合并日志，提高查询性能。 SQL示例： set hoodie.compact.inline=true; set hoodie.schedule.compact.only.inline=true; set hoodie.run.compact.only.inline=false; create table hudi_mor (order_id int, comb int, col1 string, col2 string, dt int) using hudi partitioned by(dt) options(type='mor', primaryKey='order_id', preCombineField='comb', hoodie.index.type = 'BUCKET', hoodie.bucket.index.num.buckets=100, hoodie.bucket.index.hash.field = 'order_id')

示例 删除schema web： DROP SCHEMA web; 如果schema sales存在，删除该schema： DROP SCHEMA IF EXISTS sales; 级联删除schema test_drop，schema test_drop中存在表tb_web，会先删除tb_web，再删除test_drop： CREATE SCHEMA test_drop; USE test_drop; CREATE TABLE tb_web(col1 int); DROP DATABASE test_drop CASCADE;

描述 DATABASE和SCHEMA在概念上是等价可互换的。 该语法用于删除数据库databasename，如果目标数据库不存在，将抛出错误提示，但如果使用了IF EXISTS子句则不会抛出错误提示。 可选参数RESTRICT|CASCADE用于指定删除的模式，默认是RESTRICT模式，在这种模式下，数据库必须为空，不包含任何表才能删除，如果是CASCADE模式，表示级联删除，会先删除数据库下面的表 ，再删除数据库。

示例 -- 删除原生/管控表 Create table simple(id int, name string); Insert into simple values(1,'abc'),(2,'def'); select * from simple; id | name ----|------ 1 | abc 2 | def (2 rows) Truncate table simple; select * from simple; id | name ----|------ (0 rows) --删除表分区 Create table tb_truncate_part (id int, name string) partitioned by (age int, state string); Insert into tb_truncate_part values (1,'abc',10,'ap'),(2,'abc',10,'up'),(3,'abc',20,'ap'),(4,'abc',20,'up'); select * from tb_truncate_part; id | name | age | state ----|------|-----|------- 2 | abc | 10 | up 3 | abc | 20 | ap 1 | abc | 10 | ap 4 | abc | 20 | up (4 rows Truncate table tb_truncate_part partition (state = 'ap', age = 10); select * from tb_truncate_part; id | name | age | state ----|------|-----|------- 4 | abc | 20 | up 2 | abc | 10 | up 3 | abc | 20 | ap (3 rows)

Data masking函数 数据脱敏(Data masking) 指对某些敏感信息通过脱敏规则进行数据的变形，实现敏感隐私数据的可靠保护。 mask_first_n(string str[, int n]) →varchar 描述：返回str的屏蔽版本，前n个值被屏蔽。大写字母被转为＂X＂，小写字母被转为＂x＂，数字被转为＂n＂。 select mask_first_n('Aa12-5678-8765-4321', 4); _col0 --------------------- Xxnn-5678-8765-4321 (1 row) mask_last_n(string str[, int n]) →varchar 描述：返回str的屏蔽版本，后n个值被屏蔽。大写字母被转为＂X＂，小写字母被转为＂x＂，数字被转为＂n＂。 select mask_last_n('1234-5678-8765-Hh21', 4); _col0 --------------------- 1234-5678-8765-Xxnn (1 row) mask_show_first_n(string str[, int n]) →varchar 描述：返回str的屏蔽版本，只显示前n个字符。大写字母被转为＂X＂，小写字母被转为＂x＂，数字被转为＂n＂。 select mask_show_first_n('1234-5678-8765-4321',4); _col0 --------------------- 1234-nnnn-nnnn-nnnn (1 row) mask_show_flairst_n(string str[, int n]) →varchar 描述：返回str的屏蔽版本，只显示后n个值。大写字母被转为＂X＂，小写字母被转为＂x＂，数字被转为＂n＂。 select mask_show_last_n('1234-5678-8765-4321',4); _col0 --------------------- nnnn-nnnn-nnnn-4321 (1 row)) mask_hash(string|char|varchar str) →varchar 描述：返回基于str的散列值。散列是一致的，可以用于跨表连接被屏蔽的值。对于非字符串类型，返回NULL。 select mask_hash('panda'); _col0 ------------------------------------------------------------------ a7cdf5d0586b392473dd0cd08c9ba833240006a8a7310bf9bc8bf1aefdfaeadb (1 row) 父主题： HetuEngine SQL函数和操作符说明

示例 创建一个名为web的schema： CREATE SCHEMA web; 在指定路径创建schema，兼容写法示例： CREATE SCHEMA test_schema_5 LOCATION '/user/hive'; 在名为Hive的CATALOG下创建一个名为sales的schema： CREATE SCHEMA hive.sales; 如果当前catalogs下名为traffic的schema不存在时，则创建一个名为traffic的schema： CREATE SCHEMA IF NOT EXISTS traffic; 创建一个带属性的schema： CREATE DATABASE createtestwithlocation COMMENT 'Holds all values' LOCATION '/user/hive/warehouse/create_new' WITH dbproperties('name'='akku', 'id' ='9'); --通过describe schema|database 语句来查看刚创建的schema describe schema createtestwithlocation;

概述 所有的正则表达式函数都使用Java样式的语法。但以下情况除外： 使用多行模式（通过（？m）标志启用）时，只有\ n被识别为行终止符。 此外，不支持（？d）标志，因此不能使用。 大小写区分模式（通过（？i）标志启用）时，总是以unicode的模式去实现。同时，不支持上下文敏感匹配和局部敏感匹配。此外，不支持（？u）标志。 不支持Surrogate Pair编码方式。例如，\ uD800 \ uDC00不被视为U + 10000，必须将其指定为\ x {10000}。 边界字符（\b）无法被正确处理，因为它一个不带基字符的非间距标记。 \Q和\E在字符类（如[A-Z123]）中不受支持，而是作为文本处理。 支持Unicode字符类（\ p {prop}），但有以下差异： 名称中的所有下划线都必须删除。例如，使用OldItalic而不是Old_Italic 必须直接指定脚本，不能带Is，script =或sc =前缀。示例：\ p {Hiragana} 必须使用In前缀指定块。不支持block =和blk =前缀。示例：\p{Mongolian} 必须直接指定类别，而不能带Is，general_category =或gc =前缀。示例：\p{L} 二进制属性必须直接指定，而不是Is。示例：\p{NoncharacterCodePoint}

限制 EXCHANGE PARTITION： 被迁移的单个或多个分区，迁移前必须都是已存在的分区，并归属于来源表，且在目标表中不包含这些分区； 该操作涉及的表需要有相同的列定义，并且有相同的分区键； 如果表中包含索引，该操作会失败； 来源表和目标表中任意一个为事务表时，不允许Exchange partition操作； 对于目标表，在一次操作中，多个分区要么同时迁移成功，要么全部失败。对于来源表，操作成功后，所有迁移的分区都会被释放； Alter table change column不支持orc格式的表。 ALTER TABLE table_name ADD | DROP col_name命令仅对于ORC/PARQUET存储格式的非分区表可用。

示例 将表名从users 修改为 people： ALTER TABLE users RENAME TO people; 在表users中增加名为zip的列： ALTER TABLE users ADD COLUMN zip varchar; 从表users中删除名为zip的列： ALTER TABLE users DROP COLUMN zip; 将表users中列名id更改为user_id： ALTER TABLE users RENAME COLUMN id TO user_id; 修改分区操作： --创建两个分区表 CREATE TABLE IF NOT EXISTS hetu_int_table5 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE; CREATE TABLE IF NOT EXISTS hetu_int_table6 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE; --添加分区 ALTER TABLE hetu_int_table5 ADD IF NOT EXISTS PARTITION (dt='2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23) PARTITION (dt='2008-08-09 10:20:30.0', country='IN', year=2001, bonus=100.50) ; --查看分区 show partitions hetu_int_table5; dt | country | year | bonus -------------------------|---------|------|--------- 2008-08-09 10:20:30.000 | IN | 2001 | 100.500 2008-08-08 10:20:30.000 | IN | 2001 | 500.230 (2 rows) --删除分区 ALTER TABLE hetu_int_table5 DROP IF EXISTS PARTITION (dt=timestamp '2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23); --查看分区 show partitions hetu_int_table5; dt | country | year | bonus -------------------------|---------|------|--------- 2008-08-09 10:20:30.000 | IN | 2001 | 100.500 (1 row) --迁移分区示例 CREATE SCHEMA part_test; CREATE TABLE hetu_exchange_partition1 (a string, b string) PARTITIONED BY (ds string); CREATE TABLE part_test.hetu_exchange_partition2 (a string, b string) PARTITIONED BY (ds string); ALTER TABLE hetu_exchange_partition1 ADD PARTITION (ds='1'); --查看分区 show partitions hetu_exchange_partition1; ds ---- 1 (1 row) show partitions part_test.hetu_exchange_partition2; ds ---- (0 rows) --迁移分区，从 T1 到 T2 ALTER TABLE part_test.hetu_exchange_partition2 EXCHANGE PARTITION (ds='1') WITH TABLE hetu_exchange_partition1; --再次查看分区，可以看到分区迁移成功 show partitions hetu_exchange_partition1; ds ---- (0 row) show partitions part_test.hetu_exchange_partition2; ds ---- 1 (1 rows) --重命名分区 CREATE TABLE IF NOT EXISTS hetu_rename_table ( eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee details' partitioned by (year int) STORED AS TEXTFILE; ALTER TABLE hetu_rename_table ADD IF NOT EXISTS PARTITION (year=2001); SHOW PARTITIONS hetu_rename_table; year ------ 2001 (1 row) ALTER TABLE hetu_rename_table PARTITION (year=2001) rename to partition (year=2020); SHOW PARTITIONS hetu_rename_table; year ------ 2020 (1 row) --修改分区表 create table altercolumn4(a integer, b string) partitioned by (c integer); --修改表的文件格式 alter table altercolumn4 SET FILEFORMAT textfile; insert into altercolumn4 values (100, 'Daya', 500); alter table altercolumn4 partition (c=500) change column b empname string comment 'changed column name to empname' first; --修改分区表的存储位置（需要先在hdfs上创建目录，执行语句后，无法查到之前插入的那条数据） alter table altercolumn4 partition (c=500) set Location '/user/hive/warehouse/c500'; --修改列 b 改名为name，同时类型从integer转为string create table altercolumn1(a integer, b integer) stored as textfile; alter table altercolumn1 change column b name string; --修改altercolumn1的存储属性 ALTER TABLE altercolumn1 CLUSTERED BY(a, name) SORTED BY(name) INTO 25 BUCKETS; --查看altercolumn1的属性 describe formatted altercolumn1; Describe Formatted Table ---------------------------------------------------------------------------------------- # col_name data_type comment a integer name varchar # Detailed Table Information Database: default Owner: admintest LastAccessTime: 0 Location: hdfs://hacluster/user/hive/warehouse/altercolumn1 Table Type: MANAGED_TABLE # Table Parameters: STATS_GENERATED_VIA_STATS_TASK workaround for potential lack of HIVE-12730 numFiles 0 numRows 0 orc.compress.size 262144 orc.compression.codec GZIP orc.row.index.stride 10000 orc.stripe.size 67108864 presto_query_id 20210325_025238_00034_f63xj@default@HetuEngine presto_version rawDataSize 0 totalSize 0 transient_lastDdlTime 1616640758 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: 25 Bucket Columns: [a, name] Sort Columns: [SortingColumn{columnName=name, order=ASCENDING}] Storage Desc Params: serialization.format 1 (1 row) Query 20210325_090522_00091_f63xj@default@HetuEngine, FINISHED, 1 node Splits: 1 total, 1 done (100.00%) 0:00 [0 rows, 0B] [0 rows/s, 0B/s]

描述 使用LIKE子句可以在一个新表中包含一个已存在的表所有的列定义。可以使用多个LIKE来复制多个表的列。 如果使用了INCLUDING PROPERTIES，表的所有属性也会被复制到新表，该选项最多只能对一个表生效。 对于从表中复制过来的属性，可以使用WITH子句指定属性名进行修改。 默认使用EXCLUDING PROPERTIES属性。 对于带分区的表，如果用括号包裹like子句，复制的列定义不会包含分区键的信息。

语法 CREATE TABLE [ IF NOT EXISTS] table_name ( { coulumn_name data_type [ COMMENT comment] [ WITH (property_name = expression [,…] ) ] | LIKE existing_table_name [ {INCLUDING| EXCLUDING} PROPERTIES] } ) [,…] [ COMMENT table_comment] [WITH (property_name = expression [,… ] ) ]

示例 创建基础表order01和order02 CREATE TABLE order01(id int,name string,tel string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS TEXTFILE; CREATE TABLE order02(sku int, sku_name string, sku_describe string); 创建表orders_like01，它将包含表order01定义的列及表属性 CREATE TABLE orders_like01 like order01 INCLUDING PROPERTIES; 创建表orders_like02，它将包含表order02定义的列，并将表的存储格式设置为‘TEXTFILE’ CREATE TABLE orders_like02 like order02 STORED AS TEXTFILE; 创建表orders_like03，它将包含表order01定义的列及表属性，order02定义的列，以及额外的列c1和c2 CREATE TABLE orders_like03 (c1 int,c2 float,LIKE order01 INCLUDING PROPERTIES,LIKE order02); 创建表orders_like04和orders_like05，它们都会包含同一个表order_partition的定义，但orders_like04不会包含分区键信息，而orders_like05会包含分区键的信息 CREATE TABLE order_partition(id int,name string,tel string) PARTITIONED BY (sku int); CREATE TABLE orders_like04 (like order_partition); CREATE TABLE orders_like05 like order_partition; DESC orders_like04; Column | Type | Extra | Comment --------|---------|-------|--------- id | integer | | name | varchar | | tel | varchar | | sku | integer | | (4 rows) DESC orders_like05; Column | Type | Extra | Comment --------|---------|---------------|--------- id | integer | | name | varchar | | tel | varchar | | sku | integer | partition key | (4 rows)

示例 用指定列的查询结果创建新表orders_column_aliased： CREATE TABLE orders_column_aliased (order_date, total_price) AS SELECT orderdate, totalprice FROM orders; 用表orders的汇总结果新建一个表orders_by_data： CREATE TABLE orders_by_date COMMENT 'Summary of orders by date' WITH (format = 'ORC') AS SELECT orderdate, sum(totalprice) AS price FROM orders GROUP BY orderdate; 如果表orders_by_date不存在，则创建表orders_by_date： CREATE TABLE IF NOT EXISTS orders_by_date AS SELECT orderdate, sum(totalprice) AS price FROM orders GROUP BY orderdate; 用和表orders具有相同schema创建新表empty_orders table，但是没数据： CREATE TABLE empty_orders AS SELECT * FROM orders WITH NO DATA; 使用VALUES 创建表，参考 VALUES。 分区表示例: CREATE EXTERNAL TABLE hetu_copy(corderkey, corderstatus, ctotalprice, corderdate, cds) PARTITIONED BY(cds) SORT BY (corderkey, corderstatus) COMMENT 'test' STORED AS orc LOCATION '/user/hetuserver/tmp' TBLPROPERTIES (orc_bloom_filter_fpp = 0.3, orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = 'corderstatus,ctotalprice') as select * from hetu_test; CREATE TABLE hetu_copy1(corderkey, corderstatus, ctotalprice, corderdate, cds) WITH (partitioned_by = ARRAY['cds'], bucketed_by = ARRAY['corderkey', 'corderstatus'], sorted_by = ARRAY['corderkey', 'corderstatus'], bucket_count = 16, orc_compress = 'SNAPPY', orc_compress_size = 6710422, orc_bloom_filter_columns = ARRAY['corderstatus', 'ctotalprice'], external = true, format = 'orc', location = '/user/hetuserver/tmp ') as select * from hetu_test;

语法 CREATE [EXTERNAL]① TABLE [IF NOT EXISTS] [catalog_name.][db_name.]table_name [ ( column_alias, ... ) ] [[PARTITIONED BY ①(col_name, ....)] [SORT BY① ([column [, column ...]])] ]① [COMMENT 'table_comment'] [ WITH ( property_name = expression [, ...] ) ]② [[STORED AS file_format]① [LOCATION 'hdfs_path']① [TBLPROPERTIES (orc_table_property = value [, ...] ) ] ]① AS query [ WITH [ NO ] DATA ]②

示例 -- 创建事务表 create table upd_tb(col1 int,col2 string) with (format='orc',transactional=true); --插入数据 insert into upd_tb values (3,'A'),(4,'B'); --修改col1 = 4的数据 update upd_tb set col1=5 where col1=4; --查询表，col1=4的记录已被修改 select * from upd_tb; -- col1 | col2 ------|------ 5 | B 3 | A

Concatenation Operator : || || 操作符用于将相同类型的数组或数值串联起来。 SELECT ARRAY[1] || ARRAY[2]; _col0 -------- [1, 2] (1 row) SELECT ARRAY[1] || 2; _col0 -------- [1, 2] (1 row) SELECT 2 || ARRAY[1]; _col0 -------- [2, 1] (1 row)

SYSTEM 此采样方法将表划分为数据的逻辑段，并按此粒度对表进行采样。此采样方法要么从特定数据段中选择所有行，要么跳过它（基于采样百分比与运行时计算的随机值之间的比较）。系统采样中行的选择依赖于使用的connector。例如，如果使用Hive数据源，这将取决于数据在HDFS上的布局。这种采样方法不能保证独立的抽样概率。 SELECT * FROM users TABLESAMPLE SYSTEM (75);

BERNOULLI 每一行都将基于指定的采样率选择到采样表中。当使用Bernoulli方法对表进行采样时，将扫描表的所有物理块并跳过某些行（基于采样百分比和运行时计算的随机值之间的比较）。结果中包含一行的概率与任何其他行无关。这不会减少从磁盘读取采样表所需的时间。如果进一步处理采样输出，则可能会影响总查询时间。 SELECT * FROM users TABLESAMPLE BERNOULLI (50);

描述 显示一条语句的逻辑的或者分布式的执行计划，也可以用于校验一条SQL语句，或者是分析IO。 参数TYPE DISTRIBUTED用于显示分片后的计划（fragmented plan）。每一个fragment都会被一个或者多个节点执行。Fragments separation表示数据在两个节点之间进行交换。Fragment type表示一个fragment如何被执行以及数据在不同fragment之间怎样分布。 SINGLE Fragment会在单个节点上执行。 HASH Fragment会在固定数量的节点上执行，输入数据通过哈希函数进行分布。 ROUND_ROBIN Fragment会在固定数量的节点上执行，片段在固定数量的节点上执行，输入数据以轮循方式进行分布。 BROADCAST Fragment会在固定数量的节点上执行，输入数据被广播到所有的节点。 SOURCE Fragment在访问输入分段的节点上执行。

示例 LOGICAL： CREATE TABLE testTable (regionkey int, name varchar); EXPLAIN SELECT regionkey, count(*) FROM testTable GROUP BY 1; Query Plan ------------------------------------------------------------------------------------------------------------------------------------- Output[regionkey, _col1] │ Layout: [regionkey:integer, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ _col1 := count └─ RemoteExchange[GATHER] │ Layout: [regionkey:integer, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ Project[] │ Layout: [regionkey:integer, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ Aggregate(FINAL)[regionkey][$hashvalue] │ Layout: [regionkey:integer, $hashvalue:bigint, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ count := count("count_8") └─ LocalExchange[HASH][$hashvalue] ("regionkey") │ Layout: [regionkey:integer, count_8:bigint, $hashvalue:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ RemoteExchange[REPARTITION][$hashvalue_9] │ Layout: [regionkey:integer, count_8:bigint, $hashvalue_9:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ Aggregate(PARTIAL)[regionkey][$hashvalue_10] │ Layout: [regionkey:integer, $hashvalue_10:bigint, count_8:bigint] │ count_8 := count(*) └─ ScanProject[table = hive:default:testtable] Layout: [regionkey:integer, $hashvalue_10:bigint] Estimates: {rows: 0 (0B), cpu: 0, memory: 0B, network: 0B}/{rows: 0 (0B), cpu: 0, memory: 0B, network: 0B} $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("regionkey"), 0)) regionkey := regionkey:int:0:REGULAR DISTRIBUTED： EXPLAIN (type DISTRIBUTED) SELECT regionkey, count(*) FROM testTable GROUP BY 1; Query Plan ----------------------------------------------------------------------------------------------------------------------- Fragment 0 [SINGLE] Output layout: [regionkey, count] Output partitioning: SINGLE [] Stage Execution Strategy: UNGROUPED_EXECUTION Output[regionkey, _col1] │ Layout: [regionkey:integer, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ _col1 := count └─ RemoteSource[1] Layout: [regionkey:integer, count:bigint] Fragment 1 [HASH] Output layout: [regionkey, count] Output partitioning: SINGLE [] Stage Execution Strategy: UNGROUPED_EXECUTION Project[] │ Layout: [regionkey:integer, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ Aggregate(FINAL)[regionkey][$hashvalue] │ Layout: [regionkey:integer, $hashvalue:bigint, count:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ count := count("count_8") └─ LocalExchange[HASH][$hashvalue] ("regionkey") │ Layout: [regionkey:integer, count_8:bigint, $hashvalue:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} └─ RemoteSource[2] Layout: [regionkey:integer, count_8:bigint, $hashvalue_9:bigint] Fragment 2 [SOURCE] Output layout: [regionkey, count_8, $hashvalue_10] Output partitioning: HASH [regionkey][$hashvalue_10] Stage Execution Strategy: UNGROUPED_EXECUTION Aggregate(PARTIAL)[regionkey][$hashvalue_10] │ Layout: [regionkey:integer, $hashvalue_10:bigint, count_8:bigint] │ count_8 := count(*) └─ ScanProject[table = hive:default:testtable, grouped = false] Layout: [regionkey:integer, $hashvalue_10:bigint] Estimates: {rows: 0 (0B), cpu: 0, memory: 0B, network: 0B}/{rows: 0 (0B), cpu: 0, memory: 0B, network: 0B} $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("regionkey"), 0)) regionkey := regionkey:int:0:REGULAR VALIDATE： EXPLAIN (TYPE VALIDATE) SELECT id, count(*) FROM testTable GROUP BY 1; Valid ------- true IO： EXPLAIN (TYPE IO, FORMAT JSON) SELECT regionkey , count(*) FROM testTable GROUP BY 1; Query Plan --------------------------------- { "inputTableColumnInfos" : [ { "table" : { "catalog" : "hive", "schemaTable" : { "schema" : "default", "table" : "testtable" } }, "columnConstraints" : [ ] } ] }

使用多个列 SELECT numbers, animals, n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a);

示例 -- 演示数据准备 create schema show_schema; use show_schema; create table show_table1(a int,b string); create table show_table2(a int,b string); create table from_table1(a int,b string); create table in_table1(a int,b string); --查询表名以"show"开始的表的详细信息 show table extended like 'show*'; tab_name -------------------------------------------------------------------------- tableName:show_table1 owner:admintest location:hdfs://hacluster/user/hive/warehouse/show_schema.db/show_table1 InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns:struct columns {int a,string b} partitioned:false partitionColumns: totalNumberFiles:0 totalFileSize:0 tableName:show_table2 owner:admintest location:hdfs://hacluster/user/hive/warehouse/show_schema.db/show_table2 InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns:struct columns {int a,string b} partitioned:false partitionColumns: totalNumberFiles:0 totalFileSize:0 (1 row) -- 查询表名以"from"或者"show"开头的表的详细信息 show table extended like 'from*|show*'; tab_name ---------------------------------------------------------------------- tableName show_table1 owner admintest location hdfs://hacluster/user/hive/warehouse/show_table1 InputFormat org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns struct columns {int a,string b} partitioned false partitionColumns totalNumberFiles 0 totalFileSize null tableName from_table1 owner admintest location hdfs://hacluster/user/hive/warehouse/from_table1 InputFormat org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns struct columns {int a,string b} partitioned false partitionColumns totalNumberFiles 0 totalFileSize null tableName show_table2 owner admintest location hdfs://hacluster/user/hive/warehouse/show_table2 InputFormat org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns struct columns {int a,string b} partitioned false partitionColumns totalNumberFiles 0 totalFileSize null (1 row) -- 查询web schema下的page_views表扩展信息 show table extended from web like 'page*'; tab_name ----------------------------------------------------------------------------- tableName:page_views owner:admintest location:hdfs://hacluster/user/web.db/page_views InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat columns:struct columns {timestamp view_time,bigint user_id,string page_url} partitioned:true partitionColumns: struct partition_columns {date ds,string country} totalNumberFiles:0 totalFileSize:0 (1 row)

参数说明 IN | FROM schema_name 指定schema名称，未指定时默认使用当前的schema。 LIKE 'identifier_with_wildcards' identifier_with_wildcards只支持包含“*”和“|”的规则匹配表达式。 其中“*”可以匹配单个或多个字符，“|”适用于匹配多种规则匹配表达式中的任意一种的情况，它用于分隔这些规则匹配表达式。 规则匹配表达式首尾的空格，不会参与匹配计算。 partition_spec 一个可选参数，使用键值对来指定分区列表，键值对之间通过逗号分隔。需要注意，指定分区时，表名不支持模糊匹配。

数据结构 HyperLogLog（hll）是一种统计基数的算法。它实际上不会存储每个元素出现的次数，它使用的是概率算法，通过存储元素的32位hash值的第一个1的位置，来计算元素数量。通常分为稀疏存储结构和密集存储结构两种。hll创建时是稀疏存储结构，当需要更高效处理时会转为密集型数据结构。P4HyperLogLog则在其整改生命周期都是密集型数据结构。如有必要，可以显式地转换cast(hll as P4HyperLogLog)。在当前数据引擎的实现中，hll的数据草图是通过一组32位的桶来存储对应的最大hash。