View a markdown version of this page

Tutorial: como consultar dados do Amazon S3 - Amazon Aurora
Pré-requisitosEtapa 1: criar uma política e um perfil do IAMEtapa 2: criar o cluster de banco de dadosEtapa 3: associar o perfil do IAMEtapa 4: Configurar o endpoint da VPC de gateway do S3Etapa 5: conectar-se e instalar a extensãoEtapa 6: criar uma tabela estrangeiraEtapa 7: executar a primeira consultaEtapa 8: fazer uma junção com uma tabela localEtapa 9: materializar os resultadosEtapa 10: verificar o cache e o desempenhoFazer a limpeza.Próximas etapas

Tutorial: como consultar dados do Amazon S3

Este tutorial orienta você na configuração do recurso de analytics do zero e na execução da primeira consulta de analytics em dados Parquet no Amazon S3. Ao final, o cluster de banco de dados do Aurora PostgreSQL estará consultando dados do Amazon S3 por meio de SQL padrão.

Pré-requisitos

Antes de começar, você deve ter o seguinte:

  • Uma conta da AWS com permissões para criar perfis do IAM, clusters do Amazon RDS e endpoints da VPC.

  • A AWS CLI instalada e configurada.

  • Uma VPC com pelo menos duas sub-redes em diferentes zonas de disponibilidade, um grupo de sub-redes de banco de dados e um grupo de segurança para o Aurora.

  • Arquivos de dados Parquet em um bucket do Amazon S3. Se você ainda não tiver dados, poderá carregar um arquivo de exemplo (este tutorial usa um conjunto de dados de pedidos como exemplo).

Etapa 1: criar uma política e um perfil do IAM

Crie uma política do IAM que conceda à extensão acesso de leitura ao bucket do Amazon S3 e, em seguida, crie um perfil e associe a política a ele.

Como criar a política do IAM:

aws iam create-policy \
  --policy-name AuroraAnalyticsReadAccess \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Sid": "S3Access",
        "Effect": "Allow",
        "Action": ["s3:ListBucket", "s3:GetObject", "s3:GetBucketLocation"],
        "Resource": ["arn:aws:s3:::your-data-bucket", "arn:aws:s3:::your-data-bucket/*"]
      }
    ]
  }'

Como criar o perfil do IAM e associar a política a ele:

aws iam create-role \
  --role-name AuroraAnalyticsRole \
  --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": {"Service": "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
      }
    ]
  }'

aws iam attach-role-policy \
  --role-name AuroraAnalyticsRole \
  --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess

Etapa 2: criar o cluster de banco de dados do Aurora PostgreSQL

Crie um grupo de parâmetros do cluster de banco de dados e, em seguida, crie o cluster e uma instância de banco de dados.

Para criar o grupo de parâmetros com o recurso habilitado:

aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-analytics-params \
  --db-parameter-group-family aurora-postgresql17 \
  --description "Parameter group with aurora_analytics enabled"

aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-analytics-params \
  --parameters "ParameterName=aurora_analytics.enabled,ParameterValue=true,ApplyMethod=immediate"

Para criar o cluster de banco de dados:

aws rds create-db-cluster \
  --db-cluster-identifier my-analytics-cluster \
  --engine aurora-postgresql \
  --engine-version 17.11 \
  --master-username postgres \
  --master-user-password <password> \
  --db-cluster-parameter-group-name my-analytics-params \
  --db-subnet-group-name <subnet-group> \
  --vpc-security-group-ids <security-group-id>

Para criar uma instância de banco de dados:

aws rds create-db-instance \
  --db-instance-identifier my-analytics-instance-1 \
  --db-cluster-identifier my-analytics-cluster \
  --db-instance-class db.r8gd.xlarge \
  --engine aurora-postgresql

Aguarde até que a instância de banco de dados fique disponível.

aws rds wait db-instance-available \
  --db-instance-identifier my-analytics-instance-1

Etapa 3: associar o perfil do IAM ao cluster de banco de dados

aws rds add-role-to-db-cluster \
  --db-cluster-identifier my-analytics-cluster \
  --role-arn arn:aws:iam::<account-id>:role/AuroraAnalyticsRole \
  --feature-name AuroraAnalytics

Etapa 4: configurar o endpoint da VPC de gateway do Amazon S3

Se o cluster de banco de dados do Aurora estiver em uma sub-rede privada, crie um endpoint de gateway do Amazon S3:

aws ec2 create-vpc-endpoint \
  --vpc-id <vpc-id> \
  --service-name com.amazonaws.<region>.s3 \
  --route-table-ids <route-table-id>

Etapa 5: conectar-se e instalar a extensão

Conecte-se ao cluster de banco de dados do Aurora PostgreSQL usando psql:

psql --host=my-analytics-cluster.<region>.rds.amazonaws.com \
     --port=5432 --username=postgres --password

Instale a extensão aurora_analytics:

CREATE EXTENSION aurora_analytics;

Verifique a instalação:

SELECT extname, extversion FROM pg_extension WHERE extname = 'aurora_analytics';

Saída esperada:

     extname      | extversion
------------------+------------
 aurora_analytics | 1.0

Verifique se o servidor estrangeiro foi criado:

SELECT srvname FROM pg_foreign_server WHERE srvname = 'aurora_analytics_server';

Etapa 6: criar uma tabela estrangeira

Crie uma tabela estrangeira que aponte para os dados Parquet no Amazon S3. O uso de parênteses vazios () permite que o Aurora PostgreSQL infira automaticamente o esquema com base nos metadados Parquet.

CREATE FOREIGN TABLE ft_orders ()
SERVER aurora_analytics_server
OPTIONS (
    location 's3://your-data-bucket/data/orders/',
    format 'parquet',
    region 'us-east-1'
);

Verifique o esquema inferido:

\d ft_orders

Etapa 7: executar a primeira consulta

-- Count all rows
SELECT COUNT(*) FROM ft_orders;

-- Aggregation with filter
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM ft_orders
WHERE order_date >= '2024-01-01'
GROUP BY status
ORDER BY total_revenue DESC;

Etapa 8: fazer uma junção com uma tabela local do PostgreSQL

Crie uma tabela local e faça uma junção com a tabela estrangeira:

-- Create a local dimension table
CREATE TABLE customer_segments (
    customer_id BIGINT PRIMARY KEY,
    segment TEXT,
    region TEXT
);

INSERT INTO customer_segments VALUES
    (1, 'VIP', 'US-West'),
    (2, 'Premium', 'US-East'),
    (3, 'Regular', 'EU-West');

-- Join local table with S3 data
SELECT
    cs.segment,
    cs.region,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_revenue
FROM ft_orders o
JOIN customer_segments cs ON o.customer_id = cs.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY cs.segment, cs.region
ORDER BY total_revenue DESC;

Etapa 9: materializar os resultados no PostgreSQL

Armazene os resultados de consultas do Amazon S3 em uma tabela local do PostgreSQL para agilizar os acessos repetidos:

CREATE TABLE monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM ft_orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Query the materialized result (fast, local PostgreSQL)
SELECT * FROM monthly_revenue;

Etapa 10: verificar o cache e o desempenho

Após executar as consultas, verifique se o cache está funcionando:

-- Check cache size (should be non-zero after queries)
SELECT pg_size_pretty(aurora_analytics_cache_size()) AS cache_size;

-- Check per-query statistics
SELECT query,
       pg_size_pretty(analytics_cache_hit_bytes) AS cache_hits,
       pg_size_pretty(analytics_remote_read_bytes) AS s3_reads
FROM aurora_analytics_stat_statements()
WHERE analytics_cache_hit_bytes > 0 OR analytics_remote_read_bytes > 0;

Execute a mesma consulta novamente e observe a melhora: na segunda execução, os dados são lidos do cache, em vez de serem obtidos do Amazon S3.

Fazer a limpeza.

Para evitar cobranças contínuas, exclua os recursos que você criou:

-- Drop the foreign table
DROP FOREIGN TABLE ft_orders;
DROP TABLE customer_segments;
DROP TABLE monthly_revenue;
aws rds delete-db-instance --db-instance-identifier my-analytics-instance-1 --skip-final-snapshot
aws rds delete-db-cluster --db-cluster-identifier my-analytics-cluster --skip-final-snapshot
aws iam detach-role-policy --role-name AuroraAnalyticsRole --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess
aws iam delete-role --role-name AuroraAnalyticsRole
aws iam delete-policy --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess

Próximas etapas

Agora que concluiu o tutorial, examine os seguintes tópicos: