目录
数据结构
结构化的数据:字段个数一定,字段之间的分隔符一定
半结构化的数据:例如xml,json等
非结构化的数据:没有任何规律格式的数据
基本概念
本质:将sql装换成MapReduce任务进行运算,可以理解为hive就是一个MapReduce的客户端
特点
- 可扩展:Hive可以自由的扩展集群的规模,一般情况下不需要重启服务
- 延展性:Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。
- 容错:良好的容错性,节点出现问题SQL仍可完成执行
支持的数据类型
- 整型
- TINYINT (1字节整数)
- SMALLINT (2字节整数)
- INT/INTEGER (4字节整数)
- BIGINT (8字节整数)
- 浮点型
- FLOAT (4字节浮点数)
- DOUBLE (8字节双精度浮点数)
- 字符串类型
- string(不设定长度)
- VARCHAR (字符串1-65355长度,超长截断)
- CHAR (字符串,最大长度255)
- 时间类型
- TIMESTAMP (时间戳) (包含年月日时分秒的一种封装)
- DATE (日期)(只包含年月日)
- BOOLEAN(布尔类型):true false
- BINARY (二进制)
- 复合类型
- array数组类型 字段类型必须相同 ARRAY<data_type>
- Maps:和Java中的Map相同,即存储K-V对的,键的类型必须是原子的,值可以是任意类型,同一键映射的值得类型必须相同
- Structs:一组由任意数据类型组成的结构:一组命名的字段,字段类型可以不同
解析器
编译器:主要将我们的sql语句进行编译成一个MR的任务
优化器:主要是对我们的sql语句进行优化
执行器:提交mr的任务,进行执行
元数据库
hive的元数据 表与hdfs数据之间的映射关系 默认使用的是derby,一般都会改成mysql存储
交互方式
- bin/hive 启动进入,默认在使用derby存储元数据,在存储在启动目录下
- 启动服务端hiveserver2需要hive整合mysql整合,将元数据存储在mysql
- bin/hive --service hiveserver2
- beeline> !connect jdbc:hive2://node03.hadoop.com:10000
- hive整合mysql整合
- 修改hive-site.xml添加mysql数据库
- bin/hive --service metastore 启动hive的metastore 才能访问
- hive -e直接执行sql
- bin/hive -e "use myhive;select * from test;"
- bin/hive -f hive.sql 通过 -f 指定我们需要执行的sql脚本文件
基本操作
创建库表
- 创建库
- create database id not exists xxx;
- 创建表:建表的存储位置在默认位置(默认位置在配置文件中)。可以指定hdfs的位置
- create table if not exists stu2(id int,name String) row format delimited fields terminated by '\t' stored as textfile location '/user/test';创建stu2表存储为文本格式,在hdfs上的/user/test/目录下面
-
create [external] table if not exists table_name(col_name data_type [comment col_]) external指名是外部表,当表删除时不会删除hdfs上的文件 comment table_comment 表的描述 只能用英文或拼音 row format delimited fields terminated by "\001" 指定字段之间的分隔符 partitioned by (col_name data_type[comment col_colment],...) 分区:hive的分区对应hdfs是分文件夹 分桶: clustered by (col_name,...)分桶: 按照字段进行划分文件,粒度更细 LIKE 允许用户复制现有的表结构,但是不复制数据 stored as SEQUENCEFILE|TEXTFILE|RCFILE 指定数据存储的格式 LOCATION hdfs_path 指定我们这个表在hdfs的哪一个位置 sorted by (col_name asc|desc,...)
- as 创建:将视图表的内容和表结构复制都过来create table stu3 as select * from stu2;
- like中赋值表结构,没有内容:create table stu4 like stu2;
数据导入
- hdfs 文件系统加载数据
- 加载表load data inpath 'hivedatas/techer.csv' into table teacher;
- 加载指定分区:locad data inpath '/export/servers/xx.txt' into table score partition (month='201803')
- 本地导入
- 加载到表: load data local inpath '/export/servers/hivedatas/students.csv' into table student
- 加载到分区: locad data local inpath '/export/servers/xx.txt' into table score partition (month='201803')
- insert插入数据
- insert into table score3 partition(month ='201807') values ('001','002','100');
- 注意: 每执行一次insert 生成一个小文件,
数据的导出
- export导出与import 导入 hive表数据
- export table techer to '/export/techer';
- import table techer2 from '/export/techer';
- 将查询结果数据导出到本地:
- insert overwrite local directory '/export/xxx' select * from score;
- 将查询结果数据导出到hdfs
- insert overwrite directory '/export/xxx' select * from score;
- 数据导出到并指定格式分隔符
- insert overwrite directory '/export/xxx' row format delimited fields terminated by '\t' select * from score;
- hive的外部表指定hdfs文件,可以hdfs命令直接把赋值到本地
- hdfs dfs -get /dfsfile /localname
常用sql函数
- 内置函数
- 查看自带函数 show functions
- 显示指定函数用法 desc dunction 函数名
- 详细显示函数用法 desc function extended 函数名
- 自定义函数
- 种类:按照参数输入输出的数量分为三种
- udf 一进一出
- UDAF 多进一出
- UDTF 一进多出
- 用法,
- 要继承UDF,重写evaluate方法 ,打成jar包上传到lib目录下
- evaluate方法支持重载,udf必须有返回类型,可以为null不能void,类型推荐Text/LongWritable等类型
- 添加jar包,命令add jar /export/servers/hive-1.1.0-cdh5.14.0/lib/XXX.jar
- 创建函数名与自定义函数的关联 create funname function tolowercase as 'cn.itcast.udf.ItcastUDF'
- 用法和其他函数相同使用和 select funname ('abc');
- 要继承UDF,重写evaluate方法 ,打成jar包上传到lib目录下
- 种类:按照参数输入输出的数量分为三种
编程 将参数转为大写字母
1.继承org.apache.hadoop.hive.ql.UDF
public class ItcastUDF extends UDF {
public Text evaluate(final Text s) {
if (null == s) {
return null;
}
//返回大写字母
return new Text(s.toString().toUpperCase());
}
}
hive数据压缩,
hadoop一样,推荐snappy
- hadoop开启压缩mapred-site.xml
- map输出压缩
- 开启hive中间传输数据压缩: set hive.exec.compress.intermediate=true;
- map输出压缩:set mapreduce.map.output.compress=true;
- map输出数据的压缩方式:set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
- reduce输出的数据进行压缩
- 开启hive最终输出数据压缩:set hive.exec.compress.output=true;
- 开启mapreduce最终输出数据: set mapreduce.output.fileoutputformat.compress=true;
- 设置mapreduce最终输出压缩方式:set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
- 设置mapreduce最终数据输出压缩为块压缩:set mapreduce.output.fileoutputformat.compress.type=BLOCK;
- map输出压缩
常用语法 操作
- 排序
- 全局排序order by :只有一个reducer, desc降序 asc升序(默认)
- 内部排序sort by :在数据进入reducer前完成排序当,当mapred.reduce.tasks>1全局无序
- 分区排序distribute by:distribute by (字段) 根据指定的字段将数据分到不同的reducer,分发算法是hash散列,和sort by 结合使用
- 分桶排序cluster by :如果分区字段和sort by 字段相同,cluster by 字段 = distribute by (字段)+sort by 字段,但只能是倒叙排列
- like 和 relike
- relike基于java的正则表达式接口实现,模糊查询功能更强大,例如成绩中含9的所有成绩数据
- select * from score where s_score rlike '[9]';
- like 模糊查询,下划线_匹配一个字符,%可以匹配任意多个字符例如 第二个数值为9的所有成绩数据
- select * from score where s_score like '_9%';
- relike基于java的正则表达式接口实现,模糊查询功能更强大,例如成绩中含9的所有成绩数据
- haveing和where:区别:where 对表的列发挥作用,条件是表的列,having针对查询结果中的列
- where作用在 分组(group by)和聚集(sum等)计算之前,
- having作用在 分组(group by)之后,对分组后计算的数据进行过滤
- 例:select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
- join 关联:语法同mysql相同
- 对表关联执行顺序:hive一般为每一个join创建一个job,执行顺序是从左到右,但是如果多个表join的条件相同,之创建一个job, 多表进行join时,一般要把大表放到最后面,提上效率
- 例如select * from techer t left join course c on t.t_id = c.t_id left join score s on s.c_id = c.c_id left join student stu on s.s_id = stu.s_id;
- 对表关联执行顺序:hive一般为每一个join创建一个job,执行顺序是从左到右,但是如果多个表join的条件相同,之创建一个job, 多表进行join时,一般要把大表放到最后面,提上效率
- 左半关联 left semi join :效果和inner join 差不多,但是效率会高很多,但不会展示右表的数据
- left semi join 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句都不能引用右表的字段
- 查询结果只会出现左表的数据,
分区和分桶
- 分区:对于hdfs是一个子文件夹,对于表是一个字段和值
- show partitions score;查看分区
- 添加分区:alter table score add partition(month='201804' partition(month ='201803'))
- 删除分区:alter table score drop partition(month = '201806');
- 分桶:
- 分桶的实质就是对分桶的字段做了hash 然后存放到对应文件中;
- 开启hive的桶表功能 set hive.enforce.bucketing=true;
- 语法 关键字:clustered by (col_name) into xx buckets
- 创建分桶表
- create table course(c_id string,c_name string ,t_id String) clustered by (c_id) into 3 buckeds row format delimited fields terminated by '\t'
- 加载数据只能通过查询中间表结果集的方式加载
- select overwrite table course select * from score cluster by (c_id);
数据存储格式
压缩方式 snappy
- 行式存储:
- textFile(默认)
- sequenceFile
- 列示存储
- ORC
- 组成是Stripe 每个stage包含三部分
- indexData:索引数据
- rowdata:真正的数据
- stripFooter:元数据信息
- 组成是Stripe 每个stage包含三部分
- parquet
- ORC
- 存储空间占用大小比较orc<parquet<textfile
- 查询语句运行时间大小比较:orc<parquet<textfile
建立表与文件的关联方式
方式一:新建分区表指定关联的hdfs文件,msck repair table score
- 数据准备 hdfs dfs -mkdir -p /scoredatas/month=201806
- hdfs dfs -put score.csv /scoredatas/month=201806/
- 创建外部分区表指定存放目录
create external table ecore4(s_id String,c_id string,s_score int) partitioned by month string) row format delimited fields terminated by '\t' localtion '/scoredatas' - 修复表: msck repair table score
方式二:创建完表后手动执行添加分区,前提是hdfs文件就是按照分区的方式存放的