HIVE笔记梳理

1. hive 基本操作

1.1 hive是什么？

HIVE是一个可以将sql翻译为MR程序的工具
HIVE支持用户将HDFS上的文件映射为表结构，然后用户就可以输入SQL对这些表（HDFS上的文件）进行查询分析
HIVE将用户定义的库、表结构等信息存储hive的元数据库（可以是本地derby，也可以是远程mysql）中

1.2 hive的用途？

解放大数据分析程序员，不用自己写大量的mr程序来分析数据，只需要写sql脚本即可
HIVE可用于构建大数据体系下的数据仓库

1.3 hive的使用方式

方式1：可以交互式查询：
** bin/hive -----> hive>select * from t_test;
** 将hive启动为一个服务： bin/hiveserver ，然后可以在任意一台机器上使用beeline客户端连接hive服务，进行交互式查询
方式2：可以将hive作为命令一次性运行：
** bin/hive -e “sql1;sql2;sql3;sql4”
** 事先将sql语句写入一个文件比如 q.hql ，然后用hive命令执行：　　bin/hive -f q.hql
方式3：可以将方式2写入一个xxx.sh脚本中

1.4 HIVE的DDL语法

1.4.1 建库

create database db1;

hive就会在/user/hive/warehouse/下建一个文件夹： db1.db

1.4.2 建内部表

use db1;
create table tb1 (
id int 
,name string )
row format delimited
fields terminated by '\001'

建表后，hive会在仓库目录中建一个表目录： /user/hive/warehouse/db1.db/t_test1

1.4.3 建外部表

create external table tb2(
id int
,name string)
row format delimited 
fields terminated by '\001'
location 'external/tb2'

内部表和外部表的区别

区别： 内部表的目录由hive创建在默认的仓库目录下：/user/hive/warehouse/…
外部表的目录由用户建表时自己指定： location ‘/位置/’

   drop一个内部表时，表的元信息和表数据目录都会被删除；
   drop一个外部表时，只删除表的元信息，表的数据目录不会删除；

意义： 通常，一个数据仓库系统，数据总有一个源头，而源头一般是别的应用系统产生的，
其目录无定法，为了方便映射，就可以在hive中用外部表进行映射；并且，就算在hive中把
这个表给drop掉，也不会删除数据目录，也就不会影响到别的应用系统；

1.4.4 导入数据

本质上就是把数据文件放入表目录；
可以用hive命令来做

1.4.4.1从本地或者从hdfs目录导入数据

load data [local] inpath '/data/path' [overwrite] into table tb1;

1.4.4.2 从别的表插叙数据后插入到一张新建表中

create table t1 
as 
select * from t2

1.4.5 建分区表

分区的意义在于可以将数据分子目录存储，以便于查询时让数据读取范围更精准；

create table tb3(
id int 
,name string)
partitioned by (day string ,country string)
row format delimited 
fields terminated by '\001' ;

1.4.6 插入数据到指定分区

load data [local] inpath '/data/path' [overwrite] into tb1 partition(day='2018-09-01', country='china');

导入完成后，形成的目录结构如下：
/user/hive/warehouse/db1.db/t_test1/day=2018-09-01/country=China/…

1.4.7 导出数据

1.4.7.1 将数据从hive的表中导出到hdfs的目录中

   insert overwrite directory '/aa/bb'
    select * from t1 

1.4.7.2 将数据从hive的表中导出到本地磁盘中

insert overwrite local directory '/aa/bb'
select * from t2 ;

1.4.8 修改表的分区

1.4.8.1 添加分区

alter table t1 add partition (day='2018-09-01')

1.4.8.2 检查分区情况

show partitions t1 ;

1.4.8.3 删除分区

alter table  t1 drop partition (day='2018-09-01')

1.4.9 修改表的列定义

1.4.9.1 添加列

alter table t1 add column (age int );

1.4.9.2 全部替换

alter table t1 replace columns (id int ,name string ,address string) 

1.4.10 显示命令

show tables                                                     // 显示表
show databases						// 显示数据库	
show partitions 						// 显示分区 
show functions 					   //  显示所有内置函数 
desc t1 					// 显示表定义
desc extended t1 				// 显示表定义的详细信息
desc formatted t1 				// 显示表定义的详细信息，并且用比较规范的格式显示
show create table t1   			// 显示建表语句

1.5 HIVE的dml

1.5.1 加载数据到表中

1.5.1.1 插入单条数据

insert into table t1 values ("10","xx","beijing");

1.5.1.2 多重插入

假如有一个需求
从t_4 中筛选出不同的数据，插入另外两张表中；

  insert overwrite table t_4_st_lt_200 partition(day='1')
select ip,url,staylong from t_4 where staylong<200;  
insert overwrite table t_4_st_gt_200 partition(day='1')
select ip,url,staylong from t_4 where staylong>200;

但是以上实现方式有一个弊端，两次筛选job，要分别启动两次mr过程，要对同一份源表数据进行两次读取
如果使用多重插入语法，则可以避免上述弊端，提高效率：源表只要读取一次即可
s
from t_4
insert overwrite table t_4_st_lt_200 partition(day=‘2’)
select ip,url,staylong where staylong<200
insert overwrite table t_4_st_gt_200 partition(day=‘2’)
select ip,url,staylong where staylong>200;

1.6 HIVE的存储文件格式

HIVE支持很多种文件格式： SEQUENCE FILE | TEXT FILE | PARQUET FILE | RC FILE

试验：先创建一张表t_seq，指定文件格式为sequencefile

create table t_seq(id int,name string,add string)
stored as sequencefile;

然后，往表t_seq中插入数据，hive就会生成sequence文件插入表目录中

insert into table t_seq
select * from t_1 where add='handong';