unable to create hive table with primary key

Question

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.

Correct me if i am wrong, i am encountering data loss while sqoop merge without the primary key in the hive table. — Hussain Shaik, Feb 18 '15 at 04:52
Sqoop handles merge keys independently of the Hive table definition so there is no need (or capability) to assign a primary key to a Hive table for this purpose. If you're having Sqoop merge issues then that's a different question altogether. — Jeremy Beard, Feb 18 '15 at 04:55
Primary keys appear in Hive starting from Hive version 2.1.0. Before Hive version 2.1.0, trying to use `PRIMARY KEY` will result in an error. [Source](https://issues.apache.org/jira/browse/HIVE-13290) — amiabl, Jul 16 '19 at 18:42

score 12 · Answer 1 · answered Oct 14 '19 at 11:18

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

score 4 · Answer 2 · edited Jun 11 '18 at 08:21

Sorry there is no primary key in hive if you have any other functionality related to hive table got through this

CREATE DATABASE [IF NOT EXISTS] userdb;

SHOW DATABASES;

DROP DATABASE IF EXISTS userdb;

DROP DATABASE IF EXISTS userdb CASCADE;

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
 salary String, destination String)
 COMMENT "Employee details"
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY "\t"
 LINES TERMINATED BY "\n"
 STORED AS TEXTFILE;  

LOAD DATA LOCAL INPATH '/home/user/sample.txt'
    OVERWRITE INTO TABLE employee;

ALTER TABLE employee RENAME TO emp;

desc tablename;

ALTER TABLE employee CHANGE name ename String;
hive> ALTER TABLE employee CHANGE salary salary Double;

ALTER TABLE employee ADD COLUMNS ( 
    dept STRING COMMENT 'Department name');    

ALTER TABLE employee REPLACE COLUMNS ( 
    eid INT empid Int, 
    ename STRING name String);

DROP TABLE IF EXISTS employee;

SHOW TABLES;

SELECT * FROM employee WHERE Salary>40000 && Dept=TP;  

CREATE VIEW emp_30000 AS
    SELECT * FROM employee
    WHERE salary>30000;

DROP VIEW emp_30000;

score 3 · Answer 3 · edited Jul 16 '19 at 19:51

From official Page : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

They have included in the syntax to create table as:

constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES 
table_name(col_name, ...) DISABLE NOVALIDATE .

So that means we can create table with primary keys in hive

Version information

As of Hive 2.1.0 (HIVE-13290).

Hive includes support for non-validated primary and foreign key constraints. Some SQL tools generate more efficient queries when constraints are present. Since these constraints are not validated, an upstream system needs to ensure data integrity before it is loaded into Hive. Example:

CREATE TABLE pk(
    id1 integer,
    id2 integer,
    primary key(id1, id2) disable novalidate
);

CREATE TABLE fk(
    id1 integer,
    id2 integer,
    constraint c1 foreign key(id1, id2) references pk(id2, id1) disable novalidate
);

score 0 · Answer 4 · answered Jun 06 '16 at 03:53

0

We don't have any primary key and foreign key in hive.So remove the primary key and execute.

create table exmp(name string);

answered Jun 06 '16 at 03:53

Niyati Priyadarshinee

21
4

score 0 · Answer 5 · answered Nov 29 '16 at 03:16

First, derby in hive not have key Second, some key may duplicate with keyword in hive show you have to add single quote to them. For my case, i get error with query

  create table NYSE (exchange String,stock_symbol String,stock_date String,stock_price_open double, stock_price_high double, stock_price_low double, stock_price_close double, stock_volume double, stock_price_adj_close double) row format delimited fields terminated by ‘,’;

it should be

  create table NYSE (`exchange` String,stock_symbol String,stock_date String,stock_price_open double, stock_price_high double, stock_price_low double, stock_price_close double, stock_volume double, stock_price_adj_close double) row format delimited fields terminated by ',';

score -1 · Answer 6 · edited Oct 03 '18 at 15:38

-1

There is no primary key concept in Hive as it is not a database and in hive operation is file based not the record based.

Try this

create table table_name (
    row_number() over() as rowId     operation
    name string,
    age Int,
    address string) row format delimited fields terminated by ',' stored as textfile;

Here consider rowId as unique and perform the operation.

edited Oct 03 '18 at 15:38

cakan

2,099
5
32
42

answered Oct 03 '18 at 09:44

Dhrub Thakur

27
4

score -4 · Answer 7 · answered Feb 16 '15 at 12:36

-4

Try this

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

answered Feb 16 '15 at 12:36

akhil vangala

1,043
1
10
11

unable to create hive table with primary key

7 Answers7

Linked