title: Hive (0.12.0) - Load data into table with partition, buckets and attached index
body: <p>Using Hive 0.12.0, I am looking to populate a table that is partitioned and uses buckets with data stored on HDFS. I would also like to create an index of this table on a foreign key which I will use a lot when joining tables.</p>
  
  <p>I have a working solution but something tells me it is very inefficient.</p>
  
  <p>Here is what I do:
  I load my data in a "flat" intermediate table (no partition, no buckets):</p>
  
  <pre><code>LOAD DATA LOCAL INPATH 'myFile' OVERWRITE INTO TABLE my_flat_table;
  </code></pre>
  
  <p>Then I select the data I need from this flat table and insert it into the final partitioned and bucketed table:</p>
  
  <pre><code>FROM my_flat_table
    INSERT OVERWRITE TABLE final_table
    PARTITION(date)
    SELECT
    col1, col2, col3, to_date(my_date) AS date;
  </code></pre>
  
  <p>The bucketing was defined earlier when I created my final table:</p>
  
  <pre><code> CREATE TABLE final_table
     (col1 TYPE1, col2 TYPE2, col3 TYPE3)
     PARTITIONED BY (date DATE)
     CLUSTERED BY (col2) INTO 64 BUCKETS;
  </code></pre>
  
  <p>And finally, I create the index on the same column I use for bucketing (is that even useful?):</p>
  
  <pre><code>CREATE INDEX final_table_index ON TABLE final_table (col2) AS 'COMPACT';
  </code></pre>
  
  <p>All of this is obviously really slow, so how would I go about optimizing the loading process?</p>
  
  <p>Thank you</p>