CODEWITHSUNDEEP
google-bigquery
Mathias Barea
Mathias Barea

Reputation: 31

BigQuery _TABLE_SUFFIX is empty/missing tables

Thanks in advance for taking a look into this, hope someone can help.

I am creating tables with fixed prefix + dynamic suffix something like: name123456 in which name is fixed/static and 123456 is an incremental numeric value

I currently have multiple tables like: name123456 name123457 name123458

And I am trying to dynamically query the most recent one (which is the one with the biggest suffix), in the given example it's "name123458".

When running the query below in the BigQuery UI:

#standardsql
select array_agg(distinct _TABLE_SUFFIX) from `project.dataset.name*`

I get no result, and (as far as I understand) I should get all the listed tables above.

I know to get the most recent one I need to use a WHERE clause with max(_TABLE_SUFFIX) but since I am getting an empty _TABLE_SUFFIX I can not get anything from it.

Let me know if more information is required and I'll update as needed.

Upvotes: 1

Views: 868

Answers (2)

Mathias Barea
Mathias Barea

Reputation: 31

I found the solution by myself so I'll share the solution here as an answer, but first, thanks to David and Martin Weitzmann for their time and help.

The problem with _TABLE_SUFFIX ignoring some tables/not returning something was that the tables I had in the dataset were all empty tables (just schema).

That's it, _TABLE_SUFFIX ignores empty tables, hope it helps someone else.

Upvotes: 2

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

You can't use _TABLE_SUFFIX in your SELECT statement - only in the WHERE clause. But you can instead use metatables to find the most recent one: https://cloud.google.com/bigquery/docs/information-schema-tables

Upvotes: 0

Related Questions