get_table_columns¶

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.

Usage¶

Call or Deploy get_table_columns ?

Call get_table_columns directly

The easiest way to use bigfunctions

get_table_columns function is deployed in 39 public datasets for all of the 39 BigQuery regions.
It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
(You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

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

Deploy get_table_columns in your project

Why deploy?

You may prefer to deploy get_table_columns in your own project 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).
Get started by reading the framework page

Deployment

get_table_columns function can be deployed with:

pip install bigfunctions
bigfun get get_table_columns
bigfun deploy get_table_columns

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");

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.

Need help or Found a bug?

Get help using get_table_columns

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about 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.

We also provide professional suppport.

Show your by adding a on