自学内容网 自学内容网
自学内容网 > 正文

Hive数仓操作（四）

🕗 发布于 2024-09-30 07:07 hive  hadoop  数据仓库  大数据  数据库  

一、Hive 创建表案例一（ARRAY数组类型）

1. 准备数据文件

首先，准备一个名为 stu2.txt 的文件，文件内容示例如下：

1001    Alice    fish,cat
1002    Bob      dog,rabbit
1003    Charlie  bird

注意：

  • 确保字段之间使用制表符（\t）分隔。
  • 数组元素之间使用逗号（,）分隔。

2. 上传文件到 HDFS

使用以下命令将 stu2.txt 文件上传到 HDFS 指定目录：

hadoop fs -put stu2.txt /user/root/a001/

3. 创建 Hive 数据库

为了管理数据，可以创建一个名为 stu 的数据库（如果还未创建）：

CREATE DATABASE IF NOT EXISTS stu;

4. 使用数据库

切换到刚创建的 stu 数据库：

USE stu;

5. 创建 Hive 表

在 Hive 中创建表 stu03，用来存储数据：

CREATE TABLE stu03 (
    id INT,
    name STRING,
    hobby ARRAY<STRING>
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
  • ROW FORMAT DELIMITED: 指定表的行格式为分隔格式。这意味着行中的各个字段是用特定的分隔符来分隔的。
  • FIELDS TERMINATED BY ‘\t’: 指定字段之间的分隔符为制表符（Tab）。这意味着在数据文件中，各个字段是通过制表符来分开。
  • COLLECTION ITEMS TERMINATED BY ‘,’: 指定数组类型字段（在这里是 hobby）中的各个元素是用逗号分隔的。这意味着在插入数据时，例如 hobby 字段的值可以为 fish,cat，而这两个爱好是通过逗号来分隔的。

6. 加载数据到表中

将 HDFS 中的 stu2.txt 文件中的数据加载到 stu03 表中：

LOAD DATA INPATH '/user/root/a001/stu2.txt' INTO TABLE stu03;

7. 测试查询数据

查询所有数据
SELECT * FROM stu03;
查询特定字段和处理可能缺失的数组元素
SELECT 
    id, 
    name, 
    hobby[0] AS first_hobby, 
    hobby[1] AS second_hobby, 
    NVL(hobby[2], '没有') AS third_hobby
FROM stu03;
查询数组元素个数
SELECT 
    id, 
    name, 
    hobby, 
    SIZE(hobby) AS hobby_count 
FROM stu03;

8. 多次查询的示例

在同一条查询中返回多个字段和计算结果：

SELECT 
    a.*, 
    SIZE(hobby) AS n, 
    hobby[0] AS h1, 
    hobby[1] AS h2, 
    NVL(hobby[2], 'none') AS h3, 
    NVL(hobby[3], '') AS h4 
FROM stu03 a;

9. 插入新数据

向 stu03 表中插入一条新数据：

INSERT INTO stu03 
VALUES (1005, 'Tom', ARRAY('fish', 'mouse'));

会在/user/hive/warehouse/stu.db/stu03/目录追加生成小文件，如000000_0 ，一般不使用插入操作。

二、Hive 创建表案例二（ARRAY数组类型）

1. 数据文件

假设有一个名为 stu04.txt 的文件：

123|华为Mate50|id:1111,token:2222,user_name:zhangsan1
456|华为Mate60|id:1113,token:2224,user_name:zhangsan3
89|小米14|id:1114,token:2225,user_name:zhangsan4
1235|小米13|id:1115,token:2226,user_name:zhangsan5
4562|OPPO A5|id:1116,token:2227,user_name:zhangsan6

2. 创建表

接下来，创建一个包含 map 类型数据的 Hive 表。注意修正语法和格式错误：

DROP TABLE IF EXISTS stu03;

CREATE TABLE stu03 (
    sku_id STRING COMMENT '商品id',
    sku_name STRING COMMENT '商品名称',
    state_map MAP<STRING, STRING> COMMENT '商品状态信息'
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
1）. ROW FORMAT DELIMITED
  • 表示该表的行格式是分隔的。也就是说，数据行中的每个字段都是用特定的字符分隔开的。
2）. FIELDS TERMINATED BY ‘|’
  • 指定字段（列）之间的分隔符。在这个例子中，字段之间用 | （竖线）来分隔。
  • 例如，如果一行数据是 123|华为Mate60|id:1111,token:2222,user_name:zhangsan1，那么这个行的三个字段分别是 123、华为Mate60 和 id:1111,token:2222,user_name:zhangsan1。
3）. COLLECTION ITEMS TERMINATED BY ‘,’
  • 这一部分指定了集合类型中的元素（如数组或映射）之间的分隔符。在这个例子中，集合中的元素用 , （逗号）来分隔。
  • 例如，如果某个字段的值是 id:1111,token:2222,user_name:zhangsan1，那么这个字符串被看作一个集合，元素之间用逗号分隔。
4）. MAP KEYS TERMINATED BY ‘:’
  • 这一部分指定了映射类型（即键值对）中键和值之间的分隔符。在这个例子中，键和值用 : （冒号）来分隔。
  • 例如，如果某个字段的值是 id:1111，那么这里的键是 id，值是 1111。
示例

结合这些定义，假设我们有以下一行数据：

123|华为Mate10|id:1111,token:2222,user_name:zhangsan1
  • 这行数据表示：
    • 第一个字段（商品 ID）是 123
    • 第二个字段（商品名称）是 华为Mate60
    • 第三个字段是一个映射，包含三个键值对：
      • 键 id 的值是 1111
      • 键 token 的值是 2222
      • 键 user_name 的值是 zhangsan1

3. 加载数据

将数据文件加载到刚创建的表中：

LOAD DATA LOCAL INPATH '/opt/module/datas/stu04.txt' INTO TABLE stu03;

4. 查询数据

示例查询1
SELECT 
    sku_id,
    sku_name,
    state_map 
FROM 
    stu03;
解析
  • sku_id: 选择 SKU 的 ID。
  • sku_name: 选择 SKU 的名称。
  • state_map: 选择整个 state_map 列，这将返回该列的所有内容（键值对）。
结果（只展示了三行）
sku_idsku_namestate_map
1华为Mate50{“id”:“1111”, “token”:“2222”, “user_name”:“zhangsan”}
2华为Mate60{“id”:“1113”, “token”:“2224”, “user_name”:“zhangsan2”}
3小米14{“id”:“1114”, “token”:“2225”, “user_name”:“zhangsan3”}
示例查询2：直接提取特定键
SELECT 
    sku_id,
    sku_name,
    state_map['id'] AS id,
    state_map['token'] AS token,
    MAP_KEYS(state_map) AS keys,
    MAP_VALUES(state_map) AS values,
    SIZE(state_map) AS number_of_entries 
FROM 
    stu03;
解析
  • state_map['id'] AS id: 从 state_map 中提取 id。
  • state_map['token'] AS token: 从 state_map 中提取 token。
  • MAP_KEYS(state_map) AS keys: 获取 state_map 中所有的键。
  • MAP_VALUES(state_map) AS values: 获取 state_map 中所有的值。
  • SIZE(state_map) AS number_of_entries: 获取 state_map 中的键值对数量。
结果
sku_idsku_nameidtokenkeysvaluesnumber_of_entries
1华为Mate5011112222[“id”, “token”, “user_name”][“1111”, “2222”, “zhangsan”]3
2华为Mate6011132224[“id”, “token”, “user_name”][“1113”, “2224”, “zhangsan2”]3
3小米1411142225[“id”, “token”, “user_name”][“1114”, “2225”, “zhangsan3”]3
示例查询3：使用 MAP_KEYS 和索引
SELECT 
    sku_id,
    sku_name,
    MAP_KEYS(state_map)[0] AS first_key,
    MAP_VALUES(state_map)[0] AS first_value,
    MAP_KEYS(state_map) AS keys,
    MAP_VALUES(state_map) AS values,
    SIZE(state_map) AS number_of_entries 
FROM 
    stu03;
解析
  • MAP_KEYS(state_map)[0] AS first_key: 获取 state_map 中的第一个键。
  • MAP_VALUES(state_map)[0] AS first_value: 获取 state_map 中的第一个值。
  • MAP_KEYS(state_map) AS keys: 获取所有键。
  • MAP_VALUES(state_map) AS values: 获取所有值。
  • SIZE(state_map) AS number_of_entries: 获取键值对数量。
结果

根据相同的 state_map 内容，查询结果将显示如下：

sku_idsku_namefirst_keyfirst_valuekeysvaluesnumber_of_entries
1华为Mate50id1111[“id”, “token”, “user_name”][“1111”, “2222”, “zhangsan”]3
2华为Mate60id1113[“id”, “token”, “user_name”][“1113”, “2224”, “zhangsan2”]3
3小米14id1114[“id”, “token”, “user_name”][“1114”, “2225”, “zhangsan3”]3

原文地址：https://blog.csdn.net/m0_58076578/article/details/142643482

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024