Search code examples
sqlgoogle-bigquerytableau-api

Selecting entire tables based on wildcard name values SQL - Tableau

What I'm trying to do is to pull out a subset of tables from a Google Big Query Dataset based on the name of those tables and then add those tables to a Tableau datasource without having to join or union any of the tables.

I want to pull out all the tables beginning with System_1, from the below dataset

  1. System_1_Start
  2. System_1_Middle
  3. System_1_End
  4. System_2_Start
  5. System_2_Middle
  6. System_2_End
  7. System_3_Start
  8. System_3_Middle
  9. System_3_End

I have been able to do a wildcard search using Information_Schema.Tables to get all the names of the tables that begin with System_1, but I cannot figure out a way to then get all of the tables with those names as the output of the query (SQL below)

SELECT table_name
AS matchingTables
FROM dataset.INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'System_1%'

How do I go about extracting those tables and not just the names of those tables?

~~~~~~~~~~~EDIT~~~~~~~~~~~~~

This is the best approximation as to how I could have done this but I'm getting a dataset not found error which is strange

SELECT *
FROM dataset
WHERE (SELECT table_name FROM dataset.INFORMATION_SCHEMA.TABLES)
LIKE 'System_1%'

Error Recieved:

Not found: Dataset dataset:dataset was not found in location EU

Solution

  • If I understand correctly you want the list of the table names in your dataset that start with "System_1_". You can obtain that with the following:

    SELECT CONCAT("System_1_", _TABLE_SUFFIX) AS table_name
FROM `dataset.System_1_*`