Utilisation de COPY pour charger des données dans des colonnes SUPER - Amazon Redshift
Copie de données à partir de JSON et AvroCopie de données à partir de texte et CSVCopie de données à partir de Parquet et ORC au format colonne

Amazon Redshift ne prendra plus en charge la création de nouvelles fonctions Python définies par l’utilisateur à compter du 1er novembre 2025. Si vous souhaitez utiliser des fonctions Python définies par l’utilisateur, créez-les avant cette date. Les fonctions Python définies par l’utilisateur existantes continueront de fonctionner normalement. Pour plus d’informations, consultez le billet de blog .

Utilisation de COPY pour charger des données dans des colonnes SUPER

Dans les sections suivantes, vous pouvez découvrir les différentes façons d’utiliser l’instruction COPY pour charger des données JSON dans Amazon Redshift. Pour plus d’informations sur les paramètres de format de données utilisés par Amazon Redshift pour analyser le JSON dans les commandes COPY, lisez la description du paramètre JSON format for COPY dans Paramètres du format de données.

Copie de données à partir de JSON et Avro

Amazon Redshift fournit les méthodes suivantes pour ingérer un document JSON en utilisant COPY, même avec une structure JSON totalement ou partiellement inconnue :

  • Stocker les données dérivées d’un document JSON dans une seule colonne de données SUPER en choisissant l’option noshred. Cette méthode est utile lorsque le schéma n’est pas connu ou est censé changer. Ainsi, cette méthode facilite le stockage du tuple entier dans une seule colonne SUPER.

  • Déchiquetez le document JSON en plusieurs colonnes Amazon Redshift en choisissant l’option auto ou jsonpaths. Les attributs peuvent être des scalaires Amazon Redshift ou des valeurs SUPER.

Vous pouvez utiliser ces options avec les formats JSON ou Avro. Pour plus d’informations sur les options JSON telles que noshred, auto et jsonpaths, consultez JSON format for COPY.

La taille maximale d’un objet JSON dans Amazon Redshift est de 4 Mo, ce qui s’applique avant tout déchiquetage ou analyse.

Méthode 1 : copie d’un document JSON dans une seule colonne de données SUPER à l’aide de noshred

Vous pouvez copier des documents JSON entiers dans des colonnes de données SUPER uniques à l’aide de l’option noshred de la commande COPY. Prenez l’exemple suivant :

  1. Créez une table avec une seule colonne de données SUPER.

    CREATE TABLE region_nations_noshred (rdata SUPER);

  2. Copiez les données d’Amazon S3 dans la colonne de données SUPER unique. Pour ingérer les données source JSON dans une seule colonne de données SUPER, spécifiez l’option noshred dans la clause FORMAT JSON.

    COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'noshred';

    Une fois que COPY a réussi à ingérer le JSON, votre table a une colonne de données SUPER rdata qui contient les données de l’objet JSON entier. Les données ingérées conservent toutes les propriétés de la hiérarchie JSON. Toutefois, les feuilles sont converties en types scalaires Amazon Redshift pour un traitement efficace des requêtes.

  3. Utilisez la requête suivante pour récupérer la chaîne JSON originale.

    SELECT rdata FROM region_nations_noshred;

    Lorsque Amazon Redshift génère une colonne de données SUPER, elle devient accessible à l’aide de JDBC en tant que chaîne de caractères par sérialisation JSON. Pour plus d’informations, consultez Sérialisation de JSON imbriqué complexe.

Méthode 2 : copier un document JSON dans plusieurs colonnes de données SUPER

Vous pouvez fragmenter un document JSON en plusieurs colonnes, qui peuvent être des colonnes de données SUPER ou des types scalaires Amazon Redshift. Amazon Redshift répartit différentes portions de l’objet JSON dans différentes colonnes. Prenez l’exemple suivant :

  1. Créez une table comportant plusieurs colonnes pour contenir le JSON déchiqueté.

    CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );

  2. Pour copier le JSON dans la table region_nations, spécifiez l’option AUTO dans la clause FORMAT JSON pour diviser la valeur JSON sur plusieurs colonnes. COPY fait correspondre les attributs JSON de premier niveau avec les noms de colonnes et permet d’ingérer les valeurs imbriquées en tant que valeurs SUPER, comme les tableaux et les objets JSON. 

    COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

    Lorsque les noms d’attributs JSON sont en majuscules et casse mixtes, spécifiez l’option auto ignorecase dans la clause FORMAT JSON. Pour plus d’informations sur la commande COPY, consultez Charger des données JSON à l’aide de l’option ’auto ignorecase’.

Dans certains cas, il existe un décalage entre les noms de colonnes et les attributs JSON ou l’attribut à charger est imbriqué sur plus d’un niveau. Si c’est le cas, utilisez un fichier jsonpaths pour mapper manuellement les attributs JSON aux colonnes Amazon Redshift. Prenez l’exemple suivant :

  1. Créez une table comportant plusieurs colonnes pour contenir le JSON déchiqueté.

    CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );

  2. Dans ce cas, les noms de colonnes ne correspondent pas aux attributs JSON. Vous pouvez créer un fichier jsonpaths qui fait correspondre les chemins d’accès des attributs aux colonnes de la table par leur position dans le tableau jsonpaths comme suit :

    {"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$.r_comment",
       "$.r_nations
    ]
}

  3. Utilisez l’emplacement du fichier jsonpaths comme argument de l’option FORMAT JSON dans COPY.

    COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

  4. Utilisez la requête suivante pour accéder à la table qui présente des données réparties sur plusieurs colonnes. Les colonnes de données SUPER sont imprimées en utilisant le format JSON.

    SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

Les fichiers jsonpaths mappent les champs du document JSON aux colonnes de la table. Vous pouvez extraire des colonnes supplémentaires, telles que des clés de distribution et de tri, tout en chargeant le document complet en tant que colonne SUPER. La requête suivante charge le document complet dans la colonne nations. La colonne name est la clé de tri et la colonne regionkey est la clé de distribution. Prenez l’exemple suivant :

  1. Créez une table comportant plusieurs colonnes pour contenir le JSON déchiqueté.

    CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

  2. Mappez la racine jsonpath « $ » à la racine du document comme suit :

    {"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$"
    ]
}

  3. Utilisez l’emplacement du fichier jsonpaths comme argument de l’option FORMAT JSON dans COPY.

    COPY nations_sorted FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_sorted_jsonpaths.json';

Pour plus d’informations sur l’utilisation de jsonpaths, consultez Exécution de la commande COPY à partir du format JSON.

Copie de données à partir de texte et CSV

Amazon Redshift représente des colonnes SUPER au format texte et CSV en tant que JSON sérialisés. Un formatage JSON valide est requis pour que les colonnes SUPER soient chargées avec les informations type correctes. Supprimez les objets, les tableaux, les nombres, les valeurs booléennes et les valeurs null. Placez les valeurs de chaîne entre guillemets. Les colonnes SUPER utilisent des règles d’échappement standard pour les formats texte et CSV.

Lors de la copie à partir de CSV, les délimiteurs sont échappés conformément à la norme CSV. Prenez l’exemple suivant : 

CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );
 
 COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT CSV;

Lors de la copie à partir du format texte, si le délimiteur choisi peut également apparaître dans un champ SUPER, utilisez l’option ESCAPE pour les instructions COPY et UNLOAD. Prenez l’exemple suivant :

CREATE TABLE region_nations
(
 r_regionkey smallint
 ,r_name varchar
 ,r_comment varchar
 ,r_nations super
 );
 
COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
DELIMITER ','
ESCAPE;

Copie de données à partir de Parquet et ORC au format colonne

Si vos données semi-structurées ou imbriquées sont déjà disponibles au format Apache Parquet ou Apache ORC, vous pouvez utiliser l’instruction COPY pour ingérer des données dans Amazon Redshift.

La structure de la table Amazon Redshift doit correspondre au nombre de colonnes et aux types de données de colonne des fichiers Parquet ou ORC. En spécifiant SERIALIZETOJSON dans l’instruction COPY, vous pouvez charger n’importe quel type de colonne dans le fichier qui s’aligne sur une colonne SUPER de la table en tant que SUPER. Cela inclut les types de structure et de tableau.

L’exemple suivant utilise un format Parquet :

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT PARQUET SERIALIZETOJSON;

L’exemple suivant utilise un format ORC.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT ORC SERIALIZETOJSON;

Lorsque les attributs des types de données de date ou d’heure sont en ORC, Amazon Redshift les convertit en varchar lors de leur encodage en SUPER.