Hive内部表与外部表的区别

内部表 (managed table) : 未被external修饰

外部表 (external table) :被external修饰

区别:

存储位置: 内部表的数据存储在Hive的默认文件系统中，而外部表的数据存储在外部文件系统中。外部文件系统可以是本地文件系统、HDFS等，这使得外部表可以方便地与其他系统进行数据共享和交互。

数据管理: 当删除内部表时，Hive会自动删除该表及其数据。而删除外部表时，只会删除表的元数据，而不会删除表的数据。这是因为内部表数据由Hive自身管理，外部表的数据并不是由Hive管理的(由HDFS管理)，因此在删除外部表时需要手动清理数据。

表的创建方式:内部表可以通过CREATE TABLE语句进行创建，也可通过Hive自动将HDFS上的目录映射为表的方式进行创建。而外部表只能通过CREATE EXTERNAL TABLE语句进行创建。

数据导入:对于内部表，可以通过INSERT语句将数据导入到表中。而对于外部表，需要先将数据存储在外部文件系统中，然后通过LOAD DATA语句将数据加载到表中。

应用场景:

1）内部表适合存储对数据的修改和删除操作，因为删除内部表时可以直接删除相关的数据文件。一般内部表在数仓中的DW(细节层) 中使用；

2）外部表适合存储对数据的只读操作，比如将已经存在的数据文件导入到Hive中进行分析。一般外部表在数仓中的ODS层(贴源层) 中使用。

HQL 行列转换

1、行转列: UDF聚合函数

1) 相关函数

concat: 返回输入字符串连接后的结果，支持任意个输入字符串;

concat ws(separator, str1,str2,..: 它是一个特殊形式的 concat0；第一个参数传入参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

collect set(col): 函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

2) 数据准备

3) 创建hive表并导入数据

create table person info(
  name string,
  constellation string,
  blood type string)
row format delimited fields terminated by "t";
load data local inpath "/opt/module/datas/constellation.txt" into table person info;

注：该表采用的是文本格式的数据文件，字段之间使用制表符 \t 进行分隔，因此在 create table 语句中使用了 row format delimited fields terminated by "\t" 来指定字段分隔符为制表符。其中 row format delimited 表示指定行的格式，fields terminated by "\t" 表示指定字段之间的分隔符为制表符。

接下来，使用 load data 语句将文本文件 /opt/module/datas/constellation.txt 中的数据导入到 person_info 表中。load data 语句用于将数据从外部存储器（如本地文件系统或 HDFS）导入到 Hive 表中。其中 local inpath 表示指定本地文件路径，/opt/module/datas/constellation.txt 是需要导入的文本文件的路径，into table 表示将数据导入到指定的表名 person_info 中。

需要注意的是，如果该表已经存在，则可以使用 insert into 语句将新数据插入到表中，或者使用 truncate table 语句清空表中的数据并重新导入数据。另外，在导入数据之前，需要确保表结构的定义与数据文件的格式相匹配，否则可能会导致数据导入失败或数据丢失等问题。

4)需求

把星座和血型一样的人归类到一起。结果如下:

select name,concat(constellation, " ",blood type) as base from person_info;

select
	t1.base concat_ws ('|',collect_set ( t1.name )) name 
from
	( select name, concat( constellation, ",", blood type )  as  base from person_info; ) t1 
group by t1.base;

2、列转行: UDTF爆炸函数

1)相关函数

explode(col): 将hive一列中复杂的array或者map结构拆分成多行

lateral view

用法: lateral view udtf(expression) tableAlias AS columnAlias

解释: 用于和split,explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2)数据准备

3) 创建hive表并导入数据

create table movie_info(

movie string,

category array<string>)

row format delimited fields terminated by "t"
collection items terminated by ",";

load data local inpath "/opt/module/datas/movie.txt" into table movie_info

4) 需求

将电影分类中的数组数据展开

select movie,category name from movie_info
lateral view explode(category) table_category as category_name