hive基础知识分享(三)

写在前面

今天继续学习hive部分的知识。
Hive中如何实现行列转换

• 一行变多行
  可以对表使用 LATERAL VIEW EXPLODE()，也可以直接使用 EXPLAIN() 函数来处理一行数据。
  1. SELECT name, col1 FROM testarray2 LATERAL VIEW EXPLODE(weight) t1 AS col1;

  复制代码
• 多行变一行
  使用 GROUP BY + COLLECT_SET/COLLECT_LIST：
  
  
  
  • GROUP BY 用于分组，分组后可以使用 COLLECT_SET 或 COLLECT_LIST 对每组数据进行聚合。
    
  • 最终会得到 ARRAY 类型的数据，可以使用 CONCAT_WS 转成字符串。
    
  • COLLECT_SET 会去重，COLLECT_LIST 不会。
    
  1. SELECT COLLECT_LIST(col1) FROM table GROUP BY name;

  复制代码

Hive中的自定义函数分类及实现方法

Hive 中的自定义函数分为三类：UDF (用户自定义函数)，UDAF (用户自定义聚合函数) 和 UDTF (用户自定义表天生函数)
UDF

• 一进一出
  
• 创建 Maven 项目，并参加依靠：
  1. <dependency>
  2.     <groupId>org.apache.hive</groupId>
  3.     <artifactId>hive-exec</artifactId>
  4.     <version>3.1.3</version>
  5. </dependency>

  复制代码
• 编写代码，继承 org.apache.hadoop.hive.ql.exec.UDF，实现 evaluate() 方法。
  1. import org.apache.hadoop.hive.ql.exec.UDF;
  3. public class HiveUDF extends UDF {
  4.     public String evaluate(String col1) {
  5.         return "#" + col1 + "$";
  6.     }
  7. }

  复制代码
• 将代码打成 JAR 包并上传至 Linux 虚拟机。
  
• 使用 ADD JAR 在 Hive 中加载 JAR：
  1. ADD JAR /path/to/your/jarfile.jar;

  复制代码
• 注册临时函数：
  1. CREATE TEMPORARY FUNCTION myfunction AS 'com.shujia.HiveUDF';

  复制代码
• 使用函数处理数据：
  1. SELECT myfunction(name) AS myfunction FROM students LIMIT 10;

  复制代码

UDTF

• 一进多出
  

案例一：

• 转换前：
  1. "key1:value1,key2:value2,key3:value3"

  复制代码
• 转换后：
  1. key1 value1
  2. key2 value2
  3. key3 value3

  复制代码
• 方法一：使用 EXPLAIN + SPLIT
  1. SELECT SPLIT(t.col1, ":")[0], SPLIT(t.col1, ":")[1]
  2. FROM (SELECT EXPLODE(SPLIT("key1:value1,key2:value2,key3:value3", ",")) AS col1) t;

  复制代码
• 方法二：自定义 UDTF
  1. import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
  2. import org.apache.hadoop.hive.ql.metadata.HiveException;
  3. import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
  4. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
  5. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
  6. import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
  7. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
  9. import java.util.ArrayList;
  11. public class HiveUDTF extends GenericUDTF {
  12.     @Override
  13.     public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
  14.         ArrayList<String> fieldNames = new ArrayList<>();
  15.         ArrayList<ObjectInspector> fieldObj = new ArrayList<>();
  16.         fieldNames.add("col1");
  17.         fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
  18.         fieldNames.add("col2");
  19.         fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
  20.         return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldObj);
  21.     }
  23.     public void process(Object[] objects) throws HiveException {
  24.         String col = objects[0].toString();
  25.         String[] splits = col.split(",");
  26.         for (String str : splits) {
  27.             String[] cols = str.split(":");
  28.             forward(cols);
  29.         }
  30.     }
  32.     public void close() throws HiveException {}
  33. }

  复制代码
• 使用 SQL:
  1. SELECT my_udtf("key1:value1,key2:value2,key3:value3");

  复制代码

案例二：

• 数据表：id, col1, col2, col3, ..., col12 共 13 列
  
• 数据：a, 1, 2, 3, 4, 5, ..., 12
  
• 转换结果：
  1. a, 0时, 1
  2. a, 2时, 2
  3. a, 4时, 3
  4. ...

  复制代码
• 自定义 UDTF 示例：
  1. public class HiveUDTF2 extends GenericUDTF {
  2.     @Override
  3.     public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
  4.         ArrayList<String> filedNames = new ArrayList<>();
  5.         ArrayList<ObjectInspector> fieldObj = new ArrayList<>();
  6.         filedNames.add("col1");
  7.         fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
  8.         filedNames.add("col2");
  9.         fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
  10.         return ObjectInspectorFactory.getStandardStructObjectInspector(filedNames, fieldObj);
  11.     }
  13.     public void process(Object[] objects) throws HiveException {
  14.         int hours = 0;
  15.         for (Object obj : objects) {
  16.             hours = hours + 1;
  17.             String col = obj.toString();
  18.             ArrayList<String> cols = new ArrayList<>();
  19.             cols.add(hours + "时");
  20.             cols.add(col);
  21.             forward(cols);
  22.         }
  23.     }
  25.     public void close() throws HiveException {}
  26. }

  复制代码
• 添加 JAR 文件：
  1. ADD JAR /path/to/HiveUDF2-1.0.jar;

  复制代码
• 注册 UDTF：
  1. CREATE TEMPORARY FUNCTION my_udtf AS 'com.shujia.HiveUDTF2';

  复制代码
• 使用 SQL：
  1. SELECT id, hours, value
  2. FROM udtfData
  3. LATERAL VIEW my_udtf(col1, col2, col3, ..., col12) t AS hours, value;

  复制代码

UDAF

• 多进一出
  
• 有 GROUP BY 等开窗函数，较少自行编写。
  

Hive操作方式

• Hive CLI (命令行接口)
  Hive 提供命令行界面 (CLI) 执行 HiveQL 查询和管理 Hive 数据库。
  启动命令：
  1. hive

  复制代码
• Beeline
  Beeline 是 Hive 的 JDBC 客户端，支持连接 Hive Server 2，推荐用于生产环境中。
  启动 Beeline：
  1. beeline -u jdbc:hive2://<hive-server>:10000

  复制代码
• Hive JDBC 接口
  通过 JDBC 连接 Hive，执行 SQL 查询并获取结果。
  示例：
  1. String url = "jdbc:hive2://<hive-server>:10000/default";
  2. Connection con = DriverManager.getConnection(url, "username", "password");
  3. Statement stmt = con.createStatement();
  4. ResultSet rs = stmt.executeQuery("SELECT * FROM table_name");

  复制代码
• Apache Spark 与 Hive 集成
  使用 spark-hive 模块，允许使用 Spark SQL 查询 Hive 数据。
  配置：
  1. --conf spark.sql.warehouse.dir=/user/hive/warehouse \
  2. --conf spark.sql.catalogImplementation=hive

  复制代码
  使用 Spark SQL 查询 Hive 数据：
  1. val spark = SparkSession.builder()
  2.     .appName("Hive Integration")
  3.     .enableHiveSupport()
  4.     .getOrCreate()
  6. spark.sql("SELECT * FROM table_name").show()

  复制代码
• Hive命令中的-e/-f参数
  
  
  
  • -e：执行一条 Hive 的 SQL 命令。
    
  • -f：执行一个 SQL 脚本。
    
  
  
• Crontab 中定时策略
  

在 crontab 中，定时策略通过指定一组时间字段来设置任务的执行时间。每个字段代表了一个时间单位，组合起来形成一个定时任务的执行规则。
crontab 的基本格式如下：

1. * * * * * command_to_execute
2. - - - - -
3. | | | | |
4. | | | | +---- 星期几 (0 - 7) (0 和 7 都代表星期天)
5. | | | +------ 月份 (1 - 12)
6. | | +-------- 月中的某一天 (1 - 31)
7. | +---------- 小时 (0 - 23)
8. +------------ 分钟 (0 - 59)

复制代码

时间字段含义

• 分钟（0 - 59）：表示任务在每小时的哪一分钟执行。
  
• 小时（0 - 23）：表示任务在每天的哪个小时执行。
  
• 日期（1 - 31）：表示任务在每月的哪一天执行。
  
• 月份（1 - 12）：表示任务在哪个月执行。
  
• 星期（0 - 7）：表示任务在哪一天的星期执行（0 和 7 都代表星期天，1 代表星期一，以此类推）。
  

特殊字符

• 星号 *：表示“每”或“恣意”。例如 * 在分钟字段中表示每一分钟都执行任务。
  
• 逗号 ,：用于指定多个值。例如 5,10,15 表示在第 5、10 和 15 分钟执行。
  
• 连字符 -：用于指定范围。例如 1-5 表示从第 1 天到第 5 天。
  
• 斜杠 /：表示步进值。例如 */5 表示每 5 个单位执行一次。
  

详细解释

• 每周一到周五的中午 12 点执行：
  1. 0 12 * * 1-5 command_to_execute

  复制代码
  
  
  • 0：在每个小时的第 0 分钟执行（即整点）。
    
  • 12：在每天的中午 12 点执行。
    
  • *：每天都执行。
    
  • 1-5：仅在星期一至星期五执行（星期一为 1，星期五为 5）。
    
  
  
• 每个月的 1 号和 15 号凌晨 3 点执行：
  1. 0 3 1,15 * * command_to_execute

  复制代码
  
  
  • 0：在每小时的第 0 分钟执行。
    
  • 3：在每天的 3 点执行。
    
  • 1,15：仅在每月的 1 号和 15 号执行。
    
  • *：每个月都执行。
    
  
  
• 每天的 1:30 AM 执行：
  1. 30 1 * * * command_to_execute

  复制代码
  
  
  • 30：在每个小时的第 30 分钟执行。
    
  • 1：在每天的 1 点执行。
    
  
  
• 每 10 分钟执行一次：
  1. */10 * * * * command_to_execute

  复制代码
  
  
  • */10：表示每隔 10 分钟执行一次任务。
    
  
  
• 每个星期天的下战书 2 点 30 分执行任务：
  1. 30 14 * * 0 command_to_execute

  复制代码
  
  
  • 30：在每个小时的第 30 分钟执行。
    
  • 14：在下战书 2 点执行。
    
  • 0：仅在星期天（0 或 7）执行。
    
  
  

Hive 建表时留意事项

• 分区，分桶
  一样平常是按照业务日期进行分区，每天的数据放在一个分区里。
  
• 一样平常使用外部表，克制数据误删
  使用外部表时，数据存储在 HDFS 上，不会因为删除 Hive 表而删除实际数据。
  
• 选择适当的文件储存格式及压缩格式
  选择合适的文件格式，如 Parquet、ORC 等，这些格式支持列式存储并且压缩效率高。
  
• 命名要规范
  克制表名、列名使用空格、特殊字符，遵照统一的命名规范。
  
• 数据分层，表分离，但也不要分的太散
  对数据进行合理的分层设计，克制数据过于分散，影响查询性能。
  

Hive 优化

1. 处理数据倾斜

数据倾斜原因

• Key 分布不匀称
  
• 触发了 Shuffle 操作
  
  
  
  • GROUP BY 或 DISTINCT
    
  • JOIN 或 UNION
    
  
  

数据倾斜的表现

• 任务进度长时间停滞在 99%（或 100%）。
  
• 单一 reduce 处理的数据量宏大于其他 reduce。
  

解决方案

• 检查数据源头，过滤无效数据。
  
• 对重复的 key 值进行拆分或哈希。
  
• 克制不必要的 Shuffle 操作。
  

2. 建表（分区分桶）

• 分区，分桶
  一样平常按业务日期进行分区，每天的数据放在一个分区里。
  
• 使用外部表
  使用外部表克制数据误删。
  
• 选择适当的文件存储格式及压缩格式
  使用 Parquet、ORC 等列式存储格式，适当压缩数据，减少磁盘占用。
  
• 命名要规范
  表名和列名应符合统一命名规范。
  
• 数据分层，表分离
  采用合理的数据分层设计，克制表过多或过少，克制分得过散。
  

3. SQL 规范

• 合理分区分桶
  查询时进行分区裁剪，克制扫描无关数据。
  
• WHERE 过滤
  先进行过滤，再进行 JOIN 或计算。
  
• MapJoin
  在 Hive 1.2 之后，MapJoin 默认启用。如果没有自动启用，可以手动指定：
  1. SELECT /*+mapjoin(b)*/ a.xx, b.xxx FROM a LEFT OUTER JOIN b ON a.id = b.id;

  复制代码
  左连接时，通常大表放在左边，小表放在右边。
  
• 归并小文件
  归并小文件以提拔性能。
  
• 适当的子查询
  使用子查询时，确保子查询数目合理，克制嵌套过深影响性能。
  
• 适当的排序方式
  
  
  
  • ORDER BY：全局排序，只能有一个 reduce。
    
  • SORT BY：每个 reduce 内部排序。
    
  • DISTRIBUTE BY：对指定字段进行分区。
    
  • CLUSTER BY：类似于 DISTRIBUTE BY + SORT BY。
    
  
  
• 适当的执行引擎
  
  
  
  • 使用 MR（MapReduce）、Tez 或 Spark 等引擎，根据差别情况选择最合适的执行引擎。
    
  
  

4. 开启相干参数，调整参数

• set mapred.reduce.tasks;
  设置 Reduce 的数目。
  
• set hive.auto.convert.join=true;
  开启 MapJoin，提高 JOIN 性能。
  
• set hive.mapjoin.smalltable.filesize=20000000;
  设置小表的文件大小为 20M，默认值为 25M。
  
• set hive.groupby.skewindata=true;
  开启数据倾斜优化。
  
• set hive.map.aggr = true;
  开启预聚合功能。
  
• set hive.exec.parallel=true;
  开启并行执行。
  
• set hive.exec.parallel.thread.number=16;
  设置 SQL 允许的最大并行度，默认为 8。
  

什么是究竟表、维度表？

• 究竟表
  究竟表用于存储数据堆栈中的业务过程量化数据，通常包含数字型数据（如贩卖额、订单数目、利润等）。究竟表与维度表通过外键连接，提供更多上下文信息。
  特点：
  
  
  
  • 包含度量值，如贩卖量、收入、付出等。
    
  • 每行代表一个具体的业务事件。
    
  • 外键字段与维度表连接。
    
  
  
• 维度表
  维度表存储描述究竟表中数据的相干信息，通常是文本型数据（如产品名称、客户姓名、商店地点、时间等）。
  特点：
  
  
  
  • 包含描述性数据，如客户名称、产品类别、时间等。
    
  • 主键字段与究竟表中的外键关联。
    
  • 较小的数据量。
    
  
  

星型模型与雪花模型的区别？

• 星型模型：
  究竟表在中心，四周是维度表。简洁、易于明白，但会导致数据冗余。
  
• 雪花模型：
  维度表进一步拆分成子维度表，规范化存储，减少冗余，但复杂度较高，查询性能较差。
  

数据堆栈的分层设计

为什么要设计数据分层？

数据分层有助于数据在流转过程中保持秩序，使数据生命周期清晰、可控。克制层级混乱或复杂依靠结构。
数据分层的好处：

• 清晰数据结构：每一层有明白的职责和作用，便于使用和明白。
  
• 减少重复开辟：通用数据和中间层可以减少重复计算。
  
• 统一数据口径：统一数据出口，确保输出数据的同等性。
  
• 复杂题目简单化：将复杂任务分解成多个层次完成。
  

分层设计

ODS 层（Operational Data Store 数据运营层）

ODS 层接近数据源，通常不做过多的数据清洗，原始数据直接存入该层。主要用于追溯原始数据题目。
DW 层（Data Warehouse 数据堆栈层）

DW 层是焦点层，数据从 ODS 层抽取后，进行进一步的清洗、聚合，并按主题建立数据模型。DW 层可分为以下几层：

• DWD 层（Data Warehouse Detail 数据明细层）：
  保持与 ODS 层相同的数据粒度，做数据清洗和部分聚合。
  
• DWM 层（Data Warehouse Middle 数据中间层）：
  对 DWD 层数据进行轻度聚合，天生中间表，提拔指标复用性。
  
• DWS 层（Data Warehouse Service 数据服务层）：
  

​又称数据集市或宽表。按照业务划分，如流量、订单、用户等，天生字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
​一样平常来讲，该层的数据表会相对比较少，一张表会涵盖比较多的业务内容，由于其字段较多，因此一样平常也会称该层的表为宽表。在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在计算量太大并且维度太少的题目，因此一样平常的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定，也可以去掉DWM这一层，只留DWS层，将全部的数据在放在DWS亦可。
ADS/APP/DM层（Application Data Store/Application/DataMarket 数据应用层/数据集市）：

在这里，主要是提供给数据产品和数据分析使用的数据，一样平常会存放在 ES、PostgreSql、Redis等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。比如我们常常说的报表数据，一样平常就放在这里。
DIM层（Dimension 维表层）

维表层主要包含两部分数据：

• 高基数维度数据：一样平常是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
  
• 低基数维度数据：一样平常是配置表，比如罗列值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。