Athena / ANSI SQL - select columns as struct

user_2109963 0
Viewed 315

I've done extensive Googling and haven't found an answer to this.

I'm working in Athena and using CTAS queries to pull data from large CSV files into Parquet for more efficient querying. I'd like to be able to put some of the records into a struct to be accessed with a subscript. I know structs are possible in Parquet, but I can't figure out how to write the query (or if it's even possible).

I've attempted both of these:

select email as "record.email"

and

select email as "record[email]"

Obviously, that creates a column name with the subscript as part of the string rather than as a member of a struct. Is this even possible in ANSI SQL?

amazon-athena sql
1 Answers

AWS Athena docs provide the following example

WITH dataset AS (
  SELECT
    CAST(
      ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)
    ) AS users
)
SELECT * FROM dataset

-- Result
-- +--------------------+
-- | users              |
-- +--------------------+
-- | {NAME=Bob, AGE=38} |
-- +--------------------+

So for your case, you could try to do something like

SELECT
    CAST(ROW(email_column) AS ROW(email VARCHAR)) AS "record"
FROM database.table
user_61470640
Related