question Re: not able to use '_' in column name or cannot use reserved keywords in column name in Support Questions

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

jayes — Tue, 26 Sep 2023 14:04:43 GMT

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)

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

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)

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

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)

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

DianaTorres — Tue, 26 Sep 2023 15:19:51 GMT

@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.

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

cravani — Tue, 26 Sep 2023 15:28:48 GMT

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?

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

jayes — Wed, 27 Sep 2023 04:25:50 GMT

@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

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

cravani — Thu, 28 Sep 2023 18:55:22 GMT

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

Column names enclosed in backquotes
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:

Let me know if this helps fixing the issue.

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

jayes — Tue, 03 Oct 2023 15:39:04 GMT

@cravani Thanks for the help 🙂