MaxCompute:SELECT TRANSFORM

ROW FORMAT constraints

• field_delimiter and character_escape are single characters. If you specify a multi-character string, only the first character is used.

• ROW FORMAT with inputRecordReader, outputRecordReader, or SerDe requires Hive-compatible mode. Add set odps.sql.hive.compatible=true; before the SQL statement. These Hive-specific formats may reduce execution speed.

For the full Apache Hive ROW FORMAT syntax, see Hive LanguageManual Transform.

Data format behavior

Understanding how data moves between MaxCompute and the child process prevents silent errors.

Input to child process:

• All column values are cast to STRING.

• Columns are joined with \t and written to standard input, one row per line.

Output from child process:

• Each line of standard output becomes one output row.

• Fields are split on \t (or the delimiter you set in the second ROW FORMAT).

• If the AS clause is omitted, output maps to key STRING, value STRING — the field before the first \t becomes key and all the following parts become value. This is equivalent to AS(key, value).

• If output columns are not STRING, MaxCompute calls CAST implicitly to convert values. A runtime exception may occur if the conversion fails.

Shell

Generate 50 rows numbered 1 to 50:

SELECT TRANSFORM('for i in `seq 1 50`; do echo $i; done') USING 'sh' AS (data);

Output:

+------+
| data |
+------+
| 1    |
| 2    |
| ...  |
| 50   |
+------+

Python

Generate 50 rows numbered 1 to 50:

SELECT TRANSFORM('for i in xrange(1, 51):  print(i);') USING 'python' AS (data);

AWK

Extract the second column from a table:

-- Create and populate a test table
CREATE TABLE testdata (c1 BIGINT, c2 BIGINT);
INSERT INTO TABLE testdata VALUES (1, 4), (2, 5), (3, 6);

-- Extract c2 using AWK
SELECT TRANSFORM(*) USING "awk '//{print $2}'" AS (data) FROM testdata;

Output:

+------+
| data |
+------+
| 4    |
| 5    |
| 6    |
+------+

Perl

Echo all columns back (demonstrating the default key/value output when AS is omitted):

CREATE TABLE testdata (c1 BIGINT, c2 BIGINT);
INSERT INTO TABLE testdata VALUES (1, 4), (2, 5), (3, 6);

SELECT TRANSFORM(testdata.c1, testdata.c2)
USING "perl -e 'while($input = <STDIN>){print $input;}'"
FROM testdata;

Output (no AS clause — defaults to key, value):

+-----+-------+
| key | value |
+-----+-------+
| 1   | 4     |
| 2   | 5     |
| 3   | 6     |
+-----+-------+

Using the RESOURCES clause

Specify the resource inline in the statement:

SELECT TRANSFORM(...) USING 'python myplus.py' RESOURCES 'myplus.py' AS (...) FROM ...;

Using a session-level setting

Set resources once for all SELECT TRANSFORM statements in the session:

set odps.sql.session.resources=myplus.py,bar.txt;

Multiple resource files are separated by commas. After this setting takes effect, all subsequent SELECT TRANSFORM statements in the session can access the listed resources without specifying RESOURCES in each statement.

Python script example

1. Create myplus.py:

   #!/usr/bin/env python
   import sys
   
   line = sys.stdin.readline()
   while line:
       token = line.split('\t')
       if (token[0] == '\\N') or (token[1] == '\\N'):
           print('\\N')
       else:
           print(str(token[0]) + '\t' + str(token[1]))
       line = sys.stdin.readline()

2. Upload the file as a MaxCompute resource:

   You can also upload the file through the DataWorks console. For details, see Create and use MaxCompute resources.

   add py ./myplus.py -f;

3. Run the transform:

   CREATE TABLE testdata (c1 BIGINT, c2 BIGINT);
   INSERT INTO TABLE testdata VALUES (1, 4), (2, 5), (3, 6);
   
   SELECT TRANSFORM(testdata.c1, testdata.c2)
   USING 'python myplus.py' RESOURCES 'myplus.py'
   AS (result1, result2)
   FROM testdata;

   Output:

   +---------+---------+
   | result1 | result2 |
   +---------+---------+
   | 1       | 4       |
   |         | NULL    |
   | 2       | 5       |
   |         | NULL    |
   | 3       | 6       |
   |         | NULL    |
   +---------+---------+

Java script example

1. Write and compile a Java class, then export it as Sum.jar:

   package com.aliyun.odps.test;
   import java.util.Scanner;
   
   public class Sum {
       public static void main(String[] args) {
           Scanner sc = new Scanner(System.in);
           while (sc.hasNext()) {
               String s = sc.nextLine();
               String[] tokens = s.split("\t");
               if (tokens.length < 2) {
                   throw new RuntimeException("illegal input");
               }
               if (tokens[0].equals("\\N") || tokens[1].equals("\\N")) {
                   System.out.println("\\N");
               }
               System.out.println(Long.parseLong(tokens[0]) + Long.parseLong(tokens[1]));
           }
       }
   }

2. Upload the JAR file as a MaxCompute resource:

   add jar ./Sum.jar -f;

3. Run the transform:

   CREATE TABLE testdata (c1 BIGINT, c2 BIGINT);
   INSERT INTO TABLE testdata VALUES (1, 4), (2, 5), (3, 6);
   
   SELECT TRANSFORM(testdata.c1, testdata.c2)
   USING 'java -cp Sum.jar com.aliyun.odps.test.Sum' RESOURCES 'Sum.jar'
   AS cnt
   FROM testdata;

   Output:

   +-----+
   | cnt |
   +-----+
   | 5   |
   | 7   |
   | 9   |
   +-----+

Java and Python each have a UDTF framework, but SELECT TRANSFORM is often faster to write — no dependencies, no class structure required. Offline Java scripts are resolved from JAVA_HOME; offline Python scripts from PYTHON_HOME.