1-844-696-6465 (US)        +91 77600 44484        help@dezyre.com

Partitioned Hive table question.



0
when create hive table, if I use partition, I can't decalre the column in the "Create table" part of the ddl any more (otherwise, I received an error). But then, how do I make sure data from each column from the file go to the right column in the table that created?

For example, if I do
create table user (id int, name string, hiredate string, notes string)
I can easily use a data file with fields in the same order, i.e., (id, name, hiredate, notes)

Now, if I want to partition the table by hiredate, I do
create table user (id int, name string, notes string) partitioned by (hiredate string)

be noted now that I can't put the "hiredate" field defined in the "create table" part any more. In this case, how do I use a file with fields in the same order, i.e., (id, name, hiredate, notes)?

1 Answer(s)


0

hi Haofeng,

Looks like you might have an error in the HQL script. During the table creation make sure to define the partition on the column and also define the ROW FORMAT whether it is delimited by "\t" or "," etc and while loading enable dynamic partition and finally insert the data . Here is a complete example

CREATE EXTERNAL TABLE player_runs_distribute(player_id INT, year_of_play STRING, runs_scored INT, balls_played INT)
COMMENT 'This is the staging player_runs table' PARTITIONED BY(country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/cloudera/hive_examples/p_runs/runs_distribute/';

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
FROM player_runs_p
INSERT OVERWRITE TABLE player_runs_distribute PARTITION(country)
SELECT player_id, year_of_play , runs_scored , balls_played, country
DISTRIBUTE BY country;


select * from player_runs_distribute where country='US' limit 10;

Thanks

Your Answer

Click on this code-snippet-icon icon to add code snippet.

Upload Files (Maximum image file size - 1.5 MB, other file size - 10 MB, total size - not more than 50 MB)

Email
Password