是什么
Hive是Hadoop生態的資料倉庫工具
Hive將存盤在HDFS上的檔案映射為關系表
通過決議開發者提交的SQL陳述句，將SQL轉換成MR任務，提交到Hadoop執行
Hive提供了命令列客戶端和JDBC
Hive架構
Hive 安裝
將Hive的元資料庫替換為mysql
- hive自帶關系型資料庫derby用于存盤hive中的庫和表的結構資訊(metadata)
- 生產環境中通常將derby替換為mysql等性能更好的開源資料庫
- yum
yum install mysql-
使用安裝包進行離線安裝
- 下載mysql安裝包
- 使用腳本自動化安裝
#!/bin/bash
echo -e "\033[4;40;31m歡迎使用mysql離線安裝自動化腳本 v1.0\033[0m"
echo -e "\033[4;40;31m作者:Amos QQ:410507803 E-Mail:amos@amoscloud.com\033[0m\n"
read -p "請輸入mysql8的zx壓縮包檔案所在路徑(eg:/opt/mysql8.xxx.xz):" FILE_PATH
read -p "請輸入想要安裝的目錄(eg:/usr/local/mysql):" DEST_PATH
rpm -e --nodeps $(rpm -qa | grep mariadb)
echo -e "\033[40;32m (1/13)正在解壓,請耐心等待解壓程序約1-3分鐘... \033[0m"
tar Jxf $FILE_PATH -C .
echo -e "\033[40;32m 解壓完成 \033[0m"
echo -e "\033[40;32m (2/13)移動加壓后的檔案到$DEST_PATH \033[0m"
mv mysql-8*x86_64 $DEST_PATH
echo -e "\033[40;32m (3/13)添加環境變數$DEST_PATH \033[0m"
echo "export MYSQL_HOME=$DEST_PATH" >>/etc/profile
echo 'export PATH=.:$MYSQL_HOME/bin:$PATH' >>/etc/profile
source /etc/profile
echo -e "\033[40;32m (4/13)創建data目錄 \033[0m"
mkdir $DEST_PATH/data
echo -e "\033[40;32m (5/13)創建my.cnf組態檔 \033[0m"
rm -rf /etc/my.cnf
echo "
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=$DEST_PATH
datadir=$DEST_PATH/data
log-error=$DEST_PATH/error.log
pid-file = $DEST_PATH/mysql.pid
transaction_isolation = READ-COMMITTED
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
" > /etc/my.cnf
echo 'sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"' >> /etc/my.cnf
echo -e "\033[40;32m (6/13)創建mysql組 \033[0m"
groupadd mysql
echo -e "\033[40;32m (7/13)創建mysql用戶并加入mysql組 \033[0m"
useradd -g mysql mysql
echo -e "\033[40;32m (8/13)修改安裝目錄權限和所有者 \033[0m"
chown -R mysql:mysql $DEST_PATH
chmod -R 755 $DEST_PATH
echo -e "\033[40;32m (9/13)初始化mysql \033[0m"
$DEST_PATH/bin/mysqld --initialize --user=mysql
echo -e "\033[40;32m (10/13)嘗試啟動mysql \033[0m"
$DEST_PATH/support-files/mysql.server start
echo -e "\033[40;32m (11/13)將mysqld添加為服務并設定開機自啟動 \033[0m"
cp $DEST_PATH/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 345 mysqld on
echo -e "\033[40;32m (12/13)重啟mysql \033[0m"
service mysqld restart
echo -e "\033[40;32m (13/13)讀取臨時密碼 \033[0m"
TEMP_PW=$(cat $DEST_PATH/error.log | grep 'password' | awk -F' ' '{print $NF}')
echo -e "
\033[40;32m mysql的初始臨時密碼為:$TEMP_PW \033[0m
\033[40;32m 使用初始密碼登錄mysql后,您可以使用如下SQL修改初始密碼: \033[0m
\033[40;33m ALTER user 'root'@'localhost' IDENTIFIED BY 'a123456'; \033[0m
\033[40;32m 使用如下SQL添加可遠程訪問的root用戶: \033[0m
\033[40;33m CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'a123456'; \033[0m
\033[40;33m GRANT ALL ON *.* TO 'root'@'%'; \033[0m
\033[40;33m FLUSH PRIVILEGES; \033[0m
\033[40;32m 3秒后將使用初始密碼登錄mysql,感謝您的使用 \033[0m
"
sleep 3
mysql -uroot -p$TEMP_PW安裝Hive
-
上傳解壓
-
修改組態檔
- hive-env.sh
HADOOP_HOME=/opt/hadoop-2.7.7
HIVE_CONF_DIR=/opt/hive-2.3.9/conf
JAVA_HOME=/opt/jdk1.8- hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://bd0701:3306/metastore</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
</configuration>- 將mysql驅動包上傳到hive的lib中
- 初始化metastore./schematool -initSchema -dbType mysql- 配置環境變數
echo 'export HIVE_HOME=/opt/hive-2.3.9' >> /etc/profile
echo 'export PATH=$HIVE_HOME/bin:$PATH' >> /etc/profile
source /etc/profileCOPY-
啟動hive
-
hive提供了控制臺操作方式
# hive 用于啟動終端 hive -
hive提供JDBC服務器的遠程連接方式
- 修改hdfs的訪問權限
修改hadoop core-site.xml檔案<property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value> </property> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value> </property> - 分發組態檔，并重啟HDFS集群
- 使用hiveserver2 開啟hive的JDBC服務器
- hive提供beeline客戶端連接hive2服務
- 修改hdfs的訪問權限
-
beeline
beeline> !connect jdbc:hive2://hostname:10000
beeline> root
beeline>- 資料庫連接工具遠程連接 IDEA
Hive操作
DDL
- 表
-- 查看表
show tables;
-- 創建表
-- 創建 外部 表 如果不存在 表名
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 ' |- \u0001']
-- 集合 元素 結束 于
[COLLECTION ITEMS TERMINATED BY ',']
-- 映射結構的kv之間的分隔符
[MAP KEYS TERMINATED BY ':']
-- 設定行結束符
[LINES TERMINATED BY '\n']]
-- 設定檔案的存格式
-- text
-- ORC 是一種列式存盤的資料格式，用于海量資料的存盤和查詢
[STORED AS file_format]
-- 設定當前表讀取的HDFS路徑
[LOCATION hdfs_path]
-- 查看表詳情
desc table_name;
desc extended table_name;
desc formatted table_name;
-- 洗掉表
drop table table_name;- 截斷表
-- 保留表的結構 移除所有的表內容
-- 只有管理表才能進行截斷操作
truncateDML
- Hive底層將表的內容保存在HDFS，HDFS不支持檔案的隨機修改
- 所以Hive也不支持表的欄位修改和資料的update操作
資料匯入
- load data
-- 加載資料 本地 從某個路徑 可以是本地路徑或者hdfs路徑
load data [local] inpath '/root/data/student.txt'
-- 是否覆寫 加載到 某個表
[overwrite] into table student
-- 添加磁區欄位
[partition (partcol1=val1,…)];- insert
-- 將后面的查詢結果 寫入到前表中
insert into table 表名 partition (磁區欄位) select陳述句;- as select
-- 通過查詢結果創建新表
create table if not exists 表名 as select陳述句;- hdfs直接上傳檔案
-- 本質上Hive就是將表結構映射到HDFS檔案，所以直接操作底層檔案夾可以讓Hive讀取上傳的檔案，
-- 磁區表 需要檢查并修復磁區
-- 檢查表的行格式與原始資料是否一致，如果不規整的資料通常創建單列的表作為原始表
hdfs dfs -put 檔案 表路徑資料匯出
- insert
-- 不加local則匯出到HDFS路徑
insert overwrite local directory '/output_t_weblog'
row format delimited fields terminated by '|'
select *
from t_weblog_extracted_orc;-
hdfs get
如果Hive表使用text存盤格式，則可以直接從表所在路徑讀取表的內容檔案 -
hive自帶的匯入匯出工具
-- 將表資料匯出到hdfs路徑
export table default.student to '/user/hive/warehouse/export/student';
import table student2 partition(month='202005') from '/user/hive/warehouse/export/student';Sqoop
Sqoop使用手冊 – AmosCloud
DQL
-- 去重 HQL中通常需要使用各種函式對查詢的欄位進行處理
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
-- 從哪個表查
FROM db_ref.table_ref t1, (select xxx) t2
-- 添加過濾條件
[WHERE where_condition]
-- 分組 MR底層指定磁區條件
[GROUP BY col_list]
-- 排序 (全域排序) MR要實作全域排序 就只能啟動1個reducer
[ORDER BY col_list]
-- 如果MR磁區和排序的欄位 是同一個欄位，則可以直接使用 CLUSTER by
[CLUSTER BY col_list
-- 指定按某些欄位進行分布 (MR磁區，自定義partitioner)
| [DISTRIBUTE BY col_list]
-- 指定磁區內的排序方式 (MR 中的shuffle排序)
[SORT BY col_list]
]
-- 限制讀取原始資料的條數
[LIMIT number]Hive的函式
Hive的內建函式
-- 列出所有可用的函式
show functions;
-- 查看某個函式的用法
desc function substr;
-- 查看函式的詳細用法(包含案例)
desc function extended substr;- 函式的學習方法
- 官方檔案 LanguageManual UDF - Apache Hive - Apache Software Foundation
- 日期相關和String相關的函式 每個至少操作一遍，留下印象
Hive也提供用戶自定義函式的方式 添加函式
-
所有hive函式都放在
org.apache.hadoop.hive.ql.udf包中 -
自定義函式的程序
- 創建maven工程引入依賴
hive-exec - 撰寫類 繼承
org.apache.hadoop.hive.ql.udf.UDF - 撰寫方法 evaluate ,方法名不能變，但允許多載
- 將工程打成jar上傳到HDFS
- 在Hive中創建函式
- 創建maven工程引入依賴
create [temporary] function db_name.func_name as '類的參考' using jar 'hdfsJar路徑';Hive的核心原理
外部表和管理表(內部表)
- 區別
創建時 外部表需要添加external
洗掉時 管理表會洗掉元資料(mysql)和表的內容資料(HDFS)
外部表僅僅洗掉元資料(mysql)
表的磁區
-- 創建表t_weblog
create external table db_test01.t_weblog
(
line string
);
-- 加載資料 11-01 ~11-05 加載到表中
-- load data inpath 'hdfs路徑' into table 表名;
-- hdfs dfs -mv 'hdfs路徑' 表名路徑
-- load data local inpath 'linux路徑' into table 表名;
-- hdfs dfs -put 'linux路徑' 表名路徑
-- load data [local] inpath '路徑' into table 表名
select *
from t_weblog
limit 10;
-- 統計11月2日訪問的用戶數量
-- 02/Nov/2021
-- [06/Nov/2021:03:37:21 +0800]
select t1.dt, count(*) count
from (select substr(split(line, ' ')[3], 2, 11) dt
from t_weblog) t1
where t1.dt = '02/Nov/2021'
group by t1.dt;
-- 上述方式 將所有歷史存量資料直接保存在表中
-- 會導致每次對表的查詢都會加載所有的歷史存量資料
-- 因為底層MR 會將整個表的hdfs路徑作為輸入路徑
-- FileInputFormat.setInputPath(job,new Path("hdfs/.../t_weblog"))
-- 創建Hive表 以日期作為磁區欄位
create external table t_weblog_par1
(
line string
)
partitioned by (dt string);
show tables;
desc t_weblog_par1;
line string
dt string
# Partition Information
# col_name data_type comment
dt string
-- 磁區欄位可以具有與表的列欄位相同功能的查詢和條件陳述句
-- select * from t where 磁區欄位
-- FileInputFormat.setInputPath(
-- job,new Path("hdfs/.../t_weblog/dt=20211102")
-- 指定磁區后，查詢 會直接加載表中的子目錄 避免加載全表資料
create external table t_weblog_par2
(
line string
)
partitioned by (y string, m string, d string);
-- 磁區可以支持按照順序嵌套的多級磁區，通產使用年、月、日 年月、日 作為磁區欄位
-- 如果使用load data將資料加載到表的磁區中
-- 則Hive會自動添加磁區資訊到metastore中
-- 如果使用flume或者手動通過hdfs客戶都創建磁區目錄 并上傳檔案
-- 則表在使用由于確實metastore中的磁區資訊，可能導致磁區資料無法讀取
-- 需要修復磁區資訊
msck repair table t_weblog_par2;
select m from t_weblog_par2
group by m limit 10;表的分桶操作
-- 資料清洗 提取 ip dt code url up down
create table t_weblog_extracted as
select t1.strs[0] ip,
substr(t1.strs[3], 2, 11) dt,
t1.strs[8] code,
substr(t1.strs[5], 2) type,
t1.strs[6] url,
cast(t1.strs[9] as bigint) up,
cast(t1.strs[size(t1.strs) - 1] as bigint) down
from (select split(line, ' ') strs from t_weblog) t1;
show create table t_weblog_extracted;
-- 創建分桶表 按照回應碼 將資料分散到11個桶檔案中
CREATE TABLE `t_weblog_extracted_bkt`
(
`ip` string,
`dt` string,
`code` string,
`type` string,
`url` string,
`up` bigint,
`down` bigint
)
clustered by (code)
into 11 buckets;
-- 將非分桶表的資料 查詢并插入支持分桶的表t_weblog_extracted_bkt
insert into t_weblog_extracted_bkt
select *
from t_weblog_extracted;
-- 底層MR會根據表設定的桶的個數 自動啟動若干個reducer將資料輸出到桶個數個檔案中
-- 一個桶中可以存放多個key的資料
-- 一個key的資料不會給拆分到多個桶中
-- insert into table t2 select x
-- create table t2 as select x
-- 分桶后 按照分桶欄位進行group by查詢時，可以直接通過hash值尋找key所在的桶檔案提高查詢效率
-- 分桶時還可以指定按照哪些欄位提前排序，排序可以增加按照key的資料掃描速度，因為在有序集合中 查找目標元素時，可以順序讀取目標元素所在的檔案段
select code, count(*) count
from t_weblog_extracted
group by code
order by count desc;
desc t_weblog_extracted;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/387822.html
標籤：其他