?1： DDL操作

創建表

?

建表語法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]

  [LOCATION hdfs_path]

說明：

1、 CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項來忽略這個異常。

2、 EXTERNAL關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION），Hive 創建內部表時，會將數據移動到數據倉庫指向的路徑；若創建外部表，僅記錄數據所在的路徑，不對數據的位置做任何改變。在刪除表的時候，內部表的元數據和數據會被一起刪除，而外部表只刪除元數據，不刪除數據。

3、 LIKE 允許用戶復制現有的表結構，但是不復制數據。

4、 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 確定表的具體的列的數據。

5、 STORED AS

SEQUENCEFILE|TEXTFILE|RCFILE

如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY

對于每一個表（table）或者分區， Hive可以進一步組織成桶，也就是說桶是更為細粒度的數據范圍劃分。Hive也是 針對某一列進行桶的組織。Hive采用對列值哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。

把表（或者分區）組織成桶（Bucket）有兩個理由：

（1）獲得更高的查詢處理效率。桶為表加上了額外的結構，Hive 在處理有些查詢時能利用這個結構。具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端連接 （Map-side join）高效的實現。比如JOIN操作。對于JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那么將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量。

（2）使取樣（sampling）更高效。在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便。

具體實例

?1：創建內部表mytable。?

create table if not exists mytable(sid int,sname string) row format delimited fields terminated by ',' stored as textfile

create table if not exists emp(sid int,sname string) row format delimited fields terminated by ',' stored as textfile

?2：創建外部表pageview。?

Create external table if not exists pageview(pageid int,page_url string ) row format delimited fields terminated by ',' location  '/work';

3：創建分區表student。?

create table if not exists student (sid int,sname string)  partitioned by(part string) row format delimited fields terminated by ','stored as textfile;

?Load data local inpath ‘/home/hadoop/room5.txt’  into  student_p  partition (part =’room5’)?

Create table student(id INT,age INT,name STRING) partitioned by(stat_date STRING) clustered by(id) sorted by(age) into 2 buckets row format delimited fields terminated by ‘,’

?

修改表

增加/刪除分區

語法結構

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec:

: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

ALTER TABLE table_name DROP partition_spec, partition_spec,...

具體實例

alter table student_p add partition(part='a') partition(part='b');?

重命名表ü 語法結構

ALTER TABLE table_name RENAME TO new_table_name

具體實例

語法結構

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE則是表示替換表中所有字段。

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

具體實例：

顯示命令show tables

show databases

show partitions

show functions

desc extended t_name;

desc formatted table_name;

Load 語法結構

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO

TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

說明：

1、 Load 操作只是單純的復制/移動操作，將數據文件移動到 Hive 表對應的位置。

2、 filepath：

相對路徑，例如：project/data1

絕對路徑，例如：/user/hive/project/data1

包含模式的完整 URI，列如：

hdfs://namenode:9000/user/hive/project/data1

3、 LOCAL關鍵字

如果指定了 LOCAL， load 命令會去查找本地文件系統中的 filepath。

如果沒有指定 LOCAL 關鍵字，則根據inpath中的uri查找文件

(如果指定了 LOCAL，那么：

load 命令會去查找本地文件系統中的 filepath。如果發現是相對路徑，則路徑會被解釋為相對于當前用戶的當前路徑。

load 命令會將 filepath中的文件復制到目標文件系統中。目標文件系統由表的位置屬性決定。被復制的數據文件移動到表的數據對應的位置。

如果沒有指定 LOCAL 關鍵字，如果 filepath 指向的是一個完整的 URI，hive 會直接使用這個 URI。 否則：如果沒有指定 schema 或者 authority，Hive 會使用在 hadoop 配置文件中定義的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。

如果路徑不是絕對的，Hive 相對于/user/進行解釋。

Hive 會將 filepath 中指定的文件內容移動到 table （或者 partition）所指定的路徑中。)

4、 OVERWRITE 關鍵字

如果使用了 OVERWRITE 關鍵字，則目標表（或者分區）中的內容會被刪除，然后再將 filepath 指向的文件/目錄中的內容添加到表/分區中。

如果目標表（分區）已經有一個文件，并且文件名和 filepath 中的文件名沖突，那么現有的文件會被新文件所替代。

具體實例：

1、 加載相對路徑數據。

?

?3：加載包含模式數據?。

4：OVERWRITE關鍵字使用。

?

Insert 將查詢結果插入Hive表

語法結構

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

Multiple inserts:

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

Dynamic partition inserts:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

具體實例：

1、基本模式插入。?

?3、自動分區模式。

?

導出表數據

語法結構

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

multiple inserts:

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

具體實例

1、導出文件到本地。

數據寫入到文件系統時進行文本序列化，且每列用^A來區分，\n為換行符。用more命令查看時不容易看出分割符，可以使用: sed -e 's/\x01/|/g' filename來查看。

2、導出數據到HDFS。

?

SELECT 基本的Select操作

語法結構

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

 | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

注：1、order by 會對輸入做全局排序，因此只有一個reducer，會導致當輸入規模較大時，需要較長的計算時間。

2、sort by不是全局排序，其在數據進入reducer前完成排序。因此，如果用sort by進行排序，并且設置mapred.reduce.tasks>1，則sort by只保證每個reducer的輸出有序，不保證全局有序。

3、distribute by根據distribute by指定的內容將數據分到同一個reducer。

4、Cluster by 除了具有Distribute by的功能外，還會對該字段進行排序。因此，常常認為cluster by = distribute by + sort by

具體實例

1、獲取年齡大的3個學生。

?

3、按學生名稱匯總學生年齡。

?