Hive学习笔记

数据仓库和数据库的区别

• 用途：
  • 数据库主要用于事务处理，即对数据进行增删改查等操作，支持在线应用和实时查询；
  • 数据仓库主要用于数据分析，即对数据进行汇总、统计、挖掘等操作，支持离线批处理和复杂查询。
• 数据：
  • 数据库存储的是当前的、细节的、原始的业务数据，通常是面向应用的；
  • 数据仓库存储的是历史的、汇总的、清洗过的分析数据，通常是面向主题的。
• 结构：
  • 数据库采用范式化（Normalized）的表结构，遵循数据库三大范式，减少冗余和异常，提高存储效率和一致性；
  • 数据仓库采用反范式化（Denormalized）或多维（Dimensional）的表结构，增加冗余和索引，提高查询效率和易用性。
• 优化：
  • 数据库对读写都有优化，需要保证事务的原子性、一致性、隔离性和持久性（ACID）；
  • 数据仓库只对读有优化，不需要保证ACID，但需要保证可扩展性和容错性。

反范式化的表结构（Denormalized）

反范式化或多维的表结构是指在数据仓库中，为了提高查询效率和易用性，而选择性地违反范式化规则，增加冗余数据或者对维度表进行层次化的设计方式。

反范式化有两种常见的方式：Rolldown和Rollup

• Rolldown是将父实体中的所有列和关系都下沉到子实体中，消除了表之间的连接，但增加了数据冗余。
• Rollup是将子实体中的内容保留在父实体中，减少了数据存储量，但需要指定子实体出现的次数。

多维表结构

多维的表结构是指将数据按照事实表和维度表进行划分，并根据不同的业务需求，采用星型模式、雪花模式或星座模式来组织表之间的关系。

全方位解读星型模型,雪花模型及星座模型_星型模型 雪花模型 星座模型_

一图搞懂多维数据模型各种类型（星型、雪花、星座、交叉连接）

Hive的出现解决了什么问题

• 降低了对海量数据进行分析和处理的难度，提供了类SQL的查询语言HiveQL，使得不熟悉MapReduce编程的用户也可以方便地使用Hadoop。
• 为HDFS上的文件添加了schema信息，使得数据可以结构化，并且支持多种存储格式。
• 提供了元数据管理和统一的接口，方便用户对数据进行管理和访问。
• 支持多种计算引擎，如MapReduce、Spark和Tez，提高了执行效率。

Hive的架构

Hive的架构是基于Hadoop的数据仓库工具，它可以将结构化的数据文件映射为一张数据库表，并提供类SQL的查询语言HQL来对数据进行分析和管理。

架构：

• 用户接口：提供了CLI（命令行）、JDBC/ODBC（Java访问）、WebUI（浏览器访问）等方式来连接和操作Hive。
• 元数据存储中心MetaStore：存储了表名、列名、分区字段、数据类型、数据位置等信息，可以使用MySQL等数据库来存储元数据。
• 驱动器Driver：负责接收用户的HQL语句，进行解析、编译、优化和执行，并生成物理执行计划（通常是MapReduce任务）。
  • 解析器：使用Calcite框架，将Sql解析，确定执行顺序
  • 编译器：将SQL翻译成一份逻辑计划
  • 优化器：对逻辑计划调优
  • 执行器：将调优后的逻辑计划转为物理计划，提交到yarn上执行

为什么不把hive的元数据信息保存在hdfs上

因为hdfs是一个分布式文件系统，它主要用来存储大规模的数据文件，而不是结构化的元数据。hdfs不支持随机读写，事务，索引等功能，这些功能对于元数据的管理和查询是很重要的。使用关系型数据库来保存hive的元数据，可以提高元数据的可靠性，一致性和效率。

分桶表、分区表、内部表和外部表

分桶表

hive分桶表是一种在hive中进一步划分数据的方式，它可以按照某个字段的hash值将数据分散到不同的文件中，这些文件称为桶。

hive分桶表的作用有以下几点：

• 可以进行抽样查询，只扫描部分桶中的数据，提高开发效率。
• 可以优化大表和大表之间的join操作，使用map端join（Sort Merge Bucket Map Join）代替reduce端join，减少shuffle开销和内存压力。

创建hive分桶表需要注意以下几点：

• 需要在建表语句中使用clustered by和into buckets指定分桶字段和分桶个数。
• 需要在会话终端开启hive.enforce.bucketing=true参数，让hive支持分桶操作。
• 需要使用insert overwrite table或insert into table语句将原始数据插入到分桶表中，不能使用load data语句。
• 分桶字段必须是建表中已有的字段，并且最好是经常参与join操作的字段。

为什么分桶可以提高join效率

如果两个表join的时候，两个表都是分桶表，并且分桶的字段和个数相同，那么就可以只对相同的桶进行join操作，而不需要对整个表进行扫描和shuffle。这样可以减少数据传输和计算量，提高join的效率。另外，如果一个表很小，可以存储在内存中，那么可以使用map join的方式，在map端直接完成join操作，省去reduce过程。

先后分桶和组合分桶

Hive在执行HQL语句时是根据分桶个数和分桶字段的可能取值来判断先后分桶和组合分桶的。如果分桶个数是每个字段的乘积，那么就可以实现先后分桶的效果，否则就是按照多个字段的组合进行分桶的效果。

例子：

• 先后分桶：

  如果有两个字段ID和Name，ID的可能取值有2个（1和2），Name的可能取值也有2个（A和B），那么每个字段的乘积就是2乘以2，等于4。如果按照ID和Name分桶，且分桶个数设置为4，那么就可以实现先后分桶的效果。也就是先按照ID分为两个桶，再在每个桶中按照Name分两个桶。

• 组合分桶：

  如果ID和Name的可能取值的乘积不止为4，那么会将这两个字段组合起来取hash值分桶。

分区表

hive分区表是一种在hive中按照某个或某些字段将数据划分到不同的子目录中的方式，这些子目录称为分区。

hive分区表的作用有以下几点：

• 可以将数据按照逻辑进行组织，比如按照日期、地域等进行分层存储。
• 可以在查询时通过where子句指定分区条件，只扫描部分分区中的数据，提高查询效率。
• 可以对不同的分区采用不同的压缩格式或存储格式，节省存储空间和网络传输开销。

创建hive分区表需要注意以下几点：

• 需要在建表语句中使用partitioned by指定分区字段和数据类型。
• 分区字段不能是建表中已有的字段，并且最好是经常作为过滤条件的字段。
• 需要使用alter table add partition或insert into table语句添加新的分区，不能使用load data语句。
• 分区表可以有一个或多个分区字段，形成单级或多级分区。

分区字段和数据字段

例子：

创建一个按年月分区的表，可以使用以下语法：

-- 创建动态分区表
create table if not exists dp_tmp (
  uid int ,
  commentid bigint ,
  recommentid bigint
) partitioned by ( year string, month string, day string)
row format delimited fields terminated by '\t' ;

-- 开启动态分区
set hive.exec.dynamic.partition =true ;
-- 允许所有的分区字段都可以使用动态分区，兼容严格模式
set hive.exec.dynamic.partition.mode = nonstrict;

-- 插入数据到分区表
insert overwrite table dp_tmp partition ( year, month, day )
select uid,commentid,recommentid, year, month, day from tmp;

dp_tmp中有六个字段，其中数据字段是uid，commentid和recommentid，分区字段是year，month和day

分区字段不存储实际的数据，而是作为目录名来组织数据。

分区字段和数据字段的区别主要有以下几点：

• 分区字段是用来划分数据的目录结构，而数据字段是用来存储实际的数据内容。
• 分区字段的值会作为HDFS上的目录名，而数据字段的值会存储在文件中。
• 分区字段不占用表的存储空间，而数据字段占用表的存储空间。
• 分区字段可以在查询时作为过滤条件，提高查询效率，而数据字段需要扫描文件进行过滤。
• 分区字段可以在创建表后动态添加、修改或删除，而数据字段需要在创建表时指定，并且修改或删除需要重建表。

静态分区和动态分区

hive的静态分区和动态分区是两种不同的数据存放方式。静态分区是指在插入数据之前，需要手动创建每个分区，并指定分区值。动态分区是指根据表的输入数据自动创建分区，不需要提前指定分区值。

静态分区和动态分区的区别主要有以下几点：

• 静态分区适用于分区数量少且固定的情况，例如按部门或地区划分。动态分区适用于分区数量多且不确定的情况，例如按时间划分。
• 静态分区在编译阶段就确定了分区，不需要reduce任务处理，性能较高。动态分区在运行阶段才确定分区，需要reduce任务处理，性能较低。
• 静态分区不管有没有数据都会创建指定分区，动态分区是有结果集才会创建，否则不创建。
• 动态分区需要开启相关参数，并设置动态分区的模式。默认是严格模式，表示必须指定至少一个静态分区。非严格模式表示允许所有的分区字段都可以使用动态分区。
• 动态分区有可能创建大量的分区和文件，对HDFS和Hive的元数据服务造成压力，因此有一些参数限制动态分区的数量和文件的数量。

分桶和分区的区别

• 分区是按照某个字段的值划分数据的存储路径，每个分区对应一个目录，可以跨越多个文件系统；分桶是按照某个字段的哈希值划分数据的存储文件，每个桶对应一个文件，只能在同一个文件系统中。
• 分区可以减少扫描的数据量，提高查询效率；分桶可以避免连接和聚合中的shuffle，提高计算效率。
• 分区可以动态添加或删除，不需要重建表；分桶需要在创建表时指定，如果要修改分桶个数或字段，需要重建表。
• 分区和分桶可以同时使用，实现更细粒度的数据管理。例如，可以先按照日期分区，再按照用户ID分桶。

内部表和外部表

• hive内部表由hive管理其元数据和实际数据，而hive外部表只由hive管理其元数据，实际数据由hdfs管理。
• hive内部表在创建时不需要使用external关键字，而hive外部表在创建时需要使用external关键字。
• hive内部表在删除时会同时删除其元数据和实际数据，而hive外部表在删除时只会删除其元数据，实际数据仍然保留在hdfs上。
• hive内部表的存储路径一般是默认的/hive/warehouse/数据库名称/表名，而hive外部表的存储路径一般需要使用location指定（不是必须指定）。
• hive内部表的结构和分区变化会自动同步到元数据，而hive外部表的结构和分区变化则需要使用msck repair table table_name进行修复（因为数据变化后，元数据并没有映射到最新的数据）。

一般来说，如果你想让hive完全控制你的数据，并且不需要与其他工具共享数据，你可以使用内部表。如果你想让hdfs管理你的数据，并且需要与其他工具共享数据，你可以使用外部表。

什么时候会用到msck repair table table_name语句

msck repair table语句主要是用来解决通过hdfs dfs -put或者hdfs api写入hive分区表的数据在hive中无法被查询到的问题。我们知道hive有个服务叫metastore，这个服务主要是存储一些元数据信息，比如数据库名，表名或者表的分区等等信息。如果不是通过hive的insert等插入语句，很多分区信息在metastore中是没有的，如果插入分区数据量很多的话，你用 ALTER TABLE table_name ADD PARTITION 一个个分区添加十分麻烦。这时候 msck repair table 就派上用场了。只需要运行 msck repair table 命令，hive就会去检测这个表在hdfs上的文件，把没有写入metastore的分区信息写入metastore。

一句话：只要hive表的文件夹的真实状态和元数据信息中存放的状态不一样，就得用msck repair语句

msck repair语句的作用就是让hive表的元数据信息和文件夹的真实状态保持一致。但是需要注意的是，msck repair语句只能添加分区信息，不能删除分区信息。如果你想删除某个分区，你需要用alter table drop partition语句。

Hive表的数据压缩

指标

• 压缩比：越大越好
• 压缩时间：越小越好
• 压缩后的文件是否可切割：如果可切割，在计算阶段可以启动多个mapTask对一个文件进行计算；如果不可切割，要先将大文件拆成多个小文件后分别压缩，然后在计算阶段针对每个小文件启动mapTask

Hive表的存储格式

行式存储

textFile和sequenceFile

列式存储

orc和parquet

Hive - ORC 文件存储格式详细解析 - 腾讯云开发者社区-腾讯云 (tencent.com)

再来聊一聊 Parquet 列式存储格式 - 知乎 (zhihu.com)

对比

一般来说，行式存储格式和列式存储格式有以下几个方面的区别和优缺点：

• 行式存储格式将一行数据的所有列保存在一起，比较符合面向对象的思维，也比较方便进行插入和更新操作。但是如果查询只涉及某几个列，它会把整行数据都读取出来，不能跳过不必要的列读取。这样会影响查询性能和空间利用率。
• 列式存储格式将一列数据的所有行保存在一起，比较适合分析型业务，因为它可以只读取需要的列，提高查询效率和压缩比。而且由于每一列的数据类型相同，可以针对性地设计更好的压缩算法。但是如果查询涉及多个列或者需要返回整行数据，它就需要重新组装数据，这样会增加计算开销。而且插入和更新操作也比较麻烦。

因此，一般来说，如果你的hive表主要用于存储结构化或半结构化的数据，并且需要频繁地进行插入和更新操作，那么你可以选择行式存储格式，如TEXTFILE或SEQUENCEFILE。如果你的hive表主要用于存储大量的分析型数据，并且需要高效地进行聚合和过滤操作，那么你可以选择列式存储格式，如ORC或PARQUET。

hive中将数据导入一张表中有哪些方法

• 从本地文件系统中导入数据到Hive表，使用load data local inpath命令，指定本地文件的绝对路径和目标表的名称。
• 从HDFS上导入数据到Hive表，使用load data inpath命令，指定HDFS文件的路径和目标表的名称。这种方法会删除HDFS上的原始文件。
• 从别的表中查询出相应的数据并导入到Hive表中，使用insert into table或insert overwrite table命令，指定目标表和分区（如果有的话），以及查询语句。这种方法也适用于分区表和分桶表的情况。
• 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中，使用create table … as select …命令，指定新建表的名称和结构，以及查询语句。这种方法可以一步完成创建表和导入数据的操作。

Hive是怎么把HQL语句转为MR任务的

Hive把HQL语句翻译成MR任务的大致流程如下：

• 利用Antlr框架定义HQL的语法规则，对HQL完成词法语法解析，将HQL转换为为AST（抽象语法树）；
• 遍历AST，抽象出查询的基本组成单元QueryBlock（查询块），可以理解为最小的查询执行单元；
• 遍历QueryBlock，将其转换为OperatorTree（操作树，也就是逻辑执行计划），可以理解为不可拆分的一个逻辑执行单元；
• 使用逻辑优化器对OperatorTree（操作树）进行逻辑优化。例如合并不必要的ReduceSinkOperator，减少Shuffle数据量；
• 遍历OperatorTree，转换为TaskTree。也就是翻译为MR任务的流程，将逻辑执行计划转换为物理执行计划；
• 使用物理优化器对TaskTree进行物理优化；
• 生成最终的执行计划，提交任务到Hadoop集群运行。

具体的MR任务的生成和执行细节可能根据不同的HQL语句和优化策略有所差异。

Hive怎么加快查询速度

Hive支持索引吗

mysql中可以创建B+树索引来加快查询速度

Hive也是支持索引的，但是Hive的索引与关系型数据库中的索引并不相同，并且Hive索引提供的功能很有限，效率也并不高，因此Hive索引很少使用。

Hive索引的主要作用是在指定列上建立一个额外的表（索引表），里面包含了该列的值以及对应的数据文件路径和偏移量。在查询时，可以先从索引表中过滤出符合条件的数据文件和偏移量，然后根据这些信息生成新的split，作为查询任务的输入，从而避免全表扫描。

但是Hive索引也有一些缺点和限制，例如：

• 每次查询时都要先用一个额外的job扫描索引表，如果索引列的值非常稀疏或者分布不均匀，那么索引表本身也会非常大或者效果不佳；
• 索引表不会自动更新，如果原始表有数据新增或删除，那么必须手动重建索引表数据；
• 索引只适用于静态字段和等值查询，并不能优化范围查询、聚合查询、排序查询等；
• 索引只能在单个列上创建，并不能创建复合索引或者位图索引等。

创建索引的语法

CREATE [TEMPORARY] [EXTERNAL] INDEX index_name ON TABLE table_name (column_name,...) 
AS 'index_handler_class' 
[WITH DEFERRED REBUILD] 
[TBLPROPERTIES ('key'='value', ...)] 
[IN TABLE table_name] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[COMMENT index_comment];

其中：

• TEMPORARY表示创建一个临时索引，会话结束后自动删除；
• EXTERNAL表示创建一个外部索引，不会随着表的删除而删除；
• index_handler_class表示用于创建索引的处理器类，可以是内置的或者自定义的；
• WITH DEFERRED REBUILD表示创建一个空索引，可以在之后使用ALTER INDEX ... REBUILD语句来重建索引数据；
• TBLPROPERTIES表示指定一些索引属性，例如创建者、创建时间等；
• IN TABLE table_name表示指定存放索引数据的表名，如果不指定则默认为原表名加上后缀“_index”；
• PARTITIONED BY表示指定分区列，如果不指定则默认和原表分区一致；
• COMMENT index_comment表示添加一些注释说明。

例如，为employees表的country列创建一个内置的紧凑型索引：

CREATE INDEX employees_index ON TABLE employees(country) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED REBUILD IN TABLE employees_index_table PARTITIONED BY (country,name) COMMENT 'Employees indexed by country and name';

注意：

• 创建完索引后需要重建索引数据才能生效，使用语句：

ALTER INDEX index_name ON TABLE table_name PARTITION(partition_spec) REBUILD;

• Hive没有提供一个内置的在数据变更时自动触发创建索引的机制，因此用户需要自己通过上述语句来重建索引；
• 重建索引操作是一个原子操作，因此当rebuild失败时，已创建的索引也无法使用；
• Hive中的索引功能还比较有限，并不适用于所有场景和查询。用户可以使用EXPLAIN语法来分析HiveQL语句是否可以使用索引来提升查询性能。

其他方式

有一些其他的方法可以加快查询速度，例如：

• 使用Tez引擎代替MapReduce引擎，Tez可以提高处理速度和保持MapReduce的扩展性；
• 使用向量化查询，向量化查询可以通过一次获取多行数据而不是单行数据来减少CPU的使用；
• 使用ORCFile格式存储表，ORCFile格式可以提供谓词下推、压缩等技术来减少I/O消耗和存储空间；
• 使用分区和分桶技术，分区和分桶技术可以将数据按照某些列的值划分为不同的文件夹或文件，从而缩小查询范围和提高并行度；

HDFS不支持修改操作，hive是怎么实现update等操作的

由于HDFS不支持随机修改操作，所以Hive也不支持对数据的直接更新和删除。 但是，Hive提供了一些方法来实现数据的修改，主要有以下几种：

• 使用INSERT OVERWRITE TABLE命令，将修改后的数据覆盖到原表或者分区上。
• 使用ALTER TABLE命令，修改表或者分区的属性、名称、字段、存储路径等元数据信息。
• 使用LOAD DATA命令，将外部文件加载到表或者分区中。
• 如果使用了ORC格式存储数据，并且开启了事务功能（transactional=true），则可以使用UPDATE和DELETE命令来修改数据。

这些方法都有一定的局限性和性能开销，所以一般不建议在Hive中频繁修改数据。

UPDATE是怎么实现的

UPDATE命令是Hive在0.11版本之后提供的一种数据更新操作，它需要满足以下条件：

• 表必须使用ORC格式存储；
• 表必须设置事务属性为true（transactional=true）；
• 表必须是分桶表（bucketed table）；
• Hive必须开启事务管理器（hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager）。

UPDATE命令的底层实现原理是基于ORC文件格式的特性，利用ACID输出格式（AcidOutputFormat）和ACID合并操作符（AcidMergeOperator）来完成的。

具体来说，当执行UPDATE命令时，Hive会将原表中需要更新的数据行标记为删除，并将新的数据行写入到一个临时目录中。然后，Hive会启动一个后台线程，定期扫描这些临时目录，并将其与原表中的数据进行合并。合并过程中，会忽略被标记为删除的数据行，并将新的数据行覆盖到原位置。这样就实现了数据的更新操作。

这种实现方式有以下优点：

• 不需要重写整个表或者分区，只需要处理被更新的数据行；
• 不需要锁定整个表或者分区，只需要锁定被更新的桶文件；
• 不影响其他查询对表或者分区的读取，保证了一致性和隔离性。

但也有以下缺点：

• 需要额外的存储空间和计算资源来处理临时目录和合并操作；
• 需要定期进行压缩和清理操作来回收空间和提高效率；
• 只支持ORC格式和分桶表，不支持其他格式和非分桶表。

为什么UPDATE操作只能在ORC格式的分桶表上执行

这是因为UPDATE操作的底层实现原理依赖于ORC格式和分桶表的特性。

• ORC格式可以支持ACID输出格式（AcidOutputFormat）和ACID合并操作符（AcidMergeOperator），这两个组件是实现数据更新和删除操作的关键；
• ORC格式可以保存多层级的统计信息，利用这些信息可以实现谓词下推和跳过不必要的数据，提高更新效率；
• 分桶表可以保证每个桶文件中数据行的顺序一致，方便进行锁定和合并操作；
• 分桶表可以避免产生过多或过少的小文件，影响HDFS性能。

如果使用其他格式或非分桶表，就无法利用这些特性来实现高效、安全、一致的数据更新和删除操作。

Hive提供的两个服务

HiveServer

用户可以远程将SQL提交到hive中去执行，并返回结果给用户

MetaStore

负责存储和管理元数据的服务，它使用关系型数据库来保存元数据信息，例如数据库名，表名，列名等。

配置和启动这两个服务

配置和启动hive server和hive metastore的步骤大致如下：

• 安装mysql server，并设置用户名和密码
• 安装mysql java connector，并创建软链接到hive lib目录
• 在mysql中创建一个数据库，例如hive_db，用来存储元数据
• 在hive-site.xml中配置以下属性：
  • hive.metastore.uris：指定metastore的连接地址，例如thrift://localhost:9083
  • javax.jdo.option.ConnectionURL：指定metastore的数据库连接URL，例如jdbc:mysql://localhost:3306/hive_db?createDatabaseIfNotExist=true
  • javax.jdo.option.ConnectionDriverName：指定metastore的数据库驱动名，例如com.mysql.jdbc.Driver
  • javax.jdo.option.ConnectionUserName：指定metastore的数据库用户名，例如hiveuser
  • javax.jdo.option.ConnectionPassword：指定metastore的数据库密码，例如hivepassword
• 启动hive metastore服务，使用命令nohup hive –service metastore &
• 启动hive server服务，使用命令nohup hive –service hiveserver2 &

beeline

beeline是一个基于JDBC的命令行客户端，它可以用来连接和操作hive server。beeline支持嵌入式模式和远程模式。在嵌入式模式下，它运行一个嵌入式的hive（类似于hive cli）；在远程模式下，它通过thrift协议连接到一个单独的hive server2进程。beeline可以执行hql语句，查看表结构，管理用户变量等功能。

连接方式：

beeline 
!connect jdbs:hive2//此处是启动了HiveServer的主机名:10000
#感叹号表示这是一个beeline命令，而非HQL语句

Hive中可以存储的数据类型

• 基本数据类型，包括整型（TINYINT、SMALLINT、INT、BIGINT）、浮点型（FLOAT、DOUBLE）、定点数（DECIMAL）、布尔型（BOOLEAN）、二进制型（BINARY）、字符串型（STRING、VARCHAR、CHAR）和日期时间型（TIMESTAMP、DATE）。
• 复合数据类型，包括数组（ARRAY）、映射（MAP）、结构体（STRUCT）和联合体（UNIONTYPE）。

复合数据类型

Array

例子：

-- 创建一个包含数组的表
create table if not exists arr1(
  name string,
  score array<int>
) row format delimited fields terminated by ' ' collection items terminated by ',';

-- 加载数据
load data local inpath '/home/hivedata/arr1' into table arr1;

-- 查询数据
select * from arr1;
+--------+---------------------+
| name   | score               |
+--------+---------------------+
| goudan | [60,20,10]          |
| mazi   | [90,30,20]          |
| haoge  | [15,9,20]           |
| cg     | [59,151]            |
+--------+---------------------+

-- 查询数组中的第一个元素
select name, score[0] from arr1;
+--------+-------+
| name   | _c1   |
+--------+-------+
| goudan | 60    |
| mazi   | 90    |
| haoge  | 15    |
| cg     | 59    |
+--------+-------+

-- 查询数组中是否包含某个元素
select name from arr1 where array_contains(score, 90);
+--------+
| name   |
+--------+
| mazi   |
+--------+

Map

例子：

-- 创建一个包含映射的表
create table if not exists map1(
  id int,
  info map<string,string>
) row format delimited fields terminated by ' ' collection items terminated by ',' map keys terminated by ':';

-- 加载数据
load data local inpath '/home/hivedata/map1' into table map1;

-- 查询数据
select * from map1;
+---+------------------------------------+
| id| info                              |
+---+------------------------------------+
| 1 | {"name":"Alice","age":"18"}       |
| 2 | {"name":"Bob","gender":"male"}    |
| 3 | {"name":"Charlie","hobby":"music"}|
+---+------------------------------------+

-- 查询映射中的某个键对应的值
select id, info["name"] from map1;
+---+-------+
| id| _c1   |
+---+- -----+
| 1 | Alice |
| 2 | Bob   |
| 3 | Charlie|
+- --+- -----+

-- 查询映射中所有的键或值
select id, map_keys(info), map_values(info) from map1;
+---+----------------------------------------+
| id| _c1               | _c2
+---+----------------------------------------+
| 1 | ["name","age"]    | ["Alice","18"]     |
| 2 | ["name","gender"] | ["Bob","male"]     |
| 3 | ["name","hobby"]  | ["Charlie","music"]|
+---+----------------------------------------+


-- 查询映射中是否包含某个键或值
select id from map1 where array_contains(map_keys(info), "gender");
+---+
| id|
+---+
| 2 |
+---+

Struct

结构体（STRUCT）是一种基于对象的数据结构形式，它可以包含不同类型的字段，每个字段都有一个名称和一个值。例如，表中一列user的类型为STRUCT{name string; age int}，我们可以通过user.name来访问域name。

例子：

-- 创建一个包含结构体的表
create table if not exists str1(
  name string,
  score struct<chinese:int,math:int,english:int>
) row format delimited fields terminated by ' ' collection items terminated by ',';

-- 加载数据
load data local inpath '/home/hivedata/str' into table str1;

-- 查询数据
select * from str1;
+--------+---------------------+
| name   | score               |
+--------+---------------------+
| goudan | {60,20,10}          |
| mazi   | {90,30,20}          |
| haoge  | {15,9,20}           |
| cg     | {59,151}            |
+--------+---------------------+

-- 查询英语成绩不为空的姓名和总分
select s.name, s.score.chinese + s.score.math + s.score.english as total from str1 s where s.score.english is not null;
+--------+-------+
| name   | total |
+--------+-------+
| goudan | 90    |
| mazi   | 140   |
| haoge  | 44    |
+--------+-------+

UnionType

联合体（UNIONTYPE）是一种异质的数据类型的集合，它可以存储不同类型的值，但是每次只能存储一个值。例如，表中一列value的类型为UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>，我们可以通过value.tag来获取当前值的类型标签（0表示int，1表示double，2表示array<string>，3表示struct<a:int,b:string>），然后通过value.value来获取当前值。

例子：

-- 创建一个包含联合体的表
create table if not exists uni1(
  value UNIONTYPE<int,double,array<string>,struct<a:int,b:string>>
) row format delimited fields terminated by ' ' collection items terminated by ',' map keys terminated by ':';

-- 加载数据
load data local inpath '/home/hivedata/uni' into table uni1;

-- 查询数据
select * from uni1;
+----------------------+
| value                |
+----------------------+
| {0:1}                |
| {1:2.0}              |
| {2:["three","four"]} |
| {3:{"a":5,"b":"five"}}|
| {2:["six","seven"]}  |
| {3:{"a":8,"b":"eight"}}|
| {0:9}                |
| {1:10.0}             |
+----------------------+

-- 查询当前值为数组类型的元素个数和第一个元素
select size(value.value), value.value[0] from uni1 where value.tag = 2;
+------+------------+
| _c0  | _c1        |
+------+------------+
| 2    | three      |
| 2    | six        |
+------+------------+

HQL

order by 与 sort by

• Hive会将order by语句转换为一个mapreduce任务，其中只有一个reducer负责对所有的mapper输出进行排序和输出。
• 这意味着order by语句会将所有的数据发送到一个reducer上，这会导致性能低下和内存不足的问题，尤其是当表的数据量很大时。
• 为了解决这个问题，Hive提供了sort by语句，它可以使用多个reducer来对数据进行局部排序。
• sort by语句会根据partitioner（默认是HashPartitioner）将数据分配到不同的reducer上，每个reducer会对自己的输入进行排序和输出²。
• 这样可以提高排序的效率，但是不能保证全局有序，因为不同的reducer之间没有进行排序。

内置函数

查看内置函数

desc functionsl; # 查看函数列表
desc function 函数名; # 查看具体的函数描述
desc function extended 函数名; # 查看函数使用文档

窗口函数

hive的窗口函数详解 - 知乎 (zhihu.com)

自定义函数

UDF

用户自定义函数（User Defined Function）

一进一出

UDTF

用户自定义表生成函数（User Defined Table-Generating Function）

一进多出

UDAF

用户自定义聚合函数（User Defined Aggregation Function）

例子

有两个包可以用

• org.apache.hadoop.hive.ql.exec
• org.apache.hadoop.hive.ql.udf.generic

org.apache.hadoop.hive.ql.exec包中的UDF、UDTF、UDAFEvaluator等类已经被废弃，不推荐使用，而推荐使用org.apache.hadoop.hive.ql.udf.generic包中的GenericUDF、GenericUDTF、GenericUDAFEvaluator等类；

org.apache.hadoop.hive.ql.udf.generic包中的类可以处理复杂的数据类型，并提供更高的效率，而org.apache.hadoop.hive.ql.exec包中的类只能处理简单的数据类型，并且需要使用反射机制；

因此，org.apache.hadoop.hive.ql.udf.generic包是Hive中用户自定义函数开发的推荐方式。

步骤：

• 使用Maven创建一个Java或Scala项目，添加hive-exec的依赖。
• 继承对应的类，重写对应的方法，实现自定义的逻辑。
• 打包成jar文件，上传到Hive服务器。
• 在Hive命令行中，使用add jar命令添加jar文件。
• 使用create function命令注册自定义函数，指定包名和类名。
• 使用select语句调用自定义函数，传入参数。

// Hive命令行
// 添加jar文件
add jar /xxx/xxx/xxx/xxx.jar;
// 注册函数
create function [函数名] as '[包名].[类名]';
// 注册临时函数
create temporary function [函数名] as '[包名].[类名]';

Hive使用不同计算引擎的区别

Hive可以使用不同的计算引擎来执行HQL语句，包括MapReduce、Spark、Tez等。不同的计算引擎有各自的优势和局限性。

Hive使用MapReduce作为计算引擎的优势：

• MapReduce是Hadoop的原生计算框架，与HDFS有良好的兼容性和稳定性。
• MapReduce适合处理大规模数据，可以利用磁盘进行数据交换，避免内存溢出。
• MapReduce支持多种编程语言，如Java、Python、Ruby等。

Hive使用Spark作为计算引擎的优势：

• Spark是基于内存的分布式计算框架，相比MapReduce有更高的执行效率和更低的延迟。
• Spark支持多种高级抽象，如DataFrame、DataSet、MLlib等，可以实现更复杂的数据分析和机器学习任务。
• Spark支持动态资源分配，可以根据任务需求灵活调整资源使用。

Spark on Hive 和 Hive on Spark

hive on spark是指把spark作为hive的计算引擎。这种模式下，数据是存储在hive中的，用户使用hql来处理和分析数据，但底层会通过spark来执行。

spark on hive是指使用spark来处理存储在hive中的数据，用户可以使用spark的api或sql来进行分析。

区别

• 配置方式：

  • spark on hive只需要配置spark和hive的相关参数，不需要重新编译spark或者hive；
  • hive on spark需要重新编译hive，并且将spark的jar包放到hive的lib目录下。

• 兼容性：

  • spark on hive可以兼容多个版本的hive和spark，只要保证数据源和sql语法的一致性；
  • hive on spark只能兼容特定版本的hive和spark，因为不同版本之间可能存在接口或者功能上的差异。

• 功能支持：

  • spark on hive可以支持更多的数据源和文件格式，例如parquet, orc, delta lake等。同时，spark on hive也可以支持更多的高级功能，例如机器学习，图计算等；
  • hive on spark目前还不支持delta lake等新型文件格式，hive on spark主要是针对批处理场景进行优化。

• 数据存储：

  • spark on hive是使用spark来处理存储在hive中的数据，数据以表的形式存储在hive中；
  • hive on spark是使用hive来处理和分析数据，但底层使用spark作为计算引擎，数据也可以存储在其他支持hadoop的文件系统或数据库中。

• 语言选择：

  • spark on hive可以使用spark的api或sql来进行分析，也可以使用hive语法规范的hql；
  • hive on spark只能使用hql来进行分析。

• 执行流程：

  • spark on hive是将sql或hql转换为spark作业来运行，不需要经过mapreduce；
  • hive on spark是将hql经过hive的解析优化编译后转换为spark作业来运行，也不需要经过mapreduce。

• 性能差异：

  spark on hive和hive on spark都比原生的hive on mapreduce有更快的性能，但具体哪个更快要根据具体的场景和配置来判断。一般来说，如果查询涉及到多个表或多个阶段，则hive on spark会比较快；如果查询只涉及到单表或单阶段，则spark on hive会比较快。

  涉及多表时hive on spark更快，是因为hive可以对hql进行更好的解析优化和编译，生成更高效的spark作业；而涉及单表时spark on hive更快，是因为spark可以对sql或api进行更好的执行优化和调度，减少中间数据的传输和存储。