数据库和数据仓库的区别

数据仓库（OLAP）	数据库 （OLTP）
在线分析处理	在线事务处理
延迟性高	实时性好

hive的作用

hive是在hadoop上处理结构化数据的数据仓库，hive不是关系型数据库，不适合在线数据分析处理，速度很慢，是延迟性很高的操作，不适用于实时查询和行级更新，hive存储数据结构在关系型数据库中，处理数据在hdfs。hive查询首先去关系型数据库中查询数据结构，然后将数据结构交给执行引擎去执行，得到数据。

hive安装配置

hive安装

1. 环境：安装hive首先必须安装jdk和hadoop

   	#1. 上传并解压Hive包，参数C：修改解压到的目录tar -xzvf apache-hive-2.1.0-bin.tar.gz -C /soft/#2. 配置环境变量export HIVE_HOME=/soft/apache-hive-2.1.0-binexport PATH=$PATH:$HIVE_HOME/bin#3. 验证hive安装是否成功hive --version#4. 复制oracle驱动程序jar包到apache-hive-2.1.0-bin/lib目录下，这里的驱动jar包最好使用ojbdc8#5. 到目录apache-hive-2.1.0-bin/conf下复制hive-default.xml.template模板为hive-site.xml并配置该配置文件
   

2. hive-site.xml需要修改的配置：

   	  <!--配置关系型数据库驱动--><property><name>javax.jdo.option.ConnectionDriverName</name><value>oracle.jdbc.driver.OracleDriver</value></property><!--配置关系型数据库连接地址--><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:oracle:thin:@192.168.0.7:1521/orcl</value></property><!--配置关系型数据库用户名--><property><name>javax.jdo.option.ConnectionUserName</name><value>HIVEGX</value></property><!--配置关系型数据库密码--><property><name>javax.jdo.option.ConnectionPassword</name><value>cqrjxk39</value></property><!--配置hive作业的本地原始文件路径--><property><name>hive.exec.local.scratchdir</name><value>/home/hive</value></property><!--配置hive下载的资源目录--><property><name>hive.downloaded.resources.dir</name><value>/home/hive/download</value></property><!--配置hive日志的目录--><property><name>hive.querylog.location</name><value>/home/hive/querylog</value></property><!--配置hive的server2的日志目录--><property><name>hive.server2.logging.operation.log.location</name><value>/home/hive/server2/log</value></property><!--配置权限认证--><property><name>hive.server2.enable.doAs</name><value>false</value></property>
   

3. 初始化表中的元数据到oracle中

   	schematool -dbType oracle -initSchema
   

   初始化后数据库中会增加以下表格
   

4. 打开Hive命令窗口

   	#首先启动hadoop服务start-all.sh#设置激活态名称节点hdfs haadmin -transitionToActive nn1#启动hive命令窗口hive
   

创建成功后hive的数据库的数据都是默认存放在/user/hive/warehouse这个路径下面

hive的sql命令

hive的表如果没有设置支持事务是不支持delete和update操作的

	--查看数据库，hive有一个默认库defaultshow databases;--创建数据库mydb2，创建成功之后，在hadoop文件系统的/user/hive/warehouse目录下可以看到，在oracle元数据库中通过查询dbs表也可以看到create database mydb2;--使用mydb2数据库use mydb2;--指定在mydb2数据库中创建表格t，（if not exists表示不存在就创建表，存在就不执行后面的创表语句）创建成功后，在hadoop文件系统对应的数据库的目录下可以看到，在oracle元数据库中通过查询tbls表也可以看到，comment后面是注释，写在字段后面是给字段加注释，写在表后面是给表加注释,ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE表示字段之间是以逗号分隔，存储类型为text文本create table if not exists mydb2.t(id int,name string,age int) COMMENT 'xx' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE ; --复制t表（携带数据）create table t2 as select * from t;--复制t表（不携带数据）create table t2 like t;--查看t表结构desc t;--查看t表的完整信息desc formatted t;--删除表tdrop table t;--向t表中插入数据，打开yarn调度运算管理平台（8088端口的），可以看到作业运行的进度，插入操作执行会比较慢，插入成功后可以在hadoop文件系统对应的数据库的对应的表目录下面可以看到insert into t (id,name,age) values (1,'zhangsan',23);--查询t表中数据select * from t;--退出命令行exit;

desc formatted t显示的信息：

hive中的表

hive中的表分为以下两类：

• 托管表（managed table）（默认）
  删除表时数据也删除
• 外部表（external table）
  删除表时数据不删除

	--创建托管表create managed table t(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE ;

hive的数据类型

类型	类型名称
tinyint	字节形
smallint	短整形
int	整形
bigint	长整形
string	字符串类型
boolean	布尔类型
float	单精度
double	双精度
timestamp	时间戳

远程连接操作hive数据仓库

1. 启动hiveserver2服务，监听端口默认为10000
   远程连接连接的是hive的hiveserver2服务，所以想要可以远程连接hive，就需要启动hiveserver2服务

   	#启动hiveserver2服务，&是指将服务放到后台运行，启动成功后jps查看进程会有RunJar的进程hive --service hiveserver2 &
   

2. 可以通过hive里面自带的一个脚本beeline连接hiveserver2，测试一下hiveserver2是否启动成功

   	#进入beeline命令模式beeline#连接hive的mydb2数据仓库!connect jdbc:hive2://localhost:10000/mydb2#退出beeline命令模式，不要带分号!quit
   

3. 导入hive数据仓库的驱动包

   	<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc --><dependency><groupId>org.apache.hive</groupId><artifactId>hive-jdbc</artifactId><version>2.1.0</version></dependency>
   

   注意： hive驱动的jar包版本必须跟服务器上的Hive版本保持一致，否则很容易报错

4. jdbc连接hive

   	public static void main(String[] args) {try {Class.forName("org.apache.hive.jdbc.HiveDriver");Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.200.100:10000/mydb2");String sql = "select * from T";PreparedStatement ps = conn.prepareStatement(sql);ResultSet rs = ps.executeQuery();while (rs.next()){Object id = rs.getObject(1);Object name = rs.getObject(2);Object age = rs.getObject(3);System.out.println("id:" + id + ",name:" + name + ",age:" + age );}} catch (Exception e) {e.printStackTrace();}}
   

加载文件到hive数据仓库表里

1. 文件必须是Hive表的存储类型，t表存储类型为txt文本
2. 文件数据格式字段之间必须以表指定的符号分隔，t表指定的分隔符是逗号
3. 命令

   	#方法一：加载本地文件students.txt到表t中，加载成功后可以在表对应的目录下看到，overwrite是覆盖原来的数据，谨慎使用load data local inpath '/home/students.txt' (overwrite) into table t;#方法二：加载hadoop文件系统上的文件到t表中，加载成功后原路径下的文件消失，在表对应的目录下可以看到load data inpath '/students.txt' (overwrite) into table t;#方法三：将students.txt文件上传到对应表的目录下即可hdfs dfs -put  /home/students.txt /user/hive/warehouse/mydb2.db/t/
   

hive中需要转mr的操作

1. insert插入语句
2. 复制表（带数据）
3. 排序order by
4. 连接查询

分区表

分区表是hive的优化手段之一，是从目录的层面控制搜素数据的范围。

	--1. 创建分区表，根据年和月两个字段分区create table t3(id int,name string,age int) partitioned by (year int,month int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE ;--2. 添加分区year=2020,month=08和分区year=2020,month=09alter table t3 add partition (year=2020,month=08) partition (year=2020,month=09);--3. 查看t3表的分区show partitions t3;--4. 删除分区year=2020,month=08alter table t3 drop if exists partition (year=2020,month=08);--5. 加载数据到分区(year=2020,month=09)表中load data local inpath '/home/students.txt' (overwrite) into table t3 partition(year=2020,month=09);--6. 查询数据，分区字段也会随着表中的字段一起查询出来select * from t3 where year = 2020 and month = 9;

动态分区：

	--使用动态分区首先要指定动态分区模式为非严格模式SET hive.exec.dynamic.partition.mode = nonstrict;--使用动态分区插入数据必须指定其中一个分区字段的值insert table t3 partition (year = '2020' ,month) select * from t5 where year = '2020'

桶表

桶表也是hive的优化手段之一，桶表是从存储文件的层面控制搜素数据的范围。

	--创建桶表，根据字段id分桶，创建3个桶create table t4(id int,name string,age int) clustered by (id) into 3 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;--向桶表中插入数据，会根据id的hash进行桶分配插入，插入后桶表的目录下会产生三个文件，每个文件对应每个桶insert into t4 select id,name,age from t3;

注意： 加载（load）数据不会进行分桶操作

连接查询

	--内连接（客户订单查询）select a.*,b.* from customers a,orders b where a.id = b.cid;--左外连接select a.*,b.* from customers a left join orders b on a.id = b.cid;--右外连接select a.*,b.* from customers a right join orders b on a.id = b.cid;--全外连接select a.*,b.* from customers a full join orders b on a.id = b.cid;--mapjoin暗示连接，可以不使用reduce连接，直接使用map连接来做连接查询select /*+ mapjoin(customers) */ a.*,b.* from customers a left outer join orders b on a.id = b.cid ;

导入导出

	--导出t表结构和数据export table t to '/usr/local/hadoop/t';

导出的表的结构和数据：

排序

全排序

全排序是在reduce端排序

	select * from t order by id desc;	

部分排序

部分排序是在map端排序

	select * from t sort by id desc;

分区

	--既然对数据进行了分区，就不能使用全排序，只能使用部分排序select id,orderno,price,cid from orders distribute by cid sort by id desc;

hive的函数

在hive命令窗口下，输入tab键，然后输入y，就可以看到hive支持的所有函数

	--字符串拼接select concat('Tom',100);   --Tom100--查看日期select current_date();--切割，按空格切割，切割后的类型是数组select split(line,' ') from woc;--将数组元素都聚合到一个属性下面select explode(split(line,' ')) from woc;

demo 单词统计：

	--先将每一行按照空格切割获得单词数组，然后将数组里的单词都聚合到一个属性下面，再对这个属性进行group分组统计单词个数，然后把结果塞到resultWord表中create table resultWord as select word,count(*) sum from (select explode(split(line,' ')) word from woc) t group by word order by sum desc;

事务

hive的事务处理在0.13版本之后才支持
hive的事务的特点：

1. 所有的事务都是自动提交的

2. 支持orc格式文件

3. 必须是桶表

4. 配置hive的参数使当前会话支持事务

   	--支持并发SET hive.support.concurrency=true;--强制桶处理		SET hive.enforce.bucketing=true;	--动态分区的表操作模式：非严格模式	SET hive.exec.dynamic.partition.mode=nonstrict;		--事务管理器SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;--对比初始化器SET hive.compactor.initiator.on=true;--线程数SET hive.compactor.worker.threads=1;
   

   如果想要永久支持，则去修改hive-site.xml配置文件相应的属性值即可（没有就添加）

5. 事务的相关操作

   	--显示事务show transactions;--创建桶表，存储格式为orc，设置表格属性transactional为true，使其支持事务create table t6(id int,name string,age int) clustered by (id) into 3 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS ORC tblProperties ('transactional'='true');
   

6. 事务设置完后查询表的详细信息，可以看到该表是否支持事务

   	desc formatted t6;
   

7. 事务支持后，就可以对表进行update或者delete操作

视图

视图是虚表，只存在于逻辑上，是对复杂查询语句的一个封装，便于以后查询方便快捷

	--创建视图v1，创建时连接查询两个表有相同字段名称的需要的给其中一个字段起别名，避免字段有歧义create view v1 as select a.*,b.orderNo,b.price from customers a left join orders b on a.id = b.cid;--查询视图select * fron v1;--查看视图详细信息desc formatted v1;

调优

1. 使用explain查看查询计划

   explain select count(*) from customers;
   --extended会产生更加详细的计划
   explain extended select count(*) from customers;
   

   运行结果：

   #阶段分析
   STAGE DEPENDENCIES:#1阶段为根阶段Stage-1 is a root stage	#0阶段依赖于根阶段Stage-0 depends on stages: Stage-1STAGE PLANS:#1阶段Stage: Stage-1Map Reduce#map操作Map Operator Tree:#表扫描TableScanalias: customersStatistics: Num rows: 1 Data size: 49 Basic stats: COMPLETE Column stats: COMPLETESelect OperatorStatistics: Num rows: 1 Data size: 49 Basic stats: COMPLETE Column stats: COMPLETEGroup By Operatoraggregations: count()mode: hashoutputColumnNames: _col0Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETEReduce Output Operatorsort order: Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETEvalue expressions: _col0 (type: bigint)#reduce操作Reduce Operator Tree:#分组操作Group By Operatoraggregations: count(VALUE._col0)mode: mergepartialoutputColumnNames: _col0Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETEFile Output Operatorcompressed: falseStatistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETEtable:input format: org.apache.hadoop.mapred.SequenceFileInputFormatoutput format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormatserde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe#0阶段Stage: Stage-0Fetch Operatorlimit: -1Processor Tree:ListSink
   

2. 限制调优

   	--limit默认还是会查询整个数据，只是会返回部分结果，为了避免它查询所有数据，可以配置以下属性避免limit查询全表扫描set hive.limit.optimize.enable=true;--一旦以上属性设置为true，它会先去采样，然后从样品中选择相应的结果返回，采样的行为与以下两个属性有关--设置采样的记录数set hive.limit.row.max.size=100000--设置采样的文件数量set hive.limit.optimize.limit.file=10
   

3. 设置本地模式

   	set mapred.job.tracker=local;--设置自动本地模式set hive.exec.mode.local.auto=true;
   

4. 并行执行

   	--不存在依赖关系可以同时执行set hive.exec.parallel=true;
   

5. 严格模式

   • 严格模式下分区表查询必须指定分区，否则不让你查询
   • 严格模式下order by排序时，必须使用limit限制，否则也不让你查询
   • 严格模式下不允许笛卡尔积查询

   	--设置严格模式set hive.mapred.mode=strict;
   

6. 设置MR的数量

   	--设置reduce处理的字节数set hive.exec.reducers.bytes.per.reducer=750000000;
   

7. JVM重用

   	--设置-1就没有限制，适用于大量小文件set mapreduce.job.jvm.numtasks=1
   

UDF（用户自定义函数）

	--查看所有函数show functions;--查看split函数的帮助desc function split;--查看split函数详细帮助desc function extended split;

1. 环境搭建

   	<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec --><dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId><version>2.1.0</version></dependency>
   

2. 自定义函数逻辑

   import org.apache.hadoop.hive.ql.exec.Description;
   import org.apache.hadoop.hive.ql.exec.UDF;//自定义函数的描述，创建函数后用desc命令可以查看，三个属性分别对应自定义函数名称，描述值，详细描述
   @Description(name = "myadd",value = "myadd(int a , int b) ==> return a + b ",extended = "Example:\n"+ " myadd(1,1) ==> 2 \n"+ " myadd(1,2,3) ==> 6;")
   public class MyAdd extends UDF {//函数名称必须叫evaluate，不能随意更改，可以重载，返回值和参数类型可以是基本类型或继承了org.apache.hadoop.io.Writable的可序列化类型，并且hive里面的数组类型对应java里面的java.util.List类型public int evaluate(int a,int b){return a + b;}public int evaluate(int a,int b,int c) {return a + b + c;}
   }
   

3. 将自定义函数打成jar包上传到服务器并执行以下语句将jar包添加到Hive的类路径下

   	--查看jar包结构jar -tf MyAdd-1.0-SNAPSHOT.jar--将jar包添加到hive的类路径下add jar /download/MyAdd-1.0-SNAPSHOT.jar
   

4. 创建临时自定义函数（当前会话有效）

   	create temporary function myadd as 'cn.com.trueway.hive.MyAdd';
   

5. 测试

   	select myadd(1,2);
   

数据倾斜

	--如果连接中存在数据倾斜，则将其设置为true。默认的是falseset hive.optimize.skewjoin=true;--100000是默认值。如果key的数目大于这个数目，新的key将发送给其他未使用的reducerset hive.skewjoin.key=100000;--分组时发生数据倾斜可以进行下面的设置set hive.groupby.skewindata=true;