- 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启动
- beeline连接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()
- 保留重复的元素
- concat_ws(separator, [string | array(string)]+)
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():小于等于当前值的行数 / 分组内总行数
- lag():返回当前行指定列第前?行的内容,可指定默认值
-
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,可能会有内存溢出