hive 基本创建语句
强制删除数据库,包含库下面的表一起删除
drop database myhive cascade;
创建一张表
create table if not exists stu4(id int,name string) row format delimited fields terminated by '\t';
查看表的详细信息
desc formatted stu;
添加数据
load data local '/root/hivedata/stu.txt' into table stu;
外部表
create external table exstu(id int,name string)row format delimited fields terminated by '\t';
分区表
create table score(s_id int,c_id string,s_score int) partitioned by(month String) row format delimited fields terminated by '\t\;
分区表添加数据
load data local inpath '/export/server/hivedatas/score.scv' into table score partition (month='201806');
创建分桶表
== 开启hive的桶表功能==
set hive.enforce.bucketing=true;
设置reduce的个数
set mapreduce.job.reduces=3;
创建桶表
create table course(c_id String,c_name String,t_id String) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';
桶表加载数据
创建普通表
create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
普通表中加载数据
load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;
通过insert overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(c_id);
创建一个SNAPPY压缩的ORC存储方式
create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");
标题