Hive(六):HQL DDL

      HQL語法基於 SqlLine（http://sqlline.sourceforge.net/），DDL主要包含資料庫、函數、視圖的創建、修改、刪除，參考資料：（https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL）, 語法關鍵字：

• CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
• DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
• ALTER DATABASE/SCHEMA, TABLE, VIEW
• SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
• DESCRIBE DATABASE/SCHEMA, table_name, view_name

 DataBase:

• create 語法：

  CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

• 創建資料庫：           create database if not exists demo;
• 顯示數據有數據：     show database;
• 按條件過濾：           show databases like 'dem*';   （註意通配符是*而不是%）
• 資料庫描述：           create database hello with dbproperties('creator'='tgzhu','date'='2016-07-12');
• 顯示資料庫:             describe database hello;   (基本信息，描述信息看不到的）
• 顯示資料庫信息：      describe database extended  hello;  
• 

  ---

     drop 語法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

• RESTRICT：預設的行為，即當資料庫不為空時，不允許刪除的
• CASCADE：先刪除表，再刪除資料庫
• 刪除資料庫: drop database if exists hello;
• 刪除資料庫：drop database if exists hellp cascade;
• 

DataTable:

• CreateTable 語法：

  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  
    [(col_name data_type [COMMENT col_comment], ...)]
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
    [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] 
    [SKEWED BY (col_name, col_name, ...) ]
    ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
    [STORED AS DIRECTORIES]
    [
     [ROW FORMAT row_format] 
     [STORED AS file_format]
       | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] 
    ]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (property_name=property_value, ...)]   
    [AS select_statement];   
   
  
  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
    LIKE existing_table_or_view_name

• 說明：表名和列名是大小寫不敏感，但SerDe和屬性名正好相反
• 切換資料庫： use hello;
• 創建表： 手工創建簡單表

  create table if not exists students(
   ID        string,
   Code      string,
   Name      string,
   Score     decimal(20,8),
   address   struct<street:string,city:string,state:string,zip:string>
  ) tblProperties('creator'='tgzhu','version'='1.0');

• 複製表結構：在已經存在表基礎上建表

  create table if not exists student1 like students;

• CTAS（Create table as Select)表: 創建表並載入查詢結果到表中，限制（目標表不能是分區表、外部表、桶表）
• 
• 試著執行下麵的語句：create external table empdemo1 as select * from employee;
• Re:FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table (state=42000,code=10070)
• show、describe 與上面描述的 database 的語法一致，關鍵字換為： table, 如： show tables;  describle students、describe extended students;
• 顯示指定欄位信息：  describe students.address;
• 外部表：參見上一章 Hive(五):hive與hbase整合

分區表：

• 一個表可以擁有一個或者多個分區，每個分區以文件夾的形式單獨存在表文件夾的目錄下。分區是以欄位的形式在表結構中存在，通過describe table命令可以查看到欄位存在，但是該欄位不存放實際的數據內容，僅僅是分區的表示。在Hive Select查詢中一般會掃描整個表內容，會消耗很多時間做沒必要的工作。有時候只需要掃描表中關心的一部分數據，因此建表時引入了partition概念。表中的一個 Partition 對應於表下的一個目錄,Partition 就是輔助查詢，縮小查詢範圍，加快數據的檢索速度和對數據按照一定的規格和條件進行管理
• 示例：

  create table student_p(
    id string,
    name string,   
    age int, 
    birthday date
  ) partitioned by(region string, sex string);  

• 

 桶表：

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

1. 1. 獲得更高的查詢處理效率，比如JOIN操作，在JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作，那麼將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量
   2. 使取樣更高效,在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便

• 示例建表語句如下：

  create table student_c(
    id string,
    name string,   
    age int, 
    birthday date
  ) 
  partitioned by(region string, sex string)  
  clustered by(id) sorted by(birthday) into 64 buckets;