CODEWITHSUNDEEP
sqlgoogle-bigquery
disruptive
disruptive

Reputation: 5946

Creating a table from a prior WITH clause in BigQuery

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.

Upvotes: 1

Views: 4507

Answers (1)

Cylldby
Cylldby

Reputation: 1978

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

enter image description here

Upvotes: 5

Related Questions