PageRenderTime 39ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

/tags/release-0.0.0-rc0/hive/external/ql/src/test/queries/clientpositive/index_compact_2.q

#
text | 45 lines | 35 code | 10 blank | 0 comment | 0 complexity | 18a120d397e8ff83ecb6b80b33df5a02 MD5 | raw file
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0 
    

  1. CREATE TABLE srcpart_rc (key int, value string) PARTITIONED BY (ds string, hr int) STORED AS RCFILE;
    2. 

  2. 

  3. INSERT OVERWRITE TABLE srcpart_rc PARTITION (ds='2008-04-08', hr=11) SELECT key, value FROM srcpart WHERE ds = '2008-04-08' AND hr = 11;
    4. 

  4. INSERT OVERWRITE TABLE srcpart_rc PARTITION (ds='2008-04-08', hr=12) SELECT key, value FROM srcpart WHERE ds = '2008-04-08' AND hr = 12;
    5. 

  5. INSERT OVERWRITE TABLE srcpart_rc PARTITION (ds='2008-04-09', hr=11) SELECT key, value FROM srcpart WHERE ds = '2008-04-09' AND hr = 11;
    6. 

  6. INSERT OVERWRITE TABLE srcpart_rc PARTITION (ds='2008-04-09', hr=12) SELECT key, value FROM srcpart WHERE ds = '2008-04-09' AND hr = 12;
    7. 

  7. 

  8. CREATE INDEX srcpart_rc_index ON TABLE srcpart_rc(key) as 'COMPACT' WITH DEFERRED REBUILD;
    9. 

  9. ALTER INDEX srcpart_rc_index ON srcpart_rc REBUILD;
    10. 

  10. SELECT x.* FROM default__srcpart_rc_srcpart_rc_index__ x WHERE x.ds = '2008-04-08' and x.hr = 11 ORDER BY key;
    11. 

  11. 

  12. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    13. 

  13. INSERT OVERWRITE DIRECTORY "/tmp/index_test_index_result" SELECT `_bucketname` ,  `_offsets` FROM default__srcpart_rc_srcpart_rc_index__ x WHERE x.key=100 AND x.ds = '2008-04-08';
    14. 

  14. SET hive.index.compact.file=/tmp/index_test_index_result;
    15. 

  15. SET hive.input.format=org.apache.hadoop.hive.ql.index.compact.HiveCompactIndexInputFormat;
    16. 

  16. SELECT key, value FROM srcpart_rc WHERE key=100 AND ds = '2008-04-08' ORDER BY key;
    17. 

  17. 

  18. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    19. 

  19. INSERT OVERWRITE DIRECTORY "/tmp/index_test_index_result" SELECT `_bucketname` ,  `_offsets` FROM default__srcpart_rc_srcpart_rc_index__ x WHERE x.key=100 AND x.ds = '2008-04-08' and x.hr = 11;
    20. 

  20. SET hive.index.compact.file=/tmp/index_test_index_result;
    21. 

  21. SET hive.input.format=org.apache.hadoop.hive.ql.index.compact.HiveCompactIndexInputFormat;
    22. 

  22. SELECT key, value FROM srcpart_rc WHERE key=100 AND ds = '2008-04-08' and hr = 11 ORDER BY key;
    23. 

  23. 

  24. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    25. 

  25. SELECT key, value FROM srcpart_rc WHERE key=100 AND ds = '2008-04-08' and hr = 11 ORDER BY key;
    26. 

  26. 

  27. DROP INDEX srcpart_rc_index on srcpart_rc;
    28. 

  28. 

  29. EXPLAIN
    30. 

  30. CREATE INDEX srcpart_rc_index ON TABLE srcpart_rc(key) as 'COMPACT' WITH DEFERRED REBUILD;
    31. 

  31. CREATE INDEX srcpart_rc_index ON TABLE srcpart_rc(key) as 'COMPACT' WITH DEFERRED REBUILD;
    32. 

  32. ALTER  INDEX srcpart_rc_index ON srcpart_rc REBUILD;
    33. 

  33. SELECT x.* FROM default__srcpart_rc_srcpart_rc_index__ x ORDER BY key;
    34. 

  34. 

  35. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    36. 

  36. INSERT OVERWRITE DIRECTORY "/tmp/index_result" SELECT `_bucketname` ,  `_offsets` FROM default__srcpart_rc_srcpart_rc_index__ WHERE key=100;
    37. 

  37. SET hive.index.compact.file=/tmp/index_result;
    38. 

  38. SET hive.input.format=org.apache.hadoop.hive.ql.index.compact.HiveCompactIndexInputFormat;
    39. 

  39. SELECT key, value FROM srcpart_rc WHERE key=100 ORDER BY key;
    40. 

  40. 

  41. SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    42. 

  42. SELECT key, value FROM srcpart_rc WHERE key=100 ORDER BY key;
    43. 

  43. 

  44. DROP INDEX srcpart_rc_index on srcpart_rc;
    45. 

  45. DROP TABLE srcpart_rc;
    46.