CODEWITHSUNDEEP
sqlgoogle-bigqueryinformation-schema
Velizar VESSELINOV
Velizar VESSELINOV

Reputation: 2354

BigQuery: query multi schemas for a specific table

Schema tables:

 1. AA
 2. AB
 3. BB
 4. BA
 5. ...

Target: search for a table that can be in any of the schemas?

Desired query:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'xyz' and schema_name LIKE '%A'

Works but need to repeat manually for all the schemas:

SELECT * FROM AA.INFORMATION_SCHEMA.TABLES WHERE table_name = 'xyz'

Upvotes: 1

Views: 801

Answers (3)

Velizar VESSELINOV
Velizar VESSELINOV

Reputation: 2354

After some tests, found a way to write SQL code that is covering the request.

Maybe there are options for improvements.

Another observation surprised that it is taking 3 minutes for 160 database check if the table exists.

DECLARE dbs ARRAY<STRING>;
DECLARE db STRING;
DECLARE query STRING;
DECLARE table_name STRING;
DECLARE i INT64;

SET dbs = (
  SELECT ARRAY_AGG(schema_name)
  FROM INFORMATION_SCHEMA.SCHEMATA
  WHERE schema_name LIKE '%A'
);

SET i = ARRAY_LENGTH(dbs);

LOOP
  SET i = i - 1;
  SET db = dbs[OFFSET(i)];
  EXECUTE IMMEDIATE FORMAT("""
    SELECT table_name FROM
    `%s.INFORMATION_SCHEMA.TABLES`
    WHERE table_name = 'xyz'""", db) INTO table_name;
  
  IF table_name IS NOT NULL THEN
    IF query IS NULL THEN
      SET query = CONCAT('SELECT * FROM ', db, '.', table_name);
    ELSE SET query = CONCAT(query, ' UNION ALL \nSELECT * FROM ', db, '.', table_name);
    END IF;
  END IF;
  IF i <= 0 THEN
    LEAVE;
  END IF;
END LOOP;

EXECUTE IMMEDIATE query

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

You should use region qualifier - in this case you will get ALL tables in respective region vs. tables in just default or specified dataset (if you use FROM INFORMATION_SCHEMA.TABLES or FROM myDataset.INFORMATION_SCHEMA.TABLES respectively)

So, to your version of "desired query" you should just simply add region name like in below example (also note use of table_schema field vs. schema_name)

SELECT * 
FROM region-us.INFORMATION_SCHEMA.TABLES 
WHERE table_name = 'xyz' 
AND table_schema LIKE '%A'

Please note: you need to make sure you have proper permissions set to view metadata. For example, I've added BigQuery Metadata Viewer Role for this to work

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Are you looking for filtering on the table_schema? I'm not sure what the exact logic is, perhaps:

where contains(table_schema, '^[AB]+$')

Upvotes: 0

Related Questions