sql - BigQuery - Create a table from results of a query that uses complex CTEs?

Question

I have a multi CTE query with large underlying datasets that is run too frequently. I could just create a table of the results of that query for people to use instead, and refresh that daily. But I'm lost on the syntax to create such a table.

CREATE OR REPLACE TABLE dataset.target_table
AS

with cte_one as (
    select 
      stuff
    from big.table
),

...

cte_five as (
    select 
      stuff
    from other_big.table
),

final as (
    select * 
    from cte_five left join cte_x on cte_five.id = cte_x.id
)

SELECT
*
FROM final

Is basically what I have. This actually creates the target table with the right schema even, but doesn't insert any rows...Any hints? Thanks

question from:https://stackoverflow.com/questions/65601672/bigquery-create-a-table-from-results-of-a-query-that-uses-complex-ctes

Answer 1

If you really want to do this in one step, you can just do SELECT INTO...

with cte_one as (
    select 
      stuff

    from big.table
),

...

cte_five as (
    select 
      stuff
    from other_big.table
),

final as (
    select * 
    from cte_five left join cte_x on cte_five.id = cte_x.id
)

SELECT
*
INTO dataset.target_table
FROM final

That said, since this isn't just a once-off need I recommend creating the landing table once initially and then scheduling a daily flush and fill (TRUNCATE + INSERT) to update the data. It will give you more explicit control over the data types and also lets you work with a persistent object rather than something built from scratch daily.