hive笔记（三）：DDL数据定义/DML数据操作-数据库、表、数据导入导出

目录

DDL数据定义

创建数据库

查询数据库

修改数据库

删除数据库

创建表

内部表

外部表

内部表和外部表的应用场景

内部表和外部表的相互转换

修改表

删除表

DML数据操作

数据导入

向表中加载数据

插入数据

建表并指定加载数据路径

import加载数据

数据导出

insert导出

Hadoop导出

hive shell导出

export导出（HDFS）

清除数据

---

DDL数据定义

创建数据库

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

（1）创建数据库test（普通）

create database test;

（2）创建数据库test（标准写法）

create database if not exists test;

（3）创建数据库test（指定数据在HDFS的存储位置）

create database test location '/user/hive/warehouse/test.db';

查询数据库

（1）显示数据库

show databases;

过滤显示查询的数据库

show databases like 'test';

（2）查看数据库详情

1）显示数据库详情

desc database test;

2)显示数据库详细信息

desc database extended test;

 （3）切换数据库

use spark;
use test;

修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值来描述这个数据库的属性信息。

alter database test
set dbproperties('createtime'='20220824');

查看

删除数据库

（1）删除空数据库

drop database if exists test2;

 （2）删除不为空数据库（强制删除）

drop database if exists test cascade;

创建表

（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）CREATE TABLE：创建一个指定名字的表。用IF NOT EXISTS选项处理重复表名异常；

2）EXTERNAL：关键字可以创建外部表，建表同时可以指定实际数据的路径（LOCATION）；

注：删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据；

3）COMMENT：为表和列添加注释；

4）PARTITIONED BY：创建分区表；

5）CLUSTERED BY：创建分桶表；

6）SORTED BY：对桶中的一个或多个列另外排序；

7）ROW FORMAT：用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED将会使用自带的SerDe。建表时用户需为表指定列，用户指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

注：SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。

8）STORED AS：指定存储文件类型；

注：常用的文件类型：sequencefile（二进制序列文件）、textfile（文本）、rcfile（列式存储文件）。

9）LOCATION：指定文件在HDFS上的存储路劲；

10）AS：后面跟查询语句，根据查询结果创建表；

11）LIKE：允许用户复制现有的表结构，不能复制数据。

内部表

默认创建的表都是内部表，也被称为管理表。hive控制内部表的生命周期，默认情况下内部表的数据存储在配置项定义的目录（/user/hive/warehouse）的子目录下。

配置项：hive.metastore.warehouse.dir

注：删除内部表时数据也会被删除。

（1）创建表

1）创建普通表

create table if not exists stu (
id int,
name string,
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/stu';

2）根据存在的表结构创建表

create table if not exists stu2 like stu;

3）根据查询结果创建表

create table if not exists stu3 as select id,name from stu;

外部表

hive没有全部拥有外部表的数据，拥有的是外部表的元数据。删除外部表不会删除数据，仅仅是删除元数据。

（1）创建外部表

create external table if not exists test2(
id int,
name string,
)
row format delimited fields terminated by '\t';

内部表和外部表的应用场景

例子：

每天收集数据，将收集到的网站日志数据定期存储在HDFS文本文件。在外部表的基础上做统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

内部表和外部表的相互转换

 （1）查看表类型

desc formatted stu;

 （2）修改内部表为外部表（内部表=>外部表）

alter table stu set tblproperties('EXTERNAL'='TRUE');

（3）修改外部表为内部表（外部表=>内部表）

alter table stu set tblproperties('EXTERNAL'='FALSE');

修改表

（1）重命名

alter table stu rename to stu2;

（2）增加/修改/替换列信息

1）更新列

语法：

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

2）增加和替换列

语法：

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
col_comment], ...)

注：

ADD是代表新增一字段，位置在所有列后面且partition列前)；

REPLACE表示替换表中所有字段。

例子：

#添加列
alter table stu add columns(age int);

#更新列
alter table stu change column firstname lastname string;

#替换列
alter table stu replace columns(age int,dname string);

删除表

drop table stu2;

DML数据操作

数据导入

向表中加载数据

语法：

load data [local] inpath 'data_path' [overwrite] into table 
student [partition (partcol1=val1,…)];

说明：

（1）load data：从本地加载数据；

（2）inpath：加载数据的路径；

（3）overwrite：表示覆盖表中的数据，不然就是追加；

（4）into table：加载到哪张表上；

（5）partition：上传到指定分区；

例子：

本地

load data local inpath 'datas/stu.txt' into table test.stu;

HDFS

dfs -put data/stu.txt /user/hive/warehouse/test.db;

load data inpath '/user/hive/warehouse/test.db/stu.txt' into table test.stu;

插入数据

（1）创建表

create table student(
id int,
namr string)
row format delimited fields terminated by '\t';

（2）插入数据（insert）

insert into table student values(1,'zj'),(2,'zjj');

（3）插入数据（查询插入）

单表：

insert overwrite table student select id, name from stu;

多表：

#例子：
from stu
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';

（4）建表并加载数据（查询加载）

create table if not exists student as select id, name from student;

建表并指定加载数据路径

（1）路径为/student（HDFS）

create external table if not exists student3(
id int, 
name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/test.db';

import加载数据

import table student3 from '/user/hive/warehouse/test.db/student';

注：export导出数据。

数据导出

insert导出

（1）查询导出到本地

insert overwrite local directory 'data/student'
select * from student;

（2）查询导出到本地（格式化）

insert overwrite local directory 'data/student2'
row format delimited fields terminated by '\t'
select * from student;

（3）查询导出到HDFS

insert overwrite directory '/data/student'
row format delimited fields terminated by '\t'
select * from student

Hadoop导出

dfs -get /user/hive/warehouse/test.db/student/student.txt
data/student2.txt;

hive shell导出

基本语法：（hive -f/-e执行语句或者脚本 > file）

bin/hive -e 'select * from test.student;' > data/student3.txt;

export导出（HDFS）

export table test.student to '/student';

清除数据

truncate table student;

注：Truncate只能删除内部表数据，不能删除外部表数据。

本文仅仅是学习笔记！！！