华为云用户手册

参数说明 表1 禁止或恢复生命周期参数说明 参数名称 是否必选 参数说明 table_name 是 待禁止或恢复生命周期的表的名称。 pt_spec 否 待禁止或恢复生命周期的表的分区信息。格式为partition_col1=col1_value1, partition_col2=col2_value1...。对于有多级分区的表，必须指明全部的分区值。 enable 否 恢复表或指定分区的生命周期功能 表及其分区重新参与生命周期回收，默认使用当前表及分区上的生命周期配置。 开启表生命周期前可以修改表及分区的生命周期配置，防止开启表生命周期后因使用之前的配置导致数据被误回收。 disable 否 禁止表或指定分区的生命周期功能。 禁止表本身及其所有分区被生命周期回收，优先级高于恢复表分区生命周期。即当使用禁止表或指定分区的生命周期功能时，设置待禁止或恢复生命周期的表的分区信息是无效的。 禁止表的生命周期功能后，表的生命周期配置及其分区的enable和disable标记会被保留。 禁止表的生命周期功能后，仍然可以修改表及分区表的生命周期配置。

示例 示例1：禁止表test_lifecycle的生命周期功能。 1 alter table test_lifecycle SET TBLPROPERTIES("dli.table.lifecycle.status"='disable'); 示例2：禁止表test_lifecycle中时间为20230520分区的生命周期功能。 1 alter table test_lifecycle partition (dt='20230520') LIFECYCLE 'disable'; 当设置禁止分区表的生命周期功能后，该表的所有分区的生命周期功能都会被禁止。

示例 建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'。 若新增分区指定的路径包含子目录（或嵌套子目录），则子目录下面的所有文件类型及内容也将作为该分区的记录。用户需要保证该分区目录下所有文件类型和文件内容与表的字段一致，否则查询将报错。

示例 右外连接和左外连接相似，但是会将右边表（这里的course_info)中的所有记录返回，没有匹配值的左表记录将返回NULL。 1 2 SELECT student_info.name, course_info.courseName FROM student_info RIGHT OUTER JOIN course_info ON (student_info.courseId = course_info.courseId);

功能描述 DLI 提供多版本功能，用于数据的备份与恢复。开启多版本功能后，在进行删除或修改表数据时（insert overwrite或者truncate操作），系统会自动备份历史数据并保留一定时间，后续您可以对保留周期内的数据进行快速恢复，避免因误操作丢失数据。其他多版本SQL语法请参考多版本备份恢复数据。 DLI数据多版本功能当前仅支持通过Hive语法创建的OBS表，具体建表语法可以参考使用Hive语法创建OBS表。

语法格式 开启多版本功能 ALTER TABLE [db_name.]table_name SET TBLPROPERTIES ("dli.multi.version.enable"="true"); 关闭多版本功能 1 2 ALTER TABLE [db_name.]table_name UNSET TBLPROPERTIES ("dli.multi.version.enable"); 开启多版本功能后，在执行insert overwrite或者truncate操作时会自动在OBS存储路径下存储多版本数据。关闭多版本功能后，需要通过如下命令把多版本数据目录回收。 RESTORE TABLE [db_name.]table_name TO initial layout;

示例 修改表test_table，开启多版本功能。 1 2 ALTER TABLE test_table SET TBLPROPERTIES ("dli.multi.version.enable"="true"); 修改表test_table，关闭多版本功能。 1 2 ALTER TABLE test_table UNSET TBLPROPERTIES ("dli.multi.version.enable"); 回退多版本路径。 RESTORE TABLE test_table TO initial layout;

注意事项 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 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 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类型。

语法格式 将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 想要插入到表中的值，列与列之间用逗号分隔。

关键字 GROUP BY：按列可分为单列GROUP BY与多列GROUP BY。 单列GROUP BY：指GROUP BY子句中仅包含一列，col_name_list中包含的字段必须出现在attr_expr_list的字段内，attr_expr_list中可以使用多个聚合函数，比如count()，sum()，聚合函数中可以包含其他字段。 多列GROUP BY：指GROUP BY子句中不止一列，查询语句将按照GROUP BY的所有字段分组，所有字段都相同的记录将被放在同一组中，同样，GROUP BY中出现的字段必须在attr_expr_list的字段内，attr_expr_list也可以使用聚合函数。

示例 给用户user_name1授予数据库db1的删除数据库权限。 1 GRANT DROP_DATABASE ON databases.db1 TO USER user_name1; 给用户user_name1授予数据库db1的表tb1的SELECT权限。 1 GRANT SELECT ON databases.db1.tables.tb1 TO USER user_name1; 给角色role_name授予数据库db1的表tb1的SELECT权限。 1 GRANT SELECT ON databases.db1.tables.tb1 TO ROLE role_name;

注意事项 privilege必须是可授权限中的一种。且如果赋权对象在resource或上一级resource上已经有对应权限时，则会赋权失败。Privilege支持的权限类型可参见数据权限列表。 resource可以是queue、database、table、view、column，格式分别为： queue的格式为：queues.queue_name queue支持的Privilege权限类型可以参考下表： 操作 说明 DROP_QUEUE 删除队列 SUBMIT_JOB 提交作业 CANCEL_JOB 终止作业 RESTART 重启队列 SCALE_QUEUE 扩缩容队列 GRANT_PRIVILEGE 队列的赋权 REVOKE_PRIVILEGE 队列权限的回收 SHOW_PRIVILEGES 查看其他用户具备的队列权限 database的格式为：databases.db_name database支持的Privilege权限类型可参见数据权限列表。 table的格式为：databases.db_name.tables.table_name table支持的Privilege权限类型可参见数据权限列表。 view的格式为：databases.db_name.tables.view_name view支持的Privilege权限类型和table一样，具体可以参考数据权限列表中table的权限列表描述。 column的格式为：databases.db_name.tables.table_name.columns.column_name column支持的Privilege权限类型仅为：SELECT

关键字 ROLLUP：为GROUP BY的扩展，例如：SELECT a, b, c, SUM(expression) FROM table GROUP BY a, b, c WITH ROLLUP;将转换成以下四条查询： (a, b, c)组合小计 1 2 SELECT a, b, c, sum(expression) FROM table GROUP BY a, b, c; (a, b)组合小计 1 2 SELECT a, b, NULL, sum(expression) FROM table GROUP BY a, b; (a)组合小计 1 2 SELECT a, NULL, NULL, sum(expression) FROM table GROUP BY a; 总计 1 SELECT NULL, NULL, NULL, sum(expression) FROM table;

参数说明 表1 参数描述 参数 描述 db_name Database名称，由字母、数字和下划线（_）组成。不能是纯数字，且不能以下划线开头。 table_name Database中的表名，由字母、数字和下划线（_）组成。不能是纯数字，且不能以下划线开头。匹配规则为：^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。如果特殊字符需要使用单引号（''）包围起来。 partition_specs 分区信息，key=value形式，key为分区字段，value为分区值。若分区字段为多个字段，可以不包含所有的字段，会显示匹配上的所有分区信息。

示例代码 计算所有商品库存（items）的样本方差。命令示例如下： select var_samp(items) from warehouse; 返回结果如下： _c0 294.342355 与group by配合使用，对所有商品按照仓库（warehourseId）进行分组，并计算同组商品库存（items）的样本方差。命令示例如下： select warehourseId, var_samp(items) from warehourse group by warehourseId; 返回结果如下： warehouseId _c1 city1 18.23124 city2 16.23344 city3 11.43425

注意事项 导入OBS表时，创建OBS表时指定的路径必须是文件夹，若建表路径是文件将导致导入数据失败。 仅支持导入位于OBS路径上的原始数据。 不建议对同一张表并发导入数据，因为有一定概率发生并发冲突，导致导入失败。 导入数据时只能指定一个路径，路径中不能包含逗号。 当OBS桶目录下有文件夹和文件同名时，导入数据会优先指向该路径下的文件而非文件夹。 导入PARQUET、ORC及JSON类型数据时，必须指定DATA_TYPE这一OPTIONS，否则会以默认的“ CS V”格式进行解析，从而导致导入的数据格式不正确。 导入CSV及JSON类型数据时，如果包含日期及时间列，需要指定DATEFORMAT及TIMESTAMPFORMAT选项，否则将以默认的日期及时间戳格式进行解析。

示例 导入数据前已参考创建OBS表或者创建DLI表中的示例描述创建对应的表。 可使用下列语句将CSV文件导入到DLI表，“t”为表名。 1 2 LOAD DATA INPATH 'obs://dli/data.csv' INTO TABLE t OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','COMMENTCHAR'='#','HEADER'='false'); 可使用下列语句将JSON文件导入到DLI表，“jsontb”为表名。 1 2 LOAD DATA INPATH 'obs://dli/alltype.json' into table jsontb OPTIONS('DATA_TYPE'='json','DATEFORMAT'='yyyy/MM/dd','TIMESTAMPFORMAT'='yyyy/MM/dd HH:mm:ss');

参数说明 表1 参数描述 参数 描述 folder_path 原始数据文件夹或者文件的OBS路径。 db_name 数据库名称。若未指定，则使用当前数据库。 table_name 需要导入数据的DLI表的名称。 以下是可以在导入数据时使用的配置选项： DATA_TYPE: 指定导入的数据类型，当前支持CSV、Parquet、ORC、JSON、Avro类型，默认值为“CSV”。 配置项为OPTIONS('DATA_TYPE'='CSV') 导入CSV和JSON文件时，有三种模式可以选择： PERMISSIVE：选择PERMISSIVE模式时，如果某一列数据类型与目标表列数据类型不匹配，则该行数据将被设置为null。 DROPMALFORMED：选择DROPMALFORMED模式时，如果某一列数据类型与目标表列数据类型不匹配，则不导入该行数据。 FAILFAST：选择FAILFAST模式时，如果某一列类型不匹配，则会抛出异常，导入失败。 模式设置可通过在OPTIONS中添加 OPTIONS('MODE'='PERMISSIVE')进行设置。 DELIMITER：可以在导入命令中指定分隔符，默认值为“,”。 配置项为OPTIONS('DELIMITER'=',')。 对于CSV数据，支持如下所述分隔符： 制表符tab，例如：'DELIMITER'='\t'。 任意的二进制字符，例如：'DELIMITER'='\u0001(^A)'。 单引号（'），单引号必须在双引号（" "）内。例如：'DELIMITER'= "'"。 DLI表还支持\001（^A）和\017（^Q），例如：'DELIMITER'='\001(^A)'，'DELIMITER'='\017(^Q)'。 QUOTECHAR：可以在导入命令中指定引号字符。默认值为"。 配置项为OPTIONS('QUOTECHAR'='"') COMMENTCHAR：可以在导入命令中指定注释字符。在导入操作期间，如果在行的开头遇到注释字符，那么该行将被视为注释，并且不会被导入。默认值为＃。 配置项为OPTIONS('COMMENTCHAR'='#') HEADER：用来表示源文件是否有表头。取值范围为“true”和“false”。“true”表示有表头，“false”表示无表头。默认值为“false”。如果没有表头，可以在导入命令中指定FILEHEADER参数提供表头。 配置项为OPTIONS('HEADER'='true') FILEHEADER：如果源文件中没有表头，可在LOAD DATA命令中提供表头。 OPTIONS('FILEHEADER'='column1,column2') ESCAPECHAR：如果用户想在CSV上对Escape字符进行严格验证，可以提供Escape字符。默认值为“\\”。 配置项为OPTIONS('ESCAPECHAR'='\\') 如果在CSV数据中输入ESCAPECHAR，该ESCAPECHAR必须在双引号（" "）内。例如："a\b"。 MAXCOLUMNS：该可选参数指定了在一行中，CSV解析器解析的最大列数。 配置项为OPTIONS('MAXCOLUMNS'='400') 表2 MAXCOLUMNS 可选参数名称 默认值 最大值 MAXCOLUMNS 2000 20000 设置MAXCOLUMNS Option的值后，导入数据会对executor的内存有要求，所以导入数据可能会由于executor内存不足而失败。 DATEFORMAT：指定列的日期格式。 OPTIONS('DATEFORMAT'='dateFormat') 默认值为：yyyy-MM-dd。 日期格式由Java的日期模式字符串指定。在Java的日期和时间模式字符串中，未加单引号(')的字符'A' 到'Z' 和'a' 到'z' 被解释为模式字符，用来表示日期或时间字符串元素。若模式字符使用单引号 (') 引起来，则在解析时只进行文本匹配，而不进行解析。Java模式字符定义请参见表3。 表3 日期及时间模式字符定义 模式字符 日期或时间元素 示例 G 纪元标识符 AD y 年份 1996; 96 M 月份 July; Jul; 07 w 年中的周数 27(该年的第27周) W 月中的周数 2(该月的第2周) D 年中的天数 189(该年的第189天) d 月中的天数 10(该月的第10天) u 星期中的天数 1 = 星期一, ..., 7 = 星期日 a am/pm 标记 pm(下午时) H 24小时数(0-23) 2 h 12小时数(1-12) 12 m 分钟数 30 s 秒数 55 S 毫秒数 978 z 时区 Pacific Standard Time; PST; GMT-08:00 TIMESTAMPFORMAT：指定列的时间戳格式。 OPTIONS('TIMESTAMPFORMAT'='timestampFormat') 默认值为：yyyy-MM-dd HH:mm:ss。 时间戳格式由Java的时间模式字符串指定。Java时间模式字符串定义详见表3 日期及时间模式字符定义。

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

语法格式 将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 2 3 4 5 6 7 INSERT OVERWRITE TABLE DLI_TABLE SELECT field1,field2... [FROM DLI_TEST] [WHERE where_condition] [LIMIT num] [GROUP BY field] [ORDER BY field] ...;

关键字 PARTITION BY：可以用一个或多个键分区。和GROUP BY子句类似，PARTITION BY将表按分区键分区，每个分区是一个窗口，窗口函数作用于各个分区。单表分区数最多允许7000个。 ORDER BY：决定窗口函数求值的顺序。可以用一个或多个键排序。通过ASC或DESC决定升序或降序。窗口由WINDOW子句指定。如果不指定，默认窗口等同于ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即窗口从表或分区（如果OVER子句中用PARTITION BY分区）的初始处到当前行。 WINDOW：通过指定一个行区间来定义窗口。 CURRENT ROW：表示当前行。 num PRECEDING：定义窗口的下限，即窗口从当前行向前数num行处开始。 UNBOUNDED PRECEDING：表示窗口没有下限。 num FOLLOWING：定义窗口的上限，即窗口从当前行向后数num行处结束。 UNBOUNDED FOLLOWING：表示窗口没有上限。 ROWS BETWEEN…和RANGE BETWEEN…的区别： ROW为物理窗口，即根据ORDER BY子句排序后，取前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。 RANGE为逻辑窗口，即指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内。 窗口有以下多种场景，如 窗口只包含当前行。 1 ROWS BETWEEN CURRENT ROW AND CURRENT ROW 窗口从当前行向前数3行开始，到当前行向后数5行结束。 1 ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWING 窗口从表或分区的开头开始，到当前行结束。 1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 窗口从当前行开始，到表或分区的结尾结束。 1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 窗口从表或分区的开头开始，到表或分区的结尾结束。 1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

语法格式 1 2 3 4 5 SELECT window_func(args) OVER ([PARTITION BY col_name, col_name, ...] [ORDER BY col_name, col_name, ...] [ROWS | RANGE BETWEEN (CURRENT ROW | (UNBOUNDED |[num]) PRECEDING) AND (CURRENT ROW | ( UNBOUNDED | [num]) FOLLOWING)]);

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

语法格式 将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 2 3 4 5 6 7 INSERT OVERWRITE TABLE DLI_TABLE SELECT field1,field2... [FROM DLI_TEST] [WHERE where_condition] [LIMIT num] [GROUP BY field] [ORDER BY field] ...;