雾岛与鲸的博客

Hive 中的视图和 RDBMS 中视图的概念一致，都是一组数据的逻辑表示，本质上就是一条 SELECT 语句的结果集。视图是纯粹的逻辑对象，没有关联的存储 ，当查询引用视图时，Hive才将视图的定义与查询结合起来。

最近在建设数据仓库，处理数据的过程中，经常反复使用hive的HQL语句，尽管HQL和SQL语言有很多相同之处，但也并不是说HQL就能通用SQL的语法。在使用过程中要尤为注意。事情经过是这样的，我在把业务系统数据同步到数仓(数据存储在Hive)中时，在数据汇总层(DWS)，对数据进行汇总处理时，发现有数据丢失的问题，经过排查，发现是在使用 引发的坑。

在日常开发中，使用 Hive sql 中的不等于判断符，即 和!= ，不小心踩了一个小坑， 和!= 都是会把 null 值的数据剔除掉的。如果我们希望保留 null 值，可以采取补全 null 值数据的方式，比如上述案例中：if(col1 is null,‘未知’,col1)通过以上案例的查询结果，我们可以发现，无论是使用 还是使用!= ，除了会剔除指定的条件外，还会把 null 值剔除了。首先分别测试使用 和!

可以使用explain查看一下执行计划，发现会使用UDF函数将两边的字段都转成double, 由于double类型精度问题，可能导致最终关联的结果和预期不符，数据可能会出现重复，也可能出现关联不上的情况。注: spark sql不存在这种问题。

使用正则替换:regexp_replace(需要处理的字段名称,'\\n|\\r|\\t','') as 重新命名

1.问题描述日常工作中我们经常会遇到一些非结构化数据，因此常常会将Lateral View 结合explode使用，达到将非结构化数据转化成结构化数据的目的，但是该方法对应explode的内容是有非null限制的，否则就有可能造成数据缺失。SELECT name,infoFROM ( SELECT name, split(info_list,',') as info_arrary FROM ( select '张三' as name,'1,

Hive中就是把Map，Reduce的Join拿过来，通过SQL来表示。参考链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins1、Common/Shuffle/Reduce JoinReduce Join在Hive中也叫Common Join或Shuffle Join如果两边数据量都很大，它会进行把相同key的value合在一起，正好符合我们在sql中的join，然后再去组合，如图所示:2、Map Jo

hive 中周和季度的操作假设 日期字段是order_time一周的第几天 : PMOD(DATEDIFF(order_time, ‘2012-01-01’), 7)一年的第几周: WEEKOFYEAR(order_time)季度 (hive <1.3): (INT((MONTH(order_time)-1)/3)+1)季度 (hive >=1.3): QUARTER(order_time)Hive中yyyymmdd和yyyy-mm-dd日期之间的切换方法1: from_uni

案例:with tab1 as (SELECT stack(3, '1001',50, '',60, NULL,70 ) as (id, score) ), tab2 as (select stack(3, '1001', 777, '', 999, null, 555) as (id, score) )select a.*, b.* from tab1 a full join tab2 b on a.id =

CPA、CPC、CPM、CVR、CTR、PV、UV 相关计算公式:ROI=订单额/消费量（即广告费用）=（单均额转化量）/（CPA转化量）=单均额/CPACTR=点击量/展现量CVR=转化量/点击量CPM=（消费量/展现量）*1000CPA=消费量/转化量=（CPC点击量）/（CVR点击量）=CPC/CVRCPC=消费量/点击量做网络营销的人，我们要知道以下常见名词解释：CPC (Cost Per Click): 按点击计费（平均点击价格）CPA (Cost Per Action): 按成

在表中有一堆学生记录。school stduent_id start_date end_date111 123 2010-02-03 2012-02-03222 345 2013-02-03 2014-02-03222 567 2015-02-03 2018-02-03我想计算每所学校每年有多少学生，比如从 2​​014 年一直到 2021 年。目前，我使用横向视图，但它是硬编码的：SELE

hive orc格式的表，使用注意事项测试表create table orc_part_tb(id int, name string) partitioned by (dt string) stored as orc;insert into orc_part_tb (dt = '01') select 1,'xiaoming' union all select 2,'xiaohong' union all select 3,'xiaozhang';使用orc格式的表可以使用alter table

表数据如下：user_id interest_arraytom [a,b,c,d,g,w]bob [e,d,s,d,g,w,s]cat [a]harry []peter NULL目的是按顺序选择每行“interest_array”中的前 3 个元素并将其作为数组返回，输出如下：user_id output_arraytom [a,b,c]bob [e,d,s]cat [a]harry []peter

字符串结构如下:[{"name":"john","id":12,"location":"delhi"},{"name":"raj","id":18,"location":"mumbai"},{"name":"Rahul","id":14,"location":"hyd"}]需要分解它以创建一个新表，其中列名作为name、id 和location。实现方式首先将字符串转换为 JSON 对象数组：删除方括号，在大括号之间用逗号分隔并展开。然后使用带有横向视图的 json_tuple 来提取所有值。w

我正在尝试在 Hive 中的多列中分解记录。例如，如果我的数据集如下所示COL_01 COL_02 COL_031 A, B X, Y, Z2 D, E, F V, W我想要的结果是:COL_01 COL_02 COL_031 A X1 B Y1 NULL Z2 D V2 E W2 F

我有一个从系统日志中提取的配置单元表。数据以一种奇怪的格式（映射数组）编码，其中数组的每个元素都包含field_name和它的value。列类型为 STRING。就像在下面的例子中一样：select 1 as user_id, '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_infounion allselect 2 as user_id, '[{"field":"gender", "value"

对于下面的示例，如果我使用下面的相同数据，并且我希望 Mary 和 Peter 帐户在同一日期范围内，我将如何修改 hive sql 查询来执行此操作？例如，将日期范围设置在“2021-05-24”和“2021-06-03”之间，并填充此期间的所有余额。如果我们以 Mary 为例，我还希望看到 Mary 可用余额 ‘53028.1’ 向前填充到 ‘2021-06-03’ 并且如果 Mary 在 ‘2021-05-24’ 上没有值’ 将其填充 ‘50000’ 的余额。with mytable as (--D

我花了几天的时间试图弄清楚如何在 Hive 中使用最近的值添加到缺失的日期中，但没有成功。原始表目前看起来像下表：account name,available balance,Date of balance Peter,50000,2021-05-24Peter,50035,2021-05-25Peter,50035,2021-05-26Peter,50610,2021-05-28Peter,51710,2021-06-01Peter,53028.1,2021-06-02Peter,53

我有一个日期列yyyy_mm_dd。我想使用以下逻辑基于它创建一个附加列：如果 yyyy_mm_dd = 一个月的最后一天，则 1如果 yyyy_mm_dd 不是该月的最后一天而是数据集中的最大日期，则为 1否则为 0我能够想出这个，它适用于逻辑的第 1 部分和第 3 部分：select *, case when yyyy_mm_dd == last_day(yyyy_mm_dd) then 1 when yyyy_mm_dd != last_d

最近在做mysql 入hive数仓的工作，由于业务表数量较大，单独写hive DDL太过耗时，就找到了如下方法。准备一张维度表：dim_ddl_convert，建表语句如下：CREATE TABLE dim_ddl_convert ( source VARCHAR(100) NOT NULL, data_type1 VARCHAR(100) NOT NULL, target VARCHAR(100) NOT NULL, da

方法一round(column_name,2)四舍五入截取(这种方法慎用，有时候结果不是你想要的)select round(645342.875645342,2);+------------+--+| _c0 |+------------+--+| 645342.88 |+------------+--+方法二cast(column_name as decimal(10,2))cast函数截取（推荐使用）select cast(645342.8756

Hive 配置动态分区insert into table xxx partition(xxxx)select ...使用动态分区时首先需要的一些配置：是否开启动态分区 hive.exec.dynamic.partition动态分区是否使用严格模式 hive.exec.dynamic.partition.modeMR总共可创建最大分区数 hive.exec.max.dynamic.partition.partitions（默认1000）当前节点可创建的最大分区数 hive.exec.max.

场景： 数据库有有两个字段.开始时间，和结束时间，指定一个时间段（a,b），a表示开始时间，b表示结束时间。看数据库中有没有与(a,b)冲突的时间段，有的话就返回那条记录。***解析：***两个时间段相当于两个集合，不过是有顺序的集合。两个时间段有交集细分有四种情况。用sql直接判断无交集的语句可能也有，但是目前没有想到，只想到有交集的语句，如果返回不为空则表明有交集，否则没有交集。sql语句：select * from test_tablewhere (startTime > a AND

mysql中的group_concat分组连接功能相当强大，可以先分组再连接成字符串，还可以进行排序连接。但是hive中并没有这个函数，那么hive中怎么实现这个功能呢？这里要用到：concat_ws函数和collect_list、collect_set 函数。建立测试表（无分区表）：create table if not exists db_name.test_tb( id string, content string, comment string) row format delimi

hive支持struct,map,array三种集合类型1、struct与C语言、golang中的struct类似,可以通过.语法来访问定义一个包含struct字段的表create table test2( field1 struct<name:string,age:int> comment "test field") row format delimited fields terminated by "," collection items terminated by ":.

一、Hive的几种数据模型内部表 (Table 将数据保存到Hive 自己的数据仓库目录中：/usr/hive/warehouse)外部表 (External Table 相对于内部表，数据不在自己的数据仓库中，只保存数据的元信息)分区表 (Partition Table将数据按照设定的条件分开存储，提高查询效率，分区-----> 目录)桶表 (Bucket Table本质上也是一种分区表，类似 hash 分区 桶 ----> 文件)视图表 (视图表是一个虚表，不存储数据，用来

总结一下：两种表达方式是等效的方法一：case when tb1.os = 'android' then 'android'when tb1.os = 'ios' then 'iPhone'else 'PC'end as os,方法二：case tb1.oswhen 'android' then 'android'when 'ios' then 'iPhone'else 'PC'end as os,注意: 无论是单独使用还是结合聚合函数 sum/count(distinct)/

str_to_map(字符串参数, 分隔符1, 分隔符2)使用两个分隔符将文本拆分为键值对。分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’。使用案例:select str_to_map('aaa:11&bbb:22', '&', ':')结果:{"bbb":"22","aaa":"11"}将 json 字符串转变为 map使用案例:select pro_map, pro_map[

hive中sql执行顺序fromjoin on 或 lateral view explode(需炸裂的列) tbl as 炸裂后的列名wheregroup by （开始使用select中的别名，从group 开始往后都可用）聚合函数 如Sum() avg() count(1)等havingselect 中若包含over（）开窗函数，执行完非开窗函数后select等待执行完开窗函数，然后执行select完，开窗函数通过表数据进行分区和排序，跟select查询中的字段是平行关系，不依赖查询字段。

常见的聚合操作count计数count(*) 所有值不全为NULL时，加1操作count(1) 不管有没有值，只要有这条记录，值就加1count(col) col列里面的值为null，值不会加1，这个列里面的值不为NULL，才加1sum求和:sum(可转成数字的值） 返回bigintavg求平均值:avg（可转成数字的值）返回doubledistinct不同值个数count(distinct col)...

distribute by与group by，order by与sort by , cluster by的区别distribute by与group by 的区别order by与sort by 的区别cluster bydistribute by与group by 的区别都是按key值划分数据 都使用reduce操作唯一不同的是，distribute by只是单纯的分散数据，distribute by col – 按照col列把数据分散到不同的reduce。而group by把相同key的数据聚集

一、问题跑spark或hive脚本报错如下：[INFO] 2020-03-31 11:06:03 -> java.io.IOException: Unable to close file because the last block does not have enough number of replicas. at org.apache.hadoop.hdfs.DFSOutputStream.completeFile(DFSOutputStream.java:2266) at org

在传统关系型数据库中，group by与count(distinct)都是很常见的操作。count(distinct colA)就是将colA中所有出现过的不同值取出来，相信只要接触过数据库的同学都能明白什么意思。count(distinct colA)的操作也可以用group by的方式完成，具体代码如下：select count(distinct colA) from table1;select count(1) from (select colA from table1 group by col

目录size(Map[K,V])size(Array[V])map_keys(Map[K.V])map_values(Map[K.V])array_contains(Array[T], value)sort_array(Array[T])小结size(Map[K,V])解释返回 Map 类型中的元素数。使用案例select size(map类型参数); -- 返回map中的元素个数size(Array[V])解释返回 Array 类型中的元素数。使用案例selec

目录列裁剪和分区裁剪谓词下推sort by代替order bygroup by代替distinctgroup by配置调整map端预聚合倾斜均衡配置项join基础优化build table（小表）前置多表join时key相同利用map join特性分桶表map join倾斜均衡配置项优化SQL处理join数据倾斜空值或无意义值单独处理倾斜key不同数据类型build table过大MapReduce优化调整mapper数调整reducer数合并小文件启用压缩JVM重用并行执行与本地模式严格模式采用合适的存储

经常看到一些Hive优化的建议中说当小表与大表做关联时，把小表写在前面，这样可以使Hive的关联速度更快，提到的原因都是说因为小表可以先放到内存中，然后大表的每条记录再去内存中检测，最终完成关联查询。这样的原因看似合理，但是仔细推敲，又站不住脚跟。多小的表算小表？如果所谓的小表在内存中放不下怎么办？我用2个只有几条记录的表做关联查询，这应该算是小表了，在查看reduce的执行日志时依然是有写磁盘的操作的。实际上reduce在接收全部map的输出后一定会有一个排序所有键值对并合并写入磁盘文件的操作。写入磁盘

如果某个key下记录数远超其他key，在join或group的时候可能会导致某个reduce任务特别慢。本文分析下join的场景。本例子SQL如下: 查询每个appid打开的次数，需要排除掉作弊的imei。select appid, count(*) from ( select md5imei, appid from ( select t1.md5imei, t1.appid, t2.cheat_flags from imei_open_app t1 left join cheat_

目录笛卡尔积处理测试案例笛卡尔积处理当Hive设定为严格模式（hive.mapred.mode = strict）时，不允许在HQL语句中出现笛卡尔积，这实际说明了Hive 对笛卡尔积支持较弱。因为找不到 join key, Hive只能使用一个reducer 来完成笛卡尔积。当然也可以使用 limit 的办法来减少某个表参与 join 的数据量，但对于需要笛卡尔积语义的需求来说，经常是一个大表和一个小表的join操作，结果仍然很大，以至于无法用单机处理，这时Map Join才是最好的解决办法。Map

hive里通常通过collect_set和collect_list来进行列转行，其中collect_list为不去重转换，collect_set为去重转换。创建一个测试表CREATE table stu_score( stu_id string comment '学号', stu_name string comment '姓名', course string comment '科目', score string comment '分数') comment '学生成绩表';数据如下:列转

目录一. explode, 行转列。1.1. 用于array类型的数据1.2. 用于map类型的数据二. explode函数的局限性三. lateral view3.1 第一种形式四. 行转列一. explode, 行转列。1.1. 用于array类型的数据table_name 表名array_col 为数组类型的字段new_col array_col被explode之后对应的列select explode(array_col) as new_col from table_name1.2