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?
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