HIVE入门一之数据库DDL语句

数据库Create|Drop|Alter|Show|Use|Desc

• create database 语法：

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

我们都知道hive是基于hadoop平台的数据仓库，hive的数据存储在hadoop的分布式系统文件中，默认情况下，其存储在hive配置文件hive-site.xml参数hive.metastore.warehouse.dir指定的目录下，默认值为/user/hive/warehouse，当然也可以在创建数据库时，指定LOCATION值来修改默认的路径

①创建数据库时，不指定LOCATION参数，默认存储在/user/hive/warehouse，会在该目录下生产一个database_name.db文件

hive> CREATE DATABASE IF NOT EXISTS study    >        COMMENT 'study'           >        WITH DBPROPERTIES ('create_time'='2017-08-09');

通过命令dfs -ls /user/hive/warehouse查看文件是否存在

hive> dfs -ls /user/hive/warehouse;Found 2 itemsdrwxr-xr-x   - hadoop *          0 2017-08-08 00:28 /user/hive/warehouse/empdrwxr-xr-x   - root   *          0 2017-08-09 21:38 /user/hive/warehouse/study.db

②创建数据库时，指定LOCATION参数，会发现在指定的目录下并没有生产一个database_name.db文件

hive> CREATE DATABASE IF NOT EXISTS bi    >        COMMENT 'bi'    >        LOCATION '/user/hadoop'           >        WITH DBPROPERTIES ('create_time'='2017-08-09');

通过命令dfs -ls /user/hadoop查看，发现并没有生产bi.db文件

hive> dfs -ls /user/hadoop;Found 1 itemsdrwxr-xr-x   - root *          0 2017-08-09 22:16 /user/hadoop/test

再通过show create database database_name查看数据库的创建脚本，也会发现上述两种方法LOCATION值的不同之处

hive> show create database study;OKCREATE DATABASE `study`COMMENT  'study'LOCATION  'hdfs://Master:9000/user/hive/warehouse/study.db'WITH DBPROPERTIES (  'create_time'='2017-08-09')Time taken: 0.052 seconds, Fetched: 7 row(s)hive> show create database bi;OKCREATE DATABASE `bi`COMMENT  'bi'LOCATION  'hdfs://Master:9000/user/hadoop'WITH DBPROPERTIES (  'create_time'='2017-08-09')Time taken: 0.048 seconds, Fetched: 7 row(s)

如果LOCATION参数指定具体user/hadoop/learning.db时，则hadoop目录下会生产learning.db文件

hive> show create database learning;OKCREATE DATABASE `learning`COMMENT  'learning'LOCATION  'hdfs://Master:9000/user/hadoop/learning.db'WITH DBPROPERTIES (  'create_time'='2017-08-09')Time taken: 0.073 seconds, Fetched: 7 row(s)hive> dfs -ls /user/hadoop;Found 2 itemsdrwxr-xr-x   - root *          0 2017-08-09 23:16 /user/hadoop/learning.dbdrwxr-xr-x   - root *          0 2017-08-09 22:16 /user/hadoop/test

我们也可以通过hive的元数据信息查看刚创建的数据库

• show databases  ：查看所有数据库，default为默认数据库

hive> show databases ;OKbibi_testdefaultlearningstudyTime taken: 0.015 seconds, Fetched: 5 row(s)

并没有show database database_name 命令，只有show create database database_name命令查看database_name数据库的创建脚本，不过可以通过DESC 或describe 命令查看

• desc database database_name 或describe database database_name ：查看具体某个database_name数据库

hive> desc database bi;OKbibihdfs://Master:9000/user/hadooprootUSERTime taken: 0.061 seconds, Fetched: 1 row(s)hive> describe database study;OKstudystudyhdfs://Master:9000/user/hive/warehouse/study.dbrootUSERTime taken: 0.052 seconds, Fetched: 1 row(s)

• use database_name ：切换到database_name数据库

hive> use default;OKTime taken: 0.028 secondshive> show tables;OKemptestTime taken: 0.023 seconds, Fetched: 2 row(s)hive> use bi;OKTime taken: 0.024 secondshive> show tables;OKtestTime taken: 0.017 seconds, Fetched: 1 row(s)

• drop database database_name ：删除database_name数据库，默认情况下只能删除空数据库，当数据库为非空时，可以使用drop database databse_name cascade 级联删除

hive> use bi;OKTime taken: 0.05 secondshive> show tables;OKtestTime taken: 0.051 seconds, Fetched: 1 row(s)hive> drop table test;Moved: 'hdfs://Master:9000/user/hadoop/test' to trash at: hdfs://Master:9000/user/root/.Trash/CurrentOKTime taken: 1.528 secondshive> drop database bi;Moved: 'hdfs://Master:9000/user/hadoop' to trash at: hdfs://Master:9000/user/root/.Trash/CurrentOKTime taken: 0.177 secondshive> use learning;OKTime taken: 0.039 secondshive> show tables;OKtest1Time taken: 0.043 seconds, Fetched: 1 row(s)hive> drop database learning cascade;Moved: 'hdfs://Master:9000/user/hadoop/learning.db/test1' to trash at: hdfs://Master:9000/user/root/.Trash/CurrentMoved: 'hdfs://Master:9000/user/hadoop/learning.db' to trash at: hdfs://Master:9000/user/root/.Trash/CurrentOKTime taken: 0.195 seconds

通过上述结果看：删除数据库或表，只是对应目录或文件移到了Trash(垃圾箱)目录下

hive> dfs -ls hdfs://Master:9000/user/root/.Trash/Current/user/hadoop;Found 3 itemsdrwx------   - root *          0 2017-08-10 00:01 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.dbdrwxr-xr-x   - root *          0 2017-08-10 00:01 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.db1502294480618drwxr-xr-x   - root *          0 2017-08-09 22:16 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/testhive> dfs -ls hdfs://Master:9000/user/root/.Trash/Current/user/zxy;Found 1 itemsdrwxr-xr-x   - root *          0 2017-08-09 22:34 hdfs://Master:9000/user/root/.Trash/Current/user/zxy/studenthive> dfs -ls hdfs://Master:9000/user/root/.Trash/Current/user/hadoop;Found 3 itemsdrwx------   - root *          0 2017-08-10 00:01 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.dbdrwxr-xr-x   - root *          0 2017-08-10 00:01 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.db1502294480618drwxr-xr-x   - root *          0 2017-08-09 22:16 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/testhive> dfs -ls hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.db;Found 1 itemsdrwxr-xr-x   - root *          0 2017-08-10 00:00 hdfs://Master:9000/user/root/.Trash/Current/user/hadoop/learning.db/test1

通过show databases查看删除后的结果

hive> show databases;OKdefaultstudyTime taken: 0.042 seconds, Fetched: 2 row(s)

• Alter database 语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

修改数据的create_time属性

hive> alter database study set dbproperties ('create_time' ='2017-8-10');OKTime taken: 0.046 seconds