Hive基礎sql文法(DDL)

前言：

經過前面的學習 我們了解到Hive可以使用關系型資料庫來存儲中繼資料，而且Hive提供了比較完整的SQL功能 ，這篇文章主要介紹Hive基本的sql文法。

首先了解下Hive的資料存儲結構，抽象圖如下：

1.Database:Hive中包含了多個資料庫，預設的資料庫為default，對應于HDFS目錄是/user/hadoop/hive/warehouse，可以通過hive.metastore.warehouse.dir參數進行配置（hive-site.xml中配置）

2.Table: Hive 中的表又分為内部表和外部表 ,Hive 中的每張表對應于HDFS上的一個目錄，HDFS目錄為：/user/hadoop/hive/warehouse/[databasename.db]/table

3.Partition:分區，每張表中可以加入一個分區或者多個，友善查詢，提高效率；并且HDFS上會有對應的分區目錄：

/user/hadoop/hive/warehouse/[databasename.db]/table

4.Bucket(桶)：暫且不講

參考官方文檔： DDL文檔

HiveQL DDL statements are documented here, including:

CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX

DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX

TRUNCATE TABLE

ALTER DATABASE/SCHEMA, TABLE, VIEW

MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)

SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE

DESCRIBE DATABASE/SCHEMA, table_name, view_name

下面是官網上為我們列出的文法：

IF NOT EXISTS：加上這句話代表判斷資料庫是否存在，不存在就會建立，存在就不會建立（生産環境建議使用）。

COMMENT：資料庫的描述

LOCATION：建立資料庫的位址，不加預設在/user/hive/warehouse/路徑下

WITH DBPROPERTIES：資料庫的屬性

DESCRIBE DATABASE db_name：檢視資料庫的描述資訊和檔案目錄位置路徑資訊；

EXTENDED：加上資料庫鍵值對的屬性資訊。

RESTRICT：預設是restrict，如果該資料庫還有表存在則報錯；

CASCADE：級聯删除資料庫(當資料庫還有表時，級聯删除表後再删除資料庫) --生産盡量不用。

（Note：表示對于版本進行的修改）

1.1.TEMPORARY（臨時表）

Hive從0.14.0開始提供建立臨時表的功能，表隻對目前session有效，session退出後，表自動删除。

文法：

注意點：

如果建立的臨時表表名已存在，那麼目前session引用到該表名時實際用的是臨時表，隻有drop或rename臨時表名才能使用原始表；

臨時表限制：不支援分區字段和建立索引。

1.2.Managed and External Tables（内部表和外部表）

Hive上有兩種類型的表，一種是Managed Table(預設的)，另一種是External Table（加上EXTERNAL關鍵字）。它倆的主要差別在于：當我們drop表時，Managed Table會同時删去data（存儲在HDFS上）和meta data（存儲在MySQL），而External Table隻會删meta data。

1.3.COMMENT,ROW FORMAT等其他建表參數

COMMENT ：注釋 可以給字段和表加注釋

先看看官網對于ROW FORMAT的描述

先看看官網給我們的解釋：使用者在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将會使用自帶的 SerDe。在建表的時候，使用者還需要為表指定列，使用者在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 确定表的具體的列的資料。

那麼問題又來了上面這句話又是什麼意思呢？

讓我們來一起看看到底是神馬東東：

DELIMITED：分隔符（可以自定義分隔符）；

FIELDS TERMINATED BY char:每個字段之間使用的分割；

例：-FIELDS TERMINATED BY '\n' 字段之間的分隔符為\n;

COLLECTION ITEMS TERMINATED BY char:集合中元素與元素（array）之間使用的分隔符（collection單例集合的跟接口）；

MAP KEYS TERMINATED BY char：字段是K-V形式指定的分隔符；

LINES TERMINATED BY char：每條資料之間由換行符分割（預設[ \n ]）。

一般情況下LINES TERMINATED BY char我們就使用預設的換行符\n，隻需要指定FIELDS TERMINATED BY char。

1.4.Create Table As Select (CTAS)

建立表（拷貝表結構及資料，并且會運作MapReduce作業）

1.5.Create Table Like

desc formatted table_name; 比較常用

Truncate Table用處不多

1.指定PURGE後，資料不會放到回收箱，會直接删除。

2.DROP TABLE删除此表的中繼資料和資料。如果配置了垃圾箱（并且未指定PURGE），則實際将資料移至.Trash / Current目錄。中繼資料完全丢失。

3.删除EXTERNAL表時，表中的資料不會從檔案系統中删除。

參考：https://blog.csdn.net/yu0_zhang0/article/details/78976021

關于表的DDL操作還有很多，有關分區表的操作還沒詳解 後面會單獨寫一篇分區表