Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

unable to create hive table with primary key

I am unable to create an external table in hive with primary key. Following is the example code:

hive> create table exmp((name string),primary key(name));

This returns me the following error message:

NoViableAltException(278@[]) at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:11216) at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:35977) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameType(HiveParser.java:31169) at org.apache.hadoop.hive.ql.parse.HiveParser.columnNameTypeList(HiveParser.java:29373) at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:4439) at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2084) at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1344) at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:983) at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:190) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:434) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:352) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:995) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1038) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:931) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:921) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:422) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:790) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:623) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.apache.hadoop.util.RunJar.main(RunJar.java:212) FAILED: ParseException line 1:18 cannot recognize input near '(' 'name' 'string' in column specification

Please help me out.

like image 912
Hussain Shaik Avatar asked Feb 16 '15 08:02

Hussain Shaik


People also ask

Can we create primary key in Hive table?

A table can have at most one PRIMARY KEY constraint. The parent columns referred to in PK/FK relationship must be PRIMARY KEY columns and must belong the same table. Also from Hive 3.0 — Hive includes support for UNIQUE, NOT NULL, DEFAULT and CHECK constraints. Beside UNIQUE all three type of constraints are enforced.

Can we create table in hive?

In Apache Hive we can create tables to store structured data so that later on we can process it. The table in the hive is consists of multiple columns and records. The table we create in any database will be stored in the sub-directory of that database.


1 Answers

Older version of the hive doesn't support primary key but this support has been added in 2.1.0 version of hive. So here is the sample query for it

CREATE TABLE table_name ( id int, name string, dept string, primary key(id) disable novalidate );

PS: How to get the version of hive

hive --version Hive 1.2.1000.x.x.x.xxxx-x

OR enter into beeline and hit

beeline Beeline version 1.2.1000.x.x.x.xxxx-x by Apache Hive

like image 159
Pardeep Avatar answered Oct 12 '22 15:10

Pardeep