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 :

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 :

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 :

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 :