OpenCSVSerDe para el procesamiento de CSV
Cuando crea una tabla de datos CSV en Athena, determine qué SerDe utilizar en función de los tipos de valores contenidos en sus datos:
-
Si los datos contienen valores entre comillas dobles (
"
), puede utilizar el OpenCSV SerDepara deserializar los valores en Athena. Si los datos no contienen valores entre comillas dobles ( "
), puede omitir la especificación del SerDe. En este caso, Athena utiliza la opción predeterminada,LazySimpleSerDe
. Para obtener más información, consulte LazySimpleSerDe para CSV, TSV y archivos con delimitación personalizada. -
Si los datos tienen valores
TIMESTAMP
UNIX numéricos (por ejemplo,1579059880000
), utilice el OpenCSVSerDe. Si los datos utilizan el formatojava.sql.Timestamp
, utilice el formato LazySimpleSerDe.
CSV SerDe (OpenCSVSerDe)
El SerDe de OpenCSV
-
Utiliza comillas (
"
) como carácter de comillas predeterminado y permite especificar caracteres separadores, de comillas y de escape como:WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
-
No se pueden aplicar directamente los caracteres de escape
\t
y\n
. Para aplicarlos, utilice"escapeChar" = "\\"
. Consulte el ejemplo de este tema. -
No admite saltos de línea incrustados en los archivos CSV.
Para tipos de datos distintos de STRING
, el OpenCSVSerDe se comporta de la siguiente manera:
-
Reconoce tipos de datos
BOOLEAN
,BIGINT
,INT
yDOUBLE
. -
No reconoce valores vacíos o nulos en columnas definidas como un tipo de datos numérico, y los deja como
string
. Una solución alternativa es crear la columna con los valores nulos comostring
y, a continuación, utilizarCAST
para convertir el campo de una consulta a un tipo de datos numérico, y proporcionar un valor predeterminado de0
para valores nulos. Para obtener más información, consulte Cuando consulto datos CSV en Athena, obtengo el error HIVE_BAD_DATA: Error parsing field valueen el Centro de conocimientos de AWS. -
Para las columnas especificadas con el tipo de dato
timestamp
en la instrucciónCREATE TABLE
, reconoceTIMESTAMP
si se especifica en formato numérico de UNIX en milisegundos, como1579059880000
.-
El OpenCSVSerDe no admite
TIMESTAMP
en formatojava.sql.Timestamp
compatible con JDBC, como"YYYY-MM-DD HH:MM:SS.fffffffff"
(precisión de posición de 9 decimales).
-
-
Para las columnas especificadas con el tipo de dato
DATE
en la instrucciónCREATE TABLE
, reconoce los valores como fechas si estos representan el número de días transcurridos desde el 1 de enero de 1970. Por ejemplo, el valor18276
en una columna con el tipo de datosdate
se representa como2020-01-15
cuando se consulta. En este formato UNIX, cada día se considera que tiene 86 400 segundos.-
El OpenCSVSerDe no es compatible con
DATE
en cualquier otro formato directamente. Para procesar datos de marca de tiempo en otros formatos, puede definir la columna comostring
y, a continuación, utilizar las funciones de conversión de tiempo para devolver los resultados deseados en la consultaSELECT
. Para obtener más información, consulte el artículo Cuando consulto una tabla en Amazon Athena, el resultado TIMESTAMP está vacíoen el Centro de conocimientos de AWS .
-
-
Si desea seguir convirtiendo las columnas al tipo deseado en una tabla, puede crear una vista de la tabla y utilizar
CAST
para la conversión.
ejemplo Ejemplo: uso del tipo TIMESTAMP y el tipo DATE especificados en el formato numérico de UNIX.
Tenga en cuenta las siguientes tres columnas de datos separados por comas. Los valores de cada columna se encierran entre comillas dobles.
"unixvalue creationdate 18276 creationdatetime 1579059880000","18276","1579059880000"
La siguiente instrucción crea una tabla en Athena desde la ubicación del bucket de Amazon S3 especificada.
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://DOC-EXAMPLE-BUCKET'
A continuación, ejecute la consulta siguiente:
SELECT * FROM testtimestamp1
La consulta devuelve el siguiente resultado, mostrando los datos de fecha y hora:
profile_id creationdate creationdatetime unixvalue creationdate 18276 creationdatetime 1579146280000 2020-01-15 2020-01-15 03:44:40.000
ejemplo Ejemplo: secuencia de escape \t
o \n
Considere los datos de prueba siguientes:
" \\t\\t\\n 123 \\t\\t\\n ",abc " 456 ",xyz
La instrucción siguiente crea una tabla en Athena y especifica que "escapeChar" = "\\"
.
CREATE EXTERNAL TABLE test1 ( f1 string, s2 string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\") LOCATION 's3://DOC-EXAMPLE-BUCKET/dataset/test1/'
A continuación, ejecute la consulta siguiente:
SELECT * FROM test1;
Devuelve este resultado, utilizando correctamente la secuencia de escape \t
o \n
:
f1 s2 \t\t\n 123 \t\t\n abc 456 xyz
Nombre del SerDe
Nombre de la biblioteca
Para utilizar este SerDe, especifique el nombre completo de la clase ROW FORMAT
SERDE
. Especifique también los delimitadores dentro de SERDEPROPERTIES
, tal y como se indica a continuación:
... ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
Ignorar encabezados
Para ignorar los encabezados en los datos cuando defina una tabla, puede utilizar la propiedad de tabla skip.header.line.count
, como en el siguiente ejemplo.
TBLPROPERTIES ("skip.header.line.count"="1")
Para ver ejemplos, consulte las instrucciones CREATE TABLE
en Consulta de los registros de flujo de Amazon VPC y Consultas de registros de Amazon CloudFront.
Ejemplo
En este ejemplo se presupone que hay datos CSV guardados en s3://DOC-EXAMPLE-BUCKET/mycsv/
con el siguiente contenido:
"a1","a2","a3","a4"
"1","2","abc","def"
"a","a1","abc3","ab4"
Utilice una instrucción CREATE TABLE
para crear una tabla Athena basada en los datos. Haga referencia a la clase OpenCSVSerDe después de ROW FORMAT SERDE
y especifique el separador de caracteres, el carácter de comillas y el carácter de escape en WITH
SERDEPROPERTIES
, como en el siguiente ejemplo.
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://DOC-EXAMPLE-BUCKET/mycsv/';
Consulte todos los valores de la tabla:
SELECT * FROM myopencsvtable;
La consulta devuelve los siguientes valores:
col1 col2 col3 col4
-----------------------------
a1 a2 a3 a4
1 2 abc def
a a1 abc3 ab4