PageRenderTime 12ms CodeModel.GetById 9ms app.highlight 1ms RepoModel.GetById 1ms 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
 1set hive.auto.convert.join = true;
 2
 3CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING) STORED AS TEXTFILE;
 4
 5explain
 6FROM (
 7 FROM 
 8  (
 9  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
10  ) a
11 LEFT OUTER JOIN 
12 (
13  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
14 ) b 
15 ON (a.c1 = b.c3)
16 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4
17) c
18INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4;
19
20FROM (
21 FROM 
22  (
23  FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
24  ) a
25 LEFT OUTER JOIN 
26 (
27  FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
28 ) b 
29 ON (a.c1 = b.c3)
30 SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4
31) c
32INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4;
33
34SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4)) FROM dest1;