投稿
  1. 首页
  2. 随笔

HIVE学习

mbti测试 • 2022-12-17 • 随笔 • 阅读 22

HIVE学习,第1张

HIVE学习 HIVE学习 1、基本概念

HIVE是类SQL的语言，它们的查询语言是相似的；但处理的数据规模、数据量不同。

是hadoop的一个客户端，其本质是将HQL转换为MapReduce程序。

hive官方网站

2、查询语句 2.1 基本数据类型

INT、BIGINT、DOUBLE、STRING

2.2 集合数据类型

HIVE有三种复杂数据类型ARRAY、MAP和STRUCT。

相关知识点：

  • DML和DDL、DQL、DCL

    • DDL(data define language)，数据定义语言。包括创建数据库中的对象——表、视图、索引等；

      • 创建：CREATE DATAbase/SCHEMA, TABLE, VIEW, FUNCTION, INDEX；
      • 删除：DROp DATAbase/SCHEMA, TABLE, VIEW, INDEX；
      • 替代：ALTER DATAbase/SCHEMA, TABLE, VIEW
      • 清空：TRUNCATE TABLE；
      • 修复：MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)；
      • 展示：SHOW DATAbaseS/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE；
      • 描述：DESCRIBE DATAbase/SCHEMA, table_name, view_name, materialized_view_name。

    • DML(data managed language)，数据处理语言。包括插入、更新、删除；

      • 导入：Load file to table；
      • 导出：Writing data into thie filesystem from queries；
      • 插入：Inserting data into table from queries/ SQL；
      • 更新：Update；
      • 删除：Delete；
      • 合并：Merge。

    • DQL()，数据查询语言。基本结构由 SELECT、FROM、WEHERE 子句构成查询块；

    • DCL()，数据控制语言。授予或者收回数据库的权限，控制或者 *** 纵事务发生的时间及效果、对数据库进行监视等。

  • 管理表和外部表

管理表（内部表）和外部表，区别是在删除时，管理表是都删除掉，而外部表只是删除元数据，而在HDFS上的数据还是保存的。对于内外部共享使用的数据，要建立外部表，这样在hive不需要使用时可以删除，并且不会影响其他的使用。对于中间表和临时表是建立管理表（内部表）。而且外部表可以防止数据丢失，即使误删了还是可以再建表找回数据的。

管理表和外部表的转换，

-- 注意：语句区分大小写
alter table student2 set tblproperties('EXTERNAL'='TRUE');  -- FALSE是外部表改为管理表

-- 查询表的类型
desc formatted student2;
  • 指定分隔符
create table test(id int,name string);
row format delimited fields terminated by ','; --指定行之间的分隔符

  • 修改表

    • 重命名表
    alter table table_name rename to new_table_name;

    注意：如果new_table_name是已经存在的，则会报错。

    • 增加、修改和删除表分区

      参照后面。

    • 增加、修改和替换列信息

    可以更改列名和列的类型。

    alter table table_name change [column] col_old_name col_new_name column_type;
alter table table_name add|replace columns (col_name data_type,...)
2.3 查询 2.3.1 基本查询 
select ... from ...

  • 算术运算符

    包括加减乘除、取余%、与&、或|、异或^、取反~。

  • 常用函数

    count、max、min、sum、avg

  • where

    where语句后不能使用别名，涉及到语句进行的顺序。

  • 比较运算符

A<=>B     -- 如果A、B都为null，则返回TRUE;如果仅有一边为null,则返回FALSE
A<>B,A!=B -- A或者B为null，则返回NULL；如果A不等于B，则返回TRUR,否则返回FALSE
between   -- 左闭右闭

  • like和rlike

    %代表零个或多个字符（任意字符）

    _代表一个字符

    rlike是正则表达式

2.3.2 分组 
groupby ... having ...

  • where和having的区别：

    1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
    2. where后面不能写分组函数，而having后面可以使用分组函数，即sum、avg等。
    3. having只用于group by分组统计语句。
2.3.3 join语句

  • inner join(join)，内连接

  • left join，左外连接

  • 左连接，left join + where，或者left join + not in和子查询，第二种不推荐耗时较久。

  • right join，右外连接

  • 右连接，

  • full outer join(full join)，满外连接，此时结合nvl(a,b)函数，当a为null时，输出b，反之相同。

  • 多表连接

笛卡尔积

2.3.4 排序

以下四个函数在hive中都有排序和聚集的作用，然而，它们在执行时所启动的MR却各不相同。

  • 全局排序（order by）

ASC，默认升序；DESC，降序

hive中的order by 语句和其他的sql方言中定义是一样的，其会对查询结果集执行一个全局排序，这也就是说会有一个所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。

  • 局部排序（sort by）

hive增加了一个可供选择的方式，也就是sort by ,其实会在每个reduce中对数据（数据是随机分配的，为了防止数据倾斜）进行排序，也就是执行一个局部排序过程。这可以保证每个reduce的输出数据都是有序的(但并非全局有效)。这样就可以提高后面进行的全局排序的效率了。

-- 在sort by之前我们还有配置属性:
set mapreduce.job.reduces=2;//配置两个ruduce,默认一个，不然sort by是没有用处的。
select * from zxz_5 sort by nid,year(ntime) desc ;
  • 分区（distribute by）

distribute by的功能是：distribute by 控制map结果的分发，它会将具有相同字段的map输出分发到一个reduce节点上做处理。即就是，某种情况下，我们需要控制某个特定行到某个reducer中，这种 *** 作一般是为后续可能发生的聚集 *** 作做准备。

select * from table_name z distribute by year(z.ntime) sort by year(z.ntime) asc,z.temperature desc;

特别的，因为distribute by 通常和sort by 一起用，所以当distribute by 遇上 sort by时，distribute by要放在前面，这个不难理解，因为要先通过distribute by 将待处理的数据从map端做分发，这样，sort by 这个擅长局部排序的才能去放开的干活。不然要是没有distribute by的分发，那么sort by 将要处理全部的数据，即全局排序，这不是sort by的活，这样做只能拖慢集群工作效率。

  • cluster by

如果sort by和distribute by中所用的列相同，当是多个列时，也需要排序的顺序也是相同的，可以缩写为cluster by以便同时指定两者所用的列。

from recrds select year,temperature cluster by year;
2.3.5 分区表和分桶表 （1）分区表

分区表就是对应一个HDFS文件系统上的独立的文件夹，该文件夹是该分区所有的数据文件。HIVE中的分区就是分目录，把一个大的数据集根据也去需要分割成小的数据集。在查询时通过where字句中的表达式选择查询所需要的指定的分区(必须有分区字段，才能避免全局扫描)，这样的查询效率会提高很多。

  • 分区
-- partitioned by语句
//创建分区表
create table dept_partition(deptno int,dname string,loc string)
partitioned by (day string)
row format delimited fields terminated by 't';

//加载数据到分区表
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
into table dept_partition
partition(day='20200401');

//查询分区表的数据
select * from dept_partition where day='20200401';
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union 
select * from dept_partition where day='20200403';

//增加分区
-- 创建单个分区
alter table dept_partition add partition(day='20200404');
-- 同时添加多个分区
alter table dept_partition add partition(day='20200405') partition(day='20200406');

//删除分区
--删除单个分区
alter table dept_partition drop partition(day='20200401');
--删除多个分区
alter table dept_partition drop partition(day='20200401'),partition(day='20200402');

//查看分区表有多少分区
show partitions dept_partition;

//查看分区表结构
desc formatted dept_partition;
  • 二级分区

即在建分区表时，partitioned by后包括两个字段。

//创建二级分区表
create table dept_partition2(deptno int,dname string,loc string)
partitoned by(day string,hour string)
row format delimited fields ternimated by 't';

//正常加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='11');

//查询分区数据
select * from dept_partition2 where day='20200401' and hour='11';

以上两种都属于静态分区，需要指定要写入的分区名称。

  • 动态分区（dynamic partition）

静态分区需要手动指定分区名称，当分区非常多时，无法一一指定。此时可以使用动态分区表。 建表语句不变，导入数据的方式改变了。

//设置动态分区所需属性
set hive.exec.dynamic.partition=true; --开启动态分区，必须参数
set hive.exec.dynamic.partition.mode=nonstrict(默认static); --允许所有分区都是动态的，否则必须有静态分区字段，必须参数
set hive.exec.max.dynamic.partitions.pernode=100; --(默认100，表示每个mapper或reducer可以创建的最大动态分区数。需要根据实际数据来设定)
set hive.exec.max.dynamic.partitions=1000;--(默认值，表示所有执行MR的节点上可创建的最大动态分区数)

//创建分区表
create table table_name(id int,name string) partition by (city string);
 
//装载数据并动态以city建立分区
-- 因为table_name只有两个字段，所以查询三个字段时，系统默认将最后一个字段city作为分区名，分区字段也默认  	 也是表中的字段，且依次排在表字段最后面，不是按照字段名称推断分区字段。
insert overwrite table table_name partition(city) select id,name,city from src_table;

//多个分区字段（部分静态分区、部分动态分区）
create table target_table(id int) partitioned by (state string,city string);
-- partition(state="china",city)，表示state为静态分区，city为动态分区，以src_table中的city字段为  	分区名
insert overwrite table target_table partition(state="china",city) select id,city from src_table;
-- state和city均使用动态分区
insert overwrite table target_table partition(state,city) select id,state,city from src_table;

注：分区字段要排在最后。

（2）分桶表(clustered by)

分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式，（不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况）。

数据分桶是将数据集分解为更容易管理的若干部分的另一种技术。分区针对的是数据的存储路径；分桶针对的是数据文件。

优点：方便抽样；提高join效率。

//创建分桶表
create stu_buck(id int,name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by 't';

//查看表结构
desc formatted stu_buck;

//load导入数据
load data inpath '/student.txt' into table stu_buck;

//insert导入数据
insert into table stu_buck select * from student_insert;

分桶规则：hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。ruduce的个数推荐设置为-1，让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于或等于分桶表的桶数。

（3）抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。hive可以通过对表进行抽样来满足这个需求。

语法：tablesample(bucket x out of y)，注意x的值必须小于y的值，否则会报错。y推荐是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例；x表示从哪个bucket开始抽取。

select * from stu_buck tablesample(bucket 1 out of 4 on id);

上面的语句表示从stu_buck表中抽取桶数/4个bucket的数据，当桶数等于4时，即抽取1个bucket的数据。而x=1则表示从第一个桶开始抽取。

2.4 函数 2.4.1 系统内置函数
  • 查看系统自带的函数
show functions;
  • 显示自带的函数的用法
desc function upper;
  • 详细显示自带的函数的用法
desc function extended upper;

UDF：一进一出

UDAF：多进一出

UDTF：一进多出

上面的一和多指的是行数。

2.4.2 常用内置函数
  • 空字段赋值(NVL)
-- 如果员工的comm为null，则用-1代替
select comm,nvl(comm,-1) from emp;
  • case when then else end
select dept_id
    ,sum(case when sex='男' then 1 else 0 end) as maleCnt
    ,sum(case when sex='女' then 1 else 0 end) as femaleCnt
from emp
group by dept_id

-- 如果仅有两种情况，还可以用if else
select dept_id
    ,sum(if(sex='男',1,0)) as maleCnt
    ,sum(if(sex='女',1,0)) as femaleCnt
from emp
group by dept_id
  • 行转列

concat(string a/col,string b/col,...)：返回输入字符串连接后的结果，支持输入任意个字符串。

concat_ws(separator,string1(array),string2,...)：它是一个特殊形式的concat()。第一个参数是剩余参数的分隔符，它可以是与剩余参数一样的字符串。如果分隔符为null，则返回值也为null。分隔符会被加到被连接的字符串之间。输入多行，输出一行，常常结合**group by与collect_set**使用。

select a, b, concat_ws(',' , collect_set(cast(c as string)))
from table group by a,b;

collect_set：有两个作用，第一个是去重，去除group by后的重复元素，第二个是形成一个集合，将group by后属于同一组的第三列集合起来成为一个集合。与concat_ws结合使用就是将这些元素以逗号分隔形成字符串。函数只接受基本数据类型。它的主要作用是将某字段的值进行去重汇总，产生array类型字段。输入多行，输出一行。

collect_list(col)：与collect_set相比，collect_list不会做去重。

  • 列转行

explode(col)：将hive一列中复杂的array或者map结构拆分为多行。

lateral view：用于和split、explode等UDTF一起使用，它能够将一列数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合（和原来的表相关联）。

split(str,regex)：传入字符串和分隔符。

-- 将某一字段全部展开
select explode(split(category,',')) from movie_info;
-- 全部语句
select movie
	,category_name
from movie_name
laternal VIEW explode(split(category,',')) movie_info_tmp as category_name;-- 侧写表
  • 窗口函数（开窗函数）

over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。给每条数据都开窗。

substring(str,开始索引,截取长度)：截取字符串。

current row：当前行

n preceding：往前n行

n following：往后n行

unbounded：起点。unbounded preceding：表示从前面的起点；unbounded following：表示到后面的终点。

lag(col,n,default_val)：往前第n行数据，缺失时用default_val替代。

lead(col,n,default_val)：往后第n行数据，缺失时用default_val替换。

ntile(n)：用于将分组数据按照顺序切分成n片，返回当前记录所在的切片值。n为整数。

-- over
select name
	,count(*) over() as cnt -- 此时over未加条件，因此count是针对全部数据，即count(name)
from table_name;

-- over和group by
select name
	,count(*) over()  -- 针对group by后的全部数据统计
from business
where substring(orderdate,0,7)='2020-04'
group by name;

-- over和partition by
select name
	,orderdate
	,cost
	,sum(cost) over(partition by name,month(orderdate))
from business;

-- 顾客按照日期进行累加消费金额
select name
	,orderdate
	,cost,
	sum(cost) over(partition by name order by orderdate) -- 此时为默认窗口范围，从开始到当前行
from business;
select name
	,orderdate
	,cost,
	sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) -- 此时为当前行、前一行和后一行
from business;

--顾客上一次的购买时间
select name
	,orderdate
	,lag(orderdate,1,orderdate) over(partition by name order by orderdate) --取当前行的前一行
	,lead(orderdate,1,orderdate) over(partition by name order by orderdate) --取当前行的后一行
from business;

-- 取前20%时间的订单信息
select name
	,orderdate
	,cost
	,ntile(5) over(order by orderdate) as groupId
from business;
  • Rank

rank()：排序相同时会重复（会并列），总数不会变。

dense_rank()：排序相同时会重复（会并列），总数会减少。

row_number()：会根据顺序计算，不会重复排序（不会并列）。

  • 其他函数

Hive常用函数大全一览

2.5 hive优化 2.5.1 explain查看执行计划 
explain explorarion
  • 查看下面这条语句的执行计划
-- 没有生成MR任务的
explain select * from table;
-- 走MR任务的语句
select deptno,avg(sal) as avg_sal from emp group by deptno;
2.5.2 Fetch 抓取

fetch抓取是指，hive中对某些情况的查询是可以不必使用mapreduce计算。

hive中有些查询走mr有些不走，是因为设置hive.fecth.task.conversion默认为more，在全局查找、字段查找、limit查找都不走mapreduce。

2.5.3 本地模式

大多数的hadoop job是需要hadoop提供的完整的可扩展性来处理大数据集的。不过，又是hive的输入数据量是非常小的。在这种情况下，为查询出发执行任务消耗的时间可能会比实际job执行时间要多得多。对于大多数这种情况，hive可以通过本地模式在单台机器上处理所有的任务，对于小数据集，执行时间可以明显呗缩短。

通过设置hibe.exec.mode.local.auto=true，来让hive在适当的时候自动启动这个优化。

2.5.4 表的优化 (1) 小表大表Join(Map Join)

小表放左，大表放右。现在自动选择Mapjoin默认为true。大表、小表放的顺序不影响了。

map join原理和场景

Hive的mapjoin原理与参数说明

(2) 大表Join大表
  • 空key过滤

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上（造成数据倾斜），从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下这些key对应的数据是异常数据，我们需要在sql语句中进行过滤。即先对表进行过滤再进行left join。

使用场景：非inner join（两种表inner join时会自动过滤空值）；不需要字段为null的；

  • 空key转换

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含再join得结果中，此时我们可以给表中key为空的字段赋一个随机的值（要注意给定随机值后不能改变join的结果），使得数据随机均匀地分布到不同的reducer上。

  • SMB(sort merge bucket join)

分桶表join可有效剪短时间。

smb是sort merge bucket *** 作，首先进行排序，继而合并，然后放到所对应的bucket中去，bucket是hive中和分区表类似的技术，就是按照key进行hash，相同的hash值都放到相同的buck中去。在进行两个表联合的时候。我们首先进行分桶，在join会大幅度的对性能进行优化。也就是说，在进行联合的时候，是table1中的一小部分和table1中的一小部分进行联合，table联合都是等值连接，相同的key都放到了同一个bucket中去了，那么在联合的时候就会大幅度的减小无关项的扫描。

  • group by

默认情况下，map阶段同一key数据分发给一个reduce，当一个key数据过大时就产生倾斜。

并不是所有的据合 *** 作都需要在reduce端完成，很多据合 *** 作都可以先在map端进行部分聚合，最后在redcue端得出最终结果。

-- 是否在map端进行聚合，默认为true
set hive.map.aggr=true
-- 在map端进行聚合 *** 作的条目数目
set hive.gruopby.mapaggr.checkinterval=100000
-- 有数据倾斜的时候进行负载均衡，默认为false
set hive.groupby.skewindata=true
  • count distinct去重统计

必须在一个reduce task中完成，

-- 除了分桶表，可以使用以下方式
select count(*) from (select id from emp group by id) t1;
  • 笛卡尔积

Hive只能使用一个reducer来完成笛卡尔积。

  • 行列过滤

列处理：在select时只选择需要的列，尽量少用select *；如果有分区，尽量使用分区过滤。

行处理：在分区裁剪时，当使用外关联时，如果将副表的过滤条件写在where后面，那么就会先全表关联，之后再过滤；因此，应该先对表进行筛选，再进行关联。

-- 通过子查询后，再关联表
select b.id from bigtable b join (select id from bigtable where id<10) o on b.id=o.id;
  • 分区

见上面内容

  • 分桶

见上面内容

2.5.5 合理设置Map和Reduce数
  • 复杂文件增加map数
  • 小文件进行合并
  • 合理设置Redcue数

om (select id from emp group by id) t1;

- 笛卡尔积

Hive只能使用一个reducer来完成笛卡尔积。

- 行列过滤

**列处理**：在select时只选择需要的列，尽量少用`select *`；如果有分区，尽量使用分区过滤。

**行处理**：在分区裁剪时，当使用外关联时，如果将副表的过滤条件写在where后面，那么就会先全表关联，之后再过滤；因此，应该先对表进行筛选，再进行关联。

```sql
-- 通过子查询后，再关联表
select b.id from bigtable b join (select id from bigtable where id<10) o on b.id=o.id;
  • 分区

见上面内容

  • 分桶

见上面内容

2.5.5 合理设置Map和Reduce数
  • 复杂文件增加map数
  • 小文件进行合并
  • 合理设置Redcue数
参考资料

尚硅谷大数据Hive教程（基于hive3.x丨hive3.1.2）

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5679030.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
mbti测试 mbti测试 一级用户组
0 0
上一篇 2022-12-17
下一篇 2022-12-17

发表评论

登录后才能评论

评论列表（0条）

保存