Hive
从hadoop生态圈中单独列出HIve模块的使用。包括基础语法和分桶分区的不同于SQL的特殊用法。
清平乐的技术博客
学如逆水行舟,不进则退。
展开
-
hive进行base64 加密解密函数
【代码】hive进行base64 加密解密函数。原创 2023-10-18 13:22:03 · 1961 阅读 · 0 评论 -
Impala时间函数总结
Impala是基于hive并使用内存进行计算,兼顾数据仓库,具有实时,批处理,多并发等优点。impala元数据都存储在hive的metastore当中,并且impala兼容hive的绝大多数sql语法。所以需要安装impala的话,必须先安装hive,保证hive安装成功,并且还需要启动hive的metastore服务。............原创 2022-07-27 16:42:29 · 1183 阅读 · 0 评论 -
MONTHS_BETWEEN函数使用
MONTHS_BETWEEN (date1, date2)用于计算date1和date2之间有几个月。 date1-date2-- 间隔月份:时间格式 结果带小数 16.516SELECT MONTHS_BETWEEN('2022-05-28','2021-01-12') -- 间隔月份:月份格式 结果整数:16-- 方法一:拼接SELECT MONTHS_BETWEEN(CONCAT(SUBSTR('2022-05-28',1,7),'-01'),CONCAT(SUBSTR('2021-原创 2022-05-27 16:58:56 · 5355 阅读 · 0 评论 -
MySQL和Hive的DATE_ADD用法区别
一、MySQL语法说明DATE_ADD(date, INTERVAL num TYPE)date 只要是合法的日期表达式即可num 是希望添加的时间间隔值TYPE 是时间间隔的单位MICROSECOND 间隔单位:毫秒SECOND 间隔单位:秒MINUTE 间隔单位:分钟HOUR 间隔单位:小时DAY 间隔单位:天 WEEK 间隔单位:星期MONTH 间隔单位:月QUARTER 间隔单位:季度YEAR 间隔单位:年SECOND_MICROSECOND 复合型,间隔单位:秒、毫原创 2022-05-11 15:59:44 · 516 阅读 · 0 评论 -
Hive 如何实现小时数的date_add效果?
1.Hive的date_add语法说明date_add('yyyy-MM-dd',n/-m)返回初试日期n天后、m天前的日期hive> select date_add('2022-05-16 12:00:10',5);2022-05-21 hive> select date_add('2022-05-16 17:20:10',-5);2022-05-11由此可见,hive中的date_add只能返回天数级别的值,比如我想现在12点取当前5点的数据,是实现不了的。2.实现方原创 2022-05-11 15:25:51 · 4689 阅读 · 0 评论 -
Hive分区修复msck repair
Hive的[MSCK REPAIR TABLE] 命令全量修复分区,目的就是将分区信息更新到元数据库中。该命令通常用于分区表的分区修复。官方解释:(翻译版)Hive将每个表的分区信息保存在metastore中,如果通过hadoop fs -put命令直接将分区信息添加到HDFS,metastore是不会感知到这些新增的分区,除非执行了ALTER TABLE table_name ADD PARTITION命令。但是用户可以运行metastore检查命令MSCK REPAIR TABLE table_n原创 2022-05-08 19:40:37 · 5175 阅读 · 0 评论 -
窗口函数深度探索(二):控制窗口大小(UNBOUNDED、CURRENT、PRECEDING、FOLLOWING)
本文详细介绍窗口函数进行控制窗口大小的具体操作,博主分别列举了8个实际案例进行具体讲解。通过此文大家可以熟练使用这几个函数进行数仓开发和数据分析。原创 2022-05-06 16:59:01 · 4361 阅读 · 0 评论 -
窗口函数深度探索(一):底层原理
在日常SQL数据分析中,经常会遇到需要在每组内排名,面对这类需求就需要使用sql的高级功能窗口函数了。一言以蔽之:在进行分组聚合以后 , 我们还想操作集合之前的数据就需要用到窗口函数原创 2022-05-05 19:49:37 · 1799 阅读 · 0 评论 -
LAG函数和LEAD函数(案例详解)
在SQL的开窗函数使用中,LAG函数和LEAD函数出现频率还是很高的。这里博主将这两个函数的用法结合案例做一下介绍。一、函数介绍LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)LEAD函数与LAG相反二、案例演示注:这里用HiveSQL演示(Oracle、MySQL同理)示例1:基础含义数据准备DROP TABLE IF EXIS原创 2022-04-29 11:51:27 · 6077 阅读 · 0 评论 -
Hive小数取整(floor函数,ceil函数,round函数)
1. floor()函数 向下取整select floor(1.4) # 结果是:12. ceil()函数 向上取整select ceil(1.4) #结果是:23. round()函数 四舍五入select round(1.455, 2) #结果是:1.46,即四舍五入到十分位select round(1.5) #默认四舍五入到个位,结果是:2select round(255, -1) #结果是:260,即四舍五入到十位,此时个位是5会进位...原创 2021-10-19 15:19:46 · 9325 阅读 · 0 评论 -
SQL高频面试题_实现用户连续登陆的最大天数(Hive/开窗函数/Oracle)
题目:某张表两个字段分别是用户ID和登录时间。例如记录了用户id为001和002、003的登陆日期,现在问题来了:我们如何统计处两个用户各自连续登陆的天数最大值?也就是001可能会有连续登陆3天,7天,10天的这样记录,但是这其中只有10天是001连续登陆的最长天数。一、数据集准备这里数据集以Hive为例-- 表创建DROP TABLE IF EXISTS test.user_login;CREATE TABLE test.user_login(user_id string,login_d原创 2021-03-25 11:10:11 · 2179 阅读 · 6 评论 -
Hive在指定位置添加列(不删除其他数据)
方案一:用Alter命令通常,我们只使用hive的添加列命令,默认会在表的最后面添加列,例如# 添加1列alter table table_name add columns(user_id bigint comment '用户ID');# 添加多列alter table table_name add columns( name string comment '用户名称', city string comment '城市', sex string c原创 2021-03-19 10:28:20 · 9723 阅读 · 0 评论 -
Hive set 常用参数设置
一、设置执行引擎-- 配置mapreduce计算引擎set hive.execution.engine=mr;-- 配置tez 计算引擎set hive.execution.engine=tez;-- 配置spark计算引擎set hive.execution.engine=spark;二、分区相关-- 开启动态分区set hive.exec.dynamic.partition=true;-- 设置非严格模式,动态分区插入数据需要先设置非严格模式set hive.exec.dyna原创 2021-01-06 16:48:24 · 1723 阅读 · 0 评论 -
Hive、Oracle的with as 用法
with查询语句不是以select开始的,而是以“WITH”关键字开头,可以理解为在进行查询之前预先构造了一个临时表,之后便可多次使用它做进一步的分析和处理。-- 基础语法with tempName as (select ....)select ...优点1. 增加了SQL的易读性--针对一个别名with tmp as (select * from tb_name)--针对多个...原创 2020-03-29 10:54:13 · 797 阅读 · 0 评论 -
Hive开窗函数(窗口函数)_四大类型实战案例
去年分别给大家整理了Oracle和HIve开窗函数的相关知识,文见Hive分析函数整理(开窗函数、窗口函数)今天再进一步整理窗口函数常用四大类型用法测试数据集-- 建表CREATE DATABASE IF NOT EXISTS TEST;CREATE TABLE TEST.COSTITEM( NAME STRING,ORDERDATE DATE,COST STRING);-- 数据加载INSERT INTO TEST.COSTITEM VALUES ('jack','2020-0原创 2020-11-10 13:47:10 · 2949 阅读 · 2 评论 -
Hive SQL 计算引擎的切换
1、配置mapreduce计算引擎set hive.execution.engine=mr;2、配置spark计算引擎set hive.execution.engine=spark;3、配置tez 计算引擎set hive.execution.engine=tez;原创 2020-11-03 17:15:29 · 515 阅读 · 0 评论 -
hive -e 重定向,带warn日志的处理方式
有时候执行会包含 WARN 开头的日志WARN: The method class org.apache.commons.logging.impl.SLF4JLogFactory#release() was invoked.WARN: Please see http://www.slf4j.org/codes.html#release for an explanation.方式一: grep -vhive -e "hive sql 语句 " | grep -v "WARN" > 输出文原创 2020-10-14 16:52:41 · 1423 阅读 · 0 评论 -
Hadoop数仓迁移(一):distcp命令、分区修复
目前项目涉及需求:迁移Hadoop数仓(由CDH环境迁移到HDP环境),涉及到hive表的重建,以及hdfs上文件的迁移,以及元数据、分区表的修复。一、建表hive重建表比较简单,首先show create table tablename;然后把建表语句拷贝下来即可,这里要注意的是,特殊分割符和存储文件格式(默认textfile,项目中设计parquet格式),需要将分割语句和stored as XXX语句也加上。否则会导致hadoop集群的hive建表时的默认的存储格式不一致而报错这里我写了一个批量原创 2020-08-01 17:06:10 · 1755 阅读 · 0 评论 -
批量生成hive建表语句
这里以shell为例echo "==========导出test库所有的表结构=========="hive -e "use test;show tables;" > /data/tables.txtecho "==========逐行遍历表信息========="cat tables.txt |while read eachlinedoecho "==========批量生成建表语句========="hive -e "use test;show create table $eac原创 2020-07-30 19:16:55 · 1520 阅读 · 0 评论 -
Hive静态分区和动态分区
主需要建立一个备份带分区的数据表,拷贝时若采用静态分区方式需要写N行语句,因此可以使用动态分区,节省大量时间。Hive的分区方式:由于Hive实际是存储在HDFS上的抽象,Hive的一个分区名对应一个目录名,子分区名就是子目录名,并不是一个实际字段。一)hive中支持两种类型的分区:静态分区SP(static partition)动态分区DP(dynamic partition)...原创 2020-07-29 18:21:37 · 925 阅读 · 0 评论 -
Hive数据加载方式(insert、load;普通表、分区表)
方式一:load data基础语法:load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student[partition ]参数说明:1 load data: 表示加载数据2 local: 表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表3 inpath: 表示加载数据的路径相对路径,例如:project/data1绝对路径,例如:/user/hive原创 2020-07-28 17:39:49 · 9269 阅读 · 2 评论 -
hive 空值、NULL判断和处理方式
首先需要明白一个问题,空值和NULL值不是等价的。首先看一个表结构的例子由此可见,hive中空值判断基本分两种1.NULL 与 \Nhive在底层数据中如何保存和标识NULL,是由 alter table name SET SERDEPROPERTIES('serialization.null.format' = '\N'); 参数控制的比如:1.设置 alter table name SET SERDEPROPERTIES('serialization.null.format' = '\N'原创 2020-07-21 17:36:31 · 5707 阅读 · 0 评论 -
Hive数仓开发规范
为了避免底层业务变动对上层需求影响过大,屏蔽底层复杂的业务逻辑,尽可能简单、完整的在接口层呈现业务数据,建设高内聚松耦合的数据组织,使数据从业务角度可分割,显得尤为重要。从整个集团业务条线出发,形成数据仓库总体概念框架,并对整个系统所需要的功能模块进行划分,明确各模块技术细节,建设一套完整的开发规范。一、分层规范ODS(原始数据层):ODS层是数据仓库准备区,为DWD层提供基础原始数据。DWD(明细数据层):和ODS粒度一致的明细数据,对数据进行去重,脏数据过滤,空处理,保证数据质量。DWS(服务数原创 2020-05-21 20:33:21 · 1739 阅读 · 2 评论 -
Impala的invalidate metadata与refresh 区别
在Impala中,invalidate metadata与refresh语句都可以用来刷新表,但它们本质上还是不同的。一、Impala on Hive介绍我们一般会采用传统的MySQL或PostgreSQL数据库作为Hive的Metastore(元数据存储)组件。在CDH中默认是MySQL,我们可以通过show tables in hive语句清晰地看到Hive Metastore中的各个表。mysql> show tables in hive;+-----------------------原创 2020-05-17 16:39:33 · 1250 阅读 · 0 评论 -
Hive文件存储格式(建表stored as 的五种类型)
一、综述1.建表规范我们知道hive建表的完成格式如下: CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY(col_name data_typ原创 2020-05-17 16:10:37 · 29770 阅读 · 2 评论 -
Hive函数09_条件判断函数(IF,NVL,COALESCE,CASE WHEN)
1.if()语法格式:if(条件表达式,结果1,结果2)解释: 当条件为TRUE时,返回结果1;否则返回结果2,相当于java中的三目运算符,只是if后面的表达式类型可以不一样。hive> select if(a=a,’bbbb’,111) fromlxw_dual; bbbb hive> select if(1<2,100,200) from...原创 2020-05-07 14:16:35 · 1553 阅读 · 0 评论 -
SQL中exists和not exists语句的改写
在标准SQL语句中 NOT EXISTS 对应于EXISTS,EXISTS筛选剩下的数据就是 NOT EXISTS对应数据,有了这层思路,我们就可以对相关的NOT EXISTS进行改写了。exists 改写:inner join进行SQL改写之前,我们先来看一对简单的例子,大部分情况下,EXISTS对应于INNER JOIN-- 原语句SELECT T.* FROM TEST_EXI...原创 2020-04-28 11:25:29 · 2134 阅读 · 2 评论 -
Hive中case when不支持子查询的解决方法
在mysql等关系型数据库中,case when 函数在条件判断中是我们经常使用的函数。同样的hive中也有这个函数。但是由于hive不支持case when的子查询写法,所以我们需要改成join的语法。(ps:hive中也不支持exist或者not exist这种子查询用法)一、数据库中的通常用法如下,我简单写了一个业务场景数据准备:qy_clue_account 线索表 和 lnk_op...原创 2020-04-27 11:13:05 · 6414 阅读 · 1 评论 -
SQL中count(1)、count(*)与count(列名)的执行效率区别
执行效果上:count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。执行效率上:列名为主键,coun...原创 2020-04-23 17:06:04 · 1317 阅读 · 0 评论 -
Hive函数08_自定义函数(UDF、UDAF、UDTF)
一、系统内置函数几个常用命令--(1)查看系统 内置 函数hive>show functions;--(2)显示内置函数用法hive>desc function upper;--(3)详细显示内置函数用法hive>desc function extended upper;二、自定义函数1.简介Hive 自带了一些函数,比如: max/min 等,但是数量有限...原创 2020-04-23 10:41:59 · 685 阅读 · 0 评论 -
Hive函数07_行转列、列转行函数(实战版)
一、行转列CONCAT(string A/col, string B/col )返回输入字符串连接后的结果,支持任意个输入字符串CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT() 。第一个参数剩余参数间的分隔符。分隔符可以是与剩 余参数一样的字符串。如果分隔符是 NULL ,返回值也将为 NULL 。这个函数会跳过分隔符参数后的任...原创 2020-04-19 12:04:19 · 1024 阅读 · 1 评论 -
CDH常用优化配置_Hive配置
1.hive.metastore.warehouse.dirHive 仓库目录是在 HDFS 中存储 Hive 表格的位置。注意此仓库目录的 Hive 默认值为“/user/hive/warehouse”。2.hive.warehouse.subdir.inherit.perms让表目录继承仓库或数据库目录的权限,替代使用从 dfs umask 派生权限创建。该操作允许通过 Hive 将 ...原创 2020-04-13 17:23:03 · 3160 阅读 · 0 评论 -
Zeppelin 0.8.1安装部署
官网 http://zeppelin.apache.org/docs/0.8.1/quickstart/install.html(了解其它版本改一下版本号)1.下载Zeppelin 0.8.2安装包下载地址:http://archive.apache.org/dist/zeppelin/zeppelin-0.8.1//zeppelin-0.8.1-bin-all.tgz(下载其它版本改一下...原创 2020-03-26 16:46:42 · 918 阅读 · 0 评论 -
Hive中删除部分数据
背景:1、hive表删除数据不能使用DELETE FROM table_name 中SQL语句2、hive表删除数据要分为不同的粒度:table、partition、partition内一、有partition表1. 删除具体partitionalter table table_name drop partition(partiton_name='value'))2. 删除parti...原创 2020-03-18 14:14:46 · 5064 阅读 · 0 评论 -
Hive分区(partition)详解
一、背景1、在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。2、分区表指的是在创建表时指定的partition的分区空间。3、如果需要创建有分区的表,需要在create表的时候调用可选参数partitioned by,详见表创建的语法结构。二、技术细节1、一个表可以拥有一个或者...原创 2020-03-11 14:29:18 · 25408 阅读 · 0 评论 -
Hive优化的十大方法
Hive用的好,才能从数据中挖掘出更多的信息来。用过hive的朋友,我想或多或少都有类似的经历:一天下来,没跑几次hive,就到下班时间了。Hive在极大数据或者数据不平衡等情况下,表现往往一般,因此也出现了presto、spark-sql等替代品。这里重点讲解hive的优化方式,例如优化分组:set hive.auto.convert.join=true;优化表关联内存运行:/*+MAPJ...原创 2020-01-17 15:07:12 · 2512 阅读 · 0 评论 -
Hive函数06_多维计算函数(grouping sets,cube,roll up)
一、GROUPING SETSGROUPING SETS作为GROUP BY的子句,允许开发人员在GROUP BY语句后面指定多个统计选项,可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来,下面是几个实例可以帮助我们了解,grouping sets语句等价hive语句select device_id,os_id,app_id,count...原创 2020-01-06 14:36:20 · 950 阅读 · 0 评论 -
Hive函数05_细说CAST函数(数据类型转化)
一、Hive的隐式转换Hive内置数据类型由基本数据类型和复杂数据类型组成,基础类型例如下图所示的几种今天的话题是研究Hive数据类型之间的转换。hive同java一样,也包括隐式转换(implicit conversions)和显式转换(explicitly conversions)。我们举一个numeric类型的数隐式转换的例子,假如一个数据类型是INT型,另一个是SMALLINT类型...原创 2019-12-31 11:43:51 · 21603 阅读 · 0 评论 -
hive获取当前时间(类似sql的now()函数)
在大多数的sql中获取当前时间都是用now()函数即可,hive获取当前时间的函数与sql 不一样在impala中执行now()函数时是可以通过的然而在hive中执行now()函数却报错:hive有一个获得当前时区的UNIX时间戳:unix_timestamp语法: unix_timestamp()返回值: bigint说明: 获得当前时区的UNIX时间戳举例:我们需要的不是...原创 2019-11-03 15:19:06 · 42058 阅读 · 0 评论 -
hive课程笔记
1.hive架构hive通过mr(mapreduce)进行计算,通过hdfs进行存储。2.hive与数据库比较hive采用了类SQL的查询语言HQL,(1)查询:查询语言类似(2)存储位置:hive存储在hdfs,mysql存储在本地文件系统(3)数据更新由于hive是针对数据仓库设计的,而数仓的内容是读多写少的,因此hive中不建议对数据进改写,所有的数据都是在加载的时候确定好了...原创 2019-09-08 16:46:08 · 267 阅读 · 0 评论