Hive学习笔记（三） MySQL安装

元数据服务（metastore）的作用

客户端连接metastore服务，metastore服务再去连接数据库，来存储元数据。
有了metastore服务，就可以从客户端连接，且客户端不需要知道数据库相关信息，只需要连接metastore服务即可。

Hive安装的三种方式

内嵌模式
使用内嵌的derby数据库来存储袁术，数据库和metastore服务都内嵌在hive进程
缺点：从不同路径启动hive，每个hive都会拥有一套自己的元数据，无法共享。
本地模式
采用外部数据库（第三方数据库）存储元数据，如：mysql，oracle，sqlserver等。
缺点：每个进程都启动metastore服务
3， 远程模式
需要单独启动metastore服务,每个客户端都对一个唯一或分布式的metastore服务。

更改hive的metastore存储为mysql

完成mysql安装
在mysql中创建新的数据库hive_metadata
配置hive-site.xml

数据库命令

建库语句

CREATE DATABASE [IF NOT EXISTS] database_name
[LOCATION hdfs_path]

标准建库语句：

CREATE DATABASE IF NOT EXISTS hive_mysk2;

建库+指定存储位置：

CREATE DATABASE IF NOT EXISTS hive_mysk3
COMMENT '这是一个测试数据库'
LOCATION '/user/skadmin/hive2/warehhouse/hive_mysk3.db'

模糊查询数据库名称

SHOW DATABASES LIKE 'hive_*';

查看数据库详细信息

DESC DATABASE EXTENDED hive_mysk;

删除数据库

DROP DATABASE hive

说明:如果数据库下有表，可以使用CASCADE强制删除，但尽可能不使用CASCADE

表详解

表创建

法一：类SQL建表

CREATE TABLE IF NOT EXISTS db_hive.emp
(
    empno INT COMMENT '员工id',
    ename STRING COMMENT '员工姓名',
    job STRING COMMENT '岗位',
    mgr INT COMMENT '上级领导',
    hiredate STRING COMMENT '入职时间',
    sal STRING COMMENT '基本工资',
    comm STRING COMMENT '补助',
    deptno INT COMMENT '部门编号'
)
COMMENT '员工表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

法二：从已有表中导入数据

CREATE TABLE IF NOTE EXISTS db_hive.emp_res
AS SELECT empno, ename, job FROM db_hive.emp;

法三：LIKE

CREATE TABLE IF NOT EXISTS db_hive.emp_like LIKE db_hive.emp;

法一常用语初始创建表，法二常用语数据分析过程或分析结束后的结果表

导入数据

法一：本地上传

LOAD DATA LOCAL INPATH '/opt/datas/emp.txt' INTO TABLE db_hive.emp;

法二：直接上传数据到hdfs

dfs -put /opt/datas/emp.txt /user/hive/warehouse/db_hive.db/emp

表类型

表类型包括：

管理表/内部表（默认）
外部表
分区表

区别：
管理表：当表被删除时，其元数据和数据都会被一同删除
外部表：当表被删除时，数据不会被删除

外部表应用场景

多部门共享数据分析
避免重复的数据文件反复分析
企业80%以上都创建外部表进行分析。

创建外部表流程

法一：创建表结构

CREATE EXTERNAL TABLE IF NOT EXISTS db_hive.emp_ext
(
    empno INT COMMENT '员工id',
    ename STRING COMMENT '员工姓名',
    job STRING COMMENT '岗位',
    mgr INT COMMENT '上级领导',
    hiredate STRING COMMENT '入职时间',
    sale STRING COMMENT '基本工资',
    comm STRING COMMENT '补助',
    deptno INT COMMENT '部门编号'
)
COMMENT '员工表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE 
LOCATION '/user/hive/warehouse2/dbdata/20220426/';

法二：从内部表导入

//常规语句
CREATE EXTERNAL TABLE IF NOT EXISTS db_hive.emp_external LIKE db_hive.emp ;

//指定存储位置
CREATE EXTERNAL TABLE IF NOT EXISTS db_hive.emp2_external LIKE db_hive.emp LOCATION '/user/hive/warehouse/db_hive.db/emp_external'

注：不允许使用CREATE TABLE AS SELECT创建外部表

之后在HDFS程序上创建目录，上传数据

dfs -put /opt/datas/emp.txt /user/hive/warehouse/db_hive.db/emp_external;

分区表

分区表实际上就是一个对应HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。而hive分区就是分目录，把一个大的数据集根据业务需要分割成更小的数据集（目录）再通过WHERE子句查询，指定需要访问的数据，从而提升查询效率。

eg：创建一个分区表并导入数据

准备数据

创建表脚本

CREATE EXTERNAL TABLE IF NOT EXISTS db_hive.emp_partition
(
 empno INT COMMENT '员工id',
 ename STRING COMMENT '员工姓名',
 job STRING COMMENT '岗位',
 mgr INT COMMENT '上级领导',
 hiredate STRING COMMENT '入职时间',
 sale STRING COMMENT '基本工资',
 comm STRING COMMENT '补助',
 deptno INT COMMENT '部门编号'
)
PARTITIONED BY (year STRING, month STRING, DAY STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

导入数据

LOAD DATA LOCAL INPATH '/opt/datas/emp.txt' INTO TABLE db_hive.emp_partition PARTITION (year='2022',month='04',day='24');
LOAD DATA LOCAL INPATH '/opt/datas/emp.txt' INTO TABLE db_hive.emp_partition PARTITION (year='2022',month='04',day='23');
LOAD DATA LOCAL INPATH '/opt/datas/emp.txt' INTO TABLE db_hive.emp_partition PARTITION (year='2022',month='04',day='25');
LOAD DATA LOCAL INPATH '/opt/datas/emp.txt' INTO TABLE db_hive.emp_partition PARTITION (year='2022',month='04',day='26');

测试查询

SELECT COUNT(*) FROM db_hive.emp_partition where year = '2022' and month='04' and day in('23','24');

企业流程：

将数据业务需求定时有规律上传到HDFS目录中，例如：每天，每月，每年
新建文件夹：

dfs -mkdir -p /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=23;
dfs -mkdir -p /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=24;
dfs -mkdir -p /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=25;
dfs -mkdir -p /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=26;

上传数据

dfs -put /opt/datas/emp.txt /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=23 ;
dfs -put /opt/datas/emp.txt /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=24 ;
dfs -put /opt/datas/emp.txt /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=25 ;
dfs -put /opt/datas/emp.txt /user/hive/warehouse2/dbdata/emp_partition/year=2022/month=04/day=26 ;

创建分区表

CREATE EXTERNAL TABLE IF NOT EXISTS db_hive.emp_partition3
(
 empno INT COMMENT '员工id',
 ename STRING COMMENT '员工姓名',
 job STRING COMMENT '岗位',
 mgr INT COMMENT '上级领导',
 hiredate STRING COMMENT '入职时间',
 sale STRING COMMENT '基本工资',
 comm STRING COMMENT '补助',
 deptno INT COMMENT '部门编号'
)
PARTITIONED BY (year STRING,month STRING,day STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE 
LOCATION '/user/hive/warehouse2/dbdata/emp_partition';

修复分区信息

MSCK REPAIR TABLE db_hive.emp_partition3;

查询

SELECT COUNT(*) from db_hive.emp_partition3 WHERE year='2022' AND month='04' AND day in ('24','25');

Hive学习笔记（三） MySQL安装、数据库表详解

Hive学习笔记（三） MySQL安装、数据库表详解

Hive学习笔记（三） MySQL安装

元数据服务（metastore）的作用

Hive安装的三种方式

更改hive的metastore存储为mysql

数据库命令

建库语句

表详解

表创建

导入数据

表类型

创建外部表流程

注：不允许使用CREATE TABLE AS SELECT创建外部表

分区表

Hive学习笔记（四）数据类型，存储格式，数据压缩

Hive学习笔记（二） Hive基本操作

Jay-codeman

Comments | NOTHING

Hive学习笔记（三） MySQL安装

元数据服务（metastore）的作用

Hive安装的三种方式

更改hive的metastore存储为mysql

数据库命令

建库语句

表详解

表创建

导入数据

表类型

创建外部表流程

注：不允许使用CREATE TABLE AS SELECT创建外部表

分区表

Hive学习笔记（四） 数据类型，存储格式，数据压缩

Hive学习笔记（二） Hive基本操作

Jay-codeman

Comments | NOTHING

你想搜索什么...

Hive学习笔记（四）数据类型，存储格式，数据压缩