华为云用户手册

示例代码 计算所有商品库存（items）的方差。命令示例如下： select variance(items) from warehouse; --等效于如下语句。 select var_pop(items) from warehouse; 返回结果如下： _c0 203.42352 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的方差。命令示例如下： select warehouseId, variance(items) from warehourse group by warehouseId; --等效于如下语句。 select warehouseId, var_pop(items) from warehourse group by warehouseId; 返回结果如下： 1 2 3 4 warehouseId _c1 city1 19.23124 city2 17.23344 city3 12.43425

语法格式 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE [IF NOT EXISTS] TABLE_NAME( FIELDNAME1 FIELDTYPE1, FIELDNAME2 FIELDTYPE2) USING CSS OPTIONS ( 'es.nodes'='xx', 'resource'='type_path_in_ CS S', 'pushdown'='true', 'strict'='false', 'batch.size.entries'= '1000', 'batch.size.bytes'= '1mb', 'es.nodes.wan.only' = 'true', 'es.mapping.id' = 'FIELDNAME');

示例 1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS dli_to_css (doc_id String, name string, age int) USING CSS OPTIONS ( 'es.nodes' ='to-css-1174404703-LzwpJEyx.datasource.com:9200', 'resource' ='/dli_index/dli_type', 'pushdown' ='false', 'strict' ='true', 'es.nodes.wan.only' ='true', 'es.mapping.id' ='doc_id');

示例代码 计算所有商品的最低库存（items）。命令示例如下： select min(items) from warehouse; 返回结果如下： _c0 600 与group by配合使用，求每个仓库的最低库存。命令示例如下： select warehouseId, min(items) from warehouse group by warehouseId; 返回结果如下： warehouseId _c1 city1 15 city2 10 city3 19

示例代码 返回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时，从startdate中减少指定的天数。 当days小于0时，向startdate中增加指定的天数。 当days等于0时，即加0天，日期不变。 当days值为NULL时，返回NULL。

分析窗口函数概览 DLI 所支持的分析窗口函数如分析窗口函数介绍所示。 表1 分析窗口函数介绍 函数 命令格式 返回值 功能简介 cume_dist cume_dist() DOUBLE 用于求累计分布，相当于求分区中大于等于或小于等于当前行的数据在分区中的占比。 first_value first_value(col) 参数的数据类型 返回结果集中某列第一条数据的值。 last_value last_value(col) 参数的数据类型 返回结果集中某列最后一条数据的值。 lag lag (col,n,DEFAULT) 参数的数据类型 用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。 lead lead (col,n,DEFAULT) 参数的数据类型 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）。 percent_rank percent_rank() DOUBLE 为窗口的ORDER BY子句所指定列中值的返回秩，但以介于0和1之间的小数形式表示，计算方法为 (RANK - 1)/(- 1)。 rank rank() INT 计算一个值在一组值中的排位。如果出现并列的情况，RANK函数会在排名序列中留出空位。 row_number row_number() over (order by col_1[,col_2 ...]) INT 为每一行指派一个唯一的编号。 父主题： 分析窗口函数

示例代码 例如 table1 是分区表，该表对应的分区为20120801和20120802，且都有数据。则以下语句中max_pt返回值为‘20120802’。DLI SQL语句会读出pt=‘20120802’分区下的数据。 命令示例如下。 select * from tablel where pt = max_pt('dbname.table1'); 等效于如下语句。 select * from table1 where pt = (select max(pt) from dbname.table1);

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待替换的字符。 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。 replace_string 是 STRING 将匹配pattern的字符串替换后的字符串。 occurrence 否 BIGINT 必须大于等于1，表示将第occurrence次匹配的字符串替换为replace_string，为1时表示替换所有匹配的子串。为其他类型或小于1时，返回报错。默认值为1。 说明： 该字段仅Spark3.1.1版本的功能适用。

说明 separator指语法中的分隔符或替代符，仅支持CHAR类型。 FIELDS TERMINATED BY指定表中字段级别的分隔符，仅支持CHAR类型。 COLLECTION ITEMS TERMINATED BY指定集合级别的分隔符，仅支持CHAR类型 MAP KEY TERMINATED BY仅用于指定MAP类型中的key与vaule之间的分隔符号，仅支持CHAR类型。 LINES TERMINATED BY指定行与行之间的分隔符，目前只支持“\n”。 使用NULL DEFINED AS子句可以指定NULL的格式。 SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]可利用以下语句实现NULL值转换为空字符串。 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' with serdeproperties('serialization.null.format' = '')

格式 ROW FORMAT DELIMITED [FIELDS TERMINATED BY separator] [COLLECTION ITEMS TERMINATED BY separator] [MAP KEYS TERMINATED BY separator] [LINES TERMINATED BY separator] [NULL DEFINED AS separator] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

示例代码 去除字符串 yxabcxx 的左右空格。命令示例如下。 返回字符串yxabcxx。 select trim(' yxabcxx '); 等效于如下语句。 select trim(both from ' yxabcxx '); select trim(from ' yxabcxx '); 去除字符串yxabcxx左右两端所有字符都在集合xy中的子串。 返回abc，只要左右两端遇到x或者y就会被去掉。 select trim('xy', 'yxabcxx'); 等效于如下语句。 select trim(both 'xy' from 'yxabcxx'); 输入参数为NULL。命令示例如下。 返回NULL。 select trim(null); select trim(null, 'yxabcxx');

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

示例代码 返回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 datepart 是 STRING 代表需要返回的时间单位。 参数datepart支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。 yyyy代表年份。 MM代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

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

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

示例代码 假设列int_test中的元素为1、2、3、4，类型为INT类型。 返回3.0999999999999996。 select percentile(int_test,0.7) FROM int_test; 返回3.997。 select percentile(int_test,0.999) FROM int_test; 返回2.5。 select percentile(int_test,0.5) FROM int_test; 返回[1.3,1.9,2.5,2.8,3.7]。 select percentile (int_test,ARRAY(0.1,0.3,0.5,0.6,0.9)) FROM int_test;

示例代码 去除字符串 yxabcxx 的右边空格。命令示例如下。 返回字符串 yxabcxx。 select rtrim('yxabcxx '); 等效于如下语句。 select trim(trailing from ' yxabcxx '); 去除字符串yxabcxx右端所有字符都在集合xy中的子串。 返回yxabc，只要右端遇到x或者y就会被去掉。 select rtrim('xy', 'yxabcxx'); 等效于如下语句。 select trim(trailing 'xy' from 'yxabcxx'); 输入参数为NULL。命令示例如下。 返回NULL。 select rtrim(null); select ltrim('yxabcxx', 'null');

示例代码 返回2023-08-16 10:54:36 select to_date1('2023-08-16 10:54:36','yyyy-mm-dd hh:mi:ss'); 返回2023-08-16 00:00:00。 select to_date1('2023-08-16','yyyy-mm-dd'); 返回NULL。 select to_date1(null); 返回2023-08-16。 select to_date1('2023-08-16 10:54:36');

参数说明 表1 参数说明 参数 是否必选 参数类型 说明 date 是 STRING 要转换的字符串。 格式： yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy-mm-dd hh:mi:ss.ff3 format 是 STRING 代表需要转换的日期格式。 STRING类型常量，不支持日期扩展格式。 format:格式为代表年月日时分秒的时间单位与任意字符的组合，其中： yyyy代表年份。 mm代表月份。 dd代表天。 hh代表小时。 mi代表分钟。 ss代表秒。

示例代码 计算所有商品库存（items）的样本偏差。命令示例如下： select stddev_samp(items) from warehouse; 返回结果如下： +------------+ | _c0 | +------------+ | 1.342355 | +------------+ 与group by配合使用，对所有商品按照仓库（warehouseId）进行分组，并计算同组商品库存（items）的样本偏差。命令示例如下： select warehouseId, stddev_samp(items) from warehourse group by warehouseId; 返回结果如下： +------------+------------+ | warehouseId| _c1 | +------------+------------+ | city1 | 1.23124 | | city2 | 1.23344 | | city3 | 1.43425 | +------------+------------+

示例代码 去除字符串" abc"的左边空格。命令示例如下。 返回字符串abc 。 select ltrim(' abc'); 等效于如下语句。 select trim(leading from ' abc'); leading代表去除字符串前面的空格 输入参数为NULL。命令示例如下。 返回NULL。 select ltrim(null); select ltrim('xy', null); select ltrim(null, 'xy'); 去除字符串yxlucyxx左端所有字符都在集合xy中的子串。 返回lucyxx，只要左端遇到x或者y就会被去掉。 select ltrim( 'xy','yxlucyxx'); 等效于如下语句。 select trim(leading 'xy' from 'yxlucyxx');

数学函数概览 DLI所支持的数学函数如数学函数所示。 表1 数学函数 函数 命令格式 返回值 功能简介 abs abs(DOUBLE a) DOUBLE或INT 取绝对值。 acos acos(DOUBLE a) DOUBLE 返回给定角度a的反余弦值。 asin asin(DOUBLE a) DOUBLE 返回给定角度a的反正弦值。 atan atan(DOUBLE a) DOUBLE 返回给定角度a的反正切值。 bin bin(BIGINT a) STRING 返回二进制格式。 bround bround(DOUBLE a) DOUBLE HALF_EVEN模式四舍五入，与传统四舍五入方式的区别在于，对数字5进行操作时，由前一位数字来决定，前一位数字为奇数，增加一位，前一位数字为偶数，舍弃一位。例如：bround(7.5)=8.0，bround(6.5)=6.0 bround bround(DOUBLE a, INT d) DOUBLE 保留小数点后d位，d位之后数字以HALF_EVEN模式四舍五入。与传统四舍五入方式的区别在于，对数字5进行操作时，由前一位数字来决定，前一位数字为奇数，增加一位，前一位数字为偶数，舍弃一位。例如：bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4。 cbrt cbrt(DOUBLE a) DOUBLE 返回a的立方根。 ceil ceil(DOUBLE a) DECIMAL 将参数向上舍入为最接近的整数。例如：ceil(21.2)，返回22。 conv conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) STRING 进制转换，将from_base进制下的num转化为to_base进制下面的数。例如：将5从十进制转换为四进制，conv(5,10,4)=11。 cos cos(DOUBLE a) DOUBLE 返回给定角度a的余弦值。 cot1 cot1(DOUBLE a) DOUBLE或DECIMAL类型 计算number的余切函数，输入为弧度值。 degress degrees(DOUBLE a) DOUBLE 返回弧度所对应的角度。 e e() DOUBLE 返回e的值。 exp exp(DOUBLE a) DOUBLE 返回e的a次方。 factorial factorial(INT a) BIGINT 返回a的阶乘。 floor floor(DOUBLE a) BIGINT 对给定数据进行向下舍入最接近的整数。例如：floor(21.2)，返回21。 greatest greatest(T v1, T v2, ...) DOUBLE 返回列表中的最大值。 hex hex(BIGINT a) hex(STRING a) STRING 将整数或字符转换为十六进制格式。 least least(T v1, T v2, ...) DOUBLE 返回列表中的最小值。 ln ln(DOUBLE a) DOUBLE 返回给定数值的自然对数。 log log(DOUBLE base, DOUBLE a) DOUBLE 返回给定底数及指数返回自然对数。 log10 log10(DOUBLE a) DOUBLE 返回给定数值的以10为底自然对数。 log2 log2(DOUBLE a) DOUBLE 返回给定数值的以2为底自然对数。 median median(colname) DOUBLE或DECIMAL 计算中位数。 negative negative(INT a) DECIMAL或INT 返回a的相反数，例如negative(2)，返回-2。 percentile percentile(colname,DOUBLE p) DOUBLE或ARRAY 计算精确百分位数，适用于小数据量。先对指定列升序排列，然后取精确的第p位百分数。p必须在0和1之间。 percentile_approx percentile_approx (colname,DOUBLE p) DOUBLE或ARRAY 计算近似百分位数，适用于大数据量。先对指定列升序排列，然后取第p位百分数对应的值。 pi pi() DOUBLE 返回pi的值。 pmod pmod(INT a, INT b) DECIMAL或INT 返回a除b的余数的绝对值。 positive positive(INT a) DECIMAL、DOUBLE或INT 返回a的值，例如positive(2)，返回2。 pow pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) DOUBLE 返回a的p次幂。 radians radians(DOUBLE a) DOUBLE 返回角度所对应的弧度。 rand rand(INT seed) DOUBLE 返回大于或等于0且小于1的平均分布随机数。如果指定种子seed，则会得到一个稳定的随机数序列。 round round(DOUBLE a) DOUBLE 四舍五入。 round round(DOUBLE a, INT d) DOUBLE 小数部分d位之后数字四舍五入，例如round(21.263,2)，返回21.26。 shiftleft shiftleft(BIGINT a, INT b) INT 有符号左边移，将a的二进制数按位左移b位。 shiftright shiftright(BIGINT a, INT b) INT 有符号右移，将a的二进制数按位右移b位。 shiftrightunsigned shiftrightunsigned(BIGINT a, INT b) INT 无符号右移，将a的二进制数按位右移b位。 sign sign(DOUBLE a) DOUBLE 返回a所对应的正负号，a为正返回1.0，a为负，返回-1.0，否则返回0.0。 sin sin(DOUBLE a) DOUBLE 返回给定角度a的正弦值。 sqrt sqrt(DOUBLE a) DOUBLE 返回数值的平方根。 tan tan(DOUBLE a) DOUBLE 返回给定角度a的正切值。 父主题： 数学函数

常见问题 使用default创建DataSource表时提示“xxx dli datasource v2 tables is only supported in spark3.3 or later version.”怎么办？ 使用default创建DataSource表时引擎版本不低于Spark 3.3.1，如果引擎版本低于Spark 3.3.1则会提示上述错误信息，此时请切换使用Hive语法创建表。详细操作请参考使用Hive语法创建OBS表。 使用Spark 3.3.1执行jar作业报错 "xxx don`t support dli v1 table."怎么办？ 该错误提示信息说明使用Spark 3.3.1执行Jar作业时不支持执行与该表相关的操作，请切换使用Hive语法重构表数据结构，例如使用Hive语法[STORED AS file_format] CTAS重新创建表后再执行作业。详细操作请参考使用Hive语法创建OBS表。

示例1：创建OBS非分区表 示例说明：创建名为table1的OBS非分区表，使用USING关键字指定该表的存储格式为orc格式。 在您的实际使用中，可以将obs表存储为parquet、json、avro等类型。 1 2 3 4 5 CREATE TABLE IF NOT EXISTS table1 ( col_1 STRING, col_2 INT) USING orc OPTIONS (path 'obs://bucketName/filePath');

示例2：创建OBS分区表 示例说明：创建一个名为student的分区表，该分区表使用院系编号（facultyNo）和班级编号（classNo）进行分区。该student表会同时按照不同的院系编号（facultyNo）和不同的班级编号（classNo）分区。 在实际的使用过程中，您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后的括号内。 1 2 3 4 5 6 7 CREATE TABLE IF NOT EXISTS student ( Name STRING, facultyNo INT, classNo INT) USING csv OPTIONS (path 'obs://bucketName/filePath') PARTITIONED BY (facultyNo, classNo);

示例6：创建OBS非分区表，自定义表的OPTIONS参数 示例说明：CSV表是一种以逗号分隔的纯文本文件格式，用于存储和交换数据。它通常用于简单的数据交换，但是它没有结构化数据的概念，因此不适合存储复杂数据类型。于是当file_format为csv时，支持配置更多的OPTIONS参数（参考表3）。 本例创建一个名为table4且存储格式为csv非分区表并使用了额外的OPTIONS参数对数据加以约束。 delimiter：数据分隔符，表示使用逗号（，）作为数据之间的分隔符； quote：引用字符，表示使用双引号（”）来表示数据中的引用信息； escape：转义字符，表示使用反斜杠（\）作为数据存储时的转义字符； multiLine：设置需要存储的列数据中不包含回车符或者换行符； dataFormat：表示该csv文件中data字段的指定日期格式为yyyy-MM-dd； timestamoFormat：表示该csv文件中会将时间戳格式指定为yyyy-MM-dd HH:mm:ss； header：表示该csv表中包含表头信息； nullValue：表示设置null来表示csv表中的null值； comment：表示该csv表使用斜杠（/）表示注释的开头； compression：表示该csv表被压缩，此处csv表支持gzip、bzip2和deflate的压缩格式，若不需要压缩，也可以设置为none； encoding：表示该表使用utf-8的数据编码格式，在实际使用中，可以根据您的需求选择utf-8、gb2312和gbk中任一种编码格式，其中默认编码格式为utf-8。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 CREATE TABLE IF NOT EXISTS table4 ( col_1 STRING, col_2 INT ) USING csv OPTIONS ( path 'obs://bucketName/filePath', delimiter = ',', quote = '#', escape = '|', multiline = false, dateFormat = 'yyyy-MM-dd', timestampFormat = 'yyyy-MM-dd HH:mm:ss', mode = 'failfast', header = true, nullValue = 'null', comment = '*', compression = 'deflate', encoding = 'utf - 8' );

示例5：创建OBS分区表，自定义表的OPTIONS参数 示例说明：创建OBS表时支持自定义属性名与属性值，OPTIONS参数说明可参考表2。 本例创建名为table3并以col_2为分区依据的OBS分区表。在OPTIONS中配置path、multiLevelDirEnable、dataDelegated和compression。 path：OBS存储路径，本例为“obs ://bucketName/filePath”，其中的bucketName为您存储时所使用桶名称，filePath为您实际使用的目录名称； 请注意大数据场景建议使用OBS并行文件系统进行存储； multiLevelDirEnable：本例设置为true，表示查询该表时会迭代读取表路径中的所有文件和子目录文件，若不需要此项配置可以设置为false或不设置（默认为false）； dataDelegated：本例设置为true，表示在删除表或相关分区时，会一并清除该path路径下的所有数据，若不需要此项配置可以设置为false或不设置（默认为false）； compression：当创建的OBS表需要压缩时，可以使用compression关键字来配置压缩格式，本例中就使用了zstd压缩格式。 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE IF NOT EXISTS table3 ( col_1 STRING, col_2 int ) USING parquet PARTITIONED BY (col_2) OPTIONS ( path 'obs://bucketName/filePath', multiLeveldirenable = true, datadelegated = true, compression = 'zstd' );

语法格式 1 2 3 4 5 6 7 CREATE TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name1 col_type1 [COMMENT col_comment1], ...)] USING file_format [OPTIONS (path 'obs_path', key1=val1, key2=val2, ...)] [PARTITIONED BY (col_name1, col_name2, ...)] [COMMENT table_comment] [AS select_statement]