Update tables with new data

Question

I receive new tables with data in bigquery everyday.

One tables = one date.

For example: costdata_01012018, costdata02012018 and so on.

I have script that union them every day so I have a new tables with all data I need. For now I truncate the final table every day and it doesn't seem right.

Is there any way to union them without truncation?

I just need to add a new table to the final one

I tried to create 'from' instruction that dynamically finds new table but it doesn't work.

SELECT date, adcost
FROM CONCAT('[test-project-187411:dataset.CostData_', STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, "day"), "%Y%m%d"), ']')

What am I doing wrong?

Instead of trying to use dynamic table names (=lots of issues), could you have that date as an extra key-field in the single "costdata" table? — Hans Keﬆing

Alexey Maloletkin Alexey Maloletkin · Accepted Answer · 2018-05-16T20:33:57

Two options to do this:

#standardsql
SELECT date, adcost
FROM `test-project-187411:dataset.CostData_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Legacy SQL

#legacysql
SELECT date, adcost
FROM TABLE_QUERY([test-project-187411:dataset], 'tableid = CONCAT("CostData_", STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, "day"), "%Y%m%d")')

Update tables with new data

1 Answers