hive取日期间的月份差 hive取当前月份

转载

mob64ca1410eb61 2024-05-17 23:17:28

文章标签 hive取日期间的月份差 hive round函数不起作用 hive 列转行 文章分类 Hive 大数据

行转列:explode

列转行:

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是stringhive 基础知识的总结回顾内含我收集的几个sql题目, 用来回顾练手的

包括一些优化相关的总结

是什么

为Hadoop上的任务提供一个sql的接口

静态数据分析
对实时性要求不高
只有表的元数据
具体数据存储在HDFS中

数据模型

在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
在hdfs中表现所属db目录下一个文件夹
与table类似，不过其数据存放位置可以在任意指定路径(外部表)
在hdfs中表现为table目录下的子目录
在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

基本组成

CLI
JDBC/ODBC
WebGUI

元数据存储

完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成

hive服务

命令行接口

-- 注释
-d 定义kv值
-f filename
-h help
-h hostname

hive -e "" > file

让hive以thrift服务运行
嵌入式工作的hive命令行接口
hive的web接口
更加全面的web接口
默认metostore和hive在同一个服务里
可以让其成为单独的元数据服务

特点

内部表外部表

文件
二进制格式
单机

若配置了,每次都要启动
hive --service metastore &

类似关系型数据库视图
Hive的索引目的是提高Hive表指定列的查询速度。
mapreduce
tez
hive是读时模式
在加载的时候验证

分区与分桶

分区

每个分区建立一个文件夹
外部分区表
自己增加分区
自己挂载进来

分桶

桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。
随机分数据

hql语法

基础

基本数据类型
String

集合数据类型
hdfs支持的文件压缩格式

数据库操作

与mysql类似

show databases like "h.*"

if not exists

表操作

查勘表的情况
显示XXX的分区情况

create teble XXX like XXX

建表

建表语法 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

说明： 1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。 2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 3、LIKE 允许用户复制现有的表结构，但是不复制数据。 4、ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。 5、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY 对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

create table

属性
指定下面的分隔符
要先有这个
每列之间分割
list之间
map之间
不需要粉色的声明开头
每行之间
划分分区

CLUSTERED BY(userid) SORTED BY(viewTime) INTO n BUCKETS

存储的文件格式
默认 TEXTFILE
SEQUENCEFILE
RECFILE
还可以自己制定 InputFormat 和OutputFormat
存储在哪里
默认是 /user/hive/warehouse

查询

select中可以使用 array下标和 map[key]

if语句
CASE when then when then

group having

inner
left
outer

mapside join

sort by order by

控制reducer如何划分
可以和sort by一起用

limit

函数类型

近似值

abs
count
max min avg sum
hive.map.aggr

返回多个元素
m列转为n行

as )

类型装换

length
split
ltrim
lower

重复n次

数据操作

load data [ local] inpath "XXX" [overwrite] into table XXX [Partition (...)]

2.2.1Load 语法结构 LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明： 1、Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。 2、filepath：相对路径，例如：project/data1 绝对路径，例如：/user/hive/project/data1 包含模式的完整 URI，列如： hdfs://namenode:9000/user/hive/project/data1 3、LOCAL关键字如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。如果没有指定 LOCAL 关键字，则根据inpath中的uri[如果指定了 LOCAL，那么： load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。 load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。

如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。如果路径不是绝对的，Hive 相对于/user/进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。]查找文件

4、OVERWRITE 关键字如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

- 没有local的话 就是在hdfs中, 会将其移动过去

insert [overwrite] table XXX Partition(...) select *

将查询结果插入Hive表 语法结构利用查询语句，将查询结果插入新的表 INSERT OVERWRITE [INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

插入一条数据 INSERT INTO TABLE VALUES(XX,YY,ZZ);

Multi Inserts多重插入: FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] Select statement2] ...

Dynamic partition inserts动态分区插入: INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

- 可以动态分区插入
- partition里面是 select里面的列

create table user_13302 as Select * from posts

修改

alter table xxx add partition ...
alter table xxx drop partition ...
alter table xxx partition ... set ...
Alter table xxx Change Column
Alter table xxx add Column

应用

DEMO

hive版 wordcount

原始数据
导入分区后的表中

模式设计

如何分区

调优

显示语法树

参数与优化

map端join
innor和left
本地模式执行
set hive.exec.reducers.max=
set mapred.reduce.tasks=
set hive.exec.reducers.bytes.per.reducer=

jvm重用

hive.vectorized.execution.mapjoin.native.enabled=true;

hive.vectorized.execution.mapjoin.native.fast.hashtable.enabled=true;
hive.vectorized.execution.mapjoin.minmax.enabled=true;
hive.vectorized.execution.reduce.enabled=true;
hive.llap.client.consistent.splits=true;
hive.optimize.dynamic.partition.hashjoin=true;

原理

文件的存储

支持的数据类型

hdfs支持的文本压缩

- gzip压缩

    - 压缩率比较高，而且压缩/解压速度也比较快
    - 缺点：不支持split
    - 应用场景

        - 当每个文件压缩之后在130M以内的（1个块大小内），都可以考虑用gzip压缩格式

- lzo压缩

    - 优点

        - 压缩/解压速度也比较快，合理的压缩率
        - 支持split，是hadoop中最流行的压缩格式

    - 压缩率比gzip要低一些；hadoop本身不支持，需要安装
    - 应用场景：一个很大的文本文件，压缩之后还大于200M以上的可以考虑，而且单个文件越大，lzo优点越越明显

- snappy

    - 优点：高速压缩速度和合理的压缩率；支持hadoop native库。
    - 缺点：不支持split；压缩率比gzip要低
    - 应用场景

        - 当mapreduce作业的map输出的数据比较大的时候，作为map到reduce的中间数据的压缩格式
        - 或者作为一个mapreduce作业的输出和另外一个mapreduce作业的输入。

- bzip2

    - 优点

        - 支持split
        - 具有很高的压缩率，比gzip压缩率都高

    - 缺点：压缩/解压速度慢；不支持native。
    - 应用场景

        - 适合对速度要求不高，但需要较高的压缩率的时候
        - 可以作为mapreduce作业的输出格式
        - 或者输出之后的数据比较大，处理之后的数据 需要压缩存档减少磁盘空间并且以后数据用得比较少的情况
        - 或者对单个很大的文本文件想压缩减少存储空间，同时又需要支持split，而且兼容之前的应用程 序（即应用程序不需要修改）的情况

orc

- ORC是在一定程度上扩展了RCFile，是对RCFile的优化
- orc.compress ZLIB  high level compression (one of NONE, ZLIB, SNAPPY)
    - 结构

        - ORCFile在RCFile基础上引申出来Stripe和Footer
        - 每个ORC文件首先会被横向切分成多个Stripe，而每个Stripe内部以列存储
        - IndexData中保存了该stripe上数据的位置信息，总行数等信息
        - RowData以stream的形式保存了数据的具体信息
        - Stripe Footer中包含该stripe的统计结果，包括Max，Min，count等信息

    - Hive读取数据的时候，根据FileFooter读出Stripe的信息，根据IndexData读出数据的偏移量从而读取出数据
    - 压缩

        - ZLIB

    - 支持的引擎

        - hive

- parquet

    - Parquet文件是以二进制方式存储的，是不可以直接读取和修改的
    - Parquet文件是自解析的，文件中包括该文件的数据和元数据
    - 压缩

        - snappy
        - gzip

    - 支持的引擎

        - drill
        - impala
        - hive

- avro

    - CREATE TABLE kst
      PARTITIONED BY (ds string)
      ROW FORMAT SERDE
      'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
      STORED AS INPUTFORMAT
      'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
      OUTPUTFORMAT
      'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
      TBLPROPERTIES (
        'avro.schema.url'='http://schema_provider/kst.avsc')

Sequencefile

sql命令对应MR

select , where

map中进行

join

属于一个优化,在小表可以装在内存中时使用
类似MRreduce join
以join的key作为map输出的key
区分两个tag

可以在语法树中下拉
提前过滤

根据 Key 的值进行 Hash，并将 Key/Value对按照 Hash值推至不同对Reduce中
按照key join
通过tag标识不同数据

grouy by

按照key划分reduce
reduce中执行聚合操作

distinct

把整个记录都作为map的输入
在reduce上去重

sort by order by

利用reduce排序的特性

高级特性

文件格式

序列化/反序列化简写
avro

最基本的

一种HDFS上的容器
stored as sequencefile

None
Record
block

RCFile
ORCFile
自定义输出

自定义函数

UDF

自定义或内置的函数
相当于 map

1、先开发一个java类，继承UDF，并重载evaluate方法
2、打成jar包上传到服务器

hive>add JAR /home/hadoop/udf.jar;
Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';
Select tolowercase(name),age from t_test;

例子字符串转化

UDAF

用户定义聚合函数
类似count
例子

UDTF

表生成函数

1变多
多变1

例子行转列

锁和事务

配合zookeeper使用
共享锁
独占锁
可以显式定义锁

hive2 ACID特性

Thrift服务

通过端口远程访问hive
元数据存储服务

HCatalog

屏蔽了底层数据存储的位置格式等信息，为上层计算处理流程提供统一的，共享的metadata
基于 Hive metastore

Streaming

hive语句中嵌入流处理代码
比UDF效率更低

视图

类似关系型数据库

索引

类似关系型数据库,单有区别
建立索引还是可以提高Hive表指定列的查询速度
但是好像没什么卵用
索引不会自动更新
索引可以自己定制

开窗函数

用法

函数
over
( partition by order by)

sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, sum(pv) over (partition by cookieid order by createtime) as pv2, sum(pv) over (partition by cookieid) as pv3, sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6

如果不指定ROWS BETWEEN,默认为从起点到当前行; 如果不指定ORDER BY，则将分组内所有值累加; 关键是理解ROWS BETWEEN含义,也叫做WINDOW子句： PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，　　
UNBOUNDED FOLLOWING：表示到后面的终点

普通聚合

高级函数

分片
用于将分组数据按照顺序切分成n片，返回当前切片值

rank()

行号
按顺序编号，不留空位
生成数据项在分组中的排名，排名相等会在名次中留下空位
生成数据项在分组中的排名，排名相等会在名次中不会留下空位
用于统计窗口内往上第n行值
第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

FIRST_VALUE
LAST_VALUE

grouping sets

在一个GROUP BY查询中，根据不同的维度组合进行聚合
等价于将不同维度的GROUP BY结果集进行UNION ALL

GROUPING__ID，表示结果属于哪一个分组集合

WITH CUBE

根据GROUP BY的维度的所有组合进行聚合
等于Grouping set(所有的)
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPINGID FROM cookie5 GROUP BY month,day WITH CUBE ORDER BY GROUPINGID;

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPINGID FROM cookie5 GROUP BY month,day WITH ROLLUP ORDER BY GROUPINGID;

RCfile

先水平划分，再垂直划分的理念

首先将表分为几个行组，对每个行组内的数据进行按列存储，每一列的数据都是分开存储

优化补充

行转列:explode

列转行:

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是string

数据倾斜优化:

group by

我使用Hive对数据做一些类型统计的时候遇到过某种类型的数据量特别多，而其他类型数据的数据量特别少。当按照类型进行group by的时候，会将相同的group by字段的reduce任务需要的数据拉取到同一个节点进行聚合，而当其中每一组的数据量过大时，会出现其他组的计算已经完成而这里还没计算完成，其他节点的一直等待这个节点的任务执行完成，所以会看到一直map 100% reduce 99%的情况。

解决方法：set hive.map.aggr=true　　　　　　　set hive.groupby.skewindata=true　　　　　　　　set hive.groupby.mapaggr.checkintenval=1000000; 这个是group的键对应的记录条数超过这个值则会进行优化　　原理：hive.map.aggr=true 这个配置项代表是否在map端进行聚合　　　　　　　　　　　　　　　　hive.groupby.skwindata=true 当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

map和reduce优化。

1.当出现小文件过多，需要合并小文件。可以通过set hive.merge.mapfiles=true来解决。 2.单个文件大小稍稍大于配置的block块的大写，此时需要适当增加map的个数。解决方法：set mapred.map.tasks个数 3.文件大小适中，但map端计算量非常大，如select id,count(*),sum(case when...),sum(case when...)...需要增加map个数。解决方法：set mapred.map.tasks个数，set mapred.reduce.tasks个数　

count

当HiveQL中包含count（distinct）时, 如果数据量非常大，执行如select a,count(distinct b) from t group by a;类型的SQL时，会出现数据倾斜的问题。解决方法：使用sum...group by代替。如select a,sum(1) from (select a, b from t group by a,b) group by a;

当遇到一个大表和一个小表进行join操作时。

解决方法：使用mapjoin 将小表加载到内存中。当表小于25mb的时候，小表自动注入内存 set hive.auto.convert.join=true; set hive.mapjoin.smalltable.filesize=25mb; 默认值是25mb

join优化

两阶段处理 set hive.optimize.skewjoin=true;如果是join过程出现倾斜应该设置为true set hive.skewjoin.key=1000000;--这个是join的键对应的记录条数超过这个值则会进行优化

排序选择

cluster by：对同一字段分桶并排序，不能和 sort by 连用 distribute by + sort by：分桶，保证同一字段值只存在一个结果文件当中，结合 sort by 保证每个 reduceTask 结果有序 sort by：单机排序，单个 reduce 结果有序 order by：全局排序，缺陷是只能使用一个 reduce

小文件合并

小文件合并文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件

set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件

set hive.merge.size.per.task = 25610001000 ##合并文件的大小

set mapred.max.split.size=256000000; ##每个 Map 最大分割大小

set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行 Map 前进行小文件合并

Hive的几个面试题

这几个面试题是手生的时候的,用来回顾的题目

求单月访问次数和总访问次数

现要求出：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数用户名uname，月份umonth，访问次数ucount A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B,2015-03,10 B,2015-03,1

create external table if not exists t_access( uname string comment '用户名', umonth string comment '月份', ucount int comment '访问次数' ) comment '用户访问表' row format delimited fields terminated by ","

结果数据格式如下

hive取日期间的月份差 hive取当前月份_hive 列转行

当前月的访问次数

select uname, umonth, count(*) from table group by uname, umonth

累计到本月的总访问次数

select uname, umonth sum(ucount) over(partition by uname order by uname, umonth rows between unbounded preceding and current row)

累积到本月的最大月访问次数

select uname, umonth max(ucount) over(partition by uname order by uname, umonth rows between unbounded preceding and current row)

一行命令搞定

select
uname,umonth , 
max(sum_count) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), total_count,sum_count #
from
(select uname,umonth ,max(total_sum) as total_count ,sum(ucount) as sum_count #求出当前用户,当前月的访问次数
    from
        (select uname,umonth,ucount, 
        sum(ucount) over(partition by uname order by uname,umonth  #最内层求出累计到本月的总访问次数
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
        as total_sum 
        from t1
        ) as tmp1
    group by uname,umonth
    ) as tmp2 order by uname,umonth;

所有数学课程成绩大于语文课程成绩的学生的学号

用sql求出所有数学课程成绩大于语文课程成绩的学生的学号

CREATE TABLE `course` (
`id` int,
`sid` int ,
`course` string,
`score` int 
) ;

// 字段解释：id, 学号， 课程， 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

自连接

select a.sid
from course a join course b on a.sid = b.sid and a.course = 'shuxue' and b.course ='yuwen'
where a.score > b.score;

使用case...when...将不同的课程名称转换成不同的列

create view tmp_course_view as
select sid, case course when "shuxue" then score else 0 end  as shuxue,  
case course when "yuwen" then score else 0 end  as yuwen from course;

create view tmp_course_view1 as
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;

select * from tmp_course_view1 where shuxue > yuwen;

求学生选课情况

求出id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

create table t_course(id int,course string) row format delimited fields terminated by ",";

id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e

select id, 
if(find_in_set('a',courses)==0,0,1) as a,
if(find_in_set('b',courses)==0,0,1) as b,
if(find_in_set('c',courses)==0,0,1) as c,
if(find_in_set('d',courses)==0,0,1) as d,
if(find_in_set('e',courses)==0,0,1) as e
from 
(
select id,concat_ws(',',collect_list(cid)) as courses 
from t3
group by id) tmp;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java update后马上查询 java update needed

下一篇：银联回调接口 java 银联支付api接口文档

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯