HDP sandbox 2.6.3 on my local Windows 7 machine.
From the beeline shell/hive view from Ambari, I am trying to create a table with a primary key:
create table department(Id int COMMENT 'Surrogate PK is not fun', Description string, Code string, primary key(Id) disable novalidate);
The above query works fine with our dev./prod Hive set-up but when I execute it on the my local sandbox:
0: jdbc:hive2://sandbox-hdp.hortonworks.com:2> create table department(Id int COMMENT 'Surrogate PK is not fun', Description string, Code string, primary key(Id) disable novalidate); Error: Error while compiling statement: FAILED: ParseException line 1:107 cannot recognize input near 'key' '(' 'Id' in column type (state=42000,code=40000)
Do I need to change any Hive config(via Ambari) for the PK and FK to work?
Support for PK and FK was introduced in Hive 2.1.0 . Make sure to connect to hive server interactive instead of Hive server2.
If you have not enabled LLAP, enable LLAP and connect to LLAP. You should be able to create those tables.
To enable llap follow the link : https://community.hortonworks.com/articles/114636/how-to-enable-hive-llap-for-interactive-sql-on-had...
JIRA for PK and FK support (https://issues.apache.org/jira/browse/HIVE-13290)