Solved: Renaming Columns AS or AT in BigQuery

Alapinka · 05-24-2023 08:07 AM

hi all, does anyone have an idea how to rename Columns AS and AT (columns coming from a Google Sheet have no header names when loaded).

AS as Column_X doesn't work, unfortunately (error: Syntax error: Expected end of input but got string literal 'AS' at [1:1])

'AS' as Column_X enters AS as values in the entire column, which is incorrect.

Thanks!

ms4446

The BigQuery SQL language does have reserved keywords that you can't use as identifiers unless they're enclosed in backticks. The words "AS" and "AT" are both reserved keywords.

If you have a BigQuery table with columns without names, such as when loading from Google Sheets, it assigns column names automatically. It typically assigns column names like _field1, _field2, and so on, in the order of the columns in the table.

To rename a column, you can use the AS keyword. However, if the column name is a reserved keyword, you need to enclose it in backticks. Here's how you might do it:

SELECT `AS` AS Column_X, `AT` AS Column_Y
FROM `project.dataset.table`

In this case, AS and AT are the original column names, and Column_X and Column_Y are the new names you want to assign to these columns. Replace project.dataset.table with the name of your table.

Remember, the AS keyword is case-insensitive, so it needs to be in backticks even if it's lowercase.

Please note that it's generally a good practice to avoid using reserved keywords as identifiers to prevent such issues. If you have control over the source data (like the Google Sheet in your case), consider renaming these columns there to prevent such issues in BigQuery.

View solution in original post

ms4446

The BigQuery SQL language does have reserved keywords that you can't use as identifiers unless they're enclosed in backticks. The words "AS" and "AT" are both reserved keywords.

If you have a BigQuery table with columns without names, such as when loading from Google Sheets, it assigns column names automatically. It typically assigns column names like _field1, _field2, and so on, in the order of the columns in the table.

To rename a column, you can use the AS keyword. However, if the column name is a reserved keyword, you need to enclose it in backticks. Here's how you might do it:

SELECT `AS` AS Column_X, `AT` AS Column_Y
FROM `project.dataset.table`

In this case, AS and AT are the original column names, and Column_X and Column_Y are the new names you want to assign to these columns. Replace project.dataset.table with the name of your table.

Remember, the AS keyword is case-insensitive, so it needs to be in backticks even if it's lowercase.

Please note that it's generally a good practice to avoid using reserved keywords as identifiers to prevent such issues. If you have control over the source data (like the Google Sheet in your case), consider renaming these columns there to prevent such issues in BigQuery.

Alapinka

Many thanks, this worked indeed! Another workound found by my colleague:

tab.AT as ...

FROM ... tab

gg774411

does the following syntax rename the table permanently ? or does it require being saved to enact the changes made ?

ms4446

The SELECT ... AS ... syntax does not permanently rename the columns; it only renames them in the query result. To make these changes permanent, you would need to create a new table or overwrite the existing one with the desired column names.