Dataphin:API SQL script templates

GET/LIST (query)

-- By default, the SELECT statement returns all columns.
select col1, col2, col3,…,coln from tablename

-- If the data source is MaxCompute and the target is a partitioned table, the system automatically appends a WHERE clause that filters by the partition key.
select col1, col2, col3,…,coln from tablename where ds=${ds}

Create (operation)

insert into tablename (col1, col2, col3,… coln) values(${col1}, ${col2}, ${col3},…,${coln}

Update (operation)

• By default, the WHERE clause uses equality conditions on all primary key columns, and the SET clause includes all non-primary key columns.

• If the table has no primary key, the template includes a placeholder keycolumn=${keycolumn}. You must modify the WHERE clause.

UPDATE table_name
SET 
    column1 = ${column1},
    column2 =  ${column2},
    column3 =  ${column3}
WHERE keycolumn1=${keycolumn1} and keycolumn2=${keycolumn2}

Delete (operation)

By default, the WHERE clause uses equality conditions on all primary key columns. If the table has no primary key, the template includes the placeholder keycolumn=${keycolumn}. You must modify the WHERE clause.

DELETE FROM table_name 
WHERE keycolumn1=${keycolumn1} and keycolumn2=${keycolumn2}