当对一个大表和一个或多个小表执行join *** 作时,可以在select语句中显式指定mapjoin Hint提示以提升查询性能。本文介绍如何通过mapjoin hint连接表。
功能介绍整个JOIN过程包含Map、Shuffle和Reduce三个阶段。通常情况下,join *** 作在Reduce阶段执行表连接。但是mapjoin在Map阶段执行表连接,而非等到Reduce阶段才执行表连接,可以缩短大量数据传输时间,提升系统资源利用率,从而起到优化作业的作用。
在对大表和一个或多个小表执行join *** 作时,mapjoin会将您指定的小表全部加载到执行join *** 作的程序的内存中,在Map阶段完成表连接从而加快join的执行速度。
此外,MaxCompute SQL不支持在普通join的on条件中使用不等值表达式、or等逻辑复杂的join条件,但是在mapjoin中可以进行上述 *** 作。
mapjoin *** 作的使用限制如下:
- mapjoin在Map阶段会将指定表的数据全部加载在内存中,因此指定的表仅能为小表,且表被加载到内存后占用的总内存不得超过512 MB。 由于MaxCompute是压缩存储,因此小表在被加载到内存后,数据大小会急剧膨胀。此处的512 MB是指加载到内存后的空间大小。
mapjoin中join *** 作的限制如下:
- left outer join的左表必须是大表。
- right outer join的右表必须是大表。
- 不支持full outer join。
- inner join的左表或右表均可以是大表。
- mapjoin最多支持指定128张小表,否则报语法错误。
您需要在select语句中使用Hint提示才会执行mapjoin。需要注意的是:
- 引用小表或子查询时,需要引用别名。
- mapjoin支持小表为子查询。
- 在mapjoin中,可以使用不等值连接或or连接多个条件。您可以通过不写on语句而通过mapjoin on 1 = 1的形式,实现笛卡尔乘积的计算。
例如select a.id from shop a join table_name b on 1=1;,但此 *** 作可能带来数据量膨胀问题。 - mapjoin中多个小表用英文逗号(,)分隔,例如/+ mapjoin(a,b,c)/。
--创建一张分区表sale_detail。 create table if not exists sale_detail ( shop_name string, customer_id string, total_price double ) partitioned by (sale_date string, region string); create table if not exists sale_detail_sj ( shop_name string, customer_id string, total_price double ) partitioned by (sale_date string, region string); --向源表增加分区。 alter table sale_detail add partition (sale_date='2013', region='china'); alter table sale_detail_sj add partition (sale_date='2013', region='china'); --向源表追加数据。 insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3); insert into sale_detail_sj partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2),('s2','c2',100.2);使用示例
对表sale_detail和sale_detail_sj执行join *** 作,满足sale_detail_sj中的total_price小于sale_detail中的total_price或sale_detail_sj中的total_price与sale_detail中的total_price之和小于500的条件,命令示例如下:
select a.shop_name, a.total_price, b.total_price from sale_detail_sj a join sale_detail b on a.total_price < b.total_price or a.total_price + b.total_price < 500;
返回结果:
±----------±------------±-------------+
| shop_name | total_price | total_price2 |
±----------±------------±-------------+
| s1 | 100.1 | 100.1 |
| s2 | 100.2 | 100.1 |
| s5 | 100.2 | 100.1 |
| s2 | 100.2 | 100.1 |
| s1 | 100.1 | 100.1 |
| s2 | 100.2 | 100.1 |
| s5 | 100.2 | 100.1 |
| s2 | 100.2 | 100.1 |
| s1 | 100.1 | 100.2 |
| s2 | 100.2 | 100.2 |
| s5 | 100.2 | 100.2 |
| s2 | 100.2 | 100.2 |
| s1 | 100.1 | 100.2 |
| s2 | 100.2 | 100.2 |
| s5 | 100.2 | 100.2 |
| s2 | 100.2 | 100.2 |
| s1 | 100.1 | 100.3 |
| s2 | 100.2 | 100.3 |
| s5 | 100.2 | 100.3 |
| s2 | 100.2 | 100.3 |
| s1 | 100.1 | 100.3 |
| s2 | 100.2 | 100.3 |
| s5 | 100.2 | 100.3 |
| s2 | 100.2 | 100.3 |
±----------±------------±-------------+
SELECt a., b.cnt AS cnt_category, c.cnt AS cnt_all FROM iris a JOIN ( SELECt COUNT() AS cnt,category FROM iris GROUP BY category ) b ON a.category = b.category JOIN ( SELECt COUNT(*) AS cnt FROM iris ) c;
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)