东京热日韩品精久久,人妻丰满一区二区在线

函數(shù)

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

-- 查看系統(tǒng)自帶函數(shù) 
show functions;
-- 顯示自帶函數(shù)的用法
desc function upper;
desc function extended upper;

時(shí)間函數(shù)

-- 當(dāng)前前日期
select current_date;
select unix_timestamp();
-- 建議使用current_timestamp，有沒有括號都可以 
select current_timestamp();
-- 時(shí)間戳轉(zhuǎn)日期
select from_unixtime(1505456567);
select from_unixtime(1505456567, 'yyyyMMdd');
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');
-- 日期轉(zhuǎn)時(shí)間戳
select unix_timestamp('2019-09-15 14:23:00');
-- 計(jì)算時(shí)間差
select datediff('2020-04-18','2019-11-21'); 
select datediff('2019-11-21', '2020-04-18');
-- 查詢當(dāng)月第幾天
select dayofmonth(current_date);
-- 計(jì)算月末:
select last_day(current_date);
-- 當(dāng)月第1天:
select date_sub(current_date, dayofmonth(current_date)-1)
-- 下個月第1天:
select add_months(date_sub(current_date, dayofmonth(current_date)-1), 1
-- 字符串轉(zhuǎn)時(shí)間(字符串必須為:yyyy-MM-dd格式)
select to_date('2020-01-01');
select to_date('2020-01-01 12:12:12');
-- 日期、時(shí)間戳、字符串類型格式化輸出標(biāo)準(zhǔn)時(shí)間格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'); 
select date_format(current_date(), 'yyyyMMdd');
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');
-- 計(jì)算emp表中，每個人的工齡
select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp;

字符串函數(shù)

-- 轉(zhuǎn)小寫。lower
select lower("HELLO WORLD");
-- 轉(zhuǎn)大寫。upper
select lower(ename), ename from emp;
-- 求字符串長度。length
select length(ename), ename from emp;
-- 字符串拼接。 concat / ||
select empno || " " ||ename idname from emp; select concat(empno, " " ,ename) idname from emp;
-- 指定分隔符。concat_ws(separator, [string | array(string)]+) 
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp;
-- 求子串。substr
SELECT substr('www.lagou.com', 5); SELECT substr('www.lagou.com', -5); SELECT substr('www.lagou.com', 5, 5);
-- 字符串切分。split，注意 '.' 要轉(zhuǎn)義
select split("www.lagou.com", "\\.");

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

-- 四舍五入。round
select round(314.15926); 
select round(314.15926, 2); 
select round(314.15926, -2);
-- 向上取整。ceil
select ceil(3.1415926);
-- 向下取整。floor
select floor(3.1415926);
--求絕對值
abs()
-- 平方
power()
-- 開方
sqrt()
-- 對數(shù)
log2()

條件函數(shù)

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END -- 復(fù)雜條件用 case when 更直觀
select sal, case when sal<=1500 then 1
                 when sal<=3000 then 2
                 else 3 end sallevel
from emp;


select ename, deptno,
       case deptno when 10 then 'accounting'
                   when 20 then 'research'
                   when 30 then 'sales'
                   else 'unknown' end deptname
from emp;



-- COALESCE(T v1, T v2, ...)。返回參數(shù)中的第一個非空值;如果所有值都為 NULL，那么返回NULL
select sal,comm, coalesce(comm,sal) from emp;

-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);

-- nvl(T value, T default_value)
select empno, ename, job, mgr, hiredate, deptno, sal +
nvl(comm,0) sumsal
from emp;


-- nullif(x, y) 相等為空，否則返回第一個參數(shù)
SELECT nullif("b", "b"), nullif("b", "a");

UDTF函數(shù)

表生產(chǎn)函數(shù)，一行輸入，多行輸出。將一行中復(fù)雜的數(shù)據(jù)結(jié)構(gòu)（map,array）拆分成多行。

-- 就是將一行中復(fù)雜的 array 或者 map 結(jié)構(gòu)拆分成多行 
select explode(array('A','B','C')) as col; 
select explode(map('a', 8, 'b', 88, 'c', 888));


-- UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- SELECT pageid, explode(adid_list) AS myCol... is not supported -- SELECT explode(explode(adid_list)) AS myCol... is not supported
-- lateral view 常與 表生成函數(shù)explode結(jié)合使用
-- lateral view 語法:
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)* fromClause: FROM baseTable (lateralView)*

-- 這種寫法報(bào)錯：
 with t1 as ( select 'OK' cola, split('www.lagou.com', '\\.') colb ) select cola,explode(colb) from t1;
-- FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- lateral view 的基本使用 with t1 as (
select 'OK' cola, split('www.lagou.com', '\\.') colb )
select cola, colc
  from t1
       lateral view explode(colb) t2 as colc;

UDTF案例1:

-- 數(shù)據(jù)(uid tags): 
 1 1,2,3
 2 2,3
 3 1,2
--編寫sql,實(shí)現(xiàn)如下結(jié)果: 
 11
 12
 13
 22
 23 
 31 
 32
create table market(
  id int,
  tags string)
   row format delimited fields terminated by ' ';
   
load data local inpath '/mnt/hadoop/data/market.dat' into table market;

-- SQL
 with t as (
   select id ,split(tags,',')as tags from market
 )
 select 
 id ,tag 
 from t lateral view explode(tags) t1 as tag;
 
 -- 簡寫
 select id,tag from market lateral view explode(split(tags,',')) t as tag;

UDTF案例2:

-- 數(shù)據(jù)準(zhǔn)備 
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60

create table stu (
  name string,
  score map<string,string>)
  row format delimited
  fields terminated by '|'
  collection items terminated by ','
  map keys terminated by ':';
  
-- 需求:找到每個學(xué)員的最好成績
-- 第一步，使用 explode 函數(shù)將map結(jié)構(gòu)拆分為多行
select explode(score) as (sub,soc) from stu;
-- 第二步:explode常與 lateral view 函數(shù)聯(lián)用，這兩個函數(shù)結(jié)合在一起能關(guān)聯(lián)其 他字段
select name,sub,soc from stu lateral view explode(score) t as sub,soc;
-- 第三步:找到每個學(xué)員的最好成績
-- 第一種寫法
select max(soc),name from (select name,score,sub,soc from stu lateral view explode(score) t as sub,soc) t group by name;
-- 第二種
with tmp as (select name,sub,soc from stu lateral view explode(score) t as sub,soc)
 select name,max(soc) from tmp group by name;

小結(jié)：

explode將一行數(shù)據(jù)拆分成多行數(shù)據(jù)，可以用于行中復(fù)雜數(shù)據(jù)結(jié)果（array\map）
Lateral view 與 explod連用，解決UDTF不能添加額外列問題。

窗口函數(shù)

又叫開窗函數(shù)，屬于分析函數(shù)一種，解決復(fù)雜報(bào)表統(tǒng)計(jì)需求的功能。用于計(jì)算基于組的某種聚合值，和聚合函數(shù)不同，對于每個組返回多行，聚合函數(shù)只返回一行。窗口函數(shù)制定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化。

over

-- 查詢emp表工資總和
select sum(sal) from emp;
-- 不使用窗口函數(shù)，有語法錯誤
select ename, sal, sum(sal) salsum from emp;
-- 使用窗口函數(shù)
select ename, sal, sum(sal) over() salsum from emp; 

-- 使用窗口函數(shù)，查詢員工姓名、薪水、薪水總和
select ename, sal, sum(sal) over() salsum,
       concat(round(sal / sum(sal) over()*100, 1) , '%')
ratiosal
from emp;

窗口函數(shù)針對的每一行數(shù)據(jù)，over如果沒有指定參數(shù)，默認(rèn)是整個數(shù)據(jù)集

partition by

在over窗口進(jìn)行分區(qū)，對某一列進(jìn)行分區(qū)統(tǒng)計(jì)，窗口大小就是分區(qū)大小；

-- 查詢員工姓名、薪水、部門薪水總和
select ename, sal,deptno, sum(sal) over(partition by deptno) salsum from emp; 
-- 結(jié)果：
MILLER  1300  10  8750
KING  5000  10  8750
CLARK 2450  10  8750
ADAMS 1100  20  10875
SCOTT 3000  20  10875
SMITH 800 20  10875
JONES 2975  20  10875
FORD  3000  20  10875
TURNER  1500  30  9400
ALLEN 1600  30  9400
BLAKE 2850  30  9400
MARTIN  1250  30  9400
WARD  1250  30  9400
JAMES 950 30  9400

order by

-- 在組內(nèi)計(jì)算第一行到當(dāng)前行的累積和
select ename, sal,deptno, sum(sal) over(partition by deptno order by ename) salsum from emp;
-- 結(jié)果：
ename sal deptno  salsum
CLARK 2450  10  2450
KING  5000  10  7450
MILLER  1300  10  8750
ADAMS 1100  20  1100
FORD  3000  20  4100
JONES 2975  20  7075
SCOTT 3000  20  10075
SMITH 800 20  10875
ALLEN 1600  30  1600
BLAKE 2850  30  4450
JAMES 950 30  5400
MARTIN  1250  30  6650
TURNER  1500  30  8150
WARD  1250  30  9400

window 子句

rows between ... and ...

如果要對窗口的結(jié)果做更細(xì)粒度的劃分，使用window子句，有如下幾個選項(xiàng)：

unbounded preceding 組內(nèi)第一行數(shù)據(jù)
n preceding 組內(nèi)當(dāng)前行的前n行數(shù)據(jù)
current row 當(dāng)前行
n following 組內(nèi)當(dāng)前行的后n行數(shù)據(jù)
unbounded following 組內(nèi)最后一行數(shù)據(jù)

window.png

-- 通過部門編號分區(qū)，然后計(jì)算在組內(nèi)計(jì)算第一行到當(dāng)前行的累積和
select ename, sal,deptno, sum(sal) over(partition by deptno order by ename) salsum from emp; 
-- rows between ... and... ，上面是該語句的缺省的寫發(fā)，默認(rèn)是從第一個行計(jì)算到當(dāng)前行累加
select ename, sal,deptno, sum(sal) 
over(partition by deptno order by ename rows between unbounded preceding and current row ) salsum 
from emp; 

-- rows between ... and...  從當(dāng)前行計(jì)算到最后一行
select ename, sal,deptno, sum(sal) 
over(partition by deptno order by ename rows between unbounded preceding and unbounded following ) salsum 
from emp;

-- rows between ... and...  計(jì)算前一行+當(dāng)前行+后一行的累加
select ename, sal,deptno, sum(sal) - sal
over(partition by deptno order by ename rows between 1 preceding and 1 following ) salsum 
from emp;



-- rows between ... and...  計(jì)算前一行+后一行的累加
select ename, sal,deptno, salsum - sal from (
select ename, sal,deptno, sum(sal)
over(partition by deptno order by ename rows between 1 preceding and 1 following ) salsum 
from emp) t;

排名函數(shù)

都是從1開始，生成數(shù)據(jù)項(xiàng)在分組中的排名，通常用于求解 TopN問題，排名有三種

row_number() 排名順序增加不同重復(fù)；如：1、2、3、4 .....
rank() : 排名相等會在名次中留下空位；如：1、2、2、4、5、......
dense_rank(): 排名相等會在名次中不會留下空位；如：1、2、2、3、4、5、......

--測試數(shù)據(jù)
class1 s01 100
class1 s03 100
class1 s05 100
class1 s07 99
class1 s09 98
class1 s02 98
class1 s04 97
class2 s21 100
class2 s24 99
class2 s27 99
class2 s22 98
class2 s25 98
class2 s28 97
class2 s26 96

create table t2(
    cname string,
    sname string,
    score int
) row format delimited fields terminated by ' ';

-- 按照班級，使用3種方式對成績進(jìn)行排名
select 
cname,sname,score,
row_number() over(partition by cname order by score desc) rank1,
rank() over(partition by cname order by score desc) rank2,
dense_rank() over(partition by cname order by score desc) rank3
from t2;
-- 使用3種方式對成績進(jìn)行排名
select 
cname,sname,score,
row_number() over( order by score desc) rank1,
rank() over( order by score desc) rank2,
dense_rank() over( order by score desc) rank3
from t2;

-- 求每個班級前3名的學(xué)員--前3名的定義是什么--假設(shè)使用dense_rank
select * from (
select 
cname,sname,score,
dense_rank() over(partition by cname order by score desc) rank3
from t2)t where rank3 <= 3;

序列/行函數(shù)

lag: 返回當(dāng)前行數(shù)據(jù)的上一行數(shù)據(jù)
lead：返回當(dāng)前行數(shù)據(jù)的下一行數(shù)據(jù)
first_value：將數(shù)據(jù)分組排序后，截止到當(dāng)前行，第一個值
last_value：將數(shù)據(jù)分組排序后，截止到當(dāng)前行，最后一個值
ntile：將數(shù)據(jù)分組排序后，順序切分成n分，返回當(dāng)前切片值

-- 測試數(shù)據(jù)
cookie1,2019-04-10,1
cookie1,2019-04-11,5
cookie1,2019-04-12,7
cookie1,2019-04-13,3
cookie1,2019-04-14,2
cookie1,2019-04-15,4
cookie1,2019-04-16,4
cookie2,2019-04-10,2
cookie2,2019-04-11,3
cookie2,2019-04-12,5
cookie2,2019-04-13,6
cookie2,2019-04-14,3
cookie2,2019-04-15,9
cookie2,2019-04-16,7

-- 建表語句
create table userpv(
    cid string,
    ctime date,
    pv int
)
row format delimited fields terminated by ",";
Load data local inpath '/mnt/hadoop/data/userpv.dat' into table userpv;
-- lag\load
-- 按照cid 分組，按照ctime降序排序，分別將上一行的pv、下一行的pv移動到當(dāng)前行,如圖
select cid,ctime,pv,
lag(pv) over(partition by cid order by ctime ) lagpv,
lead(pv) over(partition by cid order by ctime ) leadpv
from userpv;
-- lag\load 可以跟參數(shù)，移動多行
-- 按照cid 分組，按照ctime降序排序，分別將上兩行的pv、下三行的pv移動到當(dāng)前行,如圖
select cid,ctime,pv,
lag(pv,2) over(partition by cid order by ctime ) lagpv,
lead(pv,3) over(partition by cid order by ctime ) leadpv
from userpv;


-- first_value\last_value
select cid,ctime,pv,
first_value(pv) over(partition by cid order by ctime ) firstpv,
last_value(pv) over(partition by cid order by ctime ) leadpv
from userpv;

-- ntile 按照cid進(jìn)行分組，每組數(shù)據(jù)分成兩份
select cid,ctime,pv,
ntile(2) over(partition by cid order by ctime ) ntilepv
from userpv;

lag-lead.png

lag-load跟著參數(shù)移動多行.png

SQL 面試題

1、連續(xù)7天登陸的用戶

1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

create table ulogin(
    uid int,
    dt date,
    status int
)
row format delimited fields terminated by ' ';
load data local inpath '/mnt/hadoop/data/ulogin.dat' into table ulogin;

-- 連續(xù)值求解問題，只要是連續(xù)值求解，是同一類問題。按以下步驟
-- 1、 把需要的數(shù)據(jù)篩選出來使用row_number()對組內(nèi)數(shù)據(jù)編號
select uid,dt,
row_number() over(partition by uid order by dt) rownum
from ulogin where status =1;
-- 2、使用某個列（該題為日期）減去 rownum = gid，gid可以作為下一步分組的依據(jù)
select uid,dt,
row_number() over(partition by uid order by dt) rownum,
date_sub(dt,row_number() over(partition by uid order by dt)) gid
from ulogin where status =1;
-- 3、使用gid作為分組的依據(jù)，將數(shù)據(jù)分組，求最終的結(jié)果。
select uid,gid,count(*) from(
select uid,
date_sub(dt,row_number() over(partition by uid order by dt)) gid
from ulogin where status =1)t
group by uid,gid having count(*) >= 7;

-- 假設(shè)連續(xù)三天登陸
select  uid,max(total) total from (
select uid,gid,count(*)as total from(
select uid,
date_sub(dt,row_number() over(partition by uid order by dt)) gid
from ulogin where status =1)t
group by uid,gid having count(*) >= 3)t1
group by uid ;

對于連續(xù)值求解的問題：

使用row_number()給組內(nèi)數(shù)據(jù)編號rownum
某個列 - rownum = gid，gid作為下一步分組的依據(jù)
使用gid最為分組依據(jù)，將數(shù)據(jù)分組，求的最終結(jié)果。

2、編寫SQL語句實(shí)現(xiàn)每個班級前三名，同時(shí)求出前三名按名次排序的分差

1 1901 90
2 1901 90
3 1901 83
4 1901 60
5 1902 66
6 1902 23
7 1902 99
8 1902 67
9 1902 87

create table stu(
sno int,
class string,
score int
)row format delimited fields terminated by ' ';
load data local inpath '/mnt/hadoop/data/stu.dat' into table stu;
-- 思路：
-- 1、使用排名函數(shù)，分?jǐn)?shù)并列，使用dense_rank()
select sno,class,score,
dense_rank() over(partition by class order by score desc) as rank
from stu;
-- 2、使用行函數(shù)lag，將數(shù)據(jù)向下一行，求分差相減
select sno,class,score,
dense_rank() over(partition by class order by score desc) as rank,
score - lag(score) over(partition by class order by score desc) as lagsocre
from stu;
-- 3、處理null
select sno,class,score,
dense_rank() over(partition by class order by score desc) as rank,
nvl(score - lag(score) over(partition by class order by score desc),0) as lagsocre
from stu;
-- 自己寫的
select sno,class,rank,score,nvl((score-ss),0)
from (
select sno,class,score,
dense_rank() over(partition by class order by score desc) rank,
 lag(score) over(partition by class order by score desc) ss
from stu) t where  rank  <= 3;

總結(jié)

看見top的時(shí)候，上排名函數(shù)
看見求分差，上行函數(shù)

3、行轉(zhuǎn)列、列轉(zhuǎn)行

-- 行轉(zhuǎn)列
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8


create table rowline1(
    id1 string,
    id2 string,
    flag int
) row format delimited fields terminated by ' ';
load data local inpath '/mnt/hadoop/data/data1.dat' into table rowline1;
-- 解題思路
-- 1、 將數(shù)據(jù)分組聚攏
select id1,id2,collect_set(flag)flags
from rowline1
group by id1,id2;
-- 該方式結(jié)果：會去掉重復(fù)數(shù)據(jù)
-- id1  id2 flags
-- a    b   [2,1,3]
-- c    d   [6,8]

-- 該方式結(jié)果：不會去掉重復(fù)數(shù)據(jù)
select id1,id2,collect_list(flag) flags
from rowline1
group by id1,id2;
-- id1  id2 flags
-- a  b [2,1,3]
-- c  d [6,8,8]

-- 2、 將數(shù)據(jù)連接在一起,如果不使用case類型轉(zhuǎn)換，會報(bào)錯，提示需要使用string類型，但是你的是int類型
select id1,id2,concat_ws('|',collect_list(cast(flag as string))) flags
from rowline1
group by id1,id2;

-- 列轉(zhuǎn)行
1 java
1 hadoop
1 hive
1 hbase
2 java
2 hive
2 spark
2 flink
3 java
3 hadoop
3 hive
3 kafka
create table rowline2(
id string,
course string
)row format delimited fields terminated by ' ';
load data local inpath '/mnt/hadoop/data/data2.dat' into table rowline2;

--
select id,
case when course = 'java' then 1 else 0 end as java,
case when course = 'hadoop' then 1 else 0 end as hadoop,
case when course = 'hive' then 1 else 0 end as hive,
case when course = 'hbase' then 1 else 0 end as hbase,
case when course = 'spark' then 1 else 0 end as spark,
case when course = 'flink' then 1 else 0 end as flink,
case when course = 'kafka' then 1 else 0 end as kafka
from rowline2;
-- 
select id,
sum(case when course = 'java' then 1 else 0 end) as java,
sum(case when course = 'hadoop' then 1 else 0 end)as hadoop,
sum(case when course = 'hive' then 1 else 0 end )as hive,
sum(case when course = 'hbase' then 1 else 0 end) as hbase,
sum(case when course = 'spark' then 1 else 0 end )as spark,
sum(case when course = 'flink' then 1 else 0 end )as flink,
sum(case when course = 'kafka' then 1 else 0 end )as kafka
from rowline2 group by id;

小結(jié)：3類典型問題：行列互轉(zhuǎn)、TopN+行函數(shù)、連續(xù)值求解（row_number(),gid,分組）

自定義函數(shù)

UDF 用戶自定義函數(shù)，特點(diǎn)：一進(jìn)一出，類似于lower，upper
UDAF：用戶自定義聚集函數(shù)，特點(diǎn)，多進(jìn)一出，類似于 sum
UDTF：用戶自定義表生成函數(shù)，一進(jìn)多處，類似于explode

UDF開發(fā)：

繼承org.apache.hadoop.hive.ql.exec.UDF
需要實(shí)現(xiàn)evaluate函數(shù);evaluate函數(shù)支持重載
UDF必須要有返回類型，可以返回null，但是返回類型不能為void

開發(fā)步驟

創(chuàng)建一個maven工程，添加依賴
開發(fā)Java類，繼承UDF，實(shí)現(xiàn)evaluate
打包jar，上傳服務(wù)器
在Hive命令中添加jar
設(shè)置函數(shù)與自定義函數(shù)執(zhí)行的聯(lián)系
測試使用自定義函數(shù)

需求：拓展系統(tǒng)nvl函數(shù)功能

-- 原功能
nvl(ename, "OK"): ename==null => 返回第二個參數(shù)
-- 擴(kuò)展以后的
nvl(ename, "OK"): ename==null or ename=="" or ename==" " => 返回第二個參數(shù)

1、創(chuàng)建maven java 工程，添加依賴

<!-- pom.xml 文件 --> 
<dependency>
  <groupId>org.apache.hive</groupId>
  <artifactId>hive-exec</artifactId>
  <version>2.3.7</version>
</dependency>

2、開發(fā)java類繼承UDF，實(shí)現(xiàn)evaluate 方法

package com.hhb.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

/**
 * @author: huanghongbo
 * @Date: 2020-07-12 17:10
 * @Description:
 */
public class nvl extends UDF {

    public Text evaluate(Text x, Text y) {
        if (x == null || x.toString().trim().length() == 0) {
            return y;
        }
        return x;
    }
}

3、將項(xiàng)目打包上傳服務(wù)器

4、添加開發(fā)的jar包(在Hive命令行中)

add jar  /mnt/hadoop/hiveudf.jar;

5、創(chuàng)建臨時(shí)函數(shù)。指定類名一定要完整的路徑，即包名加類名

create temporary function mynvl as "com.hhb.hive.udf.nvl";

6、執(zhí)行查詢

-- 基本功能還有
select mynvl(comm, 0) from mydb.emp;
-- 測試擴(kuò)充的功能
select mynvl("", "OK"); 
select mynvl(" ", "OK");

7、退出Hive命令行，再進(jìn)入Hive命令行。執(zhí)行步驟6的測試，發(fā)現(xiàn)函數(shù)失效。備注:創(chuàng)建臨時(shí)函數(shù)每次進(jìn)入Hive命令行時(shí)，都必須執(zhí)行以下語句，很不方便:

add jar  /mnt/hadoop/hiveudf.jar;
create temporary function mynvl as "com.hhb.hive.udf.nvl";

可創(chuàng)建永久函數(shù):

1、將jar上傳HDFS

hdfs dfs -put hiveudf.jar jar/

2、在Hive命令行中創(chuàng)建永久函數(shù)

create function mynvl1 as 'com.hhb.hive.udf.nvl' using jar 'hdfs:/user/hadoop/jar/hiveudf.jar';
-- 查詢所有的函數(shù)，發(fā)現(xiàn) mynvl1 在列表中 
show functions;

3、退出Hive，再進(jìn)入，執(zhí)行測試

-- 基本功能還有
select mynvl(comm, 0) from mydb.emp;
-- 測試擴(kuò)充的功能
select mynvl("", "OK"); 
select mynvl(" ", "OK");

4、刪除永久函數(shù)，并檢查

drop function mynvl1;
show functions;

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

函數(shù)

函數(shù)

函數(shù)

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

時(shí)間函數(shù)

字符串函數(shù)

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

條件函數(shù)

UDTF函數(shù)

窗口函數(shù)

over

partition by

order by

window 子句

排名函數(shù)

序列/行函數(shù)

SQL 面試題

自定義函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

函數(shù)

函數(shù)

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

時(shí)間函數(shù)

字符串函數(shù)

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

條件函數(shù)

UDTF函數(shù)

窗口函數(shù)

over

partition by

order by

window 子句

排名函數(shù)

序列/行函數(shù)

SQL 面試題

自定義函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av