MaxCompute:Tabelas externas CSV e TSV

Tipos de dados suportados

Para obter mais informações sobre os tipos de dados do MaxCompute, consulte Data Type Version 1.0 e Data Type Version 2.0.

Para obter mais informações sobre o SmartParse, consulte Flexible type compatibility of Smart Parse.

Formatos de compactação suportados

Ao ler ou gravar em arquivos compactados do OSS, inclua o atributo with serdeproperties na instrução CREATE TABLE. Para obter mais informações, consulte with serdeproperties attribute parameters.

Evolução de esquema suportada

Sintaxe

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

Parâmetros comuns

Para obter mais informações sobre parâmetros comuns, consulte Basic syntax parameters.

Parâmetros específicos de formato

Lista de permissões e lista de bloqueios

As tabelas externas do OSS no 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, filtre quais arquivos ler de um diretório. Para obter detalhes, consulte Whitelist and blacklist.

Parâmetros

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

  • rigid: Força a ignorância de linhas. Esta configuração não pode ser substituída por configurações de nível de sessão ou projeto.

  • flexible: Habilita a ignorância de linhas. Esta configuração é flexível, permitindo que seja substituída por configurações de nível de sessão ou projeto.

• 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: Especifica o número de resultados de erro a serem impressos no stdout no Logview.

    • O valor máximo é 1000.

    • 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 da tabela

  tbl property	session flag	resultado
  rigid	on	On, Forçado on
  	off
  	<null>, um valor inválido ou o parâmetro não está configurado
  flexible	on	On
  	off	Off, Desabilitado pela sessão
  	<null>, um valor inválido ou o parâmetro não está configurado	On
  Não configurado	on	On, Habilitado pela sessão
  	off	Off
  	<null>, um valor inválido ou o parâmetro não está configurado

Exemplos

1. Prepare os dados

   Faça upload do arquivo de dados de teste csv_bad_row_skipping.csv, que contém linhas inválidas, para um diretório no OSS, como oss-mc-test/badrow/.

2. Crie as tabelas externas CSV

   Os exemplos a seguir mostram três cenários baseados em diferentes combinações de parâmetros de nível de tabela e nível de sessão.

   • Parâmetro da tabela: odps.text.option.bad.row.skipping = flexible | rigid | <not set>

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

   Parâmetro não definido

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

   Ignorância flexível

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

   Ignorância rígida

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

   Parâmetro não definido

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

   A consulta falha com o seguinte erro: FAILED: ODPS-0123131:User defined function exception

   Ignorância flexível

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

   A consulta falha com o seguinte erro: FAILED: ODPS-0123131:User defined function exception

   Ignorância rígida

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

   O seguinte resultado é retornado:

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

O analisador CSV/TSV integrado (OpenCSVSerde)

O analisador CSV/TSV integrado (OpenCSVSerde) exige que cada linha de dados em um arquivo CSV seja separada por \r\n ou caracteres semelhantes, e as colunas não devem conter \r\n. A lógica de divisão paralela e integridade de dados é a seguinte:

• Primeiro, o arquivo é dividido pelo tamanho do split, e algumas linhas podem ser cortadas no meio.

• Quando workers subsequentes consomem splits, todos os splits, exceto o primeiro, ignoram ativamente a linha parcial ou completa inicial.

• Cada worker também deve consumir ativamente a linha parcial ou completa final, mesmo que ela caia dentro do intervalo do próximo split.

Este analisador suporta divisão paralela, mas não suporta escape de aspas.

O analisador CSV de código aberto (CsvStorageHandler / TsvStorageHandler)

O analisador CSV de código aberto (CsvStorageHandler / TsvStorageHandler) leva em consideração o escape de aspas ao ler arquivos CSV/TSV e suporta \r\n. Ele consegue lidar com cenários onde caracteres especiais, como aspas aninhadas ou \r\n, aparecem dentro de aspas.

Por exemplo, nos dados "a\ra","b\nb","cc""cc", o \r\n e as aspas duplas podem ser analisados e gerados corretamente, suportando valores entre linhas. No entanto, como as posições reais de quebra de linha só podem ser determinadas através da análise dos dados, o arquivo não pode ser simplesmente dividido por \r\n. Como resultado, o consumo paralelo de um único arquivo não é suportado.

Se você confirmar que as colunas de dados não contêm \r\n e que \r\n é usado apenas como delimitador de linha, habilite a divisão paralela definindo odps.sql.unstructured.data.single.file.split.enabled. Nesse caso, um arquivo grande pode ser dividido em vários splits pelo tamanho do split, e o Text Extractor integrado se alinha automaticamente aos limites de quebra de linha para garantir a integridade dos dados.

Pré-requisitos

• Um MaxCompute project is created.

• Um bucket e uma pasta do OSS disponíveis. Para obter mais informações, consulte Create a bucket e Manage folders.

  O MaxCompute suporta a criação automática de pastas no OSS. Se uma instrução SQL envolver tabelas externas e funções definidas pelo usuário (UDFs), use uma única instrução para ler e gravar nas tabelas e usar as UDFs. Você também pode criar pastas manualmente.

  O MaxCompute é implantado apenas em regiões específicas. Para evitar possíveis problemas com conexões de dados entre regiões, garanta que seu bucket do OSS esteja na mesma região do seu projeto MaxCompute.

• Autorização

  • Você deve ter permissões para acessar o OSS. Use uma conta Alibaba Cloud, um usuário Resource Access Management (RAM) ou uma função RAM para acessar tabelas externas do OSS. Para obter mais informações sobre autorização, consulte Authorize access in STS mode for OSS.

  • Você deve ter a permissão CreateTable no projeto MaxCompute. Para obter mais informações sobre permissões de tabela, consulte MaxCompute permissions.

Criar uma tabela externa do OSS com o analisador de texto integrado

Criar uma tabela externa com linha de cabeçalho

Crie um diretório Demo11 no bucket oss-mc-test do sample data e execute as seguintes instruções:

--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',
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
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;

Este exemplo usa a função RAM aliyunodpsdefaultrole. Se você usar uma função RAM diferente, substitua aliyunodpsdefaultrole pelo nome da sua função RAM de destino e conceda a ela as permissões necessárias para acessar o OSS.

O comando retorna o seguinte resultado:

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

Criar uma tabela externa com colunas incompatíveis

1. Crie um diretório demo no bucket oss-mc-test do sample data e faça upload do arquivo test.csv. O arquivo test.csv contém o seguinte conteúdo:

   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. Crie as tabelas externas.

   1. Defina o método de tratamento para linhas com contagens de colunas inconsistentes como 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',
        'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   2. Especifique o método de tratamento para linhas com contagens de colunas inconsistentes como 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. Consulte os dados nas tabelas.

      • Consulte a tabela 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 |
        +----+-------+---------------+---------------+

      • Consulte a tabela test_mismatch01.

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

Criar uma tabela externa com o analisador de código aberto

Este exemplo mostra como usar o analisador de código aberto integrado para criar uma tabela externa do OSS que lê um arquivo separado por vírgulas, ignorando as linhas de cabeçalho e rodapé.

1. Crie um diretório demo-test no bucket oss-mc-test do sample data e faça upload do arquivo test.csv.

   O arquivo de teste contém os seguintes dados:

   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. Crie a tabela externa, especifique a vírgula como separador e defina parâmetros para ignorar as linhas de cabeçalho e rodapé.

   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" = ",",
   'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   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. Leia dados da tabela externa.

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

Criar uma tabela externa CSV com tipos de tempo personalizados

Para obter detalhes sobre formatos de análise e saída para tipos de tempo personalizados em CSV, consulte Flexible type compatibility with Smart Parse.

1. Crie uma tabela externa CSV que use vários tipos de dados de tempo, como DATE, DATETIME, TIMESTAMP e 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/'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   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. Após a inserção dos dados, o conteúdo do arquivo CSV é o seguinte:

   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. Consulte os dados novamente para visualizar o resultado.

   SELECT * FROM test_csv;

   O comando retorna o seguinte resultado:

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

Erro de incompatibilidade de contagem de colunas

• Sintoma

  Este erro ocorre quando a contagem de colunas em uma linha de um arquivo CSV ou TSV não corresponde à definida no DDL da tabela externa. O MaxCompute relata um erro semelhante a FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx.

• Resolução

  Controle como o MaxCompute lida com a incompatibilidade definindo o parâmetro odps.sql.text.schema.mismatch.mode no nível de sessão:

  • SET odps.sql.text.schema.mismatch.mode=error: Falha na consulta quando ocorre uma incompatibilidade de contagem de colunas. Este é o comportamento padrão.

  • SET odps.sql.text.schema.mismatch.mode=truncate: Se uma linha tiver mais colunas do que o definido no DDL da tabela externa, as colunas extras serão descartadas. Se uma linha tiver menos colunas, as colunas ausentes serão preenchidas com NULL.