Flattening BigQuery tables to Analyze in Looker

[Alison_Nguyen]

Hi all,

I am trying to bring the data from my company’s app in BigQuery into Looker so I can then create visualizations out of the data.

The data in BigQuery has a separate table for every single day. The columns are all the same so I’m trying to figure out if there is a way to join all of the individual tables from each day. When I bring the data into Looker, each day is a separate view. Then I am unable to compare data over a period of time because its all separate. I provided some photos for context on how the data looks right now.

I’m thinking that another company building an app may have come across this issue before and may be able to share what they did.

[PowdyPowPow]

Hi Alison,

Looks like you are dealing with a sharded table in BigQuery. You don’t need to create a separate view for each day in Looker. Instead you can take advantage of wildcards and create one view that references a table like so:

sql_table_name: your_project.your_dataset.your_table_* ;;

Finally you should also create a dimension that will reflect the date of when the table was loaded;

dimension_group: day {
type: time
timeframes: [date, month, year]
sql: TIMESTAMP(PARSE_DATE(’%Y%m%d’, REGEXP_EXTRACT(_TABLE_SUFFIX,r’\d\d\d\d\d\d\d\d’))) ;;
}

And then consider using that dimension in an always_filter of your explore. That way you can control the amount of data that gets processed and ultimately build.

Finally if this is Firebase, you might want to check out the prebuilt Looker block for it here

Hope that helps,
Andrej

[aiden2]

Adding on to this answer:

If you need to reference the text that is in the wildcard (i.e. if your table does not have a ‘date’ column and the table name is the only place where that info is encoded), you can reference the wildcard text with: _TABLE_SUFFIX

sql: SELECT , TABLE_SUFFIX as table_suffix FROM your_project.your_dataset.your_table ;;