Comment résoudre les erreurs de chargement de données lors de l'utilisation de la commande COPY dans Amazon Redshift ?

Dernière mise à jour : 01/07/2021

J'ai essayé d'utiliser la commande COPY pour charger un fichier plat. Toutefois, je rencontre des problèmes ou des erreurs de chargement de données dans Amazon Redshift. Comment puis-je résoudre ce problème ?

Brève description

Utilisez la table STL_LOAD_ERRORS pour identifier les erreurs de chargement de données qui se produisent pendant le chargement d'un fichier plat. La table STL_LOAD_ERRORS peut vous aider à suivre la progression d'un chargement de données, en enregistrant toute défaillance ou erreur durant le processus. Une fois que vous avez résolu le problème identifié, rechargez les données dans le fichier plat tout en utilisant la commande COPY.

Conseil : Si vous utilisez la commande COPY pour charger un fichier plat au format Parquet, vous pouvez également utiliser la table SVL_S3LOG. La table SVL_S3LOG peut être utilisée pour identifier toute erreur de chargement de données.

Solution

Remarque : les étapes suivantes utilisent un exemple de jeu de données comprenant des villes et des lieux.

1.    Vérifiez les données de votre exemple de fichier plat afin de confirmer que les données source sont valides.

Par exemple :

7|BMO Field|Toronto|ON|0
16|TD Garden|Boston|MA|0
23|The Palace of Auburn Hills|Auburn Hills|MI|0
28|American Airlines Arena|Miami|FL|0
37|Staples Center|Los Angeles|CA|0
42|FedExForum|Memphis|TN|0
52|PNC Arena|Raleigh|NC  ,25   |0
59|Scotiabank Saddledome|Calgary|AB|0
66|SAP Center|San Jose|CA|0
73|Heinz Field|Pittsburgh|PA|65050

Dans cet exemple de fichier demo.txt, cinq champs sont utilisés, séparés par un caractère à barre verticale. Pour plus d'informations, consultez Charger la table LISTING à partir d'un fichier séparé par une barre verticale (délimiteur par défaut).

2.    Ouvrez la console Amazon Redshift.

3.    Créez un exemple de table à l'aide de la DDL suivante :

CREATE TABLE VENUE1(
VENUEID SMALLINT,
VENUENAME VARCHAR(100),
VENUECITY VARCHAR(30),
VENUESTATE CHAR(2),
VENUESEATS INTEGER
) DISTSTYLE EVEN;

4.    Créez une vue pour prévisualiser les colonnes pertinentes à partir de la table STL_LOAD_ERRORS :

create view loadview as
(select distinct tbl, trim(name) as table_name, query, starttime,
trim(filename) as input, line_number, colname, err_code,
trim(err_reason) as reason
from stl_load_errors sl, stv_tbl_perm sp
where sl.tbl = sp.id);

Cette vue peut vous aider à identifier la cause de l'erreur de chargement des données.

5.    Utilisez la commande COPY pour charger les données :

copy Demo
from 's3://your_S3_bucket/venue/'
iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3'
delimiter '|' ;

Remarque : Remplacez your_S3_bucket par le nom de votre compartiment S3. Remplacez ensuite arn:aws:iam::123456789012:role/redshiftcopyfroms3 par l'Amazon Resource Name (ARN) de votre rôle AWS Identity and Access Management (IAM). Ce rôle IAM doit pouvoir accéder aux données de votre compartiment S3. Pour plus d'informations, consultez Paramètres.

6.    Interrogez la vue de chargement pour afficher et vérifier les informations relatives au chargement des erreurs de la table :

testdb=# select * from loadview where table_name='venue1';
tbl | 265190
table_name | venue1
query | 5790
starttime | 2017-07-03 11:54:22.864584
input | s3://
your_S3_bucket/venue/venue_pipe0000_part_00
line_number | 7
colname | venuestate
err_code | 1204
reason | Char length exceeds DDL length

Dans cet exemple, l'exception est générée par la valeur de longueur, qui doit être ajoutée à la colonne venuestate. La valeur (NC, 25 |) est plus longue que la durée définie dans la DDL VENUESTATE CHAR (2).

Vous pouvez résoudre cette exception de deux manières différentes :

  • Si les données doivent dépasser la longueur définie de la colonne, vérifiez et mettez donc à jour la définition de la table pour modifier la longueur de la colonne.
    -ou-
  • Si les données ne sont pas correctement formatées ou transformées, modifiez donc les données du fichier pour utiliser la valeur correcte.

Le résultat de cette requête contient les informations importantes suivantes :

  • Le fichier à l'origine de l'erreur
  • La colonne à l'origine de l'erreur.
  • Le numéro de ligne dans le fichier en entrée.
  • La raison de l'exception.

7.    Modifiez les données de votre fichier de chargement pour utiliser les valeurs correctes (la longueur doit correspondre à la longueur de colonne définie) :

7|BMO Field|Toronto|ON|0
16|TD Garden|Boston|MA|0
23|The Palace of Auburn Hills|Auburn Hills|MI|0
28|American Airlines Arena|Miami|FL|0
37|Staples Center|Los Angeles|CA|0
42|FedExForum|Memphis|TN|0
52|PNC Arena|Raleigh|NC|0
59|Scotiabank Saddledome|Calgary|AB|0
66|SAP Center|San Jose|CA|0
73|Heinz Field|Pittsburgh|PA|65050

8.    Rechargez le chargement de données :

testdb=# copy Demo
from 's3://your_S3_bucket/sales/'
iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3' delimiter '|' ;
INFO:  Load into table 'venue1' completed, 808 record(s) loaded successfully.

Remarque : La table STL_LOAD_ERRORS ne peut contenir qu'un nombre limité de journaux (généralement pendant environ 4 à 5 jours). Les utilisateurs standard peuvent aussi uniquement afficher leurs propres données lors de l'interrogation de la table STL_LOAD_ERRORS. Pour afficher toutes les données de la table, vous devez être un super-utilisateur.