0


Hive数仓

1、概念

数据仓库,英文名称为Data Warehouse,可简写为DW或DWH。数据仓库顾名思义,是一个很大的数据存储集合,出于企业的分析性报告和决策支持目的而创建,对多样的业务数据进行筛选与整合。

  • OLAP:在线分析处理(Online Analytical Processing)是大数据技术中快速解决多维分析问题的方法之一。由于OLAP需要快速读取大量数据,因此它对数据的读取吞吐量和计算效率有很高的要求。OLAP分析一般需要设计数据立方体,立方体由分析的维度(dimension)、层级(level)和指标(metric)来定义,支持上卷(roll-up)、钻取(drill-down)、切片(slicing)和切块(dicing)等分析操作。应用:数据仓库。
  • OLTP与OLAP相对的是OLTP。OLTP的全称是联机事务处理(Online Transaction Processing),是传统关系型数据库的主要应用。OLTP的特点是实现插入、更新、删除等事务的在线处理,但系统需要保证事务的完整性,满足ACID原则。应用:各类关系型数据库。

主要特征:面向主题、集成性、非易失性、时变性

2、Hive

Hive是建立在Hadoop上的开源数据仓库,可将Hadoop文件中的结构化、半结构化数据文件映射成数据库表,并提供查询模型(HQL),方便访问和分析。其核心是将HQL转换为MapReduce程序。Hive由FaceBook开源。

Hive存在的意义:MR程序属于八股文形式的编程,用户应更注重编写SQL,同时编写MR程序十分繁琐。

映射:

将文档映射成数据表

2 lisi 19

3 wangwu 15

4 zhaoliu 11
idnameage2lisi193wangwu154zhaoliu11
整体架构图如下:
在这里插入图片描述

2.1、元数据

元数据(Metadata)为描述数据的数据,主要是描述数据属性的信息。Hive元数据包括表位置、类型、属性等。元数据存储在关系型数据库中,Hive内置Derby或使用第三方数据库,如MySQL等。

元数据服务(Metastore)用来管理元数据,对外暴露服务地址,让各种客户端通过连接Metastore,再由其连接数据库来存取元数据。Metastore可实现多客户端连接,各客户端即不需知道数据库密码即可使用元数据,从而保证数据安全。
在这里插入图片描述
Metastore三种配置模式:内嵌、本地、远程
内嵌本地远程Metastore单独配置、启动否否是Metadata存储介质DerbyMySQLMySQL

2.2、安装部署
2.2.1、安装MySQL

1、删除centos自带的数据库

rpm -qa|grep mariadb
rpm-e--nodeps mariadb-libs-5.5.65-1.el7.x86_64

2、下载mysql并解压

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 tar-Jxvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz 

3、设置软链接

mv mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz /export/software
 exportPATH=$PATH:/export/software/mysql8/bin
  mysql --version#验证软链接

4、修改文件夹

mysql-8.0.20-linux-glibc2.12-x86_64文件夹更名为mysql8

创建文件夹

mkdir-p /data/mysql8_data

5、添加用户组和用户

groupadd mysql
useradd-g mysql mysql
# 更改属主和数组chown-R mysql:mysql /data/mysql8_data
# 更改模式chmod-R750 /data/mysql8_data

6、编辑配置文件

vi /etc/my.cnf

my.cnf

[mysql]
# 默认字符集
default-character-set=utf8mb4
[client]
port       = 3306
socket     = /tmp/mysql.sock
[mysqld]
port       = 3306
server-id  = 3306
user       = mysql
socket     = /tmp/mysql.sock
# 安装目录
basedir    = /usr/local/mysql8
# 数据存放目录
datadir    = /data/mysql8_data/mysql
log-bin    = /data/mysql8_data/mysql/mysql-bin
innodb_data_home_dir      =/data/mysql8_data/mysql
innodb_log_group_home_dir =/data/mysql8_data/mysql
# 日志及进程数据的存放目录
log-error =/data/mysql8_data/mysql/mysql.log
pid-file  =/data/mysql8_data/mysql/mysql.pid
# 服务端字符集
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1
##### 以上涉及文件夹明,注意修改
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
binlog_format=mixed
binlog_expire_logs_seconds =864000
# 创建表时使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

7、初始化

mysqld --defaults-file=/etc/my.cnf --basedir=/export/software/mysql8 --datadir=/data/mysql8_data/mysql --user=mysql --initialize-insecure
  • --defaults-file:指定配置文件(要放在–initialize 前面)
  • --user: 指定用户
  • --basedir:指定安装目录
  • --datadir:指定初始化数据目录
  • --intialize-insecure:初始化无密码(否则生成随机密码)

8、启动

# 完整命令
/export/software/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf &# 若添加了PATH变量,可省略如下
mysqld_safe --defaults-file=/etc/my.cnf &

9、登录

  • 无密码登录
mysql -u root --skip-password
  • 有密码登录:若初始化时设置了随机密码,在 /data/mysql8_data/mysql/mysql.log 查看
mysql -u root -p

10、修改密码

# 修改密码
ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';# 刷新权限
FLUSH PRIVILEGES;

11、配置远程连接

  • 查看用户
USE mysql;

SELECT user,host,plugin,authentication_string FROM user;
  • 创建用户
# 创建用户
CREATE user 'root'@'%';# 设置首次密码
ALTER USER'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';# 授权用户所有权限,刷新权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

参考链接:Linux:CentOS7安装MySQL8(详) - Jaywee - 博客园

2.2.2、安装Hive

1、下载Hive

下载地址:http://archive.apache.org/dist/hive/

2、编辑环境变量

etc/profile.d下新建一个hive.sh

vim /etc/profile.d/hive.sh

添加命令

exportHIVE_HOME=/export/soft/apache-hive-3.1.2-bin
exportPATH=$PATH:$HIVE_HOME/bin

环境变量生效

source /etc/profile.d/hive.sh 

3、修改配置文件

将/export/soft/apache-hive-3.1.2-bin/conf/hive-env.sh.template改名为hive-env.sh,并在后面追加

exportJAVA_HOME=$JAVA_HOMEexportHADOOP_HOME=$HADOOP_HOMEexportHIVE_HOME=$HIVE_HOME

在conf文件夹新建hive-site.xml,内容为

<?xml version="1.0" encoding="UTF-8" standalone="no"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><configuration><!-- jdbc  URL --><property><name>javax.jdo.option.ConnectionURL</name><!--规划mysql中存储元数据的数据库名为metastore,当初始化数据时不存在时自动创建--><value>jdbc:mysql://192.168.132.102:3306/metastore?createDatabaseIfNotExist=true&amp;useSSL=false</value></property><!-- jdbc  Driver--><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value></property><!--数据库用户名--><property><name>javax.jdo.option.ConnectionUserName</name><value>root</value></property><!--数据库密码--><property><name>javax.jdo.option.ConnectionPassword</name><value>123</value></property><!-- Hive 元数据存储版本的验证 --><property><name>hive.metastore.schema.verification</name><value>false</value></property><!--元数据存储授权--><property><name>hive.metastore.event.db.notification.api.auth</name><value>false</value></property><!-- Hive 默认在 HDFS 的工作目录 --><property><name>hive.metastore.warehouse.dir</name><value>/user/hive/warehouse</value></property></configuration>

4、添加jdbc的jar包

将mysql对应版本的jar包放到export/soft/apache-hive-3.1.2-bin/lib下

5、解决包版本不统一问题

把hadoop/share/hadoop/common/lib下的guava包替换掉hive/lib下的包

最终形成如下架构:
在这里插入图片描述

2.3、Hive启动

客户端与服务的依赖关系
在这里插入图片描述

2.2.1、启动服务

启动Metastore

nohup /export/soft/apache-hive-3.1.2-bin/bin/hive --service metastore &

启动hiveserver2

nohup /export/soft/apache-hive-3.1.2-bin/bin/hive --service hiveserver2  &
2.2.2、启动客户端

启动hive客户端(不推荐)

/export/soft/apache-hive-3.1.2-bin/bin/hive

启动beeline客户端

/export/soft/apache-hive-3.1.2-bin/bin/beeline

启动客户端后输入下面命令进行连接

! connect jdbc:hive2://hadoop101:10000

如出现 User: root is not allowed to impersonate root 错误,在hadoop NameNode节点的core.xml中添加

<property><name>hadoop.proxyuser.root.hosts</name><value>*</value></property><property><name>hadoop.proxyuser.root.groups</name><value>*</value></property>
2.4、使用Idea连接Hive

下载驱动并安装
在这里插入图片描述
在这里插入图片描述

2.5、基本操作
  1. 创建数据库create database test;在HDFS的/user/hive/warehouse路径下生成test.db文件
  2. 使用数据库use test
  3. 删除数据库使用drop,但只能删除空数据的库
  4. 创建表

​ 建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [DEFAULT value] [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] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS] 
[STORED BY StorageHandler] -- 仅限外部表
[WITH SERDEPROPERTIES (Options)] -- 仅限外部表
[LOCATION OSSLocation]; -- 仅限外部表
[LIFECYCLE days]
[AS select_statement]
  • 数据类型

​ 分为两类:原生类型和复杂类型,最常用为String和int

create table test_data(
    datadate string comment "日期" ,
    datatime string comment "时间",
    open double comment "股票开盘价",
    low double comment "股票最低价" ,
    high double comment "股票最高价" ,
    close double comment "股票收盘价"
)
row format delimited
fields terminated by "\t";
5. 插入数据

​ 没有数据源,可使用excel导出txt文件如图,将txt文件上传到HDFS的/user/hive/warehouse/test.db/test_data路径下(不推荐跳过hive直接操作数据)
在这里插入图片描述

select * from test_data;

查询Hive中数据,可得
在这里插入图片描述
Hive默认分隔符‘\001’

2.6、函数使用

和普通SQL差别不大

1、导入函数load

  • 从系统本地加载

使用hive完成数据导入,local inpath为hive服务所在路径,源文件还在及复制操作

 load data local inpath '/export/data/hivedata/data.txt' into table test.test_data;
  • 从HDFS加载,源文件被删除即移动(剪切)操作 load data inpath '/data.txt' into table test.test_data;

2、insert+select

为什么不直接insert,因为insert底层走的MapReduce程序,非常慢!

insert+select表示将后面查询返回的结果作为内容插入到指定表中。

注意:

  • 需要保证查询结果列的数目和插入列数目一直
  • 如果查询和插入的数据类型不同,会进行转换,有可能失败,失败的数据为NULL
insert into  table test_data3 select * from test_data2;

3、distinct

去重查询 select distinct 列 from 表

4、where

筛选 select 列 from 表 where 条件

5、聚合
函数avg平均值count计数max最大值min最小值sun求和
6、group by

按照某列聚合

select 列 from 表 group by 列,注意使用group by 时,只能查询group by的列或者聚合函数

7、having

having用来筛选分组后的各组数据,并可以在having中使用聚合函数,此时where、group by已经结束。

8、order by

对结果集进行排序

9、limit

限制返回行数

查询过程执行顺序:from>where>group by及聚合函数>having>order by>select

10、join

内连接
A join B=A∩B 在这里插入图片描述
写法:

  • select 表1.列,表2.列 from 表1 inner join 表2 on条件
  • select 表1.列,表2.列 from 表1 join 表2 on条件
  • select 表1.列,表2.列 from 表1 , 表2 where 条件

左外连接

A left join B在这里插入图片描述
写法:

  • select 表1.列,表2.列 from 表1 left join 表2 on条件
  • select 表1.列,表2.列 from 表1 left outer join 表2 on条件

小结:

  • 显示所有函数
show functions ;
  • 函数描述
describe  function extended sum
2.7、内、外部表
  • 内部表:需要Hive完全管理控制表的整个声明周期,使用内部表,在表删除时,HDFS上文件也会删除。默认创建的表就是内部表。
  • 外部表:为防止误删除,使用外部表,表删除时,HDFS上的数据会保留。创建时使用external关键字。
2.8、分区表

在同一文件夹下的txt中的数据会合并成一张表,where语句需要进行全表扫描才能过滤出结果,当文件数据量多时,扫描效率慢。针对HDFS的文件进行优化,只扫描单个的txt文件提高扫描效率。将单个txt文件存储单独的文件夹中,文件夹按照分区值命名。
在这里插入图片描述

  • 分区表语法:分区字段不能是表中已经存在的字段。数据查询时,分区字段会在查询时,出现在查询字段中。
  • 静态分区数据加载:

静态分区:分区属性值由用户在加载数据时候手动指定。

load data [local] inpath ‘filepath’ into table tablename partition (分区字段=‘分区值’) ;

local参数用于指定待加载的数据位于本地文件还是HDFS文件。

  • 多重分区

Hive支持多个分区字段Partition by (partition1 data_type, partition2 data_type,…)。

多重分区是在一个分区下继续进行分区,在HDFS的层面就是文件夹下再划分子文件夹。
在这里插入图片描述

  • 动态分区

分区字段值是基于查询结果(参数位置)自动推断出来。核心语法 insert+select

启用动态分区,默认最多100个分区。

--设置动态分区
set hive.exec.dynamic.partition=true;
--设置动态分区模式,strict至少有一个静态分区,nonstrict可以没有静态分区
set hive.exec.dynamic.partition.mode=nonstrict;

将数据查询出来后插入新表中,tmp.*, tmp.datadate位置不能错,

insert into  test_data5 partition (tabledate) select tmp.*, tmp.datadate from test_data tmp;
2.9、分桶表

一种用于优化查询而设计的表类型。分桶表对应的数据文件在底层会被分解为若干部分,即独立的小文件。在分桶时,要指定根据哪个字段将数据分成几桶(部分)。

分桶规则:桶编号相同的数据会被分到同一个桶当中。

桶编号=hash(分桶字段) 取模 桶个数,其中分桶字段类型为String等复杂数据类型,将是从该类型派生的某个数字。

创建分桶表

create table test_data6(
                           datadate string comment "日期" ,
                           datatime string comment "时间",
                           open double comment "开盘价",
                           low double comment "最低价" ,
                           high double comment "最高价" ,
                           close double comment "收盘价"
) clustered by  (datadate )
    into 3 buckets ;

插入分桶表

insert into test_data6 select * from test_data

查看分桶表
在这里插入图片描述
分桶好处:

  1. 减少全表扫描
  2. join时提高MR效率,减少笛卡尔积数量,根据join的字段进行分桶
  3. 数据高效抽样
2.10、Hive事务

update、delete通过标记使得原数据失效,并新建数据文件,从而实现数据的更新和删除,不建议使用。


本文转载自: https://blog.csdn.net/wslzoooo/article/details/136083643
版权归原作者 空指针异常Null_Point_Ex 所有, 如有侵权,请联系我们删除。

“Hive数仓”的评论:

还没有评论