Hive管理表（内部表）数据加载及JOIN操作

> 数据加载

数据准备：
在本地先创建两个txt文件：

mahao@ubuntu:~$ cat user.txt 
1,zhangsan
2,lisi
3,wangwu

mahao@ubuntu:~$ cat job.txt 
1   工程师 1
2   美工  
3   美工  4

创建users表

hive> CREATE TABLE IF NOT EXISTS users(id INT,name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/tmp/testtable/user';

OK
Time taken: 2.726 seconds

如果hdfs文件系统中没有/tmp/testtable/user这个文件，就会自动创建。

创建job表

hive> CREATE TABLE IF NOT EXISTS job(id INT,job STRING,user_id INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/tmp/testtable/job';
OK
Time taken: 0.164 seconds

如果hdfs文件系统中没有/tmp/testtable/job这个文件，就会自动创建。

此时，如果hdfs文件系统中没有这两个目录，那么已经创建好了：

mahao@ubuntu:~$ hadoop fs -ls /tmp/testtable
Found 2 items
drwx-wx-wx   - mahao supergroup          0 2016-09-13 02:06 /tmp/testtable/job
drwx-wx-wx   - mahao supergroup          0 2016-09-13 02:05 /tmp/testtable/user

管理表（内部表）Load数据有3种：

1、

LOAD DATA [LOCAL] INPATH 'file_path' [OVERWRITE] INTO TABLE table_name 

如：

LOAD DATA LOCAL INPATH '/home/mahao/use.txt' OVERWRITE INTO TABLE users; 

2、

hdfs fs -put ***

如：

hdfs fs -puy /home/mahao/user.txt /tmp/testtable/user

3、从其他表中加载

如：

INSERT OVERWRITE TABLE employees 
PARTITION (country='US',state='OR')
SELECT * FROM staged_employees se WHERE se.cnty='US' AND se.st='OR';

这里我们选择第一种方式：

hive> LOAD DATA LOCAL INPATH '/home/mahao/user.txt' OVERWRITE INTO TABLE users;
Loading data to table default.users
OK
Time taken: 0.534 seconds

然后会发现hdfs文件系统下的/tmp/testtable/user目录下多了一个user.txt文件：

mahao@ubuntu:~$ hadoop fs -ls /tmp/testtable/user
Found 1 items
-rwx-wx-wx   1 mahao supergroup         27 2016-09-13 05:31 /tmp/testtable/user/user.txt

此时查看users表：

hive> SELECT * FROM users;
OK
1   zhangsan
2   lisi
3   wangwu
Time taken: 0.503 seconds, Fetched: 3 row(s)

成功将/home/mahao/user.txt文件中的数据导入users表。

为了演示，我们采用第二种方式加载job表。

mahao@ubuntu:~$ hadoop fs -put /home/mahao/job.txt /tmp/testtable/job
mahao@ubuntu:~$ hadoop fs -ls /tmp/testtable/job
Found 1 items
-rw-r--r--   1 mahao supergroup         35 2016-09-13 05:44 /tmp/testtable/job/job.txt

查看job表:

hive> select * from job;
OK
1   工程师 1
2   美工  NULL
3   美工  4
Time taken: 0.118 seconds, Fetched: 3 row(s)

> JOIN

INNER JOIN

只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。

hive>SELECT * FROM users JOIN job ON users.id=job.user_id;

结果：

id      name            id      job     user_id
1   zhangsan    1   工程师 1
2   lisi        2   美工  2

OUTER JOIN

OUTER JOIN包括LEFT OUTER JOIN和RIGHT OUTER JOIN。

LEFT OUTER JOIN：

包括左边表中的所有符合WHERE语句的记录，甚至是右边表中没有和它匹配的记录。右表中没有匹配的记录时，字段为NULL。

hive>SELECT * FROM users LEFT OUTER JOIN job ON users.id=job.user_id;

结果：

1   zhangsan    1   工程师 1
2   lisi        2   美工  2
3   wangwu      NULL    NULL    NULL

RIGHT OUTER JOIN：

包括右边表中的所有符合WHERE语句的记录，甚至是作左边表中没有和它匹配的记录。左表中没有匹配的记录时，字段为NULL。

hive>SELECT * FROM users RIGHT OUTER JOIN job ON users.id=job.user_id;

结果：

1   zhangsan    1   工程师 1
2   lisi        2   美工  2
NULL    NULL        3   美工  4

FULL OUTER JOIN：

包括左边表和右边表的所有符合WHERE语句的记录，没有匹配的字段为NULL。

hive>SELECT * FROM users FULL OUTER JOIN job ON users.id=job.user_id;

结果：

1   zhangsan    1   工程师 1
2   lisi        2   美工  2
3   wangwu      NULL    NULL    NULL
NULL    NULL        3   美工  4

LEFT SEMI JOIN（左半开连接）：

返回左表users的记录，这些记录的id在右表job的user_id中出现:

hive> SELECT * FROM users LEFT SEMI JOIN job ON users.id=job.user_id;

结果：

1   zhangsan
2   lisi

该语句相当于SQL的如下语句

select * from user where id in (select user_id from job);

但是，hive不支持in子句。所以只能变通，使用LEFT SEMI JOIN子句。

笛卡尔积JOIN

表示左边表的行数乘以右边表的行数等于笛卡尔积的大小。

hive> SELECT * FROM users join job;

结果：

1   zhangsan    1   工程师 1
2   lisi            1   工程师 1
3   wangwu      1   工程师 1
1   zhangsan        2   美工  2
2   lisi            2   美工  2
3   wangwu          2   美工  2
1   zhangsan        3   美工  4
2   lisi            3   美工  4
3   wangwu          3   美工  4

map-side JOIN

如果所有表中只有一个小表，那么可以在最大的表通过mapper时将小表完全放到内存中。在内存中，可以和小表进行逐一匹配。

开启这个功能，需要：

hive>set hive.auto.covert.join=true;

然后在必要的时候Hive会启动这个优化。

hive>SELECT * FROM ta join tb on ta.id=tb.id AND ta.ymd=tb.ymd 

可以设置能够使用这个优化的小表的大小：

hive>set hive.mapjoin.smalltable.filesize=25000000

大小是字节。