Escaping an invalid identifier

When you need to use reserved words, special characters, or a space in a column or partition name, enclose it in backticks (`).

An identifier in SQL is a sequence of alphanumeric and underscore (_) characters enclosed in backtick characters. In Hive, these identifiers are called quoted identifiers and are case-insensitive. You can use the identifier instead of a column or table partition name.

You have set the following parameter to column in the hive-site.xml file to enable quoted identifiers:

Set the hive.support.quoted.identifiers configuration parameter to column in the hive-site.xml file to enable quoted identifiers in column names. Valid values are none and column. For example, hive.support.quoted.identifiers = column.

  1. Create a table named test that has two columns of strings specified by quoted identifiers:
    CREATE TABLE test (`x+y` String, `a?b` String);
  2. Create a table that defines a partition using a quoted identifier and a region number:
    CREATE TABLE partition_date-1 (key string, value string) PARTITIONED BY (`dt+x` date, region int);
  3. Create a table that defines clustering using a quoted identifier:
    CREATE TABLE bucket_test(`key?1` string, value string) CLUSTERED BY (`key?1`) into 5 buckets;