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.

回答1:

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

回答2:

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;  

回答3:

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
);

回答4:

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

create table exmp(name string);

回答5:

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 ',';

回答6:

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.

回答7:

Try this

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