question Re: Hive Primary key on partitioned column in Archives of Support Questions (Read Only)

Hive Primary key on partitioned column

guillaume_roger — Wed, 14 Jun 2017 17:54:14 GMT

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.

Re: Hive Primary key on partitioned column

balavignesh_nag — Wed, 14 Jun 2017 18:45:22 GMT

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!

Re: Hive Primary key on partitioned column

egarelnabi — Wed, 14 Jun 2017 18:57:41 GMT

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

Re: Hive Primary key on partitioned column

guillaume_roger — Wed, 14 Jun 2017 21:10:47 GMT

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!

Re: Hive Primary key on partitioned column

balavignesh_nag — Wed, 14 Jun 2017 22:23:35 GMT

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

Re: Hive Primary key on partitioned column

guillaume_roger — Thu, 15 Jun 2017 12:34:15 GMT

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!