浅谈hive
hive
支持sql标准的数据仓库，可以将sql语句转化成mr程序执行。基础分析一般用hive来做，比较复杂的用mr来做
数据仓库和数据库的区别
数据仓库：历史数据，面向分析，保证数据的完整性可以允许数据冗余。
数据库：存储结构化，在线数据，面向业务，使用范式来减少冗余。
hive中有解析器，编译器，优化器。hive最终会将sql语句转成mr程序（select * from 表 除外，消耗时间过长所以不转mr）
hive中也是有表的结构，来处理结构化数据，hive的元数据存储在关系型数据库（默认derby）中，元数据指表的名字、表的列和分区及其属性（是否为外部表）、表的数据所在目录等
架构
Driver：解析器，编译器（将hql转成很多操作符（树状结构），操作符是hive的最小单元，每个操作符表示对hdfs操作或者mr任务），优化器
常用操作符：select operator（查找），tablescan operator（扫描），limit（限制输出），file output operator（文件输出）。
hive的解析是通过antlr进行解析的，生成一个语法树生成上面的策略
将HQL转成一个抽象的语法树，然后再转成一个查询块，接着转成逻辑计划并重写逻辑计划，再将逻辑计划转成物理计划（mr任务，可能有多个），最终从多种物理计划中选择最佳策略
元数据库：存放元数据，需要用到元数据信息执行从这里拿
Thrift：跨语言跨平台的socket通讯组件，可以进行跨语言跨平台的数据交互，支持多种序列化和反序列化，MetaStoreServer模式中hive客户端和MetaStoreServer通讯就是用Thrift组件
Hadoop：hive解析成mr程序后提交给Hadoop进行执行
三种模式（根据元数据存储的位置来划分）
本地模式（测试）
元数据存放在derby数据库（hive自带）中，在同一台中
单用户模式
元数据存放在自己选的数据库中，笔者这里用mysql，hive直接从MySQL中获取元数据。
只允许一个hive客户端来访问MySQL，如果两个hive元数据都放在MySQL中，没有组件对元数据进行管理，两个hive同时操作会出问题
多用户模式
元数据存放在MySQL中，hive客户端通过MetaStoreServer来从MySQL中获取元数据（多个hive客户端操作一份元数据），采用Thrift协议。
由MetaStoreServer来管理元数据信息，多个hive通过MetaStoreServer来拿元数据，MetaStoreServer会通过访问的顺序来返回
安装（依赖Hadoop的环境变量，启动Hadoop）
安装之前需要查看和Hadoop版本：http://hive.apache.org/downloads.html，笔者这里采用的是2.3.4版本
1.本地模式
cp conf/hive-env.sh.template conf/hive-env.sh
添加HADOOP_HOME=/opt/hadoop-2.7.5
cp conf/hive-default.xml.template conf/hive-site.xml
<configuration>
<property>
<!--连接数据库的地址:数据库derby，数据库名称metastore_db，是否需要创建数据库-->
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
</property>
<property>
<!--驱动包-->
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
</property>
<property>
<!--是否使用hive本地来管理元数据，一定是true-->
<name>hive.metastore.local</name>
<value>true</value>
</property>
<property>
<!--hive对应HDFS的数据目录-->
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
</configuration>
初始化derby数据库
bin/schematool -dbType derby -initSchema
进入hive
bin/hive
2.单用户模式（依赖MySQL）
cp conf/hive-env.sh.template conf/hive-env.sh
添加HADOOP_HOME=/opt/hadoop-2.7.5
cp conf/hive-default.xml.template conf/hive-site.xml
<configuration>
<property>
<!--HDFS中真实数据目录，空目录或不存在-->
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive_remote/warehouse</value>
</property>
<property>
<!--hive本地管理元数据-->
<name>hive.metastore.local</name>
<value>true</value>
</property>
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<property>
<!--指定文件数据库url，用于存放元数据。格式jdbc:mysql://地址/库?库不存在自动创建-->
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node3:3306/hive_remote?createDatabaseIfNotExist=true</value>
</property>
<property>
<!--驱动-->
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<!--用户名-->
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<!--密码-->
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
</configuration>
添加mysql启动jar包，mysql-connector-java-5.1.32-bin.jar到hive的lib中
启动Hadoop
初始化MySQL数据库
bin/schematool -dbType mysql -initSchema
进入hive
bin/hive
可以在MySQL中看到TBLS表就是来存放hive表的元数据信息，COLUMNS_V2是列的元数据
3.多用户模式
1)remote一体
配置MetaStoreServer和hive客户端放在一起
cp conf/hive-env.sh.template conf/hive-env.sh
添加HADOOP_HOME=/opt/hadoop-2.7.5
cp conf/hive-default.xml.template conf/hive-site.xml
<configuration>
<property>
<!--HDFS真实存放数据的目录，空目录或不存在-->
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<!--存放元数据地址,hive_remote库为空或者不存在-->
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node3:3306/hive_remote?createDatabaseIfNotExist=true</value>
</property>
<property>
<!--驱动包-->
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<!--MySQL用户名-->
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<!--MySQL密码-->
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<property>
<!--不启动本地hive管理元数据功能-->
<name>hive.metastore.local</name>
<value>false</value>
</property>
<property>
<!--MetaStoreServer的地址端口-->
<name>hive.metastore.uris</name>
<value>thrift://node1:9083</value>
</property>
</configuration>
添加mysql启动jar包，mysql-connector-java-5.1.32-bin.jar到hive的lib中
启动Hadoop
初始化MySQL数据库
bin/schematool -dbType mysql -initSchema
启动MetaStoreServer
bin/hive --service metastore
启动hive
bin/hive
2)remote分开
1、配置MetaStoreServer
cp conf/hive-env.sh.template conf/hive-env.sh
添加HADOOP_HOME=/opt/hadoop-2.7.5
cp conf/hive-default.xml.template conf/hive-site.xml
<configuration>
<property>
<!--HDFS真实存放数据的目录，空目录或不存在-->
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<!--存放元数据地址，hive_remote数据库一定保证空或者不存在-->
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node3:3306/hive_remote?createDatabaseIfNotExist=true</value>
</property>
<property>
<!--驱动包-->
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<!--mysql用户名-->
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<!--mysql密码-->
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
</configuration>
MetaStoreServer节点添加mysql启动jar包，mysql-connector-java-5.1.32-bin.jar到hive的lib中
启动Hadoop
初始化MySQL数据库
bin/schematool -dbType mysql -initSchema
启动MetaStoreServer
bin/hive --service metastore
2、hive客户端配置
cp conf/hive-env.sh.template conf/hive-env.sh
添加HADOOP_HOME=/opt/hadoop-2.7.5
cp conf/hive-default.xml.template conf/hive-site.xml
<configuration>
<property>
<!--HDFS真实存放数据的目录，空目录或不存在，和MetaStoreServer保持一致-->
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<!--不启动本地hive管理元数据功能-->
<name>hive.metastore.local</name>
<value>false</value>
</property>
<property>
<!--MetaStoreServer的地址端口-->
<name>hive.metastore.uris</name>
<value>thrift://node2:9083</value>
</property>
</configuration>
启动hive
bin/hive
Beeline
hive2中提出，用来管理hive属性的设定，和之前使用方式一样的
在Hadoop的hdfs-site.xml和core-site.xml文件中分布添加
hdfs-site.xml
<property>
<name>dfs.webhdfs.enabled</name>
<value>true</value>
</property>
core-site.xml
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
按照上述过程安装后在MetaStoreServer节点启动hiveServer2，如果有metastore则需要启动
bin/hiveserver2
hive客户端
bin/beeline（这时只是打开了beeline窗口并没有连接到MetaStoreServer节点）
连接上MetaStoreServer节点的hiveserver2
beeline>!connect jdbc:hive2://MetaStoreServer_IP:10000
然后填写用户，可以填root，因为之前Hadoop的数据是在root用户下创建的。密码为空可直接回车
默认不启动验证，配置文件中hive.server2.authentication默认为NONE
或者
bin/beeline -u jdbc:hive2://MetaStoreServer_IP:10000/库 -n user -w password_file（没有密码去掉-w password_file）
beeline提供jdbc方式访问
例如：（导入commons-lang-2.62,guava-14.0.12,hive-common-2.3.42,hive-jdbc-2.3.42,hive-serde-2.3.42,hive-service-2.3.42,hive-service-rpc-2.3.42,httpclient-4.42,httpcore-4.42,libthrift-0.9.32,slf4j-api-1.7.102的jar包）
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
Connection con = DriverManager.getConnection("jdbc:hive2://node1:10000/default", "root", "");
Statement stmt = con.createStatement();
String sql = "select * from psn";
ResultSet res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1));
}
}
HQL（官网->Language Manual）
数据类型
复合类型array_type（定义方式array<类型>），map_type（定义方式map<key类型,value类型>），struct_type（结构体）
基本类型tinyint，smallint，int，bigint，boolean，float，double，string
hive中表分为内部表和外部表
外部表：数据存放在指定的HDFS路径中，即使执行了删除表命令，但是hdfs上数据不会删除，只是删除了元数据
内部表：数据存放在hive配置文件所指定的位置中，执行删除表命令，元数据和hdfs数据都会删除
DDL数据定义语言（对数据库表创建、删除、修改等操作）
创建数据库
create (database|schema) [if not exists] database_name
[comment database_comment]
[location hdfs_path]
[with dbproperties (property_name=property_value, ...)];
删除数据库
drop (database|schema) [if exists] database_name [restrict|cascade];（当前不可以在需要删的库内）
使用数据库
use database_name;
创建表（不指定库会在default库中创建）
create [external] table [if not exists] [db_name.]table_name
[(col_name data_type [comment col_comment],...)]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment],...)]
[[row format row_format]
row format delimited [fields terminated by char [escaped by char]]
[collection items terminated by char]
[map keys terminated by char]
[lines terminated by char]
[null defined as char]
[stored as file_format]]
[location hdfs_path]
含义
external指定内部表还是外部表，不写默认内部表。如果是外部表需要指定location关键字
[db_name.]指定库
col_name字段名
data_type数据类型
col_comment对字段的描述
table_comment对表的描述
partitioned by（注意partitionby的顺序）按照哪些列（这些列不能在前面出现过的）做分区处理，可以对不同场景的数据进行分开管理
col_name列名
data_type数据类型
comment可选的描述
row_format对行做格式化或者规定序列化和反序列化规则，不指定就用hive默认的规则来做，hive操作的是hdfs上数据所以需要row_format。
比如：hdfs上数据为1,xiaoming,man,book-shejian-shoot,beijing:wudaokou-huoxing:weizhi，一条结构化数据要映射到hive中需要为它标识以什么做分割为哪一列哪一个数据。
fields terminated by char指定字段和字段之间的分隔符
escaped by char指定分割的类型，默认char类型
collection items terminated by char指定复合类型array_type里面数据的切分方式
map keys terminated by char指定复合类型map_type里面数据的切分方式
lines terminated by char指定每行数据的分隔符，默认/n
null defined as char指定什么字符为空值
file_format指定数据存储在hdfs中的文件类型，默认文本类型（也可以做压缩等）
location指定表为外部表时加上设置存放表数据的hdfs路径
例如一：通过delimited关键字指定分隔符的方式（hive中已经写好的序列化和反序列化的规则）创建一张表
create table psn (id int,name string,sex string,likes array<string>,address map<string,string>)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
例如二：通过serde关键字指定序列化和反序列化的方式来创建一张表，这样可以自定义序列化和反序列化的规则
create table psn2
row format serde "org.apache.hadoop.hive.serde2.columnar.columnarserde"
stored as rcfile
as
select * from psn;
参照psn表的元数据来创建psn2表（就是两张表的schema一样），并且将as后面的语句所查询的数据放到psn2中
例如三：通过like关键字来创建表
create table table_name2
like table_name
根据table_name表的元数据来创建table_name2表，两个表的schema相同
删除表
drop table table_name;
截断表，将表中数据清空
truncate table table_name
修改表，
语法和关系型数据库一样
添加分区
alter table table_name add partition (dt='2008-08-08', country='us') 根据分区字段来添加具体的值而已，不会添加分区字段
删除分区（分区中的数据也会跟着删除掉，而且该分区下的所有分区也会删除）
alter table table_name drop partition (dt='2008-08-08')
视图和关系型数据库中一样，一般数据库不能对视图插入数据
DML数据操作语言
插入数据（load方式和insert）
load：load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2 ...)]
local如果需要上传的文件在hdfs上不用加，如果在本地需要加上（它会先将本地文件上传到HDFS然后再加载到指定分区目录中）
filepath文件路径
overwrite是否覆盖
partition如果创建表的时候使用了分区，那么导入的时候需要加上，而且分区个数要对应
partcol1=val1分区字段名和分区字段所指定的值
insert方式插入的速度很慢一般不会使用，但是一般如果需要将结果存到临时表中会用insert
比如：from table_name 别名
insert into table table_name1
select 别名.col1,别名.col2,别名.col3
或：from table_name 别名
insert into table table_name1
select 别名.col1,别名.col2,别名.col3
insert table table_name2
select 别名.col1,别名.col2,别名.col3
如果要覆盖之前的数据，将into换成overwrite
查询
select * from table_name where partcol1=val1（根据分区来查找）
修改数据
update tablename set column = value [, column = value ...] [where expression]
删除数据
delete from tablename [where expression]
正则表达式（官网->Getting Started Guide->Apache Weblog Data）
比如
create table apachelog (
host string,
identity string,
user string,
time string,
request string,
status string,
size string,
referer string,
agent string)
row format serde 'org.apache.hadoop.hive.serde2.regexserde' 接的是正则表达式的标准类
with serdeproperties (
"input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
)
stored as textfile;
hive内置函数
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF或链接：https://pan.baidu.com/s/1o26fIzJtTF-kqaDiufvqSQ 提取码：b5dl
自定义函数
UDF一进一出，进入一条数据返回一条数据
UDAF多进一出
UDTF一进多出，复杂类型会用到UDTF
例如：自定义的UDF实现转小写（类继承UDF类重写evaluate方法，类型必须是Hadoop中类型并且结合业务来定）
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public final class Lower extends UDF {
public Text evaluate(final Text s) {
if (s == null) { return null; }
return new Text(s.toString().toLowerCase());
}
}
打jar包，将jar包放到MetaStoreServer节点
然后在hive客户端上执行hive>add jar jar包路径 上传jar包。
创建临时函数，在hive客户端执行hive>create temporary function 自定义函数名称（随意起） as 'hive.udf.add（程序的类入口）';
测试
select id,name,tm(sex) from psn;
删除自定义函数hive> drop temporary function 自定义函数名称;
hive优化，链接：https://pan.baidu.com/s/10jVPjYTTecGFPWyJv57Qhg 提取码：9aih
