Exemplo de uso de uma consulta federada

Os exemplos a seguir mostram como executar uma consulta federada. Execute o SQL usando o cliente SQL conectado ao banco de dados do Amazon Redshift.

Exemplo de uso de uma consulta federada no PostgreSQL

O exemplo a seguir mostra como configurar uma consulta federada que faz referência a um banco de dados do Amazon Redshift, um banco de dados do Aurora PostgreSQL e Amazon S3. Este exemplo ilustra o funcionamento das consultas federadas. Para executá-lo em seu próprio ambiente, altere-o para se adequar ao ambiente. Para obter os pré-requisitos para fazer isso, consulte Conceitos básicos do uso de consultas federadas no PostgreSQL.

Crie um esquema externo que faça referência a um banco de dados do 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';

Crie outro esquema externo que faça referência ao Amazon S3, que usa o Amazon Redshift Spectrum. Além disso, conceda permissão para usar o esquema no modo 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;

Mostra a contagem de linhas na tabela do Amazon Redshift.

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

Mostra a contagem de linhas na tabela do Aurora PostgreSQL.

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

Mostra a contagem de linhas no Amazon S3.

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

Crie uma visualização das tabelas do Amazon Redshift, do Aurora PostgreSQL e do Amazon S3. Essa visualização será usada para executar a consulta federada.

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 a contagem de linhas na visualização lineitem_all com um predicado para limitar os resultados.

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

Descubra quantas vezes um item vendeu em janeiro de cada ano.

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

Exemplo de uso de um nome com maiúsculas e minúsculas

Para consultar um banco de dados remoto PostgreSQL suportado que tenha um nome com maiúsculas e minúsculas de um banco de dados, esquema, tabela ou coluna, defina enable_case_sensitive_identifier como true. Para obter mais informações sobre este parâmetro de sessão, consulte enable_case_sensitive_identifier.

SET enable_case_sensitive_identifier TO TRUE;

Normalmente, os nomes do banco de dados e do esquema estão em minúsculas. O exemplo a seguir mostra como você pode se conectar a um banco de dados remoto PostgreSQL suportado que tenha nomes minúsculos para banco de dados e esquema e nomes com maiúsculas e minúsculas para tabela e coluna.

Crie um esquema externo que faça referência a um banco de dados do Aurora PostgreSQL que tenha um nome do banco de dados minúsculas (dblower) e nome do esquema em minúsculas (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';

Na sessão em que a consulta é executada, defina enable_case_sensitive_identifier como true.

SET enable_case_sensitive_identifier TO TRUE;

Execute uma consulta federada para selecionar todos os dados do banco de dados do PostgreSQL. A tabela (MixedCaseTab) e coluna (MixedCaseName) têm nomes com maiúsculas e minúsculas. O resultado é uma linha (Harry).

select * from apg_lower."MixedCaseTab";

 MixedCaseName
-------
 Harry

O exemplo a seguir mostra como você pode se conectar a um banco de dados remoto PostgreSQL compatível com um nome com maiúsculas e minúsculas para o banco de dados, esquema, tabela e coluna.

Defina enable_case_sensitive_identifier como true antes de criar o esquema externo. Se enable_case_sensitive_identifier não está definido como true antes de criar o esquema externo, ocorre um erro de banco de dados inexistente.

Crie um esquema externo que faça referência a um banco de dados Aurora PostgreSQL que tenha um banco de dados com maiúsculas e minúsculas (UpperDB) e um nome de esquema (UpperSchema).

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

Execute uma consulta federada para selecionar todos os dados do banco de dados do PostgreSQL. A tabela (MixedCaseTab) e coluna (MixedCaseName) têm nomes com maiúsculas e minúsculas. O resultado é uma linha (Harry).

select * from apg_upper."MixedCaseTab";

 MixedCaseName
-------
 Harry

Exemplo de uso de uma consulta federada com MySQL

O exemplo a seguir mostra como configurar uma consulta federada que faz referência a um banco de dados do Aurora MySQL. Este exemplo ilustra como o funcionamento das consultas federadas. Para executá-lo em seu próprio ambiente, altere-o para se adequar ao ambiente. Para obter os pré-requisitos para fazer isso, consulte Conceitos básicos do uso de consultas federadas no MySQL.

Este exemplo depende dos seguintes pré-requisitos:

• Um segredo que foi configurado no Secrets Manager para o banco de dados do MySQL do Aurora. Este segredo é referenciado nas políticas e funções de acesso do IAM. Para obter mais informações, consulte Criar um segredo e uma função do IAM para usar consultas federadas.

• Um grupo de segurança configurado para vincular o Amazon Redshift e o Aurora MySQL.

Crie um esquema externo que faça referência a um banco de dados do 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';

Execute um exemplo de seleção SQL da tabela do Aurora MySQL para exibir uma linha da tabela de funcionários no Aurora MySQL.

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