Hive DDL语法

• 创建数据库
  语法：CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, …)];

代码演示创建数据库，并查看hdfs文件系统情况，hivedb1.db为数据库目录

hive> show databases;
OK
default
Time taken: 0.105 seconds, Fetched: 1 row(s)
hive> create database if not exists hivedb1
    > ;
OK
Time taken: 1.347 seconds
hive> show databases;
OK
default
hivedb1
Time taken: 0.03 seconds, Fetched: 2 row(s)
hadoop:hadoop:/home/hadoop:>hadoop fs -ls /user/hive/warehouse
18/01/06 10:06:40 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
drwxr-xr-x   - hadoop supergroup          0 2018-01-06 10:05 /user/hive/warehouse/hivedb1.db

代码演示创建指定路径数据库

 hadoop:hadoop:/home/hadoop:>hadoop fs -mkdir /hivedb
18/01/06 10:11:38 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
hive> create database if not exists hivedb location "/hivedb"; 
OK
Time taken: 0.081 seconds
hive> show databases;
OK
default
hivedb
hivedb1
Time taken: 0.05 seconds, Fetched: 3 row(s)

其余选项测试

 hive> create database if not exists hivedb3
    > comment "my database"
    > with dbproperties("creater"="doudian","date"="2018.1");
OK
Time taken: 0.217 seconds
mysql> select * from database_params ;
+-------+-----------+-------------+
| DB_ID | PARAM_KEY | PARAM_VALUE |
+-------+-----------+-------------+
|     8 | creater   | doudian     |
|     8 | date      | 2018.1      |
+-------+-----------+-------------+
2 rows in set (0.00 sec)

• 修改数据库信息
  ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …); – (Note: SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; – (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; – (Note: Hive 2.2.1, 2.4.0 and later)

修改update

hive> alter database hivedb3 set dbproperties ("update"="doudian");
OK
Time taken: 0.137 seconds
hive> desc database extended hivedb3;
OK
hivedb3 my database hdfs://hadoop:8020/user/hive/warehouse/hivedb3.db   hadoop  USER    {update=doudian, creater=doudian, date=2018.1}
Time taken: 0.042 seconds, Fetched: 1 row(s)

• 删除数据库（数据库中有表不能删除）
  DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

hive> show databases;
OK
default
hivedb
hivedb1
hivedb3
Time taken: 0.028 seconds, Fetched: 4 row(s)
hive> drop database hivedb1;
OK
Time taken: 1.954 seconds
hive> show databases;
OK
default
hivedb
hivedb3
Time taken: 0.035 seconds, Fetched: 3 row(s)

• 创建表
  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 [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)//向表中写入数据，以sql语句形式

create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)row format delimited fields terminated by "\t"; 

create table emp1 like emp;//有结构无数据

create table emp2 as select empno，ename from emp；

• 修改表信息
  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/Partition/Column

ALTER TABLE table_name RENAME TO new_table_name;

alter table emp2 rename to newemp2;

• 删除一张表
  DROP TABLE [IF EXISTS] table_name [PURGE]; – (Note: PURGE available in Hive 0.14.0 and later)

 hive> drop table newemp2;
OK
Time taken: 1.14 seconds

若泽大数据交流群：671914634