HQL語句總結

DDL語句

一. 資料庫的DDL語句

1. 建立資料庫

   Create database dbname [IF NOT EXISTS] [LOCATION] filepath;

   說明：IF NOT EXISTS 表示不存在時建立，LOCATION filepath表示建立時指定HDFS存儲位置

2. 修改資料庫資訊

   Alter database dbname set dbproperites(name=value);

   說明：資料庫的其他資訊不能修改，隻能利用dbproperities增加描述資訊。

3. 查詢資料庫

   Show databases;

4. 查詢資料庫資訊

   Desc database dbname;//查詢基本資訊

   Desc database extended dbname;//查詢詳細資訊

5. 切換資料庫

   Use dbname;

6. 删除資料庫

   Drop database [IF EXISTS] dbname [CASCADE];

   說明：IF EXISTS 表示在資料庫存在時執行删除，CASCADE表示級聯删除(當資料庫有表時一起删除)。

二. 表的DDL語句

Hive中的表邏輯上由存儲的資料和描述資料資訊的相關中繼資料組成。資料一般存在HDFS中(可更改)，中繼資料存在關系型資料庫中。

1. 建立表

   Create [EXTERNAL] table [IF NOT EXISTS] tablename(

   Col_name1 dataType [COMMENT col_comment1];//對列增加描述資訊

   Col_name2 dataType [COMMENT col_comment2];

   Col_name3 dataType [COMMENT col_comment3];

   ……

   )//以上确定表名和字段名字段類型,EXTERNAL用于建立外部表，預設為管理表

   [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] //指定檔案存儲類型

   [LOCATION hdfs_path]//指定表導入資料的hdfs路徑

ROW FORMAT 詳細說明:

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, …)]

2. 修改表

Alter table tablename [];

① 修改表名

Alter table tablename RENAME TO new_tablename

② 修改列名

Alter table tablename CHANGE [COLUMN] col_name new_col_name dataType;

③ 增加列(可以一次增加多列)

Alter table tablename ADD COLUMNS (col_name dataType,….);

④ 替換列(可實作删除列的功能)

Alter table tablename REPLACE COLUMNS (new_col_name dataType….);

注：替換列會用新的列資訊替換原來所有的列資訊。

說明：若替換的列和原有的列類型不一緻且表中存在資料，會發生類型自動轉換(隐式轉換,範圍小的自動轉換範圍大的)。若無法轉換的類型會用null填充。

隐式類型轉換規則如下：

（1）任何整數類型都可以隐式地轉換為一個範圍更廣的類型，如 TINYINT 可以轉換成 INT，INT 可以轉換成 BIGINT，任何數值類型可以轉成文本類型(String,varchar,char)。

（2）所有整數類型、FLOAT 和 STRING 類型都可以隐式地轉換成 DOUBLE。

（3）TINYINT、SMALLINT、INT 都可以轉換為 FLOAT。

（4）BOOLEAN 類型不可以轉換為任何其它的類型。

删除表

Drop table tablename;

3. 檢視表的資訊

Desc tablename;//檢視表的基本資訊

Desc fromatted tablename;//檢視表的詳細資訊

4. 删除表

Drop table tablename;

DML語句

一． 表資料導入

說明：自hive0.14.0版本後，hive支援insert into table…values方式插入少量資料，也支援update和delete操作，但要想使用update和delete，需要進行相關配置時hive中的表支援事務的ACID特性。(相關配置參見https://blog.csdn.net/xueyao0201/article/details/79387647），因為HDFS不提供就地檔案更新，是以插入、更新、删除操作都被儲存一個較小的增量檔案中，有metastore在背景運作的mapreduce作業會定期将這些資料合并到‘基表’中。

1. load方式導入

   load data [local] inpath ‘/opt/module/datas/student.txt’ [overwrite] into table student

   [partition (partcol1=val1,…)];

   （1） load data:表示加載資料

   （2） local:表示從本地加載資料到 hive 表；否則從 HDFS 加載資料到 hive 表

   （3） inpath:表示加載資料的路徑

   （4） into table:表示加載到哪張表

   （5） student:表示具體的表

   （6） overwrite:表示覆寫表中已有資料，否則表示追加

   （7） partition:表示上傳到指定分區

2. 建立表時使用location定位加載的資料位置（隻能定位hdfs中的路徑）

   Location (hdfs中的目錄名)

   注：load與location方式導入資料的差別比較

   ① load方式導入時會把資料複制或移動到hive的倉庫目錄中(本地複制，HDFS直接移動)。

   ② location定位hdfs中的目錄，表示該表将該路徑作為存儲資料的路徑，對表中資料的增加和删除都直接對應該路徑進行操作。

   說明：location不會在hive的預設路徑建立表的檔案夾，而是将定位的檔案夾作為表的檔案夾。

3. 通過查詢導入

   Insert into table tablename1 SELECT [LIST] from tablename2 …;//追加導入

   Insert overwrite table tablename1 SELECT [LIST] from tablename2…;//覆寫導入

   Form sourcetable

   Insert overwrite targettable select[LIST]

   ……//多表插入，根據一個源表所查到的不同結果表同時導入

4. CTAS方式導入

   Create Table target As Select [LIST] from source;

5. Import方式導入(從hdfs導入,必須是經過export方式導出的資料)

   Import table tablename from ‘user/hive/warehouse/export/emp’;

   注：hive中一個表一般對應一個檔案夾,import方式導入會校驗字段等中繼資料資訊是否比對，是以必須導入的是export方式導出的資料(會有一個中繼資料檔案夾存放中繼資料資訊)

二． 表資料導出

1. 将查詢結果導出到本地

   Insert overwrite local directory ‘/home/bduser/datas’

   Select * from emp;

2. 将查詢結果格式化導出到本地

   Insert overwrite local directory ‘/home/bduser/datas’

   Row format delimited fiedls terminated by ‘\t’

   Collection item terminated by ‘\n’

   Select * from emp;

3. 将查詢結果導出到HDFS(不帶local即可)

   Insert overwrite directory ‘/user/bduser/datas’

   Row format delimited fiedls terminated by ‘\t’

   Collection item terminated by ‘\n’

   Select * from emp;

4. HDFS指令導出

   Hdfs dfs –get source target;

5. Hive shell 導出

   實際采用輸出重定向

   Hive –e ‘select * from emp’ >> /home/bduser/datas/emp.txt

6. Export方式導出(導出到hdfs上)

   Export table emp ‘/user/hive/warehouse/export/emp’

   三． 資料删除

   Truncate table tablename;

   隻删除管理表的資料，而保留表結構，對于外部表不起作用。

   Drop table tablename

   删除管理表的資料和表結構，對于外部表删除其表結構。

DQL語句

與通常的SQL語句基本一緻

1. 句式說明

   SELECT [ALL | DISTINCT] select_expr, select_expr, …

   FROM table_reference

   [WHERE where_condition]

   [GROUP BY col_list]

   [ORDER BY col_list]

   [CLUSTER BY col_list

   | [DISTRIBUTE BY col_list] [SORT BY col_list]

   ]

   [LIMIT number]

2. 關于表連接配接

   支援等值連接配接，不支援非等值連接配接

   等值連接配接：

   内連接配接：inner join 顯示滿足連接配接條件的記錄

   左外連接配接：left join 顯示左表所有記錄，右表滿足連接配接條件的記錄(沒有記錄的字段補null)

   右外連接配接：right join 顯示左表滿足連接配接條件的記錄(沒有記錄的字段補null)，右表的所有記錄

   全外連接配接：full join 顯示兩邊所有的記錄(沒有記錄的字段補null)

多表連接配接：連接配接 n 個表，至少需要 n-1 個連接配接條件。例如：連接配接三個表，至少需要兩個連接配接條件

執行個體：

SELECT e.ename, d.deptno, l. loc_name

FROM emp e

JOIN dept d

ON d.deptno = e.deptno

JOIN location l

ON d.loc = l.loc;

大多數情況下， Hive 會對每對 JOIN 連接配接對象啟動一個 MapReduce 任大多數情況下， Hive 會對每對 JOIN 連接配接對象啟動一個 MapReduce 任務。本例中會首先啟動一個 MapReduce job 對表 e 和表 d 進行連接配接操作，然後會再啟動一個MapReduce job 将第一個 MapReduce job 的輸出和表 l;進行連接配接操作。

注意：為什麼不是表 d 和表 l 先進行連接配接操作呢？這是因為 Hive 總是按照從左到右的順

序執行的任務。

3. 關于like和rlike

Like：運算選擇類似的值

% 代表零個或多個字元(任意個字元)。

_ 代表一個字元。

Rlike：可比對正規表達式進而達到選擇的目的。

4. 關于limit

Limit作用再查詢的末端，用于限制查詢傳回的行數

MySQL中limit,n1,n2；表示從n1條記錄開始傳回，一共傳回n2行，若沒有n1則預設從第一行開始傳回。

Hive中隻能limit n2,即從第一行記錄開始傳回，傳回n2行記錄。

5. 關于排序

① Order by

全局排序，一個mapreduce進行（用法與效果與MySQL一緻）

Asc升序(預設)

Desc降序

② Sort by

局部有序，每個map的輸出有序

③ Distribute by

分區排序，可按照指定字段使用distribute分區，與sort by結合進行排序

④ Cluster by

當distribute by分區的字段和sort by的字段一樣時，可使用該字段。