开发SQL脚本
数据开发支持对SQL脚本进行在线开发、调试和执行,开发完成的脚本可以在作业中调度运行(请参见开发Pipeline作业)。
前提条件
操作步骤
- 参考访问DataArts Studio实例控制台,登录DataArts Studio管理控制台。
- 在DataArts Studio控制台首页,选择对应工作空间的“数据开发”模块,进入数据开发页面。
- 在数据开发主界面的左侧导航栏,选择 。
- 在脚本目录中,双击脚本名称,进入脚本开发页面。
- 在编辑器上方,选择如表1所示的属性。创建Flink SQL脚本时请跳过此步骤。
表1 SQL脚本属性 属性
说明
数据连接
选择数据连接。
DLI数据目录
选择DLI的数据目录。
- 在DLI默认的数据目录dli。
- 在DLI所绑定的LakeFormation已创建元数据catalog。
数据库
选择数据库。
DLI数据目录如果选择DLI默认的数据目录dli,表示为DLI的数据库和数据表。
DLI数据目录如果选择DLI所绑定的LakeFormation已创建元数据catalog,表示为LakeFormation的数据库和数据表。
资源队列
输入执行作业的资源队列。
Impala SQL、Hive SQL只能手动输入,不支持选择。
选择执行DLI作业的资源队列。当脚本为DLI SQL时,配置该参数。选择了资源队列以后,单击可以查看队列性能,系统支持查看DLI运行作业数和队列CU使用量,系统显示队列近24小时性能情况。
说明:- 当队列选择为“default”时,会提示“暂不支持"default"队列性能展示”。
- 还未选择资源队列时,图标灰化不可查看队列性能。
如需新建资源队列,请参考以下方法:- 单击,进入DLI的“购买队列”页面新建资源队列。
- 前往DLI管理控制台进行新建。
说明:DLI提供默认资源队列“default”,仅用于用户体验,用户间可能会出现抢占资源的情况,不能保证每次都可以得到资源执行相关操作。当遇到执行时间较长或无法执行的情况,建议您在业务低峰期再次重试,或选择自建队列运行业务。
另外,“default”队列不支持insert、load、cat命令。
如需以“key/value”的形式设置提交SQL作业的属性,请单击。最多可设置10个属性,属性说明如下:
说明:- 环境变量配置项需要以"hoodie."或"dli.sql."或"dli.ext."或"dli.jobs."或"spark.sql."或"spark.scheduler.pool"开头。
- 环境变量为dli.sql.autoBroadcastJoinThreshold时,值只能为整数,环境变量为dli.sql.shuffle.partitions时,值只能为正整数。
- 环境变量的key为dli.sql.shuffle.partitions或dli.sql.autoBroadcastJoinThreshold时,不能包含><符号。
- 如果作业和脚本中同时配置了同名的参数,作业中配置的值会覆盖脚本中的值。
- dli.sql.autoBroadcastJoinThreshold(自动使用BroadcastJoin的数据量阈值)
- dli.sql.shuffle.partitions(指定Shuffle过程中Partition的个数)
- dli.sql.cbo.enabled(是否打开CBO优化策略)
- dli.sql.cbo.joinReorder.enabled(开启CBO优化时,是否允许重新调整join的顺序)
- dli.sql.multiLevelDir.enabled(OBS表的指定目录或OBS表分区表的分区目录下有子目录时,是否查询子目录的内容;默认不查询)
- dli.sql.dynamicPartitionOverwrite.enabled(在动态分区模式时,只会重写查询中的数据涉及的分区,未涉及的分区不删除)
说明:在非调度场景的DLI SQL脚本运行和DLI SQL单任务作业测试运行时,系统会默认开启以下四个配置参数:
- spark.sql.adaptive.enabled(启用AQE,使Spark能够根据正在处理的数据的特征动态优化查询的执行计划,可以通过减少需要处理的数据量来提高性能。)
- spark.sql.adaptive.join.enabled(启用AQE用于连接操作,可以通过根据正在处理的数据动态选择最佳连接算法来提高性能。)
- spark.sql.adaptive.skewedJoin.enabled(启用AQE用于倾斜的连接操作,可以通过自动检测倾斜的数据并相应地优化连接算法来提高性能)
- spark.sql.mergeSmallFiles.enabled(启用合并小文件功能,可以通过将小文件合并成较大的文件来提高性能,可以减少处理许多小文件的时间,并通过减少需要从远程存储中读取的文件数量来提高数据本地性。)
如果不使用的话,可以手动配置相关参数进行关闭,参数值设置为false。
- 在编辑器中输入SQL语句,支持输入多条SQL语句。
- SQL语句之间以“;”分隔。如果其它地方使用“;”,请通过“\”进行转义。例如:
select 1; select * from a where b="dsfa\;"; --example 1\;example 2.
- RDS SQL当前不支持begin ... commit事务语法,若有需要,请使用start transaction ... commit事务语法。
- 脚本内容大小不能超过16MB。
- 使用SQL语句获取的系统日期和通过数据库工具获取的系统日期是不一样,查询结果存到数据库是以YYYY-MM-DD格式,而页面显示查询结果是经过转换后的格式。
- 当前用户提交Spark SQL脚本到MRS时,默认提交至其绑定的租户队列(绑定队列即用户绑定的租户类型角色所对应的队列)中运行。当绑定多个队列时,系统会优先根据内部排序选择队列进行提交;如果需要给该用户使用固定一个队列进行提交, 可以登录FusionInsight Manager界面,在"租户资源 > 动态资源计划 > 全局用户策略"中给该用户配置默认队列,详细操作请参见管理全局用户策略。
- Flink SQL脚本支持语法检查。单击“语法检查”,SQL语句校验完成后,可以在下方查看语法校验结果。
为了方便脚本开发,数据开发模块提供了如下能力:- 脚本编辑器支持使用如下快捷键,以提升脚本开发效率。
- F8:运行
- F9:停止
- Ctrl + /:注释或解除注释光标所在行或代码块
- Ctrl + S:保存
- Ctrl + Z:撤销
- Ctrl + F:查找
- Ctrl + Shift + R:替换
- Ctrl + X:剪切,光标未选中时剪切一行
- Alt + 鼠标拖动:列模式编辑,修改一整块内容
- Ctrl + 鼠标点选:多列模式编辑,多行缩进
- Shift + Ctrl + K:删除当前行
- Ctrl + →或Ctrl + ←:向右或向左按单词移动光标
- Ctrl + Home或Ctrl + End:移至当前文件的最前或最后
- Home或End:移至当前行最前或最后
- Ctrl + Shift + L:鼠标双击相同的字符串后,为所有相同的字符串添加光标,实现批量修改
- Ctrl + D:删除一行
- Shift + Ctrl + U:解锁
- Ctrl + Alt + K:同词选择
- Ctrl + B:格式化
- Ctrl + Shift + Z:重做
- Ctrl + Enter:执行所选行/选中内容
- Ctrl + Alt + F:标记
- Ctrl + Shift + K:查找上一个
- Ctrl + K:查找下一个
- Ctrl + Backspace:删除左侧单词
- Ctrl + Delete:删除右侧单词
- Alt + Backspace:删除至行首
- Alt + Delete:删除至行尾
- Alt + Shift-Left:选择行首
- Alt + Shift-Right:选择行尾
- 支持系统函数功能(当前Flink SQL、Spark SQL、ClickHouse SQL、Presto SQL不支持该功能)。
- 支持可视化读取数据表生成SQL语句功能(当前Flink SQL、Spark Python、ClickHouse SQL、Presto SQL不支持该功能)。
单击编辑器右侧的“数据表”,显示当前数据库或schema下的所有表,可以根据您的需要勾选数据表和对应的列名,在右下角单击“生成SQL语句”,生成的SQL语句需要您手动格式化。
- 支持脚本参数(当前仅Flink SQL不支持该功能)。
在SQL语句中直接写入脚本参数,调试脚本时可以在脚本编辑器下方输入参数值。如果脚本被作业引用,在作业开发页面可以配置参数值,参数值支持使用EL表达式(参见表达式概述)。
SQL脚本中的参数如果涉及变量,变量的格式应该与脚本变量定义中设置的格式保持一致,如果不一致,变量将不会被识别。
脚本示例如下,其中str1是参数名称,只支持英文字母、数字、“-”、“_”、“<”和“>”,最大长度为16字符,且参数名称不允许重名。
select ${str1} from data;
另外,对于MRS Spark SQL和MRS Hive SQL脚本的运行程序参数,除了在SQL脚本中参考语句“set hive.exec.parallel=true;”配置参数,也可以在对应作业节点属性的“运行程序参数”中配置该参数。图1 运行程序参数
- 支持设置脚本责任人
- 企业模式下,支持从脚本开发界面快速前往发布。标放置在上,单击“前往发布”,进入待发布任务界面。
- 在MRS API连接方式下,Spark SQL和HIve SQL脚本支持配置指定参数和参数值。代理连接不支持。
单击右上角的,设置相关脚本的环境变量。举例如下所示:
设置Hive SQL脚本的环境变量:
--hiveconf hive.merge.mapfiles=true;
--hiveconf mapred.job.queue.name=queue1
设置Spark SQL脚本的环境变量:
--num-executors 1
--executor-cores 4
--queue queue2
前者表示参数名,后者表示参数值。
脚本运行完之后,到MRS管理面查看运行详情。
- SQL语句之间以“;”分隔。如果其它地方使用“;”,请通过“\”进行转义。例如:
- (可选)在编辑器上方,单击“格式化”,格式化SQL语句。创建Flink SQL脚本请跳过此步骤。
- 在编辑器上方,单击“运行”。如需单独执行某部分SQL语句,请选中SQL语句再运行。SQL语句运行完成后,在编辑器下方可以查看脚本的执行历史、执行结果。Flink SQL脚本不涉及,请跳过该步骤。
- 执行SQL结果最多展示1000条,仅DLI SQL支持最多10000条。如需查看更多执行结果,请参考下载或转储脚本执行结果通过下载或转储获取。
- 对于执行结果支持如下操作:
- 重命名:可通过双击执行结果页签的名称进行重命名,也可通过右键单击执行结果页签的名称,单击重命名。重命名不能超过16个字符。
- 可通过右键单击执行结果页签的名称关闭当前页签、关闭左侧页签、关闭右侧页签、关闭其它页签、关闭所有页签。
- MRS集群为非安全集群、且未限制命令白名单时,在Hive SQL执行过程中,添加application name信息后,则可以方便的根据脚本名称与执行时间在MRS的Yarn管理界面中根据job name找到对应任务。需要注意若默认引擎为tez,则要显式配置引擎为mr,使tez引擎下不生效。
- 脚本执行历史结果可以进行权限管控,可设置为“仅自己可见”或“所有用户可见”,默认配置项请参见脚本执行历史展示。
- 在编辑器上方,单击“保存”,保存脚本。
如果脚本是新建且未保存过的,请配置如表2所示的参数。
表2 保存脚本 参数
是否必选
说明
脚本名称
是
脚本的名称,只能包含字符:英文字母、数字、中文、中划线、下划线和点号,且长度小于等于128个字符。
责任人
否
为该脚本指定责任人。默认为创建脚本的人为责任人。
描述
否
脚本的描述信息。
选择目录
是
选择脚本所属的目录,默认为根目录。
如果脚本未保存,重新打开脚本时,可以从本地缓存中恢复脚本内容。
脚本保存后,在右侧的版本里面,会自动生成一个保存版本,支持版本回滚。保存版本时,一分钟内多次保存只记录一次版本。对于中间数据比较重要时,可以通过“新增版本”按钮手动增加保存版本。
下载或转储脚本执行结果
- 脚本执行完成后在“执行结果”中,单击“下载”可以直接下载CSV格式的结果文件到本地。可以在下载中心查看下载记录。
- 脚本执行完成后在“执行结果”中,单击“转储”可以将脚本执行结果转储为CSV和JSON格式的结果文件到OBS中,详情请参见表3。
- 转储功能依赖于OBS服务,如无OBS服务,则不支持该功能。
- 当前仅支持转储SQL脚本查询(query)类语句的结果。
- DataArts Studio的下载或转储的SQL结果中,如果存在英文逗号、换行符等这种特殊符号,可能会导致数据错乱、行数变多等的问题。
表3 转储配置 参数
是否必选
说明
数据格式
是
目前支持导出CSV和JSON格式的结果文件。
资源队列
否
选择执行导出操作的DLI队列。当脚本为DLI SQL时,配置该参数。
压缩格式
否
选择压缩格式。当脚本为DLI SQL时,配置该参数。
- none
- bzip2
- deflate
- gzip
存储路径
是
设置结果文件的OBS存储路径。选择OBS路径后,您需要在选择的路径后方自定义一个文件夹名称,系统将在OBS路径下创建文件夹,用于存放结果文件。
您也可以到下载中心配置默认的OBS路径地址,配置好后在转储时会默认填写。
覆盖类型
否
如果“存储路径”中,您自定义的文件夹在OBS路径中已存在,选择覆盖类型。当脚本为DLI SQL时,配置该参数。
- 覆盖:删除OBS路径中已有的重名文件夹,重新创建自定义的文件夹。
- 存在即报错:系统返回错误信息,退出导出操作。
是否导出列名
否
是:导出列名
否:不导出列名
字符集
否
- UTF-8:默认字符集。
- GB2312:当导出数据中包含中文字符集时,推荐使用此字符集。
- GBK:国家标准GB2312基础上扩容后兼容GB2312的标准。
引用字符
否
仅在数据格式为csv格式时支持配置引用字符。
引用字符在导出作业结果时用于标识文本字段的开始和结束,即用于分割字段。
仅支持设置一个字符。默认值是英文双引号(")。
主要用于处理包含空格、特殊字符或与分隔符相同字符的数据。
关于“引用字符”和“转义字符”的使用示例请参考引用字符和转义字符使用示例。
转义字符
否
仅在数据格式为csv格式时支持配置转义字符。
在导出结果中如果需要包含特殊字符,如引号本身,可以使用转义字符(反斜杠 \ )来表示。
仅支持设置一个字符。默认值是英文反斜杠(\)。
常用转义字符的场景:
- 假设两个引用字符之间的数据内容存在第三个引用字符,则在第三个引用字符前加上转义字符,从而避免字段内容被分割。
- 假设数据内容中原本就存在转义字符,则在这个原有的转义字符前再加一个转义字符,避免原来的那个字符起到转义作用。
关于“引用字符”和“转义字符”的使用示例请参考引用字符和转义字符使用示例。
SQL类型 |
在线查看最大结果条数 |
下载最大结果条数 |
转储最大结果 |
---|---|---|---|
DLI |
10000 |
1000 |
无限制 |
Hive |
1000 |
1000 |
10000条或3MB |
DWS |
1000 |
1000 |
10000条或3MB |
Spark |
1000 |
1000 |
10000条或3MB |
RDS |
1000 |
1000 |
不支持 |
Presto |
1000 |
下载结果直接转储至OBS,条数无限制。 |
无限制 |
ClickHouse |
1000 |
1000 |
10000条或3MB |
HetuEngine |
1000 |
1000 |
10000条或3MB |
Impala |
1000 |
1000 |
10000条或3MB |
Doris |
1000 |
1000 |
1000条 |
引用字符和转义字符使用示例
- 引用字符和转义字符使用说明:
- 引用字符:用于识别分割字段,默认值:英文双引号(")。
- 转义字符:在导出结果中如果需要包含特殊字符,如引号本身,可以使用转义字符(反斜杠 \ )来表示。默认值:英文反斜杠(\)。
- 假设两个quote_char之间的数据内容存在第三个quote_char,则在第三个quote_char前加上escape_char,从而避免字段内容被分割。
- 假设数据内容中原本就存在escape_char,则在这个原有的escape_char前再加一个escape_char,避免原来的那个字符起到转义作用。
- 应用示例:
在进行转储时,如果引用字符和转义字符不填,如下图所示。
下载的.csv用excel打开以后如下图所示,是分成两行的。
在转储时,如果引用字符和转义字符都填写,比如,引用字符和转义字符都填英文双引号("),则下载以后查看结果如下图所示。