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

[WagnerCh]

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

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!

[ms4446 · Accepted solution]

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.