BigQuery -firebase export working different when using wildcard character and _TABLE_SUFFIX compared to without using it

user_9718479 0
Viewed 84

My Requirement: To append unnested data in a separate table and use it for visualization and analytics

Implementing it : As I am not sure at what time exactly events_intraday_YYYYMMDD syncs into events_YYYYMMDD for reference check here

0- Created an events_normalized table once at the start by using (It is done once not daily)

  create analytics_data_export.events_normalized AS
SELECT .....
FROM
    `analytics_xxxxxx.events_*

to collect all the data from events_YYYYMMDD

1- Creating/Replacing a daily temp table with

create or replace table analytics_data_export.daily_data_temp AS
SELECT...
_TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)) AND
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

as I have seen multiple days data syncing together so to be on the safe side I am using 1-4 days data

2- Deleting the inner join of both the tables(daily_data_temp,events_normalized) from events_normalized to remove any duplicates it might have like let's say events_normalized has data till 18th but daily_data_temp has data from 16-19th so all the rows till 18th from events_normalized will be removed

4- Reinserting daily_data_temp in the events_normalized

Questions:

1- Is there any optimized way of implementing the requirements

2- In the 0th step while creating events_normalized table if I use :

WHERE
  _TABLE_SUFFIX  <=
  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))

I get different results as compared to when I am using

create analytics_data_export.events_normalized AS
    SELECT .....
    FROM
        `analytics_xxxxxx.events_*

The difference is the latter one has the current date data as well wherein events_YYYYMMDD I can only see data of yesterday. I don't understand this behavior

Like if the current day is 20th July in events_YYYYMMDD I can see only till events_20200719

firebase google-bigquery google-analytics google-cloud-platform
1 Answers

  1. To optimize you can follow below steps:

    1. Create hash out of event_time_stamp and other unique fields, use this to filter the data
    2. Instead of deleting duplicate rows from the larger initial table delete them from small temp table and then insert the table.

  2. its because the filter analytics_xxxxxx.events_* will match both per day events table and intraday event tables which are name like events_intraday_20200721

user_31258000
Related