华为云用户手册

前提条件 请务必确保您的账户下已在数据仓库服务（DWS）里创建了DWS集群。 如何创建DWS集群，请参考《数据仓库服务管理指南》中“创建集群”章节。 请确保已创建DWS数据库表。 该场景作业需要运行在DLI的独享队列上，因此要与DWS集群建立增强型跨源连接，且用户可以根据实际所需设置相应安全组规则。 如何建立增强型跨源连接，请参考《数据湖探索用户指南》中增强型跨源连接章节。 如何设置安全组规则，请参见《虚拟私有云用户指南》中“安全组”章节。

示例 通过将student_info与course_info两张表中的课程编号匹配建立JOIN连接，来查看学生姓名及所选课程名称。 1 2 SELECT student_info.name, course_info.courseName FROM student_info JOIN course_info ON (student_info.courseId = course_info.courseId);

示例 在DLI数据多版本中，恢复非分区表test_table数据到版本20210930。 1 RESTORE TABLE test_table TO VERSION '20210930'; 在DLI数据多版本中，恢复分区表test_table对应dt分区数据到版本20210930。 1 RESTORE TABLE test_table PARTITION (dt='2021-07-27') TO VERSION '20210930';

关键字 表1 关键字说明 参数 是否必选 说明 type 是 输出通道类型，“opentsdb”表示输出到表格存储服务CloudTable（OpenTSDB）。 region 是 表格存储服务所在区域。 cluster_id 否 待插入数据所属集群的id，该参数与tsdb_link_address必须指定其中一个。 tsdb_metrics 是 数据点的metric，支持参数化。 tsdb_timestamps 是 数据点的timestamp，数据类型支持LONG、INT、SHORT和STRING，仅支持指定动态列。 tsdb_values 是 数据点的value，数据类型支持SHORT、INT、LONG、FLOAT、DOUBLE和STRING，支持指定动态列或者常数值。 tsdb_tags 是 数据点的tags，每个tags里面至少一个标签值，最多8个标签值，支持参数化。 batch_insert_data_num 否 表示一次性批量写入的数据量（即数据条数），值必须为正整数，上限为65536，默认值为8。 tsdb_link_address 否 待插入数据所属集群的OpenTsdb链接地址，使用该参数时，作业需要运行在独享DLI队列，且DLI队列需要与CloudTable集群建立增强型跨源，该参数与cluster_id必须指定其中一个。 说明： 如何建立增强型跨源连接，请参考《数据湖探索用户指南》中增强型跨源连接章节。

功能描述 DLI将Flink作业的输出数据输出到CloudTable的OpenTSDB中。OpenTSDB是基于HBase的分布式的，可伸缩的时间序列数据库。它存储的是时间序列数据，时间序列数据是指在不同时间点上收集到的数据，这类数据反映了一个对象随时间的变化状态或程度。支持秒级别数据的采集监控，进行永久存储，索引和查询，可用于系统监控和测量、物联网数据、金融数据和科学实验结果数据的收集监控。 表格存储服务（CloudTable），是基于Apache HBase提供的分布式、可伸缩、全托管的KeyValue数据存储服务，为DLI提供了高性能的随机读写能力，适用于海量结构化数据、半结构化数据以及时序数据的存储和查询应用，适用于物联网IOT应用和通用海量KeyValue数据存储与查询等场景。CloudTable的更多信息，请参见《表格存储服务用户指南》。

语法格式 1 2 3 4 5 6 7 8 9 10 11 CREATE SINK STREAM stream_id (attr_name attr_type (',' attr_name attr_type)* ) WITH ( type = "opentsdb", region = "", cluster_id = "", tsdb_metrics = "", tsdb_timestamps = "", tsdb_values = "", tsdb_tags = "", batch_insert_data_num = "" )

示例 将流weather_out的数据输出到表格存储服务CloudTable的OpenTSDB中。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE SINK STREAM weather_out ( timestamp_value LONG, /* 时间 */ temperature FLOAT, /* 温度值 */ humidity FLOAT, /* 湿度值 */ location STRING /* 地点 */ ) WITH ( type = "opentsdb", region = "xxx", cluster_id = "e05649d6-00e2-44b4-b0ff-7194adaeab3f", tsdb_metrics = "weather", tsdb_timestamps = "${timestamp_value}", tsdb_values = "${temperature}; ${humidity}", tsdb_tags = "location:${location},signify:temperature; location:${location},signify:humidity", batch_insert_data_num = "10" );

示例 完整的SQL作业提交流程您可以参考《快速入门》中的《提交SQL作业》等章节描述。 队列是使用DLI服务的基础，执行SQL前需要先创建队列。具体可以参考《用户指南》中的“创建队列”章节。 在DLI管理控制台，单击左侧导航栏中的“SQL编辑器”，可进入SQL作业“SQL编辑器”页面。 在“SQL编辑器”页面右侧的编辑窗口中，输入如下创建数据库的SQL语句，单击“执行”。阅读并同意隐私协议，单击“确定”。 若testdb数据库不存在，则创建数据库testdb。 1 CREATE DATABASE IF NOT EXISTS testdb;

按列GROUP BY 功能描述 按列进行分组操作。 语法格式 1 2 3 4 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] 语法说明 GROUP BY：按列可分为单列GROUP BY与多列GROUP BY。 单列GROUP BY：指GROUP BY子句中仅包含一列。 多列GROUP BY：指GROUP BY子句中不止一列，查询语句将按照GROUP BY的所有字段分组，所有字段都相同的记录将被放在同一组中。 注意事项 GroupBy在流处理表中会产生更新结果 示例 根据score及name两个字段对表student进行分组，并返回分组结果。 1 2 insert into temp SELECT name,score, max(score) FROM student GROUP BY name,score;

表达式GROUP BY 功能描述 按表达式对流进行分组操作。 语法格式 1 2 3 4 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] 语法说明 groupItem：可以是单字段，多字段，也可以是字符串函数等调用，不能是聚合函数。 注意事项 无 示例 先利用substring函数取字段name的子字符串，并按照该子字符串进行分组，返回每个子字符串及对应的记录数。 1 2 insert into temp SELECT substring(name,6),count(name) FROM student GROUP BY substring(name,6);

Grouping sets, Rollup, Cube 功能描述 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集，并且其效率比 GROUP BY 要高。 ROLLUP与CUBE按一定的规则产生多种分组，然后按各种分组统计数据。 CUBE生成的结果集显示了所选列中值的所有组合的聚合。 Rollup生成的结果集显示了所选列中值的某一层次结构的聚合。 语法格式 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY groupingItem] 语法说明 groupingItem：是Grouping sets(columnName [, columnName]*)、Rollup(columnName [, columnName]*)、Cube(columnName [, columnName]*) 注意事项 无 示例 分别产生基于user和product的结果 INSERT INTO temp SELECT SUM(amount) FROM Orders GROUP BY GROUPING SETS ((user), (product));

Union/Union ALL/Intersect/Except 语法格式 1 query UNION [ ALL ] | Intersect | Except query 语法说明 UNION返回多个查询结果的并集。 Intersect返回多个查询结果的交集。 Except返回多个查询结果的差集。 注意事项 集合运算是以一定条件将表首尾相接，所以其中每一个SELECT语句返回的列数必须相同，列的类型一定要相同，列名不一定要相同。 UNION默认是去重的，UNION ALL是不去重的。 示例 输出Orders1和Orders2的并集，不包含重复记录。 1 2 insert into temp SELECT * FROM Orders1 UNION SELECT * FROM Orders2;

IN 语法格式 1 2 3 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression WHERE column_name IN (value (, value)* ) | query 语法说明 IN操作符允许在where子句中规定多个值。若表达式在给定的表子查询中存在，则返回 true 。 注意事项 子查询表必须由单个列构成，且该列的数据类型需与表达式保持一致。 示例 输出Orders中NewProducts中product的user和amount信息。 1 2 3 4 5 insert into temp SELECT user, amount FROM Orders WHERE product IN ( SELECT product FROM NewProducts );

示例 建OBS表时仅有一个分区列，建表成功后添加分区数据。 先使用DataSource语法创建一个OBS分区表，分区列为external_data，数据存储在obs://bucketName/datapath路径下。 create table testobstable(id varchar(128), external_data varchar(16)) using JSON OPTIONS (path 'obs://bucketName/datapath') PARTITIONED by (external_data); 拷贝分区数据目录到obs://bucketName/datapath路径下。例如当前拷贝external_data=22的分区目录下所有文件到obs://bucketName/datapath路径下。 执行添加分区命令，将分区的元数据信息生效。 ALTER TABLE testobstable ADD PARTITION (external_data='22') LOCATION 'obs://bucketName/datapath/external_data=22'; 添加分区成功后，即可根据分区列进行数据查询等操作。 select * from testobstable where external_data='22'; 建OBS表时有多个分区列，建表成功后添加分区数据。 先使用DataSource语法创建一个OBS分区表，分区列为external_data和dt，数据存储在obs://bucketName/datapath路径下。 1 2 3 4 5 create table testobstable( id varchar(128), external_data varchar(16), dt varchar(16) ) using JSON OPTIONS (path 'obs://bucketName/datapath') PARTITIONED by (external_data, dt); 拷贝分区数据目录到obs://bucketName/datapath路径下。例如拷贝external_data=22及其子目录dt=2021-07-27和目录下文件到obs://bucketName/datapath路径下。 执行添加分区命令，将分区的元数据信息生效。 1 2 3 4 ALTER TABLE testobstable ADD PARTITION (external_data = '22', dt = '2021-07-27') LOCATION 'obs://bucketName/datapath/external_data=22/dt=2021-07-27'; 添加分区成功后，即可根据分区列进行数据查询等操作。 1 2 select * from testobstable where external_data = '22'; select * from testobstable where external_data = '22' and dt='2021-07-27';

功能描述 创建OBS分区表成功后，OBS表实际还没有生成分区信息。生成分区信息主要有以下两种场景： 给OBS分区表插入对应的分区数据，数据插入成功后OBS表才会生成分区元数据信息，后续则可以根据对应分区列进行查询等操作。 手工拷贝分区目录和数据到OBS分区表路径下，执行本章节介绍的分区添加命令生成分区元数据信息，后续即可根据对应分区列进行查询等操作。 本章节重点介绍使用ALTER TABLE命令添加分区的基本操作和使用说明。

注意事项 向表中添加分区时，此表和分区列（建表时PARTITIONED BY指定的列）必须已存在，而所要添加的分区不能重复添加，否则将出错。已添加的分区可通过IF NOT EXISTS避免报错。 若分区表是按照多个字段进行分区的，添加分区时需要指定所有的分区字段，指定字段的顺序可任意。 “partition_specs”中的参数默认带有“( )”。例如：PARTITION (dt='2009-09-09',city='xxx')。 在添加分区时若指定OBS路径，则该OBS路径必须是已经存在的，否则会出错。 若添加多个分区，每组PARTITION partition_specs LOCATION 'obs_path'之间用空格隔开。例如： PARTITION partition_specs LOCATION 'obs_path' PARTITION partition_specs LOCATION 'obs_path'。 若新增分区指定的路径包含子目录（或嵌套子目录），则子目录下面的所有文件类型及内容也将作为该分区的记录。用户需要保证该分区目录下所有文件类型和文件内容与表的字段一致，否则查询将报错。

语法格式 1 2 3 4 5 6 7 CREATE SINK STREAM stream_id (attr_name attr_type (',' attr_name attr_type)* ) WITH( type = "kafka", kafka_bootstrap_servers = "", kafka_topic = "", encode = "json" )

示例 将数据输出到Kafka中。 示例一 1 2 3 4 5 6 7 CREATE SINK STREAM kafka_sink (name STRING) WITH ( type="kafka", kafka_bootstrap_servers = "ip1:port1,ip2:port2", kafka_topic = "testsink", encode = "json" ); 示例二 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE SINK STREAM kafka_sink ( a1 string, a2 string, a3 string, a4 INT ) // 输出字段 WITH ( type="kafka", kafka_bootstrap_servers = "192.x.x.x:9093, 192.x.x.x:9093, 192.x.x.x:9093", kafka_topic = "testflink", // 写入的topic encode = "csv", // 编码格式，支持json/csv kafka_certificate_name = "Flink", kafka_properties_delimiter = ",", kafka_properties = "sasl.jaas.config=org.apache.kafka.common.security.plain.PlainLoginModule required username=\"xxx\" password=\"xxx\";,sasl.mechanism=PLAIN,security.protocol=SASL_SSL" );

关键字 表1 关键字说明 参数 是否必选 说明 type 是 输出通道类型，"kafka"表示输出到Kafka中。 kafka_bootstrap_servers 是 Kafka的连接端口，需要确保能连通（需要通过增强型跨源开通DLI队列和Kafka集群的连接）。 kafka_topic 是 写入的topic。 encode 是 编码格式，当前支持“json”和“user_defined”。 若编码格式为“user_defined”，则需配置“encode_class_name”和“encode_class_parameter”属性。 encode_class_name 否 当encode为user_defined时，需配置该参数，指定用户自实现编码类的类名（包含完整包路径），该类需继承类DeserializationSchema。 encode_class_parameter 否 当encode为user_defined时，可以通过配置该参数指定用户自实现编码类的入参，仅支持一个string类型的参数。 krb_auth 否 创建跨源认证的认证名。开启kerberos认证时，需配置该参数。如果创建的MRS集群未开启kerb认证的集群，请确保在DLI队列host文件中添加MRS集群master节点的“/etc/hosts”信息。 kafka_properties 否 可通过该参数配置kafka的原生属性，格式为"key1=value1;key2=value2" kafka_certificate_name 否 跨源认证信息名称。跨源认证信息类型为“Kafka_SSL”时，该参数有效。 说明： 指定该配置项时，服务仅加载该认证下指定的文件和密码，系统将自动设置到“kafka_properties”属性中。 Kafka SSL认证需要的其他配置信息，需要用户手动在“kafka_properties”属性中配置。

前提条件 Kafka服务端的端口如果监听在hostname上，则需要将Kafka Broker节点的hostname和IP的对应关系添加到DLI队列中。Kafka Broker节点的hostname和IP请联系Kafka服务的部署人员。如何添加IP域名映射，请参见《数据湖探索用户指南》中修改主机信息章节。 Kafka是线下集群，需要通过增强型跨源连接功能将Flink作业与Kafka进行对接。且用户可以根据实际所需设置相应安全组规则。 如何建立增强型跨源连接，请参考《数据湖探索用户指南》中增强型跨源连接章节。 如何设置安全组规则，请参见《虚拟私有云用户指南》中“安全组”章节。

注意事项 privilege必须为赋权对象在resource中的已授权限，否则会回收失败。Privilege支持的权限类型可参见数据权限列表。 resource可以是queue、database、table、view、column，格式分别为： queue的格式为：queues.queue_name database的格式为：databases.db_name table的格式为：databases.db_name.tables.table_name view的格式为：databases.db_name.tables.view_name column的格式为：databases.db_name.tables.table_name.columns.column_name

示例 回收用户user_name1对于数据库db1的删除数据库权限。 1 REVOKE DROP_DATABASE ON databases.db1 FROM USER user_name1; 回收用户user_name1对于数据库db1的表tb1的SELECT权限。 1 REVOKE SELECT ON databases.db1.tables.tb1 FROM USER user_name1; 回收角色role_name对于数据库db1的表tb1的SELECT权限。 1 REVOKE SELECT ON databases.db1.tables.tb1 FROM ROLE role_name;

关键字 表1 SELECT参数描述 参数 描述 ALL 返回重复的行。为默认选项。其后只能跟*，否则会出错。 DISTINCT 从结果集移除重复的行。 WHERE 指定查询的过滤条件，支持算术运算符、关系运算符和逻辑运算符。 where_condition 过滤条件。 GROUP BY 指定分组的字段，支持单字段及多字段分组。 col_name_list 字段列表。 ORDER BY 对查询结果进行排序。 ASC/DESC ASC为升序，DESC为降序，默认为ASC。 CLUSTER BY 为分桶且排序，按照分桶字段先进行分桶，再在每个桶中依据该字段进行排序，即当DISTRIBUTE BY的字段与SORT BY的字段相同且排序为降序时，两者的作用与CLUSTER BY等效。 DISTRIBUTE BY 指定分桶字段，不进行排序。 SORT BY 将会在桶内进行排序。 LIMIT 对查询结果进行限制，number参数仅支持INT类型。

语法格式 1 2 3 4 5 6 7 SELECT [ALL | DISTINCT] attr_expr_list FROM table_reference [WHERE where_condition] [GROUP BY col_name_list] [ORDER BY col_name_list][ASC | DESC] [CLUSTER BY col_name_list | DISTRIBUTE BY col_name_list] [SORT BY col_name_list]] [LIMIT number];

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 MATCH_RECOGNIZE ( [ PARTITION BY expression [, expression ]* ] [ ORDER BY orderItem [, orderItem ]* ] [ MEASURES measureColumn [, measureColumn ]* ] [ ONE ROW PER MATCH | ALL ROWS PER MATCH ] [ AFTER MATCH ( SKIP TO NEXT ROW | SKIP PAST LAST ROW | SKIP TO FIRST variable | SKIP TO LAST variable | SKIP TO variable ) ] PATTERN ( pattern ) [ WITHIN intervalLiteral ] DEFINE variable AS condition [, variable AS condition ]* ) MR SQL中的模式匹配是用MATCH_RECOGNIZE子句执行。MATCH_RECOGNIZE子句执行如下任务： 使用PARTITION BY 和ORDER BY子句对MATCH_RECOGNIZE子句中的数据进行逻辑分区和排序。 使用PATTERN子句来定义要查找的数据行的模式。这些模式使用规则表达式语法。 使用DEFINE子句指定PATTERN模式变量所需的逻辑条件。 使用MEASURES子句定义度量，这是一些可在SQL查询的其他部分所使用的表达式。

语法格式 将SELECT查询结果插入到表中： 1 2 3 4 5 6 7 INSERT INTO DLI_TABLE SELECT field1,field2... [FROM DLI_TEST] [WHERE where_condition] [LIMIT num] [GROUP BY field] [ORDER BY field] ...; 将某条数据插入到表中： 1 2 INSERT INTO DLI_TABLE VALUES values_row [, values_row ...];

参数说明 表1 参数描述 参数 描述 DLI_TABLE 已创建跨源连接的DLI表名称。 DLI_TEST 为包含待查询数据的表。 field1,field2...，field 表“DLI_TEST”中的列值，需要匹配表“DLI_TABLE”的列值和类型。 where_condition 查询过滤条件。 num 对查询结果进行限制，num参数仅支持INT类型。 values_row 想要插入到表中的值，列与列之间用逗号分隔。

UNION 语法格式 1 query UNION [ ALL ] query 语法说明 UNION返回多个查询结果的并集。 注意事项 集合运算是以一定条件将表首尾相接，所以其中每一个SELECT语句返回的列数必须相同，列的类型一定要相同，列名不一定要相同。 UNION默认是去重的，UNION ALL是不去重的。 示例 输出Orders1和Orders2的并集，不包含重复记录。 1 2 insert into temp SELECT * FROM Orders1 UNION SELECT * FROM Orders2;

按列GROUP BY 功能描述 按列进行分组操作。 语法格式 1 2 3 4 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] 语法说明 GROUP BY：按列可分为单列GROUP BY与多列GROUP BY。 单列GROUP BY：指GROUP BY子句中仅包含一列。 多列GROUP BY：指GROUP BY子句中不止一列，查询语句将按照GROUP BY的所有字段分组，所有字段都相同的记录将被放在同一组中。 注意事项 无。 示例 根据score及name两个字段对表student进行分组，并返回分组结果。 1 2 insert into temp SELECT name,score, max(score) FROM student GROUP BY name,score;

用表达式GROUP BY 功能描述 按表达式对流进行分组操作。 语法格式 1 2 3 4 SELECT [ ALL | DISTINCT ] { * | projectItem [, projectItem ]* } FROM tableExpression [ WHERE booleanExpression ] [ GROUP BY { groupItem [, groupItem ]* } ] 语法说明 groupItem：可以是单字段，多字段，也可以是字符串函数等调用，不能是聚合函数。 注意事项 无。 示例 先利用substring函数取字段name的子字符串，并按照该子字符串进行分组，返回每个子字符串及对应的记录数。 1 2 insert into temp SELECT substring(name,6),count(name) FROM student GROUP BY substring(name,6);