文件数据上传的实验二:
- 创建一个有分隔符信息的表格
create table stu02(
id int,
name string,
age int
)
row format delimited fields terminated by ‘,’;
内部表:通过create table 表名的方法,生成的表格,表格的文件夹的位置在/user/hive/warehouse/数据库名.db,并且表格的文件夹是自动生成的。
- 先有一个数据文件
- 建立一个与文件格式对应的表格
- 将文件导入到表格中
外部表:
上面的所有表格,都称为数据库的内部表,接下来的是数据库的外部表。
外部表的创建和文件关系映射的添加:
- 首先,在hdfs里面,有一个文件夹,这个文件夹里面存放的都是同一个内容和格式的文件
hadoop fs -mkdir /logs_table
hadoop fs -put log1.txt /logs_table
2020-12-11,192.168.2.1,登录
2020-12-11,192.168.2.1,查询华为
2020-12-11,192.168.2.6,添加杯子到购物车
hadoop fs -put log2.txt /logs_table
2020-12-12,192.168.2.7,退出登录
2020-12-12,192.168.2.9,注册账号 - 建立一个和文件格式相同的表格,用这个表格去找文件夹所在的位置
create external table logs(
opertetime string,
ipaddr string,
operation string
)row format delimited fields terminated by ‘,’
location ‘/logs_table’; - 查看表格的内容
select * from logs;
create external table 表名(
列名 数据类型
)
row format delimited fields terminated by ‘列的分隔符’
location ‘hdfs文件夹的位置’;
内部表和外部表的区别是什么?***
什么是内部表?
直接使用create table创建的表格,表格会自动的在数据库所在的文件夹中,创建一个自己表格名字的文件夹。
删除表的时候,表格的文件夹和数据会一起被删除。
什么是外部表?
使用create external table创建的表格,自己不会生成文件夹,需要使用location去指定一个文件夹给它。
删除表的时候,表格的文件夹和数据会被全部保留下来,insert into的数据也会一起保留下来。
什么时候用内部表,什么时候用外部表?
- 保存日志信息或者数据的埋点信息的时候,使用外部表;
- 保存sql语句的计算结果的时候,使用内部表;
- 其他的业务数据,可以用外部表也可以用内部表,没有具体限制。
分区表:
分区是以文件夹的方式而存在的,在表格中这个分区字段会变成一个虚拟的列,可以直接被用来查询。
目的:分区可以加快表格查询的速度,因为将数据按不同类型分开保存,搜索的时候可以减少检索的数据量;在做表格数据抽取的时候,需要分区管理数据,因为在hive中无法update和delete数据,所以需要通过直接删除分区的方式删除数据。
create [external] table 表名(
列名 数据类型
)
partitioned by (新的字段名 数据类型)
row format delimited fields terminated by ‘列的分隔符’
[location ‘路径’];
两种静态分区的添加:
第一种分区添加的方法,直接加载文件到某个分区中
- 创建分区的表格
create table stu_info(
id int,
name string,
sex string,
age int
)
partitioned by (class string)
row format delimited fields terminated by ‘,’; - 将linux的文件上传到hdfs
hadoop fs -put user3.txt /datas
2.用load的方法,加载文件的内容
load data inpath ‘hdfs文件的位置和名字’ into table 库名.表名 partition(分区字段=‘分区值’);
load data inpath ‘/datas/1.txt’ into table bigdata.stu_info partition(class=‘one’);
load data inpath ‘/datas/2.txt’ into table bigdata.stu_info partition(class=‘two’);
class_one
1001,lilei,男,18
1002,lucy,女,16
1003,tom,男,17
1004,jack,男,18
1005,eve,女,17
1006,allen,男,18
class_two
1007,miller,男,19
1008,flower,女,16
1009,adam,男,17
1010,toly,男,16
1011,steven,男,17
1012,bob,男,18
1013,lucky,女,17
- 查看分区表的结构
第二种分区添加的方法,直接加载表格的内容到某个分区中,通过复制表格内容的方式添加分区的信息
- 先准备a表和b表,分别是两个班的学生数据
create table a(
id int,
name string,
sex string,
age int
)row format delimited fields terminated by ‘,’;
load data inpath ‘/datas/1.txt’ into table bigdata.a;
create table b(
id int,
name string,
sex string,
age int
)row format delimited fields terminated by ‘,’;
load data inpath ‘/datas/2.txt’ into table bigdata.b;
-
再准备一个汇总学生信息的分区表 c
create table c(
id int,
name string,
sex string,
age int
)
partitioned by (class string)
row format delimited fields terminated by ‘,’; -
将a b两个表格的数据,添加到分区表 c中
insert overwrite table 表名 partition(分区字段=‘分区值’)
select 查询语句;
insert overwrite table c partition(class=‘one’)
select * from a;
insert overwrite table c partition(class=‘two’)
select * from b;
给一个数据量很大的表格,重新复制为一个有分区的表格。
create table stu_info2(
id int,
name string,
sex string,
age int
)row format delimited fields terminated by ‘,’;
准备另一个有分区的相同结构的表格:
create table stu_info2_p(
id int,
name string,
age int
)
partitioned by (sex string)
row format delimited fields terminated by ‘,’;
复制表格的信息,复制的同时添加分区:
hive (bigdata)> insert overwrite table stu_info2_p partition(sex=‘man’)
> select id,name,age from stu_info2 where sex=‘男’
> ;
hive (bigdata)> insert overwrite table stu_info2_p partition(sex=‘woman’)
> select id,name,age from stu_info2 where sex=‘女’
> ;
一种动态分区的添加:效率上比静态分区要慢
-
创建一个分区的表格
create table stu_info3(
id int,
name string,
sex string
)
partitioned by (age int)
row format delimited fields terminated by ‘,’; -
打开动态分区的开关
打开动态分区的设置
set hive.exec.dynamic.partition=true;
将动态分区的设置调整为非严格模式(分区值不用自己手动指定)
set hive.exec.dynamic.partition.mode=nonstrict; -
进行表格动态分区的添加
insert overwrite table stu_info3 partition(age)
select id,name,sex,age from stu_info2;
练习:有一张表,表格内容如下 id name birth
1001,lilei,1998-01-01
1002,hanmeimei,1999-02-09
1003,mike,1998-01-09
1004,lucy,1999-06-07
1005,tom,1998-12-16
将linux的文件上传到hdfs
hadoop fs -put user2.txt /datas
将文件的内容映射到表格中
load data inpath ‘/datas/user2.txt’ into table bigdata.stu03;
有个分区表,字段是 id name birth year(year是分区字段)
需要按照year进行动态分区的划分
create table birth_new(
id int,
name string,
birth string
)
partitioned by (year string)
row format delimited fields terminated by ‘,’;
复制数据,并且添加动态分区
insert overwrite table birth_new partition(year)
select id,name,birth,substr(birth,1,4) from birth_old;
静态分区和动态分区的区别是什么?
静态分区的分区值是自己设置的,动态分区的分区值是通过select查询出来的某个列的值,动态分区需要打开动态分区的开关和打开nostrict非严格模式的开关,效率比静态分区要低。
静态可以通过load data和insert overwrite的方式添加数据,动态只能通过insert overwrite添加。
外部分区表的添加和创建:
-
在hdfs系统里面,已经存在了一个日志的文件夹,里面根据每一天的数据,已经创建好了每天的文件夹,先设置外部表对应文件夹的结构
[root@localhost zx]# hadoop fs -mkdir /logs
[root@localhost zx]# hadoop fs -mkdir /logs/20201110
[root@localhost zx]# hadoop fs -mkdir /logs/20201111
[root@localhost zx]# hadoop fs -mkdir /logs/20201112
[root@localhost zx]# hadoop fs -put 2020-11-10.txt /logs/20201110
[root@localhost zx]# hadoop fs -put 2020-11-11.txt /logs/20201111
[root@localhost zx]# hadoop fs -put 2020-11-12.txt /logs/20201112 -
创建一个包含了分区信息的外部表:
create external table ext_logs_p(
pinpai string,
id int,
time string
)
partitioned by (date_time string)
row format delimited fields terminated by ‘,’
location ‘/logs’; -
进行外部表分区的数据挂载,分区的文件夹已经存在了,只是要创建一个分区的结构保存到mysql元数据中,与分区的文件夹进行绑定。
alter table ext_logs_p add partition (date_time=‘20201110’)
location ‘/logs/20201110’;
alter table ext_logs_p add partition (date_time=‘20201111’)
location ‘/logs/20201111’;
alter table ext_logs_p add partition (date_time=‘20201112’)
location ‘/logs/20201112’;
分区的目的:
更好的细化和管理表格的数据,例如每一天数据都用一个分区的文件夹进行管理;
查询分区字段的时候,可以有效的提高查询效率,因为查询的数据量变少了。
hive数据库中的分桶表:cluster
使用hive数据库中的哈希hash算法,将不同的数据,按照算法的结果保存在不同的文件中。
创建一个分桶表:
create table 表名(
列名 数据类型
)
clustered by (表中已经存在的字段名) into 数量 buckets
row format delimited fields terminated by ‘,’;
如果这个列经常要用来做表连接,就设置成分桶的列,两个做表连接的表格,都需要用id来连接,a.id=b.id,那么就给a表和b表做成分桶表,来增加表连接的速度,因为分桶可以减少表连接的笛卡尔积数量。
将数据添加到分桶表中的步骤:
- 打开hive中分桶的开关
set hive.enforce.bucketing=true; - 创建一个分桶表,存储以下的数据
1001,lilei,男,18
1002,lucy,女,16
1003,tom,男,17
1004,jack,男,18
1005,eve,女,17
1006,allen,男,18
1007,miller,男,19
1008,flower,女,16
1009,adam,男,17
1010,toly,男,16
1011,steven,男,17
1012,bob,男,18
1013,lucky,女,17
create table stu_info_c(
id int,
name string,
sex string,
age int
)
clustered by (id) into 4 buckets
row format delimited fields terminated by ‘,’;
-
再复制一个和分桶表相同结构的临时表,只有结构不会有内容
create table stu_info_c_tmp like stu_info_c; -
在临时表中添加表格的所有的数据
hadoop fs -put stu_c.txt /datas
load data inpath ‘/datas/stu_c.txt’ into table bigdata.stu_info_c_tmp; -
使用insert复制表格的内容到分桶表中,复制的同时进行数据的分桶,cluster by (id)默认是按照id升序排序的
insert overwrite table stu_info_c
select * from stu_info_c_tmp cluster by (id); -
尝试换一种方法,让id从大到小的进行分桶和排序,distribute by id 按照id分桶,sort by id desc 对id进行降序排序
insert overwrite table stu_info_c
select * from stu_info_c_tmp distribute by id sort by id desc;
distribute by id sort by id asc 等于 cluster by id
order by 和sort by 它们的区别?**
它们都可以用来对列进行排序,但是sort by可以用在分桶中,order by不能用在分桶中。
分桶使用的场景:
分桶在表连接的时候,才会用到,可以加快两张表联合查询的速度,只有当联合表格的分桶数量相等,或者分桶数量是倍数关系的时候,才有加速的效果。
为什么分桶可以让表连接加速?
因为分桶可以让表连接的笛卡尔积数据量变少。
分区和分桶有什么区别?***
分区使用的是表中不存在的新字段,分桶是使用表中已有的字段;
分区是自己指定的规则(分区字段=分区值),分桶是根据Hash算法分配;
分区是建立的文件夹,在文件夹中保存数据,分桶是直接拆分表格为多个小文件;
分区是对分区字段查询的时候加速,分桶只有表连接的时候加速。
对数组的内容进行查询:
select 数组类型字段[序号] from 表名;
select id,hobby[0] from t12;
对映射的内容进行查询:
select 映射类型字段[key名] from 表名;
select id,score[‘english’] from t14;
使用的函数:
获取当前的系统时间 *
from_unixtime(unix_timestamp())
from_unixtime(unix_timestamp(),‘yyyy-MM-dd hh-mm-ss’)
current_timestamp()
数据类型的转换
cast(字段 as 新的数据类型)
cast(age as string)
cast(mobile as char(11))
字符串的拼接
没有| 管道符的拼接方式,但是concat()可以拼接任意字符串
select concat(‘aaa’,‘bb’,‘cc’,1231,45646,‘sdfas’);
聚合函数sum count max min avg,常用的数字函数abs round floor ceil(不能用trunc)、字符串函数concat substr length upper lower initcap、日期函数last_day months_between add_months、分析函数 over(partition by 分组 order by 排序)、排名函数(rank dense_rank row_number)、平移函数(lag lead)都是一样的,空值判断(nvl)
判断函数:没有decode方法,使用if()函数进行替换
select if(age<=17,1,0) from stu02;
if(判断条件, 条件成立的结果, 条件不成立的结果)
多条件的嵌套判断:
select if(age<=16,1,if(age<=17,2,0)) from stu02;
case when的用法和oracle是一模一样的,工作中一般都用case when
尽量少用in进行数据的筛选:在跑程序的时候会报错,而且in只能放在where后面。
表格的联合查询,只支持等值的连接查询:
select * from a join b on a.id=b.id;
不能写a.id>b.id,!= >= <= <
但是可以使用别的方法来操作:
select * from a join b on 1=1 where a.id>a.id;
with as语句:oracle sqlserver hive三个数据库通用的语句
with 别名 as (select 语句),
别名2 as (select 语句),
…
select 语句;
with a as (select * from stu02 where age<=17),
b as (select * from stu04 where substr(name,1,1)=‘l’)
select * from a join b on a.id=b.id;
等同于
select * from
(select * from stu02 where age<=17) a
join
(select * from stu04 where substr(name,1,1)=‘l’) b
on a.id=b.id;
hive数据库的数据倾斜:****
表现:在日志中,reduce的百分比卡在了99%的位置不动了,可能几分钟可以跑完的数据,半个多小时了还没结束。大部分需要计算的数据量集中在少部分运行的机器上。
- 大表和小表进行表连接的时候
第一种方法:拆分大表
例如a表有1000万数据,b表只有200万,那么可以:
select * from
(select * from a limit 5000000) a1 join b on a1.id=b.id
unoin all
select * from
(select * from a limit 5000000,5000000) a2 join b on a2.id=b.id;
第二种方法:使用mapjoin优化器 select /*+ mapjoin(表名) /
1.先打开mapjoin表连接的开关
set hive.auto.convert.join=true;
2.使用优化器进行表连接 mapjoin(小表名)
select /+ mapjoin(b) / from a join b on a.id=b.id;
将小的表格的所有数据,通过mapjoin优化器,全部读取到内存中,然后用大表的数据去匹配内存中的小表数据,达到查询加速的效果。
-
表格中有大量空值(特别是在字符串的列上)的时候
使用字符串+随机值对空值进行填充
nvl(列名,concat(‘rand’,cast(rand()*10000000 as int))) -
不合理的sql语句,在hive里面,尽量的不要使用distinct,使用group by进行去重
例如count(distinct 列名)
去重统计使用:select count(列名) from (select 列名 from 表名 group by 列名) a; -
使用一些常见的开关来优化
当数据量比较小的时候,使用本地模式进行运算(当计算的数据量超过20M(20万行左右)以后,就不要用了):
set hive.exec.mode.local.auto=true;
分组聚合计算的优化:
set hive.map.aggr=true;
负载均衡的优化:
set hive.groupby.skewindata=true;
hadoop面试的时候会遇到的一些面试题:
hadoop 平台,你用过和知道哪些不同的组件?
离线的部分:sqoop yarn hdfs mapreduce hive
离线的部分:sqoop(进行数据库表对表的数据迁移) yarn(进行数据库表对表的数据迁移) hdfs(分布式的文件存储管理系统) mapreduce(大数据计算引擎) hive(编辑sql的数据库)
实时的部分:flume(日志信息的收集) kafka(消息队列的处理) hbase(一种列式存储的数据库) spark(基于内存的计算引擎) flink(流式处理的计算引擎)
hadoop里面,hdfs数据块是多大一块?
128M
数据默认保存几份?
3份
hdfs里面由哪几个组件构成?
datanode namenode secondarynamenode
hadoop的基础服务有哪几个?
datanode namenode secondarynamenode jps resourcemanager nodemanager
hdfs里面,常用的操作命令有哪些?上传、下载、合并等等。
hadoop fs -put
hadoop fs -get
hadoop fs -appendToFile
加载数据到数据库的表,使用什么方法?
load data inpath ‘路径’ into table 库名.表名 partition(分区字段=分区值);
如果现在,做数据的增量抽取,如何在重复抽取的过程中,避免出现重复的数据?
增量抽取的数据,会放在每天的分区里面中,然后通过删除今天的分区达到避免重复数据出现的效果。
如何删除分区呢?
alter table 表名 drop partition(分区字段=’分区值‘);
load data inpath ‘路径’ into table 库名.表名 partition(分区字段=分区值);
insert overwrite table 表名 partition(分区字段=分区值) select * from 另一个表名;
如果某个数据有问题,如果更新这个数据?
-get 这个文件,修改文件本身,删除hdfs原文件,在-put上传上去
1、**下载文件:
hadoop fs -get hdfs文件名字
hadoop fs -get /a.txt 将hdfs上面的根目录中的a.txt下载到linux本地当前文件夹中
2、删除东西:
hadoop fs -rm -r 文件夹或者文件的位置和名字
**3、上传文件:
hadoop fs -put linux上面的文件位置和名字 hdfs的位置
hadoop fs -put a.txt / 将本地的a.txt上传到hdfs的根目录
4、加载数据到数据库的表,
load data inpath ‘路径’ into table 库名.表名 partition(分区字段=分区值);
内部表和外部表的区别?
动态分区和静态分区的区别?
分区和分桶的区别?
怎么进行数据的抽取?
数据倾斜的表现是什么?
发生了数据倾斜,怎么办?
小文件过多的优化:
小文件从哪里来的?
分区分桶会得到很多的小文件;
数据源本身就有很多小文件;
reduce数量过多的时候。
小文件过多的时候,为什么会影响到服务器性能?
影响:
每一个文件,都需要一个jvm的进程去处理和执行,会造成资源的浪费,影响执行的效率;
hdfs存储过多小文件,会造成硬盘资源的浪费。
怎么调整和优化?
1.不要使用textfile存储数据
2.通过开关来调节:
控制每个map端拆分数据的最大值大小:
set mapred.max.split.size=xxx;
控制每一个处理数据节点的处理数据大小:
set mapred.min.split.size.per.node=xxx;
设置的合并的map端输出的数据:
set hive.merge.mapfiles=true;
设置的合并的reduce端输出的数据:
set hive.merge.mapredfiles=true;
设置合并后的数据的大小:
set hive.merge.size.per.task=xxx;
需要被合并的文件大小:
set hive.merge.smallfiles.avgsize=xxx;
1、UDF:用户定义(普通)函数,只对单行数值产生作用;
继承UDF类,添加方法 evaluate()
2、UDAF:User- Defined Aggregation Funcation;用户定义聚合函数,可对多行数据产生作用;等同与SQL中常用的SUM(),AVG(),也是聚合函数;
另一种涉及两个类:AbstractGenericUDAFResolver、GenericUDAFEvaluator;
继承UDAFResolver类,重写 getEvaluator() 方法;
继承GenericUDAFEvaluator类,生成实例给getEvaluator();
在GenericUDAFEvaluator类中,重写init()、iterate()、terminatePartial()、merge()、terminate()方法;
3、UDTF:User-Defined Table-Generating Functions,用户定义表生成函数,用来解决输入一行输出多行;
继承GenericUDTF类,重写initialize(返回输出行信息:列个数,类型), process, close三方法;
hive怎么解析SQL
将sgL转换为抽象语法树AST tree,然后遍历 ASTtree,查询 gueryBlock然后遍历 gueryBlock,翻译成执行操作树,然后逻辑层优化器进行操作树优化,减少 shuffle 数据量,然后遍历操作树,翻译成mapReduce ,任务。然后物理层优化器进行,mapreduce 任务的转化,生成最终计划
mr的shuffle过程有几种
在MapReduce中,Shuffle过程主要完成数据的分区和排序,使得相同键的数据被发送到同一个Reducer进行处理。在Shuffle过程中,有以下几种常见的方式:
Sort-based Shuffle:这是最常用的Shuffle方式。它的工作原理是,首先在Map阶段将输出的键值对按照键进行排序,然后将排序后的数据按照Reducer的数量进行分区,每个分区对应一个Reducer。在Reducer阶段,每个Reducer会接收到一个或多个分区的数据,并再次按照键进行排序,以便进行后续的数据处理。
Hash-based Shuffle:这种Shuffle方式将键值对根据键的哈希值进行分区。在Map阶段,每个键值对会根据哈希函数计算出一个分区编号,然后将数据发送给对应的Reducer进行处理。在Reducer阶段,只需要对接收到的数据进行排序即可。
Grouping Shuffle:这种Shuffle方式是在Sort-based Shuffle的基础上进行的改进,用于对具有相同键的数据进行分组。在Map阶段,除了按键排序之外,还会根据键分组,将相同键的数据放在一起。在Reducer阶段,每个Reducer接收到的数据已经按照键排序并分组,方便进行后续的聚合操作。
在实际使用中,Sort-based Shuffle是最常用的方式,因为它在处理大规模数据时具有较好的性能和扩展性。而Hash-based Shuffle主要适用于数据量较小且键分布均匀的情况。Grouping Shuffle则使用于需要对相同键的数据进行聚合操作的场景。根据具体的需求和数据特点,选择合适的Shuffle方式可以提高MapReduce的性能和效率。
在Hive中,有以下几种常见的Join方式:
Map Join:在Hive中,当一个小表能够全部存放在内存的时候,可以使用Map Join来实现关联操作。这种方式会将小表加载到内存中,并构建一个哈希表,然后通过Map阶段将大表数据和小表哈希表进行连接获取结果。使用Map Join可以大幅减少关联过程中的磁盘IO,提高查询性能。
Broadcast Join:当一个小表无法完全放入内存中时,但是小表相对于大表仍然较小,可以采用Broadcast Join。在这种方式下,会将小表的数据复制到每个计算节点上,然后通过Map阶段将大表数据和小表数据进行关联操作。
Sort Merge Join:当两个表的大小都较大无法全部放入内存,并且两个表都已经按照Join键进行排序时,可以使用Sort Merge Join。在这种方式下,需要先对两个表按照Join键进行排序,然后通过归并的方式将两个表进行关联操作。
Bucket Join:当两个表都已经以相同的Join键进行分桶存储时,可以使用Bucket Join。在这种方式下,Hive可以将相同桶号的数据对应的分片进行关联操作,从而提高查询效率。
以上是Hive中常用的几种Join方式,选择合适的Join方式可以根据数据集大小、内存大小以及数据分布情况等因素来进行判断和选择
MapReduce计算引擎: hive on mr
大数据的分布式计算引擎
数据的计算,分成了map 和 reduce两个部分:
假如现在要去计算一个sql语句:select deptno,sum(sal) from emp group by deptno;
- 将emp表的数据,拆分成N份,分别给到不同的机器(map端)
- 每个不同的机器,单独的对自己的数据进行分组计算(reduce端)
- 每个机器都计算完成之后,再进行数据的汇总(reduce端)
表格文件保存的几种常见的格式:
-
text:hive默认的表格保存格式,可以通过load data来加载数据
行存储的方式进行数据存储,占用空间,并且读取速度比较慢 -
sequence:序列格式,占用的空间比text实际要大
也是行存储的方式,使用key value键值对的方式存储数据。 -
rc:
facebook创建的一种文件存储格式,列存储的方式。使用懒加载存储和管理数据(对每一行的数据单独的进行数据压缩,如果要读取,只读对应的数据,只解压对应的数据),查询速度比较快。 -
orc:
列存储的方式,是rc的优化版,同样有懒加载的特点,优化在文件的压缩和存储上,orc是项目组中使用最多的文件存储格式