CODEWITHSUNDEEP
google-bigquerylooker-studio
Matthew Medlyn
Matthew Medlyn

Reputation: 28

Query a table with the table name as variable

I'm building some dashboards in Looker Studio that require 3 of the GA4 daily tables that get added each day from the sync between GA4 and BigQuery.

I want to query those specific tables, as we've been running GA4 for a couple of years now, and there's 503 sub-tables, so table name wildcard queries take forever (55GB) when I only want specific date sub tables.

the tables are added as bigquery project.dataset name.events_intraday_date-of-table as yyyymmddd So for example, todays is my-bigquery-project.analytics-123456789.events_intraday_20241909 yesterdays is my-bigquery-project.analytics-123456789.events_intraday_20241809 last years is my-bigquery-project.analytics-123456789.events_intraday_20231909

and so on

I think I need to write a user-defined table function, with a parameter of today's date, that will then run a query (even if it's select all) against a tempoarary table, returned by (something). There has to be a parameter involved as obviously, today's date changes.

I can write a stored procedure that will return the results for day, no problem, for example, here's a simple query that gets yesterday's purchases by timestamp

EXECUTE IMMEDIATE concat("select event_date, event_name, event_timestamp, from ", concat("my-bigquery-project.analytics-123456789.events_intraday_", format_date('%Y%m%d', date_sub(current_date(), interval 1 day))), " WHERE event_name = 'purchase' group by event_date, event_name, event_timestamp order by event_timestamp ASC/* LIMIT 100 */");

and I can have this as a BigQuery Custom SQL datasource in Looker Studio note the LIMIT comment is necessary as Looker adds a LIMIT to all Custom SQL if it doesn't find it in the query

However, event though Looker connects fine and returns the schema, the table itself is unusuable as Looker then wraps its own query around your Custom SQL, i.e in the example above, it generates in BigQuery

SELECT clmn0_, COUNT(1) AS t0_qt_adhhixz7kd FROM (
SELECT t0.event_date AS clmn0_ FROM (
EXECUTE IMMEDIATE concat("select event_date, event_name, event_timestamp, from ", concat("my-    bigquery-project.analytics-123456789.events_intraday_",
    format_date('%Y%m%d', date_sub(`current_date`(), INTERVAL 1 day))), " WHERE event_name = 'purchase' group by event_date, event_name, event_timestamp order by event_timestamp ASC/* LIMIT 100 */")
) AS t0
) WHERE (clmn0_ >= '20240919' AND clmn0_ <= '20240919') GROUP BY clmn0_ ORDER BY t0_qt_adhhixz7kd       DESC LIMIT 2000001;

which fails with the error Syntax error: Expected keyword JOIN but got identifier "concat" at

And I cannot figure out a way to write anything that returns a table that Looker can wrap its own SQL around

I've read a lot of random answers on stackoverflow, and elsewhere, and I think I have to write a table valued function that accepts a parameter (say, table_name) to do this, however I always get the error that "table_name" doesn't exist in the dataset, because it's not evaluating table_name.

The closest I think is this answer https://stackoverflow.com/questions/6661505/declare-variable-in-table-valued-function but again, I get unstuck at the FROM part.

or here https://stackoverflow.com/questions/74774856/bigquery-sql-table-function-with-string-interpolation but I just have a stored procedure, if I call it as my custom query in Looker, same problem as above.

I think I'm after something like

create table function analytics_123456789.todaystable(x)
AS (
  with CTE AS(
   [would like to execute something here]
  )
  select * from CTE
)

but I can't execute or call within it?

There has to be a way to do this, as otherwise, as more daily tables get created in bigquery, Lookers reports based on BigQuery GA4 tables would become teneable?

Upvotes: 0

Views: 257

Answers (1)

Dami&#227;o Martins
Dami&#227;o Martins

Reputation: 1849

BigQuery supports query multiple tables using a wildcard table using the special column _TABLE_SUFFIX:

select event_date, event_name, event_timestamp
from `my-bigquery-project.analytics-123456789.events_intraday_*`
WHERE 
_TABLE_SUFFIX =  FORMAT_DATE('%Y%m%d', date_sub(current_date(), interval 1 day))
event_name = 'purchase' 
group by event_date, event_name, event_timestamp 
order by event_timestamp ASC
/* LIMIT 100 */

Upvotes: 0

Related Questions