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.

Hive Primary key on partitioned column

Solved Go to solution

Hive Primary key on partitioned column

Expert Contributor

I want to add primary key constraints to hive tables. The only think is that my PK is actually a partitioned column. For instance:

CREATE TABLE pk 
(
  id INT, 
  PRIMARY KEY(part) DISABLE NOVALIDATE
)
PARTITIONED BY (part STRING)

This fails with the error message:

DBCException: SQL Error [10002] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10002]: Invalid column reference part

Is there a way to use a partitioned column as PK?

Context: hdp 2.6, hive 2.1 with llap.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Hive Primary key on partitioned column

Hi @Guillaume Roger

I don't think we can create partition of primary column. To add few things on top of it, if you create partition based on primary key then there will be only one record placed under each partition which will end up in 'N' of partitions. Suppose if you have 10K records then it will be chaos with that much partition on primary keys. Hope it helps!

View solution in original post

5 REPLIES 5
Highlighted

Re: Hive Primary key on partitioned column

Hi @Guillaume Roger

I don't think we can create partition of primary column. To add few things on top of it, if you create partition based on primary key then there will be only one record placed under each partition which will end up in 'N' of partitions. Suppose if you have 10K records then it will be chaos with that much partition on primary keys. Hope it helps!

View solution in original post

Highlighted

Re: Hive Primary key on partitioned column

Expert Contributor

The example I gave was a trimmed-down version of what I wanted to do to show the technical problem.

My expected PK is actually a compound PK, with a few partitioned columns and a few non-partitioned columns.

But I am afraid that your answer says it all, no can do :(.

Thanks!

Highlighted

Re: Hive Primary key on partitioned column

@Guillaume Roger

I'm not sure whether my understanding is correct based on your reply. If you have compound keys then there are work around available to make it possible. Load the data with concat(compound keys) along with the separate fields into a stage table. For the stage table you have the option of defining hte primary key as well as partition based on the other fields which are used in a compound key creation.

Highlighted

Re: Hive Primary key on partitioned column

Expert Contributor

Thanks, but I am not interested in this surrogate key. The point of defining the PK was to help eg. reporting tools to find out automatically joins between tables. This surrogate key would thus not do.

Thanks!

Highlighted

Re: Hive Primary key on partitioned column

A side note: You should not partition on any columns with high cardinality such as IDs. You would use bucketing instead

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