如何绕过Redshift Spectrum限制，为嵌套Parquet创建外部表？

Great question! While Amazon Redshift (the cluster itself) doesn’t support storing nested data types natively, Redshift Spectrum has built-in capabilities to query nested Parquet data in S3—you just need to use the right syntax and ensure your Glue Data Catalog schema is properly set up. Here’s how to do it:

1. Directly Access Struct Fields with Dot Notation #

Parquet’s struct-based nested fields can be queried directly using dot notation. Since your schema was extracted via Glue Crawler, Spectrum will recognize the nested structure defined in the Data Catalog. For example, if your table has a user struct with id and full_name sub-fields:

SELECT
  user.id AS user_id,
  user.full_name AS user_full_name,
  email
FROM your_spectrum_parquet_table;

This works because Spectrum parses the Parquet file’s nested structure on-the-fly, even though Redshift can’t store that nested type as a local column.

2. Flatten Arrays with UNNEST #

For array-type nested data, use the UNNEST clause to expand array elements into individual rows. If your data includes an orders array containing structs with order_id and purchase_amount:

SELECT
  user.id AS user_id,
  order_details.order_id,
  order_details.purchase_amount
FROM your_spectrum_parquet_table
CROSS JOIN UNNEST(orders) AS order_details;

This will turn each element in the orders array into a separate row, making the nested array data accessible as flat, queryable columns.

3. Validate & Adjust Glue Data Catalog Schema #

Since you used a Glue Crawler to generate the schema, double-check that it correctly identifies all nested structures (structs, arrays, maps). If the crawler misses any nested fields or misclassifies them, manually edit the table schema in the Glue Data Catalog to correct the type definitions. This ensures Spectrum can properly interpret the Parquet data’s structure.

For example, if your schema includes a nested shipping_address struct with street, city, and zip_code, confirm the Glue table defines it as a struct type (not a string or other incorrect type).

4. Create a Flattened View (For Reusability) #

If you need to reuse the flattened data across multiple queries, create a Redshift view on top of your Spectrum table that handles the unnesting and dot notation. This lets you query the view like a regular flat table:

CREATE OR REPLACE VIEW flattened_customer_data AS
SELECT
  user.id AS user_id,
  user.full_name AS user_full_name,
  order_details.order_id,
  order_details.purchase_amount,
  shipping_address.city AS shipping_city
FROM your_spectrum_parquet_table
CROSS JOIN UNNEST(orders) AS order_details;

Then query the view with simple syntax:

SELECT * FROM flattened_customer_data WHERE user_id = 456;

Key Notes: #

• Unlike JSON files (where you might rely on parsing functions like JSON_EXTRACT_PATH_TEXT), Parquet’s columnar format allows Spectrum to access nested fields directly—this is far more efficient.
• Ensure your Redshift cluster is running a modern version (most post-2020 versions support these features; if you’re on an older cluster, consider upgrading for full compatibility).

内容的提问来源于stack exchange，提问作者Am1rr3zA