一、資料庫
1 建立庫語句
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
2查詢資料庫
2.1 顯示資料庫
show databases;
2.2 過濾顯示查詢的資料庫
show databases like 'd*';
2.3 顯示資料庫資訊
desc database ods;
2.4 顯示資料庫詳細資訊，extended
desc database extended ods;
2.5 切換資料庫詳細資訊，use
use dwd;
3 修改資料庫
3.1 修改資料庫屬性，Alter
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);
3.2 删除資料庫，drop
3.2.1 删除空的資料庫
drop database dwd;
3.2.2删除非空的資料庫，使用cascade強制删除。
drop database dwd cascade;
二、表
1 建表語句
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 row_format]
[STORED AS file_format]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
（1）CREATE TABLE 建立一個指定名字的表。如果相同名字的表已經存在，則抛出異常；使用者可以用 IF NOT EXISTS 選項來忽略這個異常。
（2）EXTERNAL關鍵字可以讓使用者建立一個外部表，在建表的同時可以指定一個指向實際資料的路徑（LOCATION），在删除表的時候，内部表的中繼資料和資料會被一起删除，而外部表隻删除中繼資料，不删除資料。
（3）COMMENT：為表和列添加注釋。
（4）PARTITIONED BY建立分區表
（5）CLUSTERED BY建立分桶表
（6）SORTED BY不常用，對桶中的一個或多個列另外排序
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
使用者在建表的時候可以自定義SerDe或者使用自帶的SerDe。如果沒有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将會使用自帶的SerDe。在建表的時候，使用者還需要為表指定列，使用者在指定表的列的同時也會指定自定義的SerDe，Hive通過SerDe确定表的具體的列的資料。
SerDe是Serialize/Deserilize的簡稱， hive使用Serde進行行對象的序列與反序列化。
（8）STORED AS指定存儲檔案類型
常用的存儲檔案類型：SEQUENCEFILE（二進制序列檔案）、TEXTFILE（文本）、RCFILE（列式存儲格式檔案）
如果檔案資料是純文字，可以使用STORED AS TEXTFILE。如果資料需要壓縮，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存儲位置。
（10）AS：後跟查詢語句，根據查詢結果建立表。
（11）LIKE允許使用者複制現有的表結構，但是不複制資料。
（12）TBLPROPERTIES設定表的屬性，在表明壓縮類型時用過。（例如”orc.compress”=”SNAPPY”）
2 查詢表資訊
2.1 檢視表的中繼資料資訊 desc
DESCRIBE FORMATTED orders;
2.2 修改 alter
2.2.1 修改表名： alter table dept rename to depts;
2.2.2 修改表屬性：
alter table depts set tblproperties ('EXTERNAL'='TRUE')
alter table depts set tblproperties ('EXTERNAL'='FALSE')
2.2.3 修改列,注意如果修改的字段類型和之前的字段類型不一緻，之前的資料就無法顯示
修改列名和列資料類型：alter table depts change dept_name dname string ;
修改位置放置第一位：alter table depts change id did string first;
修改位置指定某一列後面：alter table depts change dname dname string after did;
2.2.4 添加列(慎用)
alter table depts add columns(daddress string);
2.2.5 添加分區
alter table depts add partition(dt=20200713);
alter table depts add partition(dt=20200713) location '/user/test/20200713.txt';
2.2.6 修改分區
alter table depts partition(dt=20170404) rename to partition(dt=20170405);
alter table depts partition(dt=20170404) set location '/user/test/depts.txt';
2.2.7 删除分區
alter table depts drop if exists partition(dt=20170404);
2.3 查詢表資料
Hive中的SELECT基礎文法和标準SQL文法基本一緻，支援WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查詢等；
具體文法：
[WITH CommonTableExpression (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
2.3.1 常用關鍵詞簡介
(1) ORDER BY和SORT BY
ORDER BY用于全局排序，就是對指定的所有排序鍵進行全局排序，使用ORDER BY的查詢語句，最後會用一個Reduce Task來完成全局排序。
explain select id,name from emp where deptid = 1001
explain select id,name from emp where deptid = 1001 order by id
SORT BY用于分區内排序，即每個Reduce任務内排序。
設定了2個reduce，從結果可以看出，每個reduce内做了排序。設定一個reduce，從結果看和order by一緻。
（2）DISTRIBUTE BY和CLUSTER BY
distribute by：按照指定的字段或表達式對資料進行劃分，輸出到對應的Reduce或者檔案中。
cluster by：除了兼具distribute by的功能，還兼具sort by的排序功能。
(3)GROUP BY語句通常會和聚合函數一起使用，按照一個或者多個列隊結果進行分組，然後對每個組執行聚合操作。
（4）子查詢
子查詢和标準SQL中的子查詢文法和用法基本一緻，需要注意的是，Hive中如果是從一個子查詢進行SELECT查詢，那麼子查詢必須設定一個别名。
SELECT col
FROM (
SELECT a+b AS col
FROM t1
) t2
where 語句中也支援子查詢。
SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
WITH..AS..也叫做子查詢部分,語句允許hive定義一個sql片段,供整個sql使用，即将子查詢作為一個表的文法，叫做Common Table Expression（CTE）
with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select from q1 union all select from q2;
with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select * from q1;
(5)Hive查詢中有兩個虛拟列：INPUT__FILE__NAME：資料對應的HDFS檔案名；
BLOCK__OFFSET__INSIDE__FILE：該行記錄在檔案中的偏移量；
(6)HAVING 語句
having與where不同點
·where後面不能寫分組函數，而having後面可以使用分組函數。
·having隻用于group by分組統計語句。
(7) JOIN語句
Hive中除了支援和傳統資料庫中一樣的内關聯、左關聯、右關聯、全關聯，還支援LEFT SEMI JOIN和CROSS JOIN，但這兩種JOIN類型也可以用前面的代替。
·内關聯（JOIN）
·左外關聯（LEFT [OUTER] JOIN）
·右外關聯（RIHGHT [OUTER] JOIN）
（8）LIKE語句
如果字元串A或者字元串B為NULL，則傳回NULL；如果字元串A符合表達式B的正則文法，則為TRUE；否則為FALSE。B中字元”_”表示任意單個字元，而字元”%”表示任意數量的字元。
年齡表
查詢年齡為1開頭的資料：select * from ages where age like '1%';
查詢年齡第二位是1的資料：select * from ages where age like '_1%';
RLIKE子句是Hive中LIKE功能的一個擴充，其可以通過Java的正規表達式這個更強大的語言來指定比對條件。
2.3.2 常用函數簡介
(1) 字元串連接配接函數，concat
文法: concat(string A, string B...)
傳回值: string
說明：傳回輸入字元串連接配接後的結果，支援任意個輸入字元串
舉例：select concat('abc','def','gh') from ages ;
（2）帶分隔符字元串連接配接函數：concat_ws
文法: concat_ws(string SEP, string A, string B...)
傳回值: string
說明：傳回輸入字元串，連接配接後的結果，SEP表示各個字元串間的分隔符
舉例：select concat_ws(',','abc','def','gh') from ages ;
除此之外還可以将數組的形式轉換為字元串concat_ws(string SEP, array)
舉例：select concat_ws('|',array('ads','des','ss')) from ages ;
（3）集合去重函數：collect_set
文法: collect_set(col)
傳回值: array
說明: 将col字段進行去重，合并成一個數組。
舉例：cookies表
select cookieid,collect_set(name) from cookies group by cookieid ;
(4) UDTF一進多出
列轉行：explode
文法: explode(ARRAY)
傳回值: 多行
說明：将數組轉換為多行
舉例：select explode(array('ads','des','ss'))from ages;
除此之外還可以将map拆分為多行。
（5）UDAF多進一出
聚合函數count，sum，avg等
（6）視窗函數
一般和聚合函數配合使用
OVER()：指定分析函數工作的資料視窗大小，這個資料視窗大小可能會随着行的變而變化。
舉例：
orders表
總結：over是對分的組得到結果的count ，因為搜到了4個人，是以count數為4。直接count是分組後對組中的資料進行count。
總結：添加partition by name 表示按照name分組進行sum求和。
(7) 排序函數
RANK() 排序相同時會重複，總數不會變
DENSE_RANK() 排序相同時會重複，總數會減少
ROW_NUMBER() 會根據順序計算
舉例：scores表
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from scores;
得到結果：
（8）函數總結大全
一、關系運算：
- 等值比較: =
- 等值比較:<=>
- 不等值比較: <>和!=
- 小于比較: <
- 小于等于比較: <=
- 大于比較: >
- 大于等于比較: >=
- 區間比較
- 空值判斷: IS NULL
- 非空判斷: IS NOT NULL
- LIKE比較: LIKE
- JAVA的LIKE操作: RLIKE
-
REGEXP操作: REGEXP
二、數學運算：
- 加法操作: +
- 減法操作: –
- 乘法操作: *
- 除法操作: /
- 取餘操作: %
- 位與操作: &
- 位或操作: |
-
位異或操作: ^
9．位取反操作: ~
三、邏輯運算：
- 邏輯與操作: AND 、&&
- 邏輯或操作: OR 、||
-
邏輯非操作: NOT、!
四、複合類型構造函數
- map結構
- struct結構
- named_struct結構
- array結構
-
create_union
五、複合類型操作符
- 擷取array中的元素
- 擷取map中的元素
-
擷取struct中的元素
六、數值計算函數
- 取整函數: round
- 指定精度取整函數: round
- 向下取整函數: floor
- 向上取整函數: ceil
- 向上取整函數: ceiling
- 取随機數函數: rand
- 自然指數函數: exp
- 以10為底對數函數: log10
- 以2為底對數函數: log2
- 對數函數: log
- 幂運算函數: pow
- 幂運算函數: power
- 開平方函數: sqrt
- 二進制函數: bin
- 十六進制函數: hex
- 反轉十六進制函數: unhex
- 進制轉換函數: conv
- 絕對值函數: abs
- 正取餘函數: pmod
- 正弦函數: sin
- 反正弦函數: asin
- 餘弦函數: cos
- 反餘弦函數: acos
- positive函數: positive
-
negative函數: negative
七、集合操作函數
- map類型大小：size
- array類型大小：size
- 判斷元素數組是否包含元素：array_contains
- 擷取map中所有value集合
- 擷取map中所有key集合
-
數組排序
八、類型轉換函數
- 二進制轉換：binary
-
基礎類型之間強制轉換：cast
九、日期函數
- UNIX時間戳轉日期函數: from_unixtime
- 擷取目前UNIX時間戳函數: unix_timestamp
- 日期轉UNIX時間戳函數: unix_timestamp
- 指定格式日期轉UNIX時間戳函數: unix_timestamp
- 日期時間轉日期函數: to_date
- 日期轉年函數: year
- 日期轉月函數: month
- 日期轉天函數: day
- 日期轉小時函數: hour
- 日期轉分鐘函數: minute
- 日期轉秒函數: second
- 日期轉周函數: weekofyear
- 日期比較函數: datediff
- 日期增加函數: date_add
-
日期減少函數: date_sub
十、條件函數
- If函數: if
- 非空查找函數: COALESCE
- 條件判斷函數：CASE
- 十一、字元串函數
- 字元ascii碼函數：ascii
- base64字元串
- 字元串連接配接函數：concat
- 帶分隔符字元串連接配接函數：concat_ws
- 數組轉換成字元串的函數：concat_ws
- 小數位格式化成字元串函數：format_number
- 字元串截取函數：substr,substring
- 字元串查找函數：instr
- 字元串長度函數：length
- 字元串查找函數：locate
- 字元串格式化函數：printf
- 字元串轉換成map函數：str_to_map
- base64解碼函數：unbase64(string str)
- 字元串轉大寫函數：upper,ucase
- 字元串轉小寫函數：lower,lcase
- 去空格函數：trim
- 左邊去空格函數：ltrim
- 右邊去空格函數：rtrim
- 正規表達式替換函數：regexp_replace
- 正規表達式解析函數：regexp_extract
- URL解析函數：parse_url
- json解析函數：get_json_object
- 空格字元串函數：space
- 重複字元串函數：repeat
- 左補足函數：lpad
- 右補足函數：rpad
- 分割字元串函數: split
- 集合查找函數: find_in_set
- 分詞函數：sentences
- 分詞後統計一起出現頻次最高的TOP-K
-
分詞後統計與指定單詞一起出現頻次最高的TOP-K
十二、混合函數
- 調用Java函數：java_method
- 調用Java函數：reflect
-
字元串的hash值：hash
十三、XPath解析XML函數
- xpath
- xpath_string
- xpath_boolean
- xpath_short, xpath_int, xpath_long
-
xpath_float, xpath_double, xpath_number
十四、彙總統計函數（UDAF）
- 個數統計函數: count
- 總和統計函數: sum
- 平均值統計函數: avg
- 最小值統計函數: min
- 最大值統計函數: max
- 非空集合總體變量函數: var_pop
- 非空集合樣本變量函數: var_samp
- 總體标準偏離函數: stddev_pop
-
樣本标準偏離函數: stddev_samp
10．中位數函數: percentile
- 中位數函數: percentile
- 近似中位數函數: percentile_approx
- 直方圖: histogram_numeric
- 集合去重數：collect_set
-
集合不去重函數：collect_list
十五、表格生成函數Table-Generating Functions (UDTF)
- 數組拆分成多行：explode
- Map拆分成多行：explode