2

I want to list the tables which can be used as wildcards in BigQuery.

My dataset has the table list is similar to the following:

events_122022
events_122021
events_122020
...
...
events_112012
...
...

analytics_122022
analytics_122021
analytics_122020
...
...
analytics_112012

These tables are created dynamically and I have no information on the used table prefix Is there a way to find the list of tables which can be used dynamically?

The result should be:[events_, analytics_]

My attempt:

Find the tables with similar DDL using the following SQL

   SELECT
    SUBSTR(ddl, STRPOS(ddl, '(')) as commonDDL,
    STRING_AGG(table_name) as table
  FROM
    dataset.INFORMATION_SCHEMA.TABLES
  GROUP BY SUBSTR(ddl, STRPOS(ddl, '('))

This gives the output as :

commonDDL table
(ID STRING, ...) events_122022, events_122021, ...
(NAME STRING, ...) analytics_112022, analytics_112021 ...

Now using a Longest common shared start algorithm I can find the required result. (Longest common start code here )

What are the other ways we can approach this problem?

Couldn't find anything on BigQuery docs.

Note: I only have readonly permission for the BigQuery dataset

bhalu007
  • 131
  • 1
  • 10

1 Answers1

0

What about finding your table with some regex:

select table_name
from yourds.INFORMATION_SCHEMA.TABLES
where regexp_contains(table_name, "_[0-2]+") is true
pmo511
  • 569
  • 3
  • 9
  • Won't this just return the table names as `events_122022 , events_112012 , events_112013` . I wanted to find the tables as `[events_, analytics_]`. – bhalu007 Aug 05 '22 at 07:46
  • Oh. I didn't get that you wanted the result to be in an array. Have a look at this an let me know how you go? https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#constructing_arrays – pmo511 Aug 05 '22 at 16:42