MaxCompute:JSON external table

Syntaxe

Si un fichier JSON contient moins de colonnes que la définition de la table externe, MaxCompute remplit les colonnes manquantes par NULL. Si le fichier contient plus de colonnes, MaxCompute ignore les colonnes supplémentaires.

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS textfile
LOCATION '<oss_location>';

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
STORED AS textfile
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

Paramètres courants

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

Paramètres exclusifs

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 depuis un répertoire. Pour plus de détails, consultez la section Liste d'autorisation et liste de blocage.

Paramètres

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

  • rigid : Force l'ignorance. Ce paramètre ne peut pas être remplacé par les configurations au niveau de la session ou du projet.

  • flexible : Active l'ignorance. Ce paramètre est flexible, ce qui signifie qu'il peut être remplacé par les 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 au niveau de la table défini sur flexible, mais pas un paramètre défini sur 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 à afficher dans 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	Activé, Forcé
  	off
  	<null>, une valeur invalide ou le paramètre n'est pas configuré
  flexible	on	Activé
  	off	Désactivé, Désactivé par la session
  	<null>, une valeur invalide ou le paramètre n'est pas configuré	Activé
  Non configuré	on	Activé, Activé par la session
  	off	Désactivé
  	<null>, une valeur invalide ou le paramètre n'est pas configuré

Exemples

1. Préparer les données

   Téléchargez les données de test json_bad_row_skipping.json, qui contiennent certaines données incorrectes, vers le répertoire oss-mc-test/badrow/ sur OSS.

2. Créer une table externe JSON

   Le comportement varie selon la propriété au niveau de la table et l'indicateur au niveau de la session. Les trois cas suivants sont possibles :

   • Paramètre de table : odps.text.option.bad.row.skipping = flexible/rigid/<unspecified>

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

   Aucune propriété au niveau de la table

   -- No table-level property is set. Errors are handled based on the session-level flag.
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_flag
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>';

   Ignorance flexible

   -- The table-level property skips error rows, but the session-level flag can disable this behavior.
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_flexible
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- Flexible mode, can be disabled at the session level.
   );

   Ignorance rigide

   -- The table-level property forces error skipping. This behavior cannot be disabled at the session level.
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_rigid
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- Forced on.
   );

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

   Aucune propriété au niveau de la table

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flag;

   Si l'ignorance des erreurs est désactivée au niveau de la session (SET odps.sql.unstructured.text.bad.row.skipping=off), la requête échoue avec l'erreur suivante : FAILED: ODPS-0123131:User defined function exception

   Ignorance flexible

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flexible;

   Si l'ignorance des erreurs est désactivée au niveau de la session (SET odps.sql.unstructured.text.bad.row.skipping=off), la requête échoue avec l'erreur suivante : FAILED: ODPS-0123131:User defined function exception

   Ignorance rigide

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_rigid;

   Le résultat suivant est renvoyé :

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 2          | 
   | 15         | 16         | 
   +------------+------------+

Prérequis

1. Vous avez créé un projet MaxCompute.

2. Vous avez préparé un bucket et un répertoire OSS. Pour plus d'informations, consultez les rubriques Créer un bucket et Gérer les répertoires.

   Assurez-vous que votre bucket se trouve dans la même région que votre projet MaxCompute.

3. Accordez les autorisations nécessaires.

   1. Vous disposez des autorisations d'accès à OSS. Vous pouvez accéder à une table externe OSS en utilisant un compte Alibaba Cloud, un utilisateur RAM ou un rôle RAM. Pour savoir comment accorder ces autorisations, consultez la section Autorisation STS pour OSS.

   2. Vous possédez l'autorisation CreateTable dans le projet MaxCompute. Pour plus de détails sur les autorisations liées aux tables, reportez-vous à la rubrique Autorisations MaxCompute.

Exemple 1 : Créer, écrire et interroger une table JSON

Cet exemple illustre la création d'une table externe JSON à l'aide du parseur de données open source intégré, l'écriture des données dans OSS, puis leur interrogation.

1. Préparez les données.

   Connectez-vous à la console OSS et téléchargez le fichier de données de test json2025.txt dans le répertoire external-table-test/json/dt=20250521/ d'un bucket OSS. Pour plus d'informations, consultez la rubrique Télécharger des fichiers vers OSS.

2. Créez une table externe JSON.

   CREATE EXTERNAL TABLE mc_oss_extable_name_json
   (
     action STRING,
     time STRING
   )
   PARTITIONED BY (dt STRING)
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json/';

3. Si votre table externe OSS est partitionnée, vous devez également importer les données de partition. Pour plus d'informations, consultez la rubrique Table externe OSS.

   -- Add partitions.
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. Lisez les données de la table externe JSON.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   Le résultat suivant est renvoyé :

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

5. Écrivez des données dans la table externe JSON.

   INSERT INTO mc_oss_extable_name_json PARTITION (dt='20250526') VALUES ('test','1627273823');

6. Consultez les données écrites.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   Le résultat suivant est renvoyé :

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | test       | 1627273823 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

Exemple 2 : Lire des champs JSON imbriqués

Préparation des données

Créez le fichier JSON events.json :

{"a":{"x":1, "y":2}, "id":"123"}
{"a":{"x":3, "y":4}, "id":"345"}

Connectez-vous à la console OSS et téléchargez les données de test dans le répertoire external-table-test/json_struct/ d'un bucket OSS. Pour plus d'informations, consultez la rubrique Télécharger des fichiers vers OSS.

Méthode 1 : Créez une table externe TEXTFILE et utilisez la fonction get_json_object pour lire les valeurs des champs

1. Créez une table externe TEXTFILE contenant une seule colonne de type string :

   CREATE EXTERNAL TABLE extable_json_test01 (
     col STRING
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';  
   
   SELECT * FROM extable_json_test01;      

   Le résultat suivant est renvoyé :

   +------------------------------------+
   |               col                  |
   +------------------------------------+
   | {"a": {"x": 1, "y": 2},"id":"123"} |
   | {"a": {"x": 3, "y": 4},"id":"345"} |
   +------------------------------------+

2. Utilisez la fonction get_json_object pour lire les champs a et id :

   SELECT 
       get_json_object(col, '$.a') AS a,
       get_json_object(col, '$.id') AS id
   FROM extable_json_test01;         

   Le résultat suivant est renvoyé :

   +-------------------+-----+
   |        a          | id  |
   +-------------------+-----+
   | {"x":1,"y":2}     | 123 |
   | {"x":3,"y":4}     | 345 |
   +-------------------+-----+

3. Lisez les champs imbriqués x, y et id :

   SELECT 
       get_json_object(get_json_object(col,'$.a'),'$.x') AS x,
       get_json_object(get_json_object(col,'$.a'),'$.y') AS y,
       get_json_object(col,'$.id') AS id
   FROM extable_json_test01;          

   Le résultat suivant est renvoyé :

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Méthode 2 : Créez une table externe JSON et utilisez le type STRUCT pour recevoir les données

1. Créez une table externe au format JSON et utilisez le type STRUCT pour接收 les champs imbriqués :

   CREATE EXTERNAL TABLE extable_json_test02
   (
     a STRUCT<x: BIGINT, y: BIGINT>,
     id STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';          

2. Interrogez directement les données de la table :

   SELECT * FROM extable_json_test02;

   Le résultat suivant est renvoyé :

   +----------+-----+
   |    a     | id  |
   +----------+-----+
   | {x:1, y:2}|123 |
   | {x:3, y:4}|345 |
   +----------+-----+

3. Vous pouvez également utiliser les fonctions get_json_object et TO_JSON pour lire les champs x et y :

   SELECT 
       get_json_object(TO_JSON(a), '$.x') AS x,
       get_json_object(TO_JSON(a), '$.y') AS y,
       id
   FROM extable_json_test02;         

   Le résultat suivant est renvoyé :

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Exemple 3 : Personnaliser les noms de fichiers de sortie

1. Définissez le préfixe personnalisé pour les fichiers écrits dans OSS sur test06_. La DDL est la suivante :

   CREATE EXTERNAL TABLE  <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Add a custom prefix.
       'odps.external.data.output.prefix'='test06_') 
   ;
   
   -- Write data to the external table.
   INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Après l'écriture des données, les fichiers générés dans OSS portent le préfixe personnalisé test06_, par exemple test06_202509101.

2. Pour personnaliser le suffixe des fichiers écrits dans OSS avec _beijing, la DDL est la suivante :

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Add a custom suffix.
       'odps.external.data.output.suffix'='_beijing') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

3. Pour générer automatiquement une extension de fichier pour les fichiers de sortie, utilisez la DDL suivante :

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Automatically generate a file extension.
       'odps.external.data.enable.extension'='true') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

4. Pour personnaliser l'extension de fichier sur jsonl pour les fichiers écrits dans OSS, la DDL est la suivante :

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Add a custom file extension.
      'odps.external.data.output.explicit.extension'='jsonl') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Le nom de fichier généré est 20250905072538695g3mlopvxicr4_M1_1_0_0-0_TableSink1.jsonl, avec l'extension de fichier personnalisée .jsonl.

5. Pour les fichiers écrits dans OSS, définissez le préfixe sur mc_, le suffixe sur _beijing et l'extension de fichier sur jsonl. La DDL est la suivante :

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
       -- Add a custom prefix.
       'odps.external.data.output.prefix'='mc_', 
       -- Add a custom suffix.
       'odps.external.data.output.suffix'='_beijing', 
       -- Add a custom file extension.
       'odps.external.data.output.explicit.extension'='jsonl') 
   ;  
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Le nom de fichier généré est mc_20250905073013526gra1l214x6t6_M1_1_0_0-0_TableSink1_beijing.jsonl, où 20250905073013526 est l'horodatage généré par le système et la partie centrale correspond à l'identifiant de tâche.

Erreur : Unexpected end-of-input: expected close marker for OBJECT

• Message d'erreur

  ODPS-0123131:User defined function exception - Traceback:
  com.aliyun.odps.serde.SerDeException: org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (from [Source: java.io.ByteArrayInputStream@5a021cb9; line: 1, column: 0])
   at [Source: java.io.ByteArrayInputStream@5a021cb9; line: 1, column: 3]
  	at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:122)
  	at com.aliyun.odps.udf.HiveReaderHandler.next(HiveReaderHandler.java:152)
  	at com.aliyun.odps.udf.HiveReaderHandler5c9b68c118d14fb2b392e8d916ddea8c.next(Unknown Source)

• Cause

  Cette erreur survient généralement lorsque les données JSON sont invalides, comme dans un fichier JSON Lines (JSONL). Elle est déclenchée si un enregistrement contient un caractère de nouvelle ligne non échappé, ce qui viole la règle « un enregistrement par ligne ».

• Solution

  Échappez les caractères de nouvelle ligne dans le fichier JSON, puis lisez les données.

• Dépannage

  Lorsque vous activez BadRowSkipping, les détails concernant les lignes ignorées sont imprimés dans le journal stdout de Logview. Cela vous aide à diagnostiquer les erreurs de données. Utilisez les paramètres suivants pour activer cette fonctionnalité :

  -- Set the BadRowSkipping parameter to on at the session level to skip the error data.
  SET odps.sql.unstructured.text.bad.row.skipping=on;
  
  -- Specify the number of error records that can be printed to stdout in Logview.
  SET odps.sql.unstructured.text.bad.row.skipping.debug.num=<number>;

Erreur de paramètre de liste d'autorisation lors de la lecture d'une table externe JSON : Start token not found where expected

• Message d'erreur

  FAILED: ODPS-0123131:User defined function exception - Traceback:
  com.aliyun.odps.serde.SerDeException: org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start token not found where expected
          at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:122)
          at com.aliyun.odps.udf.HiveReaderHandler.next(HiveReaderHandler.java:152)
  Caused by: org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start token not found where expected
          at org.apache.hive.hcatalog.data.JsonSerDe.deserialize(JsonSerDe.java:190)
          at com.aliyun.odps.hive.wrapper.json.JsonEnhancedSerde.deserialize(JsonEnhancedSerde.java:50)
          at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:120)
          ... 1 more
  Caused by: java.io.IOException: Start token not found where expected
          at org.apache.hive.hcatalog.data.JsonSerDe.deserialize(JsonSerDe.java:176)
          ... 3 more
   | fatalInstance: Odps/yyy_yueyi_dev_20251226063242326gd2yy12fi2h3_SQL_0_1_0_job_0/M1#0_0 

• Cause

  Aucun fichier ne correspond lors de la mise en correspondance par les paramètres de la liste d'autorisation.

• Solution

  Vérifiez que le modèle d'expression régulière dans le paramètre de la liste d'autorisation est correct.