Hive : Save query result in HDFS and load into a new table

Question

I have a hive table which has 3 part partitions (dt,service_type,pv)

I want to create a new table in a different database with filtered data from this original table. (filter by the columns used for partition)

So I query data using SELECT as follows and saved the result in HFDS

INSERT OVERWRITE DIRECTORY  '/user/atscale/filterd-ratlog' SELECT * FROM rat_log_normalized WHERE dt >= '2016-05-01' AND dt <='2016-05-31' AND service_type='xxxxx_jp' AND event_type='yy';

This is the result folder structure in HDFS.It is splitted into 531s of files :

hdfs dfs -ls /user/atscale/filterd-ratlog

Found 531 items -rwxr-xr-x 3 atscale atscale 8838075079 2016-08-18 06:20 /user/atscale/filterd-ratlog/000000_0 -rwxr-xr-x 3 atscale atscale 8879084968 2016-08-18 06:15 /user/atscale/filterd-ratlog/000001_0 -rwxr-xr-x 3 atscale atscale 8821619748 2016-08-18 06:20 /user/atscale/filterd-ratlog/000002_0 -rwxr-xr-x 3 atscale atscale 8724063719 2016-08-18 06:20 /user/atscale/filterd-ratlog/000003_0 . . . -rwxr-xr-x 3 atscale atscale 6878819716 2016-08-18 06:42 /user/atscale/filterd-ratlog/000527_0 -rwxr-xr-x 3 atscale atscale 5461395906 2016-08-18 06:27 /user/atscale/filterd-ratlog/000528_0 -rwxr-xr-x 3 atscale atscale 6222887747 2016-08-18 06:26 /user/atscale/filterd-ratlog/000529_0 -rwxr-xr-x 3 atscale atscale 692289350 2016-08-18 06:37 /user/atscale/filterd-ratlog/000530_0

Now, wow can I load this data in HDFS files into my new table?I want to keep the partitions as well ..

Answer 1

I guess you are looking for an external table:

DROP TABLE IF EXISTS filterd-ratlog;
create external table filterd-ratlog(
col1 int,
col2 int,
col3 int,
dt string,
service_type string,
event_type string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
location '/user/atscale/filterd-ratlog';