清平乐的技术博客
学如逆水行舟,不进则退。
展开
-
Hive函数07_行转列、列转行函数(实战版)
一、行转列CONCAT(string A/col, string B/col )返回输入字符串连接后的结果,支持任意个输入字符串CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT() 。第一个参数剩余参数间的分隔符。分隔符可以是与剩 余参数一样的字符串。如果分隔符是 NULL ,返回值也将为 NULL 。这个函数会跳过分隔符参数后的任...原创 2020-04-19 12:04:19 · 1008 阅读 · 1 评论 -
数据库的DDL、DML、DQL、DCL名词详解【全套笔记】
简言之:1.DDL(DataDefinitionLanguage):数据定义语言,用来定义数据库对象:库、表、列等;2.DML(DataManipulationLanguage):数据操作语言,用来定义数据库记录(数据);3.DQL(DataQueryLanguage):数据查询语言,用来查询记录(数据);4.DCL(DataControlLanguage):数据控制语言,用来定义访问权限和安全级别。举例说明1.DDL用来定义数据库对象:库、表、列等;适用范围:对数据库中的某些对象(.原创 2020-04-21 14:53:00 · 25555 阅读 · 11 评论 -
【数仓设计】企业数仓为什么要进行分层?(六大好处)
后来随着公司业务的发展,数据的沉淀,数据仓库发展到一定阶段,发现数据的使用杂乱无章,各种业务都是从原始数据直接计算而得。当然你公司数据规模小,非不分层可不可以,当然可以。而且便于维护数据的准确性,当数据出现问题之后,可以不用修复所有的数据,只需要从有问题的步骤开始修复。简单来讲可以这样理解,我们最终给业务诚信的是一能直接使用的张业务表,但是它的来源有很多,如果有一张来源表出问题了,我们希望能够快速准确地定位到问题,并清楚它的危害范围。规范数据分层,开发一些通用的中间层数据,能够减少极大的重复计算。...原创 2022-08-09 09:15:28 · 1511 阅读 · 0 评论 -
Impala时间函数总结
Impala是基于hive并使用内存进行计算,兼顾数据仓库,具有实时,批处理,多并发等优点。impala元数据都存储在hive的metastore当中,并且impala兼容hive的绝大多数sql语法。所以需要安装impala的话,必须先安装hive,保证hive安装成功,并且还需要启动hive的metastore服务。............原创 2022-07-27 16:42:29 · 1143 阅读 · 0 评论 -
Presto连接Clickhouse
1. 首先安装presto安装教程可以参考官网及下面这篇文章官方文档presto安装部署完毕可以连接mysql测试一下是否安装成功2. 下载presto-clickhouse这里已有大佬实现presto-clickhouse的Connectors,我们直接下载来用就好https://github.com/zeeshanabid94/presto/tree/presto-clickhouse/presto-clickhouse3. 编译presto-clickhouse我们只需要编译pres原创 2022-04-24 20:51:32 · 1244 阅读 · 0 评论 -
窗口函数深度探索(一):底层原理
在日常SQL数据分析中,经常会遇到需要在每组内排名,面对这类需求就需要使用sql的高级功能窗口函数了。一言以蔽之:在进行分组聚合以后 , 我们还想操作集合之前的数据就需要用到窗口函数原创 2022-05-05 19:49:37 · 1778 阅读 · 0 评论 -
窗口函数深度探索(二):控制窗口大小(UNBOUNDED、CURRENT、PRECEDING、FOLLOWING)
本文详细介绍窗口函数进行控制窗口大小的具体操作,博主分别列举了8个实际案例进行具体讲解。通过此文大家可以熟练使用这几个函数进行数仓开发和数据分析。原创 2022-05-06 16:59:01 · 4286 阅读 · 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 · 5114 阅读 · 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 · 5839 阅读 · 0 评论 -
Hive数仓开发规范
为了避免底层业务变动对上层需求影响过大,屏蔽底层复杂的业务逻辑,尽可能简单、完整的在接口层呈现业务数据,建设高内聚松耦合的数据组织,使数据从业务角度可分割,显得尤为重要。从整个集团业务条线出发,形成数据仓库总体概念框架,并对整个系统所需要的功能模块进行划分,明确各模块技术细节,建设一套完整的开发规范。一、分层规范ODS(原始数据层):ODS层是数据仓库准备区,为DWD层提供基础原始数据。DWD(明细数据层):和ODS粒度一致的明细数据,对数据进行去重,脏数据过滤,空处理,保证数据质量。DWS(服务数原创 2020-05-21 20:33:21 · 1722 阅读 · 2 评论 -
Hadoop 数据仓库规范
对于一个公司或者组织来说,使用数据的用户可能成百上千,如何降低大家对于数据使用的沟通成本、 何通过规范大家的行为来降低使用数据的风险,这些问题是必须加以考虑的。在实际实践中,通常用数据仓库 规范来达到 目的 数据仓库的规范包括很多方面,如数据命名规范、开发规范、流程规范、安全规范和质量规范等,下面将结合 FutureRetailer零售平台介绍常用的命名、开发和流程规范。一、命名规范命名规范主要分为表命名规范和字段命名规范等,下面分别介绍1.表命名规范表命名规范是为 了让数据所有相关方对于表原创 2021-08-25 13:41:22 · 334 阅读 · 0 评论 -
浅谈金融体系数据治理
这几年随着全球数据应用的热潮,数据治理的话题也在最近被越来越多地提及和讨论。过去的十年,银行的IT系统经历了数据量高速膨胀的时期,这些海量的、分散在不同角落的异构数据导致了数据资源的价值低、应用难度大等问题。同时,银行内部的业务条线或行政分化也在不断地制造着银行数据交互的断层,而银行与外部业务交互所产生的“体外循环”数据与企业的核心数据体系并不能自然地融合,这个时候数据治理体系建设可能不是银行的一个选择,而是唯一的出路。一、数据治理规划银行数据向来以量大质优而著称,但是实际情况是它比其他行业好一些,但原创 2021-02-23 11:34:08 · 689 阅读 · 0 评论 -
Hadoop数仓迁移(二):超大表数据同步方案(上亿数据量,文件100G以上)
博主在这段时间一直负责公司平台迁移工作,目前项目已接近尾声。数仓迁移过程中,需要将老平台(CDH)数据迁移到新平台(HDP),然而老集群数仓DW层中存在几张上亿条数据(文件100G以上)的大表,直接采用Distcp命令同步耗时耗资源原创 2020-12-14 09:33:37 · 1639 阅读 · 1 评论 -
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 · 1722 阅读 · 0 评论 -
离线数仓开发——细说维度建模技术
相比操作性数据库基于三范式数据建模的一统江湖,分析性数据库自数据仓库的概念诞生以来,就存在两种得到广泛认可构建数据仓库的方法,即 BillInmon (被称为“数据仓库之父”)的企业信息 工厂模式和 Ralph Kimball (被称为“商业智能之父”)的维度建模模式。目前实际应用中,Kimball的维度建模理论在实践中使用得最为广泛,尤其在互联网行业,互联网和移动互联网行业业务变化快 系统变化快,相应的数据变化也快,数据模型经常需要修改和重构,Kimball 的方法可以迅速响应业务需求,快速构建一个数据原创 2020-12-08 09:35:26 · 721 阅读 · 0 评论 -
【数仓设计】数据仓库逻辑分层(最新版)
一、分层必要性清晰数据结构:每一个数据分层都有它的作用域,这样我们在使用表的时候能更方便地定位和理解脏数据清洗:屏蔽原始数据的异常屏蔽业务影响:不必改一次业务就需要重新接入数据数据血缘追踪:简单来讲可以这样理解,我们最终给业务呈现的是能直接使用的一张业务表,但是它的来源有很多,如果有一张来源表出问题了,我们希望能够快速准确地定位到问题,并清楚它的危害范围。减少重复开发:规范数据分层,开发一些通用的中间层数据,能够减少极大的重复计算。把复杂问题简单化。将一个复杂的任务分解成多个步骤来完成,每一.原创 2020-07-15 13:43:35 · 1576 阅读 · 2 评论 -
浅谈大数据平台的数据治理
近几年大数据风生水起,随着大数据业务的不断开展,各大互联网公司包括一些传统企业都越老越重视数据价值的挖掘。一、数据治理模型在公司的日常运行中,各种数据分析和数据挖掘技术,为公司发展决策和业务开展提供数据支持。以某互联网公司为例,公司内部也形成了一套完善的数据治理方案,核心就是由大数据平台+数据仓库+数据治理平台+数据监控平台来实现数据治理。大数据平台支撑整个大数据的运行环境数据仓库整合各个业务线的数据,消灭数据烟囱数据治理平台提供统一指标管理、统一维度管理、统一数据出口管理数据质量负责监控数原创 2020-07-06 19:48:33 · 1770 阅读 · 0 评论 -
【数仓设计】行为数仓分层案例ODS& DWD& DWS&DM& ADS
本案例只是举例了一个通常的模型,有的项目会比现有的层数多或者少。一、数仓分层ODS: Operation Data Store原始数据DWD(数据清洗/DWI) data warehouse detail数据明细详情,去除空值,脏数据,超过极限范围的明细解析具体表PS:DWB: data warehouse base 基础数据层,有的会进一步细分额外加一层 ,存储的是客观数据,一般用作中间层,可以认为是大量指标的数据层。DWS(宽表-用户行为,轻度聚合) data warehouse se原创 2020-05-21 16:31:27 · 6577 阅读 · 0 评论 -
结构化数据、半结构化数据和非结构化数据
在实际应用中,我们会遇到各式各样的数据库如nosql非关系数据库(memcached,redis,mangodb),RDBMS关系数据库(oracle,mysql等),还有一些其它的数据库如hbase,在这些数据库中,又会出现结构化数据,非结构化数据,半结构化数据。在大数据环境下,存在很多数据以非结构化,或者半结构化的状态呈现,如图片,声音,视频以及文本等。从采集,存储,分析和解释的角度而言,目...原创 2020-04-21 15:26:36 · 2383 阅读 · 0 评论 -
【数仓设计】宽表和窄表
宽表:从字面意义上讲就是字段比较多的数据库表。通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。由于把不同的内容都放在同一张表存储,宽表已经不符合三范式的模型设计规范,随之带来的主要坏处就是数据的大量冗余,与之相对应的好处就是查询性能的提高与便捷。这种宽表的设计广泛应用于数据挖掘模型训练前的数据准备,通过把相关字段放在同一张表中,可以大大提高数据挖掘模型训练过程中迭代计算时的效率问...原创 2019-09-10 13:14:13 · 2117 阅读 · 0 评论 -
【数仓设计】(一):数仓拉链表的使用和设计
全文由下面几个部分组成:第一部分:先分享一下什么是拉链表,以及拉链表的应用场景;第二部分:通过两个使用案例来对拉链表做近一步的阐释第三部分:举一个具体的应用场景,来设计并实现一份拉链表,最后并通过一些例子说明如何使用我们设计的这张表 (因为现在Hive的大规模使用,我们会以Hive场景下的设计为例)。最后:分析一下拉链表的优缺点,并进行补充说明,比如说拉链表和流水表的区别。一、拉链表的定...原创 2020-04-18 14:24:44 · 1297 阅读 · 0 评论 -
【数仓设计】数仓建模的四种方法
一.数据仓库建模的目的为什么要进行数据仓库建模?大数据的数仓建模是通过建模的方法更好的组织、存储数据,以便在 性能、成本、效率和数据质量之间找到最佳平衡点。一般主要从下面四点考虑访问性能:能够快速查询所需的数据,减少数据I/O数据成本:减少不必要的数据冗余,实现计算结果数据复用,降低大数 据系统中的存储成本和计算成本使用效率:改善用户应用体验,提高使用数据的效率数据质量:改善数据统计口...原创 2020-04-17 10:55:30 · 6778 阅读 · 0 评论 -
数据倾斜及解决办法
一、什么是数据倾斜简单来说数据倾斜就是数据的key 的分化严重不均,造成一部分数据很多,一部分数据很少的局面。举例说明:举个 word count 的入门例子: 它的map 阶段就是形成 (“aaa”,1)的形式,然后在reduce 阶段进行 value 相加,得出 “aaa” 出现的次数。若进行 word count 的文本有100G,其中 80G 全部是 “aaa” 剩下 20G 是其...原创 2020-01-17 15:18:45 · 2181 阅读 · 1 评论 -
数据预处理06_ 数据变换和数据离散化
1.策略概述光滑:去掉数据中的噪声,包括分箱、回归和聚类。属性构造(特征构造):可以由给定的属性构造新的属性并添加到属性集中。聚集:把数据进行汇总或聚集。例如:可以聚集日销售数据,计算月和年销售量。规范化:把属性数据按比例缩放,使之落入指定区间。离散化:数值属性(例如:年龄)的原始值用区间标签或者概念标签替换。这些标签可以递归的组织成更高层概念,导致数值属性的概念分层。由标称数据产生...原创 2019-08-22 09:57:26 · 656 阅读 · 0 评论 -
数据预处理05_ 数据归约
数据归约数据集非常大的时候在海量数据上进行复杂的数据分析和挖掘需要很长的时间,使得这种分析不现实或者不可行 。数据归约:可以用来将得到的数据集进行归约表示,它小的多,但是仍然接近于保持原始数据的完整性。在归约后的数据集上挖掘将更有效,仍然产生相同(或者几乎相同)的分析结果。1.数据归约策略概述数据归约包括维归约、数量归约和数据压缩。(1)维归约(dimensionality redu...原创 2019-08-22 09:50:45 · 2398 阅读 · 0 评论 -
数据预处理04_ 数据集成
数据集成数据集成就是将多个数据源中的数据合并,存放在一个一致的数据存储中:如存放在数据仓库中。这些数据源可能包括多个数据库、数据立方体或一般文件。1.实体识别问题:实体识别问题就是要合理匹配来自多个信息源的等价实体。例如:如何确信一个数据库中的customer_id和另一个数据库中的cust_number指的是相同的属性?每个属性的元数据包括名字、含义、数据类型和属性的允许取值范围,以及...原创 2019-08-22 09:39:23 · 910 阅读 · 0 评论 -
数据预处理03_ 数据清理
现实世界的数据一般是不完整的有噪音的和不一致的。数据清理是填充缺失值、光滑噪声并识别离群点。1.缺失值处理方式(1)忽略元组:当缺少类标号的时候通常这样做(假定挖掘任务设计分类),除非元组有多个属性值缺少,否则该方法不是很有效。(2)人工填写缺失值:费时费力,且需要数据集小(3)使用一个全局常量填充将缺失的属性值用同一个常量(如:unknown)替换。如果缺失的值都用如“unknow...原创 2019-08-22 09:36:04 · 531 阅读 · 0 评论 -
数据预处理02_数据预处理的步骤
数据清理–>数据集成 —>数据归约–>数据变换1.数据清理就是处理脏数据,包括填写缺失值、清除噪声数据(降噪)、纠正不一致数据、识别或删除离群点等。常用工具例如:ETL工具2.数据集成(data integration)集成多个数据库数据。将数据由多个数据源合并成一个一致数据存储,如:数据仓库。【注意】不同数据库相同字段可能具有不同的名字,导致不一致和冗余,所以在为数仓...原创 2019-08-22 09:33:53 · 2561 阅读 · 0 评论 -
数据预处理的步骤
数据清理–>数据集成 —>数据归约–>数据变换1.数据清理就是处理脏数据,包括填写缺失值、清除噪声数据(降噪)、纠正不一致数据、识别或删除离群点等。常用工具例如:ETL工具2.数据集成(data integration)集成多个数据库数据。将数据由多个数据源合并成一个一致数据存储,如:数据仓库。【注意】不同数据库相同字段可能具有不同的名字,导致不一致和冗余,所以在为数仓...原创 2019-12-08 13:26:54 · 1061 阅读 · 0 评论 -
数据预处理01_脏数据产生的原因
数据预处理01_脏数据产生的原因一、预处理概述(1)现状:现实世界的数据库极易受到噪声、缺失值和不一致数据的侵扰,因为数据库太大并且多半来源于异种数据源。(2)数据质量要求准确性、完整性、一致性、实效性、可信性和可解释性。二、脏数据产生的原因收集数据设备故障人或计算机的错误导致数据输入时出现个人不希望提交个人信息时强制输入不正确值(掩盖的缺失数据)数据传输中出现错误错误的命名约...原创 2019-08-21 17:46:35 · 3293 阅读 · 0 评论 -
数仓知识10_数据泛化
数据泛化(面向属性的归纳;个性–>一般化)1.定义数据泛化:把较低层次的概念层(例如:年龄的数值范围)用较高层次的概念(例如:青年、中年和 老年)替换来汇总数据。或者通过减少维度在设计较少维度的概念空间汇总数据(例如汇总学生组群时,删除生日和电话号码属性)2.两种泛化方法(1)基于数据立方体的数据聚集(data focusing):a.复杂数据类型和聚集数据仓库和OLAP工具基于...原创 2019-08-19 17:14:50 · 1628 阅读 · 0 评论 -
数仓知识09_数据仓库的实现(计算和OLAP)
数据仓库海量数据–>OLAP服务器在数秒内回答决策支持查询因此数仓要支持高校的数据立方体计算技术、存取方法和查询处理技术1.数据立方体的有效计算核心:有效计算多维集合上的聚集(分组),每个聚集(分组)用一个方体表示,分组的集合形成定义数据立方体的方体的格。(1)computer cube操作和维灾难一种方法是扩充SQL使之包含computer cube操作,这个操作在操作制定的维的...原创 2019-08-19 17:12:58 · 431 阅读 · 0 评论 -
数仓知识08_数仓设计步骤和使用
1.数仓优势提供相关信息进行分析和性能估计,提高行业竞争优势提高企业生产力。可以快速收集准确描述组织机构的信息有利于客户联系管理,因为他跨越所有商务、所有部门和市场可以降低成本。通过可靠的方式进行长期趋势跟踪,发现异常。2.商务分析框架(4种视图)建立数仓前需要分析商务需求,搭建商务分析框架。a.自上向下视图:选取数仓所需信息,包括现在和未来的相关需求b.数据源视图:被操作的...原创 2019-08-19 17:10:06 · 509 阅读 · 0 评论 -
数仓知识07_数据仓库建模(数据立方体和三种模式)
数据仓库建模(数据立方体和三种模式)1.数据立方体(data cube)(1)常用概念数据立方体:数据立方体允许以多维对数据建模和观察。实际是N维结构,可以简单看做3-D集合结构维:一个单位想要记录的透视或者实体例如:一个数仓sales记录商店的销售设计维time、item、branch和location维度表:每个维都可以有一个与之相关联的表,里面会有相关的属性(字段)该表称作维表...原创 2019-08-19 16:53:02 · 2941 阅读 · 0 评论 -
数仓知识06_数仓的三层结构和三种模型
一、三层结构(1)底层:仓库数据库服务器(基本是一个关系型数据库)由ods或者其他外部数据源提取数据,放入底层。ETL工具和程序进行数据提取、数据清洗和变换(例如:将来自不同数据源的数据合并成一致的格式),已经装入和刷新,以更新数据仓库。数据提取方式:信关程序a.信关(使用后端程序或者工具提取数据):信关由DBMS支持,允许产生SQL代码例如:微软的ODBC(开放数据库连接)、OL...原创 2019-08-19 16:45:36 · 1730 阅读 · 0 评论 -
数仓知识05_认识OLAP
1.OLAP和OLTPOLAP 数据分析的目标则是探索并挖掘数据价值,作为企业高层进行决策的参考,通常被称为OLAP(On-Line Analytical Processing,联机分析处理) .OLTP 业务类系统主要供基层人员使用,进行一线业务操作,通常被称为OLTP(On-Line Transaction Processing,联机事务处理)。 从功能角度来看,OLTP负责基本业务的正常...原创 2019-08-19 16:41:01 · 226 阅读 · 0 评论 -
数仓知识04_为什么要ODS和数仓分离
a提高两个数据库系统的性能数据仓库查询复杂需要特殊的多维视图的数据组织、存储方法;在操作数据库中操作OLAP会降低性能b.操作数据库支持多事务并发而OLAP查询只需要汇总和聚集数据,对数据只读访问c.数仓和操作数据库两种系统中的数据结构、内容、用法不同...原创 2019-08-19 16:34:08 · 782 阅读 · 0 评论 -
数仓知识01_数据仓库和元数据库
1.基础概念数据仓库是一种数据库,他与单位对的操作数据库分别维护数据仓库系统将各种应用系统集成在一起,为统一的历史数据分析搭建平台,对信息处理提供支持。数据仓库是面向主题的、集成的、时变的、非易失的数据集合,支持管理者的决策过程。2.数据仓库的四个特征(1)面向主题的(subject-oriented)数仓围绕一些重要主题,如顾客、供应商、产品和销售组织(2)集成的(integra...原创 2019-08-19 16:31:28 · 637 阅读 · 0 评论 -
数仓知识03_基于ODS的三种模型
一、前言传统数据库( Database, DB) 在联机事务处理( OLT P) 中获得了较大的成功, 但是对管理人员的决策分析要求却无法满足, 数据仓库( Data Warehouse, DW) 能把日常业务处理中所收集到的各种数据转为具有商业价值的信息, 从而为企业决策分析提供了强大的技术支持。传统的数据仓库系统是两层的DB- DW 体系结构, 该结构不能涵盖企业所有的数据处理要求, 如:...原创 2019-08-19 11:07:23 · 808 阅读 · 0 评论 -
数仓知识02_相关名词解释(英文缩写)
随着大数据的到来,经常听到相关的词汇,维度、指标、BI、PV、UV等等,今天整理了这些词汇。DWDW是Data Warehouse的缩写,即数据仓库。DW要区别于普通数据库,数据仓库用于支持决策,面向分析型数据处理;而普通数据库主要服务于软件/网站,对于一致性/事物要求较高。BIBI是Business Intelligence的英文缩写,中文解释为商务智能,用来帮助企业更好地利用数据提高...原创 2019-08-08 14:05:21 · 1101 阅读 · 0 评论