//查看数据库
hive> show databases;
OK
default
Time taken:0.985 seconds, Fetched:1row(s)//创建数据库//若数据库存在会抛出异常//可使用"if not exists"不抛出异常
hive> create database ducl;
OK
Time taken:0.268 seconds
hive> create database ducl;
FAILED: Execution Error,return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database ducl already exists
hive> create database if not exists ducl;
OK
Time taken:0.007 seconds
//模糊查找数据库
hive> show databases like 'ducl*';
OK
ducl
ducl_2019
ducl_test
Time taken:0.022 seconds, Fetched:3row(s)//查看数据库描述信息
hive> describe database ducl;
OK
ducl hdfs://master:9000/user/hive/warehouse/ducl.db zjt USER
Time taken:0.022 seconds, Fetched:1row(s)//查看Hive家目录下的信息
hive> dfs -ls /user/hive/warehouse/;
Found 3 items
drwxr-xr-x - zjt supergroup 02019-03-2110:20/user/hive/warehouse/ducl.db
drwxr-xr-x - zjt supergroup 02019-03-2110:20/user/hive/warehouse/ducl_2019.db
drwxr-xr-x - zjt supergroup 02019-03-2110:20/user/hive/warehouse/ducl_test.db
//删除数据库//若要删除含有表的数据库,需要末尾加上 "cascade" 关键字
hive> drop database ducl_test;
OK
Time taken:0.432 seconds
hive> show databases;
OK
default
ducl
ducl_2019
Time taken:0.015 seconds, Fetched:3row(s)//使用数据库
hive> use ducl;
OK
Time taken:0.033 seconds
1.3.2 对表的操作
创建表
内部表被删除时,元数据信息和表数据都会被删除
外部表被删除时,只会删除元数据信息
分区表分为静态分区表和动态分区表
//创建内部表
hive> create table student(> id int,> name string,> age int)> row format delimited
> fields terminated by '\t'//字段分隔符设置> stored as textfile;
OK
Time taken:0.531 seconds
//查看表的详细信息
hive> show create table student;
OK
CREATE TABLE `student`(
`id` int,
`name` string,
`age` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://master:9000/user/hive/warehouse/ducl.db/student'
TBLPROPERTIES ('transient_lastDdlTime'='1553136204')
Time taken:0.511 seconds, Fetched:14row(s)//创建外部表
hive> create external table student2(> id int,> name string,> age int)> row format delimited
> fields terminated by '\t'> stored as textfile
> location '/ducl/student2';//指定存储路径,路径不存在会自动创建
OK
Time taken:0.068 seconds
//查看数据库中的表信息
hive> show tables;
OK
student
student2
Time taken:0.027 seconds, Fetched:2row(s)//创建分区表
hive> create table student3(> id int,> name string,> age int)> partitioned by (sex string)> row format delimited
> fields terminated by '\t'> stored as textfile;
OK
Time taken:0.094 seconds
//创建分桶表
hive> create table student4(> id int,> name string,> age int)> clustered by (id) sorted by (id desc)