Hive之环境准备 ,启动 ,DDL语言 ,建表语句(内部表 / 外部表)(2)_hiveserver2建表语句

一  启动 hive 前环境准备

1 启动mysql (两种都可,选其一)并查看启动情况

[root@linux03 ~]# service mysqld  start          // 手动启动mysql服务

[root@linux03 ~]# systemctl start mysqld       // 手动启动mysql服务

[root@doit03 ~]# netstat -nltp | grep 3306      // 查看 mysql 启动情况
tcp6       0      0 :::3306                 :::*                    LISTEN      1342/mysqld           // 启动成功

2  启动 hdfs 和 yarn

[root@linux03 ~]# start-all.sh

二  启动 hive ,进入交互窗口的两种方式

1  直接启动 hive ,连接 mysql

查询结果没有框

[root@linux03 ~]# hive --service metastore &

[root@linux03 ~]# hive

hive> show databases;           // 查询数据库 ,结果如下

OK
db_user
default
linux03
Time taken: 0.654 seconds, Fetched: 3 row(s)

2  启动 hiveserver2 服务 ,然后通过 beeline 访问JDBC ,连接 mysql

查询结果有框

[root@linux03 ~]# hive --service metastore &

[root@linux03 ~]# hiveserver2 &

[root@linux03 ~]# beeline -u jdbc:hive2://localhost:10000 -n root       一步直连式

[root@linux03 ~]# beeline     分步连接

.........
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Beeline version 3.1.2 by Apache Hive
beeline> !connect jdbc:hive2://linux03:10000                         // 连接 hiveserver2
Connecting to jdbc:hive2://linux03:10000
Enter username for jdbc:hive2://linux03:10000: root                  // 连接数据库账号 , 然后 enter
Enter password for jdbc:hive2://linux03:10000:                          // 直接 enter 确定 ,不用输密码
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://linux03:10000> show databases;             // 查询数据库 ,结果如下

+----------------+
| database_name  |
+----------------+
| db_xiaoqq      |
| default            |
| doit03             |
+----------------+
3 rows selected (0.255 seconds)

三   DDL语言

1  创建数据库

0: jdbc:hive2://linux03:10000> create database db_hive;

2  查询数据库

2.1  显示数据库

0: jdbc:hive2://linux03:10000> show databases;                          // 显示所有数据库

0: jdbc:hive2://linux03:10000> show databases like 'db_hive';      // 过滤显示查询的数据库,有相关字眼的数据库都会找出来

2.2  查看数据库详情

0: jdbc:hive2://linux03:10000> desc database db_hive;                                // 显示数据库信息

0: jdbc:hive2://linux03:10000> desc database extended db_hive;            // 显示数据库详细信息

0: jdbc:hive2://linux03:10000> use db_hive                                  // 切换数据库

3  修改数据库

使用 alter database 命令为某个数据库的 dbproperties 设置键-值对属性值 ,来描述这个数据库的属性信息 .数据库的其他元数据

信息都是不可更改的 ,包括数据库名和数据库所在的目录位置 .

0: jdbc:hive2://linux03:10000> alter database db_hive set dbproperties('createtime'='20180830');      // 修改配置信息

0: jdbc:hive2://linux03:10000> desc database extended db_hive;               // 查看修改结果

4  删除数据库

0: jdbc:hive2://linux03:10000> drop database db_hive;                      // 删除数据库

0: jdbc:hive2://linux03:10000> drop database if exists db_hive;         // 判断如果数据库存在就删除数据库

0: jdbc:hive2://linux03:10000> drop database db_hive cascade ;       // 如果数据库不为空就强制删除数据库

5  建表语句(在本地建立一个静态文件,从本地加载到hive上)

5.1  先在本地目录下建立文件夹和文件 ,然后在文件中将结构化语句插入进去  

[root@linux03 ~]# cd root/                                 // 进入root 目录下

[root@linux03 root]# mkdir  -p    hive/log          // 建立多层文件夹

[root@linux03 log]# vi datalog                          // 将结构化数据插入进去 ,保存

id ,  ctime    ,      event  ,   sessionid

1,2020-09-01,showpage,sesion1001
2,2020-09-01,showpage,sesion1002
3,2020-09-01,showpage,sesion1003
4,2020-09-01,showpage,sesion1004
5,2020-09-01,showpage,sesion1005
6,2020-09-01,showpage,sesion1006

5.2  在 hive 交互窗口 ,创建 一个表 ,表的结构与本地文件的结构化语句相对应 

0: jdbc:hive2://linux03:10000> create table if not exists tb_log(
. . . . . . . . . . . . . . > id int ,
. . . . . . . . . . . . . . > ctime string ,
. . . . . . . . . . . . . . > event string ,
. . . . . . . . . . . . . . > sessionid string
. . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . > row format delimited fields terminated by "," ;

// 将本地的结构化语句直接加载到 hive 创建的对应结构的表里面

0: jdbc:hive2://linux03:10000> load data local inpath "/root/log/" into table tb_log ;     

// 查询插入结构

0: jdbc:hive2://linux03:10000> select * from tb_log;

// 结果如下 

+------------+---------------+---------------+-------------------+
| tb_log.id  | tb_log.ctime  | tb_log.event  | tb_log.sessionid  |
+------------+---------------+---------------+-------------------+
| 1          | 2020-09-01    | showpage      | sesion1001        |
| 2          | 2020-09-01    | showpage      | sesion1002        |
| 3          | 2020-09-01    | showpage      | sesion1003        |
| 4          | 2020-09-01    | showpage      | sesion1004        |
| 5          | 2020-09-01    | showpage      | sesion1005        |
| 6          | 2020-09-01    | showpage      | sesion1006        |
+------------+---------------+---------------+-------------------+

6  建表语句(在本地建立一个静态文件, put 到 hdfs 上 ,从hdfs 上将静态文件加载到 hive上)

6.1  先在本地目录下建立文件夹和文件 ,然后在文件中将结构化语句插入进去  ,然后将本地文件 put 到 hdfs 上

[root@linux03 ~]# cd root/                                 // 进入root 目录下

[root@linux03 root]# mkdir  -p    hive/log          // 建立多层文件夹

[root@linux03 log]# vi log.txt                          // 将结构化数据插入进去 ,保存

id ,  ctime    ,      event  ,   sessionid

1,2020-09-01,showpage,sesion1001
2,2020-09-01,showpage,sesion1002
3,2020-09-01,showpage,sesion1003
4,2020-09-01,showpage,sesion1004
5,2020-09-01,showpage,sesion1005
6,2020-09-01,showpage,sesion1006

[root@linux03 log]# hdfs dfs -mkdir -p /hive/log              // 在 hdfs 上建立一个多层文件夹

[root@linux03 log]# hdfs dfs -put log.txt /hive/learning      // 将本地的静态文件上传到 hdfs 上

[root@linux03 log]#  hdfs dfs -ls /hive/learning                  // 查询文件上传情况

-rw-r--r--   3 root supergroup        198 2020-09-03 07:53 /hive/learning/log.txt

6.2  在 hive 交互窗口 ,创建 一个表 ,表的结构与本地文件的结构化语句相对应 

0: jdbc:hive2://linux03:10000> create table if not exists tb_log1(
. . . . . . . . . . . . . . > id int ,
. . . . . . . . . . . . . . > ctime string ,
. . . . . . . . . . . . . . > event string ,
. . . . . . . . . . . . . . > sessionid string
. . . . . . . . . . . . . . > )
. . . . . . . . . . . . . . > row format delimited fields terminated by "," ;

// 将 hdfs 上的结构化语句直接加载到 hive 创建的对应结构的表里面

0: jdbc:hive2://linux03:10000> load data inpath "/hive/log/" into table tb_log1 ;     

// 查询插入结构

0: jdbc:hive2://linux03:10000> select * from tb_log1;

// 结果如下 

+------------+---------------+---------------+-------------------+
| tb_log1.id  | tb_log1.ctime  | tb_log1.event  | tb_log1.sessionid  |
+------------+---------------+---------------+-------------------+
| 1          | 2020-09-01    | showpage      | sesion1001        |
| 2          | 2020-09-01    | showpage      | sesion1002        |
| 3          | 2020-09-01    | showpage      | sesion1003        |
| 4          | 2020-09-01    | showpage      | sesion1004        |
| 5          | 2020-09-01    | showpage      | sesion1005        |
| 6          | 2020-09-01    | showpage      | sesion1006        |
+------------+---------------+---------------+-------------------+

四  管理表(内部表)

默认创建的表都是所谓的管理表 ,也被称为内部表 .Hive 会控制着这些表数据的生命周期 . Hive 默认情况下会将这些表的数据

存储在由配置 hive.metastore.warehouse.dir( 如 /user/hive/warehouse) 所定义的目录的子目录下 .当我们删除一个管理表时 ,

Hive 也会删除这个表中数据 .管理表不适合和其他工具共享数据 .

1 先将结构化数据编辑存储在本地 /root/hive/log/log.txt ,然后上传到 hdfs 上 /hive/log.txt
[root@linux03 log]# vi log.txt
-rw-r--r--. 1 root root 198 Sep  3 16:52 log.txt
 
[root@linux03 log]# hdfs dfs -put log.txt /hive/  ==>将本地的log.txt文件上传到 hdfs 上
[root@linux03 log]# hdfs dfs -ls /hive           ==> 查看刚刚上传到 hdfs 上的 log.txt 文件
-rw-r--r--   3 root supergroup        198 2020-09-05 19:57 /hive/log.tx
 
 
2 建表1-----------------------------内部表-----在hive 交互窗口上创建-------
create table tb_inner_log(
id int,
ctime string,
event string,
sessionid string)
row format delimited fields terminated by ",";
load data inpath "/hive/log.txt" into table tb_inner_log;    // 将hdfs上的文件导入到表中
 
3 hive 交互窗口查询表插入情况 : select * from tb_inner_log ; 
+------------------+---------------------+---------------------+-------------------------+
| tb_inner_log.id  | tb_inner_log.ctime  | tb_inner_log.event  | tb_inner_log.sessionid  |
+------------------+---------------------+---------------------+-------------------------+
| 1                | 2020-09-01          | showpage            | sesion1001              |
| 2                | 2020-09-01          | showpage            | sesion1002              |
| 3                | 2020-09-01          | showpage            | sesion1003              |
| 4                | 2020-09-01          | showpage            | sesion1004              |
| 5                | 2020-09-01          | showpage            | sesion1005              |
| 6                | 2020-09-01          | showpage            | sesion1006              |
+------------------+---------------------+---------------------+-------------------------+
4  到 hdfs shell 客户端上查看文件 ,发现 hdfs 上原先存放着的 log.txt 文件已经查询不到了 ,已经被转移走了,存储到了对应表的目录下,所以删除表的时候,该文件也会被删除
[root@linux03 log]# hdfs dfs -ls /hive 
[root@linux03 log]#    
 
 
5  将本地的 /root/hive/log/ 目录下的静态文件"log.txt"上传到 hdfs shell端并查看 
[root@linux03 log]# hdfs dfs -put log.txt /hive/
[root@linux03 log]# hdfs dfs -ls /hive
-rw-r--r--   3 root supergroup        198 2020-09-05 20:09 /hive/log.txt 
 
 
6  建表2--------------------------内部表-----在hive 交互窗口上创建-------
drop table tb_inner_log2;--------------内部表,没有指定 external
create table tb_inner_log2(
id int,
ctime string,
event string,
sessionid string)
row format delimited fields terminated by "," location "/hive/";  //指定文件导入的路径 ,在hdfs shell端,也是不需要再另外加载的
 
7  上hdfs shell客户端上查看"log.txt" ,结果显示 ,将文件数据导入到表(内部表)中后,文件没有被移除
[root@linux03 log]# hdfs dfs -ls /hive
-rw-r--r--   3 root supergroup        198 2020-09-05 20:09 /hive/log.txt 
  
8  在 hive 交互界面将表 tb_inner_log2 删除
0: jdbc:hive2://linux03:10000> drop table tb_inner_log2;
 
9 再去查看文件时,发现文件和其所在的目录一起被删除掉了
[root@linux03 log]# hdfs dfs -ls /hive
ls: `/hive': No such file or directory

内部表总结 :
不管内部表(管理表)是以哪种方式创建的 ,只要是在创建的时候没有指定 external ,就是内部表 ,在对该表进行删除的时候,该表对应的静态文件及其存储该文件的文件夹也会被一起删除掉

五  外部表

因为表时外部的 ,所有 Hive 并非认为其完全拥有这份数据 .删除该表并不会删除掉这份数据 ,不过描述表的元数据信息会被删除.

hive 是一个处理结构化数据的工具(可以避免编写 MR 程序处理结果) ,静态数据文件在 hdfs 上 ,有些数据是共享数据,可能有多张

表要使用这个数据 ,这些共享数据是不能使用普通表存放的 ,因为普通表在加载数据的时候会将数据转移到对应的自己的表目录下

,所以需要将共享数据存放在外部表

1 先将结构化数据编辑存储在本地 /root/hive/log/log.txt ,然后上传到 hdfs 上 /hive/log.txt
[root@linux03 log]# vi log.txt
-rw-r--r--. 1 root root 198 Sep  3 16:52 log.txt
[root@linux03 log]# hdfs dfs -put log.txt /hive/  ==>将本地的log.txt文件上传到 hdfs 上
[root@linux03 log]# hdfs dfs -ls /hive            ==> 查看刚刚上传到 hdfs 上的 log.txt 文件
-rw-r--r--   3 root supergroup        198 2020-09-05 19:57 /hive/log.tx
 
 
2  创建外部表------>指定数据的位置   external:外部的
create external table tb_external(   --------------------指定了 external-------------
id int,
ctime string,
event string,
sessionid string)
row format delimited fields terminated by "," location "/hive/";  //指定静态文件的位置并自动加载 ,不需要另外加载数据
0: jdbc:hive2://doit03:10000> select * from tb_external;
+---------------------+------------------------+------------------------+----------------------------+
| tb_external.id  | tb_external.ctime  | tb_external.event  | tb_external.sessionid  |
+---------------------+------------------------+------------------------+----------------------------+
| 1                   | 2020-09-01             | showpage               | sesion1001                 |
| 2                   | 2020-09-01             | showpage               | sesion1002                 |
| 3                   | 2020-09-01             | showpage               | sesion1003                 |
| 4                   | 2020-09-01             | showpage               | sesion1004                 |
| 5                   | 2020-09-01             | showpage               | sesion1005                 |
| 6                   | 2020-09-01             | showpage               | sesion1006                 |
+---------------------+------------------------+------------------------+----------------------------+
 
3  将数据插入到 hive 端的 tb_external 表上后 ,文件仍然还在 hdfs 端的路径下 ,没有被转移走(没有转移到表的目录下)
[root@linux03 log]# hdfs dfs -ls /hive            
Found 1 items
-rw-r--r--   3 root supergroup        198 2020-09-05 20:09 /hive/log.txt
 
4  将外部表删除
0: jdbc:hive2://doit03:10000> drop table tb_external;
 
5  然后在去 hdfs shell 端查看文件还在不在,会不会被删除 ,结果显示 ,外部表对应的静态文件没有被删除
[root@linux03 log]# hdfs dfs -ls /hive
-rw-r--r--   3 root supergroup        198 2020-09-05 23:17 /hive/log.txt

外部表总结 : 
1 创建外部表时 ,需要指定 external ,并且导入数据时是指定(location)了文件加载路径的
2 该外部表被删除后,其对应的静态文件是不会被删除的

六  内部表和外部表总结

1  管理表和外部表的使用场景 :

每天将收集到的网站日志定期流入HDFS 文本文件 .在外部表(原始日志表) 的基础上做大量的统计分析 ,用到的中间表 ,结果表使

用内部表存储 ,数据通过 select+insert 进入内部表 .

内部表 --------------业务报表

外部表---------------日志数据 ,原始数据 ,主题表 ,事件表 ,流量表

2. 所有的表都可以使用 location 指定数据的位置
3  删除外部表 ,指定的数据不会被删除(存储文件所在的文件夹名字与表名不一致,删除外部表其对应的文件也不会被删除,所有两者关联性不强--文件与表的关联性)
4  删除内部表 ,会删除指定目录的数据(指定目录下的数据/文件与表的关联性很前,文件存储位置所在的文件夹名字与表名一致 ,将表删除,对应的文件及其文件夹也会被删除)
5  假如有共享数据,要创建 a 表和 b 表两个表使用相同的数据 ,在创建表的时候应该将这两个表选择为外部表,这样但对一个表进行删除操作的时候 ,不会影响到另外一个表.并且共享数据也不会被删除.

七  管理表与外部表的相互转换

1  查询表的类型

0: jdbc:hive2://doit03:10000> desc formatted tb_inner_log;

Table Type:               MANAGED_TABLE  ---------管理表

0: jdbc:hive2://doit03:10000> desc formatted tb_external;

Table Type:               EXTERNAL_TABLE  ---------管理表

2   修改内部表 tb_inner_log 为外部表 并查看

0: jdbc:hive2://doit03:10000> alter table tb_inner_log set tblproperties('EXTERNAL'='TRUE');     // 修改成外部表

0: jdbc:hive2://doit03:10000> desc formatted tb_inner_log ;                // 查看修改情况

Table Type:           EXTERNAL_TABLE

3  修改外部表 tb_external 为内部表 并查看

0: jdbc:hive2://doit03:10000> alter table tb_external set tblproperties('EXTERNAL'='FALSE');   // 修改成内部表

0: jdbc:hive2://doit03:10000> desc formatted tb_external ;                // 查看修改情况

Table Type:           MANAGED_TAB