special character "#" in column name in Hive select query

3
votes

I have a table in Hive that has a column name with special character "#' How to I escape # to select the column?

SELECT a3suph, a3sph# FROM schema.dbname; throws an exception.

ParseException line 1:20 character '#' not supported here. I am using DBeaver to connect to Hive.

2
hadoophivehiveql
surround the column name with backticks. – Vamsi Prabhala
It worked. Perfect! Thanks. – LAM3DA
Nice 1!!! @VamsiPrabhala please write an answer about this instead of comment. – Jainik

2 Answers

0
votes

It's also a problem when a column name is a HiveQL keyword (even pointed out by the syntax highlighter in the example below).

SELECT `a3sph#`, `case`, `when`
FROM schema.dbname

Backquotes encapsulation of column names should work.

0
votes

With Hive 0.13 and afterwards, Quoted identifiers in the names of table columns are supported. Quoted identifiers in Hive are case-insensitive. Sample:

       CREATE TABLE bucket_test(`key?1` string, value string)
       CLUSTERED BY (`key?1`) into 5 buckets;

Hortonworks Link: https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.5/bk_data-access/content/hive-013-feature-quoted-identifiers.html