How to Rename a Column in Google BigQuery: Step-by-Step Simplified

Google BigQuery — a serverless, highly scalable, and cloud-agnostic data warehouse-as-a-service — is a blessing for today’s businesses that deal in petabyte-scale information exchange environments.

Backed by the processing power of Google’s infrastructure, BigQuery is known for super-fast query resolving speed and cost-efficiency.

In general, it’s not possible to rename a column because it is not supported by the Cloud Console, the command-line tool, or the API. And, it is certain, if you ever try to update a table schema using a renamed column, the following error returns:

BigQuery error in update operation: Provided Schema does not match Table <strong>project_id</strong>:<strong>dataset</strong>.<strong>table</strong>.

We will use two methods to execute and get results of our likings.

• Rename a column in BigQuery using queries
• Renaming a column in BigQuery by exporting and loading data into a new table

What is Google BigQuery?

• An enterprise-wide data warehouse for analytics, BigQuery is a fully managed and serverless data warehouse-as-a-service.
• It empowers today’s data folks to analyze information efficiently by creating a logical data warehouse into columnar storage and compiling data from object storage and spreadsheets.

BigQuery Architecture

BigQuery differs from node-based cloud data warehousing solutions because it leverages technologies like Borg, colossus, Jupiter, and Dremel to produce optimum performance.

• Dremel: BigQuery executes user queries with the help of the Dremel query engine. It breaks queries into pieces and reassembles the results.
  • Google search, Google ads, Youtube, and Gmail all widely use Dremel. To know more about Dremel, don’t forget to read the paper on Dremel published in 2010.
• Colossus: Colossus is Google’s latest generation distributed file system. It manages replication, recovery, and distributed management.
  • BigQuery has the ColumnIO columnar storage format and compression algorithm, which can efficiently store and compute a large amount of data in a fraction of time.
• Borg: Borg is Google’s large-scale cluster management system. It’s the brains behind operations and consists of dozens of thousands of machines and hundreds of thousands of cores.
• Jupiter: Jupiter networking infrastructure is a powerful differentiator which sets apart Google’s cloud platform from the rest. It can deliver one petabit/sec of total bisection bandwidth.

How to Rename a Column in BigQuery?

1. Method 1: Rename a column in BigQuery using queries
2. Method 2: Rename a column in BigQuery by exporting and loading data into a new table

Method 1: Rename a column in BigQuery using queries

The method requires selecting all the columns in the table first, then assigning an alias to the column you want to rename — the alias must follow the BigQuery’s column names rules. Listed below are some advantages and disadvantages of Method 1.

Pro Tip: Start moving data to BigQuery from 100s of sources including databases, cloud applications, marketing platforms, analytics platforms, and many more. Connect your favorite tools to BigQuery and derive meaningful insights from analysis-ready data.

Advantages

• Using a query, you shift the data onto a new destination table to preserve the original data.
• Overwriting on the original table will save storage costs because you are using one table instead of two. But, this also means you lose the original data.

Disadvantages

• The query scan charges can be massive if the table size is large.
• Writing the query to result from an older table to a new destination table will incur storage costs for two tables.

The example below shows a standard SQL query selecting all the data in mytable excluding two columns that are to be renamed. Hence an alias is used (as discussed above) to generate new names for the respective columns. We will rename column_one to newcolumn_one and column_two to newcolumn_two. 

Using the Console method steps to rename a column in BigQuery looks like this:

Step 1.1: Select Query editor in the Cloud Console.

Step 1.2: Enter the query present below to select all the data in mydataset.mytable. We will not select the two columns which we are going to rename. In short, the query changes the name of the following two columns, column_one and column_two to newcolumn_one and newcolumn_two, respectively.

SELECT
 * EXCEPT(column_one, column_two),
 column_one AS newcolumn_one, column_two AS newcolumn_two
FROM
 mydataset.mytable

Step 1.3: Now, click on more and select Query settings.

Step 1.4: Check the “Set a destination table for query results” checkbox, present under the Destination section.

Step 1.5: Continue with the processes shown below:

• For the project that contains mydataset.mytable, leave the value set as the default project.
• For Dataset name, choose mydataset.
• In the Table name field, enter mytable, and click OK.

Step 1.6: In the “Destination table write preference” section select the “Overwrite table.”

Step 1.7: Click on Save, then click on Run in the Query editor. The new column names appear in the mytable.

Method 2: Rename a column in BigQuery by exporting and loading data into a new table

This method begets by first exporting the table to Cloud Storage then loading the required data into a new table with a new schema definition, containing the new column name. It is also possible to use the load job and overwrite the existing table.

Advantages

• BigQuery does not charge you for the export job and the load job. it’s free.
• Overwriting onto the original table will save storage costs because you are using one table instead of two. But, this also means you lose the original data.

Disadvantages

• Loading data onto a new table incurs storage costs for two tables until and unless you delete the old table and lose the original data.
• BigQuery charges money for storing the exported data in Cloud Storage.

Benefits & Use Cases of Rename Column in BigQuery

Benefits of BigQuery Rename Column

1. Improved Clarity: Makes column names more descriptive and meaningful, improving data readability.
2. Standardization: Ensures consistent naming conventions across datasets, making them easier to manage.
3. Error Reduction: Reduces the risk of misinterpretation or errors caused by ambiguous or outdated column names.
4. Enhanced Collaboration: Simplifies data understanding for teams, especially when multiple users access and analyze datasets.
5. Better Integration: Facilitates smoother integration with external tools or systems by aligning column names with required schemas.

Use Cases of Rename Column BigQuery

1. Data Migration: Aligns column names with new schemas when migrating datasets to BigQuery.
2. Schema Updates: Reflects changes in data structure, such as merging or splitting columns.
3. Improved Reporting: Renames columns to match business terminology, enhancing the relevance of analytics reports.
4. Data Cleaning: Fixes typos or inconsistencies in column names for a cleaner, more professional dataset.
5. Integration with BI Tools: Matches column names with the expected input for business intelligence platforms like Tableau or Power BI.

Conclusion

• Through this tutorial article, we successfully discussed the process to change column name BigQuery using two standard methods.
• If you want to learn more and want to learn some extended use cases — how to change a column’s data type or column mode — the below cited articles can help you.
  • Change a column’s data type
  • Change column mode

If you want to integrate your data from various sources and load it in Google BigQuery, then try Hevo.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

FAQ