oracle/hive下两个大表的关联如何进行优化_oracle两张千万级的表关联查询优化

Oracle环境下：

hive下两个大表，一个1000个字段，一个800个字段，如何进行关联。

啸龙的答案：小表在前，大表在后，字段用到哪些选哪些。
文君：中心思想，分区或者分表;
有时间，主键再想办法分区分表 横向或者纵向拆表

网上答案：开个并行，用并行hash 算法跑SQL。结果：不行
利用MPP数据库架构(Greenplum)/Hadoop的思想进行优化：数据切割

第一步 优化流程

select a.*,b.* 
  from trackinfo a 
  left join pm_info b 
    on (cast(a.ext_field7 as bigint) = b.id)	 
1
2
3
4

第二步 排除无效字段，ext_field7字段缺失率很高（为空、字段长度为零、字段填充了非整数）情况

select a.*,b.* 
  from trackinfo a 
  left join pm_info b 
    on (a.ext_field7 is not null
   and length(a.ext_field7)>0
   and a.ext_field7 rlike '[0-9]+$' -- 判断是否为纯数字 
   and (cast(a.ext_field7 as bigint) = b.id))
1
2
3
4
5
6
7

第三步 想了很久，第二次优化效果效果不理想的原因，其实是在左关联中，虽然设置了左表关联字段为空不去关联右表，但是这样做，左表中未关联的记录（ext_field7为空）将会全部聚集在一个reduce中进行处理，体现为reduce进度长时间处在99%。
换一种思路，解决办法的突破点就在于如何把左表的未关联记录的key尽可能打散，因此可以这么做：若左表关联字段无效（为空、字段长度为零、字段填充了非整数），则在关联前将左表关联字段设置为一个随机数，再去关联右表，这么做的目的是即使是左表的未关联记录，它的key也分布得十分均匀
耗时从50分钟降为了1分钟32秒

select a.*,b.* 
  from trackinfo a 
  left join pm_info b 
    on (case when a.ext_field7 is not null
              and length(a.ext_field7)>0
              and a.ext_field7 rlike '[0-9]+$' -- 判断是否为纯数字,一定为整数  
             then 
			      cast(a.ext_field7 as bigint)
			 else
                  cast( ceiling(rand()*-65535) as bigint)
              end = b.id  
        )	
1
2
3
4
5
6
7
8
9
10
11
12

ceiling 天花板 向上取整

like 与 rlike, alike的区别

另一个帖子的建议

黑猴子的家：Hive 表的优化之 大表 Join 大表

1、空KEY过滤

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下

配置历史服务器
配置mapred-site.xml

<property>
    <name>mapreduce.jobhistory.address</name>	
	<value>hadoop102:10020</value>
</property>	
1
2
3
4

<property>
    <name>mapreduce.jobhistory.webapp.address</name>	
	<value>hadoop102:19888</value>
</property>		
1
2
3
4

启动历史服务器

[victor@hadoop102 hadoop]sbin/mr-jobhistory-daemon.sh start historyserver	
	
1
2

查看jobhistory

```php
http://192.168.1.102:19888/jobhistory 
1
2

创建三个表  


```sql
create table         ori(id bigint, time bigint, uid string, keyword string, 
url_rank int, click_num int, click_url string) 
row format delimited fields terminated by '\t';
 
create table nullidtable(id bigint, time bigint, uid string, keyword string,
 url_rank int, click_num int, click_url string) 
row format delimited fields terminated by '\t';
 
create table jointable(id bigint, time bigint, uid string, keyword string,
 url_rank int, click_num int, click_url string) 
row format delimited fields terminated by '\t';	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

测试不过滤空id

hive(default)> insert overwrite table jointable 
               select n.* 
			     from nullidtable n 
				 left join ori o 
				   on n.id = o.id;
1
2
3
4
5

随机分布空值，

设置5个reduce个数

hive(default)> set mapreduce.job.reduces=5;
1

insert overwrite table jointable 
select n.* from nullidtable n 
full join ori o
  on case when n.id is null then concat('hive',rand())
     else n.id end = o.id;

1
2
3
4
5
6

转载
https://blog.csdn.net/qq_28652401/article/details/83509424?spm=1001.2101.3001.6650.8&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-8-83509424-blog-104983299.pc_relevant_3mothn_strategy_and_data_recovery&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-8-83509424-blog-104983299.pc_relevant_3mothn_strategy_and_data_recovery&utm_relevant_index=9
此篇文章优化步骤不多，讲的不详细，还有两个图片挂了

假设ORACLE环境：

创建一个表p1 在表结构上多加一个字段 hash_value 并且根据hash_value进行List分区

例子

Create table p1(
Hash_value number,
Id
Name
Age
Nationality 
)
Partition by list(hash_value)
(
Partition p0 values(0),
Partition p1 values(1),
Partition p2 values(2),
Partition p3 values(3),
Partition p4 values(4)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

记得5年前遇到一个SQL，就是一个简单的两表关联，SQL跑了差不多一天一夜，这两个表都非常巨大，每个表都有几十个G，数据量每个表有20多亿，表的字段也特别多。

相信大家也知道SQL慢在哪里了，单个进程的PGA 是绝对放不下几十个G的数据，这就会导致消耗大量temp tablespace，SQL慢就是慢在temp来回来回来回…的读写数据。

遇到这种超级大表与超级大表怎么优化呢？这篇文章将告诉你答案。

首先创建2个测试表 t1,t2 数据来自dba_objects

create table t1 as select * from dba_objects;

create table t2 as select * from dba_objects;

我们假设 t1 和 t2 就是 两个超级大表， 要运行的 SQL： select * from t1,t2 where t1.object_id=t2.object_id;

假设t1 t2 都是几十个GB 或者更大， 那么你懂的，上面的SQL基本上是跑不出结果的。

有些人在想，开个并行不就得了，用并行 hash hash 算法跑SQL，其实是不可以的，原因不多说了。

我们可以利用MPP数据库架构(Greenplum/Teradata/vertica)思想，或者是利用HADOOP的思想来对上面的SQL进行优化。

MPP架构/HADOOP架构的很重要的思想就是把数据切割，把大的数据切割为很多份小的数据，然后再对小的进行关联，那速度自然就快了。

在Oracle里面怎么把大数据切成小数据呢，有两个办法，一个是分区，另外一个是分表。我这里选择的是分区，当然了看了这篇文章你也可以分表。

创建一个表P1，在T1的表结构基础上多加一个字段HASH_VALUE，并且根据HASH_VALUE进行LIST分区

CREATE TABLE P1(
HASH_VALUE NUMBER,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30)
)  
   PARTITION BY  list(HASH_VALUE)
(
partition p0 values (0),
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

同样的，在T2的表结构基础上多加一个字段HASH_VALUE，并且根据HASH_VALUE进行LIST分区

CREATE TABLE P2(
HASH_VALUE NUMBER,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30)
)  
   PARTITION BY  list(HASH_VALUE)
(
partition p0 values (0),
partition p1 values (1),
partition p2 values (2),
partition p3 values (3),
partition p4 values (4)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

注意：P1和P2表的分区必须一模一样

delete t1 where object_id is null;

commit;

delete t1 where object_id is null;

commit;

insert into p1
select ora_hash(object_id,4), a.*  from t1 a;  ---工作中用append parallel并行插入

commit;

insert into p2
select ora_hash(object_id,4), a.*  from t2 a;  ---工作中用append parallel并行插入

commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这样就把 T1 和 T2的表的数据转移到 P1 和 P2 表中了

那么之前运行的 select * from t1,t2 where t1.object_id=t2.object_id 其实就等价于下面5个SQL了

select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=0 and p2.hash_value=0;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=1 and p2.hash_value=1;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=2 and p2.hash_value=2;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=3 and p2.hash_value=3;
select * from p1,p2 where p1.object_id=p2.object_id and p1.hash_value=4 and p2.hash_value=4;

工作中，大表拆分为多少个分区，请自己判断。另外一个需要注意的就是ORA_HASH函数

oracle中的hash分区就是利用的ora_hash函数

partition by hash(object_id) 等价于 ora_hash(object_id,4294967295)

ora_hash(列,hash桶) hash桶默认是4294967295 可以设置0到4294967295

ora_hash(object_id,4) 会把object_id的值进行hash运算，然后放到 0,1,2,3,4 这些桶里面，也就是说 ora_hash(object_id,4) 只会产生 0 1 2 3 4

有兴趣的同学可以自己去测试速度。生产库采用这种优化方法，之前需要跑一天一夜的SQL，在1小时内完成。

为了简便，可以使用PLSQL编写存储过程封装上面操作。

当然了，如果使用hadoop 或者 greenplum 有另外的优化方法这里就不做介绍了。

道森出品必属精品 抄袭翻版必究
————————————————
版权声明：本文为CSDN博主「robinson1988」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/robinson1988/article/details/50756921

---

别的例子：

情况1，考虑先对Join中的一个表去重，以此结果过滤无用信息。这样一般会将其中一个大表转化为小表，再使用MapJoin 。
一个实例是广告投放效果分析，例如将广告投放者信息表i中的信息填充到广告曝光日志表w中，使用投放者id关联。因为实际广告投放者数量很少（但是投放者信息表i很大），因此可以考虑先在w表中去重查询所有实际广告投放者id列表，以此Join过滤表i，这一结果必然是一个小表，就可以使用MapJoin。

广告投放者信息表 i 去重查询所有实际广告投放者id
广告曝光日志表 w

投放者 id

情况2，考虑切分Join中的一个表为多片，以便将切片全部载入内存，然后采用多次MapJoin得到结果。
一个实例是商品浏览日志分析，例如将商品信息表i中的信息填充到商品浏览日志表w中，使用商品id关联。但是某些热卖商品浏览量很大，造成数据偏斜。例如

语句实现了一个innerjoin逻辑，将商品信息表拆分成2个表：

select * from
(
select 
     w.id, 
	 w.time, 
	 w.amount, 
	 i1.name, 
	 i1.loc, 
	 i1.cat
from w -- 广告曝光日志表

left outer join i sampletable(1 out of 2 on id) i1
)
union all
(
select w.id, w.time, w.amount, i2.name, i2.loc, i2.cat
from w -- 广告曝光日志表
left outer join i sampletable(1 out of 2 on id) i2
)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

以下语句实现了left outer join逻辑：

select 
     t1.id, 
     t1.time, 
	 t1.amount,
     coalease(t1.name,t2.name),
     coalease(t1.loc, t2.loc),
     coalease(t1.cat, t2.cat)
from (  
       select 
	        w.id, 
	   	    w.time,
	   	    w.amount, 
	   	    i1.name, 
	   	    i1.loc, 
	   	    i1.cat
       from w -- 广告曝光日志表
	   left outer join isampletable(1 out of 2 on id) i1
    
	 ) t1 left outer join i sampletable(2 out of 2 on id)t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

上述语句使用Hive的sample table特性对表做切分。
说实话，上面两段代码段没看懂
————————————————
版权声明：本文为CSDN博主「这个妹妹我见过」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_37090394/article/details/111470246

1. 比如hive下两个大表，一个1000个字段，一个800个字段，如何进行关联。

五 hive中的数据倾斜
5.1 空值数据倾斜

场景：如日志中，常会有信息丢失的问题，比如全网日志中的user_id，如果取其中的user_id和bmw_users关联，会碰到数据倾斜的问题。

解决方法1： user_id为空的不参与关联

select * 
from log a 
join bmw_users b 
on a.user_id = b.user_id and a.user_id is not null 
union all 
select * 
from log a 
where a.user_id is null 
;
1
2
3
4
5
6
7
8
9

解决方法2：赋与空值分新的key值

select * 
from log a 
left outer join bmw_users b 
on case when a.user_id is null then concat('dp_hive',rand() )  else a.user_id end = b.user_id ;  
1
2
3
4

结论：方法2比方法1效率更好，不但IO少了，而且作业数也少了。方法1 log读取两次，jobs数是2个，方法2 job数是1个，log读取一次。这个优化适合无效ID (比如 -99 ，‘’ , null )产生的倾斜问题，把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上，解决数据倾斜问题。附上Hadoop通用关联的实现方法 （关联通过二次排序实现的，关联的列为partition key 关联的列为 c1 和表的tag 组成排序的group key , 根据partition key 分配 reduce 同一reduce内根据group key 排序 ）

5.2 不同数据类型关联产生数据倾斜

场景：一张表s8的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。猜测问题的原因是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志，都到一个reduce上了，解决的方法验证了这个猜测。
————————————————
版权声明：本文为CSDN博主「朱培」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sdksdk0/article/details/51675005

select * 
  from s8_log a 
  left outer join r_auction_auctions b 
    on a.auction_id = cast(b.auction_id as string)
1
2
3
4

5.3 join的数据偏斜

5.3 Join的数据偏斜
MapReduce编程模型下开发代码需要考虑数据偏斜的问题，Hive代码也是一样。数据偏斜的原因包括以下两点：

1. Map输出key数量极少，导致reduce端退化为单机作业。

2. Map输出key分布不均，少量key对应大量value，导致reduce端单机瓶颈。

Hive中我们使用MapJoin解决数据偏斜的问题，即将其中的某个表（全量）分发到所有Map端进行Join，从而避免了reduce。这要求分发的表可以被全量载入内存。

极限情况下，Join两边的表都是大表，就无法使用MapJoin。

这种问题最为棘手，目前已知的解决思路有两种：

1. 如果是上述情况1，考虑先对Join中的一个表去重，以此结果过滤无用信息。这样一般会将其中一个大表转化为小表，再使用MapJoin 。

一个实例是广告投放效果分析，例如将广告投放者信息表i中的信息填充到广告曝光日志表w中，使用投放者id关联。因为实际广告投放者数量很少（但是投放者信息表i很大），因此可以考虑先在w表中去重查询所有实际广告投放者id列表，以此Join过滤表i，这一结果必然是一个小表，就可以使用MapJoin。

此 （广告曝光日志表w 去重）

————————————————
版权声明：本文为CSDN博主「朱培」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sdksdk0/article/details/51675005

mapreduce原理：

分而治之，一个大任务分成多个小的子任务（map）并行执行后，合并结果(reduce)

比如说我们有一千副扑克牌，不含大小王，混在一起，但是其中一副少了一张牌，所以总数是 51999 张，（一副扑克牌共54张牌，除了大小王还有52张）

先把牌随机的分一下，分成五份，分给五个人做，

五个人 分别统计自己手上分到的牌，每种牌出现的花色和次数，这就是Map的操作

统计的每个结果为：

然后我们约定好了， 凡是红色A的，我们放一个地方，红色1放一个地方，

然后进行再次统计，

找到有999张牌的那个，就是少的那张牌。

mapreduce工作原理

找出访问最多的IP地址：
一个作业job
一个JOB完成需要分成多个task
task分为 maptask reducetask

还有在整个hadoop mapreduce体系结构中有两类节点，第一个的job Tracker
它也是一个master管理节点 客户端提交一个任务（Job）过来，job Tracker把它放到候选队列里面去，在适当的时候来进行调度，选择一个job出来，将这个job拆分成多个map任务和reduce任务，这个map任务分发给下面的tasktracker来做，tasktracker就是实际做这个任务，做这个事情、、、、未完待续。。。

2022-11-16号
1.spark的组件包括