hive笔记，

和通数据库htsjk.Com2020-01-25 22:55 来源:未知阅读:4502 评论 540 热度3

标签：

hive笔记，

hive
---------------------------------------------------------------------------
---------------------------------------------------------------------------
对MySQL数据库进行初始化
进入到hive的bin目录执行命令：

cd /opt/hive/apache-hive-2.1.1-bin/bin
对数据库进行初始化，执行命令：
schematool -initSchema -dbType mysql

启动hive
进入到hive的bin目录执行命令：
cd /opt/hive/apache-hive-2.1.1-bin/bin

执行hive脚本进行启动，执行命令：
./hive

执行简单测试命令
执行了hive脚本，启动成功后，就进入了hive的命令行模式。下面进行一系列简单测试：
执行查看函数的命令：
show functions;

执行查看sum函数的详细信息的命令：
desc function sum;

---------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
在刚才创建的数据库中创建数据表，执行hive命令：
use db_hive_edu;
create table student(id int,name string) row format delimited fields terminated by '\t';

将文件数据写入表中
(1)在/opt/hive目录内新建一个文件
执行Linux命令(最好是重新打开一个终端来执行)：
touch /opt/hive/student.txt
说明：ID和name直接是TAB键，不是空格，因为在上面创建表的语句中用了terminated by '\t'所以这个文本里id和name的分割必须是用TAB键(复制粘贴如果有问题，手动敲TAB键吧)，还有就是行与行之间不能有空行，否则下面执行load，会把NULL存入表内，该文件要使用unix格式，如果是在windows上用txt文本编辑器编辑后在上载到服务器上，需要用工具将windows格式转为unix格式，例如可以使用Notepad++来转换。

完成上面的步骤后，在磁盘上/opt/hive/student.txt文件已经创建成功，文件中也已经有了内容，在hive命令行中执行加载数据的hive命令：
load data local inpath '/opt/hive/student.txt' into table db_hive_edu.student;

(2)查看是否写入成功
执行命令，查看是否把刚才文件中的数据写入成功，hive命令是：
select * from student;
说明：因为什么的操作使用use db_hive_edu;指定了数据库，所以这里直接用表名student，如果没有指定数据库，请把这个语句换成
select * from db_hive_edu.student;

(3)在界面上查看刚才写入hdfs的数据
我的hadoop的namenode的IP地址是192.168.229.139，所以我要在浏览器里访问如下地址：
http://192.168.229.139:50070/explorer.html#/user/hive/warehouse/db_hive_edu.db
(4)
在MySQL的hive数据库中查看
在MySQL数据库中执行select语句，查看hive创建的表，SQL是：
SELECT * FROM hive.TBLS
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
基本例子：
create table page_view(
viewTime int,
userid bigint,
page_url string,
referer_url string,
ip string comment 'ip address of user')
partitioned by (dt string,country string)
row format delimited fields terminated by '\001'
stored as sequencefile;

[root@hserver1 usr]# hadoop fs -ls /user/hive/warehouse/db_hive_edu.db/
17/08/25 15:17:12 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 4 items
drwxr-xr-x - root supergroup 0 2017-08-17 09:08 /user/hive/warehouse/db_hive_edu.db/data_base
drwxr-xr-x - root supergroup 0 2017-08-17 07:14 /user/hive/warehouse/db_hive_edu.db/student
drwxr-xr-x - root supergroup 0 2017-08-17 08:42 /user/hive/warehouse/db_hive_edu.db/t_test1
drwxr-xr-x - root supergroup 0 2017-08-17 07:23 /user/hive/warehouse/db_hive_edu.db/test

[root@hserver1 usr]# hadoop fs -ls /user/hive/warehouse/db_hive_edu.db/student
17/08/25 15:17:50 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 1 items
-rwxr-xr-x 2 root supergroup 63 2017-08-17 07:14 /user/hive/warehouse/db_hive_edu.db/student/student.txt
[root@hserver1 usr]#

[root@hserver1 hive]# hadoop fs -ls /user/hive/warehouse/db_hive_edu.db/student_tmp
17/08/25 15:52:31 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
drwxr-xr-x - root supergroup 0 2017-08-25 15:49 /user/hive/warehouse/db_hive_edu.db/student_tmp/academy=computer
drwxr-xr-x - root supergroup 0 2017-08-25 15:50 /user/hive/warehouse/db_hive_edu.db/student_tmp/academy=physics
[root@hserver1 hive]#

我们也常常会遇到需要向分区表中插入大量数据，并且插入前不清楚数据归宿的partition，此时，我们需要dynamic partition
 使用动态分区需要设置hive.exec.dynamic.partition参数值为true。
 可以设置部分列为dynamic partition列，例如：partition(academy='computer', class);
 也可以设置所有列为dynamic partition列，例如partition(academy, class);
 设置所有列为dynamic partition列时，需要设置hive.exec.dynamic.partition.mode=nonstrict
 需要注意的是，主分区为dynamic partition列，而副分区为static partition列是不允许的，例partition(academy, class=‘034’)是不允许的

bucket主要作用：
1. 数据sampling(抽样)
2. 提升某些查询操作效率，例如mapside join（map侧的join操作）
需要特别注意的是：clustered by和sorted by不会影响数据的导入，这意味着，用户必须自己负责数据如何如何导入，包括数据的分桶和排序。
'set hive.enforce.bucketing = true' 可以自动控制上一轮reduce的数量从而适配bucket的个数，当然，用户也可以自主设置mapred.reduce.tasks去适配bucket个数，
推荐使用'set hive.enforce.bucketing = true'

---------------------------------------------------------------------------------------------------------------------------------------
create table student_tmp (id int, name string)
partitioned by(academy string,class string)
row format delimited fields terminated by ',';

id，name是真实列，partition列academy和class是伪列，并不存在于真实数据中

load data local inpath '/opt/hive/student_tmp.txt' into table student_tmp partition(academy='computer', class='034');
load data local inpath '/opt/hive/student_tmp.txt' into table student_tmp partition(academy='physics', class='034');

----------------------------------------------------------------------------
set mapreduce.map.memory.mb=8192;
set mapred.map.child.java.opts=-Xmx7792m;
set mapreduce.reduce.memory.mb=8192;
set mapred.reduce.child.java.opts=-Xmx7792m;

--------------------------------------------
hive> show partitions student_tmp;
OK
academy=computer/class=034
academy=physics/class=034

---------------------------------------------------------
alter table table_name add partition (ds = %YYYYMMDD%)
alter table table_name add partition (dt=20170803) location '/opt/hive/warehouse/table_name/part20170803'
alter table table_name drop partition (ds = %YYYYMMDD%)
alter table_name rename to new_table_name
alter table_name change col_old_name col_new_name column_type;
alter table_name change col_old_name col_new_name column_type after column_name;
alter table table_name add columns (col_name data_type);
-------------------------------------------------------
Load
HIVE装载数据没有做任何转换加载到表中的数据只是进入相应的配置单元表的位置移动数据文件。纯加载操作复制/移动操作。

insert

1.
insert overwrite table table_name
select *
from xxx
2.
动态分区插入：
insert overwrite table table_name partition (ds = %YYYYMMDD%)
select
*
from
xxx
3.多表插入：
from from_statement
insert overwrite table tablename1 [partition(ds = xx)] select_statement
insert overwrite table tablename2 [partition(ds = xx)] select_statement

4.insert时，自己即可放在select子句前，也可放在select后

5.导出文件到本地
insert overwrite local directory '/tmp/local out' select a.* from poke a;

6.导出文件到HDFS
insert overwrite directory 'hdfs文件路径'
select a.* from poke a where dt = '20170814';

-----------------------------------------------------------------------------
hive shell

------------------------------------------------------------------------------
drop
删除一个内部表的同时会同时删除表的元数据和数据
删除一个外部表，只删除元数据，而保留数据
------------------------------------------------------------------------------
set mapred.reduce.tasks = 1
select * from sales sort by amount desc limit 5;
------------------------------------------------------------------------------
select
xx
from
table_reference
where
condition
group by
col_list
order by
col_list
cluster by
col_list
limit number;

order by:全局排序；
sort by:分区排序，即每个reduce任务排序；
distribute by:按照指定的字段或表达式对数据进行划分，输出到对应的reduce或文件中
cluster by:distribute by 和 sort by 功能的联合；

-------------------------------------------------------------------------------
调用脚本：
add file /tmp/tt.py,
from networks a MAP a.networkid using 'python tt.py' as nn where a.ds = '20170829' limit 10;

add file weekday_mapper.py;
insert overwrite table u_data_new
select
transform(userid,novieid,rating,unixtime)
using 'python weekday_mapper.py'
as (userid,movieid,rating,weekday)
from u_data;

weekday_mapper.py
import sys
import datetime

for line in sys.stdin:
line = line.strip()
userid,movieid,rating,unixtime = line.split('\t')
weekday = datetime.datetime.from timestamp(float(unixtime)).isoweekday()
print '\t'.join([userid,movieid,rating,str(weekday])
-------------------------------------------------------------------------------
hive 只支持等值连接，外连接，和left/right join
hive 不支持所有的非等值连接，因为非等值连接很难转换到map/reduce任务
join 的关键字必须在 on()中指定，不能在where中指定，否则会先做笛卡尔积，再过滤
先join操作，然后再where操作，最后再select

reduce 会缓存join序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这有助于reduce端减少内存的使用量。实践中，应该把
最大的那个表写在最后（否则会因为缓存）

全外连接 full join
以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为null.
是否指定outer关键字，对结果无影响
full join时不会使用map join来优化

left semi join(半连接)
以left semi join前面的表为主表，返回主表的key也在副表中的记录
可能得到的结果比common join记录偏少，因为它遇到右表的重复记录会跳过
原理：将左表的key抽出放进内存就，传输到各个task tracker上，在每一个task执行map任务的时候，就进行key的过滤操作，减少传输到reduce端的数据量

semi join ，半连接，是从分布式数据库总借鉴过来的方法，

一个普通的join任务可能转换为1个mapreduce任务，也可能多个mapreduce任务，只有当所有的join on 里都使用了通一个key,并且该key不是最后一个join表的key,此时才会转换为1个mapreduce任务，否则任务数=参与join的表数目

-------------------------------------------------------------------------------------------------

HIVE参数设置
一般参数有3种设定方式：
配置文件，
hive-site.xml
hive-default.xml

命令行参数
启动hive客户端时，在命令行添加参数：
bin/hive -hiveconf hive.root.logger=INFO,console

参数声明
在hql中使用set关键字设定参数，如：
set mapred.reduce.tasks=100;

配置优先级
参数声明覆盖命令行参数，命令行参数覆盖配置文件设定

------------------------------------------------------------------
hive聚合函数
1.nvl(x,y) Returns y if x is null else return x

2. string A || string B || …(同concat函数)

3.T decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

4.int INSTR((string,str[,start][,appear])
返回string 中str 出现的位置。start 代表开始搜索的位置，可选参数，默认为1（1表示第一个字符位置）；appear 指示搜索第几次出现的，可选参数，默认为1；若没有匹配到，返回0。（ start和appear 取值为大于0的整数）

5.if(boolean testCondition, T valueTrue, T valueFalse)
Return valueTrue when testCondition is true, returns valueFalseOrNull otherwise

6. int datediff(string enddate, string startdate)
Return the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2

7.mod（n1,n2)
返回一个n1 除以n2 的余数。支持的类型有Byte、Short、Integer、Long、Float、Double。
返回值的正负和n1 相关。使用此函数需要注意
的2 个问题：
1、对小数执行mod 计算可能会产生精度丢失，（如mod(3.1415926535897384626,3.1415926535897384627，返回结果为0.0)
2、传入比MAX_LONG 还大的整数作为参数，则参数会被自动升级成Double 类型，函数也可以正常计算结果，但返回的结果是小数类型。

8. next_day()
计算给出日期date 之后的下一个星期day 的日期。day 是数字， 1-7分别表示星期日-六；返回日期的格式为“YYYY-MM-DD”

9. to_number()
将给出的字符转换为数字；string 必须为全数字串。（ Oracle 中的to_number 很复杂，可变参数且支持多种类型。当前仅支持整数类型，包括short、int、long）

10. Stirng to_char(date,format)
将日期date 转化为一个字符串；date 的格式固定为yyyy-mm-dd hh24:mi:ss:ff3，输出的格式由format 指定。format 当前支持的格式如下（不区分大小写）：
yyyymmdd，年月日；
yyyymm，年月；
mm，月
dd，日
yyyy-mm-dd
yyyy-mm
yyyymmddhh24miss，年月日时分秒（24小时制）
yyyy-mm-dd hh24:mi:ss
hh24miss
yyyymmddhh24missff3，年月日时分秒毫秒（24小时制）

11.last_day(date)
Returns the last day of the month extracted from the provided date value argument.
date格式为"yyyy-MM-dd"的字符串(开头为该形式即可)，返回的值是1到31之间的值，如果输入不正确则返回NULL。yyyy-MM-dd 是事先约定的输入格式

12.add_months( date, n )
Returns a date plus n months.
date is the starting date (before the n months have been added). n is the number of months to add to date. 注：目前输入的date格式为"yyyy-MM-dd"或者"yyyyMMdd"（以这个格式开头的都可以接受，否则返回null，下面同上），返回字符串也是这个格式

13. cast(expr as <type>)
Expected "=" to follow "type"
Converts the results of the expression expr to <type>
cast('1' as BIGINT) will convert the string '1' to it integral representation.
A null is returned if the conversion does not succeed.

14.date_add(string startdate, int days)
Add a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'

15.date_sub(string startdate, int days)
Subtract a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'

16.concat(string|binary A, string|binary B...)
e.g. concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.

17.concat_ws(string SEP, string A, string B...)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：
hive> select concat_ws(',','abc','def','gh') from lxw_dual;
abc,def,gh

18.parse_url(url, partToExtract[, key]) - extracts a part from a URL
解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。
举例：
* parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')返回'facebook.com'
* parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')返回'/path/p1.php'
* parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')返回'query=1'，
可以指定key来返回特定参数，例如
* parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY','query')返回'1'，

* parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF')返回'Ref'
* parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL')返回'http'

19.regexp_extract(str, regexp[, idx]) - extracts a group that matches regexp
字符串正则表达式解析函数。
-- 这个函数有点类似于 substring(str from 'regexp') ..
参数解释:
其中：
str是被解析的字符串
regexp 是正则表达式
idx是返回结果取表达式的哪一部分默认值为1。
0表示把整个正则表达式对应的结果全部返回
1表示返回正则表达式中第一个() 对应的结果以此类推
注意点：
要注意的是idx的数字不能大于表达式中()的个数。
否则报错：
实例：
如：
select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0) from default.dual;
得到的结果为:
x=18abc
select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',1) from default.dual;
得到的结果为:
18
select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',2) from default.dual;
得到的结果为:
abc
我们当前的语句只有2个()表达式所以当idx>=3的时候就会报错
正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意，在有些情况下要使用转义字符
举例：
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 1) from dual;
the
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) from dual;
bar
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 0) from dual;
foothebar

19.
语法: regexp_replace(string A, string B, string C)
返回值: string
说明：将字符串A中的符合Java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符
举例：
hive> select regexp_replace(‘foobar’, ‘oo|ar’, ”) from dual;
fb

20.
语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
举例：
hive> select substr(‘abcde’,3,2) from dual;
cd
hive>select substring(‘abcde’,-2,2) from dual;
de

------------------------------------------------------------------------------
UDF(user-defined-function)
自定义的UDF需继承org.apache.hadoop.hive.sql.UDF
需实现evaluate函数
1.把程序打包jar文件
2.进入hive客户端，添加jar包
add jar /opt/hive/jar/udf_test.jar
3.创建临时函数
create temporary function add_example as 'hive.udf.Add';
4.查询
select add_example(4,5) from scores;
5.销毁临时函数
drop temporary function add_example;
6.在使用UDF的时候，会自动进行类型转换
select add_example(8,9.4) from scores;
7.UDF只能实现一进一出的操作

UDF函数中参数类型可以为Writable,也可为java中的基本数据对象
UDF支持变长参数
客户端退出时，临时函数自动销毁
evaluate函数必须有返回类型，不能为void
----------------------------------------------------------------------------------
UDAF(user-defined aggregation function)
1.需导入两个包
org.apache.hadoop.hive.sql.exec.UDAF 和 org.apache.hadoop.hive.ql.exec.UDAFEvaluator
2.函数类需要基础UDAF类，内部类Evaluator实现UDAFEvaluator接口
3.Evaluator需实现init、iterate、terminatePartial、merge、terminate这几个函数。

UDFA是基于表的所有记录进行的计算操作

-------------------------------------------------------------------------------------
UDTF(user-defined table-generating functions)
用来解决输入一行输出多行的需求
继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
实现initialize,process,close三个方法
两种使用方法：
1.直接在select中使用
select explode_map(properties) as (col1,col2) from src;
2.和lateral view一起使用
select src.id,mytable.col1,mytable.col2 from src lateral vie explode_map(properties) mybable as col1 ,col2;

-----------------------------------------------------------------------------------------------
HIVE的MAP/REDUCE
Map:
以JOIN ON条件中的列作为key，如果有多个列，则key是这些列的组合
以JOIN之后所关心的列作为Value,当有多个列时，value是这些列的组合
在Value中还会包含表的Tag信息，用于表明此Value对应于哪个表
按照key进行排序
Shuttle:
根据Key的值进行Hash，并将Key/Value对按照 Hash 值推至不同的 Reduce 中
Reduce:
Reducer根据Key值进行Join操作，并且通过Tag来识别不同的表中的数据

--------------------------------------------------------------------------------------------------------------

注意点：
1.字符集 utf-8

2.压缩
hive.exec.compress.output这个参数，默认是false,表明对结果不做压缩

3.count(distinct)
当前的 Hive 不支持在一条查询语句中有多个Distinct
如果要在hive查询中实现多个Distinct,需要使用至少 n + 1 条查询语句（n为distinct的数目）
前 n 条查询分别对 n 个列去重，最后一条查询语句对 n 个去重之和的列做join操作，得到最终的结果

4.join
只支持等值连接

5.DML操作
只支持insert/load操作，无update和delete

6.不支持 having

7.不支持 where子句中的子查询

8.join中处理null值的语义区别
hive的join中，作为join key的字段比较，null=null是有意义的，且返回值为true
如果要与标准一致的语义，需过滤null值的情况，这是导致数据倾斜的原因之一。

9.分号字符
分号是sql语句结束的标志，在hive 解析中，只要遇到分号就认为是语句结束，而无论是否用引号包含起来。
解决办法：使用分号的八进制的ASCII码进行转义，
select concat(cookie_id,concat('\073','zoo')) from c02_fatdt limit 2;

10.insert
insert 必须加 "overwrite"关键字，每一次插入都是一次重写
如果要增加，可将原来的数据集和新增的数据集进行union all，然后重写表

-----------------------------------------------------------------------------------------------
优化
1.hadoop计算框架特性
数据量大不是问题，数据倾斜是个问题
jobs数比较多的作业效率相对比较低，比如几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长，原因是mapreduce作业初始化的时间是比较长的

count(distinct),在数据量大的情况下效率很低，如果多count(distinct)效率更低

2.优化手段
（1）好的设计模型
（2）解决数据倾斜问题
（3）减少job数
（4）设置合理的map reduce的task数，能有效提升性能
（5）了解数据分布，手动解决数据倾斜问题
set hive.groupby.skewindata=true;
(6)数据量较大的情况下，慎用count(distinct),容易产生数据倾斜
(7)对小文件进行合并
(8)整体最优
3.全排序
sort by 只在单机范围内排序

4.笛卡尔积
当hive设定严格模式（hive.mapred.mode=strict）时，不允许在hql中出现,笛卡尔积。
因为找不到join key ,hive只能使用1个reducer来完成笛卡尔积。

mapjoin

5.exist/in子句改写
select a.key,a.value from a where a.key in (select b.key from b);
改为:
select a.key,a.value from a left outer join b on (a.key = b.key) where b.key <> null;
或：
select a.key ,a.value from a left semi join b on (a.key = b.key);

6.怎样决定reducer个数
基于以下两个设定：
hive.exec.reducers.bytes.per.reducer(默认1000^3)
hive.exec.reducers.max(默认999)

计算reducer数的公式很简单：
N=min(参数2,从输入数据量/参数1)
依据经验：将参数2设定为0.95*(集群中TaskTracker个数)

7.join原则
将条目少的表/子查询放在Join操作符的左边。
原因：在join操作的Reduce阶段，位于Join操作符左边的表的内容会被加载到内存，将条目上的表放在左边，可有效减少发生oom错误几率。

8.MapJoin
join操作在Map阶段完成，不再需要Reduce
select /*+MAPJOINI(pv)*/ pv.pageid,u.age from page_view pv join user u on (pv.userid = u.userid);

相关参数：
hive.join.emit.interval =
hive.mapjoin.size.key = 10000
hive.mapjoin.cache.numrows=10000
mapjoin能够进行不等值连接的join操作

如果是一张大表关联多张小表，可以使用mapjoin加速
map join主要参数：
hive.auto.convert.join:是否自动转换为mapjoin
hive.mapjoin.smalltable.filesize:小表的最大文件大小，默认为25000000，即25M
hive.auto.convert.join.noconditionaltask:是否将多个mapjoin合并为1个
hive.auto.convert.join.noconditionaltask.size:多个mapjoin转换为1个时，所有小表的文件大小总和的最大值

合并mapjoin的好处：
每个mapjoin都要执行一次map,需要读写一次数据，所以多个mapjoin需要多次的数据读写，合并mapjoin后只用读写一次，加快了速度。
但执行map是有内存大小限制的，在一次map里对多个小表做mapjoin就必须把多个小表加载到内存，为了防止溢出，所以加了:
hive.auto.convert.join.noconditionaltask.size参数来做限制。这个值只限制输入的表的文件大小，不代表实际mapjoin的hashtable大小

9.数据倾斜
1.空值null导致
解决1：过滤null,null不参与join关联
select * from log a //*代表a中所有字段
join bmw_users b
on (a.user_id is not null
and a.user_id = b.user_id)
union all
select * from log a //*代表a中所有字段
where a.user_id is not null;

解决2：赋予null字段新的key值
select *
from 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; //dp_hive是任意的一个字符串

结论：方法2比1效率高，io少了，作业数也少了
方法2job数是1，这个优化适合无效id产生的倾斜问题
把空值的key变成一个字符串加随机数，就能把倾斜的数据分到不同的reduce上，解决数据倾斜的问题。

2.不同数据类型关联产生数据倾斜
解决：把数字类型转换为字符串类型
select * from s8_log a
left join r_auction b
on a.auction_id = cast(b.auction_id as string);

3.大表join的数据偏斜
原因包括以下两点：
Map输出的key数量极少，导致reduce端退化为单机作业
Map输出的key不均匀，少量key对应大量value,导致reduce端单机瓶颈
解决：使用MapJoin解决，将某个表（全量）分发到所有的Map端进行join，从而避免reduce
假如join的两边都是大表，就无法使用mapjoin.此时：
切分join中的一个表为多片，以便将切片全部载入内存，然后再多次Mapjoin.
如：
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 //拆分1/2的数据
)
union all
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 //拆分1/2的数据
)

10.合并小文件
文件数目过多，会给HDFS带来压力，影响处理效率，可通过合并Map和Reduce的结果文件来消除这样的影响：
hive.merge.mapfiles=true 是否合并Map输出文件，默认为True
hive.merge.mapredfiles=false 是否合并Reduce输出文件，默认为False
hive.merge.size.per.task = 256*1000*1000合并文件的大小