title: Sorted Table in Hive (ORC file format)
body: <p>I'm having some difficulties to make sure I'm leveraging sorted data within a Hive table. (Using ORC file format)</p>
  
  <p>I understand we can affect how the data is read from a Hive table, by declaring a <code>DISTRIBUTE BY</code> clause in the create DDL.</p>
  
  <pre><code>CREATE TABLE trades
  (
      trade_id INT,
      name STRING,
      contract_type STRING,
      ts INT
  )
  PARTITIONED BY (dt STRING)
  CLUSTERED BY (trade_id) SORTED BY (trade_id, time) INTO 8 BUCKETS
  STORED AS ORC;
  </code></pre>
  
  <p>This will mean that every time I make a query to this table, the data will be distributed by <code>trade_id</code> among the various mappers and afterward it will be sorted.</p>
  
  <p>My question is:</p>
  
  <p>I do not want the data to be split into <code>N</code> files (buckets), because the volume is not that much and I would stay with small files.</p>
  
  <p>However, I do want to leverage sorted insertion.</p>
  
  <pre><code>INSERT OVERWRITE TABLE trades
  PARTITION (dt)
  SELECT trade_id, name, contract_type, ts, dt
  FROM raw_trades
  DISTRIBUTE BY trade_id
  SORT BY trade_id;
  </code></pre>
  
  <p>Do I really need to use <code>CLUSTERED/SORT</code> in the create DLL statement? Or does Hive/ORC knows how to leverage the fact that the insertion process already ensured that the data is sorted?</p>
  
  <p>Could it make sense to do something like:</p>
  
  <pre><code>CLUSTERED BY (trade_id) SORTED BY (trade_id, time) INTO 1 BUCKETS
  </code></pre>