Tutorial: Abfragen von Amazon S3 S3-Daten

In diesem Tutorial erfahren Sie, wie Sie die Analysefunktion von Grund auf neu einrichten und Ihre erste analytische Abfrage für Parquet-Daten in Amazon S3 ausführen. Am Ende haben Sie einen Aurora PostgreSQL-DB-Cluster, der Amazon S3 S3-Daten über Standard-SQL abfragt.

Voraussetzungen

Stellen Sie vor dem Beginn sicher, dass Sie über das Folgende verfügen:

• Ein AWS Konto mit Berechtigungen zum Erstellen von IAM-Rollen, Amazon RDS-Clustern und VPC-Endpunkten.

• Die AWS CLI wurde installiert und konfiguriert.

• Eine VPC mit mindestens zwei Subnetzen in unterschiedlichen Availability Zones, einer DB-Subnetzgruppe und einer Sicherheitsgruppe für Aurora.

• Parquet-Datendateien in einem Amazon S3 S3-Bucket. Wenn Sie noch keine Daten haben, können Sie eine Beispieldatei hochladen (in diesem Tutorial wird ein Beispieldatensatz für Bestellungen verwendet).

Schritt 1: Erstellen Sie die IAM-Richtlinie und -Rolle

Erstellen Sie eine IAM-Richtlinie, die der Erweiterung Lesezugriff auf Ihren Amazon S3 S3-Bucket gewährt, erstellen Sie dann eine Rolle und hängen Sie die Richtlinie an.

So erstellen Sie die IAM-Richtlinie:

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/*"]
      }
    ]
  }'

Um die IAM-Rolle zu erstellen und die Richtlinie anzuhängen:

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

Schritt 2: Erstellen Sie den Aurora PostgreSQL-DB-Cluster

Erstellen Sie eine DB-Cluster-Parametergruppe und dann den DB-Cluster und eine DB-Instance.

So erstellen Sie die Parametergruppe mit aktivierter Funktion:

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"

Um den DB-Cluster zu erstellen:

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>

Um eine DB-Instance zu erstellen:

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

Warten Sie, bis die DB-Instance verfügbar ist:

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

Schritt 3: Hängen Sie die IAM-Rolle an den DB-Cluster an

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

Schritt 4: Den VPC-Endpunkt des Amazon S3 S3-Gateways einrichten

Wenn sich Ihr Aurora-DB-Cluster in einem privaten Subnetz befindet, erstellen Sie einen Amazon S3 S3-Gateway-Endpunkt:

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

Schritt 5: Connect und installieren Sie die Erweiterung

Stellen Sie mit psql Connect zu Ihrem Aurora PostgreSQL-DB-Cluster her:

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

Installieren Sie die aurora_analytics-Erweiterung.

CREATE EXTENSION aurora_analytics;

Überprüfen Sie die Installation:

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

Erwartete Ausgabe:

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

Stellen Sie sicher, dass der ausländische Server erstellt wurde:

SELECT srvname FROM pg_foreign_server WHERE srvname = 'aurora_analytics_server';

Schritt 6: Erstellen Sie eine Fremdtabelle

Erstellen Sie eine Fremdtabelle, die auf Ihre Parquet-Daten in Amazon S3 verweist. Durch die Verwendung leerer Klammern () kann Aurora PostgreSQL das Schema automatisch aus den Parquet-Metadaten ableiten:

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

Überprüfen Sie das abgeleitete Schema:

\d ft_orders

Schritt 7: Führen Sie Ihre erste Abfrage aus

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

Schritt 8: Mit einer lokalen PostgreSQL-Tabelle verbinden

Erstellen Sie eine lokale Tabelle und verknüpfen Sie sie mit Ihrer Fremdtabelle:

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

Schritt 9: Ergebnisse in PostgreSQL materialisieren

Speichern Sie Abfrageergebnisse von Amazon S3 als lokale PostgreSQL-Tabelle für schnellen wiederholten Zugriff:

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;

Schritt 10: Überprüfen Sie den Cache und die Leistung

Stellen Sie nach dem Ausführen von Abfragen sicher, dass der Cache funktioniert:

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

Führen Sie dieselbe Abfrage erneut aus und stellen Sie die Verbesserung fest: Die zweite Ausführung liest aus dem Cache statt aus Amazon S3.

Bereinigen

Um laufende Gebühren zu vermeiden, löschen Sie die Ressourcen, die Sie erstellt haben:

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

Nächste Schritte

Nachdem Sie das Tutorial abgeschlossen haben, sollten Sie sich mit den folgenden Themen befassen:

• Mit fremden Tabellen arbeiten: Erfahren Sie mehr über Iceberg-Tabellen, Zeitreisen mit Snapshot und Zeitstempel und Schemaverwaltung.

• Konfigurationsparameter: Passen Sie sich Ihrem query_mem Workload an.

• Bewährte Methoden: Optimieren Sie das Datenlayout, die Instanzgröße und die Parallelität.

• Überwachung und Problembehebung: Metriken, Warteereignisse und Problemdiagnose.