Hive如何实现join操作

除了简单的聚合（group by）、过滤（where）操作，Hive还能执行连接（join on）操作。

这两张表都有一个相同的字段userid，根据这个字段可以将两张表连接起来

SELECT pv.pageid, u.age FROM page_view pv JOIN user u ON (pv.userid = u.userid);

SELECT pv.pageid, u.age FROM page_view pv JOIN user u ON (pv.userid = u.userid);

同样，这个SQL命令也可以转化为MapReduce计算，连接的过程如下图所示。

从图上看，join的MapReduce计算过程和前面的group by稍有不同，因为join涉及两张表，来自两个文件（夹），所以需要在map输出的时候进行标记，比如来自第一张表的输出Value就记录为<1, X>，这里的1表示数据来自第一张表。这样经过shuffle以后，相同的Key被输入到同一个reduce函数，就可以根据表的标记对Value数据求笛卡尔积，用第一张表的每条记录和第二张表的每条记录连接，输出就是join的结果。如果打开Hive的源代码，看join相关的代码，会看到一个两层for循环，对来自两张表的记录进行连接操作。

Hive的数据模型

Hive的数据模型类似于RDBMS库表结构，此外还有自己特有模型，Hive的数据在粒度上分为三类：Table表，Partition分区、Bucket桶

数据库（Database）:Hive中也分多个数据库，可以理解为多个数据仓库。Hive的数据都是存储在HDFS上的

Table表：Hive表与关系数据库中的表相同，Hive中的表所对应的数据通常是存储在HDFS中，而表相关的元数据是存储在RDBMS中。怎么理解，就是说不管用户有多少个数据库（数据仓库），数据库下面有多少表，所有这些表只存储元数据信息，而所有表对应的数据都是存储在HDFS中的

Partitions分区：分区是一种优化手段，是指根据需要分区的列的值将表划分为不同分区，这样可以更快地对指定分区数据进行查询。

Buckets分桶：也是一种优化手段，是指根据表中字段的值，经过hash计算规则将数据划分为指定的若干个小文件，可以优化join查询和方便抽样查询。

Hive数据类型

Hive支持两种数据类型，一种原子数据类型、还有一种叫复杂数据类型。

原子数据类型

Hive类型中的String数据类型类似于MySQL中的VARCHAR。该类型是一个可变的字符串。

Hive支持数据类型转换，Hive是用Java编写的，所以数据类型转换规则遵循Java ：

隐式转换 --> 小转大

强制转换 --> 大传小

复杂数据类型

Hive常用命令

将mysql中的数据直接保存到Hive中

sqoop export --connect jdbc:mysql://192.168.8.103:3306/hmbbs --username root --password hadoop --export-dir '/user/hive/warehouse/pv_2013_05_31/000000_0' --table pv

删除表中数据，但要保持表的结构定义

dfs -rmr /user/hive/warehouse/records;

显示所有函数

show functions;

查看函数用法

describe function substr;

内连接

SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

外连接

SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);

查看hive为某个查询使用多少个MapReduce作业

Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);

显示表名的分区

show partitions 表名;

创建数据库

CREATE SCHEMA userdb;

删除数据库

DROP DATABASE IF EXISTS userdb;

DROP SCHEMA userdb;

创建数据表

use xxdb; 
create table xxx;              #内部表

创建一个表，结构与其他一样

create table xxx like xxx;

创建一个表，结构数据与其他一样，相当于复制一个表

create table xxx as xxx;

创建内部表

创建内部表，制定分隔符为tab键

create table tb_name(name1 int,name2 string) row format delimited fields terminated by '\t';

创建外部表

创建外部表 ，创建外部表的一般情况指的是：先有文件存放着数据，之后我们再来创建表，也就是说创建一张表，然后指向这个有数据的目录。以后只要是向这个目录中上传符合格式的数据会被自动装在到数据库表中，因为在metastore（元数据）会记录这些信息，创建外部表，制定分隔符为tab键

create external table tb_name(name1 int,name2 string) row format delimited fields terminated by '\t';

创建分区表

创建分区表，一般用于数据量比较大的情况下， partitioned by (logdate string)用来指定按照什么进行分区

创建分区：分区依据（Id int）
create table tb_name(
    id int,
    name string
) partitioned by (Id int) 
  row format delimited fields terminated by '\t';

普通表和分区表区别：有大量数据增加的需要建分区表

内外表转换

内部表转外部表

alter table table-name set TBLPROPROTIES('EXTERNAL'='TURE');

外部表转内部表

alter table table-name set TBLPROPROTIES('EXTERNAL'='FALSE');

删除分区

#注意：若是外部表，则还需要删除文件（hadoop fs -rm -r -f hdfspath）

alter table table_name drop if exists partitions (d='2016-07-01');

加载数据列表

把本地数据装载到数据表，也就是在metastore上创建信息

load data local inpath '/root/a.txt' into table tb_name;

把HDFS上的数据装载到数据表

load data inpath '/target.txt' into table tb_name;

加载数据到分区表必须指明所属分区

load data local inpath './book.txt' overwrite into table tb_name partition (Id = 10);

重命名表名

ALTER TABLE 表名1 RENAME TO 表名2;

删除表

drop table 表名;或者drop table if exists 表明;

插入表数据

向有分区的表插入数据

（1）覆盖现有分区数据，如果没有该指定分区，新建该分区，并且插入数据

INSERT OVERWRITE TABLE 库名.表名 PARTITION(dt='2018-09-12',name='Tom', ...)

SELECT ... FROM 库名.表名 where...

（2）向现有的分区插入数据 (之前的数据不会被覆盖)

INSERT INTO TABLE 库名.表名 PARTITION(dt='2018-09-12',name='Tom',...)

SELECT ... FROM 库名.表名 WHERE ...

向无分区的表插入数据

(1) 覆盖原有表里的数据，命令和有分区的表类似，只是去掉后面的PARTITION（dt=’ ‘,name=’'）

INSERT OVERWRITE TABLE 库名.表名

SELECT ... FROM 库名.表名 where...

(2) 向现有的表插入数据 (之前的数据不会被覆盖)

INSERT INTO TABLE 库名.表名

SELECT ... FROM 库名.表名 WHERE ...

表结构修改

增加字段

alter table table_name add columns(newscol1 int conment '新增')；

改字段

alter table table_name change col_name new_col_name new_type;

删除字段

删除字段（COLUMNS中只放保留的字段）

alter table table_name replace columns(col1 int,col2 string,col3string);

字段类型

tinyint ，smallint，int，bigint，float，decimal，boolean，string

复合数据类型

struct，array，map

分桶表

对于每一个表或者分区，Hive可以进一步组织成桶，也就是说桶是更为细精度的数据范围划分。

桶的使用一定要设置如下属性：

hive.enforce.bucketing = true;

创建一个桶

# 按（id）分为4个bucket 
create table tb_name ( 
    id int, 
    name string 
) clustered by (id) into 4 buckets 
    row format delimited fields terminated by ',';

通过子查询插入数据：

insert into tb_name1 select * from tb_name;

创建一个视图

create view v_name as 
    select table1.column1, table2.column2, table3.column3 
    where table1.column1 = table2.column2;

查看视图详细信息

describe extended valid_records;