查询&压缩

hive
官网：http://hive.apache.org/
Apache Hive™数据仓库软件有助于使用SQL读取，编写和管理驻留在分布式存储中的
大型数据集。可以将结构投影到已存储的数据中。提供了命令行工具和JDBC驱动程序以
将用户连接到Hive。
hive提供了SQL查询功能 hdfs分布式存储。
hive本质HQL转化为MapReduce程序。
环境前提：1）启动hdfs集群
2）启动yarn集群
如果想用hive的话，需要提前安装部署好hadoop集群。
为什么要学习hive
简化开发。
easycoding!
高德地图使用hive。
优势：
1）操作接口采用类sql语法，select * from stu;
简单、上手快！
2）hive可以替代mr程序，sqoop。
3）hive可以处理海量数据。
4）hive支持UDF，自定义函数。
劣势：
1）处理数据延迟高，慢。
引擎：1.2.2以前版本都是用的mr引擎
2.x之后用的是spark引擎
2）HQL的表达能力有限
一些sql无法解决的场景，依然需要我们写mapreduce.
hive架构原理解析
sql->转换->mapreduce->job
hive安装部署
1）下载
2）上传到linux
alt+p
3)解压
tar -zxvf .tar -C hd/
4)重命名
mv hive-env.sh.template hive-env.sh
5)修改配置文件
vi hive-env.sh
HADOOP_HOME=/root/hd/hadoop-2.8.4
export HIVE_CONF_DIR=/root/hd/hive/conf
6)启动
bin/hive
配置mysql元数据库
1）拷贝mysql驱动到hive/lib
cp/mv hive/lib
2) 添加hive-site.xml
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<value>jdbc:mysql://hd-01:3306/metastore?createDatabas
eIfNotExist=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</descr
iption>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</d
escription>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
<description>password to use against metastore database</d
escription>
</property>
3)注意：重启hadoop集群
4）启动hive
bin/hive
此时mysql中创建metastore元数据库
数据导入操作
load data [local] inpath '/root/itstar.txt' into table itstar;
load data:加载数据
local:可选操作，如果加上local导入是本地linux中的数据，如果去掉local 那么
导入的是hdfs中数据。
inpath:表示的是加载数据的路径
into table:表示要加载的对应的表
hive数据类型
Java数据类型 Hive数据类型 长度
byte TINYINT 1byte有符号整数
short SMALLINT 2byte有符号整数
int INT 4byte有符号整数
long BIGINT 8byte有符号整数
boolean BOOLEAN false/true
float FLOAT 单精度浮点
double DOUBLE 双精度浮点
string STRING 字符
BINARY 字节数组
DDL数据定义
1)查看数据库
show databases;
2)创建库
create database hive_db;
3)创建库 标准写法
create database if not exists hive_db;
4)创建库指定hdfs路径
create database hive_db location '/hive_db';
5)创建表
如果指定了hdfs路径
创建的表存在于这个路径
6）查看数据库结构
desc database hive_db;
7)添加额外的描述信息
alter database hive_db set dbproperties('created'='hunter');
注意：查询需要使用desc database extended hive_db;
8)查看指定的通配库:过滤
show databases like 'i*';
9）删除空库
drop database hive_db;
10）删除非空库
drop database hive_db2 cascade;
11) 删除非空库标准写法
drop database if exists hive_db cascade;
创建表
create [external] table [if not exists] table_name(字段信息) [part
itioned by(字段信息)]
[clustered by(字段信息)] [sorted by(字段信息)]row format delimited
fields terminated by '切割符';
管理表
默认不加external创建的就是管理表，也称为内部表。
MANAGED_TABLE管理表。
Table Type:MANAGED_TABLE
查看表类型：
desc formatted itstar;
外部表
EXTERNAL_TABLE外部表
创建方式：
create external table student(id int,name string)
区别：如果是管理表删除hdfs中数据删除，如果是外部表删除hdfs数据不删除！
hive命令
1）不登录hive客户端直接输入命令操作Hive
bin/hive -e "select * from itstar;"
2）直接把sql写入到文件中
bin/hive -f /root/hived.sql
3）查看hdfs文件
dfs -ls /;
dfs -cat /wc/in/words.txt;
4）查看历史操作
cat ~/.hivehistory
分区表
1）创建分区表
create table dept_partitions()
partition by()
row format
delimited fields
terminated by '';
2)查询
全查询：
select * from dept_partitions;
注意：此时查看的是整个分区表中的数据
单分区查询：
select * from dept_partitions where day = '0228';
注意：此时查看的是指定分区中的数据
联合查询：
select * from dept_partitions where day = '0228' union select *
from dept_partitions where day = '0302';
添加单个分区：
alter table dept_partitions add partition(day = '0303');
注意：如果想一次添加多个的话 空格分割即可
查看分区：
show partitions dept_partitions;
删除分区：
alter table dept_partitions drop partition(day='0305');
分区表在hdfs中分目录文件夹
导入数据
修复：msck repair table dept_partitions;
DML数据操作
1）数据的导入
load data [local] inpath '' into table ;
2）向表中插入数据
insert into table student_partitions partition(age = 18)
values(1,'reba');
向表中插入sql查询结果数据
insert overwrite table student_partitions partition(age = 18) se
lect * from itstar where id<3;
create方式：
create table if not exists student_partitions1 as select * from
student_partitions where id = 2;
3）创建表直接加载数据
create table student_partitions3(id int,name string)
row fromat
delimited fields
terminated by '\t'
locatition '';
注意：locatition路径是hdfs路径。
4）把操作结果导出到本地linux
insert overwrite local directory '/root/datas' select * from its
tar;
5)把hive中表数据导出到hdfs中
export table itstar to '/itstar';
把hdfs数据导入到hive中
import table itstar2 from '/itstar/';
6）清空表数据
truncate table itstar2;
查询操作
基础查询：
select * from table;全表查询
select itstar.id,itstar.name from table;指定列
1）指定列查询
select itstar.name from itstar;
2）指定列查询设置别名
select itstar.name as myname from itstar;
OK
或者直接空格
3）创建员工表
4）查询员工姓名和工资(每个员工加薪1000块)
select emptable.ename,emptable.sal+1000 salmoney from hive_db.em
ptable;
5)查看公司有多少员工
select count(1) empnumber from emptable;
6)查询工资最高的工资
select max(sal) from emptable;
7）查询工资最小的工资
select min(sal) from emptable;
8）求工资的总和
select sum(sal) sal_sum from emptable;
9)求该公司员工工资的平均值
select avg(sal) sal_avg from emptable;
10)查询结果只显示前多少条
select * from emptable limit 10;
11)where语句
作用：过滤
使用：where子句紧接着from
求出工资大于1300的员工
select * from emptable where sal>1300;
求出工资在1300~3000范围的员工
select * from emptable where sal>1300 and sal<3000;
或者
select * from emptable where sal between 1300 and 3000;
或者
select ename from emptable where sal in(2000,3000);
12）is null与is not null
空与非空的过滤
select * from emptable where comm is not null;
13）like
模糊查询
使用：通配符
% 后面零个或者多个字符
查询工资以1开头的员工信息
select * from emptable where sal like '1%';
查询工资地第二位是1的员工信息
select * from emptable where sal like '_1%';
_代表一个字符
查询工资中有5的员工信息
select * from emptable where sal like '%5%';
14）And/Not/Or
查询部门号30并且工资大于1000的员工信息
select * from emptable where sal>1000 and deptno=30;
查询部门号30或者工资大于1000的员工信息
select * from emptable where sal>1000 or deptno=30;
查询工资不在2000到3000的员工信息
select * from emptable where sal not in(2000,3000);
15）分组操作
Group By语句
通常和一些聚合函数一起使用
求每个部门的平均工资
select avg(sal) avg_sal,deptno from emptable group by deptno;
having
where：后不可以与分组函数，而having可以。
求每个部门的平均工资大于2000的部门
select deptno,avg(sal) avg_sal from emptable group by deptno hav
ing avg_sal>2000;
Join操作
员工表中只有部门编号，并没有部门名称
部门表中有部门标号和部门名称
1）查询员工编号、员工姓名、员工所在的部门名称
select emptable.empno,emptable.ename,dept.dname from emptable jo
in dept on emptable.deptno=dept.deptno;
等值join
2）查询员工编号、员工姓名、员工所在部门名称、部门所在地
内连接：只有连接的两张表中都存在与条件向匹配的数据才会被保留下来
select e.empno,e.ename,d.dname,d.loc from emptable e join dept d
on e.deptno=d.deptno;
3）左外连接
left join
查询员工编号，员工姓名，部门名称
select e.empno,e.ename,d.deptname from emptable e left join dept
d on e.deptno=d.deptno;
特点：默认用的Left join 可以省略left
保留左表数据，右表没有join上 显示为null
4)右外连接
right join
select e.empno,e.ename,d.dname from emptable e right join dept d
on e.deptno=d.deptno;
特点：
保留右表数据，左表没有join上 显示为null
5)满外连接
full join
select e.empno,e.ename,d.dname from emptable e full join dept d
on e.deptno=d.deptno;
特点：结果会返回所有表中符合条件的所有记录，如果有字段没有符合条件用null值代
替。
6）多表连接
查询员工名、部门名称、地域名称
select e.ename,d.dname,l.loc_name from emptable e join dept d on
e.deptno=d.deptno join location l on d.loc=l.loc;