以hadoop 作为基础生态，从0到进行数仓建设，主要分为基础篇和实战篇两部分，基础篇主要是各种组件的学习和案例，实战篇主要是三家企业的数仓设计案例，最后是扩展篇主要是实时数仓。

但是我们今天遇到的问题是，使用的工具不支持grouping sets，既然不支持grouping sets 那肯定就不支持cube 和rollup 了，所以这里我给大家介绍两个替代方案。提到不同纬度聚合，大家想到的肯定是grouping sets，或者是cube和rollup 其实这些我们之前都讲过，可以看看之前的文章。三个维度，你可以理解为订单表，这里我们就计算金额吧，需求是计算出每个产品的总金额，同时计算出每个品牌的总金额。这是一个小伙伴提供的思路，我自己试了一下，可以的。

selectfromshop2我们可以看到null 排在了最前面selectfromshop2窗口函数中的 case when，可以放在不同的地方，总结下来就三处窗口函数前面窗口函数参数重窗口函数定义中。

整体上来说不是很好理解，我们简单总结一下order by 会影响窗口的大小order by 加上过滤条件只有满足条件的数据才能进入窗口计算，其实就是窗口函数的where。

rows between 定义窗口的大小是根据当前行的位置来的，例如前面几行后面几行，是以当前行的位置为锚点进行计算，range between 定义窗口的大小是根据当前行的值来的，比这个值小n的行，比这个值大n的行，是以当前行的值为锚点进行计算举个例子方便理解假设当前行的位置是10，rows between 2 and 3 ,表示的是当前行的前面两行和后面三行，也就是第8行到第13行，总共6行。

URL 的解析函数，在我们进行流量分析的时候其实非常有用，否则的话我们可能需要借助正则匹配来完成这样的功能。

rlike regexp 是一样的，都是正则匹配REGEXP_REPLACE 是正则替换REGEXP_REPLACE 是正则提取Translate 是字符替换like 是字符匹配，有自己的语法。

之后，您可以使用数组操作函数来操作数组类型。数组函数将自然语言文本的字符串分割成单词和句子，每个句子在适当的句子边界处断开，并作为单词数组返回。函数根据数组元素的自然排序对输入数组进行升序排序并返回。例如，考虑以下示例对数组字符串进行排序并返回排序后的数组。例如，以下示例仅从键值对 map 类型中返回键。例如，以下示例仅从键值对 map 类型中返回值。例如，考虑以下示例，根据逗号分隔符分割字符串。函数在 map 类型上工作并返回键值数组。例如，考虑以下示例，根据整数值创建数组。

在本文中，我们将讨论如何在 Hive 事务性表上使用更新连接。您可以使用 merge 语句作为 Hive 更新连接的替代方案。您也可以在不设置 ACID 属性的情况下更新 Hive 表。Apache Hive 支持涉及您正在更新的单张表的简单更新语句。您可以在 SET 子句中仅使用静态值使用 Hive 更新语句。例如，考虑以下使用两张表的更新语句。这类语句称为更新连接条件。例如，考虑以下示例，使用 MERGE 语句作为使用连接条件更新表的替代查询。实际上，更新语句要复杂得多，涉及两张或更多表。

Hive中的宏是一组SQL语句，它们被存储并通过调用宏函数名称来执行。宏在当前会话期间存在。宏是临时的，如果你想拥有永久性的宏，最好创建用户定义的函数并在Hive中注册。在关系数据库管理系统（RDBMS）中，宏存储在数据字典中。Hive宏与关系型数据库中的宏略有不同。在我的其他文章中，我们讨论了如何使用用户定义的函数检查一个字符串是否为数字。然而，你也可以通过创建Hive宏来实现相同的功能。请注意，错误是因为参数’inputString’中的大写’S’以及表达式中的大写。，如何使用它们，以及一些宏的示例。

Apache Hive是一个基于Hadoop HDFS的数据仓库框架，用于存储和分析大量数据。Apache Hive支持大多数关系数据库功能，如对大型表进行分区和根据分区列存储值。现在，问题是如何从SELECT查询结果中排除Hive分区列？有一种方法可以用来排除分区列。这个在我们需要表中大量列的时候，例如一个表有100个列，我们需要其中99个，其中一个不要。添加下面的配置到 ‘hive-site.xml’ 文件，或者可以在命令行set。现在我们就可以通过下面的方式排除。在本文中，我们将检查。

在我的其他文章中，我们已经看到了如何使用Hive正则表达式从字符串中提取日期值。正则表达式的另一个常见用途是提取数值，例如从字符串数据中提取区号或电话号码。当您处理不同的数据源时，可能需要从给定的字符串类型列中提取数字值，如电话号码或区号。例如，考虑下面的Hive示例，使用函数中的不同表达式从字符串中提取数字。例如，考虑以下示例，使用Hive正则表达式从字符串中仅获取3位数字。这些关键字，都是和匹配有关的，今天我们介绍一下hive 的。例如，以下示例中的正则表达式仅从字符串中提取6位数字。

临时表是应用程序自动管理在大型或复杂查询执行期间生成的中间数据的一种便捷方式。Hive 0.14 及更高版本支持临时表。可以在用户会话中像使用普通表一样多次使用它们。在本文中，我们将介绍，以及如何创建和使用限制的示例。是限于用户会话的。您可以在用户会话中多次重复使用临时表。Hive 在创建它们的 Hive 会话结束时自动删除所有临时表。临时表中的数据存储在用户的临时目录而不是 Hive 仓库目录中。通常，位置将是。

通过 JDBC 驱动程序，允许用户在Hive中创建表，并通过Hive查询引擎执行 SQL 查询，将查询传递到底层的关系型数据库。通过直接使用 JDBC 与底层数据库通信，实现了高效的数据传递。这样的效率保证了数据在Hive和关系型数据库之间的快速交互。使用常见的StorageHandler 可以在我们做数据集成的时候，可以极大的方便我们完成数据的交互。

当在Apache Hive中定义和操作表时，涉及到的数据的输入和输出以及数据的序列化和反序列化都需要明确定义，这就是 InputFormat、OutputFormat 和 SerDe 的作用。存储处理程序通过指定合适的 InputFormat、OutputFormat 和 SerDe，定义了与特定存储系统的交互方式。这种设计使得Hive能够适应各种存储系统，并提供了灵活性和可扩展性用于与Hadoop分布式文件系统（HDFS）集成。用于与HBase NoSQL数据库集成。

GenericUDF 使用起来相比UDF 更复杂，但是我们也介绍了它支持复杂数据结构，性能更高，需要注意的是GenericUDF是抽象类不是接口，关于GenericUDF的使用的复杂案例可以参考我们的UDAF批量调用外部请求。

遇到一个问题，让人很无语，前面其实我们介绍过Hive 写ES 或者是Hive 数据导出到ES 的方案，其实基本上就是两类但是今天遇到的这个场景，这两种方法都不太合适，我们的场景是在阿里云上的maxcompute 将数据写到华为云上的elasticsearch，maxcompute 由于是阿里云的基础组件由于阿里云提供了数据集成组件，所以maxcompute根本就不支持外部表，其实我们第一时间想到的就是那直接使用阿里云的数据集成工具不就好了嘛，问题是阿里云的集成工具对elasticsearch的支持只支持阿里

其实我们看到生成序列的本质是要借助一串数字序列，然后在这个基础上通过一些运算来生成日期序列或者字母序列等生成数字序列我们借助posexplode 函数+space 函数。

以hadoop 作为基础生态，从0到进行数仓建设，主要分为基础篇和实战篇两部分，基础篇主要是各种组件的学习和案例，实战篇主要是三家企业的数仓设计案例，最后是扩展篇主要是实时数仓。

这一节主要介绍了full join 下关联主键重复的问题第一个问题就是严格限制关联条件顺序的情况下，结果还是不对的问题暂时没有找到原因，但是我们可以通过合并去重的方案第二个问题就是我们的关联条件有问题，我们可以通过严格限制关联条件顺序 和合并去重的方案 解决最后就是我们最初的问题没有解决，因为这个SQL 在很多其他类型数据库或者MPP都是返回两条结果，即使在本地的Hive 上也返回两条结果，但是在生产上返回三条，奇怪的是我们稍加改造，也就是将关联键由改成2的时候结果是正确的，所以这个问题暂时放着吧。

AntlrWorks 就是Antlr的一个idea ，提供了开发(editor)，测试(interpreter)，调试(debugger)，以及代码生成的功能，其中调试是idea 的Antlr插件不具备的。到今天我们的工具就介绍完了，后面我们就可以专心的学习Antlr以及SQL解析了。

数据库作为核心的基础组件，是需要重点保护的对象。无用条件去除属于优化器的逻辑优化范畴，可以仅仅根据SQL本身以及表结构即可完成，其优化的情况也是较多的，代码在sql/sql_optimizer.cc文件中的remove_eq_conds函数。首先回顾在词法解析章节，我们介绍了SQL中的关键字，并且每个关键字都有一个16位的整数对应，而非关键字统一用ident表示，其也对应了一个16位整数。最近，在对SQL解析器和优化器探索的过程中，从一开始的茫然无措到有章可循，也总结了一些心得体会，在这里跟大家分享一下。

我们这里总结一下使用Antlr 的一个流程步骤基于需求按照ANTLR4的规则编写自定义语法的语义规则, 保存成以g4为后缀的文件。使用ANTLR4工具处理g4文件，生成词法分析器、句法分析器代码、词典文件。编写代码继承Visitor类或实现Listener接口，开发自己的业务逻辑代码。其实我们看到最重要的就是实现Visitor接口，因为我们的逻辑在里面Listener模式通过walker对象自行遍历，不用考虑其语法树上下级关系。......

ANTLR 是 ANother Tool for Language Recognition的首字母，是一个语言类工具，主要充当翻译器、编译器、识别器、或者是分析器这样的一个角色，也就是将一些列规则翻译成特定语言代码(Java, C/C++, C#, Python, Ruby)的一个工具。说直白点就是定义语法规则文件—>生成特定语言解析规则文件的代码—>被特定的语言所识别，想想我们的Hive-Sql 是不是就是这样的，不然SQL怎么转化成MR程序。Antlr (ANother Tool for Langua

从上述整个SQL编译的过程，可以看出编译过程的设计有几个优点值得学习和借鉴使用Antlr开源软件定义语法规则，大大简化了词法和语法的编译解析过程，仅仅需要维护一份语法文件即可。整体思路很清晰，分阶段的设计使整个编译过程代码容易维护，使得后续各种优化器方便的以可插拔的方式开关，譬如Hive 0.13最新的特性Vectorization和对Tez引擎的支持都是可插拔的。每个Operator只完成单一的功能，简化了整个MapReduce程序。

这一节其实主要是对join 的扩展，更多与join 相关的可以参考文章中的链接。

这一节又我们学习了Hive的另外一个语法Merge，同时我们提供相关的同等替代语句，语法的学习很简单，那就是多练习。

对Hive 表进行分区分桶选择合适文件存储格式避免产生大量小文件合适的表设计优化查询的SQL(尤其关联Join)使用TEZ 或者Spark 引擎来替代MR使用CBO优化器使用VECTORIZATION需要注意的是我们在文章中关于每个知识点，都扩展了相关的文章进去，如果想进一步了解，可以自行查阅。...

这一节我们总结了一些表设计相关的最佳实践，当然我们针对具体的业务场景的时候也可以有其他的一些建表实践，但我们这里主要关注通用场景，主要有选择合适的存储格式选择合适的压缩分区表分桶表。...

主要介绍了统计信息(表/分区)的用途，以及统计信息如何收集，当然最主要的还是给hive 优化器使用。

使用列名替代 * , 使用SORT BY 替代ORDER BY ,使用CBO 并且更新 Stats ,使用WHERE 替代HAVING过滤非统计字段,避免在谓词中使用函数

HiveCli和Beeline 给我们提供了执行SQL 文件执的功能，这时候我们就会将很多SQL写在一个文件中，然后执行批量SQL 文件，但是这个过程中如果前面的一个SQL 报错了，会发生什么？或者是如果执行过程中如果报错了，我们希望指定能停止执行，接下来我们就看一下如何在HiveCli和Beeline实现这一特性。hive.cli.errors.ignore 是Hive的一个命令行参数，参数值是True/False，当该参数是True的时候，会继续执行SQL 文件即使有SQL 报错了，否则执行报错的时

Hive中的 InputFormat和 OutputFormat我们讲了，Hive的SerDe 我们也讲了，可以参考前面的文章Hive进阶之SerDeHive进阶之 InputFormat 和 OutputFormat我们今天单独介绍一个SerDe，它就是 MultiDelimitSerDe，单独介绍它是因为它比较特殊，前面我们也提到过，今天我们仔细看一下他的用法。你在Hive的项目里搜索MultiDelimitSerDe类，你会发现它处在hive-contrib模块，我们在hive的编译安装的环节说过，这

今天我们看一下Hive中的 InputFormat和 OutputFormat，前面我们在讲的时候也提到了，可以参考前面的文章 Hive进阶之SerDe，其实在前面的文章中我们已经提到了 InputFormat OutputFormat和Serde 之间的关系。Hive 的 outputformat/inputformat 与 hadoop 的 outputformat/inputformat 相当类似， inputformat 负责把输入数据进行格式化，然后提供给 hive ， outputformat

这个方案很多，随便举几个例子,但是在此之前建议你先阅读优化实战篇—UDAF批量调用外部请求(02) 在这篇文章中我们实现了在UDAF 中实现了多线程。不过今天我们介绍两种我们我们不需要引入其他组件就可以搞定的方案下面是我们的代码，主要是UDAF 进行批量数据发送下面是我们的使用，这样我们就记录下了每一批数据的发送情况这里需要处理的就是我们如何保证每个批次的大小，其实这个很简单，可以参考我们以前的文章，这里需要注意的是，我们一个批次不应该太大，如果太大重试成本就很高了。要实现KafkaStorageHan

Hive 整合ClickHouse看官方文档说clickhouse现在支持HDFS和AWS S3作为数据存储的仓库，如果是这样的话，那就意味着基于clickhouse也可以实现"存储与计算分离"的架构设计了，那自然对于整个系统的可靠性和可扩展性是有极大帮助的。这里我们先看一下Hive 整合ClickHouse常见的方案方案一 HDFS引擎表按照ClickHouse 官方文档提供的方法，在ClickHouse 中创建HDFS引擎表，读取Hive的数据，将读取的数据插入到ClickHouse本地表中。

Hive join 的执行计划今天我们接着学习hive 的执行计划，我们知道join 是我们在hive 中非常常见的一种操作，而且掌握它也是hive 性能调优的一个重要环节，今天我们来看一下hive 各种join 的执行计划。关于执行计划可以参考前面的文章：Hive进阶之执行计划Shuffle Join(Common Join)Shuffle Join 是一个Join的一个默认选项，它包含map stage 和 reduce stageMapper: 读取表数据并且输出关联的 key-value

with 扩展其实前面我们已经讲过这个语法了，你可以参考Hive语法之with as和from,那为什么我们今天还要拿出来讲一次呢。我们总结一下这个语法的特点：with…as也叫做子查询部分,该语句允许hive定义一个SQL片段,供整个SQL 使用，有点类似变量或者视图可以简化我们的SQL，尤其我们多次使用的场景除了简化我们的SQL，而且还会将这个片段产生的结果集保存在内存中,后续的SQL均可以访问这个结果集,左右有点像物化视图或者是临时表，从而提高我们的查询性能。这里提到了一个概念物化视图，你

UDF 汉字首字母今天这个业务背景其实很简单，就是公司有很多品牌，我们要提取每个名牌的第一个汉字的首字母，例如"宝马"就是 B , 然后"福特" 就是F,“长安” 就是C 。业务背景差不多就是这样的，关于UDF 我就不再过多介绍了，已经介绍过无数次了，你可以参考前面的文章Hive实战之UDF分词Hive实战之UDF解析IP地址Hive实战之UDF 加密Hive 实战之UDF 复杂JSON 处理所以这里我们直接上代码，出解决思路就可以了pinyin4j 获取汉字的拼音pinyin4j是一

UDF 复杂JSON 处理这个的背景和我们前面的复杂JSON 处理是一样的，起源于我们将数据推送到ES ,不然的话我们直接存储字符串，然后使用hive 提供的json 解析函数处理就好了。阅读之前建议你阅读：[ Hive语法之json 解析](https://blog.youkuaiyun.com/king14bhhb/article/details/111999201)​ Hive语法之复杂json解析处理​ Hive集成篇之ES方便你了解这个背景，以及我们为什么要这么做，我们在复杂js