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.
- Renaming a column in BigQuery using queries
- Renaming a column in BigQuery by exporting and loading data into a new table
How to Rename a Column in BigQuery?
- Method 1: Rename a column in BigQuery using queries
- 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.
Integrate Oracle to BigQuery
Integrate PostgreSQL to BigQuery
Integrate MongoDB to BigQuery
Integrate Salesforce to BigQuery
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: Select Query editor in the Cloud Console.
Step 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
- This is an SQL query that selects all columns from a table except for two specific ones.
- The
SELECT * statement retrieves all columns from the dataset.
- The
EXCEPT(column_one, column_two) clause excludes column_one and column_two from the selection.
- Next, the query renames
column_one to newcolumn_one and column_two to newcolumn_two in the result.
- The
FROM mydataset.mytable specifies the source table for the query.
- When executed, the query will return all columns from
mytable, except column_one and column_two while renaming the two excluded columns to newcolumn_one and newcolumn_two.
Step 3: Now, click on more and select Query settings.
Step 4: Check the “Set a destination table for query results” checkbox, present under the Destination section.
Step 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 6: In the “Destination table write preference” section select the “Overwrite table.”
Step7: 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.
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.
Conclusion
- Through this tutorial article, we successfully discussed the process to rename a table in 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.
FAQ
How do I rename a column name in BigQuery?
You can’t directly rename a column in BigQuery. Instead, you can create a new table with the updated column name by running a SELECT query and aliasing the column.
How do you rename a column in a dataset?
In most databases, you can’t rename columns directly. You need to create a new table or use the ALTER TABLE command if supported, or use a SELECT query to alias the column name.
How do you change the name of a column in a query in SQL?
You can use the AS keyword in a SELECT statement to alias the column.
Yash is a Content Marketing professional with over three years of experience in data-driven marketing campaigns. He has expertise in strategic thinking, integrated marketing, and customer acquisition. Through comprehensive marketing communications and innovative digital strategies, he has driven growth for startups and established brands.
