Hive数仓操作（三）

一、Hive 数据库操作

1. 创建数据库

• 基本创建数据库命令：

CREATE DATABASE bigdata;

• 说明：

  • 数据库会在 HDFS 中以目录的形式创建和保存，数据库名称会存储在 Hive 的元数据中。
  • 如果不指定目录，数据库将在 /user/hive/warehouse 下创建。

• 手动指定目录（了解）：

CREATE DATABASE IF NOT EXISTS db_test LOCATION '/test';

• 说明：
  • 使用 IF NOT EXISTS 可以避免因数据库已存在而导致的错误。
  • LOCATION 用于指定数据库在 HDFS 中的存储位置。

2. 查看所有数据库

• 查看数据库命令：

SHOW DATABASES;

• 说明：
  • 该命令将列出所有已创建的数据库名称。

3. 切换数据库

• 切换到指定数据库：

USE bigdata;

• 说明：
  • 切换当前会话的数据库，以后执行的查询和操作将在该数据库中进行。

4. 删除数据库

• 删除数据库命令：

DROP DATABASE database_name;

• 说明：

  • 该命令将删除指定的数据库。如果数据库中存在表，默认情况下会报错。

• 强制删除数据库：

DROP DATABASE database_name CASCADE;

• 说明：

  • 使用 CASCADE 关键字可以强制递归删除，允许删除包含表的数据库。

• 安全删除，如果数据库存在：

DROP DATABASE IF EXISTS database_name CASCADE;

• 说明：
  • IF EXISTS 关键字用于避免因数据库不存在而导致的错误。

5. 显示数据库信息

• 显示数据库信息：

DESC DATABASE bigdata;

• 说明：
  • 该命令将显示指定数据库的详细信息，包括数据库的名称、位置、创建时间等。

---

二、Hive 创建内部表

Hive 表的类型

在 Hive 中，有多种类型的表，主要包括：

1. 内部表（Managed Table）：

   • 默认的表类型。创建时，Hive 会在默认的 warehouse 目录中创建一个以表名命名的目录，并将数据存储在该目录下。
   • 当删除内部表时，表结构和数据都将被删除。

2. 外部表（External Table）：

   • 创建时，Hive 不会管理数据的存储位置。数据可以存储在 HDFS 中的任何位置。
   • 删除外部表时，只删除表结构，而不会删除数据。

3. 分区表（Partitioned Table）：

   • 将表的数据根据某个列的值进行分区，从而提高查询效率。
   • 每个分区对应一个子目录。

4. 分桶表（Bucketed Table）：

   • 将数据分成多个桶（文件），并可以在查询时提高性能。
   • 通常与分区表结合使用。

创建内部表的步骤

在 Hive 中创建一个表通常遵循以下步骤：

1. 准备数据文件：

   • 数据文件可以在 Linux 文件系统或 HDFS 中存在。

2. 创建表：

   • 表的结构需要与数据文件的格式相对应。

3. 加载数据：

   • 使用 LOAD DATA 命令将数据导入表中。

创建表的语法结构

创建表的基本语法如下：

CREATE TABLE 表名 (列名1 类型,列名2 类型,...
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '分隔符';

---

三、Hive 内部表的其他操作

1. 向内部表中插入数据

可以使用 INSERT INTO 语句将数据插入内部表，不常单条插入，一般select插入。

INSERT INTO TABLE student VALUES (1, 'Alice', 20);

2. 查询内部表

查询内部表使用标准的 SELECT 语句：

SELECT * FROM student;

3. 删除内部表

使用 DROP TABLE 语句可以删除内部表。与外部表相同，删除内部表时，会删除该表的元数据和所有相关数据。

DROP TABLE IF EXISTS student;

4. 更新内部表

在 Hive 中，内部表不支持直接的 UPDATE 操作，但可以通过创建新表的方式来实现更新。

示例：

首先查询并过滤需要更新的数据，然后插入到新表中：

CREATE TABLE student_temp AS 
SELECT * FROM student WHERE id != 1;-- 然后删除旧表
DROP TABLE student;-- 重命名新表为旧表名
ALTER TABLE student_temp RENAME TO student;

---

示例：创建一个学生表（hive的stu数据库）

stu 数据库创建：

CREATE DATABASE IF NOT EXISTS stu;

插入数据之前，需要切换到 stu 数据库：

USE stu;

创建一个学生表：

CREATE TABLE stu (id INT,name STRING,age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

• 说明：
  • ROW FORMAT DELIMITED 指定了行的格式。
  • FIELDS TERMINATED BY ',' 指定了列之间的分隔符为逗号。

从 HDFS 导入数据

假设本地 Linux 文件系统有一个文件 /opt/datas/stu.txt，可以使用 hadoop fs -put 命令将其上传到 HDFS。

hadoop fs -put /opt/datas/stu.txt /datas/

然后将数据从 HDFS 导入到表中：

LOAD DATA INPATH '/datas/stu.txt' INTO TABLE stu;

OVERWRITE：

• 语法：

  LOAD DATA INPATH '/datas/stu.txt' OVERWRITE INTO TABLE stu;
  

  • 如果表 stu 已经存在数据，执行该命令后，表中的所有现有数据将被删除，并用新的数据替换。
  • 这意味着结果表只会包含新加载的 stu.txt 文件中的数据。

从本地Linux导入数据

将数据从本地文件系统导入到表中：

LOAD DATA LOCAL INPATH '/opt/datas/stu.txt' INTO TABLE stu;

查询表的数据

查询表中的数据：

SELECT * FROM stu;

注意

• 当你创建 stu 表并加载数据后，HDFS 的 warehouse 目录下会生成一个名为 stu 的子目录，存储了表的数据文件。
• /user/hive/warehouse/stu.db/stu该目录通常包含多个文件（如从hdfs目录/datas移动而来的文件/datas/stu.txt变成 /user/hive/warehouse/stu.db/stu/stu.txt），这些文件存储的是实际的数据。

---