How do I generate a table name that contains today’s date?

It may seem a little strange, but there are already tables with names for each date.

In my project, I have tables for each date to make statistics easier to handle.

Of course, I don’t think this is always the best way, but this is the table structure for my project. (It’s a common technique in Google BigQuery and Amazon Athena. This question is about Google BigQuery)

So to get the data, I want to generate today’s date. If I use TODAY, I can get the data of the latest day without rewriting the code even if it is the next day.

I tried, but the code didn’t work.

Not work 1:

CONCAT in FROM

SELECT
  *
FROM
  CONCAT('foo_', FORMAT_TIMESTAMP('%Y%m%d', CURRENT_TIMESTAMP(), 'Asia/Tokyo'))

Error:

Table-valued function not found: CONCAT at [4:3]

Not work 2:

create temporary function:

create temporary function getTableName() as (CONCAT('foo_', FORMAT_TIMESTAMP('%Y%m%d', CURRENT_TIMESTAMP(), 'Asia/Tokyo')));

Error:

CREATE TEMPORARY FUNCTION statements must be followed by an actual query.

Question

How do I generate a table name that contains TODAY‘s date?

Answer

In this case, I would recommend you to use Wild tables in BigQuery, which allows you to use some features in Standard SQL.

With Wild Tables you can use _TABLE_SUFFIX, it grants you the ability to filter/scan tables containing this parameter. The syntax would be as follows:

SELECT *
FROM `test-proj-261014.sample.test_*` 
where _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE)

I hope it helps.