hive窗口函数和hive基础使用

7.27

0.连接客户端

beeline -u jdbc:hive2://localhost:10000

1.建库并使用

create database xxx

use xxx

2.建表

create table xxx

create external table if not exists

如果该外部表不存在则创建

外部表被删除时只会删除元数据不会删除数据。（hive不认为自己拥有这份数据）

3.导入数据

load data inpath ‘/root/hero/archer.txt’ into table dwd_trip_statistics_pdi partition(day=‘2022.7.26’);

4.窗口函数

定义：实时处理函数，针对多个输入它可以有多个输出。不同于聚组函数只有一个输出。

核心：分区，排名，框架

​ 分区是针对一个表的某个不同特征值分开一整张表，降低性能损耗。分区会以文件夹形式存放再该表目录。

具体再表中会有一个字段表示分区。

建表侧

partitioned by (day string comment '分区时间')

使用侧

load data local inpath '/root/hero/archer.txt' into table dwd_trip_statistics_pdi partition(day='2022.7.26'); 

​

​ 排名是对区进行排名。

语法：

select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名

分类：

排名函数：row_number()，rank()，dense_rank()
聚合函数：max()，min()，count()，sum()，avg()，median()
向前向后取值：lag()，lead()
百分位：percent_rank()
取值函数：first_value()，last_value()，nth_value()
分箱函数：ntile()

row_number() 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank() 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank() 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3

其他的聚合函数如max等就是每一列都执行一次该函数，每一行都会有结果

select *,max(score) over  (partition by day order by score desc )
from olapTest;

select *,min(score) over  (partition by day order by score desc )
from olapTest;


select *,
sum( 成绩 ) over (ORDER BY 学号)as current_sum,
avg( 成绩 ) over (ORDER BY 学号)as current_avg,
count( 成绩 ) over (ORDER BY 学号)as current_count,
max( 成绩 ) over (ORDER BY 学号)as current_max,
min( 成绩 ) over (ORDER BY 学号)as current_min
from 班级表;
sum是每一行累加，然后改行显示的是目前为止的sum
max和min相同

测试代码

create database test4;
use test4;
drop database test4;
drop table if exists olapTest;
create table if not exists olapTest(
id int comment '账号',
name string comment '姓名',
score int comment '分数'
)
comment '窗口函数分区和排名测试'
partitioned by (day string comment '输入日期')
row format delimited fields terminated by '\t'
//stored as Parquet
//TBLPROPERTIES('parquet.compression'='SNAPPY')
;


load data local inpath '/data/xcw/olap_data.txt' into table olapTest
partition(day='2022.7.28');

select *
from olapTest;

select *,row_number() over (partition by id order by score desc )
from olapTest;


select *,row_number() over (partition by day order by score desc )
from olapTest;//rownumber如果有两个成绩相同的不影响，正常排序是连续的

select *,rank() over (partition by day order by score desc )
from olapTest;//成绩相同会并列，然后会跳过相同成绩到另外的排名比如1224这样

select *,dense_rank() over (partition by day order by score desc )
from olapTest;//dense会并排但是后一个是连续的排名

注意：rowfromat 得放在partitioned后面

5.Hbase

常用命令：

启动客户端 bin/hbase shall

查看库里的表 list

新建表 （必须指定表名和列簇名） create ‘table_name’,‘列簇名’

表的详细描述 describe xxx

放入数据 put ‘table_name’,‘’

(添加depandency依赖需要在dependencies标签里)

-- 启动关闭相关
-- 1.启动hbase
bin/start-hbase.sh
-- 2.查看启动情况，
-- 单机版只会有 HMaster 进程
jps
-- 3.关闭hbase
bin/stop-hbase.sh
 
 
-- 基础命令
-- 1.进入 HBase 客户端命令行（在/目录
bin/hbase shell
-- 2.查看帮助命令
help
-- 3.查看当前数据库中有哪些表
list
 
 
-- 表空间
-- 1.创建namespace
create_namespace 'nametest'  
-- 2.删除namespace
drop_namespace 'nametest'  
-- 3.查看namespace
describe_namespace 'nametest'  
-- 4.列出所有namespace
list_namespace  
-- 5.在namespace下创建表
create 'nametest:testtable', 'fm1'  ,'fm2'
-- 6.查看namespace下的表
list_namespace_tables 'nametest'  
 
-- 表的操作
-- 1.创建表 , student 为表名，info为列族
create 'student','info'
-- 2.插入数据到表  1001 为rowkey , info:sex 为列族和列名，male为值，更新数据时也用put命令
put 'student','1001','info:sex','male'
put 'student','1002','info:sex','female'
-- 3.扫描查看表数据
scan 'student'
scan 'student',{LIMIT=>10}//LIMIT限制的是rowkey数量，比如两个rowkey每个有两个version一共四个数据，但是limit为2的时候也是显示4个的
//STOP和start是左闭右开，左边start可以连接上，stop取不上
scan 'student',{STARTROW => '1001', STOPROW => '1001'}
scan 'student',{STARTROW => '1001'}
scan 'student',{LIMIT => 10,INTERVAL => 10000,CACHE => 10000}. # 一次查询10000行，cache 为10000. 
scan 'xcw_test','0001',{RAW=>TRUE,VERSIONS=>5},RAW是指墓碑标记，hbase数据被删除时不会立刻在磁盘上删除，而是打上墓碑标记，下次major compaction的时候会删除，raw参数必须和version参数一起使用，但是不能和column参数一起使用
还有一个参数是reverse，反序读取数据
https://hbase.apache.org/book.html#_commands
-- 4.查看表结构
describe `student`
exists xxx//判断是否有这个表
is_enabled xxx //判断表是否启用
is_disabled xxx//判断表是否开启
-- 5.更新指定字段的数据//需要注意的是更新和插入数据都是put操作
put 'student','1001','info:name','Nick'
put 'student','1001','info:age','100'
put 'student','1001','info:sex','male'
put 'student','1001','info:age','18'
-- 6.查看“指定行”或“指定列族:列”的数据
get 'student','1001'
get 'student','1001','info'//查看列族的值
get 'student','1001','info:name'//查看列的值
get 'student','1001',{COLUMN=>'info:name',VERSIONS=>3}
get 'namespacd:table','rowkey'
get 't1','r1',{TIMERANGE=>[TS1,TS2]}//显示时间戳从ts1到ts2的数据，这个也是左闭右开区间
get 't1','r1',{COLUMN=>[C1,C2,C3]}//显示三个列族
get 't1','r1',{COLUMN=>'C1',TIMESTAMP=>ts1}//显示c1列族,时间戳为ts1的数据
scan 'xcw',{columns=>'info:name'}//查看一整列

-- 7.统计表数据行数
count 'student'
-- 8.删除数据
-- 删除某 rowkey 的全部数据:
deleteall 'student','1001'
-- 删除某 rowkey 的某一列数据:

delete 'student','1002','info:sex'
-- 9.清空表数据//保留表结构.需要先关闭表
truncate 'student'
-- 提示:清空表的操作顺序为先 disable，然后再 truncate 
-- 10.删除表
-- 首先需要先让该表为 disable 状态
disable 'student'
-- 然后才能 drop 这个表: 
drop 'student'
-- 提示:如果直接 drop 表，会报错:ERROR: Table student is enabled. Disable it first. 
-- 11.变更表信息
-- 将 info 列族中的数据存放 3 个版本:
改version的时候必须指定列族，不然不识别参数
alter 'student',{NAME=>'info',VERSIONS=>3} 
get 'student','1001',{COLUMN=>'info:name',VERSIONS=>3}

1.概念

表被拆成小块存储，叫Regions，他们存放再RegionServer上，Master进程也就是HMaster（Master的具体实现）。Master负责Region的分发。RegionServer中除了Regions外还有Hlog和HFile两种文件。hlog是预写日志文件，HFile是真实的数据存储文件。需要注意的是都是列式存储。方便检索，检索时不用把一整条数据扫描完。一个表会有若干个列簇，下包含列。

HLOG

RegionServer操作时的所有记录，也叫WAL write ahead log预写日志，存储在HDFS中，会保存客户端的操作。

HFile

最终写到datanode里的文件，默认大小是128M（与hdfs一个块的大小相同）

Region

每个RegionServer会有若干个Region，会有一个起始和终止的rowkey记录。一开始只有一个region，当大小达到一定阈值就会分割成两个region。

RegionServer

内存有两个部分，一个是memstore，一个是BlockCache，前者主要用来写，后者用来读。

有个特殊的RegionServer是存储root信息的server，会有两个表，一个是root表不可切分只有一个，还有一个是meta表可以被分为多个region，其他的regionserver也会存储meta表记录自己的meta信息

Store

一个Region会有多个Store。每个Store对应一个列族。store分为memstore（默认128M时flush），和storefile

​ memstore是再内存中缓存的数据，当到达128M后，RegionServer会启动flasheatch把memstore持久化到storefile，每次会场形成一个单独的storefile，

​ 客户端检索时。会在memstore中先检索，找不到则再storefile中查找。

​ store是用Hfile存储的，Hbase用store的大小来判断是否应该切分region

RowKey

类似于主键的东西，存储为String，行的唯一标识。regionserver存放数据时根据rowkey的字典序存放数据。

Cell

由{rowkey, column Family:column Qualifier, time Stamp} （行键，列族和列）唯一确定的单元。cell 中的数 据是没有类型的，全部是字节码形式存贮

Version

每个cell都可存储多个数据，也就是多个版本，根据时间戳（timestamp区分是long类型）区分。默认是三个版本，可以单独设置

2.表的构成

HBase模式里的逻辑实体包括：
(1)表(table)：HBase用表来组织数据。表名是字符串(String)，由可以在文件系统路径里使用的字符组成。
(2)行(row)：在表里，数据按行存储。行由行键(rowkey)唯一标识。行键没有数据类型，总是视为字节数组byte 。
(3)列族(column family)：行里的数据按照列族分组，列族也影响到HBase数据的物理存放，因此，它们必须事前定义并且不轻易修改。表中每行拥有相同列族，尽管行不需要在每个列族里存储数据。列族名字是字符串(String)，由可以在文件系统路径里使用的字符组成。
(4)列限定符(column qualifier)：列族里的数据通过列限定符或列来定位。列限定符不必事前定义，列限定符不必在不同行之间保持一致。就像行键一样，列限定符没有数据类型，总是视为字节数组byte 。
(5)单元(cell)：行键、列族和列限定符一起确定一个单元。存储在单元里的数据称为单元值(value)。值也没有数据类型，总是视为字节数组byte 。
(6)时间版本(version)：单元值有时间版本。时间版本用时间戳标识，是一个long。没有指定时间版本时，当前时间戳作为操作的基础。HBase保留单元值时间版本的数量基于列族进行配置，默认数量是3个。
HBase的每个数据值使用坐标来访问。一个值的完整坐标包括行键、列族、列限定符和时间版本。由于把所有坐标视为一个整体，因此HBase可以看作是一个键值(key-value)数据库。

3.写流程

客户端发起请求后，同时向wal（Write ahead log也叫Hlog）预写日志和memstore（内存）同时写入数据。两边都写完才算动作完成，

当memstore到达固定大小，数据会flush到磁盘，形成一个HFile。也就是存储到hdfs中

4.读流程

客户端向zookeeper发送请求，zookeeper返回hbase存储meta数据的server，返回对应的regionserver，这个regionserver多个region分区并发查找，先从memstore上读取，如果没有则去blockCache上读取，如果还没有则取HFile上读取。

先把该数据索引写到blockCache中，然后再返回客户端

第二个视角

第一步：客户端询问ZooKeeper，-ROOT-在哪里？
第二步：ZooKeeper回复客户端，-ROOT-在RegionServer RS1上面。
第三步：客户端询问在RS1上的-ROOT-表，哪一个.META. region可以找到表T1里的行00007？
第四步：RS1上的-ROOT-表回复客户端，在RegionServer RS3上的.META. region M2可以找到。
第五步：客户端询问RS3上的.META. region M2，在哪一个region上可以找到表T1里的行00007以及哪一个RegionServer为它提供服务？
第六步：RS3上的.META. region M2回复客户端，数据在RegionServer RS3上面的region T1R3上。
第七步：客户端发消息给RS3上面的region T1R3，要求读取行00007。
第八步：RS3上面的region T1R3将数据返回给客户端。

5.删除和合并操作

数据删除时并不是直接删除，而是打上墓碑标记（shell中raw=true参数搭配version可以显示）。大合并时这些数据才会在磁盘清除。

合并分为大合并和小合并，大合并是把小合并的HFile合并。

疑问区

1.业务表为什么用外部表，后续如何管理？

2.反复提及的NLP和VCF是啥

3.rowkey工程中一边怎么设计