柚子快報邀請碼778899分享：數(shù)據(jù)倉庫總結(jié)

Mercari二手優(yōu)選綜合2025-05-11530

http://yzkb.51969.com/

??數(shù)據(jù)倉庫概述?

1、數(shù)據(jù)倉庫的定義

數(shù)據(jù)倉庫是一種面向商務智能?(BI)?活動（尤其是分析）的數(shù)據(jù)管理系統(tǒng)，它僅適用于查詢和分析。一個數(shù)據(jù)倉庫通常包含以下要素：1、一個用于存儲和管理數(shù)據(jù)的關系數(shù)據(jù)庫。2、一個用于分析前數(shù)據(jù)準備的提取、加載和轉(zhuǎn)換?(ELT)?解決方案?。3、統(tǒng)計分析、報告和數(shù)據(jù)挖掘功能?。4、用于數(shù)據(jù)可視化和展現(xiàn)的客戶端分析工具

2、數(shù)據(jù)倉庫的優(yōu)勢

面向主題：數(shù)據(jù)倉庫可以高效分析關于特定主題或職能領域（例如銷售）的數(shù)據(jù)。?????? 集成：數(shù)據(jù)倉庫可在不同來源的不同數(shù)據(jù)類型之間建立一致性。???? 相對穩(wěn)定：進入數(shù)據(jù)倉庫后，數(shù)據(jù)將保持穩(wěn)定，不會發(fā)生改變。???? 反映歷史變化：數(shù)據(jù)倉庫分析著眼于反映歷史變化。

3、數(shù)據(jù)倉庫的架構

數(shù)據(jù)倉庫架構特點：簡單、通過暫存區(qū)簡化數(shù)據(jù)準備、星型結(jié)構、沙盒

4、數(shù)據(jù)倉庫的進化

從數(shù)據(jù)分析到?AI?和機器學習。

5、云數(shù)據(jù)倉庫

云數(shù)據(jù)倉庫使用云技術來提取和存儲不同數(shù)據(jù)源的數(shù)據(jù)。云數(shù)據(jù)倉庫優(yōu)勢：1、提供彈性的可擴展支持，可滿足大型或可變計算或存儲需求。2、簡單易用。3、易于管理。4、節(jié)約成本。現(xiàn)代數(shù)據(jù)倉庫包括：融合數(shù)據(jù)庫，可簡化所有數(shù)據(jù)類型的管理并支持多種數(shù)據(jù)使用方法?????自助式數(shù)據(jù)提取和轉(zhuǎn)換服務?????支持?SQL、機器學習、圖形和空間處理?????多種分析選項，可輕松使用數(shù)據(jù)而無需移動數(shù)據(jù)?????自動化流程，可簡化供應、擴展和管理如何設計數(shù)據(jù)倉庫：1、具體的數(shù)據(jù)內(nèi)容。2、各數(shù)據(jù)組內(nèi)部及相互之間的關系 3、支撐數(shù)據(jù)倉庫的系統(tǒng)環(huán)境。4、數(shù)據(jù)轉(zhuǎn)換類型。5、數(shù)據(jù)刷新頻率Hive環(huán)境配置Hive產(chǎn)生背景與框架設計

Hive環(huán)境配置

Hive產(chǎn)生背景與框架設計

一、Hive的基礎

1、Hive產(chǎn)生的背景

為解決使用MapReduce的高門檻，Hive在Facebook誕生。

2、什么是Hive

由Apache軟件基金會維護的開源項目，基于Hadoop的數(shù)據(jù)倉庫工具，用來處理存儲在Hadoop上的海量數(shù)據(jù)，提供一系列功能可以方便進行數(shù)據(jù)ETL（提取、轉(zhuǎn)化、加載）。

Hive使用HQL代替MapReduce，使傳統(tǒng)數(shù)據(jù)庫開發(fā)人員更容易使用Hadoop。

Hive依賴Hadoop的HDFS和YARN。

數(shù)據(jù)倉庫與數(shù)據(jù)庫區(qū)別：數(shù)據(jù)倉庫適合聯(lián)機分析處理（OLAP），數(shù)據(jù)庫適合聯(lián)機事務處理（OLTP）。

3、Hive在Hadoop生態(tài)系統(tǒng)的位置

Apache Hadoop Ecosystem（Apache Hadoop 生態(tài)系統(tǒng)）Ambari（安裝部署組件）zookeeper（分布式協(xié)調(diào)服務，保證各組件間的一致性）0ozie（作業(yè)調(diào)度工具）Hbase（分布式數(shù)據(jù)庫）Mahout（機器學習庫）Hive（數(shù)據(jù)倉庫工具）Pig（數(shù)據(jù)分析工具）Sqoop（數(shù)據(jù)庫ETL工具）Yarn&MapReduce（分布式計算框架）HDFS（分布式文件系統(tǒng)）Flume（日志收集）

4、Hive與傳統(tǒng)關系型數(shù)據(jù)庫

對比項HiveRDBMS查詢語言HQLSQL數(shù)據(jù)存儲HDFS塊設備、本地文件系統(tǒng)執(zhí)行MapReduceExecutor執(zhí)行延遲高低處理數(shù)據(jù)規(guī)模大小事務0.14版本后加入支持索引0.8版本后加入索引復雜

5、Hive的優(yōu)勢

可擴展：Hive可以自由擴展集群規(guī)模；

可延展：Hive支持用戶自定義函數(shù)；

可容錯：Hive良好的容錯性使得節(jié)點出現(xiàn)問題時SQL仍可以完成執(zhí)行。

二、Hive框架設計

1、Hive框架（1）Hive客戶端（Hive?Clients）；（2）Hive服務端（Hive?Services）；（3）Hive存儲與計算（Hive?Storage?and?Computing）。

2、Hive工作流程

?1.Execute?Query：hive界面如命令行或Web?UI將查詢發(fā)送到Driver?(任何數(shù)據(jù)庫驅(qū)動程序如JDBC、ODBC,等等)來執(zhí)行。

2.Get?Plan:Driver根據(jù)查詢編譯器解析query語句,驗證query語句的語法,查詢計劃或者查詢條件。

3.Get?Metadata：編譯器將元數(shù)據(jù)請求發(fā)送給Metastore(任何數(shù)據(jù)庫)。

4.Send?Metadata：Metastore將元數(shù)據(jù)作為響應發(fā)送給編譯器。

5.Send?Plan：編譯器檢查要求和重新發(fā)送Driver的計劃。到這里,查詢的解析和編譯完成。

6.Execute?Plan:Driver將執(zhí)行計劃發(fā)送到執(zhí)行引擎。

7.Execute?Job:hadoop內(nèi)部執(zhí)行的是mapreduce工作過程,任務執(zhí)行引擎發(fā)送一個任務到資源管理節(jié)點(resourcemanager)，資源管理器分配該任務到任務節(jié)點，由任務節(jié)點上開始執(zhí)行mapreduce任務。7.1Metadata?Ops：在執(zhí)行引擎發(fā)送任務的同時,對hive的元數(shù)據(jù)進行相應操作。

8.Fetch?Result：執(zhí)行引擎接收數(shù)據(jù)節(jié)點(data?node)的結(jié)果。

9.Send?Results:執(zhí)行引擎發(fā)送這些合成值到Driver。

10.Send?Results：Driver將結(jié)果發(fā)送到hive接口。

3、Hive場景技術特點

（1）為超大數(shù)據(jù)集設計了計算與擴展功能；

（2）支持SQL?like查詢語言；

（3）支持多表的join操作；

（4）支持非結(jié)構化數(shù)據(jù)的查詢與計算；

（5）提供數(shù)據(jù)存取的編程接口，支持JDBC、ODBC。?

Hive存儲模型和數(shù)據(jù)類型

一、Hive存儲方式與模型

1、Hive存儲格式

（1）TEXTFILE

默認存儲格式，按行存儲，內(nèi)容為普通的文本格式，常見的txt、CSV、tsv等文件，一般使用cat命令直接查看。

（2）SEQUENCEFILE

一種由二進制序列化過的key/value字節(jié)流組成的文本存儲文件格式，僅支持追加，無法直接導入，可分割的文件格式，支持三種壓縮選項：（1）NONE：不壓縮；（2）RECORD（默認選項）：記錄級壓縮，壓縮率低；（3）BLOCK：塊級壓縮，壓縮率高。

（3）RCFILE

專門面向列的數(shù)據(jù)存儲格式，遵循先水平劃分，再垂直劃分。

（4）ORCFILE

對RCFILE的優(yōu)化，特點：支持壓縮比很高的壓縮算法，文件可切分，提供多種索引，支持復雜的數(shù)據(jù)結(jié)構。

2、Hive數(shù)據(jù)單元

（1）數(shù)據(jù)庫

（2）表

可以進行過濾、映射、連接、聯(lián)合操作，分為內(nèi)部表和外部表，內(nèi)部表由Hive管理，外部表的真實數(shù)據(jù)不被Hive管理。

（3）分區(qū)

每個表按指定鍵分為多個分區(qū)，提高查詢效率。

（4）分桶

根據(jù)表中某一列的哈希值可將數(shù)據(jù)劃分為多個分桶。

3、Hive存儲模型

（1）/數(shù)據(jù)倉庫地址/數(shù)據(jù)庫名稱/表名稱/數(shù)據(jù)文件（或分桶數(shù)據(jù)文件）

（2）//數(shù)據(jù)倉庫地址/數(shù)據(jù)庫名稱/表名稱/分區(qū)鍵/數(shù)據(jù)文件（或分桶數(shù)據(jù)文件）

二、Hive基本數(shù)據(jù)類型

1、基本數(shù)據(jù)類型

整數(shù)：TINYINT、SMALLINT、INT、BIGINT

小數(shù)：FLOAT、DOUBLE、DECIMAL

文本：STRING、CHAR、VARCHAR

布爾：BOOLEAN

二進制：BINARY

時間：DATE、TIMESTAMP、INTERVAL

2、復雜數(shù)據(jù)類型

（1）ARRAY和MAP

ARRAY是具有相同類型變量的集合，這些變量稱為數(shù)組的元素。

MAP是一對鍵值對集合

（2）STRUCT

封裝了一組有名字的字段，任意基本類型。

（3）UNIONTYPE

可以保存指定數(shù)據(jù)類型中的任意一種。

數(shù)據(jù)庫表操作

一、Hive DDL 操作

1、hive數(shù)據(jù)庫操作

（1）創(chuàng)建數(shù)據(jù)庫

CREATE (DATABASE|SCHEMA)[IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (propety_name=property_value,......)];

DATABASE|SCHEMA：用于限定創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫模式 IF NOT EXISTS：目標對象不存在時才執(zhí)行創(chuàng)建操作（可選） COMMENT：起注釋說明作用 LOCATION：指定數(shù)據(jù)庫位于HDFS上的存儲路徑。若未指定，將使用${hive.metastore.warehouse.dir}定義值作為其上層路徑位置 WITH DBPROPERTIES：為數(shù)據(jù)庫提供描述信息，如創(chuàng)建database的用戶或時間

（2）修改數(shù)據(jù)庫

ALTER （DATABASE|SCHEMA）database_name SET DBPROPERTIES (property_name=property_value,…);

只能修改數(shù)據(jù)庫的鍵值對屬性值。數(shù)據(jù)庫名和數(shù)據(jù)庫所在的目錄位置不能修改

（3）刪除數(shù)據(jù)庫

DROP (DATABASE|SCHEMA) [IF EXISTS]?database_name [RESTRICT|CASCADE];

DATABASE|SCHEMA：用于限定刪除的數(shù)據(jù)庫或數(shù)據(jù)庫模式 IF EXISTS：目標對象存在時才執(zhí)行刪除操作（可選） RESTRICT|CASCADE：RESTRICT為 Hive 默認操作方式，當database_name中不存在任何數(shù)據(jù)庫對象時才能執(zhí)行DROP操作；CASCADE 采用強制DROP方式，匯聯(lián)通存在于database_name中的任何數(shù)據(jù)庫對象和database_name一起刪除（可選）

2、Hive數(shù)據(jù)表操作

（1）創(chuàng)建表

CREATE [TEMPOPARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] 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] [SKEWED BY (col_name,col_name,…) ON ([(col_value,col_value,…),…|col_value,col_value,…]) [STORED AS DIRECTORIES] ] [ [ROW FORMAT DELIMITED [FIFLDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value,…)] ] 　?[STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (…)] ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value,…)] [AS select_statement];

參數(shù)說明如下：

TEMPOPARY：創(chuàng)建臨時表，若未指定，則默認創(chuàng)建的是普通表

EXTERNAL：創(chuàng)建外部表，若未指定，則默認創(chuàng)建的是內(nèi)部表

IF NOT EXISTS：若表不存在才創(chuàng)建，若未指定，當目標表存在時，創(chuàng)建操作拋出異常 db_name.：前綴，指定表所屬于的數(shù)據(jù)庫。若未指定且當前數(shù)據(jù)庫非db_name，則使用default數(shù)據(jù)庫

COMMENT：添加注釋說明，注釋內(nèi)容位于單引號內(nèi)

PARTITIONED BY：針對存儲有大量數(shù)據(jù)集的表，根據(jù)表內(nèi)容所具有的某些共同特征定義一個標簽，將這類數(shù)據(jù)存儲在該標簽所標識的位置，可以提高表內(nèi)容的查詢速度。PARTITIONED BY中的列名為偽列或標記列，不能與表中的實體列名相同，否則 hive 表創(chuàng)建操作報錯

CLUSTERED BY：根據(jù)列之間的相關性指定列聚類在相同桶中（BUCKETS），可以對表內(nèi)容按某一列進行升序（ASC）或降序（DESC）排序（SORTED BY關鍵字）

SKEWED BY：用于過濾掉特定列col_name中包含值col_value(ON(col_value,…)關鍵字指定的值）的記錄，并單獨存儲在指定目錄（STORED AS DIRECTORIES）下的單獨文件中

ROW FORMAT：指定 hive 表行對象（ROW Object）數(shù)據(jù)與 HDFS 數(shù)據(jù)之間進行傳輸?shù)霓D(zhuǎn)換方式（HDFS files -> Deserializer ->Row object以及Row object ->Serializer ->HDFS files），以及數(shù)據(jù)文件內(nèi)容與表行記錄各列的對應。在創(chuàng)建表時可以指定數(shù)據(jù)列分隔符（FIFLDS TERMINATED BY 子句）、對特殊字符進行轉(zhuǎn)義的特殊字符（ESCAPED BY 子句）、符合數(shù)據(jù)類型值分隔符（COLLECTION ITEMS TERMINATED BY 子句）、MAP key-value 類型分隔符（MAP KEYS TERMINATED BY）、數(shù)據(jù)記錄行分隔符（LINES TERMINATED BY）、定義NULL字符（NULL DEFINED AS），同時可以指定自定義的SerDE（Serializer和Deserializer，序列化和反序列化），也可以指定默認的SerDE。如果ROW FORMAT 未指定或指定為ROW FORMAT DELIMITED，將使用內(nèi)部默認SerDe

STORED AS：指定 hive 表數(shù)據(jù)在 HDFS 上的存儲方式。file_format值包括TEXTFILE（普通文本文件，默認方式）、SEQUENCEFILE（壓縮模式）、ORC（ORC文件格式）和AVRO（AVRO文件格式）

STORED BY：創(chuàng)建一個非本地表，如創(chuàng)建一個 HBase 表

LOCATION：指定表數(shù)據(jù)在 HDFS 上的存儲位置。若未指定，db_name數(shù)據(jù)庫將會儲存在${hive.metastore.warehouse.dir}定義位置的db_name目錄下

TBLPROPERTIES：為所創(chuàng)建的表設置屬性（如創(chuàng)建時間和創(chuàng)建者，默認為當前用戶和當前系統(tǒng)時間） AS select_statement：使用select子句創(chuàng)建一個復制表（包括select子句返回的表模式和表數(shù)據(jù)）

（2）修改表

ALTER TABLE table_name RENAME TO new_table_name;

（3）其他常用命令

二、Hive DML 操作

1、數(shù)據(jù)裝載與插入

load命令不對數(shù)據(jù)進行任何轉(zhuǎn)換，只簡單的將數(shù)據(jù)復制或者移動到Hive表；

insert命令將會執(zhí)行MapReduce作業(yè)并將數(shù)據(jù)插入到Hive表。

（1）load命令

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

Load是將文件復制到Hive對應的存儲路徑下面；

Local 為可選項，當指定Local時，filepath為本地目錄，當不指定Local時，filepath則為hdfs的目錄

OVERWRITE 為可選項，當指定了OVERWRITE時，則表示覆蓋table中的內(nèi)容（清空，插入），如果沒有指定OVERWRITE則在原表數(shù)據(jù)基礎上面進行追加。

PARTITION 指定的是要將數(shù)據(jù)寫入到那個分區(qū)中！

（2）insert命令

1、Insert可以將查詢到的結(jié)果插入到表中

INSERT [OVERWRIT]E TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_table

2、Insert 可以將查到到的結(jié)果導出到文件中

INSERT OVERWRITE [LOCAL] DIRECTORY ‘filepath’ SELECT * FROM tbl;

查詢tbl表數(shù)據(jù)內(nèi)容，導出到本地/HDFS文件中。

LOCAL為可選項，指定LOCAL時表明導出到本地目錄中，不指定LOCAL時，表明導出到HDFS目錄中。

2、數(shù)據(jù)導入、導出

（1）export命令

EXPORT TABLE table_name [PARTITON(partcol=partval,......)] TO 'export_target_path'

（2）import命令

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]] ? FROM 'source_path'? [LOCATION 'import_target_path']

Hive內(nèi)部表和外部表

一、Hive 內(nèi)外部表的定義、區(qū)別以及數(shù)據(jù)管理方式

內(nèi)部表(managed table): 未被external修飾的表，也叫管理表。

外部表(external table):被external修飾的表。

內(nèi)部表&外部表的區(qū)別：

內(nèi)部表：刪除表會刪除表的元數(shù)據(jù)(metadata)和表數(shù)據(jù)（data）。 ?

外部表：刪除表會刪除表的元數(shù)據(jù)(metadata)，但不會刪除表數(shù)據(jù)（data）。

數(shù)據(jù)管理方式：數(shù)據(jù)(data)都存儲在HDFS上，內(nèi)部表由Hive自身管理，外部表數(shù)據(jù)由HDFS管理；元數(shù)據(jù)(metadata)都存儲在mysql上，由mysql管理。

二、Hive內(nèi)部表操作

1、創(chuàng)建內(nèi)部表

create table student1( id int, name string) row format delimited fields terminated by '\t' stored as textfile;

2、查看表格式化數(shù)據(jù)

desc formatted student1;

3、表的修改

alter table table_name add columns(column_name datatype)

4、上傳表數(shù)據(jù)并查詢表數(shù)據(jù)存儲在HDFS的文件

dfs -put /opt/module/data/student.txt /user/hive/warehouse/temp.db/student1；

5、查詢表存儲數(shù)據(jù)（DATA）

select * from ?student1;

6、查詢表的元數(shù)據(jù)（METADATA），元數(shù)據(jù)在mysql

?drop table student1;

三、Hive外部表操作

1、創(chuàng)建外部表

create external table ?student2( id int, name string) row format delimited fields terminated by '\t' stored as textfile location '/tmp/student';

2、查看表格式化數(shù)據(jù)

desc formatted student2;

3、表的修改

alter table table_name add columns(column_name datatype)

4、上傳表數(shù)據(jù)并查詢表數(shù)據(jù)存儲在HDFS的文件

dfs -put /opt/module/data/student.txt /user/hive/warehouse/temp.db/student2；

5、查詢表存儲數(shù)據(jù)（DATA）

select * from ?student2;

6、查詢表的元數(shù)據(jù)（METADATA），元數(shù)據(jù)在mysql

?drop table student2;

四、內(nèi)外部表的相互轉(zhuǎn)換。

1、將外部表轉(zhuǎn)換成內(nèi)部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

2、將內(nèi)部表轉(zhuǎn)化成外部表

alter table student1?set tblproperties('EXTERNAL'='FALSE');

Hive的分區(qū)與分桶

一、分桶表

1、創(chuàng)建一個帶分桶定義的表（分桶表）

--創(chuàng)建分桶表，指定分桶的字段，不指定數(shù)據(jù)的排序規(guī)則

create table if not exists buc1( uid int, uname string, uage int ) clustered by (uid) into 4 buckets row format delimited fields terminated by ',' ;

--創(chuàng)建分桶表，指定分桶的字段，指定數(shù)據(jù)的排序規(guī)則

create table if not exists buc2( uid int, uname string, uage int ) clustered by (uid)? sorted by (uid desc) into 4 buckets row format delimited fields terminated by ',' ;

2、加載數(shù)據(jù)

--第一種方式：直接load一個文檔里面的數(shù)據(jù)到分桶表里面;

load data local inpath '/usr/local/hive/test/3.txt' into table buc1;

--第二種方式：使用insert into(overwrite)方式來加載，前提是先有 buc_temp（只是一般表），而且字段個數(shù)一致，并且buc_temp指定分桶字段

insert overwrite table buc1 select uid,uname,uage from buc_temp cluster by (uid);

--第三種方式：依然使用insert into(overwrite)方式來加載，只不過可以指定數(shù)據(jù)的排序規(guī)則（cluster by（與第二種方式相同）或distribute by () sort by()可以相同字段也可以不同字段，指定asc或desc）

insert overwrite table buc2 select uid,uname,uage from buc_temp cluster by (uid);

insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uid asc);

insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uid desc);

insert overwrite table buc3 select uid,uname,uage from buc_temp distribute by (uid) sort by (uage desc);

3、分桶表的查詢

--1、查詢?nèi)浚?/p>

select * from buc2;

select * from buc2 tablesample(bucket 1 out of 1)?

--查詢第幾桶：

select * from buc3 tablesample(bucket 1 out of 4 on uid); ?//除4余0

select * from buc3 tablesample(bucket 1 out of 2 on uid); ?

二、分區(qū)表?

1. 創(chuàng)建分區(qū)表

CREATE TABLE partitioned_table (id INT, name STRING)

PARTITIONED BY (year INT, month INT);

2. 加載數(shù)據(jù)到分區(qū)表中

LOAD DATA INPATH '/path/to/data' INTO TABLE partitioned_table PARTITION (year=2022, month=10);

3. 查詢分區(qū)表

SELECT * FROM partitioned_table WHERE year=2022 AND month=10;

4. 添加新分區(qū)

ALTER TABLE partitioned_table ADD PARTITION (year=2023, month=1);

5. 刪除分區(qū)

ALTER TABLE partitioned_table DROP PARTITION (year=2022, month=10);

6. 查看分區(qū)信息

SHOW PARTITIONS partitioned_table;

7. 重命名分區(qū)

ALTER TABLE partitioned_table RENAME PARTITION (year=2022, month=10) TO PARTITION (year=2023, month=10);

8. 合并分區(qū)

ALTER TABLE partitioned_table MERGE PARTITIONS (year=2022, month=10), (year=2022, month=11) INTO PARTITION (year=2022, month=12);

9. 修復分區(qū)

MSCK REPAIR TABLE partitioned_table;

Hive 元數(shù)據(jù)概念與表的結(jié)構

一、Hive元數(shù)據(jù)的概念

1、Hive 元數(shù)據(jù)

Hive的元數(shù)據(jù)是關于數(shù)據(jù)的組織、結(jié)構和定義的信息。

Hive元數(shù)據(jù)包括hive庫信息、表信息（表的屬性、表的名稱、表的列、分區(qū)及其屬性）以及表數(shù)據(jù)所在的目錄等。

Hive的元數(shù)據(jù)，默認是存儲在derby中的，但是我們一般會修改其存儲在關系型數(shù)據(jù)庫比如MYSQL中（其可以在hive配置中修改），在關系型數(shù)據(jù)庫中會有一個hive庫，存放相應的表。

2、Hive 元數(shù)據(jù)特點和作用

1. 存儲結(jié)構信息：元數(shù)據(jù)中包含了數(shù)據(jù)庫和表的結(jié)構信息，如數(shù)據(jù)庫名稱、表名稱、列名及其數(shù)據(jù)類型等。 2. 映射關系：它描述了表與物理文件之間的對應關系，以及列和字段之間的對應關系，這樣Hive才能正確地解析和查詢數(shù)據(jù)。 3. 服務功能：元數(shù)據(jù)服務（Metastore）使得上層服務能夠基于結(jié)構化的庫表信息構建計算框架，而不需要直接與裸數(shù)據(jù)文件交互。 4. 數(shù)據(jù)管理：通過元數(shù)據(jù)系統(tǒng)可以管理和維護數(shù)據(jù)，比如描述數(shù)據(jù)倉庫中的數(shù)據(jù)、記錄數(shù)據(jù)抽取的時間安排、檢測系統(tǒng)數(shù)據(jù)的一致性要求等。 5. 發(fā)現(xiàn)和定位：元數(shù)據(jù)還具有幫助用戶發(fā)現(xiàn)和定位數(shù)據(jù)的功能，提供關于數(shù)據(jù)資源的描述信息。 6. 數(shù)據(jù)質(zhì)量衡量：元數(shù)據(jù)能夠幫助衡量數(shù)據(jù)質(zhì)量，確保數(shù)據(jù)的準確性和可靠性。 7. 配置控制：在軟件構建領域，元數(shù)據(jù)可以通過值的改變來改變程序的行為，控制程序的運行方式。

此外，Hive的元數(shù)據(jù)通常存儲在關系型數(shù)據(jù)庫中，如內(nèi)置的Derby或第三方數(shù)據(jù)庫如MySQL等。通過操作這些元數(shù)據(jù)，用戶可以獲取到Hive表的建表語句(DDL)，從而對表結(jié)構進行管理和操作。了解Hive的元數(shù)據(jù)對于理解和使用Hive進行數(shù)據(jù)管理和分析至關重要。

二、Hive數(shù)據(jù)表的結(jié)構

1、version（存儲Hive版本的元數(shù)據(jù)表）

VER_IDSCHEMA_VERSIONVERSION_COMMENTID主鍵Hive版本版本說明10.12.0Set by MetaStore?hadoop@192.168.137.130

2、Hive數(shù)據(jù)庫相關的元數(shù)據(jù)表

主要有DBS和DATABASE_PARAMS，這兩張表通過DB_ID字段關聯(lián)。

（1）DBS：該表存儲Hive中所有數(shù)據(jù)庫的基本信息

表字段說明示例數(shù)據(jù) DB_ID 數(shù)據(jù)庫ID 1 DESC 數(shù)據(jù)庫描述 Default Hive database DB_LOCATION_URI 數(shù)據(jù)HDFS路徑 hdfs://193.168.1.75:9000/test-warehouse NAME 數(shù)據(jù)庫名 default OWNER_NAME 數(shù)據(jù)庫所有者用戶名 public OWNER_TYPE 所有者角色 ROLE

（2）DATABASE_PARAMS：該表存儲數(shù)據(jù)庫的相關參數(shù)，在CREATE DATABASE時候用WITH DBPROPERTIES(property_name=property_value, …)指定的參數(shù)。

表字段說明示例數(shù)據(jù) DB_ID 數(shù)據(jù)庫ID 1 PARAM_KEY 參數(shù)名 createdby PARAM_VALUE 參數(shù)值 root

3、Hive表和視圖相關的元數(shù)據(jù)表?

主要有TBLS、TABLE_PARAMS、TBL_PRIVS，這三張表通過TBL_ID關聯(lián)。

（1）TBLS:該表中存儲Hive表，視圖，索引表的基本信息

表字段說明示例數(shù)據(jù) TBL_ID 表ID 21 CREATE_TIME 創(chuàng)建時間 1447675704 DB_ID 數(shù)據(jù)庫ID 1 LAST_ACCESS_TIME 上次訪問時間 1447675704 OWNER 所有者 root RETENTION 保留字段 0 SD_ID 序列化配置信息 41，對應SDS表中的SD_ID TBL_NAME 表名 ex_detail_ufdr_30streaming TBL_TYPE 表類型 EXTERNAL_TABLE VIEW_EXPANDED_TEXT 視圖的詳細HQL語句 VIEW_ORIGINAL_TEXT 視圖的原始HQL語句

（2）TABLE_PARAMS:該表存儲表/視圖的屬性信息?

表字段說明示例數(shù)據(jù) TBL_ID 表ID 1 PARAM_KEY 屬性名 totalSize，numRows，EXTERNAL PARAM_VALUE 屬性值 970107336、21231028、TRUE

（3）TBL_PRIVS：該表存儲表/視圖的授權信息

表字段說明示例數(shù)據(jù) TBL_GRANT_ID 授權ID 1 CREATE_TIME 授權時間 1436320455 GRANT_OPTION 0 GRANTOR 授權執(zhí)行用戶 root GRANTOR_TYPE 授權者類型 USER PRINCIPAL_NAME 被授權用戶 username PRINCIPAL_TYPE 被授權用戶類型 USER TBL_PRIV 權限 Select、Alter TBL_ID 表ID 21，對應TBLS表的TBL_ID

4、Hive 文件存儲信息相關的元數(shù)據(jù)表

主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表時候也可以指定各種文件格式，Hive在將HQL解析成MapReduce時候，需要知道去哪里，使用哪種格式去讀寫HDFS文件，而這些信息就保存在這幾張表中。

（1）SDS

該表保存文件存儲的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否壓縮等。TBLS表中的SD_ID與該表關聯(lián)，可以獲取Hive表的存儲信息。

表字段說明示例數(shù)據(jù) SD_ID 存儲信息ID 41 CD_ID 字段信息ID 21，對應CDS表 INPUT_FORMAT 文件輸入格式 org.apache.hadoop.mapred.TextInputFormat IS_COMPRESSED 是否壓縮 0 IS_STOREDASSUBDIRECTORIES 是否以子目錄存儲 0 LOCATION HDFS路徑 hdfs://193.168.1.75:9000/detail_ufdr_streaming_test NUM_BUCKETS 分桶數(shù)量 0 OUTPUT_FORMAT 文件輸出格式 org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat SERDE_ID 序列化類ID 41，對應SERDES表

（2）SD_PARAMS

該表存儲Hive存儲的屬性信息，在創(chuàng)建表時候使用STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)指定。

表字段說明示例數(shù)據(jù) SD_ID 存儲配置ID 41 PARAM_KEY 存儲屬性名 PARAM_VALUE 存儲屬性值

（3）SERDES

該表存儲序列化使用的類信息

表字段說明示例數(shù)據(jù) SERDE_ID 序列化類配置ID 41 NAME 序列化類別名 NULL SLIB 序列化類 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

（4）SERDE_PARAMS

該表存儲序列化的一些屬性、格式信息，比如:行、列分隔符

表字段說明示例數(shù)據(jù) SERDE_ID 序列化類配置ID 41 PARAM_KEY 屬性名 field.delim PARAM_VALUE 屬性值 |

5、Hive表字段相關的元數(shù)據(jù)表

主要涉及COLUMNS_V2

COLUMNS_V2：該表存儲表對應的字段信息

表字段說明示例數(shù)據(jù) CD_ID 字段信息ID 21 COMMENT 字段注釋 NULL COLUMN_NAME 字段名 air_port_duration TYPE_NAME 字段類型 bigint INTEGER_IDX 字段順序 119

6、Hive表分分區(qū)相關的元數(shù)據(jù)表

主要涉及PARTITIONS、PARTITION_KEYS、PARTITION_KEY_VALS、PARTITION_PARAMS

（1）PARTITIONS:該表存儲表分區(qū)的基本信息

表字段說明示例數(shù)據(jù) PART_ID 分區(qū)ID 21 CREATE_TIME 分區(qū)創(chuàng)建時間 1450861405 LAST_ACCESS_TIME 最后一次訪問時間 0 PART_NAME 分區(qū)名 hour=15/last_msisdn=0 SD_ID 分區(qū)存儲ID 43 TBL_ID 表ID 22 LINK_TARGET_ID NULL

（2）PARTITION_KEYS:該表存儲分區(qū)的字段信息

表字段說明示例數(shù)據(jù) TBL_ID 表ID 22 PKEY_COMMENT 分區(qū)字段說明 NULL PKEY_NAME 分區(qū)字段名 hour PKEY_TYPE 分區(qū)字段類型 int INTEGER_IDX 分區(qū)字段順序 0

（5）PARTITION_KEY_VALS:該表存儲分區(qū)字段值

表字段說明示例數(shù)據(jù) PART_ID 分區(qū)ID 21 PART_KEY_VAL 分區(qū)字段值 0 INTEGER_IDX 分區(qū)字段值順序 1

（6）PARTITION_PARAMS:該表存儲分區(qū)的屬性信息

表字段說明示例數(shù)據(jù) PART_ID 分區(qū)ID 21 PARAM_KEY 分區(qū)屬性名 numFiles，numRows PARAM_VALUE 分區(qū)屬性值 1，502195

Hive 元數(shù)據(jù)定義與操作存儲?

一、Hive元數(shù)據(jù)定義

Hive的元數(shù)據(jù)指的是描述和存儲有關數(shù)據(jù)和表結(jié)構的信息，包括數(shù)據(jù)庫、表、列、分區(qū)等的定義和屬性。

Hive的元數(shù)據(jù)在數(shù)據(jù)倉庫中扮演著核心角色，它們幫助Hive理解和操作存儲在底層文件系統(tǒng)（如HDFS）中的數(shù)據(jù)。這些元數(shù)據(jù)不僅包括了表和列的名稱、數(shù)據(jù)類型等基本信息，還包含了其他重要的屬性信息，比如：

- 數(shù)據(jù)庫信息：包含數(shù)據(jù)庫定義、名稱、所有者、創(chuàng)建時間等。 - 表的信息：每個表的元數(shù)據(jù)涵蓋了列名、列的數(shù)據(jù)類型、分區(qū)信息、存儲格式、所有者等信息。 - 列的信息：列的元數(shù)據(jù)則包括列名稱、數(shù)據(jù)類型、注釋等細節(jié)。 - 分區(qū)信息：對于分區(qū)表來說，元數(shù)據(jù)還包括分區(qū)列、分區(qū)值、分區(qū)路徑等關鍵信息。

此外，Hive的元數(shù)據(jù)存儲在一個特殊的數(shù)據(jù)庫中，這個數(shù)據(jù)庫可以是Hive內(nèi)置的Derby，或者是外部的關系型數(shù)據(jù)庫如MySQL。元數(shù)據(jù)服務（Metastore）是Hive用來管理這些庫表元數(shù)據(jù)的服務。通過Metastore服務，用戶無需直接訪問物理存儲即可獲取到結(jié)構化的庫表信息，從而構建計算框架。

總的來說，Hive的元數(shù)據(jù)是其能夠有效管理和查詢大規(guī)模數(shù)據(jù)集的關鍵。它不僅是Hive內(nèi)部操作的基礎，也是用戶與Hive交互的重要橋梁。

二、Hive數(shù)據(jù)操作存儲

1、創(chuàng)建數(shù)據(jù)庫、表、視圖?

① 創(chuàng)建數(shù)據(jù)庫

語法格式：

create database [if not exists] <數(shù)據(jù)庫名>;

②創(chuàng)建表

語法格式：

create table [if not exists] <表名> row format delimited fields terminated by '分隔符'? [location? '外部表地址'];

注：如果不指定分隔符，hive表默認分隔符為 ^A (\001) ，這是一種特殊的分隔符，使用的是 ASCII 編碼的值，鍵盤是打不出來的，故一般需要手動添加分隔符。[location? '外部表地址']創(chuàng)建一張外部表，指定地址，如果不指定location地址，則會默認存儲到該數(shù)據(jù)庫中。

③ 創(chuàng)建視圖

語法格式：

create view <視圖名> as select <視圖的列，使用逗號隔開> from <表名>;

?2、刪除數(shù)據(jù)庫、表、視圖

① 刪除數(shù)據(jù)庫

語法格式：

drop database [if exists] <數(shù)據(jù)庫名>

注：如果該數(shù)據(jù)不是一個空的數(shù)據(jù)庫，則需要刪除數(shù)據(jù)庫內(nèi)的內(nèi)容方可刪除，或者強制刪除數(shù)據(jù)庫

強制刪除（慎用）：

drop database <數(shù)據(jù)庫名> cascade;

② 刪除表

語法格式：

drop table [if exists] <表名>

③ 刪除視圖

語法格式：

drop view [if exists] <視圖名>

3、修改數(shù)據(jù)庫、表、視圖

① 修改數(shù)據(jù)庫

語法格式：

alter database <數(shù)據(jù)庫名> ....

hive> alter database student_db set dbprtperties('edited-by'='steven'); # 修改數(shù)據(jù)庫屬性

② 修改表

語法格式：

alter table <表名> ....

hive> alter table student rename to studentinfo; # 將student表名更改為studentinfo（重命名）

hive> alter table studentinfo add columns(address string); # 為studentinfo添加一列address

4、查看數(shù)據(jù)庫、表、視圖

① 查看數(shù)據(jù)庫表

hive> show databases;

hive> show databases like 'h.*' # 查看和h開頭的所有數(shù)據(jù)庫

② 查看表和視圖

hive> show tables; ?# 查看表和視圖

hive> show tables in student_db like 'v.*'; # 查看在student_db中的所有以u開頭的表和視圖

③ 查看數(shù)據(jù)庫、表、視圖的信息

hive> desc database student_db; # 查看數(shù)據(jù)的描述性信息

hive> desc extended student; ? ?# 查看表信息

hive> desc extended stu_external; ? # 查看外部表信息

hive> desc viewstudent; # 查看視圖

5、向表裝載數(shù)據(jù)

語法格式：

load data [local] inpath '數(shù)據(jù)路徑' [overwrite] into table <表名>;

local--從本地虛擬機中獲取數(shù)據(jù)，不過不使用local，則從HDFS中獲取數(shù)據(jù)。

overwrite--覆蓋表的原數(shù)據(jù)，如果不使用overwrite，則會在表末尾追加數(shù)據(jù)。

hive> load data local inpath '/root/userinfo.txt' overwrite into table student; # 將userinfo.txt的數(shù)據(jù)覆蓋到student表中

hive> load data local inpath '/root/userinfo_append.txt' into table student; # 將userinfo_append.txt數(shù)據(jù)追加到student表中

?Hive 數(shù)據(jù)關聯(lián)操作

?一、Hive數(shù)據(jù)關聯(lián)概念

Hive支持多種數(shù)據(jù)關聯(lián)操作，包括內(nèi)連接（INNER JOIN）、左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）和全外連接（FULL OUTER JOIN）。

內(nèi)連接（INNER JOIN）：這是最常見的一種連接方式，只有當兩個表中都存在與連接條件相匹配的數(shù)據(jù)時，這些數(shù)據(jù)才會出現(xiàn)在結(jié)果集中。左外連接（LEFT OUTER JOIN）：這種連接方式會返回左表的所有記錄，即使右表中沒有匹配的記錄，右表中的字段將返回NULL值。右外連接（RIGHT OUTER JOIN）：與左外連接相反，右外連接返回右表的所有記錄，如果沒有匹配的記錄，左表中的字段將返回NULL值。全外連接（FULL OUTER JOIN）：返回兩個表中所有記錄，如果沒有匹配的記錄，對應的字段將返回NULL值。

此外，在Hive中，關聯(lián)操作通常通過JOIN語句實現(xiàn)，且只支持等值連接，不支持非等值連接。在進行關聯(lián)查詢時，可以使用ON子句來指定連接條件。例如，SELECT e.id, e.name, e_a.city, e_a.street FROM employee e INNER JOIN employee_address e_a ON e.id = e_a.id;這個查詢將會返回employee表和employee_address表中id字段相匹配的所有記錄。

總的來說，Hive的數(shù)據(jù)關聯(lián)操作是處理和分析大規(guī)模數(shù)據(jù)集時的常用技術，它允許用戶根據(jù)特定條件將來自不同表的數(shù)據(jù)結(jié)合起來，以便進行更深入的數(shù)據(jù)分析。

二、Hive數(shù)據(jù)關聯(lián)操作

1、內(nèi)聯(lián)接（INNER JOIN）: 返回兩個表中有匹配的記錄。

語法：table_reference [inner] join table_factor [join_condition]

SELECT a.*, b.*

FROM table_a a

INNER JOIN table_b b ON a.common_column = b.common_column;

?2、左外聯(lián)接（LEFT OUTER JOIN）: 返回左表的所有記錄，即使右表中沒有匹配。

語法：table_referrence ?left[outer] join table_factor [join_condition]

SELECT a.*, b.*

FROM table_a a

LEFT OUTER JOIN table_b b ON a.common_column = b.common_column;

3、右外聯(lián)接（RIGHT OUTER JOIN）: 返回右表的所有記錄，即使左表中沒有匹配。

語法：table_reference right [outer] join table_factor [join_condition]

SELECT a.*, b.*

FROM table_a a

RIGHT OUTER JOIN table_b b ON a.common_column = b.common_column;

?4、全外聯(lián)接（FULL OUTER JOIN）: 返回兩表中任意一個表的所有記錄。

語法：table_reference full [outer] ?join table_factor [join_condition]?

SELECT a.*, b.*

FROM table_a a

FULL OUTER JOIN table_b b ON a.common_column = b.common_column;

?Hive 聚合操作

一、Hive聚合方法

Hive提供了一系列的聚合函數(shù)來執(zhí)行數(shù)據(jù)分析和統(tǒng)計計算，這些函數(shù)可以對一組值進行計算并返回單個結(jié)果。常用的聚合函數(shù)包括：

COUNT：用于統(tǒng)計行數(shù)。COUNT(*)返回總行數(shù)，而COUNT(column)返回指定列非空值的行數(shù)。SUM：計算指定列的總和。AVG：返回指定列的平均值。MIN：找出指定列中的最小值。MAX：找出指定列中的最大值。

此外，Hive還支持高級分組聚合，如GROUPING SETS、CUBE和ROLLUP，這些方法可以簡化SQL語句并提升性能。例如，使用GROUPING SETS可以在一個GROUP BY語句中指定多個分組聚合列，這通?？梢杂肬NION連接的多個GROUP BY查詢邏輯來表示。

在實際應用中，聚合函數(shù)通常與GROUP BY子句一起使用，以便對指定字段進行分組統(tǒng)計。在一些場景中，可能需要對分組字段進行不同組合的分組統(tǒng)計，這時就可以用到聚合增強函數(shù)，如GROUPING SETS等。

總的來說，Hive的聚合方法為處理和分析大規(guī)模數(shù)據(jù)集提供了強大的工具，使得用戶能夠方便地進行數(shù)據(jù)統(tǒng)計和分析。

二、Hive聚合操作

1、聚合函數(shù)

（1）sum，max，min，avg。

查詢員工的最大、最小、平均工資及所有工資的和?

hive> select max(salary),min(salary),avg(salary),sum(salary) from emp;

（2）count

查詢記錄數(shù)

hive> select count(*) from emp;

hive> select count(1) from emp;

2、分組函數(shù)（Group by）

（1）按照部門進行分組

hive> select deptno from emp group by deptno;

（2）查詢每個部門的平均工資

hive> select deptno,avg(salary) avg_sal from emp group by deptno;

（3）查詢平均工資大于2000的部門（使用having子句限定分組查詢）

hive> select deptno,avg(salary) from emp group by deptno having avg(salary) > 2000;

（4）按照部門和入職時間進行分組（先按照部門進行分組，然后針對每組按照入職時間進行分組）

hive> select deptno,hiredate from emp group by deptno,hiredate;

（5）按照部門和入職時間進行分組并計算出每組的人數(shù)

hive> select deptno,hiredate,count(ename) from emp group by deptno,hiredate;

3、case when then end

查詢員工的姓名和工資等級，按如下規(guī)則顯示 salary小于等于1000，顯示LOWER salaray大于1000且小于等于2000，顯示MIDDLE salaray大于2000小于等于4000，顯示MIDDLE sal大于4000，顯示highest

select ename, salary,

case

when salary > 1 and salary <= 1000 then 'LOWER'

when salary > 1000 and salary <= 2000 then 'MIDDLE'

when salary > 2000 and salary <= 4000 then 'HIGH'

ELSE 'HIGHEST'

end

from emp;

Hive 窗口函數(shù)

一、Hive窗口函數(shù)介紹

窗口函數(shù)是指，在指定的數(shù)據(jù)滑動窗口中，實現(xiàn)各種統(tǒng)計分析的操作。

在日常的使用中，窗口函數(shù)是與分析函數(shù)一起使用，或按照專用窗口函數(shù)使用，組成比如：窗口聚合函數(shù)、窗口排序函數(shù)等實用函數(shù)。

語法：

分析函數(shù)/專用窗口函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

1 什么是分析函數(shù)和專用窗口函數(shù)？

常用的分析函數(shù)：sum()、max()、min()、avg()、count()、......

專用窗口函數(shù)：row_number()、rank()、dense_rank()......

2 什么是窗口函數(shù)？

over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

窗口函數(shù)的3個組成部分可以單獨使用，也可以混合使用，也可以全都不用。

partition by 字段

對指定的字段進行分組，后續(xù)都會以組為單位，把每個分組單獨作為一個窗口進行統(tǒng)計分析操作。

order by 字段

order by 與 partition by 連用的時候，可以對各個分組內(nèi)的數(shù)據(jù)，按照指定的字段進行排序。如果沒有 partition by 指定分組字段，那么會對全局的數(shù)據(jù)進行排序。

rows between 開始位置 and 結(jié)束位置

rows between 是用來劃分窗口中，函數(shù)發(fā)揮作用的數(shù)據(jù)范圍。我們用如下例子加深 rows between 的理解。

rows between 常用的參數(shù)如下：

① n preceding：往前

② n following：往后

③ current row：當前行

④ unbounded：起點/終點（一般結(jié)合preceding，following使用）

rows between unbounded preceding and current row（表示從起點到當前行的數(shù)據(jù)進行）

rows between current row and unbounded following（表示當前行到終點的數(shù)據(jù)進行）

rows between unbounded preceding and unbounded following （表示起點到終點的數(shù)據(jù)）

rows between 1 preceding and 1 following（表示往前1行到往后1行的數(shù)據(jù)）

rows between 1 preceding and current row（表示往前1行到當前行）

二、Hive 窗口函數(shù)使用

?1、排序窗口函數(shù)

（1）排序并產(chǎn)生自增編號，自增編號不重復且連續(xù)

我們可以使用函數(shù)：row_number() over()

語法：

row_number() over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

?（2）排序并產(chǎn)生自增編號，自增編號會重復且不連續(xù)

我們可以使用函數(shù)：rank() over()

語法：

rank() over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

（3）排序并產(chǎn)生自增編號，自增編號會重復且連續(xù)

我們可以使用函數(shù)：dense_rank() over()

語法：

?dense_rank() over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

2、聚合窗口函數(shù)

（1）?求窗口中的累計值

我們可以使用：sum() over()

（2）求窗口中的平均價格

我們可以使用 avg() over()

（3）求分組中的最大值/最小值

我們可以使用 min() max()

（4）求分組中的總記錄數(shù)

我們可以使用 count()

3、位移窗口函數(shù)

（1）獲取分組中往前 n 行的值

基礎語法：

lead(field,n,default_value) over()

語法解析：

1. field 是指定的列名

2. n 是往前的行數(shù)

3. 行往前導致的，最后的 n 行值為 null，可以用 default_value 代替。

（2）獲取分組中往后 n 行的值

基礎語法：

lag(field,n, default_value) over()

語法解析：

1. field 是指定的列名

2. n 是往前的行數(shù)

3. 行往后導致的，前面的 n 行值為 null，可以用 default_value 代替。

4、極值窗口函數(shù)

（1）獲取分組內(nèi)第一行的值

我們可以使用?first_value(col,true/false) over()，作用是：取分組內(nèi)排序后，截止到當前行，第一個值。

?注意：

當?shù)诙€參數(shù)為 true 的時候，會跳過空值當 over() 中不指定排序的時候，會默認使用表中數(shù)據(jù)的原排序。

（2）獲取分組內(nèi)最后一行的值

我們可以使用?last_value(col,true/false) over()，作用是：取分組內(nèi)排序后，截止到當前行，最后一個值。所以，如果使用 order by 排序的時候，想要取最后一個值，需要與 rows between unbounded preceding and unbounded following 連用。

注意：

當?shù)诙€參數(shù)為 true 的時候，會跳過空值當 over() 中不指定排序的時候，會默認使用表中數(shù)據(jù)的原排序。當 over() 中指定排序的時候，要與 rows between unbounded preceding and unbounded following 連用

三、（練習）訂單任務

任務1-訂單關聯(lián)查詢并保存

create table order_details as

select order_items.order_item_order_id,

orders.order_date,

customers.customer_name,

customers.customer_city,

products.product_name,

categories.category_name,

order_items.order_item_quantity,

order_items.order_item_product_price

from

orders join customers on orders.order_customer_id=customers.customer_id

join order_items on orders.order_id=order_items.order_item_order_id

join products on order_items.order_item_product_id=products.product_id

join categories on products.product_category_id=categories.category_id

limit 10;

任務2-零售商品業(yè)務數(shù)據(jù)查詢

select a.id,sum(a.subtotal) total

from

(select orders.order_customer_id id,order_items.order_item_subtotal subtotal

from

orders join customers on orders.order_customer_id=customers.customer_id

join order_items on orders.order_id=order_items.order_item_order_id) a

group by a.id order by total desc

limit 10;

任務3-使用窗口函數(shù)查詢

#統(tǒng)計每日訂單量

select substring(orders.order_date,0,10) daystr,

count(order_id) over(partition by substring(orders.order_date,0,10))as row_count

from orders order by row_count desc limit 10;

#統(tǒng)計每日銷售額排行

select b.* from (

select a.daystr days,sum(a.subtotal) total,

RoW_NUMBER() over(partition by a.daystr order by sum(a.subtotal) desc) as rn

from

(select substring(orders.order_date,0,10) daystr,

order_items.order_item_subtotal subtotal

from orders join order_items on orders.order_id=order_items.order_item_order_id) a

group by a.daystr) b

where b.rn<=10;

Hive 內(nèi)置函數(shù)

一、Hive內(nèi)置函數(shù)概念

Hive是一個構建在Hadoop之上的數(shù)據(jù)倉庫，它提供了類似SQL的查詢語言HiveQL，讓不熟悉MapReduce開發(fā)者也能編寫數(shù)據(jù)查詢語句。Hive的一個主要優(yōu)勢是它提供了一系列的內(nèi)置函數(shù)（或稱為內(nèi)置操作符），這些函數(shù)可以直接在HiveQL查詢中使用，以完成數(shù)據(jù)的處理和分析。

Hive內(nèi)置函數(shù)主要用于集合函數(shù)、數(shù)學函數(shù)、日期函數(shù)、字符串函數(shù)和條件判斷函數(shù)等方面。例如：

條件判斷函數(shù)：IF、WHEN、CASE、COALESCE等。

字符串函數(shù)：LENGTH、SUBSTR、CONCAT、TRIM、LOWER、UPPER等；

集合函數(shù)：SUM、MAX、MIN、AVG、COUNT等；

數(shù)學函數(shù)：ROUND、EXP、LOG、SIGN等；

日期函數(shù)：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等；

二、Hive內(nèi)置函數(shù)使用

1、數(shù)學函數(shù): 提供了一些常用的數(shù)學運算，例如sqrt（計算平方根），rand（生成隨機數(shù)）。

select rand(); ? ? ?-- 0.0 ~ 1.0, 包左不包右. select round(4.3); ?-- 四舍五入, 原理: +0.5, 然后求地板數(shù). ? select abs(-10); ? ?-- 求絕對值 select ceil(5.3); ? -- 天花板數(shù), 比該數(shù)大的所有數(shù)字中, 最小的那個整數(shù)(包括本身) ? ? ?6 select ceil(5.0); ? -- 天花板數(shù), 比該數(shù)大的所有數(shù)字中, 最小的那個整數(shù)(包括本身) ? ? ?5 select floor(5.6); ?-- 地板數(shù), 比該數(shù)小的所有數(shù)字中, 最大的那個整數(shù)(包括本身) ? ? ? ?5

2、字符串函數(shù): 提供了一些用于字符串操作的函數(shù)。

1. 字符串切割

-- spilt(參數(shù)1,參數(shù)2) ? ? 參數(shù)1: 要操作的參數(shù). ?參數(shù)2: 切割符 select spilt("aa,bb,cc",","); ? ? ? ?-- ["aa","bb","cc"] ? -- substr(參數(shù)1,參數(shù)2,參數(shù)3) 和 substring(參數(shù)1,參數(shù)2,參數(shù)3)? -- 參1: 要操作的字符串. ?參2: 起始索引, 參數(shù)3: 個數(shù) -- 效果一樣, 沒有區(qū)別 select substr('2024-01-13 11:51:27', 1, 10); select substring('2024-01-13 11:51:27', 1, 10);

?2. 字符串拼接

-- concat() 拼接符默認為空 select concat('aa','bb','cc'); ? aabbcc ? -- concat_ws(參數(shù)1,參數(shù)2) 可以指定拼接符 ?參數(shù)1:拼接符 ? 參數(shù)2:要拼接的內(nèi)容 select concat_ws('-','aa','bb','cc'); ? aa-bb-cc

3. 獲取字符串長度

select length('abc');

?4. 轉(zhuǎn)大小寫

select lower('ABC'); -- abc 轉(zhuǎn)小寫

select upper('abc'); -- ABC 轉(zhuǎn)大寫

5. 移除首尾空格

select trim(' ? ?aa ? bb ? ?'); ? ? -- 'aa ? bb'

6. 正則替換, 參數(shù)1:要被處理的字符串. 參數(shù)2:正則表達式. 參數(shù)3:用來替換的內(nèi)容

?select regexp_replace('100-200', '\\d+', '夯哥');

3、日期函數(shù): 提供了一些用于日期操作的函數(shù)，例如year（獲取年份），month（獲取月份）。

select current_date(); ? ? ?-- 獲取當前時間, 年月日格式 ? select unix_timestamp(); ? ?-- 獲取當前時間, unix格式, 即: 從時間原點(1970-01-01 00:00:00)截止到當前時間的秒值. ? select unix_timestamp('2024/01/12', 'YYYY/mm/dd'); ?-- 根據(jù)指點的時間, 獲取其unix時間. ? -- 根據(jù)秒值(unix時間) 獲取其對應的日期. select from_unixtime(1684477644); ? ? ? -- 2023-05-19 06:27:24 ? -- 獲取年月日. select year('2023-05-19 14:31:02'); ? ? -- 2023 select month('2023-05-19 14:31:02'); ? ? -- 5 select day('2023-05-19 14:31:02'); ? ? -- 19 ? -- 時間相關操作, 增加, 減少, 比較. select date_add('2023-05-19', 2); ? ? ? -- 2023-05-21 select date_add('2023-05-19', -2); ? ? ? -- 2023-05-17 select datediff('2023-05-19', '2023-05-20'); ? ?-- 前 - 后, ?-1 ?

?4、聚合函數(shù): 提供了一些聚合操作，例如sum（求和），count（計數(shù)）。

SELECT sum(salary) FROM employees; SELECT count(DISTINCT id) FROM employees;?

5、條件函數(shù): 提供了類似于if-else的條件判斷功能，例如CASE WHEN THEN ELSE END。

SELECT CASE WHEN age < 20 THEN 'youth'? ? ? ? ? ? ? WHEN age >= 20 AND age < 40 THEN 'middle age'? ? ? ? ? ? ? ELSE 'elderly'? ? ? ? ?END? FROM people;

?6、轉(zhuǎn)換函數(shù): 提供了類型轉(zhuǎn)換的功能，例如cast

?SELECT cast(salary AS string) FROM employees;

Hive視圖概念與常用操作?

1、Hive視圖概念

?Hive 中的視圖（View）是一個虛擬表，它基于一個或多個表的查詢結(jié)果。視圖不存儲數(shù)據(jù)，它只存儲查詢語句的定義。當你查詢視圖時，Hive 會根據(jù)視圖的定義執(zhí)行查詢，并返回結(jié)果。視圖可以簡化復雜的查詢，提供數(shù)據(jù)的安全性（因為用戶只能看到視圖定義中的數(shù)據(jù)），以及提供數(shù)據(jù)的邏輯抽象。

虛擬表：視圖是一個虛擬表，其內(nèi)容由查詢結(jié)果定義，不占用存儲空間。數(shù)據(jù)封裝：視圖可以簡化復雜查詢，將它們封裝成簡單的表結(jié)構，便于用戶使用。動態(tài)數(shù)據(jù)：視圖在每次查詢時都會執(zhí)行定義它的查詢語句，以獲取最新的數(shù)據(jù)。數(shù)據(jù)安全：視圖可以限制用戶訪問數(shù)據(jù)的一部分，提供額外的數(shù)據(jù)安全層。

2、Hive常用操作?

1.創(chuàng)建視圖

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

通過上述語句，可以創(chuàng)建一個名為view_name的視圖，其內(nèi)容是基于table_name表的一個查詢結(jié)果。?

2.查詢視圖：?與查詢普通表一樣，使用SELECT語句查詢視圖

SELECT * FROM view_name;

3.修改視圖：?Hive 不直接支持視圖的修改，但是可以通過DROP和CREATE操作間接實現(xiàn)

DROP VIEW view_name; CREATE VIEW view_name AS -- 新的查詢語句

4.刪除視圖：?使用DROP VIEW語句可以刪除視圖

DROP VIEW view_name;

5.查看視圖結(jié)構：?使用DESCRIBE語句可以查看視圖的結(jié)構

DESCRIBE view_name;