hive 常用命令 DDL DML 验证数据 加载数据 内部表和外部表 分区 分桶 数据类型 表连接 排序 函数 jdbc

hive：

基于hadoop的数据仓库
数据库：
真正存储和管理数据的，对数据有直接的处置权。
关心的事情是在线事务过程(OLTP)
数据仓库：
可以通过一个或多个数据库读取数据做一些数据分析
关心的事情是在线分析过程(OLAP)
举例：
数据库操作：下订单，待发货，已发货，待评价，。。。
数据仓库操作：通过数据分析出销量，年龄段，性别，。。。

• hive为什么是基于hadoop的？
  1.logo的头是hadoop的头(勉强算一个理由)
  2.hive是存在与hadoop生态圈中的一个组件
  3.hive本身执行的就是MapReduce

• hive怎么执行的MapReduce？
  hive是使用类sql(HQL/HiveQL)语句完成的MapReduce

• hive可以对MapReduce做极大的简化，为什么保留MapReduce？
  有某些特殊业务，可以无法使用sql语句完成。
  此时必须使用传统的MapReduce完成。存在即有道理。

• 为什么使用sql语句的形式简化MapReduce？
  BI(商业智能) 存储过程(使用sql语句编程)
  方便了这些对于sql语句使用熟练，但对代码不太熟练的人群

• hive的运行原理
  写sql语句，hive内部自动把sql语句转换成MapReduce执行。
  原数据在hdfs上，sql语句需要用到表，所有需要创建表，
  表是以目录的形式存储在hdfs上的某个位置。
  通过加载数据的形式，可以把原数据加载到这个表对应的目录下。
  通过sql语句(mapreduce)对原数据进行分析，结果写入hdfs。

• derby数据库：
  同一个时刻只允许单用户登录的一个轻量级的数据库，无法数据共享。
  如果想做到数据共享，换成mysql数据库充当hive里的元数据库。

• 环境搭建：
  derby数据库：
  1.解压缩
  2.配置环境变量
  3.修改配置文件
  mv hive-env.sh.templete hive-env.sh
  export HADOOP_HOME=
  export HIVE_CONF_DIR=
  hive-site.xml
  看文件
  4.启动服务
  hive
  5.正确执行
  mysql数据库：
  在derby数据库基础上
  a.在hive-site.xml中添加mysql连接信息
  b.把mysql-connector-java.jar放入 $ {hive}/lib
  c.把${HIVE_HOME}/lib 下的 jline-xxx.jar放入 ${HADOOP_HOME}/share/hadoop/yarn/lib 下
  d.schematool -dbType mysql -initSchema 初始化数据库类型
  e.修改mysql的连接权限

hive的常用命令
*hive-help 查看关于hive的所有命令的帮助信息

• 在linux的命令行下执行hive的sql语句
  hive -e “sql” / ‘sql’

• 在linux命令行下执行写有hive的sql语句的文件
  hive -f fileName
  hive -f fileName >> fileRes
  注意：在linux的命令行下执行hive语句的时候，
  先进入到hive环境，执行sql，执行完毕自动退回到linux环境。

• 在hive环境下执行linux的命令
  hive> ! linux命令

• 在hive环境下执行hdfs的命令
  //以普通linux命令的形式执行，速度很慢
  hive> ! hdfs dfs commond
  //hive提供了一种简便方法，执行速度非常快
  hive> dfs commond

• 日志文件
  在${HIVE_HOME}/conf hive-log4j.properties 进行修改日志的路径和文件名

• hive的历史命令
  cat ~/.hivehistory

• 设置属性
  a.hive-default.xml
  默认设置的配置项，不能修改
  b.hive-site.xml
  可以根据需要修改部分配置项，覆盖掉hive-default.xml的内容
  不管hive服务是否重启，属性值不变。
  c.直接通过命令设置属性
  只对当次连接生效，重启hive服务失效。
  I.在hive环境下直接设置属性
  hive> set properties=value 设置属性值
  hive> set properties 查看属性值
  II.在linux环境下设置属性
  //会直接进入hive环境，而且保留在hive环境下
  hive -hiveconf properties=value

DDL

关于数据库

   増：create database [if not exists] dbName;
   删：
       drop database dbName;   删除空数据库
       drop database dbName cascade;  级联删除非空数据库
   改
   查：show databases;

关于表

   増：create table [if not exists] tbName(field type,...)
   删：drop table tbName;
   改：
       修改表名：alter table tbName rename to newTbName;
       添加字段：alter table tbName add columns(field type,...)
   查：show tables; describe tbName;

DML

关于表里的数据

   増：insert into tbName values(field,...)
   查：select * from tbName;

验证数据
真实数据：
a.hdfs里的${hive.metastore.warehouse.dir}路径为hive的根目录
b.此目录也是default数据库的所在目录
c.其他所有新建数据库都在此目录下以 xxx.db 的形式存在
d.数据库里的表在相应的数据库所在目录下，以文件的形式存在，文件名即表名
e.具体的表数据在每个表的目录下以文件的形式存在
元数据：
a.存在配置文件里指定的数据库(mysql)下的元数据库(metastore)里
b.在元数据库里，通过各种表的id进行表连接，可以获得所有内容
包括数据库，表，数据，位置，字段等具体信息。

加载数据
注意：
加载数据的默认分隔符为 ^A
如果想把分隔符改成我们常见的"\t",在建表的时候需要特别设置。
create table tbName(field type,…)
row format delimited
fields terminated by “\t”

• 通过sql语句向已知表添加数据，需要执行MapReduce。
  insert into tbName values(field1,field2,…);

• 从linux本地上传到表中，上传n次原文件不删除。
  load data local inpath " 文件所在路径 " [overwirte] into table tbName;

• 从hdfs上传到表中，上传一次，相当于剪切。
  load data inpath " 文件所在路径 " [overwirte] into table tbName;

• 创建表的时候，指定某个已有位置充当表的位置
  create table tbName(field type,…)
  row format delimited
  fields terminated by “\t”
  location “xxxxxxx”

内部表和外部表
内部表也叫做管理表。
特点：
删除表的时候，不关心是否为空，也没有任何的确认提示。
元数据，表结构(目录)，表数据(目录下的文件)一并删除。
外部表
特点：
删除表的时候，只删除表结构(元数据)，不删除hdfs里的真实数据
表被删除之后，可以以创建相同表名的形式恢复表。
验证结论：
1.如果新表字段比原表多，不足的字段以NULL占位。
2.如果新表字段比原表少，按顺序匹配。
3.如果新表字段名不同于原表，不受任何影响，按顺序匹配。
4.如果新表字段类型不同于原表，根据实际情况判断。
5.新表可以以默认的内部表的形式恢复，但是再删除就什么都没有了

分区：
在查询的过程过，按照某种条件进行分区。
在mysql中的表现形式：where
区别：
在语法上没有任何区别
在mysql中，如果想使用where条件，这个字段必须在表中存在。
在hive中，如果想使用分区，分区的字段可以在表中不存在。

• 创建分区：
  在创建表的时候就需要执行分区字段。
  create table tbName(…)
  partitioned by (分区字段 字段类型)
  row format delimited
  fields terminated by “\t”;

• 表现形式：
  1.创建表的时候，普通字段和分区字段是区分开的
  2.select的时候，是可以正常查询到普通字段和分区字段的
  3.在元数据库里，普通字段和分区字段是在不同的表当中的
  4.加载数据的时候，需要指定具体的分区字段
  load data local inpath “” into table tbName partition (分区字段=“自定义分区名称”)
  5.在HDFS上，每个分区都是一个目录，名称为分区字段=分区名称
  6.如果仿照其他分区的形式创建目录和上传文件，无法显示分区，因为在元数据库中没有此分区信息。可以通过以下形式补救：
  alter table tbName add partition(分区字段=“自定义分区名称”)

• 单分区
  以一个字段作为分区条件进行分区。

• 多分区
  以多个字段同时作为条件进行分区。
  表现形式上与单分区没有什么区别，就是多了几个分区的条件
  在HDFS上每个分区条件为一个层级的目录

分桶
分区：以目录级别进行的划分
分桶：以文件级别进行的划分
目的：为了获取数据的随机抽样数据
步骤：

• 创建分桶表
  create table hero(id int,name string,gender string)
  //按照某个字段进行分桶，字段一定是表中的字段，所以不需要添加类型
  clustered by (id)
  //注意分桶一定要说明桶的数量
  into 4 buckets
  row format delimited
  fields terminated by “\t”

• 查看表的详细信息
  desc formatted hero;

• 导入数据
  load data local inpath " 路径 " into table hero;
  通过查看数据，没有任何变化，没有成功分桶

• 设置属性
  hive> set hive.enforce.bucketing=true;
  通过查看数据，没有任何变化，没有成功分桶

• 创建一个标准的hero_fb表，表结构与hero是完全相同的。
  create table hero_fb(id int,name string,gender string)
  row format delimited
  fields terminated by “\t”

• 向hero_fb表加载数据
  load data local inpath " 路径 " into table hero_fb;

• 查询hero_fb(普通表)的数据直接添加到hero(分桶)表中
  insert overwrite/into table hero select * from hero_fb;

• 验证
  插入数据的时候会执行MapReduce，可以看到reduce的数量为之前设置的桶数。
  在hdfs上的文件数量也是之前设置的桶数。证明数据已经分桶。

分桶规则：
按照分桶字段的hashCode值对桶数取余，余数代表当前数据所属的桶的编号。
与MapReduce里的默认分区规则比较类似。

查看随机分桶数据
a.按照字段分桶
select * from hero tablesample (bucket x out of y on field);
b.完全的随机分桶
select * from hero tablesample (bucket x out of y on rand());

数据类型

java中               hive中

byte                tinyint

short               smalint

int                 int

long                bigint

float               float

double              double

boolean             boolean

string              string

复杂数据类型

array：数组，可以在一个字段中同时存放多个元素
map：键值对，可以在一个字段中可以存放多个键值对的元素
struct：结构体，类似于实体类，也是以键值对形式存在，但是元素不能随意指定。

案例：

创建employee表：
create table emploee(
  empId int,
  empName string,
  salary double,
  subordinate array<string>,
  tax map<string,double>
  address struct<city:string,area:string>
)
row format delimited
fields terminated by "\t"
collection items terminated by ","
map keys terminated by ":";
加载数据：
load data local inpath "路径" into table employee;
查看复杂数据类型数据：
a.array    array[index]
b.map   map[key]
c.struct   struct.properties
举例：
select nvl(subordinate[0],"low") as sub,nvl(tax["gjj"],0.00) as gjj from employee;
年薪：select salary*12 as salaryTotal from employee;
真实工资：select (1-nvl(tax["gjj"],0)-nvl(tax["sb"],0)-nvl(tax["yl"],0))*salary as salaryReal from employee;

嵌套查询（子查询）
注意：子查询的时候，作为子表的查询语句一定要有别名，否则报错。
查询税后薪资小于800的数据
select * from (select (1-nvl(tax[“gjj”],0)-nvl(tax[“sb”],0)-nvl(tax[“yl”],0))*salary as salReal from employee) as sss where salReal<800;

like
select empname from employee where empname like “%z%”;

case…when…
1.按照字段内容匹配，逻辑上类似于java的 switch…case…
select empname,
case empname when " " then " "
when " " then " "
else " "
end
as desc
from employee;
2.按照字段条件匹配,逻辑上类似于java的if…else…
select empname,salary,
case when salary>1000 and salary<1001 then " "
when salary>1001 then " "
when salary>500 and salary<1000 then " "
else " "
end
as desc
from employee;

表连接
相当于把mysql和MapReduce里的表连接整合在一起
首先hive中有表，可以以表的形式做表连接，但是hive执行的是MapReduce。

• inner join
  内连接，所有的匹配项都显示

• left join
  左外连接，以左表为主表进行连接

• right join
  右外连接，以右表为主表进行连接

• full join
  全连接，所有的数据全都显示

• left semi join
  半连接，按照连接条件进行连接，显示的是一半数据

案例：
股票和股息

创建表
a.stocks表
create table stocks(
  exg string,
  symbol string,
  ymd    string,
  price_open float,
  price_low  float,
  price_close float,
  volume int,
  price_adj_close float
)
row format delimited 
fields terminated by ',';
b.dividends表
create table dividends (
   exg string,
   symbol string,
   ymd string,
   dividend float
)
row format delimited 
fields terminated by ',';
加载数据
load data local inpath "" into table stocks; 
load data local inpath "" into table dividends;
查询数据
select d.symbol,d.ymd,s.price_close from dividends d left join stocks s 
on d.symbol=s.symbol and d.ymd=s.ymd where d.symbol="AAPL";

排序：

• order by
  从MapReduce的角度分析，相当于numReduceTask=1的全排序。
  即使通过设置属性的方式改变了reduce的数量，结果与reduce=1保持一致。

• sort by
  表面看与order by是一样的，但是是部分排序。
  对于每个分区自己有序，但是全局无序。

• distribute by
  按照某个字段进行分区。可以随意设置reduce的数量。
  这个字段的hashCode值与reduce数量取余，余数代表分区编号。
  如果在分区的基础上，按照某个字段进行排序，可以distribute by + sort by
  只能与sort by连用，与order by连用时报错。

• cluster by
  如果分区和排序为同一个字段，可以使用cluster by
  distribute by + sort by

• group by
  一般聚合函数与分组连用。
  select count(deptno) as count,sum(sal) as sumSal from emps group by deptno;

函数

• 内置函数
  hive本身给我们提供的函数，无需特别指定，可以直接使用的函数。
  数学函数，时间函数，条件函数，字符函数，类型转换函数，集合函数，聚合函数，表生成函数。

• 开窗函数
  与分组函数(group by)类似，也可以分组，但是可以按照自己指定的条件进行分组。
  分类：
  a.聚合开窗函数
  b.排序开窗函数

• 自定义函数
  如果内置的函数不能满足我们的需求，可以自已根据业务逻辑写代码，实现自定义功能。
  函数类型：
  UDF 用户自定义普通函数
  UDAF 用户自定义聚合函数(多进一出)
  UDTF 用户自定义表生成函数(一进多出)
  操作步骤：
  1.写代码
  a.extends UDF
  简单粗暴，代码简单但是不适合做比较细腻的业务。
  b.extexds GenaricUDF
  代码相对比较复杂，但是适合做细腻的业务。
  2.使用maven把项目打成jar包
  3.把jar包放在${HIVE_HOME}/lib下(此路径非必须)
  如果想添加永久函数，jar包要放在hdfs的路径下
  4.list jar;add jar
  path/xx.jar(此jar包路径没有双引号)
  5.添加函数
  a.添加临时函数
  create temporary function 函数名 as “类的全路径”(此路径加双引号)
  b.添加永久函数
  create function 函数名 as “类的全路径” using jar “hdfs://xxx/xxx.jar”
  6.验证
  show functions;
  7.可以正常使用自定义函数
  注意：
  a.临时函数可以用show functions查询到，永久函数不行
  b.临时函数只对当次连接有效。如果退出此连接，此函数无效。
  c.永久函数不管启动多少次hive。都是有效的。没重启一次都会自动加载一次jar包。

hive的远程连接
服务端：mysql，hive
客户端：jdbc代码
步骤：
1.xxx/hive/bin/ hiveserver2 开启远程hive的服务端
2.新打开一个窗口 xxx/hive/bin/beeline 进入远程连接的客户端
3.beeline>!connect [Driver]
!connect jdbc:hive2://node000:10000/default bduser rootroot
注意：
hive本身没有username和password，所以这两个选项可以不写。
如果写的话，就是当前hive所在节点的用户名和密码。
4.正常写类sql语句。

jdbc ：java database connector
练习：
1.测试最简单的jdbc
2.做简单的数据分析
3.向hive中写数据，从hive的student表中读数据并且写入到Windows的mysql中
步骤：
1.创建一个package 写当前这个业务
2.创建分层
controller 控制层 相当于一个调度器
service 服务层 封装好的具体方法的实现
dao 数据库层 所有与数据库交互的 jdbc
util 工具类 公共的方法
bean 实体类 与表对应的实体类