All posts by Saman Irfan

Migrate Google BigQuery to an open lakehouse on AWS with Apache Iceberg

Post Syndicated from Saman Irfan original https://aws.amazon.com/blogs/big-data/migrate-google-bigquery-to-an-open-lakehouse-on-aws-with-apache-iceberg/

Enterprises increasingly measure their readiness for AI by the readiness of their data, and that often means moving to an open lakehouse where data stays open and portable. The organizations moving fastest are the ones whose data gives them the flexibility to choose the engines and models that fit each workload. A tightly coupled, single-engine cloud data warehouse begins to hold them back. A generation of analytics tools was built inside vertically integrated systems, where the storage format, the catalog, and the query engine were fused into one vendor’s runtime. That design traded openness for convenience, and as generative AI, multi-engine analytics, and cross-team data sharing become the norm, the cost of that trade is rising. Increasingly, enterprises are choosing the opposite: an open, modular architecture where governance is defined once and enforced everywhere. This is the foundation for AI-ready data. It isn’t a new architecture to adopt, but an open lakehouse that keeps your data yours.

In this post, we explain why an open lakehouse architecture on AWS is the natural next step for organizations that are looking to future-proof their data architecture rather than re-architect onto another closed system. We then present the target architecture on AWS for migrating BigQuery workloads. Finally, we provide a step-by-step migration guide covering workload assessment, schema and query conversion, data movement strategies, and post-migration optimization to help you transition from BigQuery to an open lakehouse on AWS with confidence.

Why organizations are moving toward an open lakehouse

Open data, and the freedom to run engine of your choice. When data is held in a vendor’s proprietary format, it becomes difficult to use with other engines, tools, or AI models outside the vendor’s environment. Openness is what breaks it, but it comes in two degrees that are worth distinguishing. At the base is interoperability, which means many engines can reach the same data and it’s fast becoming table stakes now that even proprietary warehouses federate to open formats and query tables where they sit. Beyond that is portability: the freedom to take your data, your choice of engine, and your choice of AI model elsewhere without re-architecting. It’s the more durable property an open lakehouse is built to give you. Apache Iceberg is an open table format that removes these barriers: Iceberg compatible engines can read and write the same tables concurrently at petabyte scale while maintaining ACID compliance. On AWS, Apache Iceberg is a first-class citizen. Amazon Redshift, Amazon Athena, Amazon EMR, and AWS Glue all operate on one governed copy of the data in Amazon Simple Storage Service (Amazon S3). Because storage is decoupled from compute, you match the engine to the workload such as Athena for interactive SQL, Amazon Redshift for high-performance warehousing and business intelligence (BI), Amazon EMR for large-scale transformation, and Amazon SageMaker AI for machine learning (ML) without duplicating data or building extract, transform, and load (ETL) pipelines to shuffle it between systems. Interchangeability becomes a property of the architecture rather than something you engineer around.

Governance defined once, enforced across every engine. Openness doesn’t mean giving up control. On AWS, a single governance layer spans every engine that touches the data. The AWS Glue Data Catalog exposes an Apache Iceberg REST catalog endpoint that implements the Apache Iceberg REST Catalog open specification. AWS Lake Formation applies fine-grained access controls at the database, table, column, row, and cell level, defined once and enforced consistently whether a data engineer runs a Spark job on Amazon EMR, an analyst queries through Athena, or a data scientist trains a model in SageMaker AI. For discovery and sharing, you can use Amazon SageMaker Unified Studio to find, understand, and request access to governed data across the estate. Because the catalog follows an open specification rather than a single vendor’s runtime, governance travels with the data instead of locking it in.

A foundation that is AI-ready and model agnostic. The reason all this matters now is AI. Generative and agentic workloads are only as good as the data they can reach, and they perform best when data stays in open formats you fully control. But openness can’t stop at the data format. The intelligence layer matters too: the AI model, the semantic catalog, the enrichment layers, and the agents. An open foundation keeps both layers open. A single governed copy of open data sits in Amazon S3, and the same tables that serve BI and interactive analytics also feed Amazon SageMaker AI for model training and Amazon Bedrock and Amazon Bedrock AgentCore for generative and agentic work. You choose the foundation model (FM) that best fits your needs, with no extra copies to move or secure.

Target architecture: Open lakehouse on AWS

The following architecture diagram represents the recommended target state for organizations migrating BigQuery workloads to AWS and is especially well suited to customers wanting to build a unified, open data foundation. It’s built on Apache Iceberg as the foundational layer, with purpose-built consumption engines chosen per use case: Amazon Athena for interactive analytics, Amazon Redshift for BI reporting and dashboarding, and Amazon SageMaker AI for ML and agentic workloads, all on one governed copy of the data that stays open, interoperable, and portable.

The architecture follows the flow of data from left to right: source systems are ingested into an open lakehouse on Amazon S3, refined through medallion layers, and served to a diverse set of consumers such as BI tools, AI agents, and ML systems. It rests on four foundational pillars: open storage, purpose-built analytics, unified governance, and an integrated experience.

Open lakehouse target architecture on AWS: source systems flow through ingestion into an Amazon S3 Apache Iceberg lakehouse, refined through medallion layers, and served to BI, AI, and ML consumers under unified governance

Figure 1: Open lakehouse target architecture on AWS, from source ingestion through the Amazon S3 Apache Iceberg storage layer to analytics, BI, and ML consumers

Ingestion

This is where it all begins. You must bring data from a variety of sources such as databases, software as a service (SaaS) applications, streaming systems, on-premises systems, IoT devices into your data lake.

  • Bulk migration: For the initial BigQuery migration and other large batch transfers, use BigQuery’s EXPORT DATA statement to export tables to Google Cloud Storage (GCS) in Parquet format. Then use AWS DataSync to move Parquet files from Google Cloud Storage to Amazon S3. For more information, see Step 4.
  • Zero-ETL: For SaaS and operational sources such as Google Analytics, Salesforce, SAP, ServiceNow, and HubSpot, Zero-ETL integrations land data directly in the lakehouse without building or maintaining custom pipelines.
  • Change data capture (CDC): For operational data stores, Debezium connectors running on Amazon MSK Connect stream row-level changes into the lakehouse to keep it continuously current.
  • Streaming: For real-time sources, Amazon Kinesis Data Streams and Amazon Managed Streaming for Apache Kafka (Amazon MSK) provide streaming storage that feeds directly into the raw layer.

Storage layer: Amazon S3 with Apache Iceberg

Amazon S3 serves as the central storage layer, providing virtually unlimited scalability, 99.999999999% (11 nines) of data durability, and multiple storage classes, so you can match cost to the access pattern. Data is stored in Apache Iceberg table format, which brings ACID transactions, schema evolution, time travel, and partition evolution to your data lake. Amazon S3 Tables, a capability of Amazon S3, provides a fully managed Apache Iceberg experience with automatic compaction, snapshot management, and optimization removing the undifferentiated heavy lifting of table maintenance.

Data is progressively refined through a medallion architecture, with each layer stored as Apache Iceberg tables on S3:

  • Bronze/Raw: Ingested data lands here as-is, in Apache Iceberg format either on fully managed Amazon S3 Tables or as self-managed Apache Iceberg tables on Amazon S3.
  • Silver/Trusted: Silver layer serves as single source of truth. AWS Glue and Amazon EMR Spark jobs transform, clean, and conform the raw data into trusted, query-ready Apache Iceberg tables. Amazon Athena provides serverless, interactive analytics directly against this layer for interactive exploration and data discovery.
  • Gold/Serving: Amazon Redshift Serverless serves curated, business-ready data for dashboarding and reporting. Amazon Redshift is a first-class Apache Iceberg engine capable of managing the full Apache Iceberg table lifecycle (see Using Apache Iceberg tables with Amazon Redshift). Data can also be natively stored into Amazon Redshift Managed Storage for workloads that require increased performance for dashboarding and reporting use cases.

Governance: Unified across every engine

A unified foundation depends on governance that is defined once and enforced everywhere. The AWS Glue Data Catalog serves as the central metadata repository, and AWS Lake Formation provides fine-grained access control at the database, table, column, row, and cell level, enforced consistently whether data is accessed through Athena, Amazon Redshift, EMR, or SageMaker AI. AWS Glue Data Quality, a capability of AWS Glue, validates data as it moves through the layers and Amazon SageMaker Unified Studio provides a single integrated development environment for data discovery, SQL analytics, visual ETL, and artificial intelligence and machine learning (AI/ML) development all governed by the same Lake Formation permissions.

Consumers: One lakehouse, many workloads

Because data sits in open Apache Iceberg format under unified governance, a broad set of consumers can access a single source of truth without data movement:

  • BI tools: Amazon Quick Suite, Looker, Power BI, and Tableau connect for dashboarding and reporting.
  • AI agents: Agentic tooling such as MCP-based clients, Kiro, and Claude, along with Amazon Bedrock AgentCore, can reason over governed lakehouse data.
  • ML/SageMaker: Amazon SageMaker AI trains models and serves inference directly against lakehouse data.
  • Amazon SageMaker Unified Studio provides a single integrated development environment (IDE) for data exploration, SQL analytics, visual ETL, and AI/ML development. For organizations accustomed to a single-pane experience, Unified Studio addresses the concern of managing multiple services by providing one place to discover data, run queries, build dashboards, and develop ML models, all governed by the same Lake Formation permissions.

Data interoperability

The Apache Iceberg REST catalog endpoint of the AWS Glue Data Catalog, part of Amazon SageMaker Lakehouse, enables interoperability with third-party engines. Partners such as Snowflake and Databricks can read and write the same Apache Iceberg tables through that endpoint, so you choose the best tool for each job without duplicating data.

Cross-cutting layers: Orchestration, security, and observability

Two layers span the entire lakehouse rather than sitting at any single stage. An orchestration layer coordinates how data moves through the medallion layers: Amazon Managed Workflows for Apache Airflow (Amazon MWAA), AWS Step Functions, and Amazon EventBridge schedule and sequence ingestion, transformation, and load jobs across Bronze, Silver, and Gold.

A security and observability layer applies across every engine and layer. AWS Identity and Access Management (IAM) and AWS Key Management Service (AWS KMS) provide authentication, authorization, and encryption, while Amazon CloudWatch, AWS CloudTrail, and AWS Cost Explorer provide monitoring, audit logging, and cost visibility across the lakehouse.

Step-by-step migration guide

Migrating from BigQuery to an open lakehouse on AWS follows a structured, phased approach. Each phase builds confidence incrementally, so you can validate value before committing further.

Step 1: Assess and discover

Begin by evaluating your current BigQuery environment:

  • Inventory workload: Catalog all datasets, tables, views, stored procedures, scheduled queries, and downstream consumers (BI tools, applications, ML pipelines). Name the consumers that most often need a decision: Looker Studio, Connected Sheets, scheduled queries, and any production service reading BigQuery directly.
  • Classify query complexity: Analyze your SQL workloads to identify standard ANSI SQL (which migrates directly), BigQuery-specific syntax (such as STRUCT, ARRAY, UNNEST, and JavaScript UDFs), and procedural logic in stored procedures.
  • Map data dependencies: Document which tools and applications read from and write to BigQuery. Identify upstream data sources (streaming, batch, CDC) and downstream consumers (dashboards, reports, ML models).
  • Assess data volumes: Record active storage, long-term storage, data growth rates, and each dataset’s storage billing model (logical or physical) with its measured physical bytes. The compressed physical size can be a small fraction of the logical size and changes the storage cost estimate materially. This informs your S3 storage class strategy and cost modeling.
  • Evaluate governance: Document current access controls, row-level security policies, and audit requirements. Map these to Lake Formation capabilities.

The output of this phase is a migration readiness report with per-table effort scoring, query complexity classification, and a recommended migration pattern.

AWS account teams and partners have metadata-driven assessment accelerators. They read only BigQuery metadata (schemas, INFORMATION_SCHEMA job history, view definitions, and routine signatures), not raw data. From that metadata, they produce a cost comparison with the projected savings, per-table migration effort scoring, a complexity classification for every view, routine, and observed query, and a map of every tool reading from or writing to BigQuery with how each one reconnects on AWS.

Reach out to your AWS account team for an initial assessment of moving BigQuery workload to an open lakehouse on AWS.

Step 2: Design target architecture

Based on your assessment findings, design the target-state architecture:

  • Storage design: Define your S3 bucket structure with a multi-layer layout (raw, cleaned, and aggregated) aligned with data lifecycle best practices. For Apache Iceberg storage, we recommend Amazon S3 Tables for fully managed Apache Iceberg with automatic table maintenance and optimization and general-purpose S3 buckets with self-managed Apache Iceberg for cases that need granular control over table layout and compaction.
  • Partitioning strategy: Design Iceberg partition schemes aligned to actual query access patterns. Use Iceberg’s native partitioning transforms (bucketing, time slicing) for optimal query pruning.
  • Engine selection: Choose the engine from the workload you measured in Step 1. Amazon Athena fits estates that scan a moderate volume per day and tolerate a few seconds of latency whereas Amazon Redshift fits estates with sustained high concurrency, sub-second dashboard targets, or a daily scanned volume where warm compute is cheaper than paying per terabyte scanned. For large-scale data transformations, position Amazon EMR (including Amazon EMR Serverless) for complex, custom open source frameworks like Spark and Hive, or AWS Glue for serverless, integration-heavy ETL pipelines.
  • Governance model: Design your AWS Lake Formation permission model to enforce granular database, table, column, and row-level access policies. Verify the architecture plans for multi-tenant access patterns if applicable.
  • Ingestion architecture: Design data ingestion pipelines. Options include Zero-ETL integrations for operational data sources, Amazon Data Firehose for streaming data, and AWS Glue for batch ETL.

Step 3: Convert and validate (POC)

Pick a proof-of-concept slice, a handful of representative tables and the dashboards that read them, and convert BigQuery SQL, stored procedures, and data pipelines to their AWS equivalents.

  • Schema conversion: A BigQuery dataset becomes an AWS Glue database when Athena is the engine, or an Amazon Redshift schema when Amazon Redshift is. The BigQuery project maps to your AWS account with its top-level namespace being the AWS Glue Data Catalog for Athena or to an Amazon Redshift database.
  • SQL conversion: Convert BigQuery SQL dialect to ANSI SQL or engine-specific syntax. Common conversions include:
    • BigQuery STRUCT to Iceberg struct and ARRAY to Iceberg list, and BigQuery JSON maps to a string column queried with JSON function when Athena is the engine and data is stored in Apache Iceberg format. For Amazon Redshift, the recommendation is to build the gold layer which is optimized for reporting use cases where data is flattened out for high-performance queries. If you need to store nested data inside Amazon Redshift, use the SUPER data type.
    • On Amazon Redshift, the target depends on where the data lands: for data stored natively in Amazon Redshift, BigQuery JSON maps to the SUPER type (queried with PartiQL), and STRUCT/ARRAY are represented within SUPER.
    • BigQuery MERGE statements to Athena or Amazon Redshift MERGE INTO. Choose copy-on-write for tables updated in bulk and merge-on-read for frequently updated ones.
    • BigQuery JavaScript UDFs to a SQL expression where possible, otherwise an AWS Lambda-backed UDF on Athena (USING EXTERNAL FUNCTION) or Amazon Redshift.
    • BigQuery scheduled queries to Amazon EventBridge Scheduler running Athena queries, Amazon Redshift scheduled queries in Query Editor v2, or dbt for anything that is a model. Chains of schedules become AWS Step Functions.
  • Pipeline conversion: Rebuild ETL pipelines using AWS Glue, dbt (with dbt-athena or dbt-redshift adapters), or Apache Spark on EMR.
  • Validation: Run converted workloads against the slice. Compare row counts and checksums per table, query results for the dashboards on the slice, and run times against the agreed target, between BigQuery and AWS, to verify correctness.

BigQuery offers two pricing models for running queries. On-demand pricing is a scan-based model that charges $6.25 per terabyte of data scanned, while capacity-based pricing is a compute-based model that charges for the compute capacity used to run queries. Amazon Redshift is also compute-based, so if you currently run BigQuery on demand, its scan-based pricing is not directly comparable to Amazon Redshift’s compute-based pricing. Because the two models differ, benchmark a representative mix of queries on the target engine rather than a single query type.

A good practice is to run three tiers of queries and compare total cost and performance against your current BigQuery baseline: lightweight queries for simple lookups and aggregations, medium queries for moderate joins and transformations, and heavyweight queries for complex joins, window functions, and large scans. To build an accurate, workload-representative total cost of ownership (TCO), multiply each query tier by how often it runs, add storage costs, and produce a monthly figure you can compare directly to your current monthly BigQuery bill.

Step 4: Migrate data

Execute bulk data migration from BigQuery to Amazon S3 with Apache Iceberg:

  1. Use BigQuery’s EXPORT DATA statement to export tables to Google Cloud Storage (GCS) in Parquet format. For large datasets, partition exports by date or key ranges to parallelize the transfer.
  2. Use AWS DataSync to move Parquet files from Google Cloud Storage to Amazon S3. DataSync reads Google Cloud Storage directly through its S3-compatible XML API and writes the objects to your S3 bucket, with scheduling and incremental transfers built in. For most transfers you deploy a DataSync agent, either as a virtual machine in Google Cloud (closest to the source bucket) or as an Amazon Elastic Compute Cloud (Amazon EC2) instance in your virtual private cloud (VPC). DataSync Enhanced mode transfers between Google Cloud Storage and Amazon S3 run without an agent and offer higher performance and scalability.
  3. Create the Apache Iceberg tables in Athena or Amazon S3 Tables and load them from the exported Parquet with CREATE TABLE AS SELECT or INSERT INTO, or register the existing files with Spark’s add_files procedure on Amazon EMR or AWS Glue.
  4. During the transition period, implement incremental data synchronization to keep AWS data current while BigQuery remains active. Use BigQuery’s change history functions (APPENDS and CHANGES) for scheduled delta exports, change data capture (CDC) patterns, or Amazon AppFlow.
  5. Apply for Google’s data transfer fee waiver before the bulk export starts: Google waives egress fees for customers leaving Google Cloud who request it through support and complete the export within the agreed window.

Step 5: Cutover and go-live

Execute production cutover with minimal disruption:

  • Parallel run: Run workloads on both BigQuery and AWS simultaneously for a validation period. Compare outputs to confirm data consistency and query correctness.
  • Redirect consumers: Every consumer takes one of three paths:
    • Re-point: In this case, the tool stays the same, only its connection changes from BigQuery to Athena or Amazon Redshift (dbt, Fivetran, Tableau, Power BI, Looker). This is usually a JDBC/ODBC or connector update. Athena and Amazon Redshift both use the AWS Glue Iceberg REST catalog (IRC) endpoint to locate the physical Parquet files on Amazon S3. For more information, see Querying Apache Iceberg tables in Athena and Querying Apache Iceberg tables in Amazon Redshift.
    • Rebuild: In this case, the workload has no direct AWS connection and is re-implemented on an AWS counterpart (BigQuery scheduled queries → Amazon Athena Saved Queries orchestrated by AWS Step Functions, or managed through Amazon EventBridge to run an ETL pipeline on a schedule, Dataform → dbt (using the Athena or Amazon Redshift connector), BigQuery ML → SageMaker AI or Amazon Redshift ML), Google Analytics 4 and Connected Sheets → Amazon AppFlow native connectors ingest the data (Google → AWS) into Amazon S3 or Amazon Redshift on a schedule. From S3, an AWS Glue or Athena step lands it in Apache Iceberg.
    • Relay: In this case, the tool stays on Google Cloud for now and reads AWS data synced back to it (Connected Sheets, GA4 raw export).

Looker Studio, Connected Sheets, and scheduled queries are the consumers that most often need an explicit decision on which path to take.

  • Validate end-to-end: Confirm that all dashboards, reports, scheduled jobs, and ML pipelines produce correct results.
  • Decommission: After successful validation, decommission BigQuery resources and terminate cross-cloud data synchronization.

Step 6: Optimize and expand

Post-migration, optimize your lakehouse for performance and cost:

  • Query optimization: On general-purpose buckets with self-managed Apache Iceberg, tune file size targets, compaction frequency, and sort orders to the actual query patterns whereas Amazon S3 Tables handles compaction and snapshot expiry automatically. Enable Athena query result reuse and Amazon Redshift materialized views for frequently accessed datasets.
  • Cost optimization: Use S3 Intelligent-Tiering for infrequently accessed data, Athena workgroups and Amazon Redshift usage limits to control costs, and AWS Cost Explorer to monitor.
  • Enable new capabilities: With data in open Apache Iceberg format on Amazon S3, you can take on use cases that were previously difficult or impossible:
    • Train ML models directly on lakehouse data using SageMaker AI.
    • Build generative AI applications with Amazon Bedrock Knowledge Bases.
    • Implement real-time analytics with Amazon Data Firehose or Amazon Kinesis Data Streams writing Apache Iceberg tables to Amazon S3 Tables, or with Amazon Redshift streaming ingestion.
    • Share Apache Iceberg tables with partner engines such as Snowflake and Databricks through the Apache Iceberg REST catalog endpoint.

Customer success: Yggdrasil Gaming

Yggdrasil Gaming, a global iGaming studio, migrated from BigQuery to an Apache Iceberg-based lakehouse on AWS, working with GOStack, an AWS Partner. The company had been operating dual-cloud infrastructure across AWS and Google Cloud, creating significant operational overhead that limited their ability to implement advanced analytics and AI/ML initiatives.

The migration followed a phased approach: establishing the lakehouse foundation on S3 with Apache Iceberg and AWS Glue Data Catalog, implementing real-time data ingestion using Debezium for change data capture, migrating processing pipelines to AWS Lambda and Amazon EMR, modernizing the transformation layer with dbt and Amazon Athena, and enabling governance through AWS Lake Formation.

The results were significant:

  • 60 percent reduction in data processing costs.
  • 75 percent improvement in analytics latency (from 2 hours to 30 minutes).
  • Alleviated multi-cloud complexity by consolidating on AWS.
  • Enabled AI/ML use cases including player behavior modeling and fraud detection.

For the full story, read Building a modern lakehouse architecture: Yggdrasil Gaming’s journey from BigQuery to AWS on the AWS Big Data Blog.

Customer success: Global technology leader performed zero-downtime GCP exit at petabyte scale

A global technology leader in the network security space migrated from Google BigQuery to AWS to reduce costs and enable streaming ingestion for network security analytics. Working with Quantiphi, an AWS Partner, the company migrated 10 core customer-facing applications across 3 regions with zero business downtime for more than 1,000 customers.

Highlights of the migration:

  • Migrated over 1,000 BigQuery projects to Amazon Athena with Apache Iceberg on Amazon S3 (setting up over 5,000 Apache Iceberg tables).
  • Key drivers: cost reduction (30 percent savings in cloud operational costs), streaming ingestion for security analytics, and regulatory alignment.
  • Zero business downtime across a petabyte-scale migration spanning three AWS Regions, with minute-level data refresh meeting a 99.9 percent service level agreement (SLA).
  • A full GCP exit consolidating entirely on AWS through Quantiphi’s Qatapult modernization and migration platform.

For the full story, read Engineering Excellence: How a Tech Leader Migrated to AWS with Zero Business Downtime (Quantiphi case study).

Conclusion

Migrating from BigQuery to an open lakehouse architecture on AWS with Apache Iceberg is an architectural change and a strategic investment in data openness, multi-engine flexibility, and AI readiness. By storing data in open formats on Amazon S3, governing it centrally through AWS Lake Formation, and accessing it with purpose-built engines, you build a data foundation that adapts to your evolving needs without vendor lock-in.

The phased migration approach (assess, design, convert, migrate, cutover, and optimize) means you can validate value at each step and maintain business continuity throughout the transition. Organizations like Yggdrasil Gaming have demonstrated that this migration can be completed in months, not years, with measurable cost savings and performance improvements.

To get started, explore the following resources:

 


About the authors

Saman Irfan

Saman Irfan

Saman is a Senior GTM Specialist Solutions Architect at Amazon Web Services, based in Berlin, Germany. A Data & AI strategist with over 10 years of experience, she drives the technical GTM motion for Data, Analytics, and AI at AWS across EMEA, helping enterprise customers modernize their data architectures and navigate large-scale migrations to unlock the full potential of their data.

Siddhu Kolappan

Siddhu Kolappan

Siddhu is an Analytics GTM Lead at AWS, driving strategy and adoption for AWS’s data and analytics portfolio across EMEA. He has over 15 years of experience helping enterprises transform their data platforms and is currently focused on enabling customers build trusted data foundations for the agentic AI era.

Ryan Jadidi

Ryan Jadidi

Ryan is a Specialist Solutions Architect at Amazon Web Services, based in Sydney, Australia. With over fifteen years building and running data platforms across fintech, media and consulting, he works with startup and enterprise customers across Asia Pacific and Japan on the data foundations for AI.

Perform ETL operations using Amazon Redshift RSQL

Post Syndicated from Saman Irfan original https://aws.amazon.com/blogs/big-data/perform-etl-operations-using-amazon-redshift-rsql/

Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that makes it simple and cost-effective to analyze all your data using standard SQL and your existing ETL (extract, transform, and load), business intelligence (BI), and reporting tools. Tens of thousands of customers use Amazon Redshift to process exabytes of data per day and power analytics workloads such as BI, predictive analytics, and real-time streaming analytics.

There are many ways to interact with Amazon Redshift. You can programmatically access data in your Amazon Redshift cluster by using the Amazon Redshift Data API, or you can use a web-based interface such as Amazon Redshift Query Editor V2 to author SQL queries. You can also interact with Amazon Redshift in interactive or batch mode via Amazon Redshift RSQL.

Previously, you had to use the PostgreSQL psql command line tool to interact with Amazon Redshift for simple use cases such as importing and exporting data to and from Amazon Redshift or submitting SQL in batch or interactive mode, or for advanced use cases such as performing ETL operations without writing complex ETL jobs.

Now you can use Amazon Redshift RSQL, a new command line tool to connect to an Amazon Redshift cluster and perform operations such as describe database objects, query data, view and export query results in various output file formats, run scripts that include both SQL and complex business logic, perform data cleansing and transformation operations using familiar SQL, and write ETL and ELT scripts using enhanced control flow and advanced error handling. Moreover, if you’re migrating from self-managed data warehousing engines such as Teradata to Amazon Redshift and using Teradata BTEQ (Basic Teradata Query) scripting for data automation, ETL or other tasks can now migrate to Amazon Redshift RSQL.

This post explains how to use Amazon Redshift RSQL for ETL, data cleansing and preparation, enhanced control flow, and exception and error handling.

Solution overview

Amazon Redshift RSQL enables you to connect to your Amazon Redshift cluster via a command line. It supports the capabilities of the PostgreSQL psql command line tool with an additional set of Amazon Redshift specific capabilities:

  • Describe properties or attributes of external tables in an AWS Glue catalog or Apache Hive metastore, external databases in Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL, and tables shared using Amazon Redshift data sharing
  • Use enhanced control flow commands such as \IF, \ELSEIF, \ELSE, \ENDIF, \GOTO, and \LABEL
  • Use single sign-on (SSO) authentication using ADFS, PingIdentity, Okta, Azure AD, or other SAML/JWT based identity providers (IdPs), as well as use browser-based SAML IdPs with multi-factor authentication (MFA)

The following are some commonly used commands in Amazon Redshift RSQL. We use these commands in this post to demonstrate different possible use cases using Amazon Redshift RSQL scripts.

  • \echo – Prints the specified string to the output.
  • \remark – An extension of the \echo command that has the ability to break the output over separate lines.
  • \goto – Skips all intervening commands and SQL statements and resume the processing at the specified \LABEL in sequence. The \LABEL must be a forward reference. You can’t jump to a \LABEL that lexically precedes the \GOTO.
  • \label – Establishes an entry point for running the program as the target for a \GOTO command.
  • \exit – Stops running Amazon Redshift RSQL. You can also specify an optional exit code.
  • \q – Logs off database sessions and exits Amazon Redshift RSQL.

Prerequisites

The following are the prerequisites for using Amazon Redshift RSQL and perform the steps in this post:

  • An AWS account
  • Linux, Windows, or MacOs operating system (Amazon Redshift RSQL is available for these operating systems)
  • An Amazon Redshift cluster
  • SQL knowledge

Additionally, complete the following prerequisite steps:

  1. Install Amazon Redshift RSQL on your local machine. For instructions, refer to Getting started with Amazon Redshift RSQL.
  2. Connect to the Amazon Redshift cluster.
  3. Create the orders and orders_summary tables using the following DDL statements:
    create table orders (
      O_ORDERKEY bigint NOT NULL,
      O_CUSTKEY bigint,
      O_ORDERSTATUS varchar(1),
      O_TOTALPRICE decimal(18,4),
      O_ORDERDATE Date,
      O_ORDERPRIORITY varchar(15),
      O_CLERK varchar(15),
      O_SHIPPRIORITY Integer,
      O_COMMENT varchar(79))
    distkey (O_ORDERKEY)
    sortkey (O_ORDERDATE);
    
    CREATE TABLE orders_summary 
    ( o_orderkey bigint, 
     o_custkey bigint, 
     o_orderstatus character varying(1),
     o_totalprice integer,
     target_information character varying(14),
     rank character varying(15),
     description character varying(15)
    ) DISTSTYLE AUTO;

Import data into the Amazon Redshift cluster

There are multiple ways to load data into Amazon Redshift tables, including using Amazon Redshift RSQL. In this section, we review the syntax and an example of the Amazon Redshift RSQL script to load data into an Amazon Redshift table using the COPY command.

We use the following syntax:

COPY <TABLE> from <location> 
iam_role <arn>
region <region>;

We provide the following parameters:

  • <location> – The location of the source data to be loaded into the target table
  • <arn> – The AWS Identity and Access Management (IAM) role for accessing the data
  • <region> – The AWS Region of the source data

In the following example Amazon Redshift RSQL script, we load data from an Amazon Simple Storage Service (Amazon S3) bucket location into the orders table:

\echo 'Job started' 
  copy orders from 's3://redshift-immersionday-labs/data/orders/orders.tbl.'
  iam_role default
  region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;
\echo 'Job Ended'
\exit 0 

Enhanced control flow

You can use Amazon Redshift RSQL to define programmatic enhanced control flow and conditional blocks in your ETL script. We use the following syntax:

\if <condition> 
  <code_block1>
\else
  <code_block2>
\endif

The syntax includes the following components:

  • <condition> –The conditional statement
  • <code_block1> – The code block to run when the condition is met
  • <code_block2> – The code block to run when the condition is not met

In the following example script, we perform some conditional checks using if, elseif, and else commands based on the count of records from the orders table, and we display some messages based on the record count value:

\echo 'Job started'
Select count(*)  from orders \gset
select :count as count;
\if :count < 76000000 
  \echo 'Orders are less than target'
\elseif :count =76000000
  \echo 'Order met the target'
\else :count > 76000000
  \echo 'Orders exceeded the target'
\endif
\echo 'Job Ended' 
\exit 0  

Error handling

You can use Amazon Redshift RSQL to define exception handling blocks in your in ETL script to handle various user-defined and system-generated error scenarios that you might encounter while running the ETL process.

The following are some of the error handling options supported in Amazon Redshift RSQL:

  • :ACTIVITYCOUNT – This variable is similar to the psql variable ROW_COUNT, which returns the number of rows affected by last SQL statement. However, ACTIVITYCOUNT reports the affected row count for SELECT, COPY, and UNLOAD statements, which ROW_COUNT does not. For SQL statements returning data, ACTIVITYCOUNT specifies the number of rows returned to Amazon Redshift RSQL from the database.
  • :ERRORCODE – This variable contains the return code for the last submitted SQL statement to the database. Error code 0 specifies that SQL statement completed without any errors.
  • :ERRORLEVEL – This variable is used to assign severity levels to errors. You can use these severity levels to determine a course of action. The default value is ON.
  • :MAXERROR – This variable designates a maximum error severity level beyond which Amazon Redshift RSQL halts job processing. If SQL statements in Amazon Redshift RSQL scripts produce an error severity level greater than the designated maxerror value, Amazon Redshift RSQL immediately exits.
  • :LAST_ERROR_MESSAGE – This variable contains the error message of the most recent failed query.

We use the following syntax:

\if :ERROR <> 0 
  \echo :<ERRORCODE>
  \echo :<LAST_ERROR_MESSAGE>
  \goto <codeblock1>
\else
  \goto Y
\endif

The syntax includes the following information:

  • <ERRORCODE> –The error code number
  • <LAST_ERROR_MESSAGE> – The error message of the most recent failed query
  • <code_block1> – The code block to run when the error condition is met
  • <code_block2> – The code block to run when the error condition is not met

In the following example script, we create the orders_staging table and copy records into the table from an Amazon S3 location. The script also contains an exception handling section for both the table creation and copy process to handle the possible errors encountered during the process.

\echo `date`
\echo 'Job started'
DROP TABLE IF EXISTS orders_staging;

create table orders_staging (
O_ORDERKEY bigint NOT NULL,
O_CUSTKEY bigint,
O_ORDERSTATUS varchar(1),
O_TOTALPRICE decimal(18,4),
O_ORDERDATE Date,
O_ORDERPRIORITY varchar(15),
O_CLERK varchar(15),
O_SHIPPRIORITY Integer,
O_COMMENT varchar(79))
distkey (O_ORDERKEY)
sortkey (O_ORDERDATE);

\if :ERROR <> 0 
  \echo :ERRORCODE
  \remark :LAST_ERROR_MESSAGE
  \goto QUIT_JOB
\else
  \remark '***Orders_Staging Table Created Successfully****'
  \goto COPY_DATA
\endif

\label COPY_DATA
 copy orders_staging from 's3://redshift-immersionday-  labs/data/orders/orders.tbl.'
 iam_role default
 region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

\if :ERROR <> 0
  \echo :ERRORCODE
  \remark :LAST_ERROR_MESSAGE
  \goto QUIT_JOB
\else 
  \remark '****Data Copied Successfully****'
\endif

\echo `date`
\echo 'Job Ended'
\exit 0 

\label QUIT_JOB
 \echo `date`
 \echo 'Job Failed'
 \exit 1 

Data transformation and preparation

You can perform some common data preparation and transformation operations on your dataset using SQL statements in the Amazon Redshift RSQL ETL script. In this section, we demonstrate data transformation and preparation operations such as casting, new data column creation, and splitting an input column into multiple columns.

We use industry standard SQL statements for transforming and preparing data for downstream consumption.

In the following example script, we use a SQL statement to transform the data from the orders_staging table and insert it into the orders_summary table:

\echo `date`
\echo 'Job started'

insert into orders_summary 
(o_orderkey,
o_custkey,
o_orderstatus,
o_totalprice,
target_information,
rank,
description)
select
o_orderkey,
o_custkey,
o_orderstatus,
o_totalprice::int,
case 
when o_totalprice < 200000
then 'Target Missed'
when o_totalprice = 200000
then 'Target Met'
when o_totalprice > 200000
then 'Target Exceeds'
end as "Target_Information",
split_part (o_orderpriority,'-',1) as RANK, 
split_part (o_orderpriority,'-',2) as DESCRIPTION
from orders_staging; 

\if :ERROR <> 0 or :ACTIVITYCOUNT=0
 \echo :ERRORCODE
 \remark :LAST_ERROR_MESSAGE
 \goto QUIT_JOB
\else
 \remark 'Data Inserted into Summary Orders Table'
\endif

\echo `date`
\echo 'Job Ended'
\exit 0 

\label QUIT_JOB
 \echo `date`
 \echo 'Job Failed'
 \exit 1 

Export data from an Amazon Redshift cluster and output file formatting options

You can use Amazon Redshift RSQL to extract data from one or multiple Amazon Redshift tables and write to your disk for consumption by downstream applications. Amazon Redshift RSQL uses the \EXPORT option to export the result of query to an output file.

The following are some of the useful output formating options supported in RSQL:

  • \rset rformat on – This command is required for all the formatting commands to take effect.
  • \pset format – Formats can include aligned, AsciiDoc, CSV, HTML, LaTeX, LaTeX longtable, troff-ms, unaligned, and wrapped.
  • \pset border – This option specifies border information for output data. Value 0 means no border, 1 means internal dividing lines, and 2 means table frame.
  • \rset heading – This command adds the specified heading to the output result.
  • \rset rtitle – This command adds the specified heading as well as current system date of the client computer.
  • \rset titledashes on/off – This command specifies whether to print a line of dash characters between the column names and column data returned for the SQL query.
  • \rset width – This command specifies the target width for each line in a report
  • \t – This command turns off printing column names as well as result row count at the end of the output (footers).

We use the following syntax:

\export report file=<'FilePath/Filename'>
\rset rformat on
\pset format wrapped
\pset border 2
\rset heading ‘This is Heading’
\rset width 50
\rset titledashes on
<SQL Query>
\export reset

We provide the following information:

  • <‘FilePath/Filename’> – The file name and path for the output file
  • <SQL Query> – The SQL query to run

In the following example script, we export the data from the orders_summary table using a SQL query and write it into the orders.txt text file on the local machine:

\echo `date`
\echo 'Job started'

\export report file='/<FilePath>/orders.txt'
\rset rformat on
\pset format wrapped
\rset width 50
\rset titledashes on
select * from orders_summary limit 100;

\export reset
\echo 'Job Ended'
\echo `date`
\exit 0 

Automate the Amazon Redshift RSQL ETL script

One of the options to automate Amazon Redshift RSQL scripts to run on a specific schedule is via shell scripting. You can schedule the shell script via a CRON job, a command line utility.

We use the following syntax:

#!/bin/sh
rsql -D awsuser -f <RSQL_SCRIPT> <LOG_FILE>

We provide the following information:

  • <RSQL_SCRIPT> – The SQL scripts to un
  • <LOG_FILE> – The output log file

In the following example shell script, we run the Amazon Redshift RSQL script that we created and write the output log in a log file in the local machine. You can schedule the shell script via a CRON job.

#!/bin/sh
SCRIPTS_DIR="<SCRIPTS_DIR>"
LOG_DIR="<LOG_DIR>"

RSQL_SCRIPT="${SCRIPTS_DIR}/<RSQL_SCRIPT>.sql"
LOG_FILE="${LOG_DIR}/test_rsql.log"

touch $LOG_FILE

rsql -D awsuser -f ${RSQL_SCRIPT} > ${LOG_FILE}

Clean up

To avoid incurring future charges, stop the Amazon Redshift cluster created for the purpose of this post.

Conclusion

In this post, we explained how to use Amazon Redshift RSQL to perform ETL operations. We also demonstrated how to implement advanced error handling and enhanced control flow in your Amazon Redshift RSQL ETL script.

If you’re using scripts via the psql command line client on Amazon Redshift, you can operate on Amazon Redshift RSQL with no modification. If you’re migrating your Teradata BTEQ scripts to Amazon Redshift RSQL, you can use the AWS Schema Conversion Tool (AWS SCT) to automatically convert BTEQ scripts to Amazon Redshift RSQL scripts.

To learn more, refer to Amazon Redshift RSQL.


About the Authors

Saman Irfan is a Specialist Solutions Architect at Amazon Web Services. She focuses on helping customers across various industries build scalable and high-performant analytics solutions. Outside of work, she enjoys spending time with her family, watching TV series, and learning new technologies.

Sudipta Bagchi is a Specialist Solutions Architect at Amazon Web Services. He has over 12 years of experience in data and analytics, and helps customers design and build scalable and high-performant analytics solutions. Outside of work, he loves running, traveling, and playing cricket.