All Products
Search

This Product

    Platform For AI:SQL script

    Document Center

    Platform For AI:SQL script

    Last Updated:Jun 20, 2026

    The SQL Script component allows you to write custom SQL statements and execute them in MaxCompute. This topic describes the component configuration and its use cases.

    Component configuration

    In Machine Learning Designer, you can configure the parameters for the SQL Script component on its configuration tab.

    Parameter

    Description

    Use script mode

    In script mode, you must create an output table named ${o1} to serve as the node's output port. For more information, see SQL Script Mode.

    Input source

    Displays the names of the upstream input tables.

    Whether the system adds a Create Table statement

    • If you select the Whether the system adds a Create Table statement checkbox, the last statement in the SQL script must be a Select statement. Platform for AI (PAI) automatically creates a temporary table to store the query results.

    • If you clear the Whether the system adds a Create Table statement checkbox, you must create a data table named ${o1} in your SQL statement to pass data to downstream components.

    SQL Script

    You can write a custom SQL script to implement your logic. For more information about the SQL syntax, see SQL Overview.

    The code editor provides features such as code completion (with intelligent keyword suggestions) and built-in query templates to help you write code faster. For more information, see Improve code development efficiency.

    • The SQL Script component supports zero to four inputs and one output.

    • When the SQL Script component is connected to upstream tables, it automatically maps the input sources to t1, t2, t3, and t4. You can use the variables ${t1}, ${t2}, ${t3}, or ${t4} to directly reference these tables without specifying their source table names.

    • The SQL Script component supports one output. You can use the ${o1} variable in your SQL statement to represent the output data table.

    • You can use the ${lifecycle} parameter in your SQL statements to apply the default temporary table lifecycle configured for the workspace. The default value is 28 days.

      Note

      If you create a data table in your SQL statement, set the lifecycle for the temporary table as needed. For more information, see Set the cleanup time for temporary tables in a workspace.

    • If you select the Whether the system adds a Create Table statement checkbox in the parameter configuration panel, you can include other SQL statements in the script, but the last statement must be a Select statement. PAI automatically creates a temporary table to store the query results.

    Use cases

    The SQL Script component supports the following use cases.

    Scenario 1: Analyze data from a single upstream table

    Select the Whether the system adds a Create Table statement checkbox. PAI automatically creates a temporary table as the output and passes it to downstream components. The following code is an example of the SQL script:

    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};

    In the component pane, search for SQL Script and then drag the SQL Script component to the canvas. Connect the output of the Read Data Table-1 component to the input of the SQL Script-1 component.

    Scenario 2: Combine data from multiple upstream tables

    Clear the Whether the system adds a Create Table statement checkbox. Use the ${o1} and ${lifecycle} variables to create a temporary table as the output. The following code is an example of the SQL script:

    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;

    In the component pane, search for SQL Script and drag it to the canvas. Connect three Read Data Table nodes to the SQL Script node. On the parameter settings panel, map the input sources as follows: t1 to Read Data Table-1, t2 to Read Data Table-2, and t3 to Read Data Table-3.

    Scenario 3: Read data by table name

    Clear the Whether the system adds a Create Table statement checkbox. Use the ${o1} and ${lifecycle} variables to create a temporary table as the output. The following code is an example of the SQL script:

    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};

    The set statements at the beginning of the SQL script configure execution environment variables.

    Scenario 4: Run a PAI command

    Clear the Whether the system adds a Create Table statement checkbox. Use the ${o1} and ${lifecycle} variables to create a temporary table as the output. The following code is an example of the SQL script:

    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";