Hive(8) Hive的DDL操作（数据库、表）
Hive的DDL操作--数据库
下列的所有操作默认使用beeline方式。
创建数据库
create database db1;
# 或者
create database if not exists db1;
显示所有数据库
0: jdbc:hive2://node03:10000> show databases;
+----------------+--+
| database_name |
+----------------+--+
| db1 |
| default |
| myhive |
+----------------+--+
查询数据库
0: jdbc:hive2://node03:10000> show databases like 'db*';
+----------------+--+
| database_name |
+----------------+--+
| db1 |
+----------------+--+
1 row selected (0.064 seconds)
查看数据库详情
0: jdbc:hive2://node03:10000> desc db1;
Error: Error while compiling statement: FAILED: SemanticException [Error 10001]: Table not found db1 (state=42S02,code=10001)
0: jdbc:hive2://node03:10000> desc database db1;
+----------+----------+------------------------------------------------+-------------+-------------+-------------+--+
| db_name | comment | location | owner_name | owner_type | parameters |
+----------+----------+------------------------------------------------+-------------+-------------+-------------+--+
| db1 | | hdfs://node01:8020/user/hive/warehouse/db1.db | hadoop | USER | |
+----------+----------+------------------------------------------------+-------------+-------------+-------------+--+
1 row selected (0.048 seconds)
显示数据库更详细信息
desc database extended db1;
切换当前数据库
use db_hive;
删除数据库
#删除为空的数据库
drop database db_hive;
#如果删除的数据库不存在，最好采用if exists 判断数据库是否存在
drop database if exists db_hive;
#如果数据库中有表存在，这里需要使用cascade强制删除数据库
drop database if exists db_hive cascade;
Hive的DDL操作--表
使用帮助的官网地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
建表语法介绍
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] row format delimited fields terminated by “分隔符”
[STORED AS file_format]
[LOCATION hdfs_path]
字段解释说明
CREATE TABLE创建一个指定名字的表EXTERNAL创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），指定表的数据保存在哪里。COMMENT为表和列添加注释PARTITIONED BY创建分区表CLUSTERED BY创建分桶表SORTED BY按照字段排序（一般不常用）ROW FORMAT指定每一行中字段的分隔符，hive默认使用'\001'非打印字符作为分隔符- r
ow format delimited fields terminated by ‘\t’
- r
STORED AS指定存储文件类型- 常用的存储文件类型：
SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、ORCFILE（列式存储格式文件） - 如果文件数据是纯文本，可以使用
STORED AS TEXTFILE。如果数据需要压缩，使用STORED AS SEQUENCEFILE
- 常用的存储文件类型：
LOCATION指定表在HDFS上的存储位置。
示例：创建内部表并指定字段之间的分隔符，指定文件的存储格式。
use db1;
create table if not exists t1(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile;
创建内部表
第一种方法
使用标准的建表语句直接建表
use myhive;
create table stu(id int, name string);
#可以通过insert into向hive表当中插入数据，但是不建议工作当中这么做；因为每个insert语句转换成mr后会生成一个文件
insert into stu(id,name) values(1,"zhangsan");
0: jdbc:hive2://node03:10000> select * from stu;
+---------+-----------+--+
| stu.id | stu.name |
+---------+-----------+--+
| 1 | zhangsan |
+---------+-----------+--+
1 row selected (0.224 seconds)
第二种方法
查询建表法：通过AS 查询语句完成建表，将子查询的结果存入新表里
create table if not exists myhive.stu1 as select id, name from stu;
#将select id, name from stu的结果存入到myhive.stu1表了
#表中有数据
0: jdbc:hive2://node03:10000> select * from stu2;
+----------+------------+--+
| stu2.id | stu2.name |
+----------+------------+--+
| 1 | zhangsan |
+----------+------------+--+
1 row selected (0.117 seconds)
第三种方法
like建表法：根据存在的表的结构来创建表
create table if not exists myhive.stu2 like stu;
#表中没有数据
0: jdbc:hive2://node03:10000> select * fro m stu3;
+----------+------------+--+
| stu3.id | stu3.name |
+----------+------------+--+
+----------+------------+--+
No rows selected (0.113 seconds)
查看表的结构
0: jdbc:hive2://node03:10000> desc stu;
+-----------+------------+----------+--+
| col_name | data_type | comment |
+-----------+------------+----------+--+
| id | int | |
| name | string | |
+-----------+------------+----------+--+
2 rows selected (0.113 seconds)
查询表的详情
0: jdbc:hive2://node03:10000> desc formatted stu;
+-------------------------------+----------------------------------------------------+
| col_name | data_type |
+-------------------------------+----------------------------------------------------+
| # col_name | data_type |
| | NULL |
| id | int |
| name | string |
| | NULL |
| # Detailed Table Information | NULL |
| Database: | myhive |
| Owner: | hadoop |
| CreateTime: | Sat Feb 22 14:05:26 CST 2020 |
| LastAccessTime: | UNKNOWN |
| Protect Mode: | None |
| Retention: | 0 |
| Location: | hdfs://node01:8020/user/hive/warehouse/myhive.db/stu
| Table Type: | MANAGED_TABLE |
| Table Parameters: | NULL |
| | COLUMN_STATS_ACCURATE |
| | numFiles |
| | numRows |
| | rawDataSize |
| | totalSize |
| | transient_lastDdlTime |
| | NULL |
| # Storage Information | NULL |
| SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutput
| Compressed: | No |
| Num Buckets: | -1 |
| Bucket Columns: | [] |
| Sort Columns: | [] |
| Storage Desc Params: | NULL |
| | serialization.format |
+-------------------------------+----------------------------------------------------+
#我们可以看到一行：Table Type: | MANAGED_TABLE ，表明该表是内部表，内部表又叫管理表。
创建外部表
外部表：
- 外部表因为是指定其他的
hdfs路径的数据加载到表当中来 - 所以
hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉。 - 为什么使用？因为，有时候这份数据不仅仅你的团队在用，有可能其它团队也要用，因此为了防止数据被删除，要使用外部表。
- 一般情况下：
ods层是贴源层，建的表一般使用外部表，而dw层，表是属于自己的，可以使用内部表。
创建外部表：
- 创建外部表的时候需要加上external 关键字
location字段可以指定，也可以不指定- 指定就是数据存放的具体目录
- 不指定就是使用默认目录 /user/hive/warehouse
create external table t_teacher (t_id string, t_name string)
row format delimited fields terminated by "\t";
0: jdbc:hive2://node03:10000> desc formatted t_teacher;
+-------------------------------+----------------------------------------------------+
| col_name | data_type |
+-------------------------------+----------------------------------------------------+
| # col_name | data_type |
| | NULL |
| t_id | string |
| t_name | string |
| | NULL |
| # Detailed Table Information | NULL |
| Database: | db1 |
| Owner: | hadoop |
| CreateTime: | Sat Feb 22 17:56:08 CST 2020 |
| LastAccessTime: | UNKNOWN |
| Protect Mode: | None |
| Retention: | 0 |
| Location: | hdfs://node01:8020/user/hive/warehouse/db1.db/t_teac
| Table Type: | EXTERNAL_TABLE |
| Table Parameters: | NULL |
| | EXTERNAL |
| | transient_lastDdlTime |
| | NULL |
| # Storage Information | NULL |
| SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutput
| Compressed: | No |
| Num Buckets: | -1 |
| Bucket Columns: | [] |
| Sort Columns: | [] |
| Storage Desc Params: | NULL |
| | field.delim |
| | serialization.format |
+-------------------------------+----------------------------------------------------+
29 rows selected (0.084 seconds)
#Table Type: | EXTERNAL_TABLE
外部表加载数据
我们前面已经看到过通过insert的方式向内部表当中插入数据，外部表也可以通过insert的方式进行插入数据，只不过insert的方式，我们一般都不推荐
实际工作当中我们都是使用load的方式来加载数据到内部表或者外部表
load数据可以从本地文件系统加载或者也可以从hdfs上面的数据进行加载
第一种方式：从本地文件系统加载到外部表中去
从本地文件系统加载数据到teacher表当中去
- 将我们附件当汇总的数据资料都上传到
node03服务器的/kkb/install/hivedatas路径下面去
mkdir -p /kkb/install/hivedatas
- 将数据都上传到
/kkb/install/hivedatas路径下 - 然后在
hive客户端下执行以下操作
load data local inpath '/kkb/install/hivedatas/teacher.csv' into table myhive.teacher;
第二种方式：从hdfs文件系统加载到外部表中去
从hdfs上面加载文件到teacher表里面去。
将teacher.csv文件上传到hdfs的/kkb/hdfsload/hivedatas路径下，注意，这种方式是将hdfs文件的数据剪切到表中，源文件会被删除。
cd /kkb/install/hivedatas
hdfs dfs -mkdir -p /kkb/hdfsload/hivedatas
hdfs dfs -put teacher.csv /kkb/hdfsload/hivedatas
# 在hive的客户端当中执行
load data inpath '/kkb/hdfsload/hivedatas' overwrite into table myhive.teacher;
#加上overrite关键字，会把原数据覆盖掉
内部表与外部表的互相转换
- 1、内部表转换为外部表
#将stu内部表改为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');
- 2、外部表转换为内部表
#把teacher外部表改为内部表
alter table teacher set tblproperties('EXTERNAL'='FALSE');
内部表与外部表的区别
-
1、建表语法的区别
- 外部表在创建的时候需要加上external关键字
-
2、删除表之后的区别
- 内部表删除后，表的元数据和真实数据都被删除了
- 外部表删除后，仅仅只是把该表的元数据删除了，真实数据还在，后期还是可以恢复出来
内部表与外部表的使用时机
-
内部表由于删除表的时候会同步删除
HDFS的数据文件，所以确定如果一个表仅仅是你独占使用，其他人不使用的时候就可以创建内部表，如果一个表的文件数据，其他人也要使用，那么就创建外部表 -
一般外部表都是用在数据仓库的
ODS层 -
内部表都是用在数据仓库的
DW层
