Hive
一. 概述
Hive是一个基于hadoop的数据仓库工具，可以用来进行数据的提取转换加载（ETL），Hive将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为mapreduce任务进行执行。
Hive并不是为联机事务而设计，并不提供实时的查询和基于行级的数据更新操作，Hive最佳的使用场所是大数据集的批处理作业。
用户生成SQL脚本，进入Hive
Hive在Mysql数据库中查询元数据（逻辑表到hdfs文件系统的映射关系）
Hive生成SQL的逻辑执行计划
在mapreduce中执行物理计划
Hive从mapreduce中读取运行结果
Hive将结果集进行封装返回client
Hive的特性
Hive和关系数据库存储文件的系统不同，Hive使用的是Hadoop的HDFS，关系数据库则是服务器本地的文件系统。
Hive使用的计算模型是MapReduce，而关系数据库则是自己设计的计算模型。
关系数据库都是为OLTP进行设计的，而Hive则是为海量数据做数据挖掘设计的，实时性很差，实时性的区别导致Hive的应用场景和关系数据库有很大的不同。
Hive很容易扩展自己的存储能力和计算能力，这个是继承Hadoop的特性，而关系数据库在这个方面要比Hive差很多。
二. 安装
由于hive要存储一些建表的元数据信息，因此生产环境中需要实现安装mysql（数据库的字符编码集设置为：Latin1）。其次Hive是构建在Hadoop存储的计算机之上的工具，要保证hdfs和mapreduce正常运行（namenode，datanode，resourcemanager，nodemanager）。安装Hive的主机上必须配置HADOOP_HOME环境变量。
1.准备工作
保证有一个可用的Mysql服务（本机或者linux系统中的mysql）
确保hdfs，yarn正常运行，配置HADOOP_HOME
mysql要开启远程访问权限：
navcat连接mysql后:
进入mysql数据库，选择user表
查询--->新建查询
执行: grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
刷新: flush privileges;
2.更改原来Hadoop的配置
2.1 配置etc/hadoop/core-site.xml
<property>
<name>fs.defaultFS</name>
<value>hdfs://CentOS:9000</value>
</property>
<property>
<name>hadoop.tmp.dir</name>
<value>/usr/hadoop-2.9.2/hadoop-${user.name}</value>
</property>
2.2 配置etc/hadoop/hdfs-site.xml
<!--block副本因子-->
<property>
<name>dfs.replication</name>
<value>1</value>
</property>
<!--配置Sencondary namenode所在物理主机-->
<property>
<name>dfs.namenode.secondary.http-address</name>
<value>CentOS:50090</value>
</property>
<!--设置datanode最大文件操作数-->
<property>
<name>dfs.datanode.max.xcievers</name>
<value>4096</value>
</property>
<!--设置datanode并行处理能力-->
<property>
<name>dfs.datanode.handler.count</name>
<value>6</value>
</property>
2.3 配置etc/hadoop/mapred-site.xml
<!--MapRedcue框架资源管理器的实现-->
<property>
<name>mapreduce.framework.name</name>
<value>yarn</value>
</property>
2.4 配置etc/hadoop/yarn-site.xml
<!--配置MapReduce计算框架的核心实现Shuffle-洗牌-->
<property>
<name>yarn.nodemanager.aux-services</name>
<value>mapreduce_shuffle</value>
</property>
<!--配置资源管理器所在的目标主机-->
<property>
<name>yarn.resourcemanager.hostname</name>
<value>CentOS</value>
</property>
<!--关闭物理内存检查-->
<property>
<name>yarn.nodemanager.pmem-check-enabled</name>
<value>false</value>
</property>
<!--关闭虚拟内存检查-->
<property>
<name>yarn.nodemanager.vmem-check-enabled</name>
<value>false</value>
</property>
2.5 在.bashrc下配置HADOOP_HOME环境变量
HADOOP_HOME=/usr/hadoop-2.9.2
JAVA_HOME=/usr/java/latest
PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin
CLASSPATH=.
export JAVA_HOME
export PATH
export HADOOP_HOME
export CLASSPATH
//注意：此文件中如果原来配置过 HADOOP_CLASSPATH,需要注掉#export HADOOP_CLASSPATH=`hbase classpath`
//hbase中mapreduce是自己写的，所以需要配置hbase classpath在hadoop_classpath中
2.6 启动hadoop的hdfs和yarn
[root@CentOS ~]# start-dfs.sh
[root@CentOS ~]# start-yarn.sh
2.7 Hive的安装
解压Hive的安装包
[root@CentOS ~]# tar -zxf apache-hive-1.2.2-bin.tar.gz -C /usr/
[root@CentOS ~]# cd /usr/apache-hive-1.2.2-bin/
创建conf/hive-site.xml，配置以下内容(原来没有此文件)
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://CentOS:3306/hive?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>root</value>
</property>
</configuration>
将mysql的驱动jar包拷贝到lib目录下
上传mysql的jar包
cp mysql-connector-java-5.1.18.jar /usr/apache-hive-1.2.2-bin/lib
配置HIVE_HOME的环境变量
export HIVE_HOME=/usr/apache-hive-1.2.2-bin
export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin
启动Hive
单用户模式: hive
看到如下结果:
Logging initialized using configuration in jar:file:/usr/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.properties
hive> show tables;
OK
Time taken: 0.726 seconds
hive>
多用户模式: hiveserver2 >/dev/null 2>&1 &（后台启动）
[3] 26279
在想要访问的物理机上执行: beeline -u jdbc:hive2://CentOS:10000 -n root
看到如下结果:
Connecting to jdbc:hive2://CentOS:10000
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.2 by Apache Hive
0: jdbc:hive2://CentOS:10000>
三. 表操作
1. 数据类型
| 类型 | 描述 | 字面量示意 |
|---|---|---|
| ARRAY | 有序的同类型的集合 | array(1,2) |
| MAP | map集合 | map(‘a’,‘1’,‘b’,‘2’) |
| STRUCT | 字段集合,类型可以不同 | struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) |
2. 默认模式建表
创建测试数据库test，并进入test数据库中建表
0: jdbc:hive2://CentOS:10000> show databases;
+----------------+--+
| database_name |
+----------------+--+
| default |
+----------------+--+
1 row selected (0.051 seconds)
0: jdbc:hive2://CentOS:10000> create database test;
No rows affected (0.193 seconds)
0: jdbc:hive2://CentOS:10000> use test;
No rows affected (0.044 seconds)
0: jdbc:hive2://CentOS:10000> select current_database();
+-------+--+
| _c0 |
+-------+--+
| test |
+-------+--+
在test中创建t_employee表
create table if not exists t_employee (
id int,
name varchar(32),
age int,
salary double,
birthDay date,
hobbies array<string>,
address struct<street:string,country:string>,
detail map<string,double>
);
用户可使用desc formatted 表名来查看表中的信息 （不加formatted时，查看简略信息）
3. 默认分隔符
| 分隔符 | 描述 |
|---|---|
| \n | 对于文本文件而言，一行表示一条文本记录，因此可以使用\n表示 |
| ^A(Ctrl+A) | 用于分割字符的列，在创建表的时候可以使用八进制’\001’表示 |
| ^B(Ctrl+B) | 用于分割ARRAY、STRUCT或者MAP中的元素，在创建表的时候可以使用八进制’\002’表示 |
| ^C(Ctrl+C) | 用于分割MAP中的key,value，在创建表的时候可以使用八进制’\003’表示 |
建表语句（采用默认的分隔符）
create table if not exists t_employee (
id int,
name varchar(32),
age int,
salary double,
birthDay date,
hobbies array<string>,
address struct<street:string,country:string>,
detail map<string,double>
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by '\n'
stored as textfile;
在数据文件中输入数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PMIUPCSA-1578573573210)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1578569218271.png)]
将文件中的数据上传到hdfs文件系统中
load data local inpath ‘xxx路径’ overwrite into table Xxx表
4. 自定义分隔符
建表语句
create table if not exists t_user(
id int,
name string,
sex boolean,
age int,
salary double,
hobbies array<string>,
card map<string,string>,
address struct<country:string,city:string>
)
row format delimited
fields terminated by ','
collection items terminated by '|'
map keys terminated by '>'
lines terminated by '\n'
stored as textfile;
数据文件
1,zhangsan,true,18,15000,TV|Game,001>建设|002>招商,china|bj
2,lisi,true,28,15000,TV|Game,001>建设|002>招商,china|bj
3,wangwu,false,38,5000,TV|Game,001>建设|002>招商,china|sh
在hive中将数据文件上传到hdfs文件系统
load data local inpath ‘xxx路径’ overwrite into table Xxx表
5. 正则格式数据
192.168.0.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00
192.168.2.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00
192.168.0.1 xx com.xx.xx.XxxService#xx 2018-10-10 10:10:00
192.168.202.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00
create table if not exists t_access(
ip string,
app varchar(32),
service string,
last_time timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex"="^(.*)\\s(.*)\\s(.*)\\s(.*\\s.*)"
);
load data local inpath ‘xxx路径’ overwrite into table Xxx表
6. csv格式文件（大表格）
1,apple,3,20.5
2,orange,2,21.5
3,pear,2,22.5
4,banana,1,23.0
CREATE TABLE if not exists t_product(
id int,
item string,
count int,
price double
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"escapeChar" = "\\"
);
7. JSON格式文件
{"id":1,"name":"zhangsan","sex":true,"register":"1991-02-08","score":100.0}
{"id":2,"name":"lisi","sex":true,"register":"1991-02-08","score":80.0}
create table if not exists t_student(
id int,
name varchar(32),
sex boolean,
register date,
score double
)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
需要添加jar文件 add jar /usr/apache-hive-1.2.2-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.2.jar
删除的时候使用delete指令
四. Java访问Hive
1.保证Hive服务端启动Hiveserver2服务
[root@CentOS ~]# hiveserver2 >/dev/null 2>&1 & # 后台启动服务
[1] 8772
2.导入jar包
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.2</version>
</dependency>
3.java的jdbc链接hive代码
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 加载驱动类
Class.forName("org.apache.hive.jdbc.HiveDriver");
// 获取链接
Connection conn = DriverManager.getConnection("jdbc:hive2://hbase:10000/test", "root", "");
// 创建statement（用于执行sql）
Statement statement = conn.createStatement();
// 执行sql语句
String sql="select id,name,age,salary from t_employee";
ResultSet resultSet = statement.executeQuery(sql);
// 处理查询结果
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
double salary = resultSet.getDouble("salary");
System.out.println(id +" "+name+" "+age+" "+salary+" ");
}
// 释放资源
resultSet.close();
statement.close();
conn.close();
}
五. Hive的DDL操作
1. database
查看数据库
show databases;
使用数据库
use test; --test 是数据库名
新建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
查看数据库信息
DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性
删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
---默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。
drop database if exists test cascade;
查看当前数据库
select current_database();
2. Table
表的分类：
管理表: 管理表也称为内部表Managed_table;默认存储在/user/hive/warehouse下,也可以通过location指定,删除表时同时删除元数据和表数据。
外部表: 外部表称之为EXTERNAL_TABLE;在建表时可以自己指定存储位置,删除表时,只删除元数据,并不会删除表数据。
分区表: Hive中的表对应hdfs上的指定目录，在查询时默认对全表进行扫描，这样很消耗内存和性能。分区为hdfs上表目录中建立的子目录，数据按照分区存储在子目录中，如果条件符合分区，则根据对应的分区查找即可，而不必去加载整个目录。
分桶表: 分区表是将文件按照分区进行粗粒度的文件隔离，但分桶是根据数据的某个字段进行hash计算出所属的桶，然后再对桶内的数据进行排序。
临时表: 此表仅当前用户可见，临时表中的数据存储用户的暂存目录中，并会在回话结束后删除。
创建管理表
create table if not exists t_user(
id int,
name string,
sex boolean,
age int,
salary double,
hobbies array<string>,
card map<string,string>,
address struct<country:string,city:string>
)
row format delimited
fields terminated by ','
collection items terminated by '|'
map keys terminated by '>'
lines terminated by '\n'
stored as textfile;
创建外部表
create external table if not exists t_access(
ip string,
app varchar(32),
service string,
last_time timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex"="^(.*)\\s(.*)\\s(.*)\\s(.*\\s.*)"
)
LOCATION '/hive/t_access';
创建分区表
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00
7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00
CREATE EXTERNAL TABLE t_employee(
id INT,
name STRING,
job STRING,
manager INT,
hiredate TIMESTAMP,
salary DECIMAL(7,2)
)
PARTITIONED BY (deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/t_employee';
上传分区数据
load data local inpath '/root/t_emp' overwrite into table t_employee partition(deptno='10');
select id ,name,job,manager,salary,deptno from t_employee;
创建分桶表
CREATE EXTERNAL TABLE t_employee_bucket(
id INT,
name STRING,
job STRING,
manager INT,
hiredate TIMESTAMP,
salary DECIMAL(7,2),
deptno INT)
CLUSTERED BY(id) SORTED BY(salary ASC) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/employee_bucket';
: jdbc:hive2://CentOS:10000> set hive.enforce.bucketing = true;
0: jdbc:hive2://CentOS:10000> INSERT INTO TABLE t_employee_bucket SELECT * FROM t_employee;
创建临时表
CREATE TEMPORARY TABLE if not exists emp_temp(
id INT,
name STRING,
job STRING,
manager INT,
hiredate TIMESTAMP,
salary DECIMAL(7,2),
deptno INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_temp';
修改表
重命名表
0: jdbc:hive2://CentOS:10000> ALTER TABLE t_user RENAME TO t_u;
修改列(修改类型、顺序、新增)
0: jdbc:hive2://CentOS:10000> ALTER TABLE t_employee CHANGE id eid INT;--修改列名&类型
0: jdbc:hive2://CentOS:10000> ALTER TABLE t_employee CHANGE eid id decimal(7,2) AFTER name;--修改顺序
0: jdbc:hive2://CentOS:10000> ALTER TABLE t_employee ADD COLUMNS (address STRING);
清空表
0: jdbc:hive2://CentOS:10000> truncate table t_employee partition(deptno=10);
只可以截断managed-table
删除
0: jdbc:hive2://CentOS:10000> drop table t_employee PURGE;
PURGE表示数据会直接删除，不会放置在垃圾箱中
3. 其他命令
查看数据库
DESCRIBE|DESC DATABASE [EXTENDED] db_name;
查看表
DESCRIBE|DESC [EXTENDED|FORMATTED] table_name
查看数据库列表
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
查看分区
0: jdbc:hive2://CentOS:10000> show partitions t_employee;
+------------+--+
| partition |
+------------+--+
| deptno=10 |
+------------+--+
1 row selected (0.065 seconds)
查看建表语句
0: jdbc:hive2://CentOS:10000> show create table t_employee;
六. Hive的SQL案例分析
数据筹备
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,\N,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,\N,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,\N,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,\N,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,1500,\N,20
7839,KING,PRESIDENT,\N,1981-11-17 00:00:00,5000,\N,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,\N,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,\N,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,\N,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,\N,10
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
建表语句
CREATE TABLE t_employee(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
row format delimited
fields terminated by ','
collection items terminated by '|'
map keys terminated by '>'
lines terminated by '\n'
stored as textfile;
CREATE TABLE t_dept(
DEPTNO INT,
DNAME STRING,
LOC STRING)
row format delimited
fields terminated by ','
collection items terminated by '|'
map keys terminated by '>'
lines terminated by '\n'
stored as textfile;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g1lh69N4-1578798240867)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\1578658260245.png)]
SQL查询
单表查询
select empno,ename,job,mgr,hiredate,sal,comm,deptno from t_employee;
where 查询
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_employee WHERE empno > 7782 AND deptno = 10;
DISTINCT查询
select distinct(job) from t_employee; ---数据去重，效果和group by类似，尽量使用group by，因为性能要好与distinct。
分区查询
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_employee_partition e WHERE e.deptno >= 20 AND e.deptno <= 40;
limit查询 —限制结果条数
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM t_employee ORDER BY sal DESC LIMIT 5;
group by查询
set hive.map.aggr=true;
SELECT deptno,SUM(sal) as total FROM t_employee GROUP BY deptno;
hive.map.aggr控制程序如何进行聚合。默认值为false。如果设置为true，Hive会在map阶段就执行一次聚合。这可以提高聚合效率，但需要消耗更多内存。
order and sort
对字段进行排序，如果整形，按照大小顺序进行排序，如果字符串，按照字典顺序进行排序。
order by 和 sort by的区别：
使用order by时会有一个Reducer对全部查询结果进行排序，因此order by可以保证查询结果的全局有序性；使用sort by只会在每个Reducer中进行排序，不保证全局的有序性。
由于ORDER BY的时间可能很长，如果你设置了严格模式(hive.mapred.mode = strict)，则其后面必须再跟一个limit子句。
sort by
0: jdbc:hive2://CentOS:10000> set mapreduce.job.reduces=2
0: jdbc:hive2://CentOS:10000> SELECT empno,ename,sal from t_employee sort by sal desc;
order by
0: jdbc:hive2://CentOS:10000> set mapreduce.job.reduces=3;
0: jdbc:hive2://CentOS:10000> SELECT empno,ename,sal from t_employee order by sal desc;
set hive.mapred.mode = strict;
0: jdbc:hive2://CentOS:10000> SELECT empno,ename,sal from t_employee order by sal desc;
Error: Error while compiling statement: FAILED: SemanticException 1:48 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'sal' (state=42000,code=40000)
0: jdbc:hive2://CentOS:10000> SELECT empno,ename,sal from t_employee order by sal desc limit 5;
having
0: jdbc:hive2://CentOS:10000> SELECT deptno,SUM(sal) total FROM t_employee GROUP BY deptno HAVING SUM(sal)>9000;
Distribute by
默认情况下，mepreduce会将map输出结果的key值进行散列，均分到所有reduce上，而distribte by会将所有相同的key值得数据分发到同一个reduce上进行处理，需要注意的是distribute by虽然会将相同key值得数据聚集到一个reduce上，但比不会排序。
在set mapreduce.job.reduces=指定的数量;（有几种deptno）
SELECT empno,ename,sal, deptno FROM t_employee distribute BY deptno;
SELECT empno,ename,sal, deptno FROM t_employee distribute BY deptno sort by sal desc; //根据dept划分 后再根据sal排序。
cluster by
如果sort by和distribute by指定的是相同的字段，且sort by的排序规则是ASC，此时可使用cluster by替换sort by和distribute by。
0: jdbc:hive2://CentOS:10000> SELECT empno,ename,sal, deptno FROM t_employee cluster by deptno;
小结：
order by和sort by的区别：只有一个reduce时，order by和sort by效果相同，都是全局排序，当有多个reduce时，sort by就不在保证数据全局有序，此时sort by一般与ditribute by连用，保证数据在不同分区有序。
cluster by：相当于sort by和distribute by指定相同的字段，且sort by是ASC。
表连接
Hive支持内连接，外连接，左外连接，右外连接，笛卡尔连接，这和传统数据库中的概念是一致的。需要特别强调：JOIN语句的关联条件必须用ON指定，不能用WHERE指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果。
内连接
SELECT e.empno,e.ename,e.sal,d.dname,d.deptno FROM t_employee e JOIN t_dept d ON e.deptno = d.deptno WHERE e.empno=7369;
外连接
SELECT e.empno,e.ename,e.sal,d.dname,d.deptno FROM t_employee e LEFT OUTER JOIN t_dept d ON e.deptno = d.deptno;
LEFT SEMI JOIN（半开连接）
LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。
JOIN 子句中右边的表只能在 ON 子句中设置过滤条件;
查询结果只包含左边表的数据，所以只能SELECT左表中的列。
SELECT e.empno,e.ename FROM t_employee e LEFT SEMI JOIN t_dept d ON e.deptno = d.deptno AND d.loc="NEW YORK";
上述查询结果等价于
select e.empno,e.ename from t_employee e where e.deptno in (select t.deptno from t_dept t where t.loc="NEW YORK");
开窗函数
查询员工表的员工编号，姓名，薪资，员工所在部门的平均薪资。 ---单表查询可用开窗解决
select e.empno ,e.ename,e.sal,e.deptno,avg(sal) over(partition by e.deptno order by e.sal) from t_employee e;
count(列名)：查询符合列名条件的行。
count(*)：查询表中所有行。
rank() over(partition by e.deptno order by e.sal) as rank ---根据over后的内容进行排序，返回排名。
over():开窗函数 括号里面写限定条件，什么都不加，查询表中所有
dense_rank() over(partition by 属性值 order by 属性 desc/asc) from 表名 //紧密排名
Cube分析
普通查询：select e.deptno,e.job,avg(e.sal) avg,max(e.sal) max,min(e.sal) min from t_employee e group by e.deptno,e.job ;
将数据按照部门名和职位名进行分组，并求部门名，职位名，平均薪资，最大薪资等
cube查询：select e.deptno,e.job,avg(e.sal) avg,max(e.sal) max,min(e.sal) min from t_employee e group by e.deptno,e.job with cube;
多维度查询，将用户分别按部门，职位名，部门和职位名进行分组，并查询上述信息。
在应用中，由于cube数据量大，可以进行预处理，再次获取信息时只要在cube结果中进行sql查询即可。
case when 语句使用
(case ... when ... then ... else ...)：用case后的字段判断是否 '==' when后面的条件，当条件是 '>=' 或 '<=' 时，我们一般case后不加字段，将字段和判断条件写在when后面。
使用场景：
等值转换 性别在表中存储的是0/1，select查询中想要转换为男/女
select name as '名字',(case sex when 0 then '女' else '男' end) as '性别' from 表名;
范围转换 用户分数在90,80,60不同的区间内，select查询中想要转换为优秀，良好，不及格
select name as '名字',(case when score>=90 then '优秀' when score >=60 then '良好' else '不及格' end) as 等级 from 表名
列转换 select name as '名字',
max(case course when '语文' then score else 0 end) as '语文',
max(case course when '数学' then score else 0 end) as '数学',
max(case course when '英语' then score else 0 end) as '英语',
from 表名 group by name;
列转换的表
Hive on Hbase
hive还可以直接执行sql文件：
1.将想要执行的sql语句写入以.sql结尾的文件中
2.不需要启动hive，直接使用脚本 hive -f xxx.sql
在hbase中建表：
hbase(main):005:0> create_namespace 'jiangzz'
0 row(s) in 0.3920 seconds
hbase(main):006:0> create 'jiangzz:t_employee','cf1','cf2'
0 row(s) in 2.6110 seconds
=> Hbase::Table - jiangzz:t_employee
hbase(main):007:0>
在hive中：
1.准备数据
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,\N,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,\N,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,\N,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,\N,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,1500,\N,20
7839,KING,PRESIDENT,\N,1981-11-17 00:00:00,5000,\N,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,\N,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,\N,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,\N,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,\N,10
2.建表：
CREATE TABLE t_emp(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
row format delimited
fields terminated by ','
collection items terminated by '|'
map keys terminated by '>'
lines terminated by '\n'
stored as textfile;
3.加载数据到t_tmp
0: jdbc:hive2://CentOS:10000> load data local inpath '/root/hivedata/t_emp' overwrite into table t_emp;
4.建立hbase中的表和hive中的表的映射关系：
create external table t_employee(empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key,cf1:name,cf1:job,cf1:mgr,cf1:hiredate,cf1:sal,cf1:comm,cf1:deptno")
TBLPROPERTIES("hbase.table.name" = "jiangzz:t_employee");
5.执行sql文件
文件内容：
use jiangzz;
insert overwrite table t_employee select empno,ename,job,mgr,hiredate,sal,comm,deptno from t_emp;
ERROR : Ended Job = job_1578881006622_0016 with errors
Error: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask (state=08S01,code=2)
0: jdbc:hive2://CentOS:10000>
需要替换hive-hbase-handler-1.2.2.jar
1.创建一个maven项目（不用选择骨架），添加以下依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.jiangzz</groupId>
<artifactId>hive-hbase-handler</artifactId>
<version>1.2.2</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>6</source>
<target>6</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-serde</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-service</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-common</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-client</artifactId>
<version>1.2.4</version>
</dependency>
<dependency>
<groupId>org.apache.hbase</groupId>
<artifactId>hbase-server</artifactId>
<version>1.2.4</version>
</dependency>
</dependencies>
</project>
2.在http://hive.apache.org/官方网站上下载与自己的hive相对应的版本源码
解压下载下来的源码，将hbase-handler下的src下的java下的源码拷贝到项目的src目录下
3.执行mvn package指令，打包生成hive-hbase-handler-1.2.2.jar,然后将该jar替换HIVE_HOME/lib下的hive-hbase-handler-1.2.2.jar下。
扫一扫
2624
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
