hive的相关操作

hive的相关操作

---

---

一、Hive数据库操作

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

其中：WITH DBPROPERTIES ，用来指定数据属性数据。

• 创建带有属性的数据库

create database testdb WITH DBPROPERTIES ('creator' = 'tp', 'date' = '2024-06-12');

• 显示创建语句

show create database testdb;

• 显示所有数据库

show databases;

• 删除数据库

DROP DATABASE  [IF EXISTS] database_name [RESTRICT|CASCADE];

注：

• RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。
• CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

二、Hive表操作

1.建表语法

-- EXTERNAL 代表外部表                                  
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] 
-- 表所对应的hdfs目录
  [LOCATION hdfs_path]  

2.表分类

• 内部表

​ 内部表又称受控表，当删除内部表的时候，存储在文件系统上的数据（例如HDFS上的数据）和元数据都会被删除。先有内部表，再向表中插入数据。

--创建inner_test表(内部表)
CREATE TABLE inner_test(word string, num int); 

注：删除内部表，表对应的hdfs目录也一并删除

-- 创建emp职工表（内部表）
CREATE TABLE emp(
empno int, 
ename string,
job string, 
mgr int, 
hiredate string,
sal double, 
comm double, 
deptno int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

• 外部表

​ 创建外部表需要使用EXTERNAL关键字，当删除外部表的时候，只删除元数据，不删除数据。

​ 使用场景，例如：某个公司的原始日志数据存放在一个目录中，多个部门对这些原始数据进行分析，那么创建外部表就是比较好的选择了，因为即使删除了外部表，原始数据并不会被删除。

• 分区表

​ 分区表是为了防止暴力扫描全表，提高查询效率。分区字段在源文件中是不存在的，需要在添加数据的时候手动指定。每一个分区对应一个目录。通过partitioned by来在创建分区表的时候添加分区字段。分区表可以是内部表，也可以是外部表。
​ 使用场景：可以通过分区表，将每天搜集的数据进行区分，查询统计的时候通过指定分区，提高查询效率。

---

2.导入数据到表中

• 追加Linux本地文件里的数据到emp表

load data local inpath '/root/data/emp.txt' into table emp;

• overwrite覆盖操作，使用Linux本地文件里的数据覆盖掉原来emp表的数据

load data local inpath '/root/data/emp.txt' overwrite into table emp;

• 将hdfs上某文件里的数据追加到emp表（此操作完毕后，hdfs上的该文件就自动删除）

load data inpath 'hdfs://my2308-host:9000/data/emp.txt' into table emp;

• overwrite覆盖操作，使用hdfs上某文件里的数据覆盖掉emp表的数据（此操作完毕后，hdfs上的该文件就自动删除）

load data inpath 'hdfs://my2308-host:9000/data/emp.txt' overwrite into table emp;

2.分组查询

求每个部门的平均工资

select deptno, avg(sal) from emp group by deptno;

---