Hive 面试题（十）

1. 为什么内部表的删除，就会将数据全部删除，而外部表只删除表结构? 为什么用外部表更好？

内部表和外部表的删除行为之所以不同，是因为它们的设计目的和使用场景有所区别：

内部表删除行为：

生命周期管理：内部表的生命周期与Hive紧密绑定。当内部表被删除时，Hive会认为表及其数据不再需要，因此会同时删除表的元数据和数据文件，以释放存储空间和维护数据的一致性。
数据安全：内部表提供了一种安全的数据管理方式，确保在删除表时不会遗留不需要的数据。

外部表删除行为：

元数据与数据分离：外部表的元数据存储在Hive中，而数据文件存储在HDFS上指定的位置。这种设计允许数据文件独立于Hive的生命周期存在。
数据共享：外部表使得数据可以被多个系统或工具共享。即使Hive表被删除，数据文件仍然可以在其他系统中使用。
数据保护：删除外部表时，Hive不会删除数据文件，从而保护数据不被意外删除，这对于重要的数据备份和跨系统的数据分析非常有用。

为什么使用外部表更好：

数据重用：外部表允许数据在不同的分析和处理系统之间重用，无需复制数据。
数据保护：外部表提供了额外的数据保护层，因为即使表被删除，原始数据仍然安全地存储在HDFS上。
跨系统共享：外部表支持跨Hive实例的数据共享，这对于大型组织中不同团队之间的协作非常有用。
数据管理：外部表允许数据由其他系统或应用程序管理，这有助于维护数据的一致性和完整性。
灵活性：外部表提供了更大的灵活性，使得数据可以在不同的查询和分析工具之间轻松迁移。

总结：

选择内部表还是外部表取决于具体的使用场景和需求。如果数据是临时的，或者完全由Hive管理，内部表可能是更好的选择。如果数据需要跨系统共享，或者需要额外的数据保护，外部表可能更合适。在实际应用中，根据数据的重要性、使用频率和共享需求来做出选择。

2. 简述Hive建表语句？创建表时使用什么分隔符？

在 Hive 中，创建表的基本语法如下：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
  column1_name column1_datatype [COMMENT 'column1_comment'],
  column2_name column2_datatype [COMMENT 'column2_comment'],
  ...
)
[COMMENT 'table_comment']
ROW FORMAT DELIMITED
FIELDS TERMINATED BY char
[LINES TERMINATED BY char]
[STORED AS {TEXTFILE | SEQUENCEFILE | RCFILE | ORC | PARQUET | AVRO | JSON}]
[LOCATION 'table_path']
[TBLPROPERTIES (property_name=property_value, ...)];

下面是对创建表语句中各个部分的简要说明：

IF NOT EXISTS：可选，如果表已存在，则不创建新表。
db_name：可选，指定数据库名称。
table_name：要创建的表的名称。
column1_name、column2_name：列的名称。
column1_datatype、column2_datatype：列的数据类型，例如 INT、STRING、DOUBLE 等。
COMMENT：可选，为列或表提供描述。
ROW FORMAT DELIMITED：指定行格式为分隔的。
FIELDS TERMINATED BY char：指定字段（列）之间的分隔符，例如 ','。
LINES TERMINATED BY char：指定行之间的分隔符，例如 \n。
STORED AS：指定存储格式，例如 TEXTFILE、ORC、PARQUET 等。
LOCATION：可选，指定表数据存储的 HDFS 路径，如果指定为外部表，则数据存储在该位置。
TBLPROPERTIES：可选，为表设置属性。

创建表时使用的分隔符是在 FIELDS TERMINATED BY 和 LINES TERMINATED BY 子句中定义的。这些分隔符可以是任何字符，如逗号（,）、制表符（\t）、空格（）或换行符（\n）等。

例如，创建一个简单的 Hive 表，字段由逗号分隔，行由换行符分隔：

CREATE TABLE employees (
  id INT,
  name STRING,
  salary FLOAT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

在这个例子中，字段由逗号分隔，行由换行符分隔，数据以文本文件格式存储。

3. 简述Hive删除语句外部表删除的是什么？

当使用Hive的删除语句删除一个外部表（External Table）时，Hive会执行以下操作：

删除元数据：Hive会从其元数据库中删除与该外部表相关的元数据信息。这意味着Hive不再认识这个表，无法通过Hive查询到这个表的信息。
保留数据文件：与内部表不同，删除外部表时，Hive不会删除存储在Hadoop分布式文件系统（HDFS）或其他文件系统上的数据文件。这些数据文件仍然存在于它们原来的位置。
表结构移除：表的结构定义，包括列名、数据类型等，将从Hive的元数据库中移除。
依赖关系解除：如果该外部表被其他Hive对象（如视图或分区表）所依赖，这些依赖关系也会被解除。

简而言之，删除外部表主要删除的是Hive对该表的认知和元数据信息，而不会影响实际存储在文件系统中的数据文件。这样做的好处是，即使表被删除，数据仍然可以被其他系统访问和使用，提供了数据的安全性和灵活性。

4. 简述Hive导入数据的五种方式是什么?举例说明？

Hive 支持多种方式导入数据到表中，以下是五种常见的数据导入方式：

LOAD DATA语句：
使用 LOAD DATA 语句可以直接将数据文件从 HDFS 加载到 Hive 表中。这种方式不会对数据进行任何转换，数据将被直接移动到表的存储位置。
```
LOAD DATA INPATH 'hdfs_path_to_your_datafile' INTO TABLE your_table;
```
例如，将 HDFS 上的 /user/hive/warehouse/data/sample_data.txt 文件加载到 employees 表中：
```
LOAD DATA INPATH '/user/hive/warehouse/data/sample_data.txt' INTO TABLE employees;
```
INSERT语句：
使用 INSERT 语句可以向 Hive 表中插入数据。这种方式通常用于插入查询结果或手动输入的数据。
```
INSERT INTO TABLE your_table
VALUES (value1, value2, ...);
```
例如，向 employees 表插入一条新记录：
```
INSERT INTO TABLE employees VALUES (1, 'John Doe', 50000.0);
```
INSERT OVER SELECT：
使用 INSERT OVER SELECT 可以基于查询结果向表中插入数据。这种方式允许你从另一个表或查询结果中选择数据并插入到目标表。
```
INSERT INTO TABLE your_table
SELECT columns FROM another_table WHERE conditions;
```
例如，从 employees_temp 表中选择所有记录并插入到 employees 表中：
```
INSERT INTO TABLE employees SELECT * FROM employees_temp;
```

使用外部表：
创建一个外部表指向数据文件的存储位置，然后使用 INSERT INTO 将数据导入到另一个 Hive 表中。这种方式的好处是原始数据文件不会被移动或修改。

CREATE EXTERNAL TABLE IF NOT EXISTS external_table_name (
  columns_definition
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 'hdfs_path_to_external_data';

INSERT INTO TABLE your_table
SELECT * FROM external_table_name;

使用Hive的导入工具：
Hive 没有内置的导入工具，但可以使用其他 Hadoop 生态系统工具，如 Apache Sqoop，来导入数据到 Hive。Sqoop 是一个用于在 Hadoop 和关系数据库之间传输数据的工具。

sqoop import \
  --connect jdbc_connection_string \
  --username username \
  --password password \
  --table source_table \
  --hive-import \
  --create-hive-table \
  --hive-table hive_table_name \
  --hive-database hive_database_name \
  --hive-metastore-connect metastore_connection_string

例如，使用 Sqoop 从 MySQL 数据库导入数据到 Hive：

sqoop import \
  --connect jdbc:mysql://localhost:3306/your_database \
  --username your_username \
  --password your_password \
  --table your_table \
  --hive-import \
  --create-hive-table \
  --hive-table your_hive_table \
  --hive-database your_hive_database \
  --hive-metastore-connect jdbc:derby://localhost:1527/metastore_db;

以上是 Hive 导入数据的五种常见方式，每种方式都有其适用场景和优势。

5. 简述row_number()，rank()和dense_rank()的区别？

row_number()、rank()和dense_rank()是SQL中的窗口函数，它们用于为结果集中的每一行分配一个唯一的序号或排名。下面是它们的主要区别：

row_number()：
- row_number()为结果集中的每一行分配一个唯一的序号，序号是连续的，从1开始，不会跳过任何数字。
- 即使有相同的值，row_number()也会为每一行分配一个不同的序号。
- 它不考虑行之间的值是否相同。
rank()：
- rank()为结果集中的每一行分配一个排名，如果存在相同的值，它们会获得相同的排名，并且排名会跳过后续的数字。
- 例如，如果有两行并列第一，它们的排名都是1，下一行的排名将会是3，而不是2。
- 它考虑行之间的值是否相同，并为相同的值分配相同的排名。
dense_rank()：
- dense_rank()也用于为结果集中的每一行分配排名，与rank()类似，它会为相同的值分配相同的排名。
- 不同之处在于，dense_rank()不会跳过排名数字，即使有并列排名，后续的排名仍然是连续的。
- 例如，如果有两行并列第一，它们的排名都是1，下一行的排名将会是2，保持排名数字的连续性。

示例说明：

假设有一个员工表，我们根据工资进行排序，并使用这些窗口函数来分配序号或排名：

员工ID	工资
1	5000
2	5000
3	4000
4	3000
5	3000

使用row_number()：
- 序号：1, 2, 3, 4, 5
使用rank()：
- 排名：1, 1, 3, 4, 4
使用dense_rank()：
- 排名：1, 1, 2, 3, 3

总结来说，row_number()用于分配连续的序号，不考虑值的相同性；rank()和dense_rank()用于分配排名，考虑值的相同性，但rank()会在排名中跳过数字，而dense_rank()则保持排名数字的连续性。

6. 简述请说明hive中 Sort By，Order By，Cluster By，Distrbute By各代表什么意思？

在 Hive 中，SORT BY、ORDER BY、CLUSTER BY 和 DISTRIBUTE BY 是用于控制查询结果排序和数据分布的关键字。下面是它们各自的含义和用法：

ORDER BY：
- ORDER BY 是 SQL 中的标准关键字，用于对查询结果进行排序。
- 在 Hive 中，ORDER BY 通常在 MapReduce 作业的最后一个 reduce 阶段进行排序，这意味着结果集会被全局排序。
- 如果 ORDER BY 后面跟着 LIMIT，则可以在单个 reduce 上进行排序，这可以减少数据的移动。
示例：
```
SELECT * FROM table_name ORDER BY column1;
```
SORT BY：
- SORT BY 在功能上与 ORDER BY 类似，也是用于对查询结果进行排序。
- 但是，SORT BY 只在每个 reducer 的输出上进行排序，而不是全局排序。
- 当使用多个 reducer 时，每个 reducer 的输出会被排序，但不同 reducer 之间的输出顺序是不确定的。
示例：
```
SELECT * FROM table_name SORT BY column1;
```
DISTRIBUTE BY：
- DISTRIBUTE BY 用于将数据分发到不同的 reducer 上，它根据指定的列的值来决定数据如何分布。
- 这个关键字可以确保具有相同列值的数据行被发送到同一个 reducer 上。
- 它对于数据的分组操作非常有用，例如，当你需要对每个组执行聚合操作时。
示例：
```
SELECT * FROM table_name DISTRIBUTE BY column1;
```
CLUSTER BY：
- CLUSTER BY 是 DISTRIBUTE BY 和 SORT BY 的组合。
- 它不仅根据指定的列的值将数据分发到不同的 reducer 上，还会在每个 reducer 上对数据进行排序。
- CLUSTER BY 通常用于聚合操作，如 GROUP BY，它确保了具有相同列值的数据行在同一个 reducer 上，并且是有序的。
示例：
```
SELECT * FROM table_name CLUSTER BY column1;
```

在实际使用中，选择哪个关键字取决于你的具体需求。如果你需要全局排序，使用 ORDER BY；如果你需要数据在每个 reducer 上有序，但全局无序，使用 SORT BY；如果你需要控制数据的分布，使用 DISTRIBUTE BY；如果你需要数据既有序又按特定键分布，使用 CLUSTER BY。

7. 写出Hive中split、coalesce及collect_list函数的用法？

在Hive中，split、coalesce和collect_list是三种不同的函数，它们各自有不同的用法：

split 函数：
split函数用于将一个字符串按照指定的分隔符分割成多个子字符串，并将这些子字符串存储在一个数组中。它的基本语法如下：
```
SPLIT(string, delimiter)
```
- string：要分割的原始字符串。
- delimiter：用于分割字符串的分隔符。
例如，如果你有一个由逗号分隔的字符串，你想将其分割成多个部分：
```
SELECT SPLIT('apple,banana,orange', ',');
```
这将返回一个数组：['apple', 'banana', 'orange']。
coalesce 函数：
coalesce函数用于从其参数列表中返回第一个非空表达式。如果所有参数都是空值，则结果为NULL。它的基本语法如下：
```
COALESCE(expression1, expression2, ...)
```
- expression1, expression2, ...：要检查的表达式列表。
例如，如果你想从一个或多个列中获取第一个非空值：
```
SELECT COALESCE(column1, column2, 'default_value') AS non_null_column
FROM table_name;
```
如果column1不是NULL，它将返回column1的值；如果column1是NULL但column2不是，它将返回column2的值；如果两者都是NULL，它将返回'default_value'。
collect_list 函数：
collect_list函数用于将多个行中的值收集到一个数组中。它通常与GROUP BY子句一起使用，以收集分组后的结果。它的基本语法如下：
```
collect_list(expression)
```
- expression：要收集的列。
例如，如果你想收集每个组中的所有值到一个数组：
```
SELECT grouping_column, COLLECT_LIST(value_column)
FROM table_name
GROUP BY grouping_column;
```
这将为每个grouping_column的值返回一个包含value_column所有值的数组。