MaxCompute:Tabela externa JSON

Sintaxe

Se um arquivo JSON tiver menos colunas que a definição da tabela externa, o MaxCompute preenche as colunas ausentes com NULL. Se o arquivo tiver mais colunas, o MaxCompute descarta as colunas excedentes.

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>',...)];

Parâmetros comuns

Para mais informações, consulte Parâmetros de sintaxe comuns.

Parâmetros exclusivos

Lista de permissões e lista de bloqueios

As tabelas externas do OSS do MaxCompute suportam filtragem por lista de permissões e lista de bloqueios. Ao definir parâmetros de lista de permissões e lista de bloqueios em tblproperties, você filtra quais arquivos ler de um diretório. Para detalhes, consulte Lista de permissões e lista de bloqueios.

Parâmetros

• Parâmetro de nível de tabela: odps.text.option.bad.row.skipping

  • rigid: Força o salto de linhas. Configurações de nível de sessão ou de projeto não podem substituir esta definição.

  • flexible: Habilita o salto de linhas. Esta configuração é flexível, permitindo que configurações de nível de sessão ou de projeto a substituam.

• Parâmetros de nível de session/project

  • O parâmetro odps.sql.unstructured.text.bad.row.skipping pode substituir um parâmetro de nível de tabela flexible, mas não um parâmetro rigid.

    • on: Habilita o recurso. Se o recurso não estiver configurado para a tabela, ele será habilitado por padrão.

    • off: Desabilita o recurso. Se a tabela estiver configurada como flexible, o recurso será desabilitado. Caso contrário, a configuração do parâmetro da tabela será usada.

    • <null> or invalid input: A configuração de nível de tabela é usada.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num: Especifique o número de resultados de erro a serem impressos no stdout no Logview.

    • O valor máximo é 1.000.

    • Se o valor for <=0, este recurso será desabilitado.

    • Se o valor for inválido, este recurso será desabilitado.

• Interação entre parâmetros de nível de sessão e propriedades de tabela

  tbl property	session flag	result
  rigid	on	On, Forced on
  	off
  	<null>, an invalid value, or the parameter is not configured
  flexible	on	On
  	off	Off, Disabled by session
  	<null>, an invalid value, or the parameter is not configured	On
  Not configured	on	On, Enabled by session
  	off	Off
  	<null>, an invalid value, or the parameter is not configured

Exemplos

1. Prepare os dados

   Faça upload dos dados de teste json_bad_row_skipping.json, que contêm alguns dados incorretos, para o diretório oss-mc-test/badrow/ no OSS.

2. Crie uma tabela externa JSON

   O comportamento varia conforme a propriedade de nível de tabela e a flag de nível de sessão. Os três casos a seguir são possíveis:

   • Parâmetro de tabela: odps.text.option.bad.row.skipping = flexible/rigid/<unspecified>

   • Flag de sessão: odps.sql.unstructured.text.bad.row.skipping = on/off/<not set>

   Sem propriedade de nível de tabela

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

   Salto flexível

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

   Salto rígido

   -- 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. Verifique os resultados da consulta

   Sem propriedade de nível de tabela

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

   Se o salto de erros for desabilitado no nível de sessão (SET odps.sql.unstructured.text.bad.row.skipping=off), a consulta falhará com o seguinte erro: FAILED: ODPS-0123131:User defined function exception

   Salto flexível

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

   Se o salto de erros for desabilitado no nível de sessão (SET odps.sql.unstructured.text.bad.row.skipping=off), a consulta falhará com o seguinte erro: FAILED: ODPS-0123131:User defined function exception

   Salto rígido

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

   O seguinte resultado é retornado:

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

Pré-requisitos

1. Você já criou um projeto MaxCompute.

2. Você preparou um bucket e um diretório no OSS. Para mais informações, consulte Criar um bucket e Gerencie diretórios.

   Certifique-se de que seu bucket esteja na mesma região do seu projeto MaxCompute.

3. Conceda permissões.

   1. Você tem permissão para acessar o OSS. É possível acessar uma tabela externa do OSS usando uma conta Alibaba Cloud, um usuário RAM ou uma função RAM. Para mais informações sobre como conceder permissões, consulte Autorização no modo STS para OSS.

   2. Você possui a permissão CreateTable no projeto MaxCompute. Para mais informações sobre permissões relacionadas a tabelas, consulte Permissões do MaxCompute.

Exemplo 1: Crie, grave e consulte uma tabela JSON

Este exemplo mostra como criar uma tabela externa JSON usando o analisador de dados open source integrado, gravar dados no OSS e consultar os dados.

1. Prepare os dados.

   Faça login no console do OSS e faça upload do arquivo de dados de teste json2025.txt para o diretório external-table-test/json/dt=20250521/ em um bucket do OSS. Para mais informações, consulte Fazer upload de arquivos para o OSS.

2. Crie uma tabela externa 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. Se sua tabela externa do OSS for particionada, importe também os dados das partições. Para mais informações, consulte Tabela externa do OSS.

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

4. Leia dados da tabela externa JSON.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   O seguinte resultado é retornado:

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

5. Grave dados na tabela externa JSON.

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

6. Visualize os dados gravados.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   O seguinte resultado é retornado:

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

Exemplo 2: Ler campos JSON aninhados

Preparar dados

Crie o arquivo JSON events.json:

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

Faça login no console do OSS e faça upload dos dados de teste para o diretório external-table-test/json_struct/ em um bucket do OSS. Para mais informações, consulte Fazer upload de arquivos para o OSS.

Método 1: Criar uma tabela externa TEXTFILE e usar a função get_json_object para ler valores de campo

1. Crie uma tabela externa TEXTFILE que contenha apenas uma única coluna do tipo 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;      

   O seguinte resultado é retornado:

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

2. Use a função get_json_object para ler os campos a e id:

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

   O seguinte resultado é retornado:

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

3. Leia os campos aninhados x, y e 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;          

   O seguinte resultado é retornado:

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

Método 2: Criar uma tabela externa JSON e usar o tipo STRUCT para receber os dados

1. Crie uma tabela externa formatada em JSON e use o tipo STRUCT para receber campos aninhados:

   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. Consulte os dados na tabela diretamente:

   SELECT * FROM extable_json_test02;

   O seguinte resultado é retornado:

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

3. Você também pode usar as funções get_json_object e TO_JSON para ler os campos x e 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;         

   O seguinte resultado é retornado:

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

Exemplo 3: Personalizar nomes de arquivos de saída

1. Defina o prefixo personalizado para arquivos gravados no OSS como test06_. O DDL é o seguinte:

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

   Após a gravação dos dados, os arquivos gerados no OSS terão o prefixo personalizado test06_, por exemplo, test06_202509101.

2. Para personalizar o sufixo dos arquivos gravados no OSS como _beijing, use o seguinte DDL:

   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. Para gerar automaticamente uma extensão de arquivo para os arquivos de saída, use o seguinte DDL:

   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. Para personalizar a extensão do arquivo como jsonl para arquivos gravados no OSS, use o seguinte DDL:

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

   O nome do arquivo gerado é 20250905072538695g3mlopvxicr4_M1_1_0_0-0_TableSink1.jsonl, com a extensão de arquivo personalizada .jsonl.

5. Para arquivos gravados no OSS, defina o prefixo como mc_, o sufixo como _beijing e a extensão do arquivo como jsonl. O DDL é o seguinte:

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

   O nome do arquivo gerado é mc_20250905073013526gra1l214x6t6_M1_1_0_0-0_TableSink1_beijing.jsonl, onde 20250905073013526 é o carimbo de data/hora gerado pelo sistema, e a parte intermediária é o identificador da tarefa.

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

• Mensagem de erro

  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)

• Causa

  Esse erro geralmente ocorre com dados JSON inválidos, como em um arquivo JSON Lines (JSONL). O erro é acionado se um registro contiver um caractere de nova linha sem escape, violando a regra de um registro por linha.

• Solução

  Faça o escape dos caracteres de nova linha no arquivo JSON e, em seguida, leia os dados.

• Solução de problemas

  Ao habilitar o BadRowSkipping, detalhes sobre as linhas ignoradas são impressos no log stdout no Logview. Isso ajuda a diagnosticar erros de dados. Use as seguintes configurações para habilitar este recurso:

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

Erro de parâmetro de lista de permissões ao ler tabela externa JSON: Start token not found where expected

• Mensagem de erro

  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 

• Causa

  Nenhum arquivo correspondeu aos parâmetros da lista de permissões.

• Solução

  Verifique se o padrão regex no parâmetro da lista de permissões está correto.