MaxCompute:SELECT TRANSFORM

Description

Les performances de SELECT TRANSFORM et des UDTF varient selon les scénarios. Les résultats des tests comparatifs montrent que SELECT TRANSFORM offre de meilleures performances que les UDTF lors de l'interrogation de petits volumes de données. En revanche, les UDTF surpassent SELECT TRANSFORM pour les grands volumes de données. SELECT TRANSFORM est simple à développer et convient mieux aux requêtes ponctuelles.

SELECT TRANSFORM est compatible avec divers langages de programmation. Cette instruction vous permet d'écrire des scripts directement dans les commandes pour implémenter des fonctionnalités simples. Des langages tels qu'AWK, Python, Perl et Shell prennent en charge cette opération. Ainsi, vous n'avez pas besoin d'effectuer des opérations supplémentaires, telles que l'écriture de fichiers de script ou le téléchargement de ressources. Cela simplifie le processus de développement. Pour implémenter des fonctionnalités complexes, vous pouvez télécharger des fichiers de script. Pour plus d'informations, consultez les rubriques Exemple d'appel de scripts Python et Exemple d'appel de scripts Java.

Limites

PHP et Ruby ne sont pas déployés sur les clusters de calcul MaxCompute. Par conséquent, vous ne pouvez pas appeler de scripts PHP ou Ruby dans MaxCompute.

Syntaxe

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>, <col2> ...)]
(row format delimited (fields terminated by <field_delimiter> (escaped by <character_escape>)) (null defined as <null_value>))

• Mot-clé SELECT TRANSFORM : obligatoire. Vous pouvez remplacer ce mot-clé par map ou reduce, qui ont la même sémantique. Pour clarifier la syntaxe, nous vous recommandons d'utiliser SELECT TRANSFORM.

• arg1,arg2... : obligatoire. Ces paramètres spécifient les données d'entrée. Leurs formats sont identiques à ceux des instructions SELECT. Si vous utilisez les formats par défaut, les résultats des expressions pour chaque paramètre sont implicitement convertis en valeurs de type STRING. Ensuite, les paramètres sont combinés avec \t et transmis au processus enfant spécifié.

• Clause ROW FORMAT : facultative. Cette clause vous permet de personnaliser le format des données d'entrée et de sortie.

  La syntaxe utilise deux clauses ROW FORMAT. La première définit le format des données d'entrée, tandis que la seconde spécifie celui des données de sortie. Par défaut, le séparateur de colonne est \t, le séparateur de ligne est \n et la valeur nulle est représentée par \N.

  Remarque

  • Les paramètres field_delimiter et character_escape n'acceptent qu'un seul caractère. Si vous indiquez une chaîne, seul le premier caractère est pris en compte.

  • MaxCompute prend en charge les syntaxes définies par Apache Hive, telles que inputRecordReader, outputRecordReader et SerDe. Pour les utiliser, activez l'édition des types de données compatible avec Hive en ajoutant set odps.sql.hive.compatible=true; avant vos instructions SQL. Consultez la documentation Hive pour plus de détails sur les syntaxes supportées par Apache Hive.

  • L'utilisation de syntaxes propres à Apache Hive, comme inputRecordReader ou outputRecordReader, peut ralentir l'exécution des instructions SQL.

• Clause USING : obligatoire. Elle indique la commande permettant de lancer un processus enfant.

  • Dans la plupart des instructions SQL MaxCompute, la clause USING désigne des ressources. Dans une instruction SELECT TRANSFORM, elle spécifie la commande de démarrage du processus enfant, afin d'assurer la compatibilité avec la syntaxe d'Apache Hive.

  • La syntaxe de la clause USING ressemble à celle d'un script shell, mais elle crée directement un processus enfant à partir de la commande indiquée plutôt que d'exécuter un script. Certaines fonctionnalités shell, telles que la redirection des entrées/sorties, les pipelines ou les boucles, ne sont donc pas disponibles. Vous pouvez toutefois invoquer un script shell comme commande de lancement si nécessaire.

• Clause RESOURCES : facultative. Elle définit les ressources accessibles au processus enfant. Deux méthodes sont possibles :

  • Indiquez les ressources directement dans la clause RESOURCES, par exemple using 'sh foo.sh bar.txt' resources 'foo.sh','bar.txt'.

  • Utilisez le paramètre set odps.sql.session.resources. Ajoutez par exemple set odps.sql.session.resources=foo.sh,bar.txt; avant vos instructions SQL.

    Une fois cette configuration globale appliquée, toutes les instructions SELECT TRANSFORM peuvent accéder aux ressources indiquées. Séparez les différents fichiers par des virgules (,).

• Clause AS : facultative. Elle précise les colonnes de sortie et leurs types de données, par exemple as(col1 bigint, col2 boolean).

  • Si aucun type n'est indiqué, STRING est appliqué par défaut. Ainsi, AS(col1, col2) signifie que les colonnes de sortie sont de type STRING.

  • Les données de sortie proviennent de l'analyse de la sortie standard du processus enfant. Si le type spécifié diffère de STRING, MaxCompute appelle implicitement la fonction CAST pour effectuer la conversion, ce qui peut provoquer une exception lors de l'exécution.

  • Il est impossible de définir les types uniquement pour certaines colonnes, comme dans as(col1, col2:bigint).

  • Si vous omettez la clause AS, le champ précédant le premier \t dans la sortie standard devient la clé, et le reste constitue la valeur. Cela équivaut à AS(key, value).

Exemple d'appel de commandes shell

select transform(script) using 'sh' as (data) 
from (
        select  'for i in `seq 1 50`; do echo $i; done' as script
      ) t
;
-- The preceding statement is equivalent to the following statement: 
select transform('for i in `seq 1 50`; do echo $i; done') using 'sh' as (data);

+------------+
| data       |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
| 10         |
| 11         |
| 12         |
| 13         |
| 14         |
| 15         |
| 16         |
| 17         |
| 18         |
| 19         |
| 20         |
| 21         |
| 22         |
| 23         |
| 24         |
| 25         |
| 26         |
| 27         |
| 28         |
| 29         |
| 30         |
| 31         |
| 32         |
| 33         |
| 34         |
| 35         |
| 36         |
| 37         |
| 38         |
| 39         |
| 40         |
| 41         |
| 42         |
| 43         |
| 44         |
| 45         |
| 46         |
| 47         |
| 48         |
| 49         |
| 50         |
+------------+

Exemple d'appel de commandes Python

select transform(script) using 'python' as (data) 
from (
        select  'for i in xrange(1, 51):  print(i);' as script
      ) t
;
-- The preceding statement is equivalent to the following statement: 
select transform('for i in xrange(1, 51):  print(i);') using 'python' as (data);

+------------+
| data       |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
| 10         |
| 11         |
| 12         |
| 13         |
| 14         |
| 15         |
| 16         |
| 17         |
| 18         |
| 19         |
| 20         |
| 21         |
| 22         |
| 23         |
| 24         |
| 25         |
| 26         |
| 27         |
| 28         |
| 29         |
| 30         |
| 31         |
| 32         |
| 33         |
| 34         |
| 35         |
| 36         |
| 37         |
| 38         |
| 39         |
| 40         |
| 41         |
| 42         |
| 43         |
| 44         |
| 45         |
| 46         |
| 47         |
| 48         |
| 49         |
| 50         |
+------------+

Exemple d'appel de commandes AWK

-- Create a test table. 
create table testdata(c1 bigint,c2 bigint);
-- Insert test data into the test table.  
insert into table testdata values (1,4),(2,5),(3,6); 
-- Execute the SELECT TRANSFORM statement.  
select transform(*) using "awk '//{print $2}'" as (data) from testdata;

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

Exemple d'appel de commandes Perl

-- Create a test table. 
create table testdata(c1 bigint,c2 bigint);
-- Insert test data into the test table.  
insert into table testdata values (1,4),(2,5),(3,6); 
-- Execute the SELECT TRANSFORM statement.  
select transform(testdata.c1, testdata.c2) using "perl -e 'while($input = <STDIN>){print $input;}'" from testdata;

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

Exemple d'appel de scripts Python

1. Créez le fichier myplus.py. Exemple de code :

   #!/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. Ajoutez le fichier de script Python en tant que ressource dans MaxCompute.

   add py ./myplus.py -f;

   Remarque

   Vous pouvez également utiliser la console DataWorks pour ajouter le fichier Python en tant que ressource. Pour plus d'informations, consultez la rubrique Créer et utiliser des ressources MaxCompute.

3. Exécutez l'instruction SELECT TRANSFORM pour appeler ce fichier.

   -- Create a test table. 
   create table testdata(c1 bigint,c2 bigint);
   -- Insert test data into the test table.  
   insert into table testdata values (1,4),(2,5),(3,6); 
   -- Execute the SELECT TRANSFORM statement.  
   select 
   transform (testdata.c1, testdata.c2) 
   using 'python myplus.py' resources 'myplus.py' 
   as (result1,result2) 
   from testdata;
   -- The preceding statements are equivalent to the following statements: 
   set odps.sql.session.resources=myplus.py;
   select transform (testdata.c1, testdata.c2) 
   using 'python myplus.py' 
   as (result1,result2) 
   from testdata;

   Le résultat suivant est renvoyé :

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

Exemple d'appel de scripts Java

1. Rédigez un script Java et exportez-le sous forme de fichier Sum.jar. Exemple de code Java :

   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. Ajoutez le fichier en tant que ressource dans MaxCompute.

   add jar ./Sum.jar -f;

3. Exécutez l'instruction SELECT TRANSFORM pour appeler ce fichier.

   -- Create a test table. 
   create table testdata(c1 bigint,c2 bigint); 
   -- Insert test data into the test table. 
   insert into table testdata values (1,4),(2,5),(3,6); 
   -- Execute the SELECT TRANSFORM statement. 
   select transform(testdata.c1, testdata.c2) using 'java -cp Sum.jar com.aliyun.odps.test.Sum' resources 'Sum.jar' as cnt from testdata;
   -- The preceding statements are equivalent to the following statements: 
   set odps.sql.session.resources=Sum.jar; 
   select transform(testdata.c1, testdata.c2) using 'java -cp Sum.jar com.aliyun.odps.test.Sum' as cnt from testdata;

   Le résultat suivant est renvoyé :

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

Exemple d'appel de scripts en série

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;