BigQuery querying append only tables with Facebook ads insights return Column website_ctr of type ARRAY cannot be used in SELECT DISTINCT

Question

I am syncing all Facebook insights to BigQuery. This is an append-only table which means that I have to fetch the latest batch. So I’m trying to select the latest batch by using campaign_id as the primary key. But when I try to do that with the query from the docs I get this error. Does anyone know how to query the latest batch from BigQuery?

I'm using stichdata's integration to push the data to BigQuery. References: https://www.stitchdata.com/docs/data-structure/querying-append-only-tables#latest-version-every-row https://www.stitchdata.com/docs/integrations/saas/facebook-ads#ads-insights

Here is the query issue from BigQuery

Column website_ctr of type ARRAY cannot be used in SELECT DISTINCT

Here is the query I’m using

SELECT DISTINCT o.* FROM `xxx.facebook_ads.ads_insights` o
INNER JOIN (
     SELECT campaign_id,
            MAX(_sdc_sequence) AS seq,
            MAX(_sdc_batched_at) AS batch
    FROM `xxx.facebook_ads.ads_insights`
    GROUP BY campaign_id) oo
ON o.campaign_id = oo.campaign_id
AND o._sdc_sequence = oo.seq
AND o._sdc_batched_at = oo.batch

Answer 1

Try below for BigQuery Standard SQL

#standardSQL
SELECT ANY_VALUE(o).*
FROM `xxx.facebook_ads.ads_insights` o
INNER JOIN (
  SELECT campaign_id,
    MAX(_sdc_sequence) AS seq,
    MAX(_sdc_batched_at) AS batch
  FROM `xxx.facebook_ads.ads_insights`
  GROUP BY campaign_id
) oo
ON o.campaign_id = oo.campaign_id
AND o._sdc_sequence = oo.seq
AND o._sdc_batched_at = oo.batch
GROUP BY o.campaign_id 

As you can see, the change is really in the first line of your code (in SELECT statement) and extra line at the end of the query (GROUP BY) - so relatively simple fix

I am not familiar with Facebook insights data so cannot test above - but for simple cases that I could reverse engineer from details in the question - it should work!