Open CSV SerDe para el procesamiento de CSV
Utilice Open CSV SerDe para crear tablas en Athena a partir de datos de valores separados por comas (CSV).
Nombre de la biblioteca de serialización
El nombre de la biblioteca de serialización de Open CSV SerDe es org.apache.hadoop.hive.serde2.OpenCSVSerde
. Para obtener información sobre el código fuente, consulte CSV SerDe
Uso de Open CSV SerDe
Para utilizar este SerDe, especifique el nombre completo de la clase ROW FORMAT
SERDE
. Especifique también los delimitadores dentro de SERDEPROPERTIES
, como se indica en el ejemplo siguiente.
... ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
Omisión de 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.
Consideraciones sobre los datos de cadena
Open CSV SerDe tiene las siguientes características para los datos de cadena:
-
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" = "\\"
. Para ver un ejemplo, consulte Example: Escaping \t or \n. -
Open CSV SerDe no admite saltos de línea incrustados en los archivos CSV.
Consideraciones para los datos que no son de cadena
En el caso de los tipos de datos distintos de STRING
, Open CSV SerDe 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
. Para ver un ejemplo, consulte Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format.-
Open CSV SerDe 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.-
Open CSV SerDe 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.
Ejemplos
ejemplo Ejemplo: consulta de datos de CSV simples
En el siguiente ejemplo, se supone que se tienen los datos de CSV guardados en la ubicación s3://amzn-s3-demo-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 OpenCSVSerde
(tenga en cuenta la “d” en minúscula) 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://amzn-s3-demo-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
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://amzn-s3-demo-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://amzn-s3-demo-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