华为云用户手册

示例代码 计算所有商品库存（items）和价格（price）的相关系数。命令示例如下： select corr(items,price) from warehouse; 返回结果如下： _c0 1.242355 与group by配合使用，对所有商品按照仓库（warehourseId）进行分组，并计算同组商品库存（items）和价格（price）的相关系数。命令示例如下： select warehourseId, corr(items,price) from warehourse group by warehourseId; 返回结果如下： warehouseId _c1 city1 0.43124 city2 0.53344 city3 0.73425

示例代码 -返回8。 select conv('1000', 2, 10); 返回B。 select conv('1011', 2, 16); 返回703710。 select conv('ABCDE', 16, 10); 返回27。 select conv(1000.123456, 3.123456, 10.123456); 返回18446744073709551589。 select conv(-1000.123456, 3.123456, 10.123456); 返回NULL。 select conv('1100', null, 10);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 num 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 需要进行转换进制的数。 参数num格式为浮点数格式、整数格式、字符串格式。 from_base 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 被转换的进制from_base。 参数from_base格式为浮点数格式、整数格式、字符串格式。 to_base 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 转化至的进制to_base。 参数to_base格式为浮点数格式、整数格式、字符串格式。

示例代码 将字符串ABC和DEF通过:连接。命令示例如下。 返回ABC:DEF。 select concat_ws(':','ABC','DEF'); 任一输入参数为NULL。命令示例如下。 返回avg:18。 select concat_ws(':','avg',null,'18'); 将ARRAY数组array('name', 'lilei')中的元素通过:连接。命令示例如下。 返回name:lilei。 select concat_ws(':',array('name', 'lilei'));

示例代码 连接ARRAY数组array(1, 2)和array(2, -2)。命令示例如下。 返回[1, 2, 2, -2]。 select concat(array(1, 2), array(2, -2)); 任一ARRAY数组为NULL。命令示例如下。 返回NULL。 select concat(array(10, 20), null); 连接字符串ABC和DEF。命令示例如下。 返回ABCDEF。 select concat('ABC','DEF'); 输入为空。命令示例如下。 返回NULL。 select concat(); 任一字符串输入为NULL。命令示例如下。 返回NULL。 select concat('abc', 'def', null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 a 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 参数a的格式包括浮点数格式、整数格式、字符串格式。 代表需要被四舍五入的值。 该命令与传统四舍五入方式的区别在于，对数字5进行操作时，由前一位数字来决定，前一位数字为奇数，增加一位，前一位数字为偶数，舍弃一位。 参数a非DOUBLE类型时，会隐式转换为DOUBLE类型后参与运算。 d 否 DOUBLE、BIGINT、DECIMAL、STRING类型。 代表需要四舍五入到的位数。 参数d非INT类型时，会隐式转换为INT类型后参与运算。

示例代码 计算所有仓库的平均商品数（items）。命令示例如下： select avg(items) from warehouse; 返回结果如下： _c0 100.0 与group by配合使用，计算每个仓库中所有商品的平均库存。命令示例如下： select warehourseId, avg(items) from warehourse group by warehourseId; 返回结果如下： warehouseId _c1 city1 155 city2 101 city3 194

示例代码 返回2023-05-26。 select add_months('2023-02-26',3); 返回2023-05-14。 select add_months('2023-02-14 21:30:00',3); 返回NULL。 select add_months('20230815',3); 返回NULL。 select add_months('2023-08-15 20:00:00',null);

返回值说明 返回开始日期startdate增加num_months个月后的日期，返回值格式为yyyy-mm-dd。 返回值date类型的日期值。 startdate非DATE或STRING类型时，返回报错，错误信息：data type mismatch。 startdate为DATE或STRING类型，但不符合日期值的入参格式时，返回NULL。 startdate值为NULL时，返回报错。 num_months值为NULL时，返回NULL。

Spark开源命令支持说明 本章节介绍了目前 DLI 对开源的Spark SQL语法的支持情况。详细的语法、参数说明，示例等信息请参考Spark官方文档。 表1 DLI Spark开源命令支持说明 功能描述 语法示例 DLI Spark 2.4.5 DLI Spark 3.3.1 创建数据库 CREATE DATABASE testDB; 支持 支持 创建DLI表 create table if not exists testDB.testTable1( id int, age int, money double ); 支持 支持 创建OBS表 create table if not exists testDB.testTable2( id int, age int, money double ) LOCATION 'obs://bucketName/filePath' partitioned by (dt string) row format delimited fields terminated by ',' STORED as TEXTFILE; 支持 支持 插入测试数据 insert into table testDB.testTable2 values (1, 18, 3.14, "20240101" ), (2, 18, 3.15, "20240102" ); 支持 支持 修改数据库属性 ALTER DATABASE testDB SET DBPROPERTIES ('Edited-by' = 'John'); 不支持 不支持 修改数据库在OBS上的文件存放路径 ALTER DATABASE testDB SET LOCATION 'obs://bucketName/filePath'; 不支持 不支持 修改表名 ALTER TABLE testDB.testTable1 RENAME TO testDB.testTable101; 支持 支持 修改表的分区名 ALTER TABLE testDB.testTable2 PARTITION ( dt='20240101') RENAME TO PARTITION ( dt='20240103'); 只支持OBS表的分区名，且OBS上的文件存储路径不会变。 支持 支持 添加列 ALTER TABLE testDB.testTable1 ADD COLUMNS (name string); 支持 支持 删除列 ALTER TABLE testDB.testTable1 DROP columns (money); 不支持 不支持 修改列名称 ALTER TABLE testDB.testTable1 RENAME COLUMN age TO years_of_age; 不支持 不支持 修改列注释 ALTER TABLE testDB.testTable1 ALTER COLUMN age COMMENT "new comment"; 不支持 支持 替换指定列 ALTER TABLE testDB.testTable1 REPLACE COLUMNS (name string, ID int COMMENT 'new comment'); 不支持 不支持 自定义表属性 ALTER TABLE testDB.testTable1 SET TBLPROPERTIES ('aaa' = 'bbb'); 支持 支持 添加或修改表注释 ALTER TABLE testDB.testTable1 SET TBLPROPERTIES ('comment' = 'test'); 支持 不支持 修改表的存储格式 ALTER TABLE testDB.testTable1 SET fileformat csv; 不支持 不支持 删除表属性 ALTER TABLE testDB.testTable1 UNSET TBLPROPERTIES ('test'); 支持 支持 删除表的注释 ALTER TABLE testDB.testTable1 UNSET TBLPROPERTIES ('comment'); 支持 支持 展示列信息 DESCRIBE database_name.table_name col_name; 示例：DESCRIBE testDB.testTable1 id; 支持 支持 展示列信息 DESCRIBE table_name table_name.col_name; 示例：DESCRIBE testTable1 testTable1.id; 仅支持查看当前数据库下表的列信息。 支持 支持 返回查询语句的元数据信息 DESCRIBE QUERY SELECT age, sum(age) FROM testDB.testTable1 GROUP BY age; 不支持 支持 返回插入数据的元数据信息 DESCRIBE QUERY VALUES(100, 10, 10000.20D) AS testTable1(id, age, money); 不支持 支持 返回表的元数据信息 DESCRIBE QUERY TABLE testTable1; 不支持 支持 返回表中某列的元数据信息 DESCRIBE FROM testTable1 SELECT age; 不支持 支持 返回表的建表语句 SHOW CREATE TABLE testDB.testTable1 AS SERDE; Spark 3.3.1 执行该语句时，仅适用于查询Hive表的建表语句。 不支持 支持 返回表的建表语句 SHOW CREATE TABLE testDB.testTable1; 支持 支持

参数说明 表1 参数描述 参数 是否必选 描述 db_name 否 Database名称，由字母、数字和下划线（_）组成。不能是纯数字，且不能以下划线开头。 table_name 是 表名称。 col_name 是 列字段名称。col_name必须是已存在的列。 col_type 是 列数据类型。本语法不支持修改列数据类型，这里指定的是创建表时指定的列数据类型。 col_comment 是 修改后的列注释信息。注释内容为长度不超过1024字节的有效字符串。

示例 示例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'; 当设置禁止分区表的生命周期功能后，该表的所有分区的生命周期功能都会被禁止。

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

示例 示例1：修改表的生命周期，开启test_lifecycle_exists表生命周期，并将生命周期设为50天。 alter table test_lifecycle_exists SET TBLPROPERTIES("dli.lifecycle.days"='50'); 示例2：对已存在且未设置生命周期的分区表或非分区表开启表的生命周期,开启test_lifecycle_exists表生命周期，并将生命周期设为50天。 alter table test_lifecycle_exists SET TBLPROPERTIES( "dli.lifecycle.days"='50', "dli.table.lifecycle.status"='enable' );

参数说明 表1 参数说明 参数名称 是否必选 参数说明 table_name 是 需要设置生命周期的表名。 dli.lifecycle.days 是 设置的生命周期时间，只能为正整数，单位为天。 external.table.purge 否 仅OBS表支持配置该参数。 是否需要在删除表或分区时，清除path路径下的数据。默认不删除。 设置'external.table.purge'='true'时： 非分区OBS表配置删除文件后，表目录也会删除。 分区OBS表自定义分区数据也会删除。 dli.lifecycle.trash.dir 否 仅OBS表支持配置该参数。 设置'external.table.purge'='true'时，清除数据的备份目录，默认七天后删除备份数据。

示例 DataSource语法新建test_datasource_lifecycle表，生命周期为100天 1 2 3 CREATE TABLE test_datasource_lifecycle(id int) USING parquet TBLPROPERTIES( "dli.lifecycle.days"=100); Hive语法新建test_hive_lifecycle表，生命周期为100天。 1 2 3 CREATE TABLE test_hive_lifecycle(id int) stored as parquet TBLPROPERTIES( "dli.lifecycle.days"=100); DataSource语法新建test_datasource_lifecycle_obs表，生命周期为100天，过期时默认删除数据且数据备份至目录'obs://dli-test/'。 1 2 3 4 CREATE TABLE test_datasource_lifecycle_obs(name string, id int) USING parquet OPTIONS (path "obs://dli-test/xxx") TBLPROPERTIES( "dli.lifecycle.days"=100, "external.table.purge"='true', "dli.lifecycle.trash.dir"='obs://dli-test/Lifecycle-Trash' ); Hive语法新建test_hive_lifecycle_obs表，生命周期为100天，过期时默认删除数据且数据备份至目录'obs://dli-test/'。 1 2 3 4 CREATE TABLE test_hive_lifecycle_obs(name string, id int) STORED AS parquet LOCATION 'obs://dli-test/xxx' TBLPROPERTIES( "dli.lifecycle.days"=100, "external.table.purge"='true', "dli.lifecycle.trash.dir"='obs://dli-test/Lifecycle-Trash' );

语法格式 DataSource语法创建DLI表 CREATE TABLE table_name(name string, id int) USING parquet TBLPROPERTIES( "dli.lifecycle.days"=1 ); Hive语法创建DLI表 CREATE TABLE table_name(name string, id int) stored as parquet TBLPROPERTIES( "dli.lifecycle.days"=1 ); DataSource语法创建OBS表 CREATE TABLE table_name(name string, id int) USING parquet OPTIONS (path "obs://dli-test/table_name") TBLPROPERTIES( "dli.lifecycle.days"=1, "external.table.purge"='true', "dli.lifecycle.trash.dir"='obs://dli-test/Lifecycle-Trash' ); Hive语法创建OBS表 1 2 3 4 CREATE TABLE table_name(name string, id int) STORED AS parquet LOCATION 'obs://dli-test/table_name' TBLPROPERTIES( "dli.lifecycle.days"=1, "external.table.purge"='true', "dli.lifecycle.trash.dir"='obs://dli-test/Lifecycle-Trash' );

表的回收规则 在创建表时通过TBLPROPERTIES指定表的生命周期。 非分区表 如果表是非分区表，根据每张表的最后修改时间，经过生命周期时间后判断是否要回收此表。 分区表 如果是分区表，则根据各分区的最后一次表数据被修改的时间（LAST_AC CES S_TIME）判断该分区是否该被回收。分区表的最后一个分区被回收后，该表不会被删除。 分区表不支持设置分区级的生命周期，仅支持表级别的生命周期管理。 生命周期回收为每天定时启动，扫描全量分区。 生命周期回收为每天定时启动，扫描全量分区的最后一次表数据被修改的时间（LAST_ACCESS_TIME）需要超过生命周期指定的时间才回收。 假设某个分区表生命周期为1天，该分区数据最后一次被修改的时间是2023年05月20日15时。如果在2023年05月20日15时之前扫描此表（不到一天），则不会回收表分区。如果2023年05月20日回收扫描时发现表分区最后一次表数据被修改的时间（LAST_ACCESS_TIME）超过生命周期指定的时间，则上述分区会被回收。 生命周期主要提供定期回收表或分区的功能，每天根据服务的繁忙程度，不定时回收。不能确保表或分区的生命周期到期后，立刻被回收。 删除表后，表的所有属性信息全部会删除，包括生命周期。新建同名表后，表的生命周期以新设置的属性为准。

参数说明 表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] ...;

关键字 表1 CREATE TABLE关键字说明 参数 描述 url Oracle的连接地址。 Oracle url支持以下格式： 格式一：jdbc:oracle:thin:@host:port:SID，其中SID是oracle数据库的唯一标识符。 格式二：jdbc:oracle:thin:@//host:port/service_name；这种方式是Oracle推荐的，对于集群来说，每个节点的SID可能不一致，但ServiceName是一致的，包含所有节点。 driver Oracle驱动类名: oracle.jdbc.driver.OracleDriver dbtable 指定在Oracle关联的表名，或者"用户名.表名"，例如：public.table_name。 user Oracle用户名。 password Oracle用户名密码。 resource Oracle驱动包的OBS路径。 例如：obs://rest-authinfo/tools/oracle/driver/ojdbc6.jar resource中定义的driver jar包如果被更新，需要重启队列，才会生效。

示例 创建Oracle跨源表 1 2 3 4 5 6 7 8 9 CREATE TABLE IF NOT EXISTS oracleTest USING ORACLE OPTIONS ( 'url'='jdbc:oracle:thin:@//192.168.168.40:1521/helowin', 'driver'='oracle.jdbc.driver.OracleDriver', 'dbtable'='test.Student', 'user' = 'test', 'password' = 'test', 'resource' = 'obs://rest-authinfo/tools/oracle/driver/ojdbc6.jar' );

语法格式 1 2 3 4 5 6 7 8 9 CREATE TABLE [IF NOT EXISTS] TABLE_NAME USING ORACLE OPTIONS ( 'url'='xx', 'driver'='DRIVER_NAME', 'dbtable'='db_in_oracle.table_in_oracle', 'user' = 'xxx', 'password' = 'xxx', 'resource' = 'obs://rest-authinfo/tools/oracle/driver/ojdbc6.jar' );

功能描述 Spark为了提高性能会缓存Parquet的元数据信息。当更新了Parquet表时，缓存的元数据信息未更新，导致Spark SQL查询不到新插入的数据作业执行报错，报错信息参考如下： DLI.0002: FileNotFoundException: getFileStatus on error message 该场景下就需要使用REFRESH TABLE来解决该问题。REFRESH TABLE是用于重新整理某个分区的文件，重用之前的表元数据信息，能够检测到表的字段的增加或者减少，主要用于表中元数据未修改，表的数据修改的场景。

示例 修改表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;

语法格式 开启多版本功能 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;

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

功能描述 在DLI数据多版本功能开启后，过期的备份数据后续在执行insert overwrite或者truncate语句时会被系统直接清理。OBS并行文件系统可以通过配置回收站加速删除操作过期的备份数据。通过在表属性添加配置“dli.multi.version.trash.dir”即可开启回收站功能。开启和关闭多版本语法请参考开启或关闭数据多版本。 DLI数据多版本功能当前仅支持通过Hive语法创建的OBS表，具体建表SQL语法可以参考使用Hive语法创建OBS表。

参数说明 表1 参数说明 参数 描述 db_name 数据库名称，由字母、数字和下划线（_）组成。不能是纯数字，且不能以数字和下划线开头。 table_name 表名称。 obs桶多版本回收站目录 当前OBS表所在桶下的一个目录，您可以根据需要调整目录路径。比如当前OBS表所在路径为“obs://bucketName/filePath”，OBS表目录下已创建Trash目录，则该回收站目录可以指定为“obs://bucketName/filePath/Trash”。

示例 在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';