SerDe Open CSV pour le traitement de données CSV - Amazon Athena
Nom de la bibliothèque de sérialisationUtilisation du SerDe Open CSVExemples

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

SerDe Open CSV pour le traitement de données CSV

Utilisez la bibliothèque SerDe Open CSV pour créer des tables Athena à partir de données séparées par des virgules (CSV).

Nom de la bibliothèque de sérialisation

La bibliothèque de sérialisation pour le SerDe Open CSV est nommée org.apache.hadoop.hive.serde2.OpenCSVSerde. Pour plus d’informations sur le code source, consultez CSV SerDe dans la documentation Apache.

Utilisation du SerDe Open CSV

Pour utiliser ce SerDe, spécifiez son nom de classe complet après ROW FORMAT SERDE. Spécifiez également les délimiteurs dans SERDEPROPERTIES, comme illustré dans l’exemple ci-dessous.

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

Ignorer les en-têtes

Pour ignorer les en-têtes dans vos données lorsque vous définissez une table, vous pouvez utiliser la propriété de table skip.header.line.count, comme dans l'exemple suivant.

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

Pour des exemples, voir les instructions CREATE TABLE dans Interrogation des journaux de flux Amazon VPC et Interrogation des journaux Amazon CloudFront.

Utilisation de la valeur NULL pour les données non valides

Pour utiliser des valeurs NULL pour les données dont la désérialisation dans le type défini pour la colonne échoue, vous pouvez utiliser la propriété de table use.null.for.invalid.data, comme illustré dans l’exemple suivant. 

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

La définition de use.null.for.invalid.data sur TRUE peut entraîner des résultats incorrects ou inattendus, car les valeurs NULL remplacent les données non valides dans les colonnes présentant des problèmes de correspondance de schéma. Il est préférable de corriger les données dans vos fichiers ou votre schéma de table que d’activer cette propriété. Lorsque vous activez cette propriété, les données non valides n’entraînent pas l’échec des requêtes, ce qui peut vous empêcher de déceler des problèmes de qualité des données.

Considérations relatives aux données de type chaîne

Le SerDe Open CSV présente les caractéristiques suivantes pour les données de type chaîne :

  • Utilise des guillemets (") en tant que caractère par défaut et vous permet de spécifier des séparateurs, des guillemets et des caractères d'échappement, tels que : 

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

  • Vous ne pouvez pas échapper directement \t ou \n. Pour ce faire, utilisez "escapeChar" = "\\". Pour obtenir un exemple, consultez Example: Escaping \t or \n.

  • Le SerDe Open CSV ne prend pas en charge les sauts de ligne imbriqués dans les fichiers CSV.

Considérations relatives aux données d’un type autre que chaîne

Pour les types de données autres que STRING, le SerDe Open CSV se comporte comme suit :

  • Reconnaît les de données BOOLEAN, BIGINT, INT et DOUBLE.

  • Ne reconnaît pas les valeurs vides ou nulles dans les colonnes définies en tant que type de données numériques, et les conserve en tant que string. Une solution consiste à créer la colonne avec les valeurs nulles en tant que string et utiliser ensuite CAST pour convertir le champ dans une requête en un type de données numérique, en fournissant une valeur par défaut de 0 pour les valeurs nulles. Pour plus d'informations, consultez la rubrique Lorsque j'interroge des données CSV dans Athena, je reçois l'erreur HIVE_BAD_DATA : erreur d'analyse de la valeur du champ dans le Centre de connaissances AWS.

  • Pour les colonnes spécifiées avec le type de données timestamp dans votre instruction CREATE TABLE, il reconnaît les données TIMESTAMP si elles sont spécifiées dans le format numérique UNIX en millisecondes, par exemple, 1579059880000. Pour obtenir un exemple, consultez Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format.

    • Le SerDe Open CSV ne prend pas en charge TIMESTAMP dans le format java.sql.Timestamp compatible avec JDBC, comme "YYYY-MM-DD HH:MM:SS.fffffffff" (précision de neuf décimales).

  • Pour les colonnes spécifiées avec le type de données DATE dans votre instruction CREATE TABLE, il reconnaît les valeurs comme des dates si les valeurs représentent le nombre de jours qui se sont écoulés depuis le 1er janvier 1970. Par exemple, la valeur 18276 dans une colonne avec le type de données date est rendue comme 2020-01-15 lorsqu'elle est interrogée. Dans ce format UNIX, chaque jour est considéré comme ayant 86 400 secondes.

  • Pour mieux convertir les colonnes en le type souhaité dans une table, vous pouvez créer une vue sur la table et utiliser CAST pour la conversion.

Exemples

Exemple : Interrogation de données CSV simples

L’exemple suivant suppose que vous avez enregistré des données CSV dans s3://amzn-s3-demo-bucket/mycsv/ avec le contenu suivant :

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

Utilisez une instruction CREATE TABLE pour créer une table Athena basée sur les données. Référencez OpenCSVSerde (notez la lettre « d » minuscule) après ROW FORMAT SERDE et spécifiez le caractère de séparation, le caractère de guillemet et le caractère d’échappement dans WITH SERDEPROPERTIES, comme illustré dans l’exemple suivant.

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

Interrogez toutes les valeurs de la table :

SELECT * FROM myopencsvtable;

La requête renvoie les valeurs suivantes :

col1     col2    col3    col4
-----------------------------
a1       a2      a3      a4
1        2       abc     def
a        a1      abc3    ab4
Exemple : Utilisation des types TIMESTAMP et DATE spécifiés au format numérique UNIX

Considérez les trois colonnes suivantes de données séparées par des virgules. Les valeurs de chaque colonne sont placées entre guillemets.

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

L'instruction suivante crée une table dans Athena à partir de l'emplacement du compartiment Simple Storage Service (Amazon S3) spécifié.

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'

Ensuite, exécutez la requête suivante : 

SELECT * FROM testtimestamp1

La requête renvoie le résultat suivant, affichant les données de date et d'heure :

profile_id                                                        creationdate     creationdatetime
unixvalue creationdate 18276 creationdatetime 1579146280000       2020-01-15       2020-01-15 03:44:40.000
Exemple : Échappement de \t ou \n

Examinez les données de test suivantes :

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

L'instruction suivante crée une table dans Athena, en spécifiant 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/'

Ensuite, exécutez la requête suivante : 

SELECT * FROM test1;

Elle renvoie ce résultat, en échappant correctement \t ou \n :

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