| -- SORT_QUERY_RESULTS |
| |
| set hive.mapred.mode=nonstrict; |
| create table fact(m1 int, m2 int, d1 int, d2 int); |
| create table dim1(f1 int, f2 int); |
| create table dim2(f3 int, f4 int); |
| create table dim3(f5 int, f6 int); |
| create table dim4(f7 int, f8 int); |
| create table dim5(f9 int, f10 int); |
| create table dim6(f11 int, f12 int); |
| create table dim7(f13 int, f14 int); |
| |
| LOAD DATA LOCAL INPATH '../../data/files/fact-data.txt' INTO TABLE fact; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim1; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim2; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim3; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim4; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim5; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim6; |
| LOAD DATA LOCAL INPATH '../../data/files/dim-data.txt' INTO TABLE dim7; |
| |
| set hive.auto.convert.join=true; |
| set hive.auto.convert.join.noconditionaltask=true; |
| set hive.auto.convert.join.noconditionaltask.size=5000; |
| |
| explain select m1, m2, f2 from fact join dim1 on fact.d1=dim1.f1; |
| select m1, m2, f2 from fact join dim1 on fact.d1=dim1.f1; |
| |
| explain select m1, m2, f2, f4 from fact join dim1 on fact.d1=dim1.f1 join dim2 on fact.d2=dim2.f3; |
| select m1, m2, f2, f4 from fact join dim1 on fact.d1=dim1.f1 join dim2 on fact.d2=dim2.f3; |
| |
| explain select m1, m2, f2, f4 from fact join dim1 on fact.d1= dim1.f1 join dim2 on dim1.f2 = dim2.f3; |
| select m1, m2, f2, f4 from fact join dim1 on fact.d1= dim1.f1 join dim2 on dim1.f2 = dim2.f3; |
| |
| explain select m1, m2, f2, f4 from fact Left outer join dim1 on fact.d1= dim1.f1 Left outer join dim2 on dim1.f2 = dim2.f3; |
| select m1, m2, f2, f4 from fact Left outer join dim1 on fact.d1= dim1.f1 Left outer join dim2 on dim1.f2 = dim2.f3; |
| |
| explain Select m1, m2, f2, f4, f6, f8, f10, f12, f14 |
| from fact |
| Left outer join dim1 on fact.d1= dim1.f1 |
| Left outer join dim2 on dim1.f2 = dim2.f3 |
| Left outer Join dim3 on fact.d2= dim3.f5 |
| Left outer Join dim4 on dim3.f6= dim4.f7 |
| Left outer join dim5 on dim4.f8= dim5.f9 |
| Left outer Join dim6 on dim3.f6= dim6.f11 |
| Left outer Join dim7 on dim6.f12 = dim7.f13; |
| |
| Select m1, m2, f2, f4, f6, f8, f10, f12, f14 |
| from fact |
| Left outer join dim1 on fact.d1= dim1.f1 |
| Left outer join dim2 on dim1.f2 = dim2.f3 |
| Left outer Join dim3 on fact.d2= dim3.f5 |
| Left outer Join dim4 on dim3.f6= dim4.f7 |
| Left outer join dim5 on dim4.f8= dim5.f9 |
| Left outer Join dim6 on dim3.f6= dim6.f11 |
| Left outer Join dim7 on dim6.f12 = dim7.f13; |
| |