Exemple d’utilisation d’une requête fédérée avec PostgreSQL Exemple d’utilisation d’un nom en casse mixte Exemple d’utilisation d’une requête fédérée avec MySQL

Exemples d’utilisation d’une requête fédérée

Les exemples suivants illustrent l’exécution d’une requête fédérée. Exécutez l’instruction SQL à l’aide de votre client SQL connecté à la base de données Amazon Redshift.

Exemple d’utilisation d’une requête fédérée avec PostgreSQL

L’exemple suivant montre comment configurer une requête fédérée qui référence une base de données Amazon Redshift, une base de données Aurora PostgreSQL et Amazon S3. Cet exemple illustre le fonctionnement des requêtes fédérées. Pour l’exécuter sur votre propre environnement, modifiez-la afin qu’elle s’adapte à votre environnement. Pour connaître les conditions préalables à cette fin, consultez Commencer à utiliser les requêtes fédérées vers PostgreSQL.

Créez un schéma externe qui référence une base de données Aurora PostgreSQL.


CREATE EXTERNAL SCHEMA apg
FROM POSTGRES
DATABASE 'database-1' SCHEMA 'myschema'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

Créez un autre schéma externe qui référence Amazon S3, qui utilise Amazon Redshift Spectrum. En outre, définissez l’autorisation d’utiliser le schéma sur public.


CREATE EXTERNAL SCHEMA s3 
FROM DATA CATALOG 
DATABASE 'default' REGION 'us-west-2' 
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3'; 

GRANT USAGE ON SCHEMA s3 TO public;

Affiche le nombre de lignes dans la table Amazon Redshift.


SELECT count(*) FROM public.lineitem;
            
  count
----------
25075099

Affiche le nombre de lignes dans la table Aurora PostgreSQL.


SELECT count(*) FROM apg.lineitem;
            
count
-------
11760

Affiche le nombre de lignes dans Amazon S3.


SELECT count(*) FROM s3.lineitem_1t_part;
            
   count
------------
6144008876

Créez une vue des tables depuis Amazon Redshift, Aurora PostgreSQL et Amazon S3. Cette vue est utilisée pour exécuter votre requête fédérée.


CREATE VIEW lineitem_all AS
  SELECT l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus,
         l_shipdate::date,l_commitdate::date,l_receiptdate::date, l_shipinstruct ,l_shipmode,l_comment 
  FROM s3.lineitem_1t_part 
  UNION ALL SELECT * FROM public.lineitem 
  UNION ALL SELECT * FROM apg.lineitem 
     with no schema binding;

Affiche le nombre de lignes dans la vue lineitem_all avec un prédicat pour limiter les résultats.


SELECT count(*) from lineitem_all WHERE l_quantity = 10;
               
   count
-----------
123373836

Découvrez le nombre de ventes pour un article en janvier de chaque année.



SELECT extract(year from l_shipdate) as year,
       extract(month from l_shipdate) as month,
       count(*) as orders
FROM lineitem_all
WHERE extract(month from l_shipdate) = 1
AND l_quantity < 2
GROUP BY 1,2
ORDER BY 1,2;

 year | month | orders
------+-------+---------
 1992 |     1 |  196019
 1993 |     1 | 1582034
 1994 |     1 | 1583181
 1995 |     1 | 1583919
 1996 |     1 | 1583622
 1997 |     1 | 1586541
 1998 |     1 | 1583198
 2016 |     1 |   15542
 2017 |     1 |   15414
 2018 |     1 |   15527
 2019 |     1 |     151

Exemple d’utilisation d’un nom en casse mixte

Pour interroger une base de données distante PostgreSQL prise en charge et dont le nom de base de données, de schéma, de table ou de colonne comporte une casse mixte, définissez enable_case_sensitive_identifier sur true. Pour plus d’informations sur le paramètre de cette session, consultez enable_case_sensitive_identifier.


SET enable_case_sensitive_identifier TO TRUE;

En général, les noms de base de données et de schéma sont en minuscules. L’exemple suivant montre comment vous pouvez vous connecter à une base de données distante PostgreSQL prise en charge qui a des noms en minuscules pour la base de données et le schéma et des noms en casse mixte pour la table et la colonne.

Créez un schéma externe qui référence une base de données Aurora PostgreSQL ayant un nom de base de données en minuscules (dblower) et un nom de schéma en minuscules (schemalower).


CREATE EXTERNAL SCHEMA apg_lower
FROM POSTGRES
DATABASE 'dblower' SCHEMA 'schemalower'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

Dans la session où la requête s’exécute, définissez enable_case_sensitive_identifier sur true.


SET enable_case_sensitive_identifier TO TRUE;

Exécutez une requête fédérée pour sélectionner toutes les données de la base de données PostgreSQL. La table (MixedCaseTab) et la colonne (MixedCaseName) ont des noms en casse mixte. Le résultat est une ligne (Harry).


select * from apg_lower."MixedCaseTab";



 MixedCaseName
-------
 Harry

L’exemple suivant montre comment vous pouvez vous connecter à une base de données distante PostgreSQL prise en charge, dont le nom de la base de données, du schéma, de la table et de la colonne sont en casse mixte.

Définissez enable_case_sensitive_identifier sur true avant de créer le schéma externe. Si enable_case_sensitive_identifier n’est pas défini sur true avant de créer le schéma externe, une erreur de non-existence de la base de données (database does not exist) se produit.

Créez un schéma externe qui fait référence à une base de données Aurora PostgreSQL dont le nom de base de données (UpperDB) et de schéma (UpperSchema) est en casse mixte.


CREATE EXTERNAL SCHEMA apg_upper
FROM POSTGRES
DATABASE 'UpperDB' SCHEMA 'UpperSchema'
URI 'endpoint to aurora hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';


select * from apg_upper."MixedCaseTab";



 MixedCaseName
-------
 Harry

Exemple d’utilisation d’une requête fédérée avec MySQL

L’exemple suivant montre comment configurer une requête fédérée qui référence une base de données Aurora MySQL. Cet exemple illustre le fonctionnement d’une requête fédérée. Pour l’exécuter sur votre propre environnement, modifiez-la afin qu’elle s’adapte à votre environnement. Pour connaître les conditions préalables à cette fin, consultez Commencer à utiliser des requêtes fédérées vers MySQL.

Cet exemple dépend des prérequis suivants :

Un secret qui a été configuré dans Secrets Manager pour la base de données Aurora MySQL. Ce secret est référencé dans les stratégies d’accès et les rôles IAM. Pour plus d’informations, consultez Création d’un secret et d’un rôle IAM pour utiliser des requêtes fédérées.
Groupe de sécurité qui est configuré pour lier Amazon Redshift et Aurora MySQL.

Créez un schéma externe qui référence une base de données Aurora MySQL.


CREATE EXTERNAL SCHEMA amysql
FROM MYSQL
DATABASE 'functional'
URI 'endpoint to remote hostname'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

Exécutez un exemple de sélection SQL de la table Aurora MySQL pour afficher une ligne de la table des employés dans Aurora MySQL.


SELECT level FROM amysql.employees LIMIT 1;
            
 level
-------
     8

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Création d’un secret et d’un rôle IAM

Différences de type de données