學習篇-Hadoop-Hive-DDL操作

文章目錄

• • • • • 一、Hive-DDL
        • • 1. Hive-DDL-資料庫
          • 2. Hive-DDL-表

一、Hive-DDL

官網位址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

DDL：全稱-Data Definition Language

• create、delete、alter

1. Hive-DDL-資料庫

• 建立資料庫

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

  • (|)：表示二選一
  • []：可寫可不寫
  • /user/hive/warehouse是Hive預設的存儲在HDFS上的路徑，可以使用 [LOCATION hdfs_path]進行更改。
  • 檢視表資訊

    # 普通檢視
    desc database xxx;
    # 詳細檢視
    desc database extended xxx;
               

  • 顯示目前的庫

    # 檢視hive.cli.print.current.db屬性是否打開
    set hive.cli.print.current.db;
    # 設定屬性
    set hive.cli.print.current.db=true;
               

  • 清除螢幕

    !clern
               

• 删除資料庫

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

2. Hive-DDL-表

• 建立語句

  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
    [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
    [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]
    [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
       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 (...)]  -- (Note: Available in Hive 0.6.0 and later)
    ]
    [LOCATION hdfs_path]
    [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
    [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
   
             

  • 建表

    # 建議使用英文，作為分割，空格，源資料一定要處理好。
    CREATE  TABLE `emp`(
        `empno` int,
        `empname` string,
        `job` string,
        `mgr` int,
        `hiredate` string,
        `sal` double,
        `comm` double,
        `deptno` int
    ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
               

  • 幾種檢視表結構
    • 簡單檢視- desc emp;

      

    • 詳細檢視- desc extended emp;

      

    • 格式化詳細檢視- desc formatted emp;

      

  • 修改表名

    ALTER TABLE table_name RENAME TO new_table_name;