开发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只能手动输入，不支持选择。 选择执行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语法参考。
   • ClickHouse SQL脚本：请参见SQL语法参考。
   • IMPALA SQL脚本：请参见SQL语法参考。
   • Flink SQL脚本：请参见SQL语法参考。
   • RDS SQL脚本：请参见SQL语法参考。
   • Presto SQL脚本：请参见SQL语法参考。
   • Spark Python脚本：请参见SQL语法参考样例。
   • Doris 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脚本支持语法检查。单击“语法检查”，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管理面查看运行详情。

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打开以后如下图所示，是分成两行的。

  

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