https://nino-laiqiu.github.io/2020/11/14/Hive-principle-reinforcement/#more

Apache-Hive實(shí)踐

數(shù)據(jù)類型

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

集合數(shù)據(jù)類型

• STRUCT:例如: address struct<street:string, city:string> 可以通過address.street 來訪問

• ARRAY:例如:friends array<string> 可以通過 friend[index] 來訪問

• MAP:例如:children map<string, int>, 可以通過children[key]來訪問

類型的轉(zhuǎn)換

• INT不會自動轉(zhuǎn)換為TINYINT類型垦梆，它會返回錯(cuò)誤匹颤，除非使用CAST操作

• 任何整數(shù)類型都可以隱式地轉(zhuǎn)換為一個(gè)范圍更廣的類型，如TINYINT可以轉(zhuǎn)換成INT托猩，INT可以轉(zhuǎn)換成BIGINT印蓖。

• 所有整數(shù)類型、FLOAT和STRING類型都可以隱式地轉(zhuǎn)換成DOUBLE京腥。

• TINYINT赦肃、SMALLINT、INT都可以轉(zhuǎn)換為FLOAT。

• BOOLEAN類型不可以轉(zhuǎn)換為任何其它的類型

//表達(dá)結(jié)構(gòu)
{
    "MobilePlatform":"apple",
    "APP":["jianshu","csdn"],
      "name":{
              "xiaoming":19,
              "xioahuang":17
         },
     "address":{
                "street":"1000A",
                "city":"zhongguo"
             }
}

#建表語句
create table test(
    MobilePlatform string,
    APP array<string>,
    name map<string,int>,
    address  struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
#語句的解釋
1.行列分隔符
2.MAP STRUCT 和 ARRAY 的分隔符(數(shù)據(jù)分割符號)
3.MAP中的key與value的分隔符
4.行分隔符

txt數(shù)據(jù)與導(dǎo)入hive的語句
apple,jianshu_weibo,xiaoming:19_xiaohua:17,1000A_zhongguo
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing

load data local inpath "/root/test.txt" into table test

#查詢語句和結(jié)果

select * from test;

apple   ["jianshu","weibo"] {"xiaoming":19,"xiaohua":17}    {"street":"1000A","city":"zhongguo"}
songsong    ["bingbing","lili"] {"xiao song":18,"xiaoxiao song":19} {"street":"hui long guan","city":"beijing"}

select APP[1],name["xiaoming"], address.city from  test;

weibo   19      zhongguo
lili    null    beijing

DDL 數(shù)據(jù)定義語言詳解

庫

1. 數(shù)據(jù)庫創(chuàng)建的標(biāo)準(zhǔn)語法

create database if not exists  db_demo1; 
#創(chuàng)建一個(gè)數(shù)據(jù)庫指定在HDFS位置
create database if not exists  db_demo1  "/path";

1. 查詢數(shù)據(jù)庫的語法

#1.顯示 支持模糊查詢
show databases;
show databases like "db_*"

#2.查詢 顯示數(shù)據(jù)庫詳細(xì)信息 extended 
desc database db_demo1;
desc database extended db_demo1;

#3.切換
use db_demo1

3. 修改數(shù)據(jù)庫的語法與注意事項(xiàng)

   用戶可以使用ALTER DATABASE命令為某個(gè)數(shù)據(jù)庫的DBPROPERTIES設(shè)置鍵-值對屬性值他宛，來描述這個(gè)數(shù)據(jù)庫的屬性信息船侧。數(shù)據(jù)庫的其他元數(shù)據(jù)信息都是不可更改的，包括數(shù)據(jù)庫名和數(shù)據(jù)庫所在的目錄位置堕汞。

 alter database db_demo1 set dbproperties('createtime'='20201114');

1. 刪除數(shù)據(jù)庫的標(biāo)準(zhǔn)語法

#刪除
drop database if exists db_demo1 ;
#強(qiáng)制刪除
drop database db_demo1  cascade;

表

1. 建表語法的描述

   CREATE TABLE:建表

   EXTERNA:外部表

   COMMENT:為表和列添加注釋

   PARTITIONED BY:分區(qū)

   CLUSTERED BY:分桶

   SORTED BY:

   ROW FORMAT:在建表的時(shí)候可以自定義SerDe或者使用自帶的SerDe

   STORED AS :設(shè)置存儲的類型

   LOCATION :指定表在HDFS上的存儲位置

   LIKE:復(fù)制現(xiàn)有表的結(jié)構(gòu),但不復(fù)制內(nèi)容

#普通建表

#查詢建表(查詢結(jié)果添加到新表中)
create table if not exists student1 as select id, name from student;
#根據(jù)已經(jīng)存在的表結(jié)構(gòu)建表
create table if not exists student2 like student;
#查詢類型
desc formatted student2;

2. 內(nèi)部表和外部表

• 默認(rèn)創(chuàng)建的表都是所謂的管理表勺爱，有時(shí)也被稱為內(nèi)部表,當(dāng)我們刪除一個(gè)管理表時(shí)，Hive也會刪除這個(gè)表中數(shù)據(jù)讯检。管理表不適合和其他工具共享數(shù)據(jù)

• 表是外部表琐鲁，所以Hive并非認(rèn)為其完全擁有這份數(shù)據(jù)。刪除該表并不會刪除掉這份數(shù)據(jù)人灼，不過描述表的元數(shù)據(jù)信息會被刪除掉

• 刪除內(nèi)部表會直接刪除元數(shù)據(jù)（metadata）及存儲數(shù)據(jù)围段；刪除外部表僅僅會刪除元數(shù)據(jù)，HDFS上的文件并不會被刪除投放；

• 對內(nèi)部表的修改會將修改直接同步給元數(shù)據(jù)奈泪，而對外部表的表結(jié)構(gòu)和分區(qū)進(jìn)行修改，則需要修復(fù)（MSCK REPAIR TABLE
  table_name;）

• 適用的場合:每天將收集到的網(wǎng)站日志定期流入HDFS文本文件灸芳。在外部表（原始日志表）的基礎(chǔ)上做大量的統(tǒng)計(jì)分析涝桅，用到的中間表、結(jié)果表使用內(nèi)部表存儲烙样，數(shù)據(jù)通過SELECT+INSERT進(jìn)入內(nèi)部表冯遂。

#內(nèi)部表和外部表的轉(zhuǎn)換
#把內(nèi)部表轉(zhuǎn)換為外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
##把外部表轉(zhuǎn)換為內(nèi)部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
##注意 'EXTERNAL'='TRUE'  'EXTERNAL'='FALSE' 是固定寫法區(qū)分大小寫

3. 分區(qū)表

   • 概念

     • 分區(qū)表實(shí)際上就是對應(yīng)一個(gè)HDFS文件系統(tǒng)上的獨(dú)立的文件夾，該文件夾下是該分區(qū)所有的數(shù)據(jù)文件谒获。Hive中的分區(qū)就是分目錄蛤肌，把一個(gè)大的數(shù)據(jù)集根據(jù)業(yè)務(wù)需要分割成小的數(shù)據(jù)集。在查詢時(shí)通過WHERE子句中的表達(dá)式選擇查詢所需要的指定的分區(qū)批狱，這樣的查詢效率會提高很多裸准。

     • 查詢時(shí)Hive自動過濾掉不用于提高性能的分區(qū)

     • 分為靜態(tài)分區(qū)和動態(tài)分區(qū)

   • 操作 (一級分區(qū)、二級分區(qū)赔硫、)

#沒有分區(qū)
create table  tb_order2(
 oid int ,
dt string ,
cost double
)
row format delimited fields terminated by "," ;

load data local inpath "/root/6.18.txt" into table  tb_order2 ;
load data local inpath "/root/6.19.txt" into table  tb_order2 ;

#查詢
select * from tb_order2 where dt='2020-06-18'

#一級分區(qū)
create table  tb_p_order(
oid int ,
dt string ,
cost double
)
partitioned  by (dy string)
row format delimited fields terminated by "," ;

load data local inpath "/root/6.18.txt" into table  tb_p_order  partition(dy="06-18");
load data local inpath "/root/6.19.txt" into table  tb_p_order  partition(dy="06-19");

#查詢
select * from tb_p_order where  dy="06-18";

#刪除分區(qū)
alter table tb_p_order drop  partition (dy = "06-18")

#二級分區(qū)
create table tb_partition2(
id int ,
name string ,
gender string ,
birthday string
)
partitioned  by (y string , m string)
row format delimited fields terminated by "," ;

load data local  inpath "/root/a.txt"  into  table tb_partition2 partition(y='90',m='01');
load data local  inpath "/root/b.txt"  into  table tb_partition2 partition(y='90',m='02');
load data local  inpath "/root/c.txt"  into  table tb_partition2 partition(y='95',m='02');
load data local  inpath "/root/d.txt"  into  table tb_partition2 partition(y='95',m='03');

#查詢
select * from tb_partition2  where y='90' and m='01';

4. 修改表和刪除表

#重命名
ALTER TABLE table_name RENAME TO new_table_name
#刪除表
drop table dept_partition;

DML 數(shù)據(jù)操縱語言詳解

數(shù)據(jù)導(dǎo)入

#向表中裝載數(shù)據(jù)

#語法
hive> load data [local] inpath '/opt/module/datas/student.txt' overwrite  into table student [partition (partcol1=val1,…)];
#字段的解釋

load data:表示加載數(shù)據(jù)
local:表示從本地加載數(shù)據(jù)到hive表炒俱；否則從HDFS加載數(shù)據(jù)到hive表
inpath:表示加載數(shù)據(jù)的路徑
overwrite:表示覆蓋表中已有數(shù)據(jù)，否則表示追加
into table:表示加載到哪張表
student:表示具體的表
partition:表示上傳到指定分區(qū)

#通過查詢語句向表中插入數(shù)據(jù)
insert overwrite table student partition(month='201708') select id, name from student where month='201709';

#查詢語句中創(chuàng)建表并加載數(shù)據(jù)
create table if not exists student3 as select id, name from student;

數(shù)據(jù)導(dǎo)出

#查詢結(jié)果導(dǎo)入到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;

#查詢結(jié)果格式化導(dǎo)入本地
insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

#查詢結(jié)果導(dǎo)入到HDFS
insert overwrite  directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

#Truncate只能刪除管理表爪膊，不能刪除外部表中數(shù)據(jù)
truncate table student;

DQL 數(shù)據(jù)查詢語言詳解

基本查詢

• 常用函數(shù)

• 算術(shù)運(yùn)算符

• Limit

• 空值的處理

# count( ) count(*) 區(qū)別
select count( ) from Company --包括空值
select count(*) from Company --不包括空值

# 只有count不忽略空值,例如avg函數(shù)把空值設(shè)置為0
 avg(IsNull(score向胡，0)） as ’Average Score‘

where語句

• between and

• in is null exist

• like rlike

  • % 代表零個(gè)或多個(gè)字符(任意個(gè)字符)。
  • _ 代表一個(gè)字符惊完。
  • rlike支持正則表達(dá)式

• 邏輯運(yùn)算符

分組查詢

• group by

• having

join連接

• Hive支持通常的SQL JOIN語句僵芹，但是只支持等值連接，不支持非等值連接

• join連接謂詞中不支持or

基礎(chǔ)SQL案例

#SQL互聯(lián)網(wǎng)50題hive實(shí)現(xiàn)

排序語法

• 全局排序(order by )

  1. Order By：全局排序小槐，一個(gè)Reducer

  2. ASC（ascend）: 升序（默認(rèn)）

  3. DESC（descend）: 降序

• 每個(gè)MapReduce內(nèi)部排序（Sort By）

  1. set mapreduce.job.reduces=3;(設(shè)置reduce數(shù)目)

  2. set mapreduce.job.reduces;(查看reduce數(shù)目)

• 分區(qū)排序（Distribute By）

  1. 類似MR中partition拇派，進(jìn)行分區(qū)荷辕，結(jié)合sort by使用

  2. Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前

  3. 要分配多reduce進(jìn)行處理，否則無法看到distribute by的效果件豌。

• Cluster By

  1. 當(dāng)distribute by和sorts by字段相同時(shí)疮方，可以使用cluster by方式。

  2. cluster by除了具有distribute by的功能外還兼具sort by的功能茧彤。但是排序只能是升序排序骡显，不能指定排序規(guī)則為ASC或者DESC。

分桶與抽樣查詢

• 概念:

  1. 分區(qū)針對的是數(shù)據(jù)的存儲路徑曾掂；分桶針對的是數(shù)據(jù)文件,

  2. 分區(qū)提供一個(gè)隔離數(shù)據(jù)和優(yōu)化查詢的便利方式惫谤。不過，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)珠洗，特別是之前所提到過的要確定合適的劃分大小這個(gè)疑慮

  3. 分桶對數(shù)據(jù)的處理比分區(qū)更加細(xì)粒度化溜歪；

  4. 分桶和分區(qū)兩者不干擾，可以把分區(qū)表進(jìn)一步分桶许蓖；

create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

set hive.enforce.bucketing = true;

load data local inpath '/root/e.txt' into table
stu_buck;

• 分桶抽樣查詢

#示例
select * from stu_buck tablesample(bucket 1 out of 4 on id);

#含義的說明

tablesample是抽樣語句蝴猪，語法：TABLESAMPLE(BUCKET x OUT OF y) 
y必須是table總bucket數(shù)的倍數(shù)或者因子,例如:例如，table總共分了4份膊爪，當(dāng)y=2時(shí)自阱，抽取(4/2=)2個(gè)bucket的數(shù)據(jù)，當(dāng)y=8時(shí)米酬，抽取(4/8=)1/2個(gè)bucket的數(shù)據(jù)沛豌。
x表示從哪個(gè)bucket開始抽取，如果需要取多個(gè)分區(qū)淮逻，以后的分區(qū)號為當(dāng)前分區(qū)號加上y,例如:table總bucket數(shù)為4琼懊，tablesample(bucket 1 out of 2)阁簸，表示總共抽扰涝纭（4/2=）2個(gè)bucket的數(shù)據(jù)，抽取第1(x)個(gè)和第3(x y)個(gè)bucket的數(shù)據(jù)启妹。

動態(tài)分區(qū)和靜態(tài)分區(qū)詳解

首先是要創(chuàng)建靜態(tài)分區(qū)表筛严；然后將表設(shè)置為非嚴(yán)格模式；再次創(chuàng)建動態(tài)分區(qū)表饶米，并加載數(shù)據(jù)桨啃。

加載數(shù)據(jù)的時(shí)候，是按照靜態(tài)分區(qū)的模式檬输，將數(shù)據(jù)加載到動態(tài)分區(qū)中去照瘾。

注意事項(xiàng)要開啟yarn不然報(bào)錯(cuò):org.apache.hadoop.hive.ql.exec.mr.MapRedTask

#案例
#數(shù)據(jù)user.txt
u001 ZSS 23 M beijing
u002 YMM 33 F nanjing
u003 LSS 43 M beijing
u004 ZY 23 F beijing
u005 ZM 23 M beijing
u006 CL 23 M dongjing
u007 LX 23 F beijing
u008 YZ 23 M beijing
u009 YM 23 F nanjing
u010 XM 23 M beijing
u011 XD 23 F beijing
u012 LH 23 M dongjing

#建普通表語句
create  table  if not exists  tb_user(
uid string ,
name  string ,
age int ,
gender string ,
address string
)
row format delimited fields  terminated by  " " ;

##加載數(shù)據(jù)
load data local  inpath  "/root/user.txt"  into table  tb_user ;

##創(chuàng)建目標(biāo)表
create  table  if not exists  tb_p_user(
uid string ,
name  string ,
age int ,
gender string ,
address string
)
partitioned  by (addr string)
row format delimited fields  terminated by  " " ;

#開啟動態(tài)分區(qū)設(shè)置
set hive.exec.dynamic.partition=true ;
set hive.exec.dynamic.partition.mode= nonstrict;  可以從普通表中導(dǎo)入數(shù)據(jù)

#插入數(shù)據(jù)(這里是全部插入,也可以插入普通表的部分字段)
普通表5個(gè)字段
分區(qū)表 5個(gè)主字段 1 個(gè)分區(qū)字段
插入數(shù)據(jù)的時(shí)候字段個(gè)數(shù)類型一致  最后一個(gè)字段就是分區(qū)字段 
insert into tb_p_user partition(addr) select uid , name , age , gender , address,address  from  tb_user ;

窗口函數(shù)總結(jié)

窗口函數(shù)

#窗口函數(shù)的語法

over關(guān)鍵字用來指定函數(shù)執(zhí)行的窗口范圍，若后面括號中什么都不寫丧慈，則意味著窗口包含滿足WHERE條件的所有行析命，窗口函數(shù)基于所有行進(jìn)行計(jì)算主卫；如果不為空，則支持以下4中語法來設(shè)置窗口鹃愤。
①window_name：給窗口指定一個(gè)別名簇搅。如果SQL中涉及的窗口較多，采用別名可以看起來更清晰易讀软吐；
②PARTITION BY 子句：窗口按照哪些字段進(jìn)行分組瘩将，窗口函數(shù)在不同的分組上分別執(zhí)行；
③ORDER BY子句：按照哪些字段進(jìn)行排序凹耙，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號姿现；
④FRAME子句：FRAME是當(dāng)前分區(qū)的一個(gè)子集，子句用來定義子集的規(guī)則使兔，通常用來作為滑動窗口使用

1. 偏移量函數(shù):lag lead

? lag(列名,往前的行數(shù),[行數(shù)為null時(shí)的默認(rèn)值建钥，不指定為null])，可以計(jì)算用戶上次購買時(shí)間虐沥，或者用戶下次購買時(shí)間熊经。

? lead(列名,往后的行數(shù),[行數(shù)為null時(shí)的默認(rèn)值，不指定為null])

2. first_value 和 last_value

? first_value取分組內(nèi)排序后欲险，截止到當(dāng)前行镐依，第一個(gè)值
? last_value取分組內(nèi)排序后，截止到當(dāng)前行天试，最后一個(gè)值

#sql
select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from order
#展示
name    orderdate   cost    time1   time2
jack    2015-01-01  10  2015-01-01  2015-01-01
jack    2015-01-05  46  2015-01-01  2015-01-05
jack    2015-01-08  55  2015-01-01  2015-01-08
jack    2015-02-03  23  2015-01-01  2015-02-03
jack    2015-04-06  42  2015-01-01  2015-04-06
mart    2015-04-08  62  2015-04-08  2015-04-08
mart    2015-04-09  68  2015-04-08  2015-04-09
mart    2015-04-11  75  2015-04-08  2015-04-11
mart    2015-04-13  94  2015-04-08  2015-04-13
neil    2015-05-10  12  2015-05-10  2015-05-10
neil    2015-06-12  80  2015-05-10  2015-06-12
tony    2015-01-02  15  2015-01-02  2015-01-02
tony    2015-01-04  29  2015-01-02  2015-01-04
tony    2015-01-07  50  2015-01-02  2015-01-07

序列函數(shù)

• rank():1,2,2,2,5,6

• dense_rank():1,2,2,3,4,4,5

• row_number():1,2,3,4,5,6

• ntile(n):用于將分組數(shù)據(jù)按照順序切分成n片槐壳，返回當(dāng)前切片值

#ntile(n) 支持over()

控制窗口大小的使用

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：當(dāng)前行
UNBOUNDED：起點(diǎn)，UNBOUNDED PRECEDING 表示從前面的起點(diǎn)喜每， UNBOUNDED FOLLOWING：表示到后面的終點(diǎn)

#例如
select name,orderdate,cost,
sum(cost) over() as fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分組务唐，組內(nèi)數(shù)據(jù)相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分組，組內(nèi)數(shù)據(jù)累加 
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1   --和fabno一樣,由最前面的起點(diǎn)到當(dāng)前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2,   --當(dāng)前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3,   --當(dāng)前行和前邊一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4  --當(dāng)前行及后面所有行 
over (order by score range between 2 preceding and 2 following) 窗口范圍為當(dāng)前行的數(shù)據(jù)幅度減2加2后的范圍內(nèi)的數(shù)據(jù)求和带兜。
from order; 

SQL習(xí)題-窗口函數(shù)

topN案例

#SQL
有表score
想知道學(xué)生成績排名前幾的科目
select 
*
from 
(
select 
*,
row_number() over(partition by subject order by score desc) rmp
from score
) t
where t.rmp<=3;

連續(xù)登陸案例

#sql
#數(shù)據(jù)
1,2020-01-01
1,2020-01-02
1,2020-01-03
1,2020-01-04
1,2020-01-07
1,2020-01-08
9,2020-01-08
7,2020-01-15
3,2020-01-09
4,2020-01-12
1,2020-01-09
2,2020-02-09
2,2020-02-10
2,2020-02-11
2,2020-02-12
2,2020-02-14
2,2020-02-15
4,2020-01-11
4,2020-01-13
4,2020-01-15

#建表與SQL
create table sigin1(
userid int,
sigindate string
)row format delimited
fields terminated by ",";
load data local inpath '/root/user.txt' into table sigin1;

#方法一,使用序列函數(shù),date_sub()查詢連續(xù)登陸天數(shù)大于三天的用戶(沒有去重,假設(shè)同一個(gè)用戶在同一天登陸倆次,分組解決此問題)
select
userid,result
from
(
select
userid,sigindate,date_sub(sigindate,number) as result
from
(
select
userid,sigindate,
dense_rank() over (partition by userid order by sigindate) as number
from sigin1
order by  userid,sigindate )t1)t2
group by result,userid
having  count(result) >=3
order by userid;

#方法二,使用偏移函數(shù),lag函數(shù) 偏移3個(gè)單位如果  date_sub()日期偏移3相等那么用戶登錄的天數(shù)就是連續(xù)3天
#這個(gè)方法可以求但是在這個(gè)題目中是不行的,因?yàn)閘ag是偏移函數(shù),開始的三個(gè)日期前面沒有日期,得到的結(jié)果偏移為0
select
* ,date_sub(sigindate,3) as `date`
from
(
select
userid,sigindate,lag(sigindate,3,0) over (partition by userid order by sigindate) as `date`
from
(
select
userid,sigindate
from sigin1
group by userid, sigindate)t1)t2
where sigindate is not null  and date_sub(sigindate,3) = `date`;

基礎(chǔ)SQL案例

#SQL
#測試數(shù)據(jù)
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

#建表與需求
create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加載數(shù)據(jù)
load  data local inpath "/root/business.txt" into table business;

1枫笛、查詢在2017年4月份購買過的顧客及總?cè)藬?shù)
select
collect_set(name) as people,
count(name) as number
from
(
select
name,substr(orderdate,1,7) as result
from business
where  substr(orderdate,1,7) = '2017-04'
) t1
group by t1.result;

2、查詢顧客的月購買總額,查詢月購買明細(xì)
select
name,total_amount
from
(
select
*,
row_number() over (partition by name ,substr(orderdate,1,7)) as rank
from
(
select
*,
sum(cost) over(partition by name,substr(orderdate,1,7) ) total_amount
from
business) t1 )t2
where rank =1;

#月購買明細(xì)
select
*,
sum(cost) over (partition by name,substr(orderdate,1,7)) as total_amount
from business;

3刚照、查詢顧客的購買明細(xì)及到目前為止每個(gè)顧客購買總金額
select *,
sum(cost) over (partition by name order by orderdate rows between unbounded preceding  and current row )
from business;

4刑巧、查詢顧客上次的購買時(shí)間----lag()over()偏移量分析函數(shù)的運(yùn)用
select
*,
lag(orderdate,1,0) over (partition by name order by orderdate) as Thelastimetobuy
from business;

5、查詢前20%時(shí)間的訂單信息

select
name,orderdate,cost
from
(
select
*,
ntile(5) over (order by  orderdate) as portion
from business) t1
where portion =1;

列轉(zhuǎn)行无畔、行轉(zhuǎn)列

列轉(zhuǎn)行

• 函數(shù)的說明:
  • CONCAT(STRING A ,STRINFG B):返回字符串的連接后的結(jié)果
  • CONCAT_WS(separator, str1, str2,...):這是一個(gè)特殊的CONCAT(),第一個(gè)參數(shù)是分隔符
  • COLLECT_SET():函數(shù)只接受基本數(shù)據(jù)類型啊楚，它的主要作用是將某字段的值進(jìn)行去重匯總，產(chǎn)生array類型字段,COLLECT_LIST()不去重
  • CONCAT_WS(SEPARATOR ,COLLECT_SET(column)) ===>GROUP_CONCAT（）函數(shù)

#SQL
#數(shù)據(jù)
孫尚香,白羊座,A
司馬懿,射手座,A
呂布,白羊座,B
貂蟬,白羊座,A
許褚,射手座,A

#需求
射手座,A    司馬懿|許褚
白羊座,A    孫尚香|貂蟬
白羊座,B    呂布

#建表和SQL
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by ",";
load data local inpath "/root/f.txt" into table person_info;


select
//concat(constellation,",",blood_type),
concat_ws(",",constellation,blood_type) as list,
concat_ws("|",collect_set(name)) as name
from
person_info
group by  constellation,blood_type;

行轉(zhuǎn)列

• 函數(shù)的說明
  • EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行浑彰。
  • LATERAL VIEW:用于和split, explode等UDTF一起使用恭理，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進(jìn)行聚合
  • split(str , 分隔符):返回一個(gè)數(shù)組

#SQL
#數(shù)據(jù)
《疑犯追蹤》 懸疑,動作,科幻,劇情
《Lie to me》 懸疑,警匪,動作,心理,劇情
《戰(zhàn)狼2》 戰(zhàn)爭,動作,災(zāi)難

#需求
《疑犯追蹤》      懸疑
《疑犯追蹤》      動作
《疑犯追蹤》      科幻
《疑犯追蹤》      劇情
《Lie to me》   懸疑
《Lie to me》   警匪
《Lie to me》   動作
《Lie to me》   心理
《Lie to me》   劇情
《戰(zhàn)狼2》        戰(zhàn)爭
《戰(zhàn)狼2》        動作
《戰(zhàn)狼2》        災(zāi)難

#建表與SQL
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "|"
collection items terminated by ",";

load data local inpath "/root/g.txt" into table movie_info;

#lateral view explode(category) table_emp相當(dāng)于一個(gè)虛擬表
select
movie,
result
from movie_info
lateral view
explode(category) result as result

#另一種建表,使用split()切分為數(shù)組形式

case when語法郭变、if語法

case when的語法

• CASE 字段 WHEN 值1 THEN 值1 [WHEN 值2 THEN 值2] [ELSE 值] END
• CASE WHEN 條件表達(dá)式 THEN 值1 [WHEN 條件表達(dá)式 [and or] 條件表達(dá)式THEN 值2] [ELSE 值] END

if的語法

• if( , , )

#SQL
#數(shù)據(jù)
悟空,A,男
娜娜,A,男
宋宋,B,男
鳳姐,A,女
熱巴,B,女
慧慧,B,女

#需求
dpt   男      女
A     2       1
B     1       2

#建表與SQL(一)
create table tb_case_when_demo(
name string ,
dname string ,
gender string
)
row format delimited fields terminated by "," ;

load data local inpath "/root/h.txt" into table tb_case_when_demo;

select
dname,
sum(case gender when '男' then 1 else 0 end) as  nan,
sum(case gender when '女' then 1 else 0 end ) as nv
from tb_case_when_demo
group by  dname;

#SQL(二)
select
dname,
sum(case when gender ='男' then 1 else 0 end) as nan,
sum(case when gender = '女' then  1 else 0 end ) as nv
from tb_case_when_demo
group by dname;

#SQL(三)
select
dname,
sum(`if`(gender='男',1,0)) as nan,
sum(`if`(gender = '女',1,0)) as nv
from tb_case_when_demo
group by  dname;

#SQL練習(xí)題
#數(shù)據(jù)與需求
https://blog.csdn.net/qq_37933018/article/details/106878128
#一個(gè)SQL項(xiàng)目
https://blog.csdn.net/BeiisBei/article/details/103672522

基礎(chǔ)SQL案例

#SQL
#數(shù)據(jù)與需求
(用戶工資組成表,基本工資,基金,提成)
1,2000,3000,1500,1
2,5000,500,1000,2
3,1500,1000,3000,2
4,3000,6000,8000,3
5,1500,2000,1800,1
6,2500,1000,1900,1
(部門表)
1,銷售
2,技術(shù)
3,行政
(員工信息表)
1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32

create table gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
load data local inpath "/root/gz.txt" into table gz;

create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";
load data local inpath "/root/bm.txt" into table bm;

create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/root/yg.txt" into table yg;

1.求出公司中每個(gè)員工的姓名 和 三類收入中最高的那種收入的類型(greatest()函數(shù))
with x as (
select
uid, greatest(jb, jj, tc)as `max`,
//case  when   greatest(jb, jj, tc) = jb then 'jb'  when greatest(jb, jj, tc) = jj then 'jj' when  greatest(jb, jj, tc) = tc then 'tc' else '_' end  as  gz_category,
case   greatest(jb, jj, tc)  when jb then 'jb' when  jj then 'jj' when tc then 'tc' else '_' end  as gz_category1
from gz )
select  yg.uid,max,gz_category1
from  yg  join  x on yg.uid = x.uid;

2.求出公司中每個(gè)崗位的薪資總和
with x as (
select
deptno,
sum(jj+tc+jb)  sum_gz
from
gz
group by deptno)
select
bm.name,x.sum_gz
from bm
join  x on x.deptno = bm.deptno;

3.求出公司中不同性別颜价、不同年齡階段（20-30,31-40,41-50）的員工薪資總和
with x as
(
select
uid,
gender,
case when age>20 and age <=30 then '20-30' when age>30 and age<=40 then '31-40' when age > 41 and age <=50 then '41-50' else '_' end  as stage
from yg )
select
gender,stage,sum(jb+jj+tc) as `max`
from  gz  join  x  on gz.uid = x.uid
group by  x.gender,x.stage
order by stage desc ;

內(nèi)置函數(shù)集合

數(shù)學(xué)函數(shù)

字符函數(shù)

• substr()

  substr(a,b)：從字符串a(chǎn)中薄风，第b位開始取，取右邊所有的字符

  substr(a,b,c)：從字符串a(chǎn)中拍嵌，第b為開始取遭赂，取c個(gè)字符,b可為負(fù)數(shù)從后面數(shù)

• 填充

  lpad（左填充）rpad（右填充）

  例如:select lpad('abc',10,'*'); 往左填充10個(gè)*

集合函數(shù)

• str_to_map('a:1,b:2,c:3');字符串轉(zhuǎn)map
• select size(str_to_map('a:1,b:2,c:3'));返回map的元素個(gè)數(shù)
• map_keys(str_to_map('a:1,b:2'));返回key

轉(zhuǎn)換函數(shù)

• cast():select cast('2018-06-28' as date);字符串轉(zhuǎn)日期類型

日期函數(shù)

• unix_timestamp() 日期轉(zhuǎn)換為當(dāng)前時(shí)間戳
• from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)時(shí)間戳轉(zhuǎn)變?yōu)槿掌诟袷?/li>
• from_unixtime(unix_timestamp(date_created),'yyyy-MM-dd HH:mm:ss')`來規(guī)范時(shí)間的格式

Hive自定義函數(shù)詳解

（UDF、UDAF横辆、UDTF）

MACRO (宏) 的使用

執(zhí)行流程分析

優(yōu)化策略

常用優(yōu)化策略

• 減少job數(shù)量
• 數(shù)據(jù)量較大的情況下撇他，慎用 count(distinct)，group by 容易產(chǎn)生傾斜問題
• 合并小文件
• 解決數(shù)據(jù)傾斜

笛卡爾積與MapJoin的使用

怎么寫in狈蚤、exists

• hive 的一個(gè)高效替代方案：left semi join

小文件的合并

#常用設(shè)置
set hive.merge.mapfiles = true ##在 map only 的任務(wù)結(jié)束時(shí)合并小文件
set hive.merge.mapredfiles = false ## true 時(shí)在 MapReduce 的任務(wù)結(jié)束時(shí)合并小文件
set hive.merge.size.per.task = 256*1000*1000 ##合并文件的大小
set mapred.max.split.size=256000000; ##每個(gè) Map 最大分割大小
set mapred.min.split.size.per.node=1; ##一個(gè)節(jié)點(diǎn)上 split 的最少值
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##執(zhí)行 Map 前進(jìn)行小文件合并

解決小文件過多的方案

產(chǎn)生小文件的來源和影響

• 來源
  • 源數(shù)據(jù)本身有很多小文件
  • 動態(tài)分區(qū)會產(chǎn)生大量小文件
  • reduce個(gè)數(shù)越多, 小文件越多
  • 按分區(qū)插入數(shù)據(jù)的時(shí)候會產(chǎn)生大量的小文件, 文件個(gè)數(shù) = maptask個(gè)數(shù) * 分區(qū)數(shù)
• 影響
  • 從Hive的角度看困肩，小文件會開很多map，一個(gè)map開一個(gè)JVM去執(zhí)行脆侮，所以這些任務(wù)的初始化锌畸，啟動，執(zhí)行會浪費(fèi)大量的資源靖避，嚴(yán)重影響性能潭枣。
  • HDFS存儲太多小文件, 會導(dǎo)致namenode元數(shù)據(jù)特別大, 占用太多內(nèi)存, 制約了集群的擴(kuò)展。

解決的方法

• 調(diào)參進(jìn)行合并

#每個(gè)Map最大輸入大小(這個(gè)值決定了合并后文件的數(shù)量)
set mapred.max.split.size=256000000;

#一個(gè)節(jié)點(diǎn)上split的至少的大小(這個(gè)值決定了多個(gè)DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;

#一個(gè)交換機(jī)下split的至少的大小(這個(gè)值決定了多個(gè)交換機(jī)上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;

#執(zhí)行Map前進(jìn)行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

#===設(shè)置map輸出和reduce輸出進(jìn)行合并的相關(guān)參數(shù)：

#設(shè)置map端輸出進(jìn)行合并幻捏，默認(rèn)為true
set hive.merge.mapfiles = true

#設(shè)置reduce端輸出進(jìn)行合并盆犁，默認(rèn)為false
set hive.merge.mapredfiles = true

#設(shè)置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000

#當(dāng)輸出文件的平均大小小于該值時(shí)，啟動一個(gè)獨(dú)立的MapReduce任務(wù)進(jìn)行文件merge篡九。
set hive.merge.smallfiles.avgsize=16000000

• distribute by rand() 將數(shù)據(jù)隨機(jī)分配給 reduce

# 設(shè)置每個(gè)reducer處理的大小為5個(gè)G
set hive.exec.reducers.bytes.per.reducer=5120000000;

# 使用distribute by rand()將數(shù)據(jù)隨機(jī)分配給reduce, 避免出現(xiàn)有的文件特別大, 有的文件特別小
insert overwrite table test partition(dt)
select * from iteblog_tmp
DISTRIBUTE BY rand();

• sequencefile 作為表存儲格式谐岁，不要用 textfile，在一定程度上可以減少小文件
• 使用hadoop的archive歸檔

#用來控制歸檔是否可用
set hive.archive.enabled=true;
#通知Hive在創(chuàng)建歸檔時(shí)是否可以設(shè)置父目錄
set hive.archive.har.parentdir.settable=true;
#控制需要?dú)w檔文件的大小
set har.partfile.size=1099511627776;

#使用以下命令進(jìn)行歸檔
ALTER TABLE srcpart ARCHIVE PARTITION(ds='2020-04-08', hr='12');

#對已歸檔的分區(qū)恢復(fù)為原文件
ALTER TABLE srcpart UNARCHIVE PARTITION(ds='2020-04-08', hr='12');

#注意榛臼，歸檔的分區(qū)不能夠INSERT OVERWRITE伊佃，必須先unarchive

• hadoop自帶的三種小文件處理方案

電子商務(wù)消費(fèi)行為分析(未找到數(shù)據(jù))

#數(shù)據(jù)與需求

textfile、sequencefile 和 rcfile

面試題摘要

#SQL
#數(shù)據(jù)與需求
現(xiàn)有這么一批數(shù)據(jù)娄柳，現(xiàn)要求出：  
每個(gè)用戶截止到每月為止的最大單月訪問次數(shù)和累計(jì)到該月的總訪問次數(shù)  
三個(gè)字段的意思：  
用戶名寓辱，月份，訪問次數(shù) 
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
create table if not exists infos(name string, date string, ftime int) 
row format delimited fields terminated by ",";
load data local inpath "/root/j.txt" into table infos;

#sql(窗口寫法)
select
name,mydate,
max(t1.ints) over (partition by name order by mydate),
t1.ints,
sum(ints) over (partition by name order by mydate rows  between  unbounded preceding and  current row )
from
(
select
name,mydate,sum(ints) as ints
from infos1
group by  name,mydate) t1;

sql(考慮使用自連接實(shí)現(xiàn))
//核心where datea <= dateb
select nameb, dateb, visitb,
max(visita) as max_visit,
sum(visita) as sum_visit
from (
select
a.name as namea,
a.mydate as datea,
a.visit as visita,
b.name as nameb,
b.mydate as dateb,
b.visit as visitb
from (select name,mydate,sum(ints) as visit from infos1 group by name,mydate) a join
(select name,mydate,sum(ints) as visit from infos1 group by name,mydate)  b
on a.name = b.name
 ) t1
where datea <= dateb
group by nameb, dateb, visitb;

//SQL實(shí)現(xiàn)dense_rank()排序,sum()的窗口的實(shí)現(xiàn)