一、Hive簡介
1、hive概述
Apache Hive™資料倉庫軟體有助于使用SQL讀取，編寫和管理駐留在分布式存儲中的大型資料集。
可以将結構投影到已存儲的資料中。提供了指令行工具和JDBC驅動程式以将使用者連接配接到Hive。
資料計算：mapreduce分布式計算>難度大
hive>SQL語句 mysql 簡化開發 減少學習成本
2、優缺點
優點：
（1）操作接口采用了sql，簡化開發，減少學習成本
（2）避免手寫mapreduce程式
（3）hive執行延遲較高，适用場景大多用在對實時性要求不強的情景
（4）優點在于處理大資料
（5）支援自定義函數
缺點：
（1）hive的sql表達能力有限（HSQl）
（2）hive效率低（粒度比較粗，調優較難）
3、hive架構
提供了一系列接口：hive shell、jdbc/odbc、webui Hive架構
二、Hive安裝
1、Hive安裝
1）下載下傳安裝包
http://hive.apache.org/downloads.html
2）上傳安裝包
alt+p
3) 解壓
tar -zxvf apache-hive-1.2.2-bin.tar.gz -C hd/
4) 解壓後的包重命名
mv apache-hive-1.2.2-bin/ hive
5) 修改配置檔案
mv hive-env.sh.template hive-enc.sh
vi hive-env.sh
HADOOP_HOME=/root/hd/hadoop-2.8.4
export HIVE_CONF_DIR=/root/hd/hive/conf
6) 啟動hive前啟動hadoop叢集
start-dfs.sh
start-yarn.sh
# start-all.sh # 不建議使用
7) 在hdfs上建立檔案夾
hdfs dfs -mkdir /tmp
hdfs dfs -mkdir -p /user/hive/warehouse/
8) 啟動hive
bin/hive
2、hive測試
1）檢視資料庫
show databases;
2) 使用資料庫
use default;
3) 檢視表
show tables;
4) 建立表
create table student(id int,name string);
5）插入資料
insert into student values(1,"tom");
6) 查詢
select * from student;
7) 删除表
drop table student;
8) 退出終端
quit;
3、SecureCRT使用hive指令行不能回退
選擇工具欄中的【選項(O)】-【會話選項】-【終端】-【仿真】-【終端】，
下拉選項選擇【Linux】，【确定】即可。
這樣修改完在hive指令行中輸入指令就可以自由增删改了。
4、操作資料
1）準備資料
vi student.txt
1 tom
2 zhangsan
3 lisi
4 zhangsanfeng
5 xiexiaofeng
2)建立hive表
create table student(id int,name string) row format delimited fields terminated by "\t";
3)加載資料
load data local inpath '/root/student.txt' into table student;
三、Hive資料類型
1、配置hive中繼資料到mysql
1）驅動拷貝
拷貝mysql-connector-java-5.1.39-bin.jar到/root/hd/hive/lib/下
2）配置Metastore到MySql
在/root/hd/hive/conf目錄下建立一個hive-site.xml
根據官方文檔配置參數，拷貝資料到hive-site.xml檔案中（hive/conf/下建立檔案）
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl" target="_blank" rel="external nofollow" ?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hd09-1:3306/metastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
<description>password to use against metastore database</description>
</property>
</configuration>
2、hive資料類型
Java資料類型 Hive資料類型 長度
byte TINYINT 1byte
short SMALINT 2byte
int INT 4byte
long BIGINT 8byte
float FLOAT 單精度浮點數
double DOUBLE 雙精度浮點數
string STRING 字元
TIMESTAMP 時間類型
BINARY 位元組數組
3.DDL資料定義
1）建立資料庫
檢視資料庫
show databases;
建立資料庫
create database hive_db;
建立資料庫标準寫法
create database if not exist db_hive;
建立資料庫指定所在hdfs路徑
create database hive_db1 location '/hive_db';
2）修改資料庫
檢視資料庫結構
desc database hive-db;
添加描述資訊
alter database hive_db set dbproperties('datais'='tom');
檢視拓展屬性
desc database extended hive_db;
3）查詢資料庫
顯示資料庫
show databases;
篩選查詢的資料庫
show database like 'db*';
4）删除資料庫
删除資料庫
drop dabase hive_db;
删除資料庫标準寫法
drop database if exists hive_db;
5）建立表
建立表
> create table db_h(id int,name string)
> row format
> delimited fields
> terminated by "\t";
6）管理表（内部表）
不擅長做資料共享
删除hive中管理表，資料删除。
加載資料
load data local inpath '/root/user.txt' into table emp;
查詢并儲存到一張新的表
create table if not exists emp2 as select * from emp where name = 'tom';
查詢表結構
desc formatted emp;
Table Type: MANAGED_TABLE
7）外部表
hive不認為這張表擁有這份資料，删除該表，資料不删除。
擅長做資料共享
建立外部表
> create external table if not exists emptable(empno int,ename string,job
string,mgr int,birthdate string,sal double,comm double,deptno int)
> row format
> delimited fields
> terminated by '\t';
導入資料
load data local inpath '/root/emp.txt' into table emptable;
檢視表結構
desc formatted emptable;
Table Type: EXTERNAL_TABLE
删除表
drop table emptable;
提示：再次建立相同的表 字段相同 将自動關聯資料！ 附件1：資料員工表 emp.txt
2369 SMITH CLERK 2902 1980-12-17 800.00 20
2499 ALLEN SALESMAN 2698 1981-2-20 1600.00 300.00 30
2521 WARD SALESMAN 2698 1981-2-22 1250.00 500.00 30
2566 JONES MANAGER 2839 1981-4-2 2975.00 20
2654 MARTIN SALESMAN 2698 1981-9-28 1250.00 1400.00 30
2698 BLAKE MANAGER 2839 1981-5-1 2850.00 30
2782 CLARK MANAGER 2839 1981-6-9 2450.00 10
2788 SCOTT ANALYST 2566 1987-4-19 3000.00 20
2834 KING PRESIDENT 1981-11-17 5000.00 10
2844 TURNER SALESMAN 2698 1981-9-8 1500.00 0.00 30
2976 ADAMS CLERK 2788 1987-5-23 1100.00 20
2900 JAMES CLERK 2698 1981-12-3 950.00 30
2902 FORD ANALYST 2566 1981-12-3 3000.00 20
2924 MILLER CLERK 2782 1982-1-23 1300.00 10 四、DML資料操作
一、分區表
1、建立分區表
hive> create table dept_partitions(depno int,dept string,loc string)
partitioned by(day string)
row format delimited fields
terminated by '\t';
2、加載資料
load data local inpath '/root/dept.txt' into table dept_partitions;
注意：不能直接導入需要指定分區
load data local inpath '/root/dept.txt' into table dept_partitions partition(day='1112');
3、添加分區
alter table dept_partitions add partition(day='1113');
4、單分區查詢
select * from dept_partitions where day='1112';
5、全查詢
select * from dept_partitions;
6、查詢表結構
desc formatted dept_partitions;
7、删除單個分區
alter table dept_partitions drop partition(day='1112');
二、修改表
1、修改表名
alter table emptable rename to new_table_name;
2、添加列
alter table dept_partitions add columns(desc string);
3、更新列
alter table dept_partitions change column desc desccc int;
4、替換
alter table dept_partitions replace columns(desccc int);
三、DML資料操作
1、向表中加載資料
load data local inpath '/root/student.txt' into table student;
2、加載hdfs中資料
load data inpath '/student.txt' into table student;
提示：相當于剪切
3、覆寫原有的資料
load data local inpath '/root/student.txt' overwrite into table student;
4、建立分區表
create table student_partitions(id int,name string) partitioned by (month string) row format
delimited fields terminated by '\t';
5、向分區表插入資料
insert into table student_partitions partition(month='201811') values(1,'tom');
6、按照條件查詢結果存儲到新表
create table if not exists student_ppp as select * from student_partitions where name='tom';
7、建立表時加載資料
create table db_h(id int,name string)
row format
delimited fields
terminated by "\t"
location '/root/student.txt';
8、查詢結果導出到本地
hive> insert overwrite local directory '/root/datas/student.txt' select * from student where name='tom';
bin/hive -e 'select * from student' > /root/student.txt
hive> dfs -get /usr/hive/warehouse/00000 /root; 附件2：資料部門表 dept.txt
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900 五、查詢與函數
一、查詢
1、配置查詢頭資訊
在hive-site.xml
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
2、基本查詢
(1)全表查詢select * from empt;
(2)查詢指定列select empt.empno,empt.empname from empt;
(3)列别名select ename name,empno from empt;
3、算數運算符
算數運算符 描述 Col3
+ 相加 field3
相減 field3
* 相乘 field3
/ 相除 field3
% 取餘 field3
& 按位取與 field3
按位取或
^ 異或 field3
~ 按位取反 field3
二、函數
(1)求行數count select count(*) from empt;
(2)求最大值max select max(empt.sal) sal_max from empt;
(3)求最小值 select min(empt.sal) sal_min from empt;
(4)求總和 select sum(empt.sal) sal_sum from empt;
(5)求平均值 select avg(empt.sal) sal_avg from empt;
(6)前兩條 select * from empt limit2;
三、where語句
(1)工資大于1700的員工資訊 select * from empt where empt.sal > 1700;
(2)工資小于1800的員工資訊 select * from empt where empt.sal < 1800;
(3)查詢工資在1500到1800區間的員工資訊 select * from empt where empt.sal between 1500 and 1800;
(4)查詢有獎金的員工資訊 select * from empt where empt.comm is not null;
(5)查詢無獎金的員工資訊 select * from empt where empt.comm is null;
(6)查詢工資是1700和1900的員工資訊 select * from empt where empt.sal in(1700,1900);
四、Like使用like運算選擇類似的值選擇條件可以包含字母和數字
(1)查找員工薪水第二位為6的員工資訊 select * from empt where empt.sal like'_6%'; _代表一個字元 %代表0個或多個字元
(2)查找員工薪水中包含7的員工資訊 select * from empt where empt.sal like'%7%';
(3)rlike--select * from empt where empt.sal rlike'[7]';
五、分組
（1）Group By語句計算empt表每個部門的平均工資 select avg(empt.sal) avg_sal,deptno from empt group by deptno;
（2）計算empt每個部門中最高的薪水 select max(empt.sal) max_sal,deptno from empt group by deptno;
（3）求部門平均薪水大于1700的部門 select deptno,avg(sal) avg_sal from empt group by deptno having avg_sal>1700;注意：having隻用于group by分組統計語句
六、其他語句
（1）查詢薪水大于1700并且部門是40 員工資訊
select * from empt where deptno=40 and empt.sal>1700;
（2）查詢薪水除了10部門和40部門以外的員工資訊
select * from empt where deptno not in(10,40);
（3）查詢薪水大于1700或者部門是40的員工資訊
select * from empt where sal>1700 or deptno=40; 六、join和分桶
一、Join操作
(1)等值join
根據員工表和部門表中部門編号相等，查詢員工編号、員工名、部門名稱
select e.empno,e.ename,d.dept from empt e join dept d on e.deptno = d.deptno;
(2)左外連接配接 left join
null
select e.empno,e.ename,d.dept from empt e left join dept d on e.deptno = d.deptno;
(3)右外連接配接 right join
select e.empno,e.ename,d.dept from dept d right join empt e on e.deptno = d.deptno;
(4)多表連接配接查詢
查詢員工名字、部門名稱、員工位址
select e.ename,d.dept,l.loc_name from empt e join dept d on e.deptno = d.deptno join location l on d.loc = l.loc_no;
(5)笛卡爾積
select ename,dept from empt,dept;
為了避免笛卡爾積采用設定為嚴格模式
檢視：set hive.mapred.mode;
設定：set hive.mapred.mode = strict;
二、排序
(1)全局排序 order by
查詢員工資訊按照工資升序排列
select * from empt order by sal asc;預設
select * from empt order by sal desc;降序
(2)查詢員工号與員工薪水按照員工二倍工資排序
select empt.empno,empt.sal*2 two2sal from empt order by two2sal;
(3)分區排序
select * from empt distribute by deptno sort by empno desc;
三、分桶
分區表分的是資料的存儲路徑
分桶針對資料檔案
(1)建立分桶表
create table emp_buck(id int,name string)
clustered by(id) into 4 buckets
row format delimited
fields terminated by '\t';
(2)設定mapreduce數量
set mapreduce.job.reduces;
(3)設定分桶屬性：
set hive.enforce.bucketing = true;
(4)導入資料
insert into table emp_buck select * from emp_b;
注意：分區分的是檔案夾 分桶是分的檔案
(5)應用
抽樣測試 附件3：資料位址表 location.txt
1700 Beijing
1800 London
1900 Tokyo 附件4：資料分桶資料表 emp_b.txt
2001 hhh1
2002 hhh2
2003 hhh3
2004 hhh4
2005 hhh5
2006 hhh6
2007 hhh7
2008 hhh8
2009 hhh9
2010 hhh10
2011 hhh11
2012 hhh12
2013 hhh13
2014 hhh14
2015 hhh15
2016 hhh16 七、Hive優化
一、自定義函數
之前使用hive自帶函數sum/avg/max/min...
三種自定義函數：
UDF：一進一出（User-Defined-Function）
UDAF:多進一出（count、max、min）
UDTF:一進多出
（1）導入hive依賴包 hive/lib下
（2）上傳 alt+p
（3）添加到hive中 add jar /root/lower.jar;
（4）關聯 create temporary function my_lower as "com.css.com.Lower";
（5）使用 select ename,my_lower(ename) lowername from empt;
二、Hive優化
1、壓縮
（1）開啟Map階段輸出壓縮
開啟輸出壓縮功能：
set hive.exec.compress.intermediate=true;
開啟map輸出壓縮功能：
set mapreduce.map.output.compress=true;
設定壓縮方式：
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
例子：
select count(ename) name from empt;
（2）開啟reduce輸出端壓縮
開啟最終輸出壓縮功能：
set hive.exec.compress.output=true;
開啟最終資料壓縮功能：
set mapreduce.output.fileoutputformat.compress=true;
設定壓縮方式：
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.
compress.SnappyCodec;
設定塊壓縮：
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
例子：
insert overwrite local directory '/root/compress/rsout' select * from empt sort by empno desc;
2、存儲
Hive存儲格式：TextFile/SequenceFile/orc/Parquet
orc:Index Data/row Data/stripe Footer
壓縮比：
orc > parquet > textFile
查詢速度：
orc > textFile
50s > 54s
例子：
create table itstar(time string,host string) row format delimited fields terminated by '\t';
load data local inpath '/root/itstar.log' into table itstar;
create table itstar_log(time string,host string) row format delimited fields terminated by '\t' stored as orc;
insert into itstar_log select * from itstar;
select count(*) from itstar;
select count(*) from itstar_log;
3、Group by優化
分組：mr程式，map階段把相同key的資料分發給一個reduce,一個key的量很大。
解決方案：
在map端進行聚合（combiner）
set hive.map.aggr=true;
設定負載均衡
set hive.groupby.skewindata=true;
4、資料傾斜
（1）合理避免資料傾斜
合理設定map數
合并小檔案
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
合理設定reduce數
（2）解決資料傾斜
在map端進行聚合（combiner）
set hive.map.aggr=true;
設定負載均衡
set hive.groupby.skewindata=true;
（3）JVM重用
mapred-site.xml
mapreduce.job.jvm.numtasks
10~20 （插槽） 附件5：Lower類的代碼如下：
package com.css.com;
import org.apache.hadoop.hive.ql.exec.UDF;
public class Lower extends UDF {
public String evaluate(final String s) {
if (s == null)
return null;
return s.toLowerCase();
}
} 附件6：資料表itstar.log（未顯示全部）
20180724101954 http://java.itstar.com/java/course/javaeeadvanced.shtml
20180724101954 http://java.itstar.com/java/course/javaee.shtml
20180724101954 http://java.itstar.com/java/course/android.shtml
20180724101954 http://java.itstar.com/java/video.shtml
20180724101954 http://java.itstar.com/java/teacher.shtml
20180724101954 http://java.itstar.com/java/course/android.shtml
20180724101954 http://bigdata.itstar.com/bigdata/teacher.shtml
20180724101954 http://net.itstar.com/net/teacher.shtml
20180724101954 http://java.itstar.com/java/course/hadoop.shtml
20180724101954 http://java.itstar.com/java/course/base.shtml
...
...
...
20180724102628 http://java.itstar.com/java/course/javaeeadvanced.shtml
20180724102628 http://java.itstar.com/java/course/base.shtml
20180724102628 http://net.itstar.com/net/teacher.shtml
20180724102628 http://net.itstar.com/net/teacher.shtml
20180724102628 http://net.itstar.com/net/course.shtml
20180724102628 http://java.itstar.com/java/course/android.shtml
20180724102628 http://net.itstar.com/net/course.shtml
20180724102628 http://java.itstar.com/java/course/base.shtml
20180724102628 http://java.itstar.com/java/video.shtml
20180724102628 http://java.itstar.com/java/course/hadoop.shtml 轉載于:https://www.cnblogs.com/areyouready/p/10016487.html
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)