Reputation: 174
So I was trying to partition my Impala table with the column 'file' which has 1500 distinct records. That means 1500 partitions. I first run a query like this to return the partition queries:
SELECT DISTINCT
concat('insert into partitioned_table partition (year=',
cast(year as string),', month=',cast(month as string),
') select c1, c2, c3 from raw_data where year=',
cast(year as string),' and month=',cast(month as string),';') AS command
FROM raw_data;
Then I got 1500 queries to run.
Now there's one problem: Since each query might take 3 minutes to finish. 1500 queries could take several days. Which is a really long time. To save the time, I have already done some tuning: using COMPUTE STATS to get statics, convert table to Parquet. MY question is, Is there a way that can speed up this process? Like max up the executors just like Hive can do?
Upvotes: 2
Views: 1637
Reputation: 44921
You can use dynamic partitioning
insert into partitioned_table partition (year,month)
select c1, c2, c3, year, month
from raw_data
Demo
create table t (i int) partitioned by (year string,month string);
insert into t partition (year,month) values
( 1,'2015','02')
,( 2,'2017','01')
,( 3,'2016','02')
,( 4,'2013','09')
,( 5,'2015','07')
,( 6,'2012','03')
,( 7,'2012','12')
,( 8,'2017','12')
,( 9,'2015','11')
,(10,'2015','02')
;
select * from t order by year,month,i;
+----+------+-------+
| i | year | month |
+----+------+-------+
| 6 | 2012 | 03 |
| 7 | 2012 | 12 |
| 4 | 2013 | 09 |
| 1 | 2015 | 02 |
| 10 | 2015 | 02 |
| 5 | 2015 | 07 |
| 9 | 2015 | 11 |
| 3 | 2016 | 02 |
| 2 | 2017 | 01 |
| 8 | 2017 | 12 |
+----+------+-------+
hdfs dfs -ls -R /user/hive/warehouse/t;
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2012
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2012/month=03
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2012/month=03/174c30c4e1edc236-b57504ce4afd76a2_1891304442_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2012/month=12
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2012/month=12/174c30c4e1edc236-b57504ce4afd76a2_798564417_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2013
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2013/month=09
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2013/month=09/174c30c4e1edc236-b57504ce4afd76a2_432428758_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2015
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=02
-rw-r--r-- 1 impala supergroup 5 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=02/174c30c4e1edc236-b57504ce4afd76a2_768620898_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=07
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=07/174c30c4e1edc236-b57504ce4afd76a2_2029099237_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=11
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2015/month=11/174c30c4e1edc236-b57504ce4afd76a2_974618320_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2016
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2016/month=02
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2016/month=02/174c30c4e1edc236-b57504ce4afd76a2_502842645_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2017
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2017/month=01
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2017/month=01/174c30c4e1edc236-b57504ce4afd76a2_2014291428_data.0.
drwxr-xr-x - impala supergroup 0 2017-02-07 13:45 /user/hive/warehouse/t/year=2017/month=12
-rw-r--r-- 1 impala supergroup 2 2017-02-07 13:45 /user/hive/warehouse/t/year=2017/month=12/174c30c4e1edc236-b57504ce4afd76a2_1693475255_data.0.
Upvotes: 3