PageRenderTime 20ms CodeModel.GetById 17ms app.highlight 1ms RepoModel.GetById 1ms app.codeStats 0ms

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

#
text | 80 lines | 62 code | 18 blank | 0 comment | 0 complexity | 6cb01a8fd1c46e864669c0b8ae718c14 MD5 | raw file
 1CREATE TABLE srcbucket_mapjoin(key int, value string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
 2load data local inpath '../data/files/srcbucket20.txt' INTO TABLE srcbucket_mapjoin;
 3load data local inpath '../data/files/srcbucket21.txt' INTO TABLE srcbucket_mapjoin;
 4
 5CREATE TABLE srcbucket_mapjoin_part (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
 6load data local inpath '../data/files/srcbucket20.txt' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08');
 7load data local inpath '../data/files/srcbucket21.txt' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08');
 8load data local inpath '../data/files/srcbucket22.txt' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08');
 9load data local inpath '../data/files/srcbucket23.txt' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08');
10
11CREATE TABLE srcbucket_mapjoin_part_2 (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
12load data local inpath '../data/files/srcbucket22.txt' INTO TABLE srcbucket_mapjoin_part_2 partition(ds='2008-04-08');
13load data local inpath '../data/files/srcbucket23.txt' INTO TABLE srcbucket_mapjoin_part_2 partition(ds='2008-04-08');
14
15create table bucketmapjoin_hash_result_1 (key bigint , value1 bigint, value2 bigint);
16create table bucketmapjoin_hash_result_2 (key bigint , value1 bigint, value2 bigint);
17
18set hive.optimize.bucketmapjoin = true;
19create table bucketmapjoin_tmp_result (key string , value1 string, value2 string);
20
21explain extended
22insert overwrite table bucketmapjoin_tmp_result 
23select /*+mapjoin(b)*/ a.key, a.value, b.value 
24from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
25on a.key=b.key and b.ds="2008-04-08";
26
27insert overwrite table bucketmapjoin_tmp_result 
28select /*+mapjoin(b)*/ a.key, a.value, b.value 
29from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
30on a.key=b.key and b.ds="2008-04-08";
31
32select count(1) from bucketmapjoin_tmp_result;
33insert overwrite table bucketmapjoin_hash_result_1
34select sum(hash(key)), sum(hash(value1)), sum(hash(value2)) from bucketmapjoin_tmp_result;
35
36
37set hive.optimize.bucketmapjoin = false;
38insert overwrite table bucketmapjoin_tmp_result 
39select /*+mapjoin(b)*/ a.key, a.value, b.value 
40from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
41on a.key=b.key and b.ds="2008-04-08";
42
43select count(1) from bucketmapjoin_tmp_result;
44insert overwrite table bucketmapjoin_hash_result_2
45select sum(hash(key)), sum(hash(value1)), sum(hash(value2)) from bucketmapjoin_tmp_result;
46
47select a.key-b.key, a.value1-b.value1, a.value2-b.value2
48from bucketmapjoin_hash_result_1 a left outer join bucketmapjoin_hash_result_2 b
49on a.key = b.key;
50
51set hive.optimize.bucketmapjoin = true;
52explain extended
53insert overwrite table bucketmapjoin_tmp_result 
54select /*+mapjoin(a)*/ a.key, a.value, b.value 
55from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
56on a.key=b.key and b.ds="2008-04-08";
57
58insert overwrite table bucketmapjoin_tmp_result 
59select /*+mapjoin(a)*/ a.key, a.value, b.value 
60from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
61on a.key=b.key and b.ds="2008-04-08";
62
63select count(1) from bucketmapjoin_tmp_result;
64insert overwrite table bucketmapjoin_hash_result_1
65select sum(hash(key)), sum(hash(value1)), sum(hash(value2)) from bucketmapjoin_tmp_result;
66
67
68set hive.optimize.bucketmapjoin = false;
69insert overwrite table bucketmapjoin_tmp_result 
70select /*+mapjoin(a)*/ a.key, a.value, b.value 
71from srcbucket_mapjoin a join srcbucket_mapjoin_part_2 b 
72on a.key=b.key and b.ds="2008-04-08";
73
74select count(1) from bucketmapjoin_tmp_result;
75insert overwrite table bucketmapjoin_hash_result_2
76select sum(hash(key)), sum(hash(value1)), sum(hash(value2)) from bucketmapjoin_tmp_result;
77
78select a.key-b.key, a.value1-b.value1, a.value2-b.value2
79from bucketmapjoin_hash_result_1 a left outer join bucketmapjoin_hash_result_2 b
80on a.key = b.key;