Skip to content

Instantly share code, notes, and snippets.

@prodeezy

prodeezy/nested_data_parquet.sql

Created December 1, 2021 22:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save prodeezy/225df44ef08fd4eacf49a466514d25c6 to your computer and use it in GitHub Desktop.
Save prodeezy/225df44ef08fd4eacf49a466514d25c6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
nested_data_parquet.sql
CREATE TABLE nested_array_data_parquet USING PARQUET AS
SELECT id as row_id,
NAMED_STRUCT( "first_level", ARRAY(NAMED_STRUCT("second_level",
ARRAY(NAMED_STRUCT("level3_id", id+1, "revenue", CAST(RAND(1)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+2, "revenue", CAST(RAND(2)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+3, "revenue", CAST(RAND(3)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+4, "revenue", CAST(RAND(4)*100 AS DOUBLE))),
"level2_id", id+1,
"level_2_other", "should_not_be_read"
),
NAMED_STRUCT("second_level",
ARRAY(NAMED_STRUCT("level3_id", id+1, "revenue", CAST(RAND(1)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+2, "revenue", CAST(RAND(2)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+3, "revenue", CAST(RAND(3)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+4, "revenue", CAST(RAND(4)*100 AS DOUBLE))),
"level2_id", id+2,
"level_2_other", "should_not_be_read"
),
NAMED_STRUCT("second_level",
ARRAY(NAMED_STRUCT("level3_id", id+1, "revenue", CAST(RAND(1)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+2, "revenue", CAST(RAND(2)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+3, "revenue", CAST(RAND(3)*100 AS DOUBLE)),
NAMED_STRUCT("level3_id", id+4, "revenue", CAST(RAND(4)*100 AS DOUBLE))),
"level2_id", id+3,
"level_2_other", "should_not_be_read"
)) ,
"level1_id", id
) as root_level FROM range(1000000000)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment