GBQ Error when inserting data into a table

im fetching some data from different tables, merging everything into one table and when i try to insert the data into the main prod table i get an error saying the INSERT statement is incorrect.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>WITH all_domains AS (
SELECT 'dom1.com' AS domain UNION ALL
SELECT 'dom2.com' AS domain UNION ALL
SELECT 'dom3.com' AS domain UNION ALL
SELECT 'dom4.com' AS domain UNION ALL
SELECT 'dom5.com' AS domain
),
-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM dataset.table_1 t
WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE()
GROUP BY keyword_id, requested
),
latest_filtered_rankings_results AS (
SELECT
-- keyword metrics
keyword_id,
requested,
phrase,
device,
location_type,
location,
keyword_groups,
keyword_metrics.adwords_search_volume AS search_volume,
-- result primary key
result.pack_rank AS pack_rank,
result.item_rank AS item_rank,
-- result data
result.title.text as title,
result.url.domain as domain,
result.url.url as url,
-- ranking data
result.page AS page,
result.rank AS ranking_position,
result.paid_adjusted_rank AS ranking_position_incl_paid,
-- calculate organic rank
IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL,
DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank),
NULL
) AS ranking_position_classic,
-- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
result.measurements.is_visible AS is_visible,
-- calculated metrics
result.click_through_rate AS ctr,
result.estimated_traffic AS estimated_traffic,
-- SERP feature or organic
result.featured_snippet.is_featured_snippet,
result.organic.is_organic,
CASE
WHEN result.rank IS NULL THEN null
WHEN result.rank BETWEEN 1 AND 1 THEN '1'
WHEN result.rank BETWEEN 2 AND 3 THEN '2-3'
WHEN result.rank BETWEEN 4 AND 10 THEN '4-10'
WHEN result.rank BETWEEN 11 AND 20 THEN '11-20'
WHEN result.rank BETWEEN 21 AND 50 THEN '21-50'
ELSE 'above 50'
END AS ranking_group
FROM latest_filtered_rankings
JOIN UNNEST(results) AS result
JOIN dataset.table_2 USING (keyword_id)
QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com')
),
distinct_rankings AS (
SELECT
-- keyword metrics
keyword_id,
requested,
domain,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(device) AS device,
ANY_VALUE(location_type) AS location_type,
ANY_VALUE(location) AS location,
ANY_VALUE(country) AS country,
ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume,
ANY_VALUE(keyword_groups) AS keyword_groups,
FROM latest_filtered_rankings lfr
JOIN dataset.table_2 USING (keyword_id)
CROSS JOIN all_domains
GROUP BY keyword_id, requested, all_domains.domain
),
metrics AS (
-- join tables
SELECT
dr.keyword_id,
dr.requested,
dr.phrase,
dr.device,
dr.location_type,
dr.location,
dr.country,
dr.search_volume,
dr.keyword_groups,
lfrr.pack_rank,
lfrr.item_rank,
lfrr.title,
dr.domain,
lfrr.url,
lfrr.page,
lfrr.ranking_position,
lfrr.ranking_position_incl_paid,
lfrr.ranking_position_classic,
lfrr.ranking_group,
lfrr.is_visible,
lfrr.ctr,
lfrr.estimated_traffic,
lfrr.is_featured_snippet,
lfrr.is_organic,
--flag the top organic position (incl. featured snippets)
ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking
FROM distinct_rankings dr
LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain)
),
INSERT INTO `final.table` (
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
)
(SELECT
keyword_id,
requested,
phrase,
device,
location_type,
location,
country,
search_volume,
keyword_groups,
pack_rank,
item_rank,
title,
domain,
url,
page,
ranking_position,
ranking_position_incl_paid,
ranking_position_classic,
ranking_group,
is_visible,
ctr,
estimated_traffic,
is_featured_snippet,
is_organic,
is_top_ranking
FROM metrics
WHERE 1 = 1
AND EXTRACT(YEAR FROM requested) = 2024
AND EXTRACT(MONTH FROM requested) = 1)
</code>
<code>WITH all_domains AS ( SELECT 'dom1.com' AS domain UNION ALL SELECT 'dom2.com' AS domain UNION ALL SELECT 'dom3.com' AS domain UNION ALL SELECT 'dom4.com' AS domain UNION ALL SELECT 'dom5.com' AS domain ), -- grabbing the latest version of each serp in case of reparse latest_filtered_rankings AS ( SELECT AS VALUE ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)] FROM dataset.table_1 t WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE() GROUP BY keyword_id, requested ), latest_filtered_rankings_results AS ( SELECT -- keyword metrics keyword_id, requested, phrase, device, location_type, location, keyword_groups, keyword_metrics.adwords_search_volume AS search_volume, -- result primary key result.pack_rank AS pack_rank, result.item_rank AS item_rank, -- result data result.title.text as title, result.url.domain as domain, result.url.url as url, -- ranking data result.page AS page, result.rank AS ranking_position, result.paid_adjusted_rank AS ranking_position_incl_paid, -- calculate organic rank IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL, DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank), NULL ) AS ranking_position_classic, -- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc) result.measurements.is_visible AS is_visible, -- calculated metrics result.click_through_rate AS ctr, result.estimated_traffic AS estimated_traffic, -- SERP feature or organic result.featured_snippet.is_featured_snippet, result.organic.is_organic, CASE WHEN result.rank IS NULL THEN null WHEN result.rank BETWEEN 1 AND 1 THEN '1' WHEN result.rank BETWEEN 2 AND 3 THEN '2-3' WHEN result.rank BETWEEN 4 AND 10 THEN '4-10' WHEN result.rank BETWEEN 11 AND 20 THEN '11-20' WHEN result.rank BETWEEN 21 AND 50 THEN '21-50' ELSE 'above 50' END AS ranking_group FROM latest_filtered_rankings JOIN UNNEST(results) AS result JOIN dataset.table_2 USING (keyword_id) QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com') ), distinct_rankings AS ( SELECT -- keyword metrics keyword_id, requested, domain, ANY_VALUE(phrase) AS phrase, ANY_VALUE(device) AS device, ANY_VALUE(location_type) AS location_type, ANY_VALUE(location) AS location, ANY_VALUE(country) AS country, ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume, ANY_VALUE(keyword_groups) AS keyword_groups, FROM latest_filtered_rankings lfr JOIN dataset.table_2 USING (keyword_id) CROSS JOIN all_domains GROUP BY keyword_id, requested, all_domains.domain ), metrics AS ( -- join tables SELECT dr.keyword_id, dr.requested, dr.phrase, dr.device, dr.location_type, dr.location, dr.country, dr.search_volume, dr.keyword_groups, lfrr.pack_rank, lfrr.item_rank, lfrr.title, dr.domain, lfrr.url, lfrr.page, lfrr.ranking_position, lfrr.ranking_position_incl_paid, lfrr.ranking_position_classic, lfrr.ranking_group, lfrr.is_visible, lfrr.ctr, lfrr.estimated_traffic, lfrr.is_featured_snippet, lfrr.is_organic, --flag the top organic position (incl. featured snippets) ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking FROM distinct_rankings dr LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain) ), INSERT INTO `final.table` ( keyword_id, requested, phrase, device, location_type, location, country, search_volume, keyword_groups, pack_rank, item_rank, title, domain, url, page, ranking_position, ranking_position_incl_paid, ranking_position_classic, ranking_group, is_visible, ctr, estimated_traffic, is_featured_snippet, is_organic, is_top_ranking ) (SELECT keyword_id, requested, phrase, device, location_type, location, country, search_volume, keyword_groups, pack_rank, item_rank, title, domain, url, page, ranking_position, ranking_position_incl_paid, ranking_position_classic, ranking_group, is_visible, ctr, estimated_traffic, is_featured_snippet, is_organic, is_top_ranking FROM metrics WHERE 1 = 1 AND EXTRACT(YEAR FROM requested) = 2024 AND EXTRACT(MONTH FROM requested) = 1) </code>
WITH all_domains AS (
SELECT 'dom1.com' AS domain UNION ALL
  SELECT 'dom2.com' AS domain UNION ALL
  SELECT 'dom3.com' AS domain UNION ALL
  SELECT 'dom4.com' AS domain UNION ALL
  SELECT 'dom5.com' AS domain
),

-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM dataset.table_1 t
  WHERE DATE(requested) BETWEEN '2023-06-01' AND CURRENT_DATE()
  GROUP BY keyword_id, requested
),


latest_filtered_rankings_results AS (
  SELECT
   -- keyword metrics
    keyword_id,
    requested,
    phrase,
    device,
    location_type,
    location,
    keyword_groups,
    keyword_metrics.adwords_search_volume AS search_volume,

    -- result primary key
    result.pack_rank AS pack_rank,
    result.item_rank AS item_rank,

    -- result data
    result.title.text as title,
    result.url.domain as domain,
    result.url.url as url,

    -- ranking data
    result.page AS page,
    result.rank AS ranking_position,
    result.paid_adjusted_rank AS ranking_position_incl_paid,

    -- calculate organic rank
    IF(result.organic.is_organic=TRUE AND result.featured_snippet.is_featured_snippet IS NULL,
      DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.organic.is_organic=TRUE OR result.featured_snippet.is_featured_snippet=TRUE ORDER BY result.rank),
      NULL
    ) AS ranking_position_classic,

    -- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
    result.measurements.is_visible AS is_visible,

    -- calculated metrics
    result.click_through_rate AS ctr,
    result.estimated_traffic AS estimated_traffic,

    -- SERP feature or organic
    result.featured_snippet.is_featured_snippet,
    result.organic.is_organic,

    CASE
        WHEN result.rank IS NULL THEN null
        WHEN result.rank BETWEEN 1 AND 1 THEN '1'
        WHEN result.rank BETWEEN 2 AND 3 THEN '2-3'
        WHEN result.rank BETWEEN 4 AND 10 THEN '4-10'
        WHEN result.rank BETWEEN 11 AND 20 THEN '11-20'
        WHEN result.rank BETWEEN 21 AND 50 THEN '21-50'
        ELSE 'above 50'
    END AS ranking_group

  FROM latest_filtered_rankings
  JOIN UNNEST(results) AS result
  JOIN dataset.table_2 USING (keyword_id)
  QUALIFY result.url.domain IN ('dom1.com','dom2.com', 'dom3.com', 'dom4.com', 'dom5.com')
),

distinct_rankings AS (
  SELECT
   -- keyword metrics
    keyword_id,
    requested,
    domain,
    ANY_VALUE(phrase) AS phrase,
    ANY_VALUE(device) AS device,
    ANY_VALUE(location_type) AS location_type,
    ANY_VALUE(location) AS location,
    ANY_VALUE(country) AS country,
    ANY_VALUE(keyword_metrics.adwords_search_volume) AS search_volume,
    ANY_VALUE(keyword_groups) AS keyword_groups,
  FROM latest_filtered_rankings lfr
  JOIN dataset.table_2 USING (keyword_id)
  CROSS JOIN all_domains 
  GROUP BY keyword_id, requested, all_domains.domain
),

metrics AS (
  -- join tables
  SELECT 
      dr.keyword_id,
      dr.requested,
      dr.phrase,
      dr.device,
      dr.location_type,
      dr.location,
      dr.country,
      dr.search_volume,
      dr.keyword_groups,
      lfrr.pack_rank,
      lfrr.item_rank,
      lfrr.title,
      dr.domain,
      lfrr.url,
      lfrr.page,
      lfrr.ranking_position,
      lfrr.ranking_position_incl_paid,
      lfrr.ranking_position_classic,
      lfrr.ranking_group,
      lfrr.is_visible,
      lfrr.ctr,
      lfrr.estimated_traffic,
      lfrr.is_featured_snippet,
      lfrr.is_organic,

      --flag the top organic position (incl. featured snippets)
      ROW_NUMBER() OVER (PARTITION BY keyword_id, requested,dr.domain ORDER BY CASE WHEN lfrr.ranking_position IS NOT NULL THEN lfrr.ranking_position ELSE 1e6 END) = 1 AS is_top_ranking
  FROM distinct_rankings dr
  LEFT JOIN latest_filtered_rankings_results lfrr USING (keyword_id, requested, domain)
),

INSERT INTO `final.table` (
  keyword_id,
  requested,
  phrase,
  device,
  location_type,
  location,
  country,
  search_volume,
  keyword_groups,
  pack_rank,
  item_rank,
  title,
  domain,
  url,
  page,
  ranking_position,
  ranking_position_incl_paid,
  ranking_position_classic,
  ranking_group,
  is_visible,
  ctr,
  estimated_traffic,
  is_featured_snippet,
  is_organic,
  is_top_ranking
)
(SELECT 
  keyword_id,
  requested,
  phrase,
  device,
  location_type,
  location,
  country,
  search_volume,
  keyword_groups,
  pack_rank,
  item_rank,
  title,
  domain,
  url,
  page,
  ranking_position,
  ranking_position_incl_paid,
  ranking_position_classic,
  ranking_group,
  is_visible,
  ctr,
  estimated_traffic,
  is_featured_snippet,
  is_organic,
  is_top_ranking
FROM metrics 
WHERE 1 = 1
  AND EXTRACT(YEAR FROM requested) = 2024
  AND EXTRACT(MONTH FROM requested) = 1)

Can anyone help please?

Errors i get: Syntax error: Expected keyword AS but got keyword INTO at [163:8] (if add a comma before INSERT INTO)
Syntax error: Unexpected keyword INSERT at [163:1] (if i dont add a comma)

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật