Can I use a query parameter in a table name?

Question

I want to do something along the lines of:

SELECT some_things
FROM `myproject.mydataset.mytable_@suffix`

But this doesn't work because the parameter isn't expanded inside the table name.

This does work, using wildcard tables:

SELECT some_things
FROM `myproject.mydataset.mytable_*`
WHERE _TABLE_SUFFIX = @suffix

However, it has some problems:

• If I mistype the parameter, this query silently returns zero rows, rather than yelling at me loudly.

• Query caching stops working when querying with a wildcard.

• If other tables exist with the mytable_ prefix, they must have the same schema, even if they don't match the suffix. Otherwise, weird stuff happens. It seems like BigQuery either computes the union of all columns, or takes the schema of an arbitrary table; it's not documented and I didn't look at it in detail.

Is there a better way to query a single table whose name depends on a query parameter?

Answer 1

Yes, you can, here's a working example:

DECLARE tablename STRING;
DECLARE tableQuery STRING;

##get list of tables
CREATE TEMP TABLE tableNames as select table_name from nomo_nausea.INFORMATION_SCHEMA.TABLES where table_name not in ('_sdc_primary_keys', '_sdc_rejected', 'fba_all_order_report_data');

WHILE (select count(*) from tableNames) >= 1 DO
  SET tablename = (select table_name from tableNames LIMIT 1);
  ##build dataset + table name
  SET tableQuery = CONCAT('nomo_nausea.' , tablename);
  ##use concat to build string and execute
  EXECUTE IMMEDIATE CONCAT('SELECT * from `', tableQuery, '` where _sdc_deleted_at is not null');
  DELETE FROM tableNames where table_name = tablename;
END WHILE;

Answer 2

In order to answer your stated problems:

1. Table scanning happens in FROM clause, in WHERE clause happens filtering [1] thus if WHERE condition is not match an empty result would be returned.

2. "Currently, Cached results are not supported when querying with wildcard" [2].

3. "BigQuery uses the schema for the most recently created table that matches the wildcard as the schema" [3]. What kind of weird stuff you have faced in your use case? "A wildcard table represents a union of all the tables that match the wildcard expression" [4].

In BigQuery parameterized queries can be run, But table names can not be parameterized [5]. Your wildcard solution seems to be the only way.