CODEWITHSUNDEEP
google-bigqueryreportinglooker-studio
gip
gip

Reputation: 121

Google Looker Studio - Google Data Studio | Bad performance using table_suffix filter in BigQuery data source

CONTEXT

Hi, in bigquery, I have a table that is partitioned by an integer that can be from 0 to 999. Every time I use this data source in Looker Studio for reporting, I filter this column using a parameter to get the right partition; after that, another filter is used on the date column. The queries are fast but very expensive.

GOAL

To reduce cost, I divided the table into 1000 wildcard tables in my big query project and use the date as a partition for all of them.

So,

In the Looker Studio, I changed the custom query for the data source from:

SELECT a.*
FROM `my_project.big_table` AS a
WHERE a.date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
      AND a.id = @id1     
      AND a.user_email =    @DS_USER_EMAIL

to :

SELECT a.*
FROM `my_project.table_*` AS a
WHERE a.date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
      AND a._TABLE_SUFFIX = @id1      
      AND a.user_email =    @DS_USER_EMAIL

ISSUE DESCRIPTION

the change above caused a dramatic issue in the performance of the dashboard. Every page now spends more than 5' to give me results, before the pages were loaded in less than 10''.

I tried to use:

When I try to use direct one of the 1k table suffixes, for example id 400:

SELECT a.*
FROM `my_project.table_400` AS a
WHERE a.date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
      AND a.user_email =    @DS_USER_EMAIL

the performances are exactly the same as before, but, I must filter for reporting.

I know that the wildcard tables are limited in many aspects( cache for example) but, testing the query on BigQuery, the time spent is 0/1 second.

Is there something that I miss/I can change on the query? Do you have some advice/suggestions?

Many thanks!

Upvotes: 0

Views: 869

Answers (1)

Scott B
Scott B

Reputation: 2944

As OP mentioned, this was solved using Table Function.

Table Function behaves just like a VIEW but can be used dynamically because it can accept parameters.

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.

Upvotes: 0

Related Questions