hive学习笔记

HIVE 基础

1. 基于Hadoop的数据仓库-datawarehouse。【是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合】
   2.数据基本不删除和修改；数据是面向主题的，数据是集成的，数据是不可更新的；数据由于不断灌入，所以是不断更新的。
   3.提供雷SQL的查询，本质是将MR包装，简化为HQL的编写。
   4.数据存在HDFS，HQL分析底层是由MR事先，执行程序运行在YARN上。

优缺点：
类SQL语法，简单易上手
避免MR编写
执行延迟比较高，常用于实时性要求不高的场合，常用于数据分析
优势在于处理大量数据
HIVE支持UDF/UDAF
表达能力有限，效率低（数据挖掘不擅长，迭代式算法无法表达）
粒度较粗，调优困难

HIVE运行过程：
客户端–>HQL结合metastore元数据—>(解析-编译-优化-执行)—>调度MR
【元数据包括：表名，表所属的数据库，表拥有者，表的字段，类型，表数据所在的目录】

HIVE和MYSQL比较：

除了拥有类似的查询语言之外再无相似之处；
数据库可以增删改查，支持OL应用；HIVE只能查-离线；
1）数据存储位置
	HIVE-HDFS，SQL-块设备/本地文件
2）数据更新
	数仓-读多写少，不支持改写和添加，只能追加，所有数据都是在加载的时候确定好的；
	SQL中数据需要经常修改
3）索引
	HIVE加载时不会扫描数据，也不会添加索引。因此HIVE检索数据时会暴力扫描全表，故此延迟较高。
	但是由于MR引入，可以实现并行数据访问，即使没有索引也能体现出优势。
	
	SQL中通常会针对若干列建立索引，对于少量特定调教的数据访问拥有很高的效率。
4）执行延迟：全表扫描+MR本身的延迟
5）可扩展性：HIVE等价与HADOOP的扩展能力  HIVE数万台，ORACLE-百太
6）数据规模

HIVE常用命令

查询

• hive -e “select * from stu” 【代表不进入HIVE命令行执行查询
  “-e”不进入hive的交互窗口执行sql语句
• hive -f /opt/module/datas/hivef.sql+
  执行文件中的sql语句
• hive -f /opt/module/datas/hivef.sql > /opt/module/datas/hive_result.txt
  （3）执行文件中的sql语句并将结果写入文件中

退出HIVE：
exit，先提交隐形数据在推出
quit：不提交数据直接退出

在hive中查看文件

在hive中直接查看HDFS中的文件：
dfs -lsr / 【递归查看所有数据
hive中查看本地文件：
！ ls /opt/modules

查看在hive所有历史命令

	1 进入到当前用户的家目录
	2 cat ~/.hivehistory

查看HIVE的配置信息

进入hive
hive>set;

参数配置方式

默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml

注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，
因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。

配置文件方式

默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml

注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，
Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。

命令行方式：
	启动Hive时，可以在命令行添加-hiveconf param=value来设定参数。

	例如：
	[atguigu@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;
		**注意：仅对本次hive启动有效**

查看参数设置：
	hive (default)> set mapred.reduce.tasks;
-------------
参数声明方式：
	可以在HQL中使用SET关键字设定参数

	例如：
hive (default)> set mapred.reduce.tasks=100;
注意：仅对本次hive启动有效。
--------------
查看参数设置
hive (default)> set mapred.reduce.tasks;

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数
例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

HIVE数据类型：9+3

tinyint--byte
smallint--short
int--int
bigint--long
float--float
double--double
string--string 理论存储2G的字符
timestamp---时间类型
binary---字节数组

集合数据类型
struct–KV以array存储

map--KV对

array

三种集合数据类型的访问方式
array–使用下标 array01[0]
map–使用K访问V kids[‘xiaosong’]
struct–可以通过“点”符号访问元素内容 address.city

类型转换

可以自动由小大 --隐式

可以使用CAST进行强制转换，
CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1
如果转换失败将返回NULL

数据表

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用-排序
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
| SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
（8）STORED AS 指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）LIKE允许用户复制现有的表结构，但是不复制数据。

实例

• 普通创建表

create table if not exists student2(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';

• 根据查询结果创建表

create table if not exists student3
as select id, name from student;

• 根据已经存在的表结构创建表

create table if not exists student4 like student;

• 查询表的类型

desc formatted student2;

外部表

因为表是外部表，所有Hive并非认为其完全拥有这份数据。
删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

使用场景：
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，
用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

当删除外部表的时候，表结构等元数据会被删除，但是源数据还在，如果仍在
当前库下建立一个同名同结构的外部表，数据会自动关联。

分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。
Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。
在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

语法
partitioned by

create table dept_partition(
        deptno int, dname string, loc string)
        partitioned by (month string)
        row format delimited fields terminated by '\t';

加载数据到分区表中

尾部添加关键字 partition（key=value）

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

查询

select * from dept_partition where month='201709';
>多分区联合查询
select * from dept_partition where month='201710'
              union
              select * from dept_partition where month='201712'
              union
              select * from dept_partition where month='201711';

增删分区

add/drop
注意增加分区时，分区字段之间空格隔开
删除分区时，分区字段之间用逗号隔开

alter table dept_partition add partition(month='201705') partition(month='201704');

 alter table dept_partition drop partition (month='201705'), partition (month='201706');

查看分区数目/结构

show partitions dept_partition;

desc formatted dept_partition;

二级分区

create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';

其实就是增加分区字段，期于都一样；

加载数据

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');

查询数据

select * from dept_partition2 where month='201709' and day='13';

分区表关联数据的方式

（1）方式一：上传数据后修复

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
执行修复命令
hive>msck repair table dept_partition2;

（2）方式二：上传数据后添加分区

上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
执行添加分区
hive (default)> alter table dept_partition2 add partition(month='201709', day='11');

（3）方式三：上传数据后使用load命令加载数据到分区

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');