Hive之DDL&DML

DDL


#创建数据库(标准写法)
create database if not exists db_hive;
#创建数据库,指定在HDFS上存放的位置
create database db_hive lovation '/db_hive.db';
#显示数据库
show databases
show databases like 'db_hive*';
#查看数据库详情
desc database db_hive;
#显示数据库详细信息
desc database extended db_hive;
#切换数据库
use db_hive;
#修改数据库
alter database hive set dbproperties('createtime'='20021128');
#删除空数据库
drop database db_hive2;
#非空数据库删除
drop database db_hive cascade;

创建表


CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
#例子1 内部表
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';
#例子2 外部表
create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
#内部外部表的转换
alter table student2 set tblproperties('EXTERNAL'='TRUE');
alter table student2 set tblproperties('EXTERNAL'='FALSE');

分区表


#创建分区表
create table dept_partition(deptno int ,dname string, loc string)
partitioned by (month string)
row format delimited fields terminated by '\t';
#加载数据到分区表中
load data local inpath '/opt/module/dept.txt' into table default.dept_partition
partition(moth='200211');
#查询分区表中的数据,联合查询用union连接
select * from dept_partition where month='200211'
#增加分区
alter table dept_partition add partition(month='200211') ;
#删除分区
alter table dept_partition drop partition (month='200214');
#查看分区表有多少分区
show partitions dept_partition;
#查看分区表结构
desc formatted dept_partition;
#创建二级分区表
create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';
#加载数据到二级分区表中
load data local inpath '/opt/module/datas/dept.txt' into table
 default.dept_partition2 partition(month='200211', day='11');
#分区表和数据产生关联的三种方式
#1上传数据后修复
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
msck repair table dept_partition2;
Hive之DDL&DML

发表评论

电子邮件地址不会被公开。 必填项已用*标注

滚动到顶部