bigfunctions > get_table_columns

get_table_columns¶

Call or Deploy get_table_columns ?

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

This get_table_columns 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

get_table_columns(fully_qualified_table)

Description

Get the column information of the given table from INFORMATION_SCHEMA.COLUMNS and put them in a temporary table called bigfunction_result.

Examples¶

call bigfunctions.eu._get_columns("bigfunctions.samples.natality"); select column_name, data_type from bigfunction_result ;

EUUSeurope-west1

call bigfunctions.eu.get_table_columns("bigfunctions.eu.natality");

call bigfunctions.us.get_table_columns("bigfunctions.us.natality");

call bigfunctions.europe_west1.get_table_columns("bigfunctions.europe_west1.natality");

Need help using get_table_columns?

The community can help! Engage the conversation on Slack

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

Found a bug using get_table_columns?

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 get_table_columns function is to programmatically determine the schema of a BigQuery table. This can be useful in various scenarios, including:

Data Validation: Before loading data into a table, you could use this function to verify that the incoming data matches the expected schema. This can prevent errors and ensure data consistency.
Dynamic Query Generation: You might need to construct SQL queries dynamically based on the columns present in a table. get_table_columns allows you to retrieve the column names and data types, which you can then use to build your query string.
Data Discovery and Exploration: When working with unfamiliar datasets, this function can help you quickly understand the structure of a table without manually inspecting it in the BigQuery UI.
Schema Migration: If you're migrating data between tables or systems, you can use get_table_columns to compare the schemas of the source and destination tables and identify any discrepancies.
Automated Documentation: You can use this function as part of a script to automatically generate documentation about your BigQuery tables, including a list of columns and their data types.
Monitoring and Auditing: Regularly checking the schema of critical tables can help detect any unexpected changes that might indicate data quality issues or unauthorized modifications.

Example Scenario: Dynamic Query Generation

Let's say you have a table with a variable number of columns, and you want to write a query that selects only the columns of a specific data type (e.g., INTEGER). You can use get_table_columns to achieve this:

-- Call the function to get the columns of the table 'your_project.your_dataset.your_table'
CALL `bigfunctions.your_region.get_table_columns`('your_project.your_dataset.your_table');

-- Build a dynamic SQL query based on the results
DECLARE query STRING;
SET query = 'SELECT ';

SELECT
    ARRAY_TO_STRING(
        ARRAY_AGG(
            IF(data_type = 'INTEGER', column_name, NULL)
        ),
        ', '
    )
INTO query
FROM bigfunction_result;

SET query = query || ' FROM `your_project.your_dataset.your_table`';

-- Execute the dynamic query
EXECUTE IMMEDIATE query;

This code first calls get_table_columns to populate the bigfunction_result table. Then, it constructs a dynamic SQL query by iterating over the results and including only the INTEGER columns in the SELECT clause. Finally, it executes the generated query. This approach allows you to adapt your queries to different table schemas without hardcoding column names. Remember to replace your_project, your_dataset, your_table, and your_region with the appropriate values for your environment.

Spread the word¶

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

Share on Add a on