Écrit par Steph
le 16 décembre 2020
827 mots - 4 minutes de lecture

Comment charger facilement des données de S3 à PostgreSQL sur AWS

Le problème

J’utilise Redshift depuis pas mal de temps, et une des fonctionnalités super pratique, est de pouvoir insérer des données dans une table, depuis un fichier stocké sur s3, en une seule commande :

COPY table_name
FROM 's3://bucket-name/path/file.ext'
  iam_role 'arn:aws:iam::<aws-account-id>:role/<role-name>'
  region 'region-code';

Dernièrement, j’ai eu besoin de faire la même chose, mais sur une base PostgreSQL, hébergée sur RDS.
Et la bonne nouvelle c’est qu’AWS nous facilite un peu la vie.

La solution

L’installation

AWS pré-installe (entre autres) 2 extensions sur les EC2 RDS :

aws_commons
aws_s3

L’installation se fait en 1 seule commande, il faut juste s’assurer que le user utilisé soit superuser ou owner de la base de données:

CREATE EXTENSION aws_s3 CASCADE;

Cette commande installe à la fois aws_commons et aws_s3.
Okay, that was easy !

Bon, maintenant, il va falloir s’attaquer au rôle IAM, et là AWS nous met un peu des bâtons dans les roues.
L’installation de l’extension ajoute aussi silencieusement un rôle à l’instance, un rôle managé par AWS (et donc non modifiable). Comme on le voit sur l’image ci-dessus, le role est attaché à la fonction S3Import AWS Role Le problème, c’est qu'on ne peut affecter qu’un seul rôle à une fonction, et que ce rôle non modifiable par nos soins, n’a pas les droits de lecture sur le bucket S3 que l’on va utiliser (et c’est bien normal vu qu’AWS n’en a aucune idée).

Donc contrairement à ce qu’énonce AWS dans la doc, la policy va devoir contenir un tantinet plus d’accès, à savoir :

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "s3import",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::datadiscovery-dev-benchmark-leboncoin",
                "arn:aws:s3:::datadiscovery-dev-benchmark-leboncoin/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "rds:CrossRegionCommunication"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:AllocateAddress",
                "ec2:AssociateAddress",
                "ec2:AuthorizeSecurityGroupIngress",
                "ec2:CreateNetworkInterface",
                "ec2:CreateSecurityGroup",
                "ec2:DeleteNetworkInterface",
                "ec2:DeleteSecurityGroup",
                "ec2:DescribeAddresses",
                "ec2:DescribeAvailabilityZones",
                "ec2:DescribeCoipPools",
                "ec2:DescribeInternetGateways",
                "ec2:DescribeLocalGatewayRouteTables",
                "ec2:DescribeLocalGatewayRouteTableVpcAssociations",
                "ec2:DescribeLocalGateways",
                "ec2:DescribeSecurityGroups",
                "ec2:DescribeSubnets",
                "ec2:DescribeVpcAttribute",
                "ec2:DescribeVpcs",
                "ec2:DisassociateAddress",
                "ec2:ModifyNetworkInterfaceAttribute",
                "ec2:ModifyVpcEndpoint",
                "ec2:ReleaseAddress",
                "ec2:RevokeSecurityGroupIngress",
                "ec2:CreateVpcEndpoint",
                "ec2:DescribeVpcEndpoints",
                "ec2:DeleteVpcEndpoints",
                "ec2:AssignPrivateIpAddresses",
                "ec2:UnassignPrivateIpAddresses"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "sns:Publish"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup"
            ],
            "Resource": [
                "arn:aws:logs:*:*:log-group:/aws/rds/*",
                "arn:aws:logs:*:*:log-group:/aws/docdb/*",
                "arn:aws:logs:*:*:log-group:/aws/neptune/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents",
                "logs:DescribeLogStreams"
            ],
            "Resource": [
                "arn:aws:logs:*:*:log-group:/aws/rds/*:log-stream:*",
                "arn:aws:logs:*:*:log-group:/aws/docdb/*:log-stream:*",
                "arn:aws:logs:*:*:log-group:/aws/neptune/*:log-stream:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "kinesis:CreateStream",
                "kinesis:PutRecord",
                "kinesis:PutRecords",
                "kinesis:DescribeStream",
                "kinesis:SplitShard",
                "kinesis:MergeShards",
                "kinesis:DeleteStream",
                "kinesis:UpdateShardCount"
            ],
            "Resource": [
                "arn:aws:kinesis:*:*:stream/aws-rds-das-*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "cloudwatch:PutMetricData"
            ],
            "Resource": "*",
            "Condition": {
                "StringEquals": {
                    "cloudwatch:namespace": "AWS/RDS"
                }
            }
        }
    ]
}

Une fois crée, il suffira de :

détacher le rôle généré par AWS de la fonction s3Import ;
rattacher le rôle nouvellement créé à la fonction s3Import ;

Attention, le changement de rôle peut prendre quelques minutes avant d’être effectif, comme souvent, il ne faut pas se fier à ce qu’affichera la console AWS.

Et voilà, l’installation est à présent terminée.

L’utilisation

Pour copier un fichier CSV depuis S3, il suffira de procéder comme suit :

SELECT aws_s3.table_import_from_s3 (
   'nom-de-la-table-cible',
   'liste des colonnes séparées par des virgules, OPTIONNELLE',
   'DELIMITER '','' CSV HEADER',
     aws_commons.create_s3_uri(
           'nom-du-bucket',
           'prefix-du-ficher',
           'region'
       )
    );

Pour ce qui est de la liste de colonnes, si l’on fournit une chaîne vide, la commande tentera de mapper les colonnes du fichier aux colonnes de la table.

Les pièges

Contraintes

Alors par contre, il y’a des petites choses non supportées que perso, je n’ai pas vu dans la doc AWS :

les buckets incluant des points ‘.’ ne peuvent être utilisés, j’ai eu le message d’erreur suivant : S3 bucket names with a period (.) are not supported. Alors oui, ça devient la norme d’avoir des noms de bucket transposables en URL, mais n’ayant aucune contre-indication dans la documentation, nous avons été un peu surpris par ce message d’erreur ;
le ; ne peut être utilisé comme séparateur (PostgreSQL accepte tout séparateur du moment qu’il n’excède pas 1 byte)
à l’instar de la commande COPY de PostgreSQL seuls les formats text, CSV, ou binaires sont supportés

Gestion d’erreur

En cas d’erreur, le message renvoyé au client PG est est pour le moins obscure, en cas d’erreur de droits d’accès ou de bucket non supporté, on a le même message d’erreur :
Unable to generate pre-signed url, look at engine log for details

Il faut donc forcément aller fouiller les logs PG, depuis la console RDS pour avoir un message explicite et debugger efficacement.

Conclusion

Une fois les écueils d’installation et les limites comprises, l’utilisation est assez pratique, si l’on est sur RDS, il ne faut pas s’en priver.

Malheureusement (et ce n’est pas très étonnant) les deux extensions AWS ne peuvent être installées sur de simples EC2.
Évidemment, la vie serait trop belle si on pouvait exploiter les features proposées par AWS sur des machines qu’on manage par nous-même et en faire ce qu’on veut.

Alors il faut choisir :

soit on en bénéficie et on se restreint aux extensions (habilement) sélectionnées par AWS (bye bye timescale, citus ou son stockage colonne, … ),
soit on se débrouille avec d’autres solutions (j’en ferais surement un autre TIL :D).

← → Top