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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With