hive-进阶版-1
第6章 hive内部表与外部表的区别
Hive 是一个基于 Hadoop 的数据仓库工具,用于对大规模数据集进行数据存储、查询和分析。Hive 支持内部表(Managed Table)和外部表(External Table)两种表类型,它们在数据存储、管理方式和生命周期等方面存在显著区别。以下是内部表和外部表的主要区别:
1. 数据存储位置
- 内部表:数据存储在 Hive 的默认存储目录下,通常位于 HDFS(Hadoop Distributed File System)的某个指定路径中。具体路径由 Hive 配置决定,例如
/user/hive/warehouse
。当创建内部表时,Hive 会自动将数据存储到这个目录下。 - 外部表:数据存储在用户指定的路径中,这个路径可以是 HDFS 的任意位置,也可以是其他支持的存储系统(如 S3)。外部表的数据存储位置由用户在创建表时明确指定。
2. 数据管理方式
2.1内部表:
- Hive 对数据拥有完全控制权。当删除内部表时,Hive 会同时删除表的元数据和存储的数据文件。
- 数据的加载、更新和删除操作都由 Hive 管理,用户不需要手动操作底层存储。
2.2外部表:
- Hive 只管理表的元数据(如表结构、列信息等),而不管理数据文件本身。数据文件的创建、更新和删除需要用户自行管理。
- 当删除外部表时,Hive 只会删除表的元数据,而不会删除存储在指定路径中的数据文件。
3. 数据生命周期
- 内部表:数据的生命周期与表的生命周期紧密绑定。一旦表被删除,数据也会被自动清理,因此数据的存储和管理完全依赖于 Hive。
- 外部表:数据的生命周期独立于表的生命周期。即使外部表被删除,数据仍然保留在指定的存储路径中,不会被清理。用户可以在需要时重新创建表并关联到这些数据。
4. 适用场景
4.1内部表:
- 适用于数据完全由 Hive 管理的场景,例如数据仓库中的事实表和维度表。
- 当数据需要频繁更新或删除时,内部表更适合,因为 Hive 可以高效地管理这些操作。
4.2外部表:
- 适用于数据需要共享或与其他系统集成的场景。例如,数据可能同时被 Hive 和其他工具(如 Spark、Pig 或其他 Hadoop 生态系统工具)使用。
- 当数据存储在外部系统(如 S3 或其他云存储)时,外部表是更好的选择。
- 如果数据需要保留,即使表被删除,外部表可以确保数据不会丢失。
5. 创建语法
内部表:
CREATE TABLE internal_table (
id INT,
name STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
外部表:
CREATE EXTERNAL TABLE external_table (
id INT,
name STRING
)
LOCATION '/path/to/external/data';
示例:
CREATE EXTERNAL TABLE employees (
emp_id INT,
emp_name STRING,
emp_age INT,
emp_position STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/path/to/employees.txt';
-
CREATE EXTERNAL TABLE
:声明这是一个外部表。 -
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
:指定字段分隔符为逗号。 -
LOCATION '/path/to/employees.txt'
:指定数据存储的位置。
注意:请将 /path/to/employees.txt
替换为实际的 HDFS 路径。
第7章 关于hive使用的一些技巧
1.可以直接不进入hive的情况下执行SQL
通过shell的参数-e可以执行一次就运行完的命令
hive -e "select * from yhdb_student"
在查询的结果显示字段名:
set hive.cli.print.header=true; ------临时,重启后再一次执行
永久设置:修改/opt/installs/hive/conf/.hiverc文件
追加一条:set hive.cli.print.header=true;
2. hive可以直接运行SQL文件
1. 创建 SQL 文件
首先,将你的 Hive SQL 语句保存到一个文件中,例如 script.sql
。文件内容可以是单条或多条 SQL 语句,例如:
-- script.sql
CREATE TABLE IF NOT EXISTS my_table (
id INT,
name STRING,
age INT
);INSERT INTO TABLE my_table VALUES (1, 'Alice', 30), (2, 'Bob', 25);
SELECT * FROM my_table;
2. 在 Hive 命令行中运行 SQL 文件
方法 1:使用 -f
参数
在 Hive 命令行中,使用 -f
参数指定 SQL 文件路径:
hive -f /path/to/script.sql
方法 2:在 Hive Shell 中运行
如果你已经进入了 Hive Shell,可以使用 source
命令运行 SQL 文件
hive
hive> source /path/to/script.sql;
3. 参数化 SQL 文件(可选)
如果你需要在运行时传递参数到 SQL 文件中,可以通过 Hive 的变量替换功能实现。例如:
SQL文件示例:
-- script.sql
CREATE TABLE IF NOT EXISTS my_table (
id INT,
name STRING,
age INT
);
INSERT INTO TABLE my_table VALUES (1, '${hiveconf:name}', 30);
SELECT * FROM my_table;
运行时传递参数
hive -f /path/to/script.sql -hiveconf name="Alice"
-
${hiveconf:name}
是一个变量占位符,-hiveconf name="Alice"
会将变量name
的值设置为"Alice"
。 -
Hive 会替换
${hiveconf:name}
为"Alice"
,然后执行 SQL 文件。
查询日志:
hive -f /path/to/script.sql -v
3.hive中执行Linux命令
Hive Shell 支持通过 !
符号直接运行 Linux 命令。例如:
!ls /path/to/directory;
4.在hive中操作hdfs
1. 在 Hive 中执行 HDFS 命令
Hive 提供了直接执行 HDFS 命令的功能。你可以在 Hive Shell 中通过 dfs
命令来操作 HDFS,格式为:
查看 HDFS 目录下的文件:
dfs -ls /user/hive/warehouse/;
将 HDFS 中的文件复制到本地:
dfs -get /user/hive/warehouse/mydb.db/myfile.txt ./local_directory/;
将本地文件上传到 HDFS:
dfs -put /local_path/myfile.txt /user/hive/warehouse/mydb.db/;
2. 将 HDFS 文件加载到 Hive 表中
你可以通过 Hive 的 LOAD DATA INPATH
语句将 HDFS 上的文件加载到 Hive 表中。步骤如下:
2.1创建hive表
CREATE TABLE my_table (
column1 STRING,
column2 INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
2.2加载数据
LOAD DATA INPATH '/path/to/hdfs/file' INTO TABLE my_table;
其中 /path/to/hdfs/file
是 HDFS 上文件的实际路径。
3. 在 Hive 中查看表的 HDFS 存储路径
DESCRIBE FORMATTED my_table;
第8章 hive中SQL基础查询
1. 查询表中的所有数据
SELECT * FROM table_name;
2. 查询指定列
SELECT column1, column2, column3 FROM table_name;
3. 添加条件过滤(WHERE 子句)
SELECT emp_name, emp_age FROM employees
WHERE emp_age > 30;
4. 使用比较运算符
SELECT * FROM employees
WHERE emp_age >= 25 AND emp_age <= 35;
5. 使用逻辑运算符
- AND:同时满足多个条件。
- OR:满足任意一个条件。
- NOT:对条件取反。
SELECT * FROM employees
WHERE emp_age > 30 OR emp_position = 'Manager';
6. 使用 IN
和 NOT IN
- IN:用于指定多个可能的值。
- NOT IN:用于排除指定的值。
SELECT * FROM employees
WHERE emp_position IN ('Manager', 'Engineer');
7. 使用 LIKE
和通配符
- LIKE:用于模糊查询。
- %:匹配任意字符序列。
- _:匹配任意单个字符。
SELECT * FROM employees
WHERE emp_name LIKE 'J%'; -- 匹配以 "J" 开头的名字
8. 使用 ORDER BY
排序
SELECT emp_name, emp_age FROM employees
ORDER BY emp_age DESC;
9. 使用 LIMIT
限制结果数量
SELECT * FROM table_name
LIMIT n;
10. 使用 GROUP BY
进行分组
SELECT emp_position, COUNT(*) AS num_employees
FROM employees
GROUP BY emp_position;
11. 使用 HAVING
筛选分组
SELECT emp_position, COUNT(*) AS num_employees
FROM employees
GROUP BY emp_position
HAVING COUNT(*) > 2;
12. 使用 JOIN
进行表连接
--inner join
SELECT a.emp_id, a.emp_name, b.department_name
FROM employees a
JOIN departments b
ON a.department_id = b.department_id;
--left join
SELECT a.emp_id, a.emp_name, b.department_name
FROM employees a
LEFT JOIN departments b
ON a.department_id = b.department_id;
13. 使用子查询
SELECT emp_name, emp_age
FROM employees
WHERE emp_id IN (
SELECT emp_id
FROM salaries
WHERE salary > 5000
);
14. 使用 UNION ALL
合并结果
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
15. 使用 DISTINCT
去重
SELECT DISTINCT column1, column2 FROM table_name;
第9章 hive -复杂数据类型用法
1、ARRAY类型
在Hive中,ARRAY
是一种复杂数据类型,用于存储相同类型的有序集合。
1.1定义与创建
ARRAY
类型可以通过array<type>
语法定义,其中type
表示数组中元素的数据类型。例如,创建一个包含字符串数组的表:
CREATE TABLE example_table (
id INT,
names ARRAY<STRING>
)
row format delimited
fields terminated by '\t' --tab空格
collection items terminated by '.'
lines terminated by '\n'
stored as textfile;
2. 插入数据
INSERT INTO example_table VALUES (1, array('Alice', 'Bob', 'Charlie'));
3. 查询数组数据
3.1 访问数组元素
可以通过下标访问数组中的元素,下标从0开始。例如:
SELECT names[0] AS first_name FROM example_table;
3.3 获取数组长度
使用size
函数可以获取数组的长度:
SELECT size(names) AS array_length FROM example_table;
3.4 对数组排序
使用sort_array
函数可以对数组进行排序:
SELECT sort_array(names) AS sorted_names FROM example_table;
4. 嵌套使用
ARRAY
可以与其他复杂数据类型嵌套使用。例如,创建一个包含结构体数组的表:
CREATE TABLE nested_table (
user_id INT,
user_info ARRAY<STRUCT<name:STRING, age:INT>>
);
插入数据
INSERT INTO nested_table VALUES (1, array(named_struct('name', 'Alice', 'age', 25), named_struct('name', 'Bob', 'age', 30)));
查询嵌套数据
SELECT user_info[0].name, user_info[0].age FROM nested_table;
2.explode的用法
1. explode
函数的基本用法
explode
函数可以将array
或map
类型的列展开为多行数据。具体用法如下:
1.1 对array
类型展开
将数组中的每个元素拆分为单独的行:
SELECT explode(array('apple', 'banana', 'pear')) AS fruit;
+---结果----+
| fruit |
+-------+
| apple |
| banana|
| pear |
+-------+
1.2 对map
类型展开
SELECT explode(map('a', 1, 'b', 2)) AS (key, value);
+-----+结果-------+
| key | value |
+-----+-------+
| a | 1 |
| b | 2 |
+-----+-------+
2. 展开字符串类型
explode
不直接支持字符串类型,但可以通过split
函数将字符串转换为数组,然后再使用explode
:
SELECT explode(split('apple,banana,pear', ',')) AS fruit;
+-------+结果
| fruit |
+-------+
| apple |
| banana|
| pear |
+-------+
3. 结合LATERAL VIEW
使用
为了克服explode
的局限性,通常会结合LATERAL VIEW
一起使用。LATERAL VIEW
可以将explode
生成的结果与原表的其他字段关联起来:
SELECT id, fruit
FROM array_table
LATERAL VIEW explode(items) exploded_items AS fruit;
3.map的使用以及练习
在 Hive 中,MAP
是一种复杂数据类型,用于存储键值对(Key-Value)结构的数据。
1. Hive 中 MAP
的基本概念
MAP
是一种无序的键值对集合,键(Key)必须是唯一的,而值(Value)可以是任意数据类型(如 STRING、INT、ARRAY 等)。键通常为 STRING 类型。
2. 创建包含 MAP
字段的表
可以通过 CREATE TABLE
语句创建一个包含 MAP
类型字段的表。例如:
CREATE TABLE user_info (
user_id INT,
user_attributes MAP<STRING, STRING>
);
在这个例子中,user_attributes
是一个 MAP
类型字段,键和值都是字符串。
3. 向 MAP
字段插入数据
使用 INSERT INTO
语句向表中插入数据,可以通过 MAP
函数构造键值对。例如:
INSERT INTO TABLE user_info VALUES
(1, MAP('name', 'Alice', 'age', '30')),
(2, MAP('name', 'Bob', 'age', '25'));
在这个例子中,MAP
函数用于创建键值对。
4. 查询 MAP
数据
4.1查询 MAP
中的某个键值
可以通过 []
运算符访问 MAP
中的特定键值。例如:
SELECT user_id, user_attributes['name'] AS name, user_attributes['age'] AS age
FROM user_info;
4.2查询 MAP
的所有键或值
可以使用 map_keys()
和 map_values()
函数分别获取 MAP
的所有键和值。例如:
SELECT map_keys(user_attributes) AS keys, map_values(user_attributes) AS values
FROM user_info;
结果会返回一个数组,包含所有键或值。
合并两个 MAP
可以使用 map_concat()
函数合并两个 MAP
。例如:
SELECT map_concat(map('gender', 'female'), user_attributes)
FROM user_info;
5. 练习示例
示例 1:创建表并插入数据、
CREATE TABLE student_scores (
student_id INT,
scores MAP<STRING, INT>
);
INSERT INTO TABLE student_scores VALUES
(1, MAP('Math', 90, 'English', 85)),
(2, MAP('Math', 70, 'English', 80));
示例 2:查询特定科目成绩
SELECT student_id, scores['Math'] AS math_score
FROM student_scores;
示例 3:查询包含特定键值的数据
SELECT * FROM student_scores
WHERE scores['Math'] > 80;
示例 4:合并 MAP
SELECT student_id, map_concat(map('Science', 95), scores)
FROM student_scores;