OpenCSVSerDe per elaborare file CSV - Amazon Athena
Nome della libreria di serializzazioneUsare Open CSV SerDeEsempi

OpenCSVSerDe per elaborare file CSV

Utilizzare la libreria OpenCSvSerDe per creare tabelle in Athena per i dati separati da virgole.

Nome della libreria di serializzazione

Il nome della libreria di serializzazione per Open CSV SerDe è org.apache.hadoop.hive.serde2.OpenCSVSerde. Per informazioni sul codice sorgente, consultare CSV SerDe nella documentazione Apache.

Usare Open CSV SerDe

Per usare questa SerDe, specificare il nome di classe completo dopo ROW FORMAT SERDE. Specificare anche i delimitatori all'interno di SERDEPROPERTIES nel modo seguente:

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

Intestazioni di origine

Per ignorare le intestazioni nei dati quando si definisce una tabella, è possibile utilizzare la proprietà skip.header.line.count, come nell'esempio seguente.

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

Per alcuni esempi, consulta le istruzioni CREATE TABLE in Eseguire query sui log di flusso Amazon VPC e Query dei log di Amazon CloudFront.

Utilizzo di NULL per dati non validi

Per utilizzare i valori NULL per i dati che non riescono a deserializzarsi nel tipo definito della colonna, è possibile utilizzare la proprietà della tabella use.null.for.invalid.data, come illustrato nell'esempio seguente. 

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

L'impostazione use.null.for.invalid.data su TRUE può causare risultati errati o imprevisti perché i valori NULL sostituiscono i dati non validi nelle colonne con mancate corrispondenze dello schema. Si consiglia di correggere i dati nei file o nello schema della tabella anziché abilitare questa proprietà. Quando si abilita questa proprietà, le query non avranno esito negativo su dati non validi, il che potrebbe impedire di scoprire problemi di qualità dei dati.

Considerazioni sui dati SUPER

OpenCSV SerDe ha le seguenti caratteristiche per i dati stringa:

  • Utilizza doppie virgolette (") come virgolette predefinite e consente di specificare separatore, virgolette e caratteri di escape, ad esempio: 

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

  • Impossibile utilizzare direttamente il carattere di escape \t o \n. Per utilizzarli come caratteri di escape, utilizza "escapeChar" = "\\". Per vedere un esempio, consulta Example: Escaping \t or \n.

  • Non supporta le interruzioni di linea incorporate nei file CSV.

Considerazioni per i dati non stringa

Per tipi di dati diversi da STRING, OpenCSVSerDe si comporta nel modo seguente:

  • Riconosce i tipi di dati BOOLEAN, BIGINT, INT e DOUBLE.

  • Non riconosce valori vuoti o nulli nelle colonne definite come un tipo di dati numerici, lasciandoli come string. Una soluzione alternativa è creare la colonna con i valori nulli comestring e quindi utilizzare CAST per convertire il campo in una query per un tipo di dati numerico, fornendo un valore predefinito 0 per i valori nulli. Per ulteriori informazioni, consulta Quando eseguo query sui dati CSV in Athena, ottengo l'errore HIVE_BAD_DATA: Error parsing field value nel Portale del sapere di AWS.

  • Per le colonne specificate con il tipo di dati timestamp nell'istruzione CREATE TABLE, riconosce i dati TIMESTAMP se sono specificati nel formato numerico UNIX in millisecondi, ad esempio 1579059880000. Per vedere un esempio, consulta Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format.

    • OpenCSVSerDe son supporta TIMESTAMP nel formato java.sql.Timestamp compatibile con JDBC, ad esempio "YYYY-MM-DD HH:MM:SS.fffffffff" (precisione a 9 posizioni decimali).

  • Per le colonne specificate con il tipo di dati DATE nell'istruzione CREATE TABLE, riconosce i valori come date se i valori rappresentano il numero di giorni trascorsi dal 1° gennaio 1970. Ad esempio, il valore 18276 in una colonna con il tipo di dati date viene eseguito come 2020-01-15 quando viene interrogato. In questo formato UNIX, si considera che ogni giorno abbia 86.400 secondi.

  • Per convertire ulteriormente le colonne nel tipo desiderato in una tabella, è possibile creare una vista della tabella e utilizzare CAST per convertirle nel tipo desiderato.

Esempi

Esempio: interrogazione di semplici dati CSV

L'esempio seguente presuppone che i dati CSV siano salvati nella posizione s3://amzn-s3-demo-bucket/mycsv/ con i seguenti contenuti:

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

Utilizza un'istruzione CREATE TABLE per creare una tabella Athena basata sui dati. Fare riferimento alla classe OpenCSvSerDe dopo OpenCSVSerde e specificare il separatore di caratteri, il carattere virgoletta e il carattere di escape in , come nell'esempio seguente.

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/';

Crea una query di tutti i valori nella tabella:

SELECT * FROM myopencsvtable;

La query restituisce i seguenti valori:

col1     col2    col3    col4
-----------------------------
a1       a2      a3      a4
1        2       abc     def
a        a1      abc3    ab4
Esempio: utilizzo del tipo TIMESTAMP e del tipo DATE specificati nel formato numerico UNIX.

Considerare le tre seguenti colonne di dati separati da virgole. I valori in ciascuna colonna sono racchiusi tra virgolette doppie.

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

L'istruzione seguente crea una tabella in Athena dal percorso del bucket Amazon S3.

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'

Esegui quindi la seguente query: 

SELECT * FROM testtimestamp1

La query restituisce il seguente risultato, mostrando i dati di data e ora:

profile_id                                                        creationdate     creationdatetime
unixvalue creationdate 18276 creationdatetime 1579146280000       2020-01-15       2020-01-15 03:44:40.000
Esempio: utilizzo di o come caratteri escape

Tieni in considerazione i seguenti dati di verifica:

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

La seguente istruzione crea una tabella in Athena, specificando "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/'

Esegui quindi la seguente query: 

SELECT * FROM test1;

Restituisce questo risultato, utilizzando correttamente \t o \n come caratteri di escape:

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