华为云用户手册

  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 a 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 参数a的格式包括浮点数格式、整数格式、字符串格式。 当参数a非BIGINT类型时,会隐式转换为BIGINT类型后参与运算。 b 是 DOUBLE、BIGINT、DECIMAL、STRING类型。 参数b的格式包括浮点数格式、整数格式、字符串格式。 当参数b非BIGINT类型时,会隐式转换为BIGINT类型后参与运算。
  • 示例代码 计算所有商品库存(items)的样本偏差。命令示例如下: select stddev_samp(items) from warehouse; 返回结果如下: +------------+| _c0 |+------------+| 1.342355 |+------------+ 与group by配合使用,对所有商品按照仓库(warehourseId)进行分组,并计算同组商品库存(items)的样本偏差。命令示例如下: select warehourseId, stddev_samp(items) from warehourse group by warehourseId; 返回结果如下: +------------+------------+| warehouseId| _c1 |+------------+------------+| city1 | 1.23124 || city2 | 1.23344 || city3 | 1.43425 |+------------+------------+
  • 示例代码 计算所有仓库的商品(items)总和。命令示例如下: select sum(items) from warehouse; 返回结果如下: _c0 55357 与group by配合使用,对所有商品按照仓库(warehouseId)进行分组,计算各仓库商品的总数(items)总和。命令示例如下: select warehouseId, sum(items) from warehouse group by warehouseId; 返回结果如下: warehouseId| _c1 city1 15500 city2 10175 city3 19400
  • 示例代码 将 basketball 按照 bas(.*?)(ball) 拆分。返回ket。 select regexp_extract('basketball', 'bas(.*?)(ball)'); 返回 basketball 。 select regexp_extract('basketball', 'bas(.*?)(ball)',0); 返回99。在 DLI 上提交正则计算的SQL,需要使用两个"\"作为转义字符。 select regexp_extract('8d99d8', '8d(\\d+)d8'); 返回【你好】。 select regexp_extract('【你好】hello', '([^\\x{00}-\\x{ff}]+)'); 返回你好。 select regexp_extract('【你好】hello', '([\\x{4e00}-\\x{9fa5}]+)');
  • 示例代码 去除字符串 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');
  • 示例代码 计算所有商品库存(items)的 0.5 百分位。命令示例如下: select percentile(items,0.5) from warehouse; 返回结果如下: +------------+| _c0 |+------------+| 500.6 |+------------+ 与group by配合使用,对所有商品按照仓库(warehourseId)进行分组,并计算同组商品库存(items)的 0.5 百分位。命令示例如下: select warehourseId, percentile(items, 0.5) from warehourse group by warehourseId; 返回结果如下: +------------+------------+| warehouseId| _c1 |+------------+------------+| city1 | 499.6 || city2 | 354.8 || city3 | 565.7 |+------------+------------+
  • 示例代码 示例数据 为便于理解函数的使用方法,本文为您提供源数据,基于源数据提供函数相关示例。创建表salary,并添加数据,命令示例如下: CREATE EXTERNAL TABLE salary (dept STRING, -- 部⻔名称userid string, -- 员⼯IDsal INT -- 薪⽔) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile; 添加数据如下: d1,user1,1000d1,user2,2000d1,user3,3000d2,user4,4000d2,user5,5000 示例:计算员工薪水在部门内的百分比排名。 select dept, userid, sal, percent_rank() over(partition by dept order by sal) as pr2from salary;-- 结果分析:d1 user1 1000 0.0 -- (1-1)/(3-1)=0.0d1 user2 2000 0.5 -- (2-1)/(3-1)=0.5d1 user3 3000 1.0 -- (3-1)/(3-1)=1.0d2 user4 4000 0.0 -- (1-1)/(2-1)=0.0d2 user5 5000 1.0 -- (2-1)/(2-1)=1.0
  • 说明 语法 描述 join_condition join逻辑判断条件。 and 逻辑运算符:与。 or 逻辑运算符:或。 not 逻辑运算符:非。 ( 子逻辑判断条件开始。 ) 子逻辑判断条件结束。 condition 逻辑判断条件。 const_set 常量集合,元素间逗号分隔。 in 关键字,用于判断属性是否在一个集合中。 atrrs_value_set_expr 属性值集合。 attr_expr 属性表达式。 Equality and inequality 等式与不等式,详情请参见关系运算符。 pattern_string 模式匹配字符串,支持通配符匹配。WHERE LIKE条件过滤时,支持SQL通配符中“%”与“_”,“%”代表一个或多个字符,“_”仅代表一个字符。
  • 说明 语法 描述 having_condition having逻辑判断条件。 and 逻辑运算符:与。 or 逻辑运算符:或。 not 逻辑运算符:非。 ( 子逻辑判断条件开始。 ) 子逻辑判断条件结束。 condition 逻辑判断条件。 const_set 常量集合,元素间逗号分隔。 in 关键字,用于判断属性是否在一个集合中。 attrs_value_set_expr 属性值集合。 attr_expr 属性表达式。 Equality and inequality 等式与不等式,详情请参见关系运算符。 pattern_string 模式匹配字符串,支持通配符匹配。WHERE LIKE条件过滤时,支持SQL通配符中“%”与“_”,“%”代表一个或多个字符,“_”仅代表一个字符。 like 关系运算符:用于通配符匹配。
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待替换的字符。 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范,请参见正则表达式规范。pattern为空串时返回报错。 replace_string 是 STRING 将匹配pattern的字符串替换后的字符串。 occurrence 否 BIGINT 必须大于等于1,表示将第occurrence次匹配的字符串替换为replace_string,为1时表示替换所有匹配的子串。为其他类型或小于1时,返回报错。默认值为1。 说明: 该字段仅Spark3.1.1版本的功能适用。
  • 说明 语法 描述 attr_expr 属性表达式。 attr 表的字段,与col_name相同。 const_value 常量值。 case_expr case表达式。 math_func 数学函数。 date_func 日期函数。 string_func 字符串函数。 aggregate_func 聚合函数。 window_func 分析窗口函数。 user_define_func 用户自定义函数。 general_binary_operator 普通二元操作符。 general_unary_operator 普通一元操作符。 ( 指定子属性表达式开始。 ) 指定子属性表达式结束。
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待搜索的字符串,其他类型会返回报错。 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。pattern为空串或其他类型时返回报错。 start_position 否 BIGINT BIGINT类型常量,必须大于0。其他类型或值小于等于0时返回报错。不指定时默认为1,表示从source的第一个字符开始匹配。
  • 参数说明 表1 参数描述 参数 描述 db_name Database名称,由字母、数字和下划线(_)组成。不能是纯数字,且不能以下划线开头。 table_name Database中的表名,由字母、数字和下划线(_)组成。不能是纯数字,且不能以下划线开头。匹配规则为:^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。如果特殊字符需要使用单引号('')包围起来。 partition_specs 分区信息,key=value形式,key为分区字段,value为分区值。若分区字段为多个字段,可以不包含所有的字段,会删除匹配上的所有分区。“partition_specs”中的参数默认带有“( )”,例如:PARTITION (facultyNo=20, classNo=103);。
  • 示例 为了便于理解删除分区语句的使用方法,本节示例为您提供源数据,基于源数据提供删除分区的操作示例。 使用DataSource语法创建一个OBS表分区表。 创建了一个名为student的OBS分区表,表中有学生学号(id),学生姓名(name),学生院系编号(facultyNo)和学生班级编号(classNo),该表使用学生院系编号(facultyNo)和学生班级编号(classNo)进行分区。 12345678 create table if not exists student (id int,name STRING,facultyNo int,classNo INT)using csvoptions (path 'obs://bucketName/filePath')partitioned by (faculytNo, classNo); 在表格中插入分区数据。 利用插入数据中的内容,可以插入以下数据 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627 INSERT into studentpartition (facultyNo = 10, classNo = 101)values (1010101, "student01"), (1010102, "student02");INSERT into studentpartition (facultyNo = 10, classNo = 102)values (1010203, "student03"), (1010204, "student04");INSERT into studentpartition (facultyNo = 20, classNo = 101)values (2010105, "student05"), (2010106, "student06");INSERT into studentpartition (facultyNo = 20, classNo = 102)values (2010207, "student07"), (2010208, "student08");INSERT into studentpartition (facultyNo = 20, classNo = 103)values (2010309, "student09"), (2010310, "student10");INSERT into studentpartition (facultyNo = 30, classNo = 101)values (3010111, "student11"), (3010112, "student12");INSERT into studentpartition (facultyNo = 30, classNo = 102)values (3010213, "student13"), (3010214, "student14"); 查看分区。 利用查看指定表所有分区中的内容,可以查看相关的分区内容。 示例代码如下: SHOW partitions student; 表2 表数据示例 facultyNo classNo facultyNo=10 classNo=101 facultyNo=10 classNo=102 facultyNo=20 classNo=101 facultyNo=20 classNo=102 facultyNo=20 classNo=103 facultyNo=30 classNo=101 facultyNo=30 classNo=102 删除分区。 示例1:指定多个筛选条件删除分区 本示例删除facultyNo为20,classNo为103的分区; 如需按指定筛选条件删除分区请参考指定筛选条件删除分区(只支持OBS表)。 示例代码如下: ALTER TABLE studentDROP IF EXISTSPARTITION (facultyNo=20, classNo=103); 重新利用第三步中的方法查看表中的分区,可以看到该分区被删除: SHOW partitions student; 示例2:指定单个筛选条件删除分区 本示例删除facultyNo为30的分区;在插入数据的过程中可以了解到,facultyNo为30的分区有两个。 如需按指定筛选条件删除分区请参考指定筛选条件删除分区(只支持OBS表)。 示例代码如下: ALTER TABLE studentDROP IF EXISTSPARTITION (facultyNo = 30); 执行后结果: 表3 表数据示例 facultyNo classNo facultyNo=10 classNo=101 facultyNo=10 classNo=102 facultyNo=20 classNo=101 facultyNo=20 classNo=102 facultyNo=20 classNo=103
  • 使用限制 创建含有复杂数据类型字段的表时,该表存储格式不支持 CS V(txt)。 如果表中含有复杂数据类型字段时,该表不支持CSV(txt)格式的文件数据导入。 MAP数据类型建表必须指定schema,且不支持date、short、timestamp数据类型。 对于JSON格式OBS表,MAP的键类型只支持STRING类型。 由于MAP类型的键不能为NULL,MAP键不支持对插入数据进行可能出现NULL值类型之间的隐式转换,如:STRING类型转换为其他原生类型、FLOAT类型转换为TIMESTAMP类型、其他原生类型转换为DECIMAL类型等。 STRUCT数据类型不支持double,boolean数据类型。
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 json 是 STRING 标准的JSON格式对象,格式为{Key:Value, Key:Value,...} path 是 STRING 表示在json中的path,以$开头。不同字符的含义如下: $表示根节点。 .表示子节点。 []表示[number]表示数组下标,从0开始。 *表示Wildcard for [],返回整个数组。*不支持转义。
  • 示例代码 提取JSON对象src_json.json中的信息。命令示例如下。 jsonString = {"store": {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} }, "email":"amy@only_for_json_udf_test.net", "owner":"Tony" } 提取owner字段信息,返回Tony。 select get_json_object(jsonString, '$.owner'); 提取store.fruit字段第一个数组信息,返回{"weight":8,"type":"apple"}。 select get_json_object(jsonString, '$.store.fruit[0]'); 提取不存在的字段信息,返回NULL。 select get_json_object(jsonString, '$.non_exist_key'); 提取数组型JSON对象的信息。命令示例如下。 返回22。 select get_json_object('{"array":[["a",11],["b",22],["c",33]]}','$.array[1][1]'); 返回["h00","h11","h22"]。 select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h[*]'); 返回["h00","h11","h22"]。 select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h'); 返回h11。 select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h[1]'); 提取带有.的JSON对象中的信息。命令示例如下。 创建一张表。 create table json_table (id string, json string); 向表中插入数据,Key带 "." insert into table json_table (id, json) values ("1", "{\"city1\":{\"region\":{\"rid\":6}}}"); 向表中插入数据,Key不带 "." insert into table json_table (id, json) values ("2", "{\"city1\":{\"region\":{\"rid\":7}}}"); 取rid的值,查询key为city1,返回6。由于包含.,只能用['']来解析。 select get_json_object(json, "$['city1'].region['id']") from json_table where id =1; 取rid的值,查询key为city1,返回7。查询方法有如下两种。 select get_json_object(json, "$['city1'].region['id']") from json_table where id =2; select get_json_object(json, "$.city1.region['id']") from json_table where id =2; JSON输入为空或非法格式。命令示例如下。 返回NULL。 select get_json_object('','$.array[2]'); 返回NULL。 select get_json_object('"array":["a",1],"b":["c",3]','$.array[1][1]'); JSON字符串涉及转义。命令示例如下。 返回"3"。 select get_json_object('{"a":"\\"3\\"","b":"6"}', '$.a'); 返回'3'。 select get_json_object('{"a":"\'3\'","b":"6"}', '$.a'); 一个JSON对象中可以出现相同的Key,可以成功解析。 返回1。 select get_json_object('{"b":"1","b":"2"}', '$.b'); 输出结果按照JSON字符串的原始排序方式输出。 返回{"b":"3","a":"4"}。 select get_json_object('{"b":"3","a":"4"}', '$');
  • 示例 完整的SQL作业提交流程您可以参考《快速入门》中的《提交SQL作业》等章节描述。 队列是使用DLI服务的基础,执行SQL前需要先创建队列。具体可以参考《用户指南》中的“创建队列”章节。 在DLI管理控制台,单击左侧导航栏中的“SQL编辑器”,可进入SQL作业“SQL编辑器”页面。 在“SQL编辑器”页面右侧的编辑窗口中,输入如下创建数据库的SQL语句,单击“执行”。阅读并同意隐私协议,单击“确定”。 若testdb数据库不存在,则创建数据库testdb。 1 CREATE DATABASE IF NOT EXISTS testdb;
  • 示例代码 假设列int_test中的元素为1、2、3、4,类型为INT类型。 返回3。 select percentile_approx(int_test,0.7) FROM int_test; 返回3。 select percentile_approx(int_test,0.75) FROM int_test; 返回2。 select percentile_approx(int_test,0.5) FROM int_test; 返回[1,2,2,3,4]。 select percentile_approx (int_test,ARRAY(0.1,0.3,0.5,0.6,0.9)) FROM int_test;
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 source 是 STRING 待搜索的字符串。 pattern 是 STRING STRING类型常量或正则表达式。待匹配的模型。 start_position 否 BIGINT 起始位置,必须大于0。不指定时默认为1,表示从source的第一个字符开始匹配。 occurrence 否 BIGINT BIGINT常量,必须大于0。不指定时默认为1,表示返回第一次匹配的子串。
  • 示例代码 返回a。 select regexp_substr('a1b2c3', '[a-z]'); 返回b。 select regexp_substr('a1b2c3', '[a-z]', 2, 1); 返回c。 select regexp_substr('a1b2c3', '[a-z]', 2, 2); 返回NULL。 select regexp_substr('a1b2c3', null);
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 urlString 是 STRING URL链接。无效URL链接会返回报错。 partToExtract 是 STRING 取值包含:HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE和USERINFO,不区分大小写。 keyToExtract 否 STRING 当part取值为QUERY时,根据key值取出对应的Value值。
  • 示例代码 例如 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);
  • 示例代码 返回example.com。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST'); 返回/over/there/index.dtb。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'PATH'); 返回animal。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'QUERY', 'type'); 返回nose。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'REF'); 返回file。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'PROTOCOL'); 返回 username@example.com:8042。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'AUTHORITY'); 返回username。 select parse_url('file://username@example.com:666/over/there/index.dtb?type=animal&name=narwhal#nose', 'USERINFO');
  • 参数说明 表1 参数说明 参数 是否必选 参数类型 说明 str 是 STRING 待搜索的目标字符串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其他类型会返回报错。 substr 是 STRING 待匹配的子串。 如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其他类型会返回报错。 start_pos 否 BIGINT 指定查找的起始位置。
  • 示例代码 查找字符串ab在字符串abhiab中的位置。命令示例如下。 返回1。 select locate('ab', 'abhiab'); 返回5。 select locate('ab', 'abhiab', 2); 返回0。 select locate('ab', 'abhiab', null); 查找字符串hi在字符串hanmeimei and lilei中的位置。命令示例如下。 返回0。 select locate('hi', 'hanmeimei and lilei');
  • 语法格式 1234567 CREATE TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name1 col_type1 [COMMENT col_comment1], ...)] USING file_format [OPTIONS (key1=val1, key2=val2, ...)] [PARTITIONED BY (col_name1, col_name2, ...)] [COMMENT table_comment] [AS select_statement];
  • 参数说明 表1 参数描述 参数 是否必选 描述 db_name 否 Database名称。 由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。 table_name 是 Database中的表名。 由字母、数字和下划线(_)组成。不能是纯数字,且不能以数字和下划线开头。匹配规则为:^(?!_)(?![0-9]+$)[A-Za-z0-9_$]*$。 特殊字符需要使用单引号('')包围起来。 表名对大小写不敏感,即不区分大小写。 col_name 是 以逗号分隔的带数据类型的列名。 列名由字母、数字和下划线(_)组成。不能是纯数字,且至少包含一个字母。 列名为大小写不敏感,即不区分大小写。 col_type 是 列字段的数据类型。数据类型为原生类型。 请参考原生数据类型。 col_comment 否 列字段描述。仅支持字符串常量。 file_format 是 DLI表数据存储格式,支持:parquet和orc格式。 table_comment 否 表描述。仅支持字符串常量。 select_statement 否 用于CTAS命令,将源表的select查询结果或某条数据插入到新创建的DLI表中。 表2 OPTIONS参数描述 参数 是否必选 描述 默认值 multiLevelDirEnable 否 是否迭代查询子目录中的数据。当配置为true时,查询该表时会迭代读取该表路径中所有文件,包含子目录中的文件。 false compression 否 指定压缩格式。一般为parquet格式时指定该参数,推荐使用'zstd'压缩格式。 -
  • 示例2:创建DLI分区表 示例说明:创建一个名为student的分区表,该分区表使用院系编号(facultyNo)和班级编号(classNo)进行分区,该student表会同时按照不同的院系编号(facultyNo)和不同的班级编号(classNo)分区。 在实际的使用过程中,您可以选择合适的分区字段并将其添加到PARTITIONED BY关键字后。 1234567 CREATE TABLE IF NOT EXISTS student ( Name STRING, facultyNo INT, classNo INT)USING orcPARTITIONED BY (facultyNo, classNo);
  • 注意事项 CTAS建表语句不能指定表的属性。 若没有指定分隔符,则默认为逗号(,)。 关于分区表的使用说明: 创建分区表时,PARTITIONED BY中指定分区列必须是表中的列,且必须在Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。 创建分区表时,分区字段必须是表字段的最后一个字段或几个字段,且多分区字段的顺序也必须对应。否则将出错。 单表分区数最多允许200000个。 2024年1月后新注册使用DLI服务的用户,且使用Spark3.3及以上版本的引擎,在使用DataSource语法创建表时支持使用CTAS创建分区表。
共99354条