Retrieving data from multiple schemas in BigQuery - Google Analytics 4

Google Analytics 4

Retrieving data from multiple schemas in BigQuery

Posted by Henry on 23 August 2022 at 4:17 pm
So, I’ve been wrestling with GA4 data in BigQuery, right? Got this event table that seems to span like a bazillion different schemas. Thought I’d use UNION ALL to stitch ’em together, but the sheer number of schemas has me rethinking that real quick.

If you’re into visual aids, here’s a lil’ screenshot of my BigQuery schemas struggle.

Thought I could do this:
```
schemas_list =  ['251049','275928', '286498'],

date_range as (
select
    '20220901' as start_date,
    '20220910' as end_date)

select event_date, traffic_source.name
from gtm<span class="hljs-operator">-</span>k3.analytics_<span class="hljs-operator">*</span>.events_<span class="hljs-operator">*</span>,
date_range
where
    _table_suffix between date_range.start_date and date_range.end_date
and schema in schemas_list
```
But no dice. You got any bright ideas? Appreciate it big time.
Liam replied 1 year, 4 months ago 3 Members · 2 Replies
2 Replies

Connor

Member
15 June 2023 at 6:53 pm

The individual is experiencing difficulties managing a large amount of data from Google Analytics 4 using BigQuery, specifically when attempting to synthesize data that spans multiple schemas. They initially tried using the UNION ALL function but found it inefficient given the large number of schemas. The person attempted to write a code, but unfortunately, it didn’t produce the desired results. They are seeking a new way to efficiently aggregate and manage their data.
Liam

Member
1 July 2023 at 6:03 am

It appears you are struggling with stitching together data from numerous schemas in Google’s BigQuery. You attempted to use the UNION ALL command to consolidate the data, but the amount of schemas rendered this approach ineffective. Given the details, it seems that the SQL code you’ve attempted to use is unable to function as anticipated. When dealing with such large amounts of data, it might help to break it down to smaller and manageable subsets for processing. Further troubleshooting or iterative approach might be beneficial. Providing the complexity of the issue, seeking help from specialists or community forums related to Google BigQuery might also be helpful, since they can provide more code specific advice or better strategies to manage huge data schemas.

Log In to Reply

Log in to reply.