华为云用户手册

示例代码 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表salary，并添加数据，命令示例如下： CREATE EXTERNAL TABLE salary ( dept_id STRING, -- 部⻔ userid string, -- 员⼯ID sal INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile; 添加数据如下： d1,user1,1000 d1,user2,2000 d1,user3,3000 d2,user4,4000 d2,user5,5000 示例 返回每个部门的名称 当 dept_id 的值为d1时，返回 DLI ；值为d2时，返回 MRS ；其他场景返回Others。 select dept, decode1(dept, 'd1', 'DLI', 'd2', 'MRS', 'Others') as result from sale_detail; 返回结果： d1 DLI d2 MRS d3 Others d4 Others d5 Others

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 expression 是 所有数据类型。 要比较的表达式。 search 是 与expression一致。 与expression进行比较的搜索项。 result 是 所有数据类型。 search和expression的值匹配时的返回值。 default 否 与result一致。 如果所有的搜索项都不匹配，则返回default值，如果未指定，则返回NULL。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 DATE 或 STRING 代表起始日期。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 datepart 是 STRING 代表需要返回的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

示例代码 静态数据示例 返回2023-01-01 00:00:00。 select datetrunc('2023-08-14 17:00:00', 'yyyy'); 返回2023-08-01 00:00:00。 select datetrunc('2023-08-14 17:00:00', 'month'); 返回2023-08-14。 select datetrunc('2023-08-14 17:00:00', 'DD'); 返回2023-01-01。 select datetrunc('2023-08-14', 'yyyy'); 返回2023-08-14 17:00:00。 select datetrunc('2023-08-14 17:11:11', 'hh'); 返回NULL。 select datetrunc('2023-08-14', null);

示例代码 返回2023。 select datepart(date '2023-08-14 17:00:00', 'yyyy'); 返回2023。 select datepart('2023-08-14 17:00:00', 'yyyy'); 返回59。 select datepart('2023-08-14 17:59:59', 'mi') 返回NULL。 select datepart(date '2023-08-14', null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 DATE 或 STRING 代表起始日期。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 datepart 是 STRING 代表需要返回的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date1 是 DATE 或 STRING 计算两个时间date1、date2的日期差值中的被减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 date2 是 DATE 或 STRING 计算两个时间date1、date2的日期差值的减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 datepart 是 STRING 代表需要返回的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

示例代码 返回14400。 select datediff1('2023-06-30 00:00:00', '2023-06-20 00:00:00', 'mi'); 返回10。 select datediff1(date '2023-06-21', date '2023-06-11', 'dd'); 返回NULL。 select datediff1(date '2023-05-21', date '2023-05-10', null); 返回NULL。 select datediff1(date '2023-05-21', null, 'dd');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date1 是 DATE 或 STRING 计算两个时间date1、date2的日期差值中的被减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 date2 是 DATE 或 STRING 计算两个时间date1、date2的日期差值的减数。 格式为： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 DATE 或 STRING 代表起始日期。 支持以下格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 delta 是 BIGINT 代表修改幅度。 datepart 是 BIGINT 代表修改的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

示例代码 返回2023-08-15 17:00:00。加1天。 select dateadd( '2023-08-14 17:00:00', 1, 'dd'); 返回2025-04-14 17:00:00。加20个月，月份溢出，年份加1。 select dateadd('2023-08-14 17:00:00', 20, 'mm'); 返回2023-09-14 17:00:00。 select dateadd('2023-08-14 17:00:00', 1, 'mm'); 返回2023-09-14。 select dateadd('2023-08-14', 1, 'mm'); 假设当前时间为2023-08-14 17:00:00，返回2023-08-13 17:00:00。 select dateadd(getdate(),-1,'dd'); 返回NULL。 select dateadd(date '2023-08-14', 1, null);

示例代码 返回2023-08-12。减2天。 select date_sub('2023-08-14 17:00:00', 2); 返回2023-08-15。增1天。 select date_sub(date'2023-08-14', -1); 假设当前时间为2023-08-14 17:00:00，返回2023-08-13。 select date_sub(getdate(),1); 返回NULL。 select date_sub('2023-08-14 17:00:00', null);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 start_date 是 DATE 或 STRING 代表起始日期。 支持以下格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 days 是 BIGINT 代表需要减少的天数。 days大于0，则为增加天数。 days小于0，则减去天数。 days等于0，即加0天，日期不变。 days值为NULL时，返回NULL。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 DATE 或 STRING 代表待转换的日期。 格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 format 是 STRING 代表需要转换的格式。 格式为代表年月日时分秒的时间单位与任意字符的组合，其中： yyyy代表年份。 MM代表月份。 dd代表天。 HH代表24小时制时。 hh代表12小时制时。 mm代表分钟。 ss代表秒。

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 start_date 是 DATE 或 STRING 代表起始日期。 支持以下格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 days 是 BIGINT 代表需要增加天的数量。 days大于0，则为增加天数。 days小于0，则减去天数。 days等于0，即加0天，日期不变。 days值为NULL时，返回NULL。

示例代码 返回2023-03-01。加1天，结果超出当年2月份的最后1天，实际值为下个月的第1天。 select date_add('2023-02-28 00:00:00', 1); 返回2023-02-27。减1天。 select date_add(date '2023-02-28', -1); 返回2023-03-20。 select date_add('2023-02-28 00:00:00', 20); 假设当前时间为2023-08-14 16:00:00，返回2023-08-13。 select date_add(getdate(),-1); 返回NULL。 select date_add('2023-02-28 00:00:00', null);

示例代码 为便于理解函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表salary，并添加数据，命令示例如下： CREATE EXTERNAL TABLE salary ( dept STRING, -- 部⻔名称 userid string, -- 员⼯ID sal INT -- 薪⽔ ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile; 添加数据如下： d1,user1,1000 d1,user2,2000 d1,user3,3000 d2,user4,4000 d2,user5,5000 统计小于等于当前薪水的人数占比 select dept, userid, sal, cume_dist() over(order by sal) as cume1 from salary; -- 结果： d1 user1 1000 0.2 d1 user2 2000 0.4 d1 user3 3000 0.6 d2 user4 4000 0.8 d2 user5 5000 1.0 按部⻔分组统计⼩于等于当前薪⽔的⼈数的⽐例 select dept, userid, sal, cume_dist() over (partition by dept order by sal) as cume2 from salary; -- 结果： d1 user1 1000 0.3333333333333333 d1 user2 2000 0.6666666666666666 d1 user3 3000 1.0 d2 user4 4000 0.5 d2 user5 5000 1.0 按照sal降序排序后，结果就是统计 ⼤于等于 当前薪⽔的⼈数的⽐例 select dept, userid, sal, cume_dist() over(order by sal desc) as cume3 from salary; -- 结果： d2 user5 5000 0.2 d2 user4 4000 0.4 d1 user3 3000 0.6 d1 user2 2000 0.8 d1 user1 1000 1.0 select dept, userid, sal, cume_dist() over(partition by dept order by sal desc) as cume4 from salary; -- 结果： d1 user3 3000 0.3333333333333333 d1 user2 2000 0.6666666666666666 d1 user1 1000 1.0 d2 user5 5000 0.5 d2 user4 4000 1.0

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

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

示例代码 计算所有仓库表中的记录数。命令示例如下： select count(*) from warehouse; 返回结果如下： _c0 10 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，计算各仓库（warehouseId）的商品数。命令示例如下： select warehouseId, count(*) from warehouse group by warehouseId; 返回结果如下： warehouseId _c1 city1 6 city2 5 city3 6 示例3：通过distinct去重，计算仓库数量。命令示例如下： select count(distinct warehouseId) from warehouse; 返回结果如下： _c0 3

示例代码 计算所有商品库存（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

参数说明 表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格式为浮点数格式、整数格式、字符串格式。

示例代码 -返回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);

示例代码 将字符串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

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

示例代码 返回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);

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; 支持 支持