Support Questions
Find answers, ask questions, and share your expertise

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

Contributor

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

18 REPLIES 18

Mentor

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

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

Mentor

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

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.

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/

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.

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

So will this work?

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

Explorer