How do I query a specific range of Firebase's analytics table using Data Studio's date parameters?

Question

I've been reading up on how to query a wildcard table in BigQuery, but Data Studio doesn't seem to recognize the _TABLE_SUFFIX keyword.

I'm trying to use the recently added date parameters for a custom query in Data Studio. The goal is to prevent the custom query from scanning all partitions to save time.

When using the following query:

SELECT
  *
FROM
  `project-name.analytics_196324132.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN DS_START_DATE AND DS_END_DATE

I receive the following error:

Unrecognized name: _TABLE_SUFFIX

I expected the suffix keyword to be recognized so that the custom query is more efficient. But I get this error message. Does Data Studio not yet support this? Or is there another way?

rsantiago rsantiago · Accepted Answer · 2019-06-27T16:17:44

It could be possible that you are setting the query in the wrong place. I created a DataSource from a Custom Query and the wildcard worked. The query I tested was the following, similar to yours since _TABLE_SUFFIX is a wildcard that is available in standardSQL in BigQuery:

select  
    * 
from 
    `training_project.training_dataset.table1_*` 
where  
    _TABLE_SUFFIX BETWEEN '20190625' AND '20190626'

As per your comments you are trying to add a query in the formula field of a custom parameter, however the formula field only accepts basic math operations, functions, and branching logic.

The workaround I can see is to build a select query and use it as a Custom Query in the Data Source definition so that the query can calculate any extra fields in advance (steps 5,6 and 7 from this tutorial).

How do I query a specific range of Firebase's analytics table using Data Studio's date parameters?

1 Answers