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
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 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
\tou\ndiretamente. 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,INTeDOUBLE. -
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 comostringe usarCASTpara converter o campo de uma consulta em um tipo de dados numérico, especificando um valor padrão de0para 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
timestampna instruçãoCREATE TABLE, reconhece os dados deTIMESTAMPcaso sejam especificados no formato numérico UNIX em milissegundos, como1579059880000. 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
TIMESTAMPno formatojava.sql.Timestampcompatí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
DATEna instruçãoCREATE 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 valor18276em uma coluna com o tipo de dadosdateé processado como2020-01-15quando consultado. Nesse formato UNIX, é considerado que cada dia tenha 86.400 segundos.-
O Open CSV SerDe não oferece suporte a
DATEem nenhum outro formato diretamente. Para processar os dados de carimbo de data/hora em outros formatos, você pode definir a coluna comostringe usar as funções de conversão de tempo para retornar os resultados desejados em sua consultaSELECT. 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
CASTpara 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 ab4exemplo 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 testtimestamp1A 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
