Creating a table from a prior WITH clause in BigQuery

Question

WITH LAYER AS (
   SELECT 
      SPLIT(de_nest, '|')[OFFSET(1)] AS product,
      ....
      

   FROM `table`,
   UNNEST(SPLIT(LOWER(REPLACE(variable, '^', '-')), '-')) AS de_nest
) 
-- Filter out empty products
CREATE OR REPLACE TABLE `newtable` AS
SELECT * FROM LAYER WHERE product is NOT NULL

This leads me to the following error.

Syntax error: Expected "(" or "," or keyword SELECT but got keyword CREATE at [25:1]

But I cannot seem to find a sensible way of resolving this. My first workload is doing the un-nesting of the first table and the second is doing some filtering on those columns generated from the un-nesting process.

Answer 1

You should try to put the CTE declaration after the CREATE statement:

CREATE OR REPLACE TABLE `new_table` AS
WITH layer AS ...

EDIT: a complete example

CREATE OR REPLACE TABLE
  `your_project.your_dataset.your_table` AS
WITH
  layer1 AS (
  SELECT
    'this is my CTE' AS txt),
  another_cte AS (
  SELECT
    txt,
    SPLIT(txt, ' ') AS my_array
  FROM
    layer1)
SELECT
  *
FROM
  another_cte

Creates the following table