Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Mentor

@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?

Contributor

@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?

Mentor

@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?

Contributor

@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?

New Contributor

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?

Contributor

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?

Rising Star

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
Highlighted

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

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?

Explorer
Don't have an account?
Coming from Hortonworks? Activate your account here