Flattening Athena Complex Data Types (Array, Struct) for Amazon Quick Visualization

Overview

Amazon Quick currently supports the following primitive data types: Date, Decimal, Integer, and String (Supported data types and values). However, when querying data stored in formats like JSON or Parquet using Amazon Athena, your tables often contain complex types such as ARRAY and STRUCT that Quick cannot directly consume.

This article demonstrates how to flatten these complex data types in Athena using CROSS JOIN UNNEST (for arrays) and dot notation (for structs), then store the result as a view that Quick can query directly.

Scenario

Consider an e-commerce application that stores order data in S3 as Parquet files. Each record contains:

• Order metadata (order ID, date, customer ID) as primitive types
• An items column of type ARRAY<STRUCT<product_name: STRING, quantity: INTEGER, price: DECIMAL>> representing line items
• A shipping_address column of type STRUCT<street: STRING, city: STRING, state: STRING, zip: STRING>

Quick cannot ingest the items array or the shipping_address struct directly. You need to flatten them into individual columns with primitive types.

Sample Table Definition

CREATE EXTERNAL TABLE orders (
    order_id STRING,
    order_date DATE,
    customer_id STRING,
    items ARRAY<STRUCT<
        product_name: STRING,
        quantity: INTEGER,
        price: DECIMAL(10,2)
    >>,
    shipping_address STRUCT<
        street: STRING,
        city: STRING,
        state: STRING,
        zip: STRING
    >
)
STORED AS PARQUET
LOCATION 's3://your-bucket/orders/';

Step 1: Flatten Structs Using Dot Notation

Access individual fields within a STRUCT column by using dot notation. Each field becomes its own column in the result set:

SELECT
    order_id,
    order_date,
    customer_id,
    shipping_address.street  AS shipping_street,
    shipping_address.city    AS shipping_city,
    shipping_address.state   AS shipping_state,
    shipping_address.zip     AS shipping_zip
FROM orders;

This converts the nested shipping_address struct into four separate STRING columns that Quick can consume directly.

Step 2: Flatten Arrays Using CROSS JOIN UNNEST

Use CROSS JOIN UNNEST to expand each element of an array into its own row. Combined with dot notation, you can extract the struct fields within each array element:

SELECT
    order_id,
    order_date,
    customer_id,
    item.product_name,
    item.quantity,
    item.price
FROM orders
CROSS JOIN UNNEST(items) AS t(item);

Each array element produces a new row. An order with 3 line items results in 3 rows, each carrying the parent order's metadata alongside one item's details.

Step 3: Combine Both Techniques in a View

Create a view that flattens both the array and the struct. Quick can then use this view as a data source:

CREATE OR REPLACE VIEW orders_flat AS
SELECT
    order_id,
    order_date,
    customer_id,
    -- Flattened array elements (via CROSS JOIN UNNEST)
    item.product_name   AS item_product_name,
    item.quantity        AS item_quantity,
    item.price          AS item_price,
    -- Flattened struct fields (via dot notation)
    shipping_address.street  AS shipping_street,
    shipping_address.city    AS shipping_city,
    shipping_address.state   AS shipping_state,
    shipping_address.zip     AS shipping_zip
FROM orders
CROSS JOIN UNNEST(items) AS t(item);

All columns in this view are now primitive types (STRING, INTEGER, DECIMAL, DATE) that map directly to Quick's supported data types.

Step 4: Connect the View to Quick

1. In Quick, create a new dataset and select Athena as the data source.
2. Choose your database and select the orders_flat view.
3. Import to SPICE or use direct query mode.
4. Build your visuals using the flattened columns.

Summary

When Athena tables contain complex types that Quick cannot directly consume:

1. Use dot notation (column.field) to extract individual fields from STRUCT columns.
2. Use CROSS JOIN UNNEST to expand ARRAY elements into separate rows.
3. Combine both techniques and store the result as a view.
4. Point Quick at the flattened view for visualization.

This pattern bridges the gap between Athena's rich type system and Quick's requirement for primitive data types, enabling you to visualize nested and repeated data without modifying your source files.

References

• Supported data types and values - Amazon Quick
• Flatten nested arrays - Amazon Athena
• Query arrays with complex types and nested structures - Amazon Athena
• Supported data types from external data sources - Amazon Quick