MaxCompute:SELECT TRANSFORM

SELECT TRANSFORM(<arg1>, <arg2>, ...)
  [ROW FORMAT DELIMITED
    [FIELDS TERMINATED BY '<field_delimiter>' [ESCAPED BY '<character_escape>']]
    [NULL DEFINED AS '<null_value>']]
USING '<unix_command_line>'
  [RESOURCES '<res_name>' [, '<res_name>'...]]
[AS (<col1> [<type>], <col2> [<type>], ...)]
  [ROW FORMAT DELIMITED
    [FIELDS TERMINATED BY '<field_delimiter>' [ESCAPED BY '<character_escape>']]
    [NULL DEFINED AS '<null_value>']]

Restrições do ROW FORMAT

• Os parâmetros field_delimiter e character_escape aceitam apenas caracteres únicos. Se você especificar uma string com vários caracteres, somente o primeiro será utilizado.

• O uso de ROW FORMAT com inputRecordReader, outputRecordReader ou SerDe exige o modo compatível com Hive. Adicione set odps.sql.hive.compatible=true; antes da instrução SQL. Esses formatos específicos do Hive podem reduzir a velocidade de execução.

Para consultar a sintaxe completa do ROW FORMAT do Apache Hive, acesse Hive LanguageManual Transform.

Comportamento do formato de dados

Compreender como os dados trafegam entre o MaxCompute e o processo filho evita erros silenciosos.

Entrada para o processo filho:

• Todos os valores das colunas são convertidos para STRING.

• As colunas são unidas com \t e gravadas na entrada padrão, com uma linha por registro.

Saída do processo filho:

• Cada linha da saída padrão se torna uma linha de saída.

• Os campos são divididos por \t (ou pelo delimitador definido no segundo ROW FORMAT).

• Se a cláusula AS for omitida, a saída será mapeada como key STRING, value STRING — o campo anterior ao primeiro \t torna-se key e as partes seguintes tornam-se value. Isso equivale a AS(key, value).

• Se as colunas de saída não forem do tipo STRING, o MaxCompute chama CAST implicitamente para converter os valores. Uma exceção de tempo de execução poderá ocorrer se a conversão falhar.

Shell

Gere 50 linhas numeradas de 1 a 50:

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

Saída:

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

Python

Gere 50 linhas numeradas de 1 a 50:

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

AWK

Extraia a segunda coluna de uma tabela:

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

Saída:

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

Perl

Retorne todas as colunas (demonstrando a saída padrão key/value quando AS é omitido):

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;

Saída (sem cláusula AS — assume o padrão key, value):

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

Usando a cláusula RESOURCES

Especifique o recurso inline na instrução:

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

Usando uma configuração no nível da sessão

Defina os recursos uma única vez para todas as instruções SELECT TRANSFORM da sessão:

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

Separe vários arquivos de recurso por vírgulas. Após essa configuração entrar em vigor, todas as instruções SELECT TRANSFORM subsequentes na sessão poderão acessar os recursos listados sem precisar especificar RESOURCES em cada instrução.

Exemplo de script Python

1. Crie o arquivo 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. Faça upload do arquivo como um recurso do MaxCompute:

   Também é possível fazer upload do arquivo pelo console do DataWorks. Para mais detalhes, consulte Criar e usar recursos do MaxCompute .

   add py ./myplus.py -f;

3. Execute a transformação:

   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;

   Saída:

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

Exemplo de script Java

1. Escreva e compile uma classe Java e exporte-a como 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. Faça upload do arquivo JAR como um recurso do MaxCompute:

   add jar ./Sum.jar -f;

3. Execute a transformação:

   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;

   Saída:

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

Tanto Java quanto Python possuem frameworks de UDTF, mas o SELECT TRANSFORM costuma ser mais rápido de escrever, pois não exige dependências nem estrutura de classes. Scripts Java offline são resolvidos a partir de JAVA_HOME ; scripts Python offline, a partir de PYTHON_HOME .

SELECT TRANSFORM(key, value) USING '<cmd2>' FROM
(
    SELECT TRANSFORM(*) USING '<cmd1>' FROM
    (
        SELECT * FROM testdata DISTRIBUTE BY c2 SORT BY c1
    ) t DISTRIBUTE BY key SORT BY value
) t2;

@a := SELECT * FROM data DISTRIBUTE BY col2 SORT BY col1;
@b := map * USING 'cmd1' DISTRIBUTE BY col1 SORT BY col2 FROM @a;
reduce * USING 'cmd2' FROM @b;