Hive DDL

1. create database schema

1) syntax

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

  [COMMENT database_comment]

  [LOCATION hdfs_path]

  [WITH DBPROPERTIES (property_name=property_value, ...)];

  * DBPROPERTIES : 데이터베이스 속성을 정의

2) example

  create database hivetest;

  create database if not exists hivetest;

  create database if not exists hivetest comment 'test' location '/tmp/test';

  create database if not exists hivetest comment 'test' location '/tmp/test'

  with dbproperties ('Created by' = 'User', 'Created on' = '1-Jan-2015');

2. drop database schema

1) synax

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

2) example

  drop database hivetest;

  drop database if exists hivetest;

  drop database if exists hivetest  restrict;

  drop database if exists hivetest  cascade;

3. alter database schema

 1) syntax

   ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

 2) example

   alter database hivetest set dbproperties ('Created by' = 'User1', 'Created on'='');

4. use database schema

  1) syntax

    use (database|schema) database_name;

 2) example

    use database hivetest;

5. show database schema

 1) syntax

    show (database|shema) [like identifier-with-wildcards]

 2) example

    show databases;

    show database like 'hive*';

6. describe database schema

  1) syntax

    describe database [extended] db_name;

    describe schema [extended] db_name;

  2) example

    describe database hivetest;

    describe schema hivetest;

    describe database extended hivetest;

7. create table

  1) syntax

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]

    [db_name.] 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]

    [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];

    * like : 데이터를 제외한 테이블 뼈대만 생성 

8. drop table

  1) syntax

     drop table [if exists] table_name [purge];

  2) example

     drop table if exists hivetest;

     drop table if exists hivetest purge;

9. truncate table

  1) syntax

     truncate table table_name [ partition partition_spec]

  

  2) example

     truncate table hivetest;

10. rename table

  1) syntax

     alter table table_name rename to new_table;

 

  2) example

     alter table hivetest rename to hivetest2;

11. alter table properties

  1) syntax

     alter table table_name set talproperties table_properties;

  2) example

     alter table hivetest set tblproperties ( 'comment' = 'test');

12. create view

  1) syntax

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...)]

    [COMMENT view_comment]

    [TBLPROPERTIES (property_name = property_value, ...)]

    AS SELECT ...;

 2) example

    create view hiveview as select * from hivetest;

    create view if not exists hiveview1 as select id, firstname from

    hivetest where firstname = 'John';

13. drop table

  1) syntax

     drop view [if exists] view_name;

  2) example

     drop view hiveview;

14. alter the view properties

  1) syntax

     alter view view_name set tblproperties table_properties;

  2) example

     alter view hiveview set tblproperties ('comment' = 'test');

15. alter the view as select

  1) syntax

     alter view view_name as select_statement;

  2) example

     alter view hiveview as select id, firstname from sales;

16. show tables

  1) syntax

     show tables [in database_name] ['identifier_with_wildcards']

  2) example

     show tables;

     show tables in hiveview;

     show tables 'hive@';

17. show partitions

   1) syntax

      show partitions [db_name.]table_name [partition(partition_spec)];

   2) example

      show partitions sales;

      show partitions sales partition(dop='2015-01-01');

      show partitions hivetest.sales partition(dop='2015-01-01');

18. show table properties

   1) syntax

      show talproperties tblname;

   2) example

      show talproperties sales;

      show partitions sales ('numFiles');

19. show create table

   1) syntax

      show create table ([db_name.]table_name | view_name);

   2) example

      show create table sales;

      show create table hivetest.sales;