PageRenderTime 46ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

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

#
text | 34 lines | 30 code | 4 blank | 0 comment | 0 complexity | b652b0cd366678cc924c8c2d82958288 MD5 | raw file
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0
  1. set hive.auto.convert.join = true;
  2. CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE;
  3. explain
  4. FROM (
  5. FROM
  6. (
  7. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  8. ) a
  9. LEFT OUTER JOIN
  10. (
  11. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  12. ) b
  13. ON (a.c1 = b.c3)
  14. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4
  15. ) c
  16. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4;
  17. FROM (
  18. FROM
  19. (
  20. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  21. ) a
  22. LEFT OUTER JOIN
  23. (
  24. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  25. ) b
  26. ON (a.c1 = b.c3)
  27. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4
  28. ) c
  29. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4;
  30. SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1;