开发SQL脚本

前提条件

• 已开通相应的云服务并在云服务中创建数据库。
• 已创建与脚本的数据连接类型匹配的数据连接，请参见新建数据连接。Flink SQL脚本不涉及该操作。
• 当前用户已锁定该脚本，否则需要通过“抢锁”锁定脚本后才能继续开发脚本。新建或导入脚本后默认被当前用户锁定，详情参见编辑锁定功能。

操作步骤

1. 参考访问DataArts Studio实例控制台，登录DataArts Studio管理控制台。
2. 在DataArts Studio控制台首页，选择对应工作空间的“数据开发”模块，进入数据开发页面。
3. 在数据开发主界面的左侧导航栏，选择“数据开发 > 脚本开发”。
4. 在脚本目录中，双击脚本名称，进入脚本开发页面。
5. 在编辑器上方，选择如表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、HetuEngine 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。

6. 在编辑器中输入SQL语句，支持输入多条SQL语句。
   不同数据源的SQL语法有所差异，开发SQL语句前请预先了解各数据源的语法规则。

   • DLI SQL脚本：请参见SQL语法参考。
   • Hive SQL脚本：请参见SQL语法参考。
   • DWS SQL脚本：请参见SQL语法参考。
   • Spark SQL脚本：请参见SQL语法参考。
   • HetuEngine SQL脚本：请参见SQL语法参考。
   • ClickHouse SQL脚本：请参见SQL语法参考。
   • IMPALA SQL脚本：请参见SQL语法参考。
   • Flink SQL脚本：请参见SQL语法参考。
   • RDS SQL脚本：请参见SQL语法参考。
   • Presto SQL脚本：请参见SQL语法参考。
   • Spark Python脚本：请参见SQL语法参考样例。
   • Doris SQL脚本：请参见SQL语法参考。
   • GBase 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、Hive SQL、Spark SQL、HetuEngine 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、HetuEngine 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管理面查看运行详情。

7. （可选）在编辑器上方，单击“格式化”，格式化SQL语句。创建Flink SQL脚本请跳过此步骤。
8. 在编辑器上方，单击“运行”。如需单独执行某部分SQL语句，请选中SQL语句再运行。SQL语句运行完成后，在编辑器下方可以查看脚本的执行历史、执行结果。Flink SQL脚本不涉及，请跳过该步骤。
   

   • 执行SQL结果最多展示1000条，仅DLI SQL支持最多10000条。如需查看更多执行结果，请参考下载或转储脚本执行结果通过下载或转储获取。
   • 对于执行结果支持如下操作：
     • 重命名：可通过双击执行结果页签的名称进行重命名，也可通过右键单击执行结果页签的名称，单击重命名。重命名不能超过16个字符。
     • 可通过右键单击执行结果页签的名称关闭当前页签、关闭左侧页签、关闭右侧页签、关闭其它页签、关闭所有页签。
   • MRS集群为非安全集群、且未限制命令白名单时，在Hive SQL执行过程中，添加application name信息后，则可以方便的根据脚本名称与执行时间在MRS的Yarn管理界面中根据job name找到对应任务。需要注意若默认引擎为tez，则要显式配置引擎为mr，使tez引擎下不生效。
   • 查看脚本执行结果时，双击任意一行的一个字段，可以查看该行的结果详情，支持复制字段名称。
   • 脚本执行历史结果可以进行权限管控，可设置为“仅自己可见”或“所有用户可见”，默认配置项请参见脚本执行历史展示。
9. 在编辑器上方，单击“保存”，保存脚本。
   如果脚本是新建且未保存过的，请配置如表2所示的参数。

   表2 保存脚本

   参数	是否必选	说明
   脚本名称	是	脚本的名称，只能包含字符：英文字母、数字、中文、中划线、下划线和点号，且长度小于等于128个字符。
   责任人	否	为该脚本指定责任人。默认为创建脚本的人为责任人。
   描述	否	脚本的描述信息。
   选择目录	是	选择脚本所属的目录，默认为根目录。

   

   如果脚本未保存，重新打开脚本时，可以从本地缓存中恢复脚本内容。

   脚本保存后，在右侧的版本里面，会自动生成一个保存版本，支持版本回滚。保存版本时，一分钟内多次保存只记录一次版本。对于中间数据比较重要时，可以通过“新增版本”按钮手动增加保存版本。

下载或转储脚本执行结果

引用字符和转义字符使用示例

• 引用字符和转义字符使用说明：
  • 引用字符：用于识别分割字段，默认值：英文双引号（"）。
  • 转义字符：在导出结果中如果需要包含特殊字符，如引号本身，可以使用转义字符（反斜杠 \ ）来表示。默认值：英文反斜杠（\）。
    1. 假设两个quote_char之间的数据内容存在第三个quote_char，则在第三个quote_char前加上escape_char，从而避免字段内容被分割。
    2. 假设数据内容中原本就存在escape_char，则在这个原有的escape_char前再加一个escape_char，避免原来的那个字符起到转义作用。
• 应用示例：

  

  在进行转储时，如果引用字符和转义字符不填，如下图所示。

  

  下载的.csv用excel打开以后如下图所示，是分成两行的。

  

  在转储时，如果引用字符和转义字符都填写，比如，引用字符和转义字符都填英文双引号（"），则下载以后查看结果如下图所示。

  

模板

在开发Hive SQL、Spark SQL、Flink SQL脚本时，系统支持可以引用脚本模板。创建模板的详细操作请参见配置模板，脚本模板的使用场景指导请参见引用脚本模板和参数模板的使用介绍。同时，系统提供了多个Hive SQL、Spark SQL、Flink SQL的公共模板，便于用户在开发常用的SQL语句时进行参考。