hive的DDL和DML操作(一）

一、hive数据库

1. 创建数据库

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

create database testbase;#创建testbase数据库

(1) IF NOT EXISTS:判断该数据库是否存在，可选
(2) COMMENT database_comment:添加注释，可选
(3) LOCATION hdfs_path:指定数据库存放路径，默认为user目录下，可选
(4) WITH DBPROPERTIES (property_name=property_value, …)：参数信息，只是一些描述信息，可以添加当前创建库的是谁，时间是什么等信息，可选

2. 查数据库和修改数据库、删除数据库

(1) 查看数据库及其详情

  show databases; # 查看所有数据库
  desc databases default; #查看数据库信息
  desc database extended default; #显示数据库详细信息  

(2) 修改数据库

alter database testbase
set  dbproperties('createtime'='20211015'); # 修改创建时间
desc database extended testbase;#查看数据库

(3) 删除数据库

drop database testbase;#没有数据情况下
drop database testbase cascade;#有数据情况下，使用cascade强制删除

二、hive表

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

(1)EXTERNAL:创建一个外部表，内部表删除时，元数据和表中数据都删除，外部表删除表时不删除表中数据，删除元数据。
(2)COMMENT：为表和列添加注释
(3)PARTITIONED BY 创建分区表
(4)CLUSTERED BY 创建分桶表
(5)SORTED BY 不常用，对桶中的一个或多个列另外排序
(6)ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
(7) STORED AS S 指定存储文件类型
(8)LOCATION ：指定表在 HDFS 上的存储位置。
(9)AS：后跟查询语句，根据查询结果创建表。
(10)）LIKE 允许用户复制现有的表结构，但是不复制数据。

row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释：
row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符
lines terminated by ‘\n’; – 行分隔符

2. 内部表与外部表的转换

alter table text_table set tblproperties('EXTERNAL'='TRUE');
alter table text_table set tblproperties('EXTERNAL'='FALSE');

3. 修改表

(1) 修改表名

ALTER TABLE table_name RENAME TO new_table_name;#语法
alter table text_table rename to text1;#案例

(2)增加修改替换列信息
更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name
column_type [COMMENT col_comment] [FIRST|AFTER column_name]

alter table test1 change column id dept string;# 修改id列名为dept

desc test1;查看表结构信息
desc formatted test1;查看表的详细信息

增加替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type
    [COMMENT col_comment], ...)
    
alter table test1 add columns (name string);#增加列
alter table test1 replace columns (id string,dname string,age int);# 替换列为id,dname,age

4. row format 的使用

(1)指定数据的分隔符

create table test1(id int,name string)
row format delimited fields terminated by ',';#使用逗号分开数据

创建数据stu.txt
1001,lisi
1002,wangwu
1003,zhangsan
上传到hdfs的test1下。
查看表，数据自动分割。

三、DML数据操作之数据导入

1. load数据导入

(1)向表中加载数据
语法

load data [local] inpath '数据的 path' [overwrite] into table
student [partition (partcol1=val1,…)]; #语法

1）load data:表示加载数据
2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
3）inpath:表示加载数据的路径
4）overwrite:表示覆盖表中已有数据，否则表示追加
5）into table:表示加载到哪张表
6）student:表示具体的表
7）partition:表示上传到指定分区

案例

#创建student表，以逗号为分隔符
create table student(id int,name string)
row format delimited fields terminated by ',';
#加载本地数据到student表中
load data local inpath '/opt/moudle/hive/student.txt' into table student;
#加载HDFS上的数据到student表中
load data inpath '/user/student.txt' into table student;
#覆盖表中数据
load data inpath '/user/student.txt' overwrite into table student;

(2)load和-put的区别（尽量不适用-put）
使用load加载数据后，select count(*) from student;
命令走MR

使用-put上传数据，select count(*) from student1;
命令不走MR

2. insert插入数据

(1)基本插入数据

 insert into test values(1001,'zhangsan'),(1002,'lisi');

(2)根据单表插入数据
将student1中的数据插入到test表中，并覆盖

insert overwrite table test
select id,name from student1;

(3)查询一张表插入多张表

from student
insert overwrite table student1
select id,name
insert into table test
select id,name;

3. 创建表并插入数据AS SELECT

创建test1表，并将student中的数据插入到test1表中

create table test1
as select id,name from student;

4. 创建表并指定hdfs上的位置

create external table test2(id int,name string)
row format delimited fields terminated by ','
location '/user/hive/warehouse/testbase.db';

四、DML数据操作之数据导出

1. Insert导出

(1)将查询到的结果导出到本地
自动创建data/export/student目录，并将数据导入到文件夹中

insert overwrite local directory
'/opt/moudle/hive/data/export/student'
select *from student;

(2)将查询到的结果格式化导出到本地

insert overwrite local directory
'/opt/moudle/hive/data/export/student1'
row format delimited fields terminated by ','
select *from student;

(3)将查询结果导出到HDFS上
没事别用overwrite，用into好点

insert overwrite directory '/user/student2'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 select * from student;

2. hadoop命令将数据导出到本地

dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;

3. Hive shell 命令导出

bin/hive -e 'select * from default.student;' >
/opt/module/hive/data/export/student4.txt;

4. Export导出数据到HDFS上

export table default.student
to '/user/hive/warehouse/export/student';

5. 清空表中数据Truncate
   Truncate 只能删除管理表，不能删除外部表中数据

 truncate table student;