Hive数仓操作（五）

一、Hive 信息查看

1. Hive的元数据管理：

   • Hive 将表的元数据（如表名、列名、类型等）存储在关系型数据库中，通常是 MySQL。元数据的主要表包括：
     • TBLS：存储表的信息（表名、类型、ID 等）。
     • COLUMNS_V2：存储每个列的详细信息（列名、类型等）。
     • DBS：存储数据库的信息。
     • SDS：存储数据文件的位置。

2. Mysql数据字典查询：

   • 查询表信息：

     SELECT * FROM TBLS;               -- 查询表名
     SELECT * FROM COLUMNS_V2;         -- 查询列信息
     SELECT * FROM DBS;                 -- 查询数据库信息
     SELECT * FROM SDS;                 -- 查询数据文件位置信息
     

3. Hive查看表定义的命令：

   • 查看表的结构信息：

     DESC table_name;                    -- 查看表定义信息
     DESC EXTENDED table_name;           -- 查看表详细信息
     DESC FORMATTED table_name;          -- 格式化显示表定义的详细信息
     SHOW CREATE TABLE table_name;        -- 查看创建表的语句
     

4. Hive与MySQL的关系：

• Hive 将其元数据存储在 MySQL 数据库中，使用 MySQL 作为 Hive 的 metastore。
• MySQL 负责存储 Hive 表、列、数据库等的结构信息，而实际数据则存储在 HDFS 中。
• Hive 依赖于 Hadoop 提供的数据存储能力，同时通过 MySQL 管理其结构化的元数据。

二、 Hive表的复制方式

1. 复制一个表的结构（复制列的定义和数据类型，不包括分隔符，表属性等）和数据

使用 CREATE TABLE AS SELECT 语句，可以将一个查询的结果创建成一个新表。这种方法将同时复制表的结构和数据。

CREATE TABLE emp1 AS 
SELECT * FROM emp;

2. 复制一个表的某些列来创建新表

如果只想复制表中的特定列，可以在 SELECT 子句中指定所需的列。

CREATE TABLE emp2 AS 
SELECT empno, ename, deptno FROM emp;

3. 复制一个表的某些行来创建新表

如果想要根据特定条件复制表中的某些行，可以在 WHERE 子句中指定条件。

CREATE TABLE emp3 AS 
SELECT empno, ename, deptno FROM emp WHERE deptno = 20;

4. 复制表完整结构 （保留原表的所有属性包括分隔符、表注释等，但没有复制数据）

如果只想复制表的结构而不复制数据，可以使用 CREATE TABLE LIKE 语句。

CREATE TABLE emp4 LIKE emp;

三、 Hive表的插入方式

1. 将 SQL 语句的结果覆盖到已存在的表中

使用 INSERT OVERWRITE 语句将查询结果覆盖写入到一个已存在的表中。

INSERT OVERWRITE TABLE empl 
SELECT * FROM emp;

2. 将 SQL 语句的结果追加到已存在的表中

使用 INSERT INTO 语句将查询结果追加到已存在的表中。这将产生新的文件。

INSERT INTO TABLE empl 
SELECT * FROM emp;

3. 将 SQL 语句的结果保存到本地目录

使用 INSERT OVERWRITE LOCAL DIRECTORY 语句将查询结果保存到本地文件系统中。建议使用一个新空目录，以避免覆盖原有内容。

INSERT OVERWRITE LOCAL DIRECTORY '/opt/module/export/emp' 
SELECT * FROM emp;

4. 将 SQL 语句的结果格式化保存到 HDFS

使用 INSERT OVERWRITE DIRECTORY 语句将查询结果保存到 HDFS 目录中。需要指定结果的格式和分隔符。

INSERT OVERWRITE DIRECTORY '/datas/emp' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
SELECT * FROM emp;

5. 将查询结果格式化导出到本地

使用 INSERT OVERWRITE LOCAL DIRECTORY 并设置格式化选项，将查询结果导出到本地。

INSERT OVERWRITE LOCAL DIRECTORY '/opt/module/export/emp1' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
SELECT * FROM emp;

四、Hive 中的内部表与外部表

在 Hive 中，表的类型主要分为内部表（Managed Table）和外部表（External Table）。这两种表在数据管理和存储方面有一些显著的区别，下面将详细介绍它们的定义、特点以及使用场景。

1. 内部表（Managed Table）

定义:
内部表是指直接使用 CREATE TABLE 命令创建的表。Hive 会自动在数据库所在的目录中创建一个同名的子目录，并将数据存储在这个子目录中。

特点:

• 数据存储: 数据完全由 Hive 管理。当删除内部表时，表的结构和数据都将被删除。
• 删除操作: 使用 DROP TABLE 表名 命令时，将同时删除表的目录和数据，并移除在元数据库（如 MySQL）中对应的记录。

使用场景:

• 适用于需要 Hive 完全控制数据的场景，比如临时数据或计算中间结果。

• 在使用 Hive 内部表时，首先需要有一个数据文件（在 Linux 服务器或 HDFS 文件系统中）。然后可以建立一个与文件格式相对应的表，并通过以下方式将数据导入到表格中：

  LOAD DATA INPATH 'hdfs文件路径' INTO TABLE 表名;
  

2. 外部表（External Table）

定义:
外部表是通过 CREATE EXTERNAL TABLE 命令创建的表。用户可以指定数据存储的具体位置（即 location），而 Hive 不会在数据库目录中创建新的子目录。

特点:

• 数据存储: 外部表与数据文件之间建立了一种映射关系。数据文件可以存储在 HDFS 或者 Linux 文件系统中。
• 删除操作: 当使用 DROP TABLE 表名 命令删除外部表时，Hive 只会删除表的结构和元数据，数据文件会保留下来。

使用场景:

• 保存日志信息、埋点数据等需要保留的历史数据。
• 当数据文件可能被多个项目或用户共享时，使用外部表可以避免不必要的数据删除。

3. 创建外部表的语法

CREATE EXTERNAL TABLE 表名 (
    字段名 数据类型
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '分隔符'
LOCATION 'hdfs中的目录的位置';

4. 什么时候使用内部表和外部表

• 内部表:

  • 适合于对数据拥有完全控制权的场景。
  • 适合保存 SQL 查询的计算结果。

• 外部表:

  • 适合于日志信息和数据埋点信息等需要持久化的数据。
  • 适合于多个应用共享数据的情况。