hadoop数据匹配及分析_Hadoop数据分析之Hive

“ Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据映射为一张表。并提供类SQL查询功能。其本质是将SQL转换为MapReduce的任务进行运算。”

01

—

Hive：是什么？

• Hive与数据库的区别

• Hive 具有 SQL 数据库的外表，但应用场景完全不同。

• Hive 只适合用来做海量离线数据统计分析，也就是数据仓库

优缺点

• 优点

• • 操作接口采用类SQL语法，提供快速开发的能力(简单、容易上手)。

  • 避免了去写MapReduce，减少开发人员的学习成本。

  • Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

• 缺点

• • Hive 不支持记录级别的增删改操作

  • Hive 的查询延迟很严重

  • • hadoop jar  xxxx.jar  xxx.class /input /output

    • • 进行任务的划分，然后进行计算资源的申请

      • map 0%  reduce 0%

      • map 10%  reduce 0%

  • Hive 不支持事务

02

—

Hive：架构原理

• 用户接口：Client

# CLI(hive shell)、JDBC/ODBC(java访问hive)、WEBUI(浏览器访问hive)# 第一种 hive Clientbin/hive# 第二种nohup  bin/hive --service hiveserver2  &# bin/beelinebeeline> !connect jdbc:hive2://node03:10000  eg:sql    bin/hive -e "show databases"    bin/hive -f hive.sql  # 执行文件

• 元数据：Metastore

• 元数据包括：表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型(是否是外部表)、表的数据所在目录等；• 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore

• Hadoop集群

使用HDFS进行存储，使用MapReduce进行计算。

• Driver：驱动器

• 解析器(SQL Parser)      1. 将SQL字符串转换成抽象语法树AST      2. 对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误• 编译器(Physical Plan)：将AST编译生成逻辑执行计划• 优化器(Query Optimizer)：对逻辑执行计划进行优化• 执行器(Execution)：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说默认就是mapreduce任务

03

—

Hive：数据类型

基本数据类型

类型名称	描述	举例
boolean	true/false	true
tinyint	1字节的有符号整数	1
smallint	2字节的有符号整数	1
int	4字节的有符号整数	1
bigint	8字节的有符号整数	1
float	4字节单精度浮点数	1.0
double	8字节单精度浮点数	1.0
string	字符串(不设长度)	“abc”
varchar	字符串(1-65355长度，超长截断)	“abc”
timestamp	时间戳	1563157873
date	日期	20190715

复合数据类型

类型名称	描述	举例
array	一组有序的字段，字段类型必须相同 array(元素1，元素2)  ：array[0]	Array(1,2,3)
map	一组无序的键值对 map(k1,v1,k2,v2)  ：map['a']	Map(‘a’,1,'b',2)
struct	一组命名的字段, col3 struct : c.a	Struct('a',1,2,0)

03

—

Hive：数据操作

数据库ddl操作

-- 1. 创建数据库hive > create database db_hive;# 或者hive > create database if not exists db_hive;-- 查询数据库  show databases like 'db_hive*';-- 查看数据库详情desc database db_hive;-- 显示数据库详细信息desc database extended db_hive;-- 删除为空的数据库hive> drop database db_hive;-- 如果删除的数据库不存在，最好采用if exists 判断数据库是否存在hive> drop database if exists db_hive;-- #如果数据库中有表存在，这里需要使用cascade强制删除数据库hive> drop database if exists db_hive cascade;

表DDL操作

• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

-- 通过AS 查询语句完成建表：将子查询的结果存在新表里，有数据 create table if not exists myhive.stu1 as select id, name from stu;-- 根据已经存在的表结构创建表create table if not exists myhive.stu2 like stu;-- 查询表的类型desc formatted myhive.stu;-- eg:external 加上为外部表create  [external] table if not exists myhive.stu3(id int ,name string)row format delimited fields terminated by '\t' stored as textfile location       '/user/stu2';-- 加载数据 [local]：加上代表本地路径，不加代表hdfs路径load data [local] inpath '/kfly/install/hivedatas/teacher.csv' into table myhive.teacher;-- 内部表转换为外部表alter table stu set tblproperties('EXTERNAL'='TRUE');-- 注意：内部表由于删除表的时候会同步删除HDFS的数据文件，所以确定如果一个表仅仅是你独占使用，其他人不适用的时候就可以创建内部表，如果一个表的文件数据，其他人也要使用，那么就创建外部表-- 一般外部表都是用在数据仓库的ODS层-- 内部表都是用在数据仓库的DW层

hive的分区表

-- 创建分区表create table score(s_id string,c_id string, s_score int) partitioned by (month string) row -format delimited fields terminated by '\t';-- 创建一个表带多个分区create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';-- 加载数据到分区表load data  local inpath '/kfly/install/hivedatas/score.csv' into table score partition  (month='201806');-- 展示分区show  partitions  score;-- 添加一个 / 多个分区alter table score add partition(month='201805');alter table score add partition(month='201804') partition(month = '201803');-- 删除分区alter table score drop partition(month = '201806');-- 进行表的修复,建立我们表与我们数据文件之间的一个关系映射，本地文件数据与元数据之间不同步时执行msck  repair   table  score4;

hive分桶表

• 分桶是相对分区进行更细粒度的划分。

• 分桶将整个数据内容安装某列属性值取hash值进行区分，具有相同hash值的数据进入到同一个文件中

  • 比如按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。

    • 取模结果为0的数据记录存放到一个文件

    • 取模结果为1的数据记录存放到一个文件

    • 取模结果为2的数据记录存放到一个文件

    • 取模结果为3的数据记录存放到一个文件

• 作用

  • 取样sampling更高效。没有分区的话需要扫描整个数据集。

  • 提升某些查询操作效率，例如map side join

  • 在创建分桶表之前要执行的命令

• set hive.enforce.bucketing=true;  # 开启对分桶表的支持• set mapreduce.job.reduces=4;      # 设置与桶相同的reduce个数(默认只有一个reduce)

-- 进入hive客户端然后执行以下命令use myhive;set mapreduce.job.reduces=4;  set hive.enforce.bucketing=true; --分桶表create table myhive.user_buckets_demo(id int, name string)clustered by(id) into 4 buckets row format delimited fields terminated by '\t';-- 加载数据到普通表 user_demo 中load data local inpath '/user_bucket.txt'  overwrite into table user_demo; -- 在hive客户端当中加载数据load data local inpath '/user_bucket.txt' into table user_demo;-- 加载数据到桶表user_buckets_demo中insert into table user_buckets_demo select * from user_demo;

• 抽样查询桶表的数据

• • tablesample抽样语句，语法：tablesample(bucket  x  out  of  y)

  • • x表示从第几个桶开始取数据(x 为start y为步长)

    • y表示桶数的倍数，一共需要从桶数/y个桶中取数据

select * from user_buckets_demo tablesample(bucket 1 out of 2)-- 需要的总桶数=4/2=2个-- 先从第1个桶中取出数据-- 再从第1+2=3个桶中取出数据

Hive修改表结构

-- 修改表名称语法alter table  old_table_name  rename to  new_table_name;alter table stu3 rename to stu4;-- 增加列alter table stu4 add columns(address string);-- 修改列alter table stu4 change column address address_id int;

数据导入

#-- 数据导入load data [local] inpath 'dataPath' overwrite | into table student [partition (partcol1=val1,…)]; insert overwrite table score5 partition(month = '201806') select s_id,c_id,s_score from score;-- 查询语句创建表，并加载数据create table score6 as select * from score;-- 查询语句中创建表并加载数据(as select)create external table score (s_id string,c_id string,s_score int) row format delimited fields terminated by '\t' location '/myscore7';msck repair table score;-- 数据导入导出hive (myhive)> create table teacher2 like teacher;hive (myhive)> import table teacher2 from '/kfly/teacher';

数据导出

-- 将查询的结果导出到本地insert overwrite local directory '/kfly/install/hivedatas/stu' select * from stu;-- 将查询的结果格式化导出到本地insert overwrite local directory '/kkb/install/hivedatas/stu2' row format delimited fields terminated by  ',' select * from stu;-- 将查询的结果导出到HDFS上==(没有local)==insert overwrite  directory '/kfly/hivedatas/stu'  row format delimited fields terminated by  ','  select * from stu;-- shell 命令导出hive -e "sql语句" >   filehive -f  sql文件   >    filebin/hive -e 'select * from myhive.stu;' > /kfly/install/hivedatas/student1.txt-- 导出到hdfs上export table  myhive.stu to '/kfly/install/hivedatas/stuexport';

静态、动态分区

-- 静态分区  create table order_partition(order_number string,order_price  double,order_time string)  partitioned BY(month string)  row format delimited fields terminated by '\t';    load data local inpath '/kfly/install/hivedatas/order.txt' overwrite into table order_partition partition(month='2019-03');-- 动态分区--创建普通表create table t_order(    order_number string,    order_price  double,     order_time   string)row format delimited fields terminated by '\t';load data local inpath '/kfly/install/hivedatas/order_partition.txt' overwrite into table t_order;--创建目标分区表create table order_dynamic_partition(    order_number string,    order_price  double    )partitioned BY(order_time string)row format delimited fields terminated by '\t';-- 要想进行动态分区，需要设置参数-- 开启动态分区功能hive> set hive.exec.dynamic.partition=true; -- 设置hive为非严格模式hive> set hive.exec.dynamic.partition.mode=nonstrict; hive> insert into table order_dynamic_partition partition(order_time) select order_number,order_price,order_time from t_order;-- 查看分区bin/hive>  show partitions order_dynamic_partition;

排序

 -- 全局排序 order by   --局部排序，每个reduce内部进行排序 sort by   set mapreduce.job.reduces=3;  -- 分区排序,类似MR中partition，==采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中==。 distribute by -- 当distribute by和sort by字段相同时，可以使用cluster by方式 cluster by

Hive java Api

 <dependency>   <groupId>org.apache.hivegroupId>   <artifactId>hive-execartifactId>   <version>1.1.0-cdh5.14.2version>dependency><dependency>  <groupId>org.apache.hivegroupId>  <artifactId>hive-jdbcartifactId>  <version>1.1.0-cdh5.14.2version>dependency><dependency>  <groupId>org.apache.hivegroupId>  <artifactId>hive-cliartifactId>  <version>1.1.0-cdh5.14.2version>dependency><dependency>  <groupId>org.apache.hadoopgroupId>  <artifactId>hadoop-commonartifactId>  <version>2.6.0-cdh5.14.2version>dependency>

Class.forName("org.apache.hive.jdbc.HiveDriver");//获取数据库连接Connection connection = DriverManager.getConnection(url, "hadoop","");//定义查询的sql语句String sql="select * from stu";PreparedStatement ps = connection.prepareStatement(sql);ResultSet rs = ps.executeQuery();