question Re: How can I set primary key and reference foreign keys while creating a table / external table in hive? in Archives of Support Questions (Read Only)

How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 01:25:46 GMT

external-table-error.pngI'm getting the following error:

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 01:26:24 GMT

Thanks in advance for your time and suggestions.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 01:27:23 GMT

@keerthana gajarajakumar hive is not a replacement for EDW or RDBMS there is no concept of foreign key.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

nsabharwal — Wed, 03 Feb 2016 01:30:36 GMT

@keerthana gajarajakumar

It's not there yet but eventually we will get there.

See this https://issues.apache.org/jira/browse/HIVE-6905

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 01:30:54 GMT

@Neeraj Sabharwal duplicate link dude

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 02:51:20 GMT

@Artem Ervits -- do you have any suggestion to overcome this error? I'm unable to make updates to the following table:update-error.png

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 02:56:40 GMT

@keerthana gajarajakumar take a look at ACID support in Hive, we have some support for ACID but it's not fully working yet.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 03:55:34 GMT

@Artem Ervits - Thanks. I'd like to update hive-site.xml with the following statements. Where can I find hive-site.xml within the hortonworks web interface?

hive.support.concurrency – true
hive.enforce.bucketing – true (Not required as of Hive 2.0)
hive.exec.dynamic.partition.mode – nonstrict
hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
hive.compactor.initiator.on – true (for exactly one instance of the Thrift metastore service)
hive.compactor.worker.threads – a positive number on at least one instance of the Thrift metastore service

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 03:57:07 GMT

@keerthana gajarajakumar you can find those properties in configuration section of ambari under Hive service. they are most likely not there so you will need to add them to the properties in the custom hive-site section.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 04:17:03 GMT

@Artem Ervits - I was able to find all the settings under hive service . Thanks!

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 04:21:48 GMT

@keerthana gajarajakumar great, contribute an article on your findings when you're done.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 04:44:38 GMT

@Artem Ervits & @Neeraj Sabharwal -- Can you please guide me with the following error?

FYI - I updated the hive-site.xml settings under hive configs but I'm still unable to update my table.

FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table default.sal_orc that does not use an AcidOutputFormat or is not bucketed

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

aervits — Wed, 03 Feb 2016 04:47:19 GMT

@keerthana gajarajakumar it's a good practice to open a new question with new error.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

carlos_hilario — Wed, 03 Feb 2016 04:52:09 GMT

Hi,

I have a suggestion for working with hive acid tables at the link below (I think it is helpful for you):

http://www.remay.com.br/blog/how-to-work-with-acid-tables-in-hive/

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Wed, 03 Feb 2016 05:14:52 GMT

thanks @Carlos Hilario-- The challenge is identifying how to write the following statement :clustered by ('xxx') into 'xxx' buckets. The data was not completely loaded based on the values mentioned in this line of code.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

keerthanakumar2 — Fri, 05 Feb 2016 05:13:31 GMT

@Artem Ervits - I was able to solve for the question I posted, as you suggested I'll write a topic about it later. Thanks again for your guidance.

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

khaslbeck — Wed, 01 Feb 2017 00:11:38 GMT

Updating this thread. Hive has primary and foreign keys for metadata and query optimization.

https://cwiki.apache.org/confluence/display/Hive/Column+Statistics+in+Hive

ALTER TABLE TABLENAME ADD CONSTRAINT COLNAME_PK PRIMARY KEY (CS_ID);
ALTER TABLE TABLENAME ADD CONSTRAINT COLNAME_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

nandini_bhattac — Tue, 07 Feb 2017 14:59:12 GMT

So will this work?

ALTER TABLE HCALENDAR2017 ADD CONSTRAINT RWID_PK PRIMARY KEY (rowid() as rwid);

Re: How can I set primary key and reference foreign keys while creating a table / external table in hive?

msabri — Tue, 28 Mar 2017 10:25:39 GMT

Pls, check this: https://community.hortonworks.com/answers/22307/view.html