Quoted Identifiers in Column Names – Apache Hive

In SQL, an identifier is a string of alphanumeric and underscore (_) characters surrounded by backtick (`). In Hive, quoted IDs are case-insensitive. For example, `x+y` and `a?b` are allowed column names while creating a table.

To enable quoted identifiers in SQL column names, set the hive.support.quoted.identifiers configuration parameter to column in hive-site.xml. The acceptable settings for Hive 0.13 are none and column.

You can construct table columns with quoted identifiers if the hive.support.quoted.identifiers property is set to “column.” Look at the following example.

hive> SET hive.support.quoted.identifiers=column;
hive> CREATE TABLE test(`x+y` String, `a?b` String);

However, if you want to exclude certain columns from data retrieval, you’ll need to set the configuration option to “none.” Otherwise, the exception will be thrown. Consider the following scenario.

hive> SELECT `(sal|comm)?+.+` FROM emp;

The command above is used to exclude the columns “sal” and “comm” from the “emp” table. However, the outcome is as shown below:

FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference ‘(sal|comm)?+.+’: (possible column names are: empno, ename, job, mgr, hiredate, sal, comm, deptno)

Now update the parameter value as given below and run the SELECT query as stated above; it should work.