What is quoted identifiers in Big query? How to use case-sensitive column and table names in Big query?

Google Big Query @ Freshers.in

Quoted identifiers in BigQuery are used to specify case-sensitive column and table names. They allow you to use column and table names that are case-sensitive or contain special characters that are not normally allowed in unquoted identifiers.

For example, consider a table named “MyTable” with a column named “MyColumn”. Without quoted identifiers, you would refer to the column in a query as MyColumn. However, if you wanted to use a case-sensitive column name, such as “myColumn”, you would need to use quoted identifiers to specify the case, like “myColumn”.

Here’s an example of how you might use quoted identifiers in a BigQuery query:

# Standard, unquoted query
SELECT MyColumn FROM MyTable;

# Query using quoted identifiers
SELECT "myColumn" FROM "MyTable";

In the first query, the table and column names are not case-sensitive, so the query will work as expected. In the second query, the table and column names are specified with quoted identifiers, making them case-sensitive, thus the query will only work if the table and column names are in the exact case as they are specified in the query.

It’s worth noting that, quoted identifier is not necessary when you are working with bigquery standard SQL, but it’s necessary when you are working with bigquery legacy SQL.

The rules that need to followed for Quoted identifiers are :

  1. Backtick (`) characters are required to surround the value.
  2. every character, including spaces and symbols, is allowed.
  3. Cannot be empty.
  4. Have the same escape sequences string literals
  5. An identifier must be quoted if it is the same as a reserved keyword.

BigQuery import urls to refer


Author: user

Leave a Reply