Day04-Hive——数据库基础操作

一、hive服务启动与关闭

在hive服务前必须启动hadoop服务（在linux中）

--一键启动
start-all.sh
--启动历史服务
mapred --daemon start historyserver

 启动hive服务

nohup hive --service metastore &

nohup hive --service hiveserver2 &

查看是否启动成功

jps
lsof -i:10000

关闭hive服务

kill -9 10693 10829

二、基本操作

ps：要连接数据库后使用

1、hive数据库增删改

①创建数据库:

location路径默认是:  hdfs://node1:8020/user/hive/warehouse/库名.db           

create database [if not exists] 库名 [location '路径'];

②使用数据库:

use 库名;

③删除数据库:

hive比较特殊,drop不能直接删除有表的库，此时要加cascade

drop database 数据库名 [cascade];

2、hive数据库其他操作

①修改数据库路径:

-- 节点地址为HDFS 集群中的一个节点地址
alter database 库名 set location 'hdfs://节点地址:节点端口号/路径'

②修改数据库属性:

alter database 库名 set dbproperties ('k'='v');

③查看所有的数据库:

show databases;

④查看某库建库语句:

show create database 库名;

⑤查看当前使用的数据库:

select current_database();

⑥查看指定数据库信息:

desc database 库名;

⑦查看指定数据库扩展信息:

desc database extended 库名;

三、Hive内外部表概念

1、创建表

create [external] table [if not exists] 表名(
字段名 字段类型 ,
 字段名 字段类型 ,
 ... )
[partitioned by (分区字段名 分区字段类型)] # 分区表
[clustered by (分桶字段名) [sorted by (字段名 asc|desc)] into 桶个数 buckets]  # 分桶表
[row format delimited fields terminated by '字段分隔符' ...] # 自定义字段分隔符固定格式
[stored as textfile]  # 默认即可
[location 'hdfs://域名:8020/user/hive/warehouse/库名.db/表名'] # 默认即可
; # 注意: 最后一定加分号结尾

关键字顺序是从上到下从左到右,否则报错

hive中default库默认存储路径:  /user/hive/warehouse   
自定义库在HDFS的默认存储路径:    /user/hive/warehouse/库名.db
自定义表在HDFS的默认存储路径:    /user/hive/warehouse/库名.db/表名
业务数据文件在HDFS的默认存储路径:    /user/hive/warehouse/库名.db/表名/数据文件

2、数据类型

基本数据类型（跟sql一样）:  
    整数: int  bigint
    小数: float double  decimal(10,0)
    字符串: string   varchar(长度)  
    日期: date   timestamp

复杂数据类型:  
    集合: array  
    映射: map   
    结构体: struct  

    联合体: union

3、内外部表核心区别（纯概念）

①内部表：又叫管理表或者托管表

数据存储位置：内部表的数据存储在Hive 的数据仓库目录下，默认位置通常是 /user/hive/warehouse。

数据管理：Hive 完全管理内部表的数据。这意味着当你删除一个内部表时，表中的数据也会被删除。（删除时直接删除元数据（metadata）和存储的数据本身）

创建方式：在创建内部表时，如果没有特别指定表的类型，默认创建的就是内部表

②外部表：又叫非管理表或者非托管表

数据存储位置：外部表的数据存储在外部的文件系统位置，用户在创建表时需要指定数据的具体存储路径。

数据管理：Hive 只管理表的元数据，而不管理数据本身。因此，当删除一个外部表时，Hive 只会删除表的元数据，数据文件仍然保留在原来的位置。（仅仅是删除元数据（metadata），不会删除存储的数据本身）

创建方式：在创建外部表时，需要使用 EXTERNAL 关键字并指定数据存储的位置。

③概述

1、数据管理

内部表：由Hive全权管理，删除时会删除数据。

外部表：Hive仅管理元数据，删除时不删除数据。

2、存储位置

内部表：数据存储在Hive默认仓库目录。

外部表：数据存储在指定的HDFS路径或其它存储系统。

3、应用场景

内部表：适合临时数据处理和统计分析。

外部表：适合长期存储和多工具共享数据。

4、创建方式：

内部表：创建时默认为内部表

外部表：创建时需要使用 EXTERNAL 关键字

④查看表格式化信息:

desc formatted 表名; 

内部表类型: MANAGED_TABLE
外部表类型: EXTERNA_TABLE

四、内外部表基本操作

1、创建表

row format delimited fields terminated by '字段分隔符'——用于定义表的行格式和字段分隔符

创建表的时候,如果不指定分隔符,以后表只能识别默认的分隔符,键盘不好打印,展示形式一般为:\0001,SOH,^A,□

如何打出hive默认分隔符:
    方式1: window用notepad++的编辑选项->字符面板  , 双击SOH输入即可
    方式2: linux用vim编辑器输入模式下 ,  先按ctrl+V,再按ctrl+A

①创建内部表

创建表时默认为内部表

 [ ]内的内容可以省略

 create table [if not exists] 内部表名(
字段名 字段类型 , 
字段名 字段类型 , 
... )[row format delimited fields terminated by '字段分隔符'] ;

②创建外部表

创建外部表时要加关键字external

 create external table [if not exists] 外部表名(
字段名 字段类型 , 
字段名 字段类型 ,
 ... )[row format delimited fields terminated by '字段分隔符'] ;

2、复制表

①复制内部表

方式1: like方式复制表结构 （先复制表结构,再把查询结果插入到该表中）

--下面的表1为复制得到的表，表2为有数据被复制的表
create table 表1 like 表2;
insert into 表1 select * from 表2;

方式2: as 方式复制表结构和数据（复制表结构和表数据一步到位）

--下面的表1为复制得到的表，表2为有数据被复制的表
create table 表1 as select * from 表2;

②复制外部表

只能用like方式复制表结构，as方式不可以使用

--下面的表1为复制得到的表，表2为有数据被复制的表
create external table 表1 like 表2;
insert into 表1 select * from 表2;

3、删除表

①删除内部表

由于删除内部表效果，是mysql中表相关的元数据被删除,所以同时存储在hdfs中的业务数据本身也被删除

drop table 内部表名;

可以使用truncate清空内部表数据     

truncate table 内部表名;

②删除外部表

删除外部表效果是mysql中元数据被删除,但是存储在hdfs中的业务数据本身被保留。（外部表对HDFS没有管理权）

drop table 外部表名;

外部表不能使用truncate清空数据本身,因为对外部表对这些数据没有管理权。

4、查看表格式化信息

内部表类型: MANAGED_TABLE

外部表类型: EXTERNAL_TABLE

desc formatted 表名;

另：插入数据用insert into 表名 values（）;如果多次insert写入,hive会报code1错误,但是数据依然正常插入进去了...

五、查看、修改表的常用操作

1、查看所有表

show tables;

2、查看建表语句

show create table 表名;

3、查看表基本信息

desc 表名;

4、查看表格式化信息

desc formatted 表名;            --formatted能够展示详细信息

5、修改表名

 alter table 旧表名 rename to 新表名;

6、修改表路径

location: 建表的时候不写会默认路径/user/hive/warehouse/库名.db/表名,当然建表的时候也可以直接指定路径

 alter table 表名 set location 'hdfs中存储路径';             --建议使用默认路径

7、修改表属性:

alter table 表名 set tblproperties ('属性名'='属性值');     --经常用于内外部表切换

内外部表类型切换:（改了以后可以用desc formatted 表名;来查看）  外部表属性: 'EXTERNAL'='TRUE'   内部表属性: 'EXTERNAL'='FALSE'

8、字段的添加

alter table 表名 add columns (字段名 字段类型);

9、字段的替换:

alter table 表名 replace columns (字段名 字段类型 , ...);

10、字段名和字段类型同时修改:

字符串类型不能直接改数值类型

alter table 表名 change 旧字段名 新字段名 新字段类型;

六、快速映射表与数据的导入导出

1、快速映射表

创建表的时候指定分隔符:

create [external] table 表名(
字段名 字段类型
)row format delimited fields terminated by '分隔符号';

-- 加载数据
-- 注意: 如果从hdfs中加载文件,本质就是移动文件到对应表路径下

加载数据:

load data [local] inpath '结构化数据文件' into table 表名;

2、数据的导入

①直接上传文件到表目录

第一步：根据数据创建表

第二步：主机地址:9870访问页面把文件上传

如：要把caijiuduolian.txt上传到db数据库中的text表中

到hdfs中的/user/hive/warehouse/db/text路径下上传文件即可

②linux本地put上传

第一步：根据数据创建表

第二步：linux使用hdfs命令上传文件 

如：要把caijiuduolian.txt上传到db数据库中的text表中

hdfs dfs -put caijiuduolian.txt /user/hive/warehouse/db/text

③load上传Linux文件

第一步：根据数据创建表

第二步：把数据文件传到linux中

第三步：load命令上传文件

如：要把caijiuduolian.txt上传到db数据库中的text表中

根据数据创建表后

先把caijiuduolian.txt传到linux中——如：传到 /home中

然后load data local inpath '/home/caijiuduolian.txt' overwrite into table text;

④load移动HDFS文件

第一步：根据数据创建表

第二步：在hdfs中别的路径上传数据文件

第三步：使用load移动数据文件到hdfs对应的表存储路径中

如：要把caijiuduolian.txt上传到db数据库中的text表中

根据数据创建表

先把caijiuduolian.txt上传到/src中

然后

load data inpath '/src/caijiuduolian.txt' into table text;

七、分区表、分桶表

--未完待续

面试题：

1、内外部表核心区别

2、分区表与分桶表的区别