Bigquery Select _Tables_ from All Tables Within Project

BigQuery select __TABLES__ from all tables within project?

__TABLES__ syntax is supported only for specific dataset and does not work across datasets

What you can do is something as below

#standardSQL
WITH ALL__TABLES__ AS (
  SELECT * FROM `bigquery-public-data.1000_genomes.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.baseball.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.bls.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.census_bureau_usa.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.cloud_storage_geo_index.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.cms_codes.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.common_us.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.fec.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.genomics_cannabis.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.ghcn_d.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.ghcn_m.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.github_repos.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.hacker_news.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.irs_990.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.medicare.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.new_york.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.nlm_rxnorm.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.noaa_gsod.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.open_images.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.samples.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.san_francisco.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.stackoverflow.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.usa_names.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.utility_us.__TABLES__` 
)
SELECT *
FROM ALL__TABLES__

In this case you need to know in advance list of datasets, which you can easily do via Datasets: list API or using respective bq ls

Please note: above approach will work only for datasets with data in same location. If you have datasets with data in different locations you will need to query them in two different queries

For example:

#standardSQL
WITH ALL_EU__TABLES__ AS (
  SELECT * FROM `bigquery-public-data.common_eu.__TABLES__` UNION ALL
  SELECT * FROM `bigquery-public-data.utility_eu.__TABLES__` 
)
SELECT *
FROM ALL_EU__TABLES__

How to query all tables in dataset and add an identifier?

You're using a wildcard query, which support a special _TABLE_SUFFIX identifier. Most use it for filtering the set of matched tables, but you can project it as a result column as well.

More info here: https://cloud.google.com/bigquery/docs/querying-wildcard-tables

Something like this:

SELECT
  _TABLE_SUFFIX as src_tbl,
  * 
FROM `project.dataset.*`

Query all datasets and tables within project on bigquery?

Unfortunately, you can't write a single query that can query all tables in all datasets without knowing the dataset names beforehand.

However, if you can programatically construct your query, you can use BigQuery's datasets.list API to get all the dataset names, and then construct a query that will get all tables within those datasets using table wildcards as described above.

Query for listing Datasets and Number of tables in Bigquery

You can do this using some procedural language as follows:

CREATE TEMP TABLE table_counts (dataset_id STRING, table_count INT64);

FOR record IN 
  (
    SELECT 
      catalog_name as project_id,
      schema_name as dataset_id
    FROM `elzagales.INFORMATION_SCHEMA.SCHEMATA`
  )

DO 
  EXECUTE IMMEDIATE 
    CONCAT("INSERT table_counts (dataset_id, table_count)  SELECT table_schema as dataset_id, count(table_name) from ", record.dataset_id,".INFORMATION_SCHEMA.TABLES GROUP BY dataset_id");

END FOR;

SELECT * FROM table_counts;

This will return something like:

How to UNION ALL tables with tablename satisfying condition in BigQuery?

You can use a wildcard:

select * from `project.dataset.ourtable*`

---

Related Topics