OpenCSVSerDe para el procesamiento de CSV - Amazon Athena
CSV SerDe (OpenCSVSerDe)

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 SerDe para 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 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 formato java.sql.Timestamp, utilice el formato LazySimpleSerDe.

CSV SerDe (OpenCSVSerDe)

El SerDe de OpenCSV 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" = "\\". 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 y DOUBLE.

  • 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 como string y, a continuación, utilizar CAST para convertir el campo de una consulta a un tipo de datos numérico, y proporcionar un valor predeterminado de 0 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 value en el Centro de conocimientos de AWS.

  • Para las columnas especificadas con el tipo de dato timestamp en la instrucción CREATE TABLE, reconoce TIMESTAMP si se especifica en formato numérico de UNIX en milisegundos, como 1579059880000.

    • El OpenCSVSerDe no admite TIMESTAMP en formato java.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ón CREATE 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 valor 18276 en una columna con el tipo de datos date se representa como 2020-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 como string y, a continuación, utilizar las funciones de conversión de tiempo para devolver los resultados deseados en la consulta SELECT. Para obtener más información, consulte el artículo When I query a table in Amazon Athena, the TIMESTAMP result is empty (Cuando consulto una tabla en Amazon Athena, el resultado TIMESTAMP está vacío) en 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

El SerDe de CSV

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