SQL脚本

SQL脚本是自定义SQL组件,您可以通过SQL脚本编辑器编写SQL语句,并提交至MaxCompute执行。本文为您介绍SQL脚本的组件配置和使用场景。

组件配置

Designer仅支持通过可视化方式,配置SQL脚本组件的参数,组件参数配置如下所示。

参数

描述

使用Script模式

Script模式下,需要用户自己创建输出表,表名指定为${o1}可作为节点端口输出。更多信息,请参见SQL脚本模式

输入源

展示上游输入的表名。

是否由系统添加Create Table语句

  • 选中是否由系统添加Create Table语句复选框,则SQL脚本的最后一行SQL语句必须为Select语句,PAI会自动创建一个临时表,用来存储Select语句查询的结果。

  • 取消选中是否由系统添加Create Table语句复选框,您需要自己在SQL语句中创建数据表${o1},作为向下游传递的数据表。

SQL脚本

您可以自定义待实现功能的SQL脚本,SQL语句具体语法,详情请参见SQL概述

在代码编辑器界面,SQL脚本支持自动补全代码(智能推荐关键字代码)、内置查询模板等功能,帮助您快速编写代码,提升开发效率,详情请参见代码开发提效

SQL脚本组件的使用说明:

  • SQL脚本组件支持0~4个输入,1个输出。

  • 如果SQL脚本组件接入了上游表,输入源已自动映射成t1~t4,您可以直接调用${t1}${t2}${t3}${t4},不用写入源表名。

  • SQL脚本组件支持1个输出,您可以在SQL语句中使用${o1}代替输出数据表。

  • 您可以在SQL语句中,使用${lifecycle}参数自动获取当前工作空间设置的临时表生命周期,默认为28天。

    说明

    如果您在SQL语句中自行创建了数据表,请按需设置临时表的生命周期,详情请参见设置工作空间临时表清理时间

  • 如果您在可视化参数配置中选中了是否由系统添加Create Table语句复选框,SQL脚本的中间位置可以配置任意的SQL语句,但是最后一句必须为Select语句,PAI会自动创建一个临时表,用来存储Select语句查询的结果。

使用场景

SQL脚本组件支持以下使用场景。

场景1:SQL脚本组件从单个上游表读取数据后做一些分析

选中是否由系统添加Create Table语句复选框,PAI会自动创建一个以pai_temp开头的临时表作为输出传递给下游。SQL脚本代码配置如下所示。

select age,
(case sex when 'male' then 1 else 0 end) as sex,
(case cp when 'angina' then 0  when 'notang' then 1 else 2 end) as cp,
trestbps,
chol,
(case fbs when 'true' then 1 else 0 end) as fbs,
(case restecg when 'norm' then 0  when 'abn' then 1 else 2 end) as restecg,
thalach,
(case exang when 'true' then 1 else 0 end) as exang,
oldpeak,
(case slop when 'up' then 0  when 'flat' then 1 else 2 end) as slop,
ca,
(case thal when 'norm' then 0  when 'fix' then 1 else 2 end) as thal,
(case status  when 'sick' then 1 else 0 end) as ifHealth
from  ${t1};

image

场景2:SQL脚本组件从多个上游表读取数据做联合分析

取消选中是否由系统添加Create Table语句复选框,通过变量${o1}${lifecycle}创建一个临时表作为输出。SQL脚本代码配置如下所示。

create table if not exists ${o1} lifecycle ${lifecycle}
as
with
    a as (select * from ${t1}),
    b as (select * from ${t2}),
    c as (select * from ${t3})
select * from a
    UNION all
select * from b
    UNION all
select * from c;

image

场景3:SQL脚本组件无上游输入,直接通过表名读取源表内容

取消选中是否由系统添加Create Table语句复选框,通过变量${o1}${lifecycle}来创建一个临时表作为输出。SQL脚本代码配置如下所示。

set odps.sql.mapper.split.size=128;
set odps.sql.executionengine.batch.rowcount=100;

drop table if exists test_a_5_1;
create table test_a_5_1
as
select * from pai_online_project.wumai_data;

drop table if exists test_a_5_2;
create table test_a_5_2
as
select * from pai_online_project.wumai_data;

create table if not exists ${o1}
LIFECYCLE ${lifecycle}
as
select a.pm2, a.pm10, a.so2, b.co, b.no2
from
test_a_5_1 a join test_a_5_2 b
on a.time = b.time
and a.hour = b.hour;

select * from ${o1};

其中SQL脚本开始位置通过set语句设置执行环境变量。image

场景4:在SQL脚本组件中执行PAI命令

取消选中是否由系统添加Create Table语句复选框,通过变量${o1}${lifecycle}来创建一个临时表作为输出。SQL脚本代码配置如下所示。

drop table if exists ${o1};
PAI -name type_transform_v1 -project algo_public
-Dlifecycle=${lifecycle} -DoutputTable=${o1}
-Dcols_to_double="time,hour,pm2,pm10,so2,co,no2"
-Ddefault_double_value="0.0" -Ddefault_int_value="0"
-DreserveOldFeat="false" -DinputTable="pai_online_project.wumai_data";

image