HQL语句
删除查看语句:
show databases; #查看数据库
show tables; #查看表
drop database 数据库名称 #删除数据库;
#清空表:
truncate table 表名; #清空表;
drop table 表名称; #删除表;
创建语句:
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]
#普通创建
create table if not exists stu2(id int ,name string)
row format delimited fields terminated by '\t' location '/benchmarks';
#根据查询结果创建表
create table stu3 as select * from stu2;
#根据已经存在的表结构创建表(不包含数据)
create table stu4 like stu2;
创建内部/外部表
区别:删除表时外部表只会删除元数据不会删除数据本身.
#内部表
create table teacher (t_id string,t_name string)
row format delimited fields terminated by '\t';
#外部表
create external table teacher (t_id string,t_name string)
row format delimited fields terminated by '\t';
#内部外部之间的转换
alter table student set tblproperties('EXTERNAL'='TRUE');
hive的三种数据类型
Array: array<string>
---------------------------------------------------------
COLLECTION ITEMS TERMINATED BY ',';
---------------------------------------------------------
数据格式:
Map: map<string,string>
---------------------------------------------------------
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';
---------------------------------------------------------
数据格式:
Struct: struct<name:string, age:int>
---------------------------------------------------------
COLLECTION ITEMS TERMINATED BY ':';
---------------------------------------------------------
数据格式:
create database 数据库名称 [location hdfs路径];
创建分区表:
两种操作:
第一种,将没有分好的数据存入分区表; 新创建一个临时表,把数据存入,然后使用insert into 插入分区表中;
第二种,将现有根据字段分开的好的数据分别移动上传到分区表中.使用load data 载入数据.
--第一种--
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
#创建分区表partitioned by
create table score(s_id string,c_id string, s_score int)
partitioned by (month string)
row format delimited fields terminated by '\t';
#创建临时表
create table score_tmp2(s_id string,c_id string,
s_score int , month string)
row format delimited fields terminated by '\t';
#将表导入到临时表
load data local
inpath '/root/score.csv' into table score_tmp;
#把数据插入到分区表中.
#添加数据 此处不能加入month 列,然后 into/overwrite 追加/覆盖
insert into table score partition (month='202007')
select s_id, c_id, s_score from score_tmp2 where month='202007';
--第二种--
#创建只有一个分区的字段:
create table score(s_id string,c_id string, s_score int)
partitioned by (month string)
row format delimited fields terminated by '\t';
#创建带有多个分区的字段:
create table score2 (s_id string,c_id string, s_score int)
partitioned by (year string,month string,day string)
row format delimited fields terminated by '\t';
#载入数据:
load data local inpath '/root/score.csv'
into table score partition(month='202006');
#载入多个分区的字段数据:
load data local inpath '/root/score.csv'
into table score2 partition(year='2020',month='06',day='06');
#查看分区:
show partitions score;
#添加一个分区
alter table score add partition(month='202005');
#同时添加多个分区
alter table score add partition(month='202004')
partition(month = '202003');
#删除分区
alter table score drop partition(month = '2020-06');
构建元数据
alter table score3 add partition(month='202009')
partition(month = '202010') partition(month = '202011');
创建分桶表:
分桶表其实指的就是MR中分区操作, 将一个文件拆分为多个文件过程, 这些多个文件是放置在一个文件夹下的
#首先开启hive的桶表的支持
set hive.enforce.bucketing=true;
#默认情况下, hive不支持桶表, 如果需要使用, 必须先开启
#设置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';
#加载数据:
# 1先创建一个和桶表一致的表, 只不过这个是一个普通表:
create table course_tmp (c_id string,c_name string,t_id string)
row format delimited fields terminated by '\t';
#接着通过 load data方式 将数据加载到这个临时表:
load data local inpath '/root/hivedatas/course.csv'
into table course_tmp;
# 最后, 从临时表将数据查询出来, 然后加载到桶表中:
格式:
insert into/overwrite table 表名 + select 语句
操作:
insert into table course select * from course_tmp where t_id = '02';
内部:hive会认为对表有绝对的控制权
create external table ....
#内部转换外部
alter table student set tblproperties('EXTERNAL'='TRUE');
#相反
alter table student set tblproperties('EXTERNAL'='FALSE');
#查看表类型
desc formatted 表名;
外部:hive会认为对此表没有控制权
create external table ....
插入数据两种格式
#通过 insert into/overwrite + select 加载数据 : 重要的.....
格式:
insert into/overwrite [table] 表名 [partition(分区字段1=值1...)] + select查询语句
#通过 load data方式加载数据: 重要的.....
格式:
load data [local] inpath '路径' into table 表名 [partition(分区字段1=值1....)]
将数据导出
#操作一: 默认分割符号导出本地磁盘
insert overwrite local directory '/root/hivedatas/exporthive' select * from score;
#操作二: 采用指定分割符号导出本地磁盘
insert overwrite local directory '/root/hivedatas/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student
#注意: 以上两种操作, 如果不加 local 表示导出到HDFS中
