1

I have a series of tables that are named {YYYYMM}_{id} and I have ML models that are named {groupid}_cost_model. I'm attempting to collate some data across all the tables using the following query:

SELECT * FROM `mydataset.20*`

The problem I'm having is that I have a model named 200_cost_model and it causes the following error:

Wildcard table over non partitioning tables and field based partitioning tables is not yet supported, first normal table myproject:mydataset.200_cost_model, first column table myproject:mydataset.202001_4544248676.

Is there a way to filter out the models from wildcard queries or am I stuck joining all the tables together?

Wytrzymały Wiktor
  • 11,492
  • 5
  • 29
  • 37
Woody1193
  • 7,252
  • 5
  • 40
  • 90

1 Answers1

0

When using Wildcard tables you can use psuedo column to filter results:

Queries with wildcard tables support the _TABLE_SUFFIX pseudo column in the WHERE clause. This column contains the values matched by the wildcard character, so that queries can filter which tables are accessed. For example, the following WHERE clauses use comparison operators to filter the matched tables

I have tested on my side, although only on standard freshly created tables, that it should work for example like that:

SELECT *
FROM
  `mydataset.20*`
WHERE
    _TABLE_SUFFIX like '%cost_model' ;

As well to check all possible _TABLE_SUFFIX choices it work to me like this:

select  DISTINCT   _TABLE_SUFFIX as suffix from  `mydataset.20*`

but I am not sure, if this will work in your situation.

vitooh
  • 4,132
  • 1
  • 5
  • 16