not able to use '_' in column name or cannot use reserved keywords in column name

[#1]

Hello Team,

I am trying to use the underscore "_" in the column name example "_name" but the create statement is failing. i also wanted to use the reserved keywords example "transform" as column name but that is also not working.

I tried setting the values for keyword and for identifiers while running command still it is giving me error when running via beeline 

Following are the commands and the error that i am getting  :

Command : sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "SET hive.support.quoted.identifiers=column;use jayesh; create external table testkeyword1( `transform` string);";

error : Error: Error while compiling statement: FAILED: ParseException line 1:45 cannot recognize input near ')' '<EOF>' '<EOF>' in column type (state=42000,code=40000)
------------------------------------------------------------------------------------------------------------
Command : sudo -u hive beeline -u "jdbc:hive2://cdp1.:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "SET hive.support.sql11.reserved.keywords=false;use jayesh; create external table testkeyowrd2( transform string)";

error :  Error while compiling statement: FAILED: ParseException line 1:38 cannot recognize input near 'transform' 'string' ')' in column name or constraint (state=42000,code=40000)

--------------------------------------------------------------------------------------------------------

Command :  sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "set hive.support.sql11.reserved.keywords=false; set hive.support.quoted.identifiers=column;use jayesh; create external table testkeyword3( transform string)";

error : Error: Error while compiling statement: FAILED: ParseException line 1:38 cannot recognize input near 'transform' 'string' ')' in column name or constraint (state=42000,code=40000)

-------------------------------------------------------------------------------------------------------------

Command :  sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "set hive.support.sql11.reserved.keywords=false; set hive.support.quoted.identifiers=column;use jayesh; create external table testunderscore( `_name` string, `_id` string)";

error : 

-bash: _name: command not found
-bash: _id: command not found

Error: Error while compiling statement: FAILED: ParseException line 1:45 cannot recognize input near ',' 'string' ')' in column type (state=42000,code=40000)

[#2]

@jayes Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @JoseManuel @cravani  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3]

Both the below queries should work by default. I have checked by running them against CDP 7.2.17.

create external table testkeyword1( `transform` string)
create external table testunderscore1( `_name` string, `_id` string);

What is the Apache Hive or CDP/CDH/HDP version you are using?

[#4]

@cravani please find the hive version

Hive 3.1.3000.7.1.8.28-1
Git git://centos7-builds-zb40x/grid/0/jenkins/workspace/workspace/CDH-parallel-centos7/SOURCES/hive -r ******
Compiled by jenkins on Fri Apr 14 11:40:33 UTC 2023

[#5 · Accepted solution]

@jayes  I had another look into the problem description and I see queries run with 2 categories

1. Column names enclosed in backquotes
2. Column names not enclosed in backquotes.

below are the root cause for both the categories, please find the solution at the end of this comment.

Case 1: This one fails because you are running SQL with -e option and and column values are enclosed in backticks "`", the value inside backticks is considered as a Linux command as opposed to be a part of SQL leading to the compilation error.

Command : sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "SET hive.support.quoted.identifiers=column;use jayesh; create external table testkeyword1( `transform` string);";
error : Error: Error while compiling statement: FAILED: ParseException line 1:45 cannot recognize input near ')' '<EOF>' '<EOF>' in column type (state=42000,code=40000)

 Command : sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "set hive.support.sql11.reserved.keywords=false; set hive.support.quoted.identifiers=column;use jayesh; create external table testunderscore( `_name` string, `_id` string)";

error :

-bash: _name: command not found
-bash: _id: command not found

Error: Error while compiling statement: FAILED: ParseException line 1:45 cannot recognize input near ',' 'string' ')' in column type (state=42000,code=40000)

---

Case 2: This one fails because you are using reserved keyword transform which is not escaped properly.

Command : sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "set hive.support.sql11.reserved.keywords=false; set hive.support.quoted.identifiers=column;use jayesh; create external table testkeyword3( transform string)";
error : Error: Error while compiling statement: FAILED: ParseException line 1:38 cannot recognize input near 'transform' 'string' ')' in column name or constraint (state=42000,code=40000)

Command : sudo -u hive beeline -u "jdbc:hive2://cdp1.:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "SET hive.support.sql11.reserved.keywords=false;use jayesh; create external table testkeyowrd2( transform string)";
error : Error while compiling statement: FAILED: ParseException line 1:38 cannot recognize input near 'transform' 'string' ')' in column name or constraint (state=42000,code=40000)

---

Solution:

• You can escape backticks while passing SQL from command line
  eg:- 

sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -e "SET use jayesh; create external table testkeyword1( \`transform\` string);";​

• You can also pass the SQL to beeline command as a file.
  eg;- transform.sql

create external table testkeyword1( `transform` string);​

Beeline command:

sudo -u hive beeline -u "jdbc:hive2://cdp1:2181,cdp2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;;principal=hive/_HOST@hdpcluster.test" -n hive --showHeader=false --outputformat=tsv2 -f transform.sql​

Let me know if this helps fixing the issue.

[#6]

@cravani Thanks for the help 🙂