MaxCompute:Tables externes CSV et TSV

Types de données pris en charge

Pour plus d'informations sur les types de données MaxCompute, consultez Data Type Version 1.0 et Data Type Version 2.0.

Pour plus d'informations sur SmartParse, reportez-vous à la section Compatibilité flexible des types de Smart Parse.

Formats de compression pris en charge

Lorsque vous lisez ou écrivez des fichiers OSS compressés, vous devez inclure l'attribut with serdeproperties dans l'instruction CREATE TABLE. Pour plus d'informations, consultez la section Paramètres de l'attribut with serdeproperties.

Évolution du schéma prise en charge

Syntaxe

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)] 
STORED BY 'com.aliyun.odps.CsvStorageHandler'  
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)] 
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)] 
STORED BY 'com.aliyun.odps.TsvStorageHandler'  
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)] 
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
[WITH serdeproperties (
  ['<property_name>'='<property_value>',...]
)]
STORED AS TEXTFILE 
LOCATION '<oss_location>' 
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

Paramètres courants

Pour plus d'informations sur les paramètres courants, consultez la section Paramètres de syntaxe de base.

Paramètres spécifiques au format

Liste d'autorisation et liste de blocage

Les tables externes OSS de MaxCompute prennent en charge le filtrage par liste d'autorisation et liste de blocage. En définissant les paramètres de liste d'autorisation et de liste de blocage dans tblproperties, vous pouvez filtrer les fichiers à lire dans un répertoire. Pour plus de détails, consultez Liste d'autorisation et liste de blocage.

Paramètres

• Paramètre au niveau de la table : odps.text.option.bad.row.skipping

  • rigid : Force le saut des lignes. Ce paramètre ne peut pas être remplacé par des configurations au niveau de la session ou du projet.

  • flexible : Active le saut des lignes. Ce paramètre est flexible, ce qui permet de le remplacer par des configurations au niveau de la session ou du projet.

• Paramètres au niveau session/project

  • Le paramètre odps.sql.unstructured.text.bad.row.skipping peut remplacer un paramètre de table flexible, mais pas un paramètre rigid.

    • on : Active la fonctionnalité. Si la fonctionnalité n'est pas configurée pour la table, elle est activée par défaut.

    • off : Désactive la fonctionnalité. Si la table est configurée en mode flexible, la fonctionnalité est désactivée. Sinon, le paramètre de la table est utilisé.

    • <null> or invalid input : La configuration au niveau de la table est utilisée.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num : Spécifie le nombre de résultats d'erreur à imprimer sur stdout dans Logview.

    • La valeur maximale est 1000.

    • Si la valeur est <=0, cette fonctionnalité est désactivée.

    • Si la valeur est invalide, cette fonctionnalité est désactivée.

• Interaction entre les paramètres au niveau de la session et les propriétés de la table

  Propriété tbl	Indicateur de session	Résultat
  rigid	on	On, Forcé à on
  	off
  	<null>, une valeur invalide ou le paramètre n'est pas configuré
  flexible	on	On
  	off	Off, Désactivé par la session
  	<null>, une valeur invalide ou le paramètre n'est pas configuré	On
  Non configuré	on	On, Activé par la session
  	off	Off
  	<null>, une valeur invalide ou le paramètre n'est pas configuré

Exemples

1. Préparez les données

   Téléchargez le fichier de données de test csv_bad_row_skipping.csv, qui contient des lignes incorrectes, vers un répertoire dans OSS, tel que oss-mc-test/badrow/.

2. Créez les tables externes CSV

   Les exemples suivants présentent trois scénarios basés sur différentes combinaisons de paramètres au niveau de la table et de la session.

   • Paramètre de table : odps.text.option.bad.row.skipping = flexible | rigid | <not set>

   • Indicateur de session : odps.sql.unstructured.text.bad.row.skipping = on | off | <not set>

   Paramètre non défini

   -- No table-level parameter is set. Queries will fail on bad rows unless overridden by the session-level flag.
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flag
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>';

   Saut flexible

   -- The table is configured to skip bad rows, but this can be disabled by the session-level flag.
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flexible
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- Enables flexible skipping, which can be disabled at the session level.
   );

   Saut rigide

   -- The table is configured to forcibly skip bad rows. This cannot be disabled at the session level.
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_rigid
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- Forces skipping on.
   );

3. Vérifiez les résultats de la requête

   Paramètre non défini

   -- The following command enables skipping, but it is immediately overridden by the next command in this example.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command disables skipping and is the active setting for the SELECT query below, causing it to fail.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- You can use this command to print details of skipped rows when skipping is enabled. It has no effect here because the query fails.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   La requête échoue avec l'erreur suivante : FAILED: ODPS-0123131:User defined function exception

   Saut flexible

   -- The following command enables skipping, but it is immediately overridden by the next command in this example.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command disables skipping, overriding the table's 'flexible' setting. It is the active setting for the SELECT query below, causing it to fail.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print details of up to 10 bad rows at the session level. The maximum is 1,000. A value of 0 or less disables printing.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   La requête échoue avec l'erreur suivante : FAILED: ODPS-0123131:User defined function exception

   Saut rigide

   -- This command is redundant because the 'rigid' setting already enforces skipping.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command attempts to disable skipping, but it is ignored because the 'rigid' table setting cannot be overridden.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- This command prints details for up to 10 bad rows that are skipped by the 'rigid' setting.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_rigid;

   Le résultat suivant est retourné :

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 26         | 
   | 5          | 37         | 
   +------------+------------+

L'analyseur CSV/TSV intégré (OpenCSVSerde)

L'analyseur CSV/TSV intégré (OpenCSVSerde) exige que chaque ligne de données d'un fichier CSV soit séparée par \r\n ou des caractères similaires, et que les colonnes ne contiennent pas \r\n. La logique de fractionnement parallèle et d'intégrité des données est la suivante :

• Tout d'abord, le fichier est fractionné selon la taille de split, ce qui peut couper certaines lignes en plein milieu.

• Lorsque les workers suivants consomment les splits, tous les splits sauf le premier ignorent activement la première ligne partielle ou complète.

• Chaque worker doit également consommer activement la dernière ligne partielle ou complète, même si celle-ci tombe dans la plage du split suivant.

Cet analyseur prend en charge le fractionnement parallèle, mais pas l'échappement des guillemets.

L'analyseur CSV open source (CsvStorageHandler / TsvStorageHandler)

L'analyseur CSV open source (CsvStorageHandler / TsvStorageHandler) prend en compte l'échappement des guillemets lors de la lecture des fichiers CSV/TSV et gère \r\n. Il peut traiter des scénarios où des caractères spéciaux, tels que des guillemets imbriqués ou \r\n, apparaissent entre guillemets.

Par exemple, dans les données "a\ra","b\nb","cc""cc", les caractères \r\n et les guillemets doubles peuvent être correctement analysés et restitués, permettant ainsi des valeurs multilignes. Toutefois, comme les positions réelles des sauts de ligne ne peuvent être déterminées que par l'analyse des données, le fichier ne peut pas être simplement fractionné par \r\n. Par conséquent, la consommation parallèle d'un seul fichier n'est pas prise en charge.

Si vous confirmez que les colonnes de données ne contiennent pas \r\n et que \r\n sert uniquement de délimiteur de ligne, vous pouvez activer le fractionnement parallèle en définissant odps.sql.unstructured.data.single.file.split.enabled. Dans ce cas, un fichier volumineux peut être divisé en plusieurs splits selon la taille de split, et l'Extracteur de texte intégré s'aligne automatiquement sur les limites de saut de ligne pour garantir l'intégrité des données.

Prérequis

• Un projet MaxCompute. Consultez la rubrique Créer un projet MaxCompute.

• Un bucket OSS situé dans la même région que votre projet MaxCompute. Reportez-vous aux rubriques Créer un bucket et Gérer les dossiers. MaxCompute peut créer automatiquement les dossiers OSS ; il est donc inutile de les créer manuellement avant d'exécuter des requêtes SQL.

• Des permissions d'accès à OSS accordées via un compte Alibaba Cloud, un utilisateur Resource Access Management (RAM) ou un rôle RAM. Consultez la rubrique Autoriser l'accès en mode STS pour OSS.

• La permission CreateTable sur votre projet MaxCompute. Voir Permissions MaxCompute.

Créer une table externe OSS avec l'analyseur de texte intégré

Créer une table externe avec une ligne d'en-tête

Créez un répertoire Demo11 dans le bucket oss-mc-test des données d'exemple, puis exécutez les instructions suivantes :

--Create the external table.
CREATE EXTERNAL TABLE mf_oss_wtt
(
  id BIGINT,
  name STRING,
  tran_amt DOUBLE
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH serdeproperties (
  'odps.text.option.header.lines.count' = '1',
  'odps.sql.text.option.flush.header' = 'true'
)
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo11/';

--Insert data.
INSERT OVERWRITE TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);

--Query data.
--When you create the table, you can define all columns as STRING. Otherwise, an error occurs when the header is read.
--Alternatively, add the 'odps.text.option.header.lines.count' = '1' parameter to the table definition to skip the header.
SELECT * FROM mf_oss_wtt;

Cet exemple utilise le rôle RAM aliyunodpsdefaultrole. Si vous employez un autre rôle RAM, remplacez aliyunodpsdefaultrole par le nom du rôle souhaité et accordez-lui les permissions nécessaires pour accéder à OSS.

La commande renvoie le résultat suivant :

+----------+--------+------------+
| id       | name   | tran_amt   |
+----------+--------+------------+
| 1        | val1   | 1.1        |
| 2        | value2 | 1.3        |
+----------+--------+------------+

Créer une table externe avec des colonnes non concordantes

1. Créez un répertoire demo dans le bucket oss-mc-test des données d'exemple, puis chargez le fichier test.csv. Le contenu du fichier test.csv est le suivant :

   1,kyle1,this is desc1
   2,kyle2,this is desc2,this is two
   3,kyle3,this is desc3,this is three, I have 4 columns

2. Créez les tables externes.

   1. Définissez le mode de gestion des lignes ayant un nombre de colonnes incohérent sur TRUNCATE.

      -- Drop the table.
      DROP TABLE test_mismatch;
      -- Create the external table.
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch
      (
        id string,
        name string,
        dect string,
        col4 string
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'truncate')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   2. Spécifiez le mode de gestion des lignes ayant un nombre de colonnes incohérent sur IGNORE.

      -- Drop the table.
      DROP TABLE test_mismatch01;
      -- Create the external table.
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch01
      (
        id STRING,
        name STRING,
        dect STRING,
        col4 STRING
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'ignore')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   3. Interrogez les données contenues dans ces tables.

      • Interrogez la table test_mismatch.

        SELECT * FROM test_mismatch;
        
        --Returned result
        +----+-------+---------------+---------------+
        | id | name  | dect          | col4          |
        +----+-------+---------------+---------------+
        | 1  | kyle1 | this is desc1 | NULL          |
        | 2  | kyle2 | this is desc2 | this is two   |
        | 3  | kyle3 | this is desc3 | this is three |
        +----+-------+---------------+---------------+

      • Interrogez la table test_mismatch01.

        SELECT * FROM test_mismatch01;
        
        --Returned result
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

Créer une table externe avec l'analyseur open source

Cet exemple montre comment utiliser l'analyseur open source intégré pour créer une table externe OSS permettant de lire un fichier séparé par des virgules, tout en ignorant les lignes d'en-tête et de pied de page.

1. Créez un répertoire demo-test dans le bucket oss-mc-test des données d'exemple, puis chargez le fichier test.csv.

   Le fichier de test contient les données suivantes :

   1,1,51,1,46.81006,-92.08174,9/14/2014 0:00,S
   1,2,13,1,46.81006,-92.08174,9/14/2014 0:00,NE
   1,3,48,1,46.81006,-92.08174,9/14/2014 0:00,NE
   1,4,30,1,46.81006,-92.08174,9/14/2014 0:00,W
   1,5,47,1,46.81006,-92.08174,9/14/2014 0:00,S
   1,6,9,1,46.81006,-92.08174,9/15/2014 0:00,S
   1,7,53,1,46.81006,-92.08174,9/15/2014 0:00,N
   1,8,63,1,46.81006,-92.08174,9/15/2014 0:00,SW
   1,9,4,1,46.81006,-92.08174,9/15/2014 0:00,NE
   1,10,31,1,46.81006,-92.08174,9/15/2014 0:00,N

2. Créez la table externe, définissez la virgule comme séparateur et configurez les paramètres pour ignorer les lignes d'en-tête et de pied de page.

   CREATE EXTERNAL TABLE ext_csv_test08
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
   WITH serdeproperties (
   "separatorChar" = ","
   )
   stored AS textfile
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/***/'
   -- Set parameters to ignore the header and footer rows.
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. Lisez les données depuis la table externe.

   SELECT * FROM ext_csv_test08;
   
   -- The result includes 8 rows of data because the header and footer rows are ignored.
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | W          |
   | 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          |
   | 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | S          |
   | 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | N          |
   | 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | SW         |
   | 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

Créer une table externe CSV avec des types temporels personnalisés

Pour plus de détails sur les formats d'analyse et de sortie des types temporels personnalisés en CSV, consultez la rubrique Compatibilité flexible des types avec Smart Parse.

1. Créez une table externe CSV utilisant divers types de données temporelles, tels que DATE, DATETIME, TIMESTAMP et TIMESTAMP_NTZ.

   CREATE EXTERNAL TABLE test_csv
   (
     col_date DATE,
     col_datetime DATETIME,
     col_timestamp TIMESTAMP,
     col_timestamp_ntz TIMESTAMP_NTZ
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/'
   TBLPROPERTIES (
     'odps.text.option.date.io.format' = 'MM/dd/yyyy',
     'odps.text.option.datetime.io.format' = 'yyyy-MM-dd-HH-mm-ss x',
     'odps.text.option.timestamp.io.format' = 'yyyy-MM-dd HH-mm-ss VV',
     'odps.text.option.timestamp_ntz.io.format' = 'yyyy-MM-dd HH:mm:ss.SS'
   );
   
   INSERT OVERWRITE test_csv 
     VALUES(DATE'2025-02-21', DATETIME'2025-02-21 08:30:00', TIMESTAMP'2025-02-21 12:30:00', TIMESTAMP_NTZ'2025-02-21 16:30:00.123456789');

2. Après l'insertion des données, le contenu du fichier CSV se présente comme suit :

   02/21/2025,2025-02-21-08-30-00 +08,2025-02-21 12-30-00 Asia/Shanghai,2025-02-21 16:30:00.12

3. Interrogez à nouveau les données pour visualiser le résultat.

   SELECT * FROM test_csv;

   La commande renvoie le résultat suivant :

   +------------+---------------------+---------------------+------------------------+
   | col_date   | col_datetime        | col_timestamp       | col_timestamp_ntz      |
   +------------+---------------------+---------------------+------------------------+
   | 2025-02-21 | 2025-02-21 08:30:00 | 2025-02-21 12:30:00 | 2025-02-21 16:30:00.12 |
   +------------+---------------------+---------------------+------------------------+

Erreur de non-concordance du nombre de colonnes

• Symptôme

  Cette erreur survient lorsque le nombre de colonnes d'une ligne dans un fichier CSV ou TSV ne correspond pas à celui défini dans le DDL de la table externe. MaxCompute signale alors une erreur similaire à FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx.

• Résolution

  Vous pouvez contrôler le traitement de cette non-concordance par MaxCompute en définissant le paramètre odps.sql.text.schema.mismatch.mode au niveau de la session :

  • SET odps.sql.text.schema.mismatch.mode=error : fait échouer la requête en cas de non-concordance du nombre de colonnes. Il s'agit du comportement par défaut.

  • SET odps.sql.text.schema.mismatch.mode=truncate : si une ligne comporte plus de colonnes que prévu dans le DDL de la table externe, les colonnes excédentaires sont ignorées. À l'inverse, si elle en comporte moins, les colonnes manquantes reçoivent la valeur NULL.