hive基础

OLAP面壁者

于 2022-05-25 09:49:41 发布

阅读量163

点赞数

文章标签： hive hadoop big data

本文链接：https://blog.csdn.net/m0_59025104/article/details/124960119

版权

hdfs常用命令

hdfs dfs -mkdir hdfs文件路径名 #创建一个目录
hdfs dfs -ls hdfs目录 #查看hdfs目录
hdfs dfs -put 本地文件路径 hdfs文件路径 #推送一个文件到hdfs
hdfs dfs -cat hdfs文件路径 [| head] #显示文件的头几行
hdfs dfs -get hdfs文件路径 #从hdfs获取一个文件到本地
hdfs dfs -rmdir hdfs文件路径
hdfs dfs -copyFromLocal 本地路径 hdfs://localhost+目的路径
hdfs dfs -copyToLocal hdfs文件路径 本地路径

hive配置

配置完基本hadoop集群后
cp core-site.xml hdfs-site.xml /root/install/apache-hive-2.3.6-bin/conf/
 
vi hive-site.xml  //添加配置

shell脚本后台启动hive服务
- cd ~/hdp/start-server
- vi hive-start-server 添加以下内容
```
#!/bin/bash
nohup hive --service metastore &
nohup hive --service hiveserver2 &
```
- 添加后命令行输入 sh hive-start-server 即可启动hive服务
进入hive两种方式
- beeline连接hive
  - 以下代码是以root用户进入并使用hive_db1数据库
```
beeline -u jdbc:hive2://hadoop5:10000/hive_db1 -n root   
```
- 命令行键入hive启动

查看正在使用的数据库

方法一

select current_database();

方法二： vi hive-site.xml 添加以下参数，再重新进入hive即可

<property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
</property>

查找表及查询操作

show tables;  
show tables '*ploy*';  show tables '*sam|lily*';  --查找表
show table extended like '*o*';     --显示表的拓展信息
desc [formatted|extended] table_name;
show create table table_name;        -- 显示创建表的语句和具体信息
select work_place,work_place[1] from employee_external;
select sex_age.age from employee_external;
select name, skills_score['DB'] from employee_external;
select name, depart_title['Product'][0] from employee_external;
--复杂数据类型操作作为了解就可以了。

SELECT 语句

子查询

with t1 as (select ...
),
with t2 as (select ...
)
[insert ...]
select * from t1

Hive建表相关

CTE

Common Table Expression (CTE) 公共表达式

CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;

CTAS

基于SELECT查询的结果生成表，不能生成分区表，桶表，外部表

create table table_name as select
create table table_name like

查看表结构

desc table
desc formatted table
show create table

EXTERNAL TABLE（外部表）

drop table 不会删除加载的数据源文件，删除在MySQL中元数据
创建外部表没有指定location，默认读取的是database/tablename/ 下的文件

CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (
name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>>
)
COMMENT 'This is an external table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

MANAGED TABLE（内部表）

drop table 会删除元数据和加载的数据源文件

CREATE  TABLE IF NOT EXISTS employee_external (
)
COMMENT 'This is an managertable'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE

PARTITION TABLE（分区表）

桶表

Temporary Table（临时表）

HIVE视图

常用操作

--建立视图支持 CTAS，CTE, ORDER BY, LIMIT, JOIN, etc.
CREATE VIEW view_name AS SELECT statement;  --创建视图
SHOW TABLES; (SHOW VIEWS after hive v2.2.0) --查找视图
SHOW CREATE TABLE view_name;   --显示视图定义
DROP view_name;                --删除视图
ALTER VIEW view_name SET TBLPROPERTIES ('comment'='This is a view');    --更改视图属性
ALTER VIEW view_name AS SELECT statement  --更改视图定义

LATERAL VIEW
- 在一个复杂的SQL查询中，可以生成类似于临时的视图一样的功能
- 如果查询到LATERAL VIEW的结果为NULL，最终结果不会显示（与其他字段无关）
```
select col_1, col_lateral from table lateral view explode() view_name as col_lateral
```
- LATERAL VIEW OUTER
- 结果显示包含NULL
```
select col_1, col_lateral from table lateral view outer explode() view_name as col_lateral
```

插入数据的三种方式

1）数据文件放到相应的表的目录下

hive指定目录/database/tablename/

2）LOAD DATA

load data 会移动加载的数据源文件到loaction指定的hdfs目录下，没指定则移动在hive-site.xml文件中hive.metastore.warehouse.dir属性定义的hdfs目录
LOAD和INSERT的异同
- LOAD和INSERT语句都需要表存在才能加入数据
- LOAD从文件中加载数据，INSERT可从结果集（其他表的数据）中插入数据到表中
- INSERT可以把一次把一张表的数据加载到多个目标表中，可选字段
从本地加载数据，源数据文件还存在，相当于复制到HIVE指定的HDFS目录下

LOAD DATA LOCAL INPATH 'local_file_pwd' OVERWRITE INTO TABLE TABLE_NAME;

从HDFS加载数据，把数据源文件移动到HIVE表对应的HDFS目录，源数据文件会被移动（mv）过去 [推荐]

LOAD DATA INPATH 'hdfs_file_pwd' OVERWRITE INTO TABLE TABLE_NAME;

3）INSETRT语句

insert into insert_table_name select * from table_name;

--一个源表多个目的表
from table_sourece 
insert overwrite table table1 select *
insert overwrite table table2 select *;

--插入到分区


--插入指定的列
insert into table(name) select 'zs' from test limit 1;

--插入指定的值
insert into table(name) values ('LZJ'),('WJC');

--使用CTE表达式
with t1 as (select name,age,addr from table_name)
from t1 
insert overwrite table table1 select *
insert into table table2(name) select name;

### 删除数据

一次删除一个指定的元组

Delete from table_name where id=1

删除所有数据，保留表结构，不能撤消还原

TRUNCATE TABLE table_name

删除表，数据和表结构一起删除，快速

DROP TABLE IF EXISTS table_name

HIVE函数

查找函数及函数帮助

desc function func_name; //显示函数所需参数及返回值
desc function extended fanc_name; //详细
show function like ‘explo*’; //查找函数

HIVE函数分类

UDTF //一个输入多个输出，例：explode
UDF //一个输入一个输出
UDAF //多个输入一个输出

HIVE常用函数

order by
- 默认升序，对输入进行全局排序，因此只有一个reducer（多个reducer无法保证全局有序），缺点：当输入规模大时，时间成本太大
sort by
- 在数据进入reducer前完成排序
distribute by
- 按照指定的字段对数据进行划分，输出到不同的reducer中
cluster by
- 兼具distribute by和sort by的功能
explode()
- 参数：一个集合
- 返回值：集合的每一个元素
- 可以基于explode+lateral view 实现词频统计

HIVE SELECT IN ADVANCE （HIVE进阶查询）

正则匹配

查找列的时候可以使用正则匹配

SET hive.support.quoted.identifiers = none;
SELECT ^o.* FROM offers;

like
rlike
regexp()
regexp_extract()
regexp_replace()
Virtual Columns：虚拟列
- map任务输入的文件名
- BLOCK_OFFSET__INSIDE_FILE //文件块中的行数据偏移

Hive Sorting Data（数据排序）

ORDER BY
SORT BY
- 在reduce阶段

set mapred.reduce.tasks=2

select name from employee sort by name desc;
当有超过一个reducer时，数据排序不正确

DISTRIBUTE BY
- 总结：类似于mapreduce里面的自定义分区
- 通常在 sort by 之前使用
- 类似sql的group by
- 不会对每个reduce输出进行排序
- 确保匹配列值的行，将被分区到同一个reducer
- distribute by 的列，必须出现在select列表中
CLUSTER BY

Hive Aggregation （聚合运算）

Hive GROUP BY（分组）
有分组就有聚合函数
支持按照数字位置排序
支持使用case、when或表达式
Hive HAVING
- Hive 0.7.0 开始，添加了having以支持group by的聚合结果的条件过滤
- 使用HAVING可避免在GROUP BY之后使用子查询
Hive Basic Aggregation（基本聚合函数）–UDF多个输入一个输出
- concat_ws(separator, [string | array(string)]+)
  - 参数：1：分割标识 2+：字符串或字符串集合
  - 返回值：以分割标识连接每一个字符串
- collect_set()
  - 去除重复的元素
- collect_list()
  - 保留重复的元素

Hive Window Functions Overview （窗口函数概述）

定义：在整个有界的数据集中查看一些局部的数据结果，即可显示聚合前的数据，又可显示聚集后的数据
- 分析函数功能强大，不受GROUP BY的限制
- 可以通过它完成复杂的计算和聚合
- 在HIVE 0.11.0添加，hive窗口函数是一组特殊的函数，它们扫描多个输入行以计算每个输出值
语法：function(arg1, …, argn) over ([partition by<…>] [order by<…>] [<window_clause>])
- function()：任意一个窗口函数
- PARTITION BY：与GROUP BY类似，如果没有分区，则按所有分区，用来指定开窗的列，分区列的值相同的行被视为在同一个窗口内
- ORDER BY：如果没有，则无法定义window——clause，order by用来指定数据在一个窗口内如何排序
- windows_clause：不常用，但功能很强，可以用rows指定开窗方式，目前有以下两种方式
  - rows between x proceding|follwing and y proceding|follwing --表示窗口范围是从前或后x行到前或后y行
  - rows x proceding|follwing --表示窗口范围是从前或后第x行到当前行
排序：ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
- NTILE()：分为几等分
- RANK()：相同的数字，排名一样，占用索引（第几个数）
- DENSE_RANK()：相同的数字，排名一样，不占用索引
- ROW_NUMBER：相同的数字，排名不一样
- PERCENT_RANK：（当前排名 - 1）/（总行数 - 1）。因此，它返回值相对于一组值的百分比排名。(最低％)
统计：COUNT, SUM, AVG, MAX, MIN
分析：CUME_DIST, LEAD, LAG, FIRST_VALUE, LAST_VALUE
- lag()：返回当前行指定列第前？行的内容，可指定默认值
  - arg1：列名，arg2：前第几行，arg3：可选，默认值
- lead()：
- first_value或last_value：分区排序后截止到当前行的第一个值或最后一个值
- cume_dist()：小于等于当前值的行数 / 分组内总行数
WINDOW clause（窗口的定义）
Case Study（案例分析）
Row Type Of Windows（行类窗口） --行与行之间比较
- rows between 2 preceding and current row //样例
- current row //当前行
- N following //
- M preceding //
- unbounded preceding //窗口内排序后第一行
- unbounded following //窗口内排序后最后一行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6MwSsd4F-1653443046718)(hadoop_pic/hive/行类窗口.png)]

MAPJOIN
- 底层基于MapReduce的DC（分布式缓存）实现
- 只有MAP执行连接，没有REDUCE；读取小表中所有数据到内存，然后广播到所有的运行map任务的节点
  定义）
Case Study（案例分析）
Row Type Of Windows（行类窗口） --行与行之间比较
- rows between 2 preceding and current row //样例
- current row //当前行
- N following //
- M preceding //
- unbounded preceding //窗口内排序后第一行
- unbounded following //窗口内排序后最后一行

[外链图片转存中…(img-6MwSsd4F-1653443046718)]

MAPJOIN
- 底层基于MapReduce的DC（分布式缓存）实现
- 只有MAP执行连接，没有REDUCE；读取小表中所有数据到内存，然后广播到所有的运行map任务的节点
- hive.auto.convert.join=true //Hive在运行时自动将JOIN转换成MAPJOIN，可能会有内存溢出

OLAP面壁者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
hive基础

hdfs常用命令hdfs dfs -mkdir hdfs文件路径名 #创建一个目录hdfs dfs -ls hdfs目录 #查看hdfs目录hdfs dfs -put 本地文件路径 hdfs文件路径 #推送一个文件到hdfshdfs dfs -cat hdfs文件路径 [| head] #显示文件的头几行hdfs dfs -get hdfs文件路径 #从hdfs获取一个文件到本地hdfs dfs -rmdir hdfs文件路径hdfs dfs -copyFromLocal 本地路径 hdf.
复制链接

扫一扫