什么是HIVE

Hive 是基于 Hadoop 的一个数据仓库工具。以下是具体介绍:

功能特点：Hive 可以将结构化的数据文件映射为一张数据库表，并提供完整的 SQL 查询功能，能将 SQL 语句转换为 MapReduce 任务进行运行。它允许熟悉 SQL 的用户方便地查询数据，也支持熟悉 MapReduce 的开发者自定义 mapper 和 reducer，以处理复杂的分析工作。
优势：学习成本低，通过类 SQL 语句可快速实现简单的 MapReduce 统计，无需开发专门的 MapReduce 应用，十分适合数据仓库的统计分析。
应用场景：常用于对时效性要求不高的数据分析场景。由于 Hive 底层依赖 Hadoop 的 HDFS 存储数据，利用 MapReduce 进行计算，因此能够处理大规模的数据，在处理海量结构化日志的数据统计等方面应用广泛。
与数据库的区别：
1. 数据库一般用于在线应用，支持对某一行或某些行数据的更新、删除等操作，采用 “写时模式”，数据加载慢但查询快。
2. 而 Hive 不支持对具体行的操作，也不支持事务和索引，采用 “读时模式”，适合处理非结构化或存储模式未知的数据，更侧重于对海量数据的批量处理和分析。

HIVE安装

配置mysql安装源

（在线安装方法）

#下载安装源
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 安装 mysql 源
yum localinstall mysql57-community-release-el7-11.noarch.rpm
# 导入key
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# 修改国内源
vim /etc/yum.repos.d/mysql-community.repo
修改 baseurl 为 https://mirrors.cloud.tencent.com/mysql/yum/mysql-5.7-community-el7-x86_64/


#安装mysql
yum install -y mysql-community-server

安装mysql （本地安装方法）



00#rpm包安装
#tar -zvxf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/mysql


#2.解压：
cd /usr/local
tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar
mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql-5.7.22
ln -s mysql-5.7.22 mysql


#3.添加用户组和用户
#添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql
useradd -g mysql mysql
#4.安装
cd /usr/local/mysql
mkdir data
chown -R mysql:mysql ./
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/m'y's'q --datadir=/usr/local/mysql/data


#将mysql/目录下除了data/目录的所有文件，改回root用户所有
chown -R root .
#mysql用户只需作为mysql-5.7.22/data/目录下所有文件的所有者
chown -R mysql data


#5.复制启动文件
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
cp bin/my_print_defaults /usr/bin/ 


#6.修改启动脚本
vi /etc/init.d/mysqld
#修改项：
basedir=/usr/local/mysql-5.7.22/
datadir=/usr/local/mysql-5.7.22/data
port=3306
#加入环境变量，编辑 /etc/profile，这样可以在任何地方用mysql命令了
vi ~/.bash_profile
#添加mysql路径，加入下面内容，按ESC-->:wq保存
export PATH=$PATH:/usr/local/mysql-5.7.22/bin
#刷新立即生效
source ~/.bash_profile


#7.修改mysql配置项


vi /etc/my.cnf
#配置如下：


[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
user = mysql
tmpdir = /tmp
symbolic-links=0


[mysqld_safe]


log-error = /usr/local/mysql/data/error.log
pid-file = /usr/local/mysql/data/mysql.pid
#!includedir /etc/my.cnf.d


#8.启动mysql
service mysqld start


#如启动失败，删除 /usr/local/mysql-5.7.22/data下所有文件，重新执行./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data，再启动


#9.进入mysql修改初始密码，修改远程连接的用户权限问题
mysql -uroot -p


ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';


use mysql;
UPDATE user SET host='%' WHERE user='root';
flush privileges;


#开机自启动
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list
  mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关

配置mysql

#启动mysql 服务
systemctl start mysqld
#设置开机启动
systemctl enable mysqld
# 重载所有修改过的配置文件
systemctl daemon-reload
# 关闭密码验证
vim /etc/my.cnf
# mysqld选项下加入
validate_password=OFF
#重启mysql服务
systemctl restart mysqld
# 得到临时密码
grep 'temporary password' /var/log/mysqld.log
#登录mysql
mysql -uroot -p
# 开发环境 修改密码为root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
#修改root账号登录限制
use mysql;
UPDATE user SET host='%' WHERE user='root';
flush privileges; 
#创建metastore元数据库
create database metastore
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

安装HIVE

#解压apache-hive-3.1.2-bin.tar.gz到/opt/module/目录下面
   tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/module/
#修改`/etc/profile.d/my_env.sh`，添加环境变量
   vim /etc/profile.d/my_env.sh
#添加内容
#HIVE_HOME
   export HIVE_HOME=/opt/module/apache-hive-3.1.2-bin
   export PATH=$PATH:$HIVE_HOME/bin
#刷新环境变量
source /etc/profile

hive基础配置

#解决日志Jar包冲突
mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

#拷贝mysql-connector.jar到lib库
cp /opt/software/mysql-connector-java-5.1.27-bin.jar $HIVE_HOME/lib

配置hive-site.xml

挑转到/opt/module/apache-hive-3.1.2-bin/conf/目录新建文件 hive-site.xml

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>


<configuration> 
  <!--jdbc连接的URL-->
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://hadoop100:3306/metastore?useSSL=false</value>
  </property>
  <!--jdbc连接的Driver-->
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>
  <!--jdbc连接的username-->
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
  <!--jdbc连接的password-->
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</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> 
  <property>
    <name>hive.server2.enable.doAs</name>
    <value>false</value>
</property>
<property>
    <!--取消本地模式改为false-->
    <name>hive.exec.mode.local.auto</name>
    <value>true</value>
</property>
 <property>
<name>mapred.map.child.java.opts</name>
<value>-Xmx2048m</value>
</property>
</configuration>

初始化Hive元数据库

1	schematool -initSchema -dbType mysql -verbose

优化mapreduce

1	vim $HADOOP_HOME/etc/hadoop/mapred-site.xml

增加配置

<property>
    <name>mapreduce.map.memory.mb</name>
    <value>1536</value>
</property>
<property>
    <name>mapreduce.map.java.opts</name>
    <value>-Xmx1024M</value>
</property>
<property>
    <name>mapreduce.reduce.memory.mb</name>
    <value>3072</value>
</property>
<property>
    <name>mapreduce.reduce.java.opts</name>
    <value>-Xmx2560M</value>
</property>

配置beeline

配置core-site.xml 使其任意节点都可以访问hadoop

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

启动 hiveserver2

#前端启动
hiveserver2
#后台启动
nohup hiveserver2 &

登录命令

1 2	beeline -u jdbc:hive2://localhost:10000 -n root -p 123456 [密码随意] beeline -u jdbc:hive2://localhost:10000 -n root -p 123456 -e 'show tables;'

dbeaver登录

获取文件 hadoop-common-3.1.3.jar
获取文件 hive-jdbc-3.1.2-standalone.jar
添加hive数据库链接

hive 数据操作语句

元数据查看语句

--查看数据库
show  database db_hive
--过滤查看数据库
show databases like 'db_hive*';
--查看详情
desc database db_hive
desc database extended db_hive;
--查看表
show tables;
--查看表列详情
desc dept;
--查看表所有详细信息
desc extended emp;
show formatted emp;
--查看分区信息
show partitions emp;

建库操作

-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]

建表操作

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 DELIMITED --数据格式
[FIELDS TERMINATED BY char] --列分隔
[COLLECTION ITEMS TERMINATED BY char] --复合数据item分隔
[MAP KEYS TERMINATED BY char] --复合数据key分隔
[LINES TERMINATED BY char]] --行分隔
[STORED AS file_format] --压缩格式
[LOCATION hdfs_path] --表数据文件存储路径
[TBLPROPERTIES (property_name=property_value, ...)] --内外部表转换
[AS select_statement]

上传数据

--load
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];

--上传hdfs
dfs -put /opt/module/hive/datas/student.txt /user/atguigu/hive;

--插入数据
insert into table  student_par values(1,'wangwu'),(2,'zhaoliu');

--覆盖插入并且使用结果集进行插入
insert overwrite table student_par select id, name from student ;

下载数据

insert overwrite local directory '数据的path'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' dql_command;

--hadoop 导出
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/datas/export/student3.txt;

--hive shell导出
bin/hive -e 'select * from default.student;' >
/opt/module/hive/datas/export/student4.txt;

--export导出
 export table default.student to
 '/user/hive/warehouse/export/student';

select语句

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

Hive复合数据类型

数组array: array<value数据类型>

相同数据类型
有序的排列
下标为数字

--查询复合数据
select  a_score[0] from student2

--构造复合数据-array
select array(值,值) from student

集合struct: struct<key值:value数据类型,key值:value数据类型>

预定义个数
预定义顺序
key预定义
数据类型可不同

--查询复合数据
select  s_score.chinese from student2

--构造复合数据
select named_struct(key,value,key,value)
from student

字典map: map<key数据类型,value数据类型>

标准字典类型
key自定义
数据类型可不同
个数不限

--查询复合数据
select  m_score['语文'] from student2

--构造复合数据
select map(key,value,key,value)
from student

hive 内置函数

-- 查看系统自带的函数
show functions;
-- 显示自带的函数的用法
desc function upper;
-- 详细显示自带的函数的用法
desc function extended upper;

python连接hive

linux环境安装python

# 下载安装包 或上传安装包
wget https://www.python.org/ftp/python/3.9.0/Python-3.9.0.tgz

# 下载
wget https://www.python.org/ftp/python/3.9.9/Python-3.9.9.tgz
# 解压
tar -zxvf Python-3.9.10.tgz -C /opt/module/
# 支撑包
yum install openssl-devel libffi-devel bzip2-devel gcc gcc-c++ wget -y
# 配置
./configure --enable-optimizations
# 安装
make altinstall

配置环境变量

# 配置环境变量
vim  /etc/profile.d/my_env.sh


PATH=$PATH:/opt/python39/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

#生效 环境变量
source /etc/profile


#或者进行软连接 配置 
cp libpython3.9.a  /usr/lib64/


ln -s /root/software/python3.9/Python-3.9.10/python /usr/bin/python3
ln -s /opt/module/Python-3.9.10/python /usr/bin/python3

安装pyhive库

pip3 install  -i https://mirrors.aliyun.com/pypi/simple/  thrift
pip3 install  -i https://mirrors.aliyun.com/pypi/simple/  thrift-sasl
pip3 install   -i https://mirrors.aliyun.com/pypi/simple/ PyHive
pip3 install   -i https://mirrors.aliyun.com/pypi/simple/ PyM

from pyhive import hive
conn=hive.connect(host='localhost',port=10000,username='root',database='db_hive')
cursor=conn.cursor()
sql='show tables'
cursor.execute(sql)
print(cursor.fetchall())

分区表

分区是将一个表或索引物理地分解为多个更小、更可管理的部分。
分区对应用透明，即对访问数据库的应用而言，逻辑上讲只有一个表或一个索引（相当于应用“看到”的只是一个表或索引），但在物理上这个表或索引可能由数十个物理分区组成。

分区应用场景

oracle分区表种类

范围分区(range)
列表分区(list)
散列分区(hash)
组合组合分区(subpartition)

oracle分区-范围分区

CREATE TABLE ORDER_ACTIVITIES   
(  ORDER_ID      NUMBER(7) NOT NULL,   
    ORDER_DATE    DATE,   
    TOTAL_AMOUNT NUMBER,   
    CUSTOTMER_ID NUMBER(7),   
    PAID   CHAR(1)   
)   -- 建表语句不变
PARTITION BY RANGE (ORDER_DATE) -- 范围分区创建 语句 关键字 range(列名)
(  
  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,  
    -- 分区名 （时间值）表空间
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,  
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (MAXVALUE) TABLESPACE ORD_TS03   --使用maxvalue 将其他不符合上述范围的值放入其中
);

oracle分区-列表分区

CREATE TABLE ORDER_ACTIVITIES  
(  PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,   
    CUSTOMER_ID  NUMBER(7) NOT NULL,     
    STATUS       VARCHAR2(20))   
PARTITION BY LIST (STATUS)    -- 范围分区创建 语句 关键字 LIST(列名)
(    PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE PROB_TS01,  -- 
      PARTITION PROB_INACTIVE VALUES ('INACTIVE','unknow') TABLESPACE PROB_TS02  
);

oracle分区-散列分区

CREATE TABLE HASH_TABLE   
( COL NUMBER(8),   
  INF VARCHAR2(100)   
)   
PARTITION BY HASH (COL)    -- 范围分区创建 语句 关键字 HASH(列名) 
(   
  PARTITION PART01 TABLESPACE HASH_TS01,   
  PARTITION PART02 TABLESPACE HASH_TS02,   
  PARTITION PART03 TABLESPACE HASH_TS03   
)

oracle分区-组合分区

CREATE TABLE SALES   
(  
PRODUCT_ID VARCHAR2(5),  
SALES_DATE DATE,  
SALES_COST NUMBER(10),  
STATUS VARCHAR2(20)  
)  
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)  
(  
   PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009   
  (   
      SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,   
      SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009   
  ),   
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009   
  (   
      SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,   
      SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009   
  )   
)

oracle分区-分区表操作

-- 添加分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));  
--注意：以上添加的分区界限应该高于最后一个 分区界限。
-- 添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 

-- 删除分区
ALTER TABLE SALES DROP PARTITION P3;  
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
-- 注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

-- 交换分区
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE nonpartition_name;

--将一个分区(子分区)和非分区表进行数据交换，oracle交换的方法是其实是对逻辑存储段进行交换。使用INCLUDEING INDEXES子句可以同步将本地索引也进行交换，使用WITH VALIDATATION子句还可以实现行数据的验证。

--交换分区时如果不带UPDATE INDEXES子句，则全局索引或全局索引基于的分区将变为不可用。

hive分区-创建分区表

在 Hadoop 中，Hive 分区表通常以特定的目录结构来存储。

每个分区对应一个独立的目录，目录名通常包含分区列的值。数据文件会存储在相应的分区目录下。

-- 创建分区表
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

hive分区-分区表操作

-- 分区表数据导入
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');

--select分区表插入数据
insert into table log_list_6 partition(dat='20221231') select * from log_list_tmp
--多表分区插入 
from student 
  insert overwrite table student partition(month='201707')select id, name where month='201707'
  insert overwrite table student partition(month='201706')select id, name where month='201706';
-- 查看分区
show partitions tab_name;

--添加分区
alter table dept_partition add partition(day='20200404') ;
--添加多分区
alter table dept_partition add partition(day='20200405') partition(day='20200406');
--删除分区
alter table dept_partition drop partition (day='20200406');
--查看分区表信息
show partitions dept_partition;
--查看分区表结构
desc formatted dept_partition;
--修改分区表
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

超市分区表示例

create table supermarket_p (
id string, -- 行 ID  
ord_id string comment '订单 ID',  
ord_date string comment '订单日期', 
exch_date string comment '发货日期',
exch_type string comment '邮寄方式',
cust_id string comment '客户 ID ',
cust_name string comment '客户名称',
d_type string comment '细分',
city string comment '城市', 
prov string comment '省/自治区',  
country string comment'国家',
area string comment '地区', 
pro_id string comment '产品 ID',  
type1 string comment '类别',
type2 string comment '子类别', 
pro_name string comment '产品名称',
sales float comment '销售额',  
count1 int comment '数量  ',
discount float comment '折扣  ',
profit float comment '利润'
)
partitioned by (c_type1 string)
row format delimited fields terminated by '\t'

动态分区配置


--开启动态分区(默认开启)
set hive.exec.dynamic.partition=true
--指定非严格模式 nonstrict模式表示允许所有的分区字段都可以使用动态分区
set hive.exec.dynamic.partition.mode=nonstrict
--在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
set hive.exec.max.dynamic.partitions=1000
--在每个执行MR的节点上，最大可以创建多少个动态分区(分区字段有多少种设多少个)
set hive.exec.max.dynamic.partitions.pernode=100
--整个MR Job中，最大可以创建多少个HDFS文件。默认100000
set hive.exec.max.created.files=100000
--当有空分区生成时，是否抛出异常
set hive.error.on.empty.partition=false
--打开正则查询模式`(dt|hr)?+.+`
set hive.support.quoted.identifiers=none

分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。

分桶表注意事项

分桶策略
Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

==reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数==

==从hdfs中load数据到分桶表中，避免本地文件找不到问题==

==不要使用本地模式==

hive分桶表-创建分桶表

--创建4个分桶的分桶表
create table stu_bucket(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

--设置mapreduce数量(二选一)
set mapreduce.job.reduces=3
 set mapred.reduce.tasks=3
--向分桶表导入数据
load data inpath   '/student.txt' 
into table stu_bucket;

hive排序关键字

##hive****排序语句

--使用order by 排序
select * from student2 order by id
--使用sort by 排序
select * from student2 sort by class_name desc
--使用distribute by 分组
set mapreduce.job.reduces=15;
select * from student2 distribute by class_name sort by id desc

insert overwrite  local directory '/root/student2/'
row format delimited fields terminated by '\t'
select * from student2_b 
distribute by sex 
sort by chinese desc
--使用cluster by 分组并排序
select * from student2 cluster by class_name

使用awk 清洗 log

cat 2021-05-20.log | awk -F "\"-\"" '{split($1, arr, " ");\
split(substr(arr[4],2),dd,":");\
split(dd[1],ee,"/");\
print arr[1]"\t"ee[1]"-
"ee[2]"-"ee[3]" "dd[2]":"dd[3]":"dd[4]"\t"arr[7]"\t"$2}' | \
awk -F "\t" '{"date -d \""$2"\" +%Y%m%d%H%M%S" | getline d;print
$1"\t"d"\t"$3"\t"$4 }' | \
awk -F "\t" '{print $1"\t"$2"\t"$3"\t"(index($4,"Windows")?"Windows":
(index($4,"Linux")?"Linux":"Mac"))"\t"(index($4,"Chrome")?"Chrome":
(index($4,"Version")?"Safari":(index($4,"Firefox")?"Firefox":"Opera")))}' >
new_2021-05-20.log