在DataArts Studio开发DLI SQL作业

操作场景

华为云数据治理中心DataArts Studio提供了一站式数据治理平台，可以实现与DLI服务的对接，从而提供统一的数据集成、数据开发服务，方便企业对全部数据进行管控。

本节操作介绍在DataArts Studio开发DLI SQL作业的操作步骤。

开发流程

图1 在DataArts Studio开发DLI SQL作业的流程图

环境准备：准备执行作业所需的DLI资源和DataArts Studio资源。请参考环境准备。
创建数据库和表：提交SQL脚本创建数据库和表。请参考步骤1：创建数据库和表。
导入业务数据：提交SQL脚本导入业务数据。请参考步骤2：业务数据的计算与处理。
数据查询与分析：提交SQL脚本分析业务数据，例如查询单日销售情况。请参考步骤3：销售情况的查询与分析。
作用编排：将数据处理和数据分析脚本编排成一个pipeline。DataArt会按照编排好的pipeline顺序执行各个节点。请参考步骤4：作业编排。
测试作业运行：测试作业运行。请参考步骤5：测试作业运行。
设置作业调度与监控：设置作业调度属性与监控规则。请参考步骤6：设置作业周期调度和相关操作。

环境准备

DLI资源环境准备
- 配置DLI作业桶
  使用DLI服务前需配置DLI作业桶，该桶用于存储DLI作业运行过程中产生的临时数据，例如：作业日志、作业结果。具体操作请参考：配置DLI作业桶。
- 创建弹性资源池并添加SQL队列
  弹性资源池为DLI作业运行提供所需的计算资源（CPU和内存），用于灵活应对业务对计算资源变化的需求。
  
  创建弹性资源池后，您可以在弹性资源池中创建多个队列，队列关联到具体的作业和数据处理任务，是资源池中资源被实际使用和分配的基本单元，即队列是执行作业所需的具体的计算资源。
  
  同一弹性资源池中，队列之间的计算资源支持共享。通过合理设置队列的计算资源分配策略，可以提高计算资源利用率。
  
  具体操作请参考：创建弹性资源池并添加队列。
DataArts Studio资源环境准备
- 购买DataArts Studio实例
  在使用DataArts Studio提交DLI作业前，需要先购买DataArts Studio实例。
  
  具体操作请参考购买DataArts Studio基础包。
- 进入DataArts Studio实例空间
  1. 购买完成DataArts Studio实例后，单击“进入控制台”。
    图2 进入DataArts Studio实例控制台
  2. 单击“空间管理”，进入数据开发页面。
    购买DataArts Studio实例的用户，系统将默认为其创建一个默认的工作空间“default”，并赋予该用户为管理员角色。您可以使用默认的工作空间，也可以参考本章节的内容创建一个新的工作空间。
    
    如需创建新的空间请参考创建并管理工作空间。
    图3 进入DataArts Studio实例空间
    
    图4 进入DataArts Studio数据开发页面

步骤1：创建数据库和表

开发创建数据库和表的SQL脚本

数据库和表是SQL作业开发的基础，在执行作业前您需要根据业务场景定义数据库和表。

本节操作介绍提交SQL脚本创建数据库和表的操作步骤。

在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 脚本开发”。
单击“新建DLI SQL脚本”。
图5 新建DLI SQL脚本

在脚本编辑页面输入创建数据库和表的示例代码。

        
             ```SQL -- 创建数据库CREATE DATABASE IF not EXISTS supermarket_db;
-- 创建商品维表CREATE TABLE IF not EXISTS supermarketdb.products ( productid INT, -- 商品编号 productname STRING, -- 商品名称 category STRING, -- 商品类别 price DECIMAL(10,2) -- 单价 ) using parquet;
-- 创建交易表 CREATE TABLE IF not EXISTS supermarketdb.transactions (transactionid INT, -- 交易编号 productid INT, -- 商品编号 quantity INT, -- 数量 dt STRING -- 日期 ) using parquet partitioned by (dt);
-- 创建销售分析表CREATE TABLE IF not EXISTS supermarketdb.analyze (transactionid INT, -- 交易编号 productid INT, -- 商品编号 productname STRING, -- 商品名称 quantity INT, -- 数量 dt STRING -- 日期 ) using parquet partitioned by (dt); ```

单击“保存”，保存SQL脚本，本例定义脚本名称为 create_tables。
单击“提交”按钮执行脚本创建数据库和表。

创建SQL作业运行脚本
1. 在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 作业开发”。
  图6 新建作业
2. 编辑作业信息，本例定义SQL作业名称为 “job_create_tables”。
  图7 编辑作业信息
3. 在作业开发页面，拖动DLI SQL节点到画布中，并单击节点编辑属性。
  - SQL或脚本：本例选择“SQL脚本”。并选择2.b中创建的脚本。
  - 数据库名称：选择SQL脚本中设置的数据库。
  - 队列名称：选择步骤▪创建弹性资源池并添加SQL队列中创建的SQL队列。
  更多属性参数配置请参考DLI SQL属性参数说明。
  
  图8 编辑DLI SQL节点属性
4. 属性编辑完成后，单击“保存”，保存属性配置信息。
配置作业调度

由于创建库表只需要执行一次，所以本示例只设置为单次调度。
1. 鼠标左键单击作业画布空白处。
2. 单击 “调度配置” ，选择 “单次调度”（该作业只会被调度一次，后续不会再被自动调度）。
  图9 配置作业调度
3. 完成调度配置后单击“执行调度”。
  单击“前往运维中心”可以查看作业运行状况。

步骤2：业务数据的计算与处理

开发导入业务数据的SQL脚本

本节操作介绍提交SQL脚本导入业务数据的操作步骤。

在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 脚本开发”。
单击“新建DLI SQL脚本”。
图10 新建DLI SQL脚本

在脚本编辑页面输入分析数据的示例代码。

        
              SQL -- 实际业务中数据一般来自其他数据源，本示例简化了数据入库逻辑，模拟插入商品数据。 
 INSERT INTO supermarketdb.products (productid, productname, category, price) VALUES
(1001, '洗发水', '日用品', 39.90),
(1002, '牙膏', '日用品', 15.90),
(1003, '方便面', '食品', 4.50),
(1004, '可乐', '饮料', 3.50);
-- 实际业务中数据一般来自其他数据源，本示例简化了数据入库逻辑，模拟插入交易记录。 
INSERT INTO supermarketdb.transactions (transactionid, productid, quantity, dt) VALUES (1, 1001, 50, '2024-11-01'), -- 销售50瓶洗发水
(2, 1002, 100, '2024-11-01'), -- 销售100支牙膏 (3, 1003, 30, '2024-11-02'), -- 销售30包方便面
(4, 1004, 24, '2024-11-02'); -- 销售24瓶可乐
-- 模拟超市业务分析，查询某个商品的交易记录
INSERT INTO supermarketdb.analyze SELECT t.transactionid, t.productid, p.productname, t.quantity, t.dt
FROM supermarketdb.transactions t 
JOIN supermarketdb.products p ON t.productid = p.productid 
WHERE t.dt = '2024-11-01';

单击“保存”，保存SQL脚本，本例定义脚本名称为 job_process_data。
单击“提交”执行脚本。

创建SQL作业
1. 在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 作业开发”。
  图11 新建作业
2. 编辑作业信息，本例定义SQL作业名称为 “job_process_data”。
  图12 编辑作业信息
3. 在作业开发页面，拖动DLI SQL节点到画布中，并单击节点编辑属性。
  - SQL或脚本：本例选择“SQL脚本”。并选择1中创建的脚本。
  - 数据库名称：选择SQL脚本中设置的数据库。
  - 队列名称：选择步骤▪创建弹性资源池并添加SQL队列中创建的SQL队列。
  - 环境变量："DLI环境变量" 为可选项。
    本例中添加的参数含义如下：
    
    spark.sql.optimizer.dynamicPartitionPruning.enabled = true
    - 该配置项用于启用或禁用动态分区修剪。在执行SQL查询时，动态分区修剪可以帮助减少需要扫描的数据量，提高查询性能。
    - 配置为true时，代表启用动态分区修剪，SQL会在查询中自动检测并删除那些不满足WHERE子句条件的分区，适用于在处理具有大量分区的表时。
    - 如果SQL查询中包含大量的嵌套left join操作，并且表有大量的动态分区时，这可能会导致在数据解析时消耗大量的内存资源，导致Driver节点的内存不足，并触发频繁的Full GC。
    - 在这种情况下，可以配置该参数为false即禁用动态分区修剪优化，有助于减少内存使用，避免内存溢出和频繁的Full GC。
    但禁用此优化可能会降低查询性能，禁用后Spark将不会自动修剪掉那些不满足条件的分区。
  更多属性参数配置请参考DLI SQL属性参数说明。
  
  图13 编辑DLI SQL节点属性
4. 属性编辑完成后，单击“保存”，保存属性配置信息。

步骤3：销售情况的查询与分析

开发数据分析与处理的SQL脚本

本节操作介绍提交SQL脚本分析数据的操作步骤。

在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 脚本开发”。
单击“新建DLI SQL脚本”。
图14 新建DLI SQL脚本

在脚本编辑页面输入分析数据的示例代码。

      
           -- 查询单日销售情况 
SELECT transaction_id, productid, productname, quantity, dt FROM supermarket_db.analyze WHERE dt = '2024-11-01';

单击“保存”，保存SQL脚本，本例定义脚本名称为 select_analyze_data。
单击“提交”执行脚本。

步骤4：作业编排

在作业 “job_process_data”中新建一个DLI SQL节点 "select_analyze_data"。并单击节点编辑属性。
- SQL或脚本：本例选择“SQL脚本”。并选择1中创建的脚本。
- 数据库名称：选择SQL脚本中设置的数据库。
- 队列名称：选择步骤▪创建弹性资源池并添加SQL队列中创建的SQL队列。
更多属性参数配置请参考DLI SQL属性参数说明。

图15 编辑DLI SQL节点属性
属性编辑完成后，单击“保存”，保存属性配置信息。
将这两个节点编排成一个pipeline。DataArt会按照编排好的pipeline顺序执行各个节点。然后单击左上角 “保存” 和 “提交”。

步骤5：测试作业运行

作业编排完成后，单击“测试运行”，测试运行作业。

运行结束后，可打开“select_analyze_data”SQL脚本，单击“运行”，查询分析销售明细。

如果查询结果符合预期，可以继续执行步骤6：设置作业周期调度设置作业周期调度。

图16 执行select_analyze_data脚本
点击放大

步骤6：设置作业周期调度

在DataArts Studio数据开发页面，选择左侧导航栏的“数据开发 > 作业开发”。
双击“job_process_data”。
单击右侧导航栏中的 “调度配置”。
选择周期调度，并设置调度属性。
本例中，该作业的调度策略从2024/11/22 10:15:00开始生效，且首次执行调度的时间是2024/11/22 10:20:00，调度周期建个1天，即后续每天10:20:00 AM会自动调度这些这个作业，会按照编排好的pipeline顺序执行作业中的每个节点。

图17 配置作业调度
依次单击 “保存” 、“提交” 和 “执行调度” 按钮，即可完成作业周期调度配置。

了解更多作业调度设置请参考调度作业。

常见问题

如果 DataArts 作业失败，且 DataArts 提供的日志不够详细，怎么办？还能从哪里找更具体的日志？您可以可通过 DataArts 的日志找到 DLI job id，然后根据 DLI job id 在DLI控制台中找到具体的作业。

在DLI控制台中找到具体的作业，单击归档日志即可查看详细日志：

可以通过 DataArts 的 nodename 或 jobname 在DLI 控制台搜索作业：
如果在运行复杂DLI作业时遇到权限类报错，应该怎么办？
使用DLI的过程中需要与其他云服务协同工作，因此需要您将部分服务的操作权限委托给DLI服务，确保DLI具备基本使用的权限，让DLI服务以您的身份使用其他云服务，代替您进行一些资源运维工作。

了解更多：配置DLI云服务委托权限