Ejemplos de uso de una consulta federada

En los siguientes ejemplos, se muestra cómo ejecutar una consulta federada. Ejecute la SQL con su cliente SQL conectado a la base de datos de Amazon Redshift.

Ejemplo de uso de una consulta federada con PostgreSQL

En el siguiente ejemplo, se muestra cómo configurar una consulta federada que referencie una base de datos de Amazon Redshift, una base de datos de Aurora PostgreSQL y Amazon S3. En este ejemplo, se muestra cómo funcionan las consultas federadas. Para ejecutarla en su propio entorno, cámbiela para que se ajuste a su entorno. Para obtener información sobre los requisitos previos para ello, consulte Introducción al uso de consultas federadas en PostgreSQL.

Cree un esquema externo que referencie una base de datos de 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';

Cree otro esquema externo que referencie Amazon S3, que usa Amazon Redshift Spectrum. Además, conceder permiso para utilizar el esquema a 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;

Muestre el recuento de filas de la tabla de Amazon Redshift.

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

Muestre el recuento de filas de la tabla de Aurora PostgreSQL.

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

Muestre el recuento de filas en Amazon S3.

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

Cree una vista de las tablas de Amazon Redshift, Aurora PostgreSQL y Amazon S3. Esta vista se utiliza para ejecutar las consultas federadas.

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;

Mostrar el recuento de filas en la vista lineitem_all con un predicado para limitar los resultados.

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

Averiguar cuántas ventas de un artículo ha habido en enero de cada año.

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

Ejemplo de uso de un nombre compuesto por mayúsculas y minúsculas

Para consultar una base de datos PostgreSQL remota compatible que tenga un nombre de base de datos, esquema, tabla o columna compuesto por mayúsculas y minúsculas, establezca enable_case_sensitive_identifier como true. Para obtener más información sobre este parámetro de sesión, consulte enable_case_sensitive_identifier.

SET enable_case_sensitive_identifier TO TRUE;

Por lo general, los nombres de bases de datos y esquemas se escriben en minúsculas. En el siguiente ejemplo, se muestra cómo puede conectarse a una base de datos PostgreSQL remota compatible que tenga nombres escritos en minúsculas para la base de datos y el esquema, por un lado, y nombres compuestos por mayúsculas y minúsculas para la tabla y la columna, por el otro.

Cree un esquema externo que referencie una base de datos de Aurora PostgreSQL con el nombre de la base de datos (dblower) y del esquema (schemalower) en minúsculas.

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';

En la sesión en la que se ejecuta la consulta, establezca enable_case_sensitive_identifier como true.

SET enable_case_sensitive_identifier TO TRUE;

Ejecute una consulta federada para seleccionar todos los datos de la base de datos PostgreSQL. La tabla (MixedCaseTab) y la columna (MixedCaseName) tienen nombres compuestos por mayúsculas y minúsculas. El resultado es una fila (Harry).

select * from apg_lower."MixedCaseTab";

 MixedCaseName
-------
 Harry

En el siguiente ejemplo, se muestra cómo puede conectarse a una base de datos PostgreSQL remota compatible que tenga nombres de base de datos, esquema, tabla y columna compuestos por mayúsculas y minúsculas.

Establezca enable_case_sensitive_identifier como true antes de crear el esquema externo. Si enable_case_sensitive_identifier no se establece como true antes de crear el esquema externo, se produce un error del tipo que indica que la base de datos no existe.

Cree un esquema externo que referencie una base de datos de Aurora PostgreSQL con el nombre de la base de datos (UpperDB) y del esquema (UpperSchema) compuesto por mayúsculas y minúsculas.

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';

Ejecute una consulta federada para seleccionar todos los datos de la base de datos PostgreSQL. La tabla (MixedCaseTab) y la columna (MixedCaseName) tienen nombres compuestos por mayúsculas y minúsculas. El resultado es una fila (Harry).

select * from apg_upper."MixedCaseTab";

 MixedCaseName
-------
 Harry

Ejemplo de uso de una consulta federada con MySQL

En el siguiente ejemplo, se muestra cómo configurar una consulta federada que referencie una base de datos de Aurora MySQL. En este ejemplo se muestra cómo funcionan las consultas federadas. Para ejecutarla en su propio entorno, cámbiela para que se ajuste a su entorno. Para obtener información sobre los requisitos previos para ello, consulte Introducción al uso de consultas federadas en MySQL.

Este ejemplo depende de los siguientes requisitos previos:

• Un secreto que se configuró en Secrets Manager para la base de datos de Aurora MySQL. Se referencia este secreto en las políticas de acceso y los roles de IAM. Para obtener más información, consulte Creación de un secreto y rol de IAM para utilizar consultas federadas.

• Un grupo de seguridad que se configura a partir de la vinculación de Amazon Redshift y Aurora MySQL.

Cree un esquema externo que referencie una base de datos de 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';

Ejecute una expresión SQL SELECT de ejemplo de la tabla de Aurora MySQL para mostrar una fila de la tabla de empleados de Aurora MySQL.

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