Open CSV SerDe para processamento de CSV - Amazon Athena
Nome da biblioteca de serializaçãoUsar Open CSV SerDeExemplos

Open CSV SerDe para processamento de CSV

Use a biblioteca Open CSV SerDe para criar tabelas no Athena a partir de dados separados por vírgula (CSV).

Nome da biblioteca de serialização

O nome da biblioteca de serialização do Open CSV SerDe é org.apache.hadoop.hive.serde2.OpenCSVSerde. Para obter informações sobre o código-fonte, consulte CSV SerDe na documentação do Apache.

Usar Open CSV SerDe

Para usar esse SerDe, especifique o nome totalmente qualificado da classe após ROW FORMAT SERDE. Especifique também os delimitadores dentro de SERDEPROPERTIES, como no exemplo a seguir.

...
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "separatorChar" = ",",
  "quoteChar"     = "`",
  "escapeChar"    = "\\"
)

Ignorar cabeçalhos

Para ignorar os cabeçalhos nos dados ao definir a tabela, você pode usar a propriedade de tabela skip.header.line.count, conforme mostrado no exemplo a seguir.

TBLPROPERTIES ("skip.header.line.count"="1")

Para ver exemplos, consulte as instruções CREATE TABLE em Consultar os logs de fluxo do Amazon VPC e Consultar os logs do Amazon CloudFront.

Considerações sobre dados de strings

O Open CSV SerDe apresenta as seguintes características de dados de strings:

  • Usa aspas duplas (") como o caractere de aspas padrão e permite que você especifique separadores, aspas e caracteres de escape, como: 

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )

  • Não é possível inserir um caractere de escape em \t ou \n diretamente. Para inserir um caractere de escape neles, use "escapeChar" = "\\". Para ver um exemplo, consulte Example: Escaping \t or \n.

  • O Open CSV SerDe não oferece suporte a quebras de linha incorporadas em arquivos CSV.

Considerações sobre dados não strings

Para tipos de dados diferentes de STRING, o Open CSV SerDe apresenta o comportamento a seguir:

  • Reconhece os tipos de dados BOOLEAN, BIGINT, INT e DOUBLE.

  • Não reconhece valores vazios ou nulos nas colunas definidas como um tipo de dados numérico, deixando-os como string. Uma solução alternativa é criar a coluna com os valores nulos como string e usar CAST para converter o campo de uma consulta em um tipo de dados numérico, especificando um valor padrão de 0 para nulos. Para obter mais informações, consulte Quando consulto dados CSV no Athena, aparece o erro HIVE_BAD_DATA: erro ao analisar valor do campo (em inglês) na Central de Conhecimento da AWS.

  • Para colunas especificadas com o tipo de dados timestamp na instrução CREATE TABLE, reconhece os dados de TIMESTAMP caso sejam especificados no formato numérico UNIX em milissegundos, como 1579059880000. Para ver um exemplo, consulte Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format.

    • O Open CSV SerDe não oferece suporte a TIMESTAMP no formato java.sql.Timestamp compatível com JDBC, como "YYYY-MM-DD HH:MM:SS.fffffffff" (precisão de 9 casas decimais).

  • Para colunas especificadas com o tipo de dados DATE na instrução CREATE TABLE, reconhece os valores como datas se os valores representam o número de dias decorridos desde 1º de janeiro de 1970. Por exemplo, o valor 18276 em uma coluna com o tipo de dados date é processado como 2020-01-15 quando consultado. Nesse formato UNIX, é considerado que cada dia tenha 86.400 segundos.

    • O Open CSV SerDe não oferece suporte a DATE em nenhum outro formato diretamente. Para processar os dados de carimbo de data/hora em outros formatos, você pode definir a coluna como string e usar as funções de conversão de tempo para retornar os resultados desejados em sua consulta SELECT. Para obter mais informações, consulte o artigo When I query a table in Amazon Athena, the TIMESTAMP result is empty (Quando consulto uma tabela no Amazon Athena, o resultado de TIMESTAMP está vazio) na Central de Conhecimento da AWS.

  • Para converter mais colunas para o tipo desejado em uma tabela, crie uma visualização sobre a tabela e use CAST para converter para o tipo desejado.

Exemplos

exemplo Exemplo: consulta de dados CSV simples

O exemplo a seguir pressupõe que você tenha dados CSV salvos no local s3://amzn-s3-demo-bucket/mycsv/ com o conteúdo a seguir:

"a1","a2","a3","a4"
"1","2","abc","def"
"a","a1","abc3","ab4"

Use uma instrução CREATE TABLE para criar uma tabela do Athena com base nos dados. Faça referência a OpenCSVSerde (observe o “d” minúsculo) após ROW FORMAT SERDE e especifique o separador de caractere, o caractere de aspas e o caractere de escape em WITH SERDEPROPERTIES, como no exemplo a seguir.

CREATE EXTERNAL TABLE myopencsvtable (
   col1 string,
   col2 string,
   col3 string,
   col4 string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '"',
   'escapeChar' = '\\'
   )
STORED AS TEXTFILE
LOCATION 's3://amzn-s3-demo-bucket/mycsv/';

Consulte todos os valores na tabela:

SELECT * FROM myopencsvtable;

A consulta retorna os valores a seguir:

col1     col2    col3    col4
-----------------------------
a1       a2      a3      a4
1        2       abc     def
a        a1      abc3    ab4
exemplo Exemplo: uso do tipo TIMESTAMP e do tipo DATE especificados no formato numérico UNIX

Considere as três colunas de dados separados por vírgula a seguir. Os valores em cada coluna são colocados entre aspas duplas.

"unixvalue creationdate 18276 creationdatetime 1579059880000","18276","1579059880000"

A instrução a seguir cria uma tabela no Athena com base no local do bucket do Amazon S3 especificado.

CREATE EXTERNAL TABLE IF NOT EXISTS testtimestamp1(
 `profile_id` string,
 `creationdate` date,
 `creationdatetime` timestamp
 )
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
 LOCATION 's3://amzn-s3-demo-bucket'

Em seguida, execute a consulta a seguir: 

SELECT * FROM testtimestamp1

A consulta retorna o seguinte resultado, exibindo os dados de data e hora:

profile_id                                                        creationdate     creationdatetime
unixvalue creationdate 18276 creationdatetime 1579146280000       2020-01-15       2020-01-15 03:44:40.000
exemplo Exemplo: como inserir caracteres de escape \t ou \n

Considere os seguintes dados de teste:

" \\t\\t\\n 123 \\t\\t\\n ",abc
" 456 ",xyz

A instrução a seguir cria uma tabela no Athena que especifica "escapeChar" = "\\". 

CREATE EXTERNAL TABLE test1 (
f1 string,
s2 string) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\") 
LOCATION 's3://amzn-s3-demo-bucket/dataset/test1/'

Em seguida, execute a consulta a seguir: 

SELECT * FROM test1;

Ele retorna esse resultado, inserindo corretamente caracteres de escape em \t ou \n:

f1            s2
\t\t\n 123 \t\t\n            abc
456                          xyz