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 you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

Solved Go to solution
Highlighted

How can you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

New Contributor
 
1 ACCEPTED SOLUTION

Accepted Solutions

Re: How can you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

Rising Star
3 REPLIES 3

Re: How can you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

Rising Star

Re: How can you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

New Contributor
@icocio

Thank you

Re: How can you make the row_id the primary key in a hive table?,how can you make row_id the primary key in HIVE?

New Contributor

Hi, I found another way of doing this:

1. I first loaded my data set in HDFS. The data set contained the following columns: rwid, ctrname, clndrdate and clndrmonth.

Note that column rwid had no values.

2. Then i created an external table that maps to this data set in hdfs

CREATE EXTERNAL TABLE IF NOT EXISTS calendar(rwid int, ctrname string, clndrdate DATE, clndrmonth string ) COMMENT 'Calendar for Non Business Days' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE location '<location of my file in hdfs>';

3. I created an ORC

CREATE TABLE IF NOT EXISTS calendar_nbd(rwid int, ctrname string, clndrdate DATE, clndrmonth string ) COMMENT 'Calendar for Non Business Days' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS ORC;

4. The last step is most important. I used row_number() over() in the insert overwrite query. This automatically updated the rwid column with the row number.

insert overwrite table calendar_nbd select row_number() over () as rwid, ctrname,clndrdate, clndrmonth from calendarnonbusdays;

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