华为云用户手册

常见问题 使用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表。

示例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 9101112 CREATE TABLE IF NOT EXISTS table3 ( col_1 STRING, col_2 int)USING parquetPARTITIONED BY (col_2)OPTIONS ( path 'obs://bucketName/filePath', multiLeveldirenable = true, datadelegated = true, compression = 'zstd');

注意事项 创建表时不会统计大小。 添加数据时会修改大小至0。 如需查看表大小可以通过OBS查看。 CTAS建表语句不能指定表的属性。 OBS目录下包含子目录的场景： 创建表时，若指定路径为OBS上的目录，且该目录下包含子目录（或嵌套子目录），则子目录下的所有文件类型及其内容也是表内容。 您需要保证所指定的目录及其子目录下所有文件类型和建表语句中指定的存储格式一致，所有文件内容和表中的字段一致，否则查询将报错。 您可以在建表语句OPTIONS中设置“multiLevelDirEnable”为true以查询子目录下的内容，此参数默认值为false（注意，此配置项为表属性，请谨慎配置。Hive表不支持此配置项)。 关于分区表的使用说明： 创建分区表时，PARTITIONED BY中指定分区列必须是表中的列，且必须在Column列表中指定类型。分区列只支持string, boolean, tinyint, smallint, short, int, bigint, long, decimal, float, double, date, timestamp类型。 创建分区表时，分区字段必须是表字段的最后一个字段或几个字段，且多分区字段的顺序也必须对应。否则将出错。 单表分区数最多允许200000个。 2024年1月后新注册使用 DLI 服务的用户，且使用Spark3.3及以上版本的引擎，在使用DataSource语法创建表时支持使用CTAS创建分区表。

语法格式 1234567 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]

数学函数概览 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。 percentlie percentile(colname,DOUBLE p) DOUBLE或ARRAY 计算精确百分位数，适用于小数据量。先对指定列升序排列，然后取精确的第p位百分数。p必须在0和1之间。 percentlie_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的正切值。 父主题： 数学函数

示例 导入数据前已参考创建OBS表或者创建DLI表中的示例描述创建对应的表。 可使用下列语句将 CS V文件导入到DLI表，“t”为表名。 12 LOAD DATA INPATH 'obs://dli/data.csv' INTO TABLE t OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','COMMENTCHAR'='#','HEADER'='false'); 可使用下列语句将JSON文件导入到DLI表，“jsontb”为表名。 12 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 日期及时间模式字符定义。

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

执行Insert into后数据重复怎么办？ 问题现象： 使用Hive和Datasource（除Hudi外）表在执行数据修改类命令（例如insert into，load data）时由于数据源不支持事务性，在系统故障或队列资源重启后，可能会导致数据重复或数据不一致等问题。 原因分析： 在数据的Commit阶段如果出现队列资源重启可能会导致数据已经被修复到正式目录中。如果执行的是Insert into语句，资源重启后触发重试就会有概率导致数据重复写入。 解决方案： 推荐使用具备ACID能力的Hudi类型数据源。 建议尽量使用insert overwrite这样幂等的语法而不是insert into等非幂等语法插入数据。 如果严格需求数据不能重复，建议在insert into后对表数据执行去重操作，防止数据重复。

示例 导入数据前已参考创建OBS表或者创建DLI表中的示例描述创建对应的表。 将SELECT查询结果插入到表中 使用DataSource语法创建一个parquet格式的分区表 CREATE TABLE data_source_tab1 (col1 INT, p1 INT, p2 INT) USING PARQUET PARTITIONED BY (p1, p2); 插入查询结果到分区 (p1 = 3, p2 = 4)中 INSERT INTO data_source_tab1 PARTITION (p1 = 3, p2 = 4) SELECT id FROM RANGE(1, 3); 插入新的查询结果到分区 (p1 = 3, p2 = 4) 中 INSERT OVERWRITE TABLE data_source_tab1 PARTITION (p1 = 3, p2 = 4) SELECT id FROM RANGE(3, 5); 将某条数据插入表中 使用Hive语法创建一个parquet格式的分区表 CREATE TABLE hive_serde_tab1 (col1 INT, p1 INT, p2 INT) USING HIVE OPTIONS(fileFormat 'PARQUET') PARTITIONED BY (p1, p2); 插入两条数据到分区 (p1 = 3, p2 = 4)中 INSERT INTO hive_serde_tab1 PARTITION (p1 = 3, p2 = 4) VALUES (1), (2); 插入新的数据到分区 (p1 = 3, p2 = 4) 中 INSERT OVERWRITE TABLE hive_serde_tab1 PARTITION (p1 = 3, p2 = 4) VALUES (3), (4);

约束限制 insert overwrite语法不适用于“自读自写”场景，该场景因涉及数据的连续处理和更新，如果使用insert overwrite语法可能存在数据丢失风险。 "自读自写"是指在处理数据时能够读取数据，同时根据读取的数据生成新的数据或对数据进行修改。 使用Hive和Datasource（除Hudi外）表在执行数据修改类命令（例如insert into，load data）时由于数据源不支持事务性，在系统故障或队列资源重启后，可能会导致数据重复或数据不一致等问题。 为了避免这种情况，建议优先选择支持事务性的数据源，如Hudi类型数据源，该类数据源具备ACID（Atomicity、Consistency、Isolation、Durability）能力，有助于确保数据的一致性和准确性。 了解更多：执行Insert into后数据重复怎么办？

语法格式 将SELECT查询结果插入到表中 12 INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] select_statement; 12 INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] select_statement; part_spec: : (part_col_name1=val1 [, part_col_name2=val2, ...]) 将某条数据插入到表中 12 INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...]; 12 INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...]; values_row: : (val1 [, val2, ...])

关键字 表1 INSERT关键字说明 参数 描述 db_name 需要执行INSERT命令的表所在数据库的名称。 table_name 需要执行INSERT命令的表的名称。 part_spec 指定详细的分区信息。若分区字段为多个字段，需要包含所有的字段，但是可以不包含对应的值，系统会匹配上对应的分区。单表分区数最多允许100000个。 select_statement 源表上的SELECT查询（支持DLI表、OBS表）。 values_row 想要插入到表中的值，列与列之间用逗号分隔。

注意事项 向表中添加分区时，此表和分区列（建表时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'。 若新增分区指定的路径包含子目录（或嵌套子目录），则子目录下面的所有文件类型及内容也将作为该分区的记录。 您需要保证该分区目录下所有文件类型和文件内容与表的字段一致，否则查询将报错。 您可以在建表语句OPTIONS中设置“multiLevelDirEnable”为true以查询子目录下的内容，此参数默认值为false（注意，此配置项为表属性，请谨慎配置。Hive表不支持此配置项)。

示例 建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路径下。 12345 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路径下。 执行添加分区命令，将分区的元数据信息生效。 1234 ALTER TABLE testobstableADD PARTITION (external_data = '22', dt = '2021-07-27') LOCATION 'obs://bucketName/datapath/external_data=22/dt=2021-07-27'; 添加分区成功后，即可根据分区列进行数据查询等操作。 12 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命令添加分区的基本操作和使用说明。

示例 将student表的分区dt='2008-08-08',city='xxx'的OBS路径设置为“obs://bucketName/fileName/student/dt=2008-08-08/city=xxx”。 123 ALTER TABLE student PARTITION(dt='2008-08-08',city='xxx') SET LOCATION 'obs://bucketName/fileName/student/dt=2008-08-08/city=xxx';

注意事项 所要修改位置的表分区必须是已经存在的，否则将报错。 “partition_specs”中的参数默认带有“( )”，例如：PARTITION (dt='2009-09-09',city='xxx')。 所指定的新的OBS路径必须是已经存在的绝对路径，否则将报错。 若新增分区指定的路径包含子目录（或嵌套子目录），则子目录下面的所有文件类型及内容也将作为该分区的记录。用户需要保证该分区目录下所有文件类型和文件内容与表的字段一致，否则查询将报错。

说明 语法 描述 where_condition where逻辑判断条件。 and 逻辑运算符：与。 or 逻辑运算符：或。 not 逻辑运算符：非。 ( 子逻辑判断条件开始。 ) 子逻辑判断条件结束。 condition 逻辑判断条件。 exists 关键字，用于判断是否存在一个不为空的集合，若exists后面跟的为子查询，子查询中须包含逻辑判断条件。 in 关键字，用于判断属性是否在一个集合中。 attrs_value_set_expr 属性值集合。 attr_expr 属性表达式。

说明 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, ...)]

分析窗口函数概览 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 为每一行指派一个唯一的编号。 父主题： 分析窗口函数

示例代码 返回 2222。 select regexp_replace1('abcd', '[a-z]', '2'); 返回 2bcd。 select regexp_replace1('abcd', '[a-z]', '2', 1); 返回 a2cd。 select regexp_replace1('abcd', '[a-z]', '2', 2); 返回 ab2d。 select regexp_replace1('abcd', '[a-z]', '2', 3); 返回 abc2。 select regexp_replace1('abcd', '[a-z]', '2', 4);

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

Spark SQL常用配置项说明 本章节为您介绍DLI 批作业SQL语法的常用配置项。 表1 常用配置项 名称 默认值 描述 spark.sql.files.maxRecordsPerFile 0 要写入单个文件的最大记录数。如果该值为零或为负，则没有限制。 spark.sql.shuffle.partitions 200 为连接或聚合过滤数据时使用的默认分区数。 spark.sql.dynamicPartitionOverwrite.enabled false 当前配置设置为“false”时，DLI在覆盖写之前，会删除所有符合条件的分区。例如，分区表中有一个“2021-01”的分区，当使用INSERT OVERWRITE语句向表中写入“2021-02”这个分区的数据时，会把“2021-01”的分区数据也覆盖掉。 当前配置设置为“true”时，DLI不会提前删除分区，而是在运行时覆盖那些有数据写入的分区。 spark.sql.files.maxPartitionBytes 134217728 读取文件时要打包到单个分区中的最大字节数。 spark.sql.badRecordsPath - Bad Records的路径。 spark.sql.legacy.correlated.scalar.query.enabled false 该参数设置为true： 当子查询中数据不重复的情况下，执行关联子查询，不需要对子查询的结果去重。 当子查询中数据重复的情况下，执行关联子查询，会提示异常，必须对子查询的结果做去重处理，比如max(),min()。 该参数设置为false： 不管子查询中数据重复与否，执行关联子查询时，都需要对子查询的结果去重，比如max(),min()，否则提示异常。 spark.sql.keep.distinct.expandThreshold - 参数说明： 对于包含count(distinct)的多维分析（with cube）的查询场景，spark典型的执行计划是将cube使用expand算子来实现，但该操作会导致查询膨胀，为了避免出现查询膨胀，建议执行如下配置： spark.sql.keep.distinct.expandThreshold： 默认值：-1，即使用Spark默认的expand算子。 设置具体数值：即代表定义了查询膨胀的阈值（例如512），超过该阈值count(distinct) 使用distinct聚合算子来执行，不再使用expand算子。 spark.sql.distinct.aggregator.enabled：强制使用distinct聚合算子的开关。配置为true时不再根据spark.sql.keep.distinct.expandThreshold来判断。 适用场景：包含count(distinct)的多维分析（with cube）的查询场景，可能包含多个count(distinct)，且包含cube/roll up 典型场景示例： SELECT a1, a2, count(distinct b), count(distinct c) FROM test_distinct group by a1, a2 with cube spark.sql.distinct.aggregator.enabled false dli.jobs.sql.resubmit.enable null 通过设置该参数可以控制在driver故障、队列重启时Spark SQL作业是否重新提交。 false：禁用作业重试，所有类型的命令都不重新提交，一旦driver故障，作业将标记为失败（FAILED）。 true：启用作业重试，即在driver故障时，所有类型的作业都将重新提交。 注意： 如果配置为true，在执行INSERT等幂等类型的操作时（例如insert into，load data、update），可能会导致数据一致性问题。即driver故障后作业重试，导致driver故障前已插入的数据被重复写入。 spark.sql.optimizer.dynamicPartitionPruning.enabled true 该配置项用于启用或禁用动态分区修剪。在执行SQL查询时，动态分区修剪可以帮助减少需要扫描的数据量，提高查询性能。 配置为true时，代表启用动态分区修剪，SQL会在查询中自动检测并删除那些不满足WHERE子句条件的分区，适用于在处理具有大量分区的表时。 如果SQL查询中包含大量的嵌套left join操作，并且表有大量的动态分区时，这可能会导致在数据解析时消耗大量的内存资源，导致Driver节点的内存不足，并触发频繁的Full GC。 在这种情况下，可以配置该参数为false即禁用动态分区修剪优化，有助于减少内存使用，避免内存溢出和频繁的Full GC。 但禁用此优化可能会降低查询性能，禁用后Spark将不会自动修剪掉那些不满足条件的分区。

功能描述 在DLI数据多版本功能开启后，备份数据默认保留7天，您可以通过配置系统参数“dli.multi.version.retention.days”调整保留周期。保留周期外的多版本数据后续在执行insert overwrite或者truncate语句时会自动进行清理。在添加列或者修改分区表时，也可以设置表属性“dli.multi.version.retention.days”调整保留周期。 开启和关闭多版本功能SQL语法请参考开启或关闭数据多版本。 DLI数据多版本功能当前仅支持通过Hive语法创建的OBS表，具体建表SQL语法可以参考使用Hive语法创建OBS表。

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

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

语法格式 将SELECT查询结果插入到表中： 1234567 INSERT INTO DLI_TABLE SELECT field1,field2... [FROM DLI_TEST] [WHERE where_condition] [LIMIT num] [GROUP BY field] [ORDER BY field] ...; 将某条数据插入到表中： 12 INSERT INTO DLI_TABLE VALUES values_row [, values_row ...]; 覆盖插入数据 1234567 INSERT OVERWRITE TABLE DLI_TABLE SELECT field1,field2... [FROM DLI_TEST] [WHERE where_condition] [LIMIT num] [GROUP BY field] [ORDER BY field] ...;

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