[TOC]

一、hql 建表語法格式

hql不區(qū)分大小寫，[]里的屬性是可選屬性。

 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]  

二、參數(shù)說明

1. CREATE TABLE 創(chuàng)建一個(gè)指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項(xiàng)來忽略這個(gè)異常。

2. EXTERNAL 關(guān)鍵字可以讓用戶創(chuàng)建一個(gè)外部表，默認(rèn)是內(nèi)部表。外部表在建表的必須同時(shí)指定一個(gè)指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION），Hive 創(chuàng)建內(nèi)部表時(shí)，會(huì)將數(shù)據(jù)移動(dòng)到數(shù)據(jù)倉庫指向的路徑；若創(chuàng)建外部表，僅記錄數(shù)據(jù)所在的路徑，不對(duì)數(shù)據(jù)的位置做任何改變。在刪除表的時(shí)候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會(huì)被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。

3. COMMENT 是給表字段或者表內(nèi)容添加注釋說明的。

4. PARTITIONED BY 給表做分區(qū)，決定了表是否是分區(qū)表。

5. CLUSTERED BY 對(duì)于每一個(gè)表（table）或者分區(qū)， Hive 可以進(jìn)一步組織成桶，也就是說桶是更為細(xì)粒度的數(shù)據(jù)范圍劃分，Hive采用對(duì)列值哈希，然后除以桶的個(gè)數(shù)求余的方式?jīng)Q定該條記錄存放在哪個(gè)桶當(dāng)中。

6. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','， 這里指定表存儲(chǔ)中列的分隔符，默認(rèn)是 \001，這里指定的是逗號(hào)分隔符，還可以指定其他列的分隔符。

7. STORED AS SEQUENCEFILE|TEXTFILE|RCFILE，如果文件數(shù)據(jù)是純文本，可以使用 STORED AS TEXTFILE，如果數(shù)據(jù)需要壓縮，使用 STORED AS SEQUENCEFILE。

8. LOCATION 定義 hive 表的數(shù)據(jù)在 hdfs 上的存儲(chǔ)路徑，一般管理表（內(nèi)部表不不要自定義），但是如果定義的是外部表，則需要直接指定一個(gè)路徑。

三、創(chuàng)建表的三種方式

3.1、 使用 create 命令

CREATE  TABLE `employee`(  
      `dept_no` int,   
      `addr` string,   
      `tel` string)
    partitioned by(statis_date string ) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

詳細(xì)參考上述建表說明。

可以使用describe formatted employee查看建表相關(guān)的各種配置屬性以及默認(rèn)屬性。

3.2、 create table ...as select..(CTAS)

[AS select_statement] 這個(gè)語句是用來通過查詢已有的表來創(chuàng)建一張新表，這樣可以根據(jù)已有的表來創(chuàng)建子表，對(duì)于數(shù)據(jù)分析和優(yōu)化都是有很大的好處的。

create table employee1 
        as   
       select *  from employee where statis_date='20180229';

1. 使用查詢創(chuàng)建并填充表，select 中選取的列名會(huì)作為新表的列名（所以通常是要取別名）；
2. 會(huì)改變表的屬性、結(jié)構(gòu)，比如只能是內(nèi)部表、分區(qū)分桶也沒了：
   • 目標(biāo)表不允許使用分區(qū)分桶的，FAILED: SemanticException [Error 10068]: CREATE-TABLE-AS-SELECT does not support partitioning in the target table，對(duì)于舊表中的分區(qū)字段，如果通過 select * 的方式，新表會(huì)把它看作一個(gè)新的字段，這里要注意 ；
   • 目標(biāo)表不允許使用外部表，如 create external table … as select… 報(bào)錯(cuò) FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table；
   • 創(chuàng)建的表存儲(chǔ)格式會(huì)變成默認(rèn)的格式 TEXTFILE 。
3. 可以指定表的存儲(chǔ)格式，行和列的分隔符等。

3.3、 使用 like 創(chuàng)建相同結(jié)構(gòu)的表

CREATE TABLE LIKE

• 用來復(fù)制表的結(jié)構(gòu)
• 需要外部表的話，通過 create external table like … 指定
• 不填充數(shù)據(jù)