MaxCompute:Tabelas externas CSV e TSV

Tipos de dados suportados

Para obter mais informações sobre os tipos de dados do MaxCompute, consulte Versão 1.0 dos tipos de dados e Versão 2.0 dos tipos de dados.

Para mais detalhes sobre o SmartParse, consulte Compatibilidade flexível de tipos do Smart Parse.

Formatos de compactação suportados

Ao ler ou gravar arquivos compactados no OSS, inclua o atributo with serdeproperties na instrução CREATE TABLE. Para mais informações, consulte Parâmetros do atributo with serdeproperties.

Evolução de schema 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 Parâmetros básicos de sintaxe.

Parâmetros específicos de formato

Parâmetros

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

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

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

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

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

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

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

    • <null> or invalid input: A configuração no nível da tabela é utilizada.

  • 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á desativado.

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

• Interação entre parâmetros no 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 parâmetro não configurado
  flexible	on	On
  	off	Off, Desativado pela sessão
  	<null>, um valor inválido ou parâmetro não configurado	On
  Não configurado	on	On, Ativado pela sessão
  	off	Off
  	<null>, um valor inválido ou parâmetro não 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 no nível de tabela e de sessão.

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

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

   Parameter not set

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

   Flexible skipping

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

   Rigid skipping

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

   Parameter not set

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

   Flexible skipping

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

   Rigid skipping

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

Compatibilidade flexível de tipos com Smart Parse

Para tabelas externas formatadas em CSV no OSS, o MaxCompute SQL utiliza o tipo de dados 2.0 para operações de leitura e gravação. Anteriormente, apenas valores em formatos estritos eram suportados. Este recurso oferece compatibilidade flexível de tipos para leitura de uma ampla variedade de formatos de valores em arquivos CSV. As regras específicas de análise estão detalhadas abaixo.

Tipo	Entrada como string	Saída como string	Descrição
BOOLEAN	"true"/"false" "T"/"F" "1"/"0" "Yes"/"No" "Y"/"N" "" (Uma string vazia é analisada como NULL.) Nota Uma operação trim() será executada na entrada durante a análise.	"true"/"false" "true"/"false" "true"/"false" "true"/"false" "true"/"false" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	A análise falha se a string de entrada não for um dos valores suportados.
TINYINT	"0" "1" "-100" "1,234,567" (notação com separador de milhar; vírgulas não podem estar no início ou fim da string) "1_234_567" (estilo Java; underscores não podem estar no início ou fim da string) "0.3e2" (notação científica; analisado apenas se o valor for um inteiro; caso contrário, ocorre um erro) "-1e5" (notação científica) "0xff" (hexadecimal, insensível a maiúsculas/minúsculas) "0b1001" (binário, insensível a maiúsculas/minúsculas) "4/2" (fração; analisado apenas se o valor for um inteiro; caso contrário, ocorre um erro) "1000%" (porcentagem; analisado apenas se o valor for um inteiro; caso contrário, ocorre um erro) "1000‰" (por mil; analisado apenas se o valor for um inteiro; caso contrário, ocorre um erro) "1,000 $" (com símbolo monetário) "$ 1,000" (com símbolo monetário) "3M" (estilo K8s, unidade base-1000) "2Gi" (estilo K8s, unidade base-1024) "" (Uma string vazia é analisada como NULL.) Nota Durante a análise, a entrada passa por uma operação trim(). Se você usar notação com separador de milhar, como "1,234,567", deverá definir o delimitador CSV como um caractere diferente de vírgula. Para mais informações, consulte o uso de odps.text.option.delimiter em atributos with serdeproperties. As unidades base-1000 estilo K8s suportadas incluem K, M, G, P e T. As unidades base-1024 incluem Ki, Mi, Gi, Pi e Ti. Para mais informações, consulte resource-management. Os símbolos monetários suportados incluem $/¥/€/£/₩/USD/CNY/EUR/GBP/JPY/KRW/IDR/RP. As strings "0", "1", "-100" e "" também podem ser analisadas corretamente no naive mode.	"0" "1" "-100" "1234567" "1234567" "30" "-100000" "255" "9" "2" "10" "1" "1000" "1000" "3000000" (1M = 1000*1000) "2147483648" (1 Gi = 102410241024) "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Inteiro de 8 bits. Ocorre um erro se o valor estiver fora do intervalo [-128, 127].
SMALLINT			Inteiro de 16 bits. Ocorre um erro se o valor estiver fora do intervalo [-32768, 32767].
INT			Inteiro de 32 bits. Ocorre um erro se o valor estiver fora do intervalo [-2147483648, 2147483647].
BIGINT			Inteiro de 64 bits. Ocorre um erro se o valor estiver fora do intervalo [-9223372036854775807, 9223372036854775807]. Nota O valor -263 (-9223372036854775808) está fora do intervalo suportado devido a uma limitação no mecanismo SQL.
FLOAT	"3.14" "0.314e1" (notação científica) "2/5" (fração) "123.45%" (porcentagem) "123.45‰" (por mil) "1,234,567.89" (notação com separador de milhar) "1,234.56 $" (com símbolo monetário) "$ 1,234.56" (com símbolo monetário) "1.2M" (estilo K8s, unidade base-1000) "2Gi" (estilo K8s, unidade base-1024) "NaN" (insensível a maiúsculas/minúsculas) "Inf" (insensível a maiúsculas/minúsculas) "-Inf" (insensível a maiúsculas/minúsculas) "Infinity" (insensível a maiúsculas/minúsculas) "-Infinity" (insensível a maiúsculas/minúsculas) "" (Uma string vazia é analisada como NULL.) Nota A entrada passa por uma operação trim() durante a análise. As strings "3.14", "0.314e1", "NaN", "Infinity", "-Infinity" e "" também podem ser analisadas corretamente no naive mode.	"3.14" "3.14" "0.4" "1.2345" "0.12345" "1234567.89" "1234.56" "1234.56" "1200000" "2147483648" "NaN" "Infinity" "-Infinity" "Infinity" "-Infinity" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Valores especiais (insensíveis a maiúsculas/minúsculas) incluem NaN, Inf, -Inf, Infinity e -Infinity. Ocorre um erro se um valor estiver fora do intervalo. Se a precisão exceder o limite, o valor será arredondado.
DOUBLE	"3.1415926" "0.314e1" (notação científica) "2/5" (fração) "123.45%" (porcentagem) "123.45‰" (por mil) "1,234,567.89" (notação com separador de milhar) "1,234.56 $" (com símbolo monetário) "$ 1,234.56" (com símbolo monetário) "1.2M" (estilo K8s, unidade base-1000) "2Gi" (estilo K8s, unidade base-1024) "NaN" (insensível a maiúsculas/minúsculas) "Inf" (insensível a maiúsculas/minúsculas) "-Inf" (insensível a maiúsculas/minúsculas) "Infinity" (insensível a maiúsculas/minúsculas) "-Infinity" (insensível a maiúsculas/minúsculas) "" (Uma string vazia é analisada como NULL.) Nota Durante a análise, uma operação trim() é executada na entrada. As strings "3.1415926", "0.314e1", "NaN", "Infinity", "-Infinity" e "" também podem ser analisadas corretamente no naive mode.	"3.1415926" "3.14" "0.4" "1.2345" "0.12345" "1234567.89" "1234.56" "1234.56" "1200000" "2147483648" "NaN" "Infinity" "-Infinity" "Infinity" "-Infinity" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Valores especiais (insensíveis a maiúsculas/minúsculas) incluem NaN, Inf, -Inf, Infinity e -Infinity. Ocorre um erro se um valor estiver fora do intervalo. Se a precisão exceder o limite, o valor será arredondado.
DECIMAL (precision, scale) Exemplo: DECIMAL(15,2)	"3.358" "2/5" (fração) "123.45%" (porcentagem) "123.45‰" (por mil) "1,234,567.89" (notação com separador de milhar) "1,234.56 $" (com símbolo monetário) "$ 1,234.56" (com símbolo monetário) "1.2M" (estilo K8s, unidade base-1000) "2Gi" (estilo K8s, unidade base-1024) "" (Uma string vazia é analisada como NULL.) Nota Durante a análise, a operação trim() é executada na entrada. As strings "3.358" e "" também podem ser analisadas corretamente no naive mode.	"3.36" (arredondado) "0.4" "1.23" (arredondado) "0.12" (arredondado) "1234567.89" "1234.56" "1234.56" "1200000" "2147483648" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Ocorre um erro se a parte inteira contiver mais de precision - scale dígitos. Se a parte fracionária exceder a escala especificada, o valor será arredondado. Um erro é relatado. Se a parte fracionária exceder a escala, o valor será arredondado e truncado.
CHAR(n) Exemplo: CHAR(7)	"abcdefg" "abcdefghijklmn" "abc" "" (Uma string vazia é analisada como NULL.)	"abcdefg" "abcdefg" (O restante da string é truncado.) "abc____" (preenchido com quatro caracteres de espaço, representados por _) "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	O comprimento máximo é 255. Se uma string de entrada for menor que n, ela será preenchida com espaços à direita, mas esses espaços são ignorados nas comparações. Se uma string de entrada for maior que n, ela será truncada.
VARCHAR(n) Exemplo: VARCHAR(7)	"abcdefg" "abcdefghijklmn" "abc" "" (Uma string vazia é analisada como NULL.)	"abcdefg" "abcdefg" (O restante da string é truncado.) "abc" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	O comprimento máximo é 65.535. Se uma string de entrada for maior que n, ela será truncada.
STRING	"abcdefg" "abc" "" (Uma string vazia é analisada como NULL.)	"abcdefg" "abc" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	O comprimento máximo é 8 MB.
DATE	"yyyy-MM-dd" (por exemplo, "2025-02-21") "yyyyMMdd" (por exemplo, "20250221") "MMM d,yyyy" (por exemplo, "Oct 1,2025") "MMMM d,yyyy" (por exemplo, "October 1,2025") "" (Uma string vazia é analisada como NULL.) Nota Você também pode definir a propriedade odps.text.option.date.io.format para controlar o formato de análise. Por exemplo, se você definir o formato como 'dd/MM/yyyy#yyyy--MM--dd', o MaxCompute poderá analisar strings como '21/02/2025' e '2025--02--21'.	"2000-01-01" "2000-01-01" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Este tipo não contém informações de hora, portanto, alterar o fuso horário não afeta a saída. O formato de saída padrão é "yyyy-MM-dd". Você pode definir a propriedade odps.text.option.date.io.format para definir formatos personalizados de análise e saída. O primeiro padrão definido é usado para saída. Para sintaxe de padrões, consulte DateTimeFormatter.
TIMESTAMP_NTZ Nota O OpenCsvSerde não suporta este tipo porque é incompatível com o formato de dados Hive.	A parte de nanossegundos pode ter de 0 a 9 dígitos. Os formatos integrados suportados pelo MaxCompute são: "yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]" (por exemplo, "2000-01-01 00:00:00.123") "yyyy-MM-ddTHH:mm:ss[.SSSSSSSSS]" (por exemplo, "2000-01-01T00:00:00.123456789") "yyyyMMddHHmmss" (por exemplo, "20000101000000") "" (Uma string vazia é analisada como NULL.) Você também pode definir a propriedade odps.text.option.timestamp_ntz.io.format para controlar o formato de análise. Por exemplo, se você definir o formato como 'ddMMyyyy-HHmmss', o MaxCompute poderá analisar uma string como '31102024-103055'.	"2000-01-01 00:00:00.123000000" "2000-01-01 00:00:00.123456789" "2000-01-01 00:00:00.000000000" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Este tipo representa um carimbo de data/hora com precisão de nanossegundos. Ele não é afetado pelo fuso horário da sessão e sua saída tem como padrão o fuso horário UTC. O formato de saída padrão é "yyyy-MM-dd HH:mm:ss.SSSSSSSSS". Você pode definir a propriedade odps.text.option.timestamp_ntz.io.format para definir formatos personalizados de análise e saída. Para sintaxe de padrões, consulte DateTimeFormatter.
DATETIME	A parte de milissegundos pode ter de 0 a 3 dígitos. [x] representa o deslocamento do fuso horário. Supondo que o fuso horário do sistema seja Asia/Shanghai, os formatos integrados suportados pelo MaxCompute são: "yyyy-MM-dd HH:mm:ss[.SSS][x]" (por exemplo, "2000-01-01 00:00:00.123") "yyyy-MM-ddTHH:mm:ss[.SSS][x]" (por exemplo, "2000-01-01T00:00:00.123+0000") "yyyyMMddHHmmss[x]" (por exemplo, "20000101000000+0000") "" (Uma string vazia é analisada como NULL.) Você também pode definir a propriedade odps.text.option.datetime.io.format para controlar o formato de análise. Por exemplo, se você definir o formato como 'yyyyMMdd-HHmmss.SSS', o MaxCompute poderá analisar uma string como '20241031-103055.123'.	Supondo que o fuso horário do sistema seja Asia/Shanghai: "2000-01-01 00:00:00.123+0800" "2000-01-01 08:00:00.123+0800" "2000-01-01 08:00:00.000+0800" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Este tipo representa um carimbo de data/hora com precisão de milissegundos. O valor de saída é afetado pelo fuso horário da sessão. O formato de saída padrão é "yyyy-MM-dd HH:mm:ss.SSSx". Você pode definir a propriedade odps.sql.timezone para alterar o fuso horário do sistema, o que controla o deslocamento do fuso horário do valor de saída. Você também pode definir a propriedade odps.text.option.datetime.io.format para definir formatos personalizados de análise e saída. Para sintaxe de padrões, consulte DateTimeFormatter.
TIMESTAMP	A parte de nanossegundos pode ter de 0 a 9 dígitos. [x] representa o deslocamento do fuso horário. Supondo que o fuso horário do sistema seja Asia/Shanghai, os formatos integrados suportados pelo MaxCompute são: "yyyy-MM-dd HH:mm:ss[.SSSSSSSSS][x]" (por exemplo, "2000-01-01 00:00:00.123456") "yyyy-MM-ddTHH:mm:ss[.SSSSSSSSS][x]" (por exemplo, "2000-01-01T00:00:00.123+0000") "yyyyMMddHHmmss[x]" (por exemplo, "20000101000000+0000") "" (Uma string vazia é analisada como NULL.) Você também pode definir a propriedade odps.text.option.timestamp.io.format para controlar o formato de análise. Por exemplo, se você definir o formato como 'yyyyMMdd-HHmmss', o MaxCompute poderá analisar uma string como '20240910-103055'.	(Supondo que o fuso horário do sistema seja Asia/Shanghai) "2000-01-01 00:00:00.123456000+0800" "2000-01-01 08:00:00.123000000+0800" "2000-01-01 08:00:00.000000000+0800" "" (Valores NULL são gravados como uma string vazia no arquivo CSV.)	Este tipo representa um carimbo de data/hora com precisão de nanossegundos. O valor de saída é afetado pelo fuso horário da sessão. O formato de saída padrão é "yyyy-MM-dd HH:mm:ss.SSSSSSSSSx". Você pode definir a propriedade odps.sql.timezone para alterar o fuso horário do sistema, o que controla o deslocamento do fuso horário do valor de saída. Você também pode definir a propriedade odps.text.option.timestamp.io.format para definir formatos personalizados de análise e saída. Para sintaxe de padrões, consulte DateTimeFormatter.

• Regras gerais

  • Para qualquer tipo de dado, uma string vazia no arquivo de dados CSV é analisada como NULL ao ser lida em uma tabela.

• Tipos de dados não suportados

  • Tipos complexos (STRUCT, ARRAY, MAP): Não suportados. Valores desses tipos frequentemente contêm caracteres como a vírgula (,), que podem entrar em conflito com delimitadores CSV comuns e causar falhas na análise.

  • BINARY e INTERVAL: Atualmente não suportados. Se precisar de suporte para esses tipos, entre em contato com o suporte técnico do MaxCompute.

• Tipos numéricos (INT, DOUBLE, etc.)

  • Para tipos de dados numéricos como INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE e DECIMAL, o MaxCompute oferece amplas capacidades de análise padrão.

  • Se precisar analisar apenas strings numéricas básicas, defina a propriedade odps.text.option.smart.parse.level como naive em tblproperties. No modo naive, o parser suporta apenas formatos simples como "123" e "123.456". Analisar outros formatos de string causa um erro.

• Tipos de data e hora (DATE, TIMESTAMP, etc.)

  • A classe java.time.format.DateTimeFormatter processa todos os quatro tipos de data e hora: DATE, DATETIME, TIMESTAMP e TIMESTAMP_NTZ.

  • Formatos padrão: O MaxCompute possui vários formatos de análise integrados.

  • Formatos personalizados:

    • Defina múltiplos formatos de análise e um formato de saída configurando a propriedade odps.text.option.<date|datetime|timestamp|timestamp_ntz>.io.format em tblproperties.

    • Utilize o símbolo de cerquilha (#) para separar múltiplos padrões de análise.

    • Formatos personalizados têm precedência sobre os formatos integrados. O primeiro padrão personalizado é usado para saída.

    • Exemplo: Se você definir a string de formato personalizado para o tipo DATE como pattern1#pattern2#pattern3, o MaxCompute poderá analisar strings que correspondam a pattern1, pattern2 ou pattern3. No entanto, ao gravar dados em um arquivo, a saída sempre usará o formato especificado por pattern1. Para mais informações, consulte DateTimeFormatter.

• Nota importante sobre o padrão de fuso horário 'z'

  • Evite usar 'z' (nome do fuso horário) em formatos personalizados, especialmente para usuários na China, pois é ambíguo.

  • Em vez disso, use 'x' (deslocamento de zona) ou 'VV' (ID do fuso horário) para o padrão de fuso horário.

  • Exemplo: 'CST' geralmente significa China Standard Time (UTC+8) na China. No entanto, quando java.time.format.DateTimeFormatter analisa 'CST', ele interpreta como US Central Standard Time (UTC-6), o que pode causar resultados inesperados de entrada ou saída.

Exemplos

Pré-requisitos

• Um projeto MaxCompute. Consulte Criar um projeto MaxCompute.

• Um bucket do OSS na mesma região do seu projeto MaxCompute. Consulte Criar um bucket e Gerenciar pastas. O MaxCompute pode criar pastas no OSS automaticamente — não é necessário criá-las antes de executar SQL.

• Permissões de acesso ao OSS via conta Alibaba Cloud, usuário Resource Access Management (RAM) ou função RAM. Consulte Autorizar acesso no modo STS para OSS.

• A permissão CreateTable no seu projeto MaxCompute. Consulte Permissões do MaxCompute.

Criar uma tabela externa OSS com o parser de texto integrado

Exemplo 1: Tabela não particionada

1. Mapeie a tabela externa para o diretório Demo1/ dos dados de amostra. Utilize o comando a seguir para criar uma tabela externa OSS.

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external1
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo1/';
   
   -- You can run the `desc extended mc_oss_csv_external1;` command to view the schema of the created OSS external table.

   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 alvo e conceda a ela as permissões necessárias para acessar o OSS.

2. Consulte a tabela externa não particionada.

   SELECT * FROM mc_oss_csv_external1;

   O comando retorna o seguinte resultado:

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 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          |
   +------------+------------+------------+------------+------------------+-------------------+------------+----------------+

3. Grave dados na tabela externa não particionada e verifique se a gravação foi bem-sucedida.

   INSERT INTO mc_oss_csv_external1 VALUES(1,12,76,1,46.81006,-92.08174,'9/14/2014 0:10','SW');
   SELECT * FROM mc_oss_csv_external1 WHERE recordId=12;

   O comando retorna o seguinte resultado:

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

   Verifique se um novo arquivo aparece no diretório Demo1/ no OSS.

   Após a gravação dos dados, visualize o arquivo de resultado gerado 20250606054845430gpwnhakujm16_M1_1_0_0-0_TableSink1-0-.csv (0,046 KB) no caminho correspondente do OSS, juntamente com o arquivo de dados original vehicle.csv (0,45 KB).

Exemplo 2: Tabela particionada

1. Mapeie a tabela externa para o diretório Demo2/ dos dados de amostra. O comando de exemplo a seguir cria uma tabela externa OSS particionada.

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external2
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING
   )
   PARTITIONED BY (
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo2/';
   
   -- You can run the `DESC EXTENDED mc_oss_csv_external2;` command to view the schema of the created external table.

   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 alvo e conceda a ela as permissões necessárias para acessar o OSS.

2. Importe os dados da partição. Se você criar uma tabela externa OSS particionada, também deverá importar os dados da partição. Para mais informações, consulte Tabelas externas OSS.

   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;
   
   -- This is equivalent to the following statement.
   ALTER TABLE mc_oss_csv_external2 ADD PARTITION (direction = 'N') 
     PARTITION (direction = 'NE') PARTITION (direction = 'S') 
     PARTITION (direction = 'SW') PARTITION (direction = 'W');

3. Consulte a tabela externa particionada.

   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE';

   O comando retorna o seguinte resultado:

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 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          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

4. Grave dados na tabela externa particionada e verifique se a gravação foi bem-sucedida.

   INSERT INTO mc_oss_csv_external2 PARTITION(direction='NE') VALUES(1,12,76,1,46.81006,-92.08174,'9/14/2014 0:10');
   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE' AND recordId=12;

   O comando retorna o seguinte resultado:

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

   Verifique se um novo arquivo foi gerado no diretório Demo2/direction=NE no OSS.

   O arquivo de dados de partição gerado automaticamente 20250606062610590gocsdsoujm16_M1_1_0_0-0_TableSink1-0-.csv pode ser visto na lista de arquivos do OSS, indicando que os dados foram gravados com sucesso no caminho de partição correspondente no OSS.

Exemplo 3: Dados compactados

Este exemplo mostra como criar uma tabela externa CSV compactada em GZIP e realizar operações de leitura e gravação.

1. Crie uma tabela interna e insira dados de teste para um teste de gravação subsequente.

   CREATE TABLE vehicle_test(
     vehicleid INT, 
     recordid INT, 
     patientid INT, 
     calls INT, 
     locationlatitute DOUBLE, 
     locationlongtitue DOUBLE, 
     recordtime STRING, 
     direction STRING
   );
   
   INSERT INTO vehicle_test VALUES (1,1,51,1,46.81006,-92.08174,'9/14/2014 0:00','S');

2. Crie uma tabela externa CSV compactada em GZIP e mapeie-a para o diretório Demo3/ (que contém dados compactados) dos dados de amostra. O comando de exemplo a seguir cria a tabela externa OSS.

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external3
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   PARTITIONED BY (dt STRING)
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
     'odps.text.option.gzip.input.enabled'='true',
     'odps.text.option.gzip.output.enabled'='true' 
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo3/';
   
   -- Import partition data.
   MSCK REPAIR TABLE mc_oss_csv_external3 ADD PARTITIONS;
   
   -- You can run the `DESC EXTENDED mc_oss_csv_external3;` command to view the schema of the created external table.

   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 alvo e conceda a ela as permissões necessárias para acessar o OSS.

3. Use um cliente MaxCompute para ler dados do OSS:

   Nota

   Se os dados compactados no OSS estiverem em um formato de dados de código aberto, adicione o comando set odps.sql.hive.compatible=true; antes da instrução SQL e envie-os juntos para execução.

   --Enable a full table scan for the current session only.
   SET odps.sql.allow.fullscan=true;
   SELECT recordId, patientId, direction FROM mc_oss_csv_external3 WHERE patientId > 25;

   O comando retorna o seguinte resultado:

   +------------+------------+------------+
   | recordid   | patientid  | direction  |
   +------------+------------+------------+
   | 1          | 51         | S          |
   | 3          | 48         | NE         |
   | 4          | 30         | W          |
   | 5          | 47         | S          |
   | 7          | 53         | N          |
   | 8          | 63         | SW         |
   | 10         | 31         | N          |
   +------------+------------+------------+

4. Leia dados da tabela interna e grave-os na tabela externa OSS.

   Execute o comando INSERT OVERWRITE ou INSERT INTO em uma tabela externa a partir do cliente MaxCompute para gravar dados no OSS.

   INSERT INTO TABLE mc_oss_csv_external3 PARTITION (dt='20250418') SELECT * FROM vehicle_test;

   Após a execução bem-sucedida do comando, visualize o arquivo exportado no diretório do OSS.

Criar uma tabela externa com linha de cabeçalho

Crie um diretório Demo11 no bucket oss-mc-test dos dados de amostra 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'
)
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 alvo 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 dos dados de amostra 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')
      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 parser de código aberto

Este exemplo mostra como usar o parser integrado de código aberto para criar uma tabela externa 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 dos dados de amostra 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" = ","
   )
   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 Compatibilidade flexível de tipos com Smart Parse.

1. Crie uma tabela externa CSV que usa 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/'
   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 |
   +------------+---------------------+---------------------+------------------------+

FAQ

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 da sessão:

  • SET odps.sql.text.schema.mismatch.mode=error: Falha a 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.