Hive Primary key on partitioned column

[#1]

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.

[#2 · Accepted solution]

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!

[#3]

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!

[#4]

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

[#5]

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!

[#6]

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