学习篇-Hadoop-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;