Dear All,
I have an GA4 dataset on bigquery and because of slow arriving events going to the same event (portioned) table i have to query multiple days to fetch the history. We have a config framework which takes date in the format YYYYMMDD and generates the necessary query. I am trying to generate a query something like below
WITH
changed_records AS (
SELECT
*,
DATE(_CHANGE_TIMESTAMP) AS change_date
FROM
APPENDS(TABLE `project.dataset.events_20240727`,
FORMAT_TIMESTAMP('%FT%T.0000 UTC', TIMESTAMP(PARSE_DATE('%Y%m%d', '20240801'), 'UTC')),
'2024-08-01T23:59:59.999999 UTC'
)
)
select event_date, change_date, count(*)
from changed_records
group by 1, 2 order by change_date
;
i am getting an error on line 8 saying its a not valid signature for APPENDS function. Can you please suggest any workaround for this.
Thanks
Jai Prakash
Solved! Go to Solution.
Hi @jai_prakash,
Welcome to the Google Cloud Community!
It appears the issue stems from the improper use of the APPENDS function. This is likely due to the fact that GA4 event tables in BigQuery are partitioned based on ingestion time rather than event time.
To address this, you should leverage the _PARTITIONTIME pseudo-column in your queries. This column provides the partition date for each row, helping you correctly manage and query the partitioned data.
Additionally, consider using wildcard tables when dealing with multiple tables that have similar names and compatible schemas. This approach simplifies querying across datasets with uniform structures, making data management more efficient.
I hope the above information is helpful.