hive中基本的创建相关语句_hive 创建语句-优快云博客

本文介绍Hive SQL的基本操作，包括数据库及表的创建与删除、数据加载与查询等。涵盖内部表、外部表、分区表及分桶表的创建方法，并提供加载数据的具体示例。

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");

标题