Hive基本操作_① 创建一个名为studentinfo的数据库。 ② 查看hive中包含的所有数据库。 ③-CSDN博客

本文链接：https://blog.csdn.net/weixin_43786255/article/details/99341855

Hive基本操作

1 DDL操作

1.1 数据库操作

（1）创建库：

#语法结构

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, ...)];

创建普通库并检查是否存在：

CREATE DATABASE IF NOT EXISTS database_name;

创建库的时候带注释：

CREATE DATABASE IF NOT EXISTS database_name COMMENT 'Is my database'

创建库的时候指定位置：

CREATE DATABASE database_name LOCATION '/user/hive/database1.db';

创建带属性的库：

CREATE DATABASE IF NOT EXISTS database_name WITH DBPROPERTIES ('a'='a','b'='b');

（2）切换库：

USE database_name

（3）查看库：

①查看正在使用的库：

SELECT current_database();

②查看库列表：

SHOW DATABASES 或SHOW DATABASES LOKE 'a*'（查看数据库以a开头的数据库）;

③查看库的详细描述信息：

DESC DATABASE [EXTENDED] database_name;

④查看创建库的详细语句：

SHOW CREATE DATABASE database_name;

（4）删除库

①删除空数据库：

DROP DATABASE IF EXISTS database_name;

②删除包含表的数据库：

DROP DATABASE IF EXISTS database_name CASCADE;

默认情况下就是 restrict：

DROP DATABASE IF EXISTS database_name == DROP DATABASE IF EXISTS database_name RETRICT

注意：Hive不支持修改数据库

1.2 表操作

（1）建表

语法结构

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], ...)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfs_path]

EXTERNAL：外部表关键字，存在则创建外部表，不存在则为内部表

IF NOT EXISTS：建表防止报错，表不存在则创建

COMMENT：为表与字段增加描述信息

PARTITIONED BY：分区的表示，分区分为两种：一种是单分区，表文件夹目录下只有一级文件夹目录；一种是多分区，表文件夹下出现多文件夹嵌套模式

CLUSTERED BY：指定分桶，对于每一个表（table）或者分区，Hive 可以进一步组织成桶，

SORTED BY：指定每一个桶的是否排序的

ROW FORMAT：指定格式化，通常情况下用于指定列之间的分隔符

ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char]

[COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char]

[LINES TERMINATED BY char] |

SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

STORED AS ：文件数据格式，默认是 textFile 格式，有TEXTFILE（存文本） | SEQUENCEFILE （二进制文件）| RCFILE（行列存储相结合的存储方式）

LOCATION：指定数据文件存放的 HDFS 目录，默认路径 /user/hive/warehouse

注意：分区字段一定不能再建表字段中分区字段是一个全新的字段。分桶字段一定是建表字段中的。

①创建内部表

CREATE TABLE IF NOT EXISTS table_case1(id int,name string,age int)

ROW FORMAT DELIMITED FIELDS TERMINATED BY "," STORED AS textfile

②创建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS table_case2(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY "," LOCATION"/user/hive/share/table_case2"

③创建分区表

CREATE TABLE IF NOT EXISTS table_case3(id int,name string,age int) PARTITIONED BY (dept String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ","

④创建分桶表

CREATE TABLE IF NOT EXISTS table_case4(id int,name string,age int,dept String) CLUSTERED BY (dept ) SORTED BY (dept desc) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ","

⑤like关键字表复制（只复制表结构(字段)，不会复制表属性）

CREATE [EXTERNAL] TABLE table_copy LIKE table_case1

⑥CTAS语句建表

CREATE TABLE table_ctas AS SELECT * FROM table_case1

（2）查看

①查看表列表

SHOW TABLES

SHOW TABLES IN database_name (查看某一个数据库下的)

SHOW TABLES LIKE "*t*" (模糊查看表列表)

②查看表的详细描述信息

DESC table_name (查看字段信息)

DESC EXTENDED table_name (查看表的详细信息)

DESC FORMATTED table_name (查看表的详细信息，格式化显示)

③查看表的建表语句

SHOW CREATE TABLE table_name

④查看表的分区信息

SHOW PARTITIONS table_name

SHOW PARTITIONS table_name PARTITION (dept="AA")

（3）修改表

①重命名表

ALTER TABLE table_name RENAME TO new_table_name

②修改表的列信息

Ⅰ 修改列的类型

ALTER TABLE table_name CHANGE old_col new_col new_type

Ⅱ 添加列

ALTER TABLE table_name ADD COLUMNS (col type)

Ⅲ 替换列(将整个表的所有列替换为指定的列)

ALTER TABLE table_name REPLACE COLUMNS (col type...)

注意：不支持删除列

③修改分区信息

Ⅰ 添加分区

ALTER TABLE table_name ADD PARTITION (分区字段=分区值) [PARTITION (分区字段=值)]

[ LOCATION "路径"]

Ⅱ 修改分区的存储路径

ALTER TABLE table_name PARTITION (分区字段=分区值) SET LOCATION "路径"

Ⅲ 删除分区

ALTER TABLE table_name DROP PARTITION (分区字段=分区值)

（4）删除表

①清空表数据（只针对内部表，将表对应的HDFS的目录下的文件删除了）

TRUNCATE TABLE table_name

②删除表

DROP TABLE IF EXISTS table_name

2 DML操作

2.1插入数据

（1）LOAD向表中装置数据

将一个已经存在的文件（本地或|HDFS）加载到Hive表中按照Hive表中指定的分割方式进行解析这个数据：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

load的操作本质：将数据挪到hive表所在的目录下（HDFS文件是将指定路径数据，移动到Hive表所在的HDFS的路径的）

（2）INSERT插入数据

①单条数据插入，一次插入一条数据

INSERT INTO TABLE table_name VALUES((XX,YY)

②单重数据插入，一次性插入一个sql的查询结果

INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)] SELECT...

③多重数据插入，一次扫描表，但是最终将多个查询结果，插入到多张表中或者一个表的多个分区中

FROM from_statement

INSERT OVERWRITE [INTO] TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 ...)]SELECT...

INSERT OVERWRITE [INTO] TABLE table_name2 [PARTITION (partcol1=val1, partcol2=val2 ...)] SELECT...

④分区插入

分区插入有两种：静态分区和动态分区。如果混合使用静态分区和动态分区，则静态分区必须出现在动态分区之前。

静态分区：创建静态分区表 --》从查询结果中导入数据 --》查看插入结果

INSERT INTO TABLE table_name PARTITION（partcol1=val1） SELECT X，Y，Z FROM table_name1 partcol1=val1

动态分区：静态分区需要创建非常多的分区，Hive 提供了一个动态分区功能，可以基于查询参数推断出需要创建的分区名称。创建分区表--->参数设置(set hive.exec.dynamic.partition=true和set hive.exec.dynamic.partition.mode=nonstrict)--》动态数据插入。

动态分区默认情况下是开启的。但是却以默认是”strict”模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。

INSERT INTO TABLE table_name PARTITION（partcol1=val1, partcol2=val2 ...） SELECT X，Y，Z，val1,val2 FROM table_name1

查询语句 select 查询出来的动态分区 val1和 val2必须放最后，和分区字段对应，不然结果会出错

2.2 导出数据

语法结构：①单模式导出： INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement

②多模式导出： FROM from_statement INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1 [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

2.3 查询数据

Hive 中的 SELECT 基础语法和标准 SQL 语法基本一致，支持 WHERE、DISTINCT、GROUP BY、 ORDER BY、HAVING、LIMIT、子查询等；

不支持的语法有：①不支持 update 和 delete

②hive 虽然支持 in/exists（老版本是不支持的），但是 hive 推荐使用 semi join 的方式来代替实现，而且效率更高。

③不支持非等值连接：如select a.*, b.* from a join b on a.id > b.id

语法结构：

SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...

FROM table_name a

[JOIN table_other b ON a.id = b.id]

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY col_list] ]

[LIMIT number]

分桶查询指定 reducetask 数量，也就是指定桶的数量：set mapreduce.job.reduces=4;

设置本地模式：set hive.exec.mode.local.auto=true;

2.4 join查询

语法结构

table_reference JOIN table_factor [join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

| table_reference LEFT SEMI JOIN table_reference join_condition

Hive 支持等值连接（equality join）、外连接（outer join）和（left/right join）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务，也支持多表连接。

注意点：①只支持等值链接，支持 and，不支持 or；

②可以 join 多于 2 个表；

③Join 时，reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）；

④HiveJoin 分三种：inner join, outer join, semi join。outer join 包括 left join，right join 和 full outer join,主要用来处理 join 中空记录的情况。

inner join（内连接）（把符合两边连接条件的数据查询出来）；

left join（左连接，等同于 left outer join），以左表数据为匹配标准，匹配不上的就是 null ，返回的数据条数与左表相同

right join（右连接，等同于 right outer join），以右表数据为匹配标准，匹配不上的就是 null ，返回的数据条数与右表相同

left semi join（左半连接）（因为老版本 hive 不支持 in/exists 操作（1.2.1 版本后的 hive 支持 in 的操作），所以用该操作实现，并且是 in/exists 的高效实现）

full outer join（完全外链接）

mysql中的in/exists

SELECT * FROM A WHERE id IN (SELECT id FROM B);

等价于：1、SELECT id FROM B ----->先执行in中的查询

2、SELECT * FROM A WHERE A.id = B.id

in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。

exists

语法：SELECT 字段 FROM table WHERE EXISTS（subquery）;

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

等价于：

1、SELECT * FROM A;

2、SELECT I FROM B WHERE B.id = A.id;

EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase,EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

left semi join特点：

①left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在其他地方过滤都不行。

②只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。

③因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。