Unable to rename BigQuery Column with SQL data definition language (DDL)

Google Cloud Data Analytics
1

Hello,

I made the mistake to edit the scheme of a BQ table manually and added several columns with a space (’ ‘) in the name instead of an underscore (’_').

Now I can’t work with this table in google looker because of the error “invalid field name”.

If I try to rename the column using DDL I get the syntax error displayed below.

Syntax error because of blank character
Syntax error because of blank character847×123 39.2 KB

It seems like I can’t rename the badly named column because not even the syntax can deal with the blank charakter in the column name.

Is there a way to solve this as a newbie in BQ?

Thank you in advance!

2

While BigQuery doesn’t directly support spaces in column names, you can address this issue using the following methods:

1. Workaround Using Views

If you prefer not to alter the underlying table structure, creating a view can serve as an effective workaround. Here’s how to do it:

  • Create a View: Construct a SQL query that selects all columns from your existing table but renames the columns with spaces using aliases.
CREATE VIEW `your_dataset.new_view_name` AS 
SELECT column1, column2, `column with space` AS column_without_space, ... 
FROM `your_dataset.your_table`;

Important Notes:

  • Replace placeholders like column with space with your actual column names, and column_without_space with your desired new names.
  • Use backticks (`) to enclose column names with spaces.

2. Fixing the Table

For a more permanent solution, you can create a new table with corrected column names and transfer the data from the old table:

  • Create a New Table: Create a new table with the desired schema, replacing spaces in column names with underscores.

  • Copy Data: Use a SELECT statement to copy data from the old table to the new one, renaming columns as needed.

INSERT INTO `your_dataset.new_table_name` (column_without_space, ...) 
SELECT `column with space` AS column_without_space, ... 
FROM `your_dataset.your_table`;
  • Delete Old Table (Optional): After confirming that the new table is correct, you may delete the old table.

Important Notes:

  • BigQuery Field Naming Restrictions: Field names must contain only letters, numbers, and underscores, and must start with a letter or underscore.

  • Using Backticks: Enclose column names with spaces or special characters in backticks (`) to help BigQuery interpret them correctly.

3 Likes