Skip to content

bigfunctions > list_dataset_tables

list_dataset_tables

Call or Deploy list_dataset_tables ?

✅ You can call this list_dataset_tables bigfunction directly from your Google Cloud Project (no install required).

  • This list_dataset_tables function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions. This is particularly useful if you want to create private functions (for example calling your internal APIs). Discover the framework

Public BigFunctions Datasets:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

list_dataset_tables(fully_qualified_dataset)

Description

List tables of fully_qualified_dataset

Examples

1. List tables of your-project.your_dataset

call bigfunctions.eu.list_dataset_tables("your-project.your_dataset");
call bigfunctions.us.list_dataset_tables("your-project.your_dataset");
call bigfunctions.europe_west1.list_dataset_tables("your-project.your_dataset");

2. You can list tables of several datasets by providing several datasets separated by commas

call bigfunctions.eu.list_dataset_tables("your-project.your_dataset1, your-project.your_dataset2");
call bigfunctions.us.list_dataset_tables("your-project.your_dataset1, your-project.your_dataset2");
call bigfunctions.europe_west1.list_dataset_tables("your-project.your_dataset1, your-project.your_dataset2");
Need help using list_dataset_tables?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using list_dataset_tables?

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

For professional suppport, don't hesitate to chat with us.

Use cases

A use case for the list_dataset_tables function is to quickly get an overview of the tables within one or more datasets in BigQuery. This can be useful in several scenarios:

  • Data Discovery/Exploration: When working with a new project or dataset, you might not know all the tables that exist. list_dataset_tables provides a quick way to see what data is available.
  • Auditing/Documentation: You can use this function to generate a list of tables for documentation purposes or to audit the contents of your datasets.
  • Automated Processes: In scripts or workflows, you could use list_dataset_tables to dynamically determine which tables to process based on their presence in a dataset. For example, you might have a process that iterates over all tables in a dataset and performs some operation (e.g., data validation, backup, etc.).
  • Data Governance: This function can be used as part of a data governance process to track and manage the tables within your BigQuery environment. You can regularly run the function and compare the results to a known list of approved tables to identify any unauthorized tables.
  • Interactive Analysis: When working in the BigQuery console, you might want a quick reminder of the tables available in a dataset without navigating through the UI. This function can provide that information directly in the query results.

Example in a data pipeline:

Imagine you have a daily data pipeline that aggregates data from several raw tables into a summary table. You could use the list_dataset_tables function to automatically determine which raw tables to include in the aggregation process, making the pipeline more flexible and adaptable to changes in the raw data.

DECLARE raw_dataset_id STRING DEFAULT "your-project.your_raw_dataset";
DECLARE raw_tables ARRAY<STRING>;

SET raw_tables = (
    SELECT ARRAY_AGG(table_name)
    FROM bigfunctions.your_region.list_dataset_tables(raw_dataset_id)
    WHERE STARTS_WITH(table_name, 'raw_data_') -- Filter for relevant tables
);

-- Use the raw_tables array in your aggregation query
SELECT ...
FROM UNNEST(raw_tables) AS table_name
JOIN `your-project.your_raw_dataset`.table_name  -- Dynamically access tables
...

This example shows how list_dataset_tables can help automate processes by dynamically retrieving a list of tables within a dataset, enhancing the pipeline's flexibility and maintainability. Replace your_region with the appropriate BigQuery region (e.g., us, eu, us-central1). Remember to adjust the table name filtering logic (STARTS_WITH) to suit your specific requirements.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on