创建数据库/表
create database if not exists 库名;
create table if not exists 表名;
数据库切换
use 库名;
删除/强制删除
drop database 库名;如果库里面有东西删不掉,需要强制删除
drop database 库名 cascade;
drop table 表名 ;
drop table 表名 cascade;
truncate table 表名; -- 清空表数据
msck repair table table_name;恢复分区
查看库 /表
show databases;
show tables;
show partitions dept_partition;查看分区
select current_database();当前使用的数据库
describe database 库名; -- 可以查看数据库更多的描述信息
desc formatted 表名;查询表类型
desc database 库名;查看数据库详情
desc 表名;查看表结构
插入数据
insert into table 表名 values(1,'zhangsan');
insert overwrite table 表名 values(2,'lisi');
修改数据库的使用者:
alter database myhivebook set owner user dayongd;
alter table 表名 rename to 新表名;
alter table 表名 set fileformat 文件格式; 修改文件格式
alter table 表名 change name new_name string;修改列属性
alter table 表名 add columns(age int,gender string); 添加列
alter table 表名 replace columns(id int,username string);删除替换--原有的所有字段被替换成括号内字段
alter table t1 add partition (pt_d = ‘333333’);添加分区
内/外部表转换
alter table 表名 set tblproperties('EXTERNAL'='TRUE');//区分大小写 内部表转换为外部表
alter table 表名 set tblproperties('EXTERNAL'='FALSE');外部表转为外部表
内部表(管理表):
hdfs中所属数据库目录下的子文件。
删除表时,数据也一起删除
创表语句:
create table studentp(
id int,
name string,
likes array<string>,
address map<string,string>,
)
row format delimited fields terminated by ',' //-- 指定列分隔符语法
collection items terminated by '-' //分隔集合和映射
map keys terminated by ':' //分隔集合和映射
lines terminated by '\n'; //分隔行
创建内部表,此内部表会在数据的子目录下,有和表名相同的目录。
添加数据 :
load data [local] inpath ‘文件所处的路径’ into [overwriter] table A;
有local:从linux下拷贝数据,
无local:从hdfs上剪切数据到hive的指定位置下。
有overwrite:覆盖原有的数据
无overwrite:追加
外部表:External Tables
数据保存在指定位置的HDFS路径中
Hive不完全管理数据,删除表(元数据)不会删除数据
多用于ods层,
create external table if not exists A(
id string,
name string,
age string)
row format delimited
fields terminated by ','
lines terminated by '\n';
location '/home/hadoop/hive/warehouse/student';
--------------------------------------------------------------
-- 查询表的类型 desc formatted student;
location '数据保存的指定路径'
上传数据到这个目录下:
load data [local] inpath '文件所处的路径' into [overwriter] table A;
或者
hdfs dfs -put 源数据路径 hdfs文件路径(local下的路径)
此时数据可以自动映射为一张表
可以使用select * from A 查看这张映射的表。
临时表:
临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法
表只对当前session有效,session退出后自动删除
表空间位于/tmp/hive-<user_name>(安全考虑)
如果创建的临时表表名已存在,实际用的是临时表
CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..
————————————————
分区表(重要):hadoop fs -chmod -R 777 /
分区:就是在表的目录下根据一些特定的条件再创建一些子目录,这些子目录下有我们原始数据的划分号的一部分数据。
查询时我们可以通过分区列和常规列来查询,大大提高查询速度。
分区又分为是动态分区和静态分区
但是不管是静态还是动态,都要先创建分区表(创表方式一样):最好创建外部表,安全,create external table if not exists A
create table student2(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';
注意:根据创建的表中的某个字段进行分割时,在创建表时不可以在添加这个字段,如上表中根据sex分区,创建的表的字段不能再有sex
表创建完成后,我们要导入数据,然后映射出一张表,供我们后期使用:
此时静态分区和动态分区才会有很大的不同,或者说是依据导数据的方式来判断 是动态分区还是静态分区:
静态分区:是自己定义数据 多用于增量表(不断增加表的内容)比如新闻表,每天都会变化增加需要事先知道有多少分区,每个分区都要手动插入。
如果你的原始数据文件已经根据分区关键字,如sex 分好了,即男是一个文件 女是一个文件
导入分区数据 指定分区
load data local inpath '/opt/tmp/student.txt' into table student2 partition(age=10);
load data local inpath '/opt/tmp/student.txt' into table student2 partition(age=20);
load data [local] inpath '数据存放的路径' into [overwriter] table A。
如果是一个完整的大文件
此时可以使用如下方法导入://注意查询的字段要与创建分区表时的字段一致//产生一级子目录,需手动创建每一个分区,当分区交多时,麻烦
insert into table employee_hrpar partition(sex='male') select id,username,bir from ods_users where sex='male';
insert into table employee_hrpar partition(sex='female') select id,username,bir from ods_users where sex='female';//产生二级子目录
insert into table empar partition (year=2015,month=11) select name,id,number from employee_hr where year(start_date)=2015 and month(start_date)=11;
加载数据到二级分区表中
load data local inpath '/opt/datas/dept.txt' into table dept_partition2 partition(month='201909', day='13');
添加分区
alter table dept_partition add partition(month='201906') ;
alter table dept_partition add partition(month='201905') partition(month='201904');
删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');
动态分区:事先先用group 或者distinct 看一下字段值的种类,多用于每年 每月 每日 等 多用于全量导入 数据量不能太大
首先设置这两个配置:(短暂性设置,关闭后需重新设置)
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
创建分区表都是上面的创建方式
添加数据的方式如下:
再次之前可以使用groupby 或者 distinct 了解下数据信息,方便下面的分区.
//不需要手动一个一个分区,可以根据关键字自动分区。//此时select中的字段要和创建表时的字段加上partition的关键字的一致。根据一个字段分区
insert into table myuser partition(sex) select * from ods_users;//根据两个字段分区
insert into table empar partition(year,month) select name,id,number,year(start_date),month(start_date) from employee_hr;
Hive的数据分桶
1.分桶对应于HDFS中的文件更高的查询处理效率
2.使抽样(sampling)更高效
3.一般根据"桶列"的哈希函数将数据进行分桶
1.什么叫分桶: 类似分区,根据表中的某一字段进行哈希后, 分到不同的桶里。
2.为何要分桶:
1.分区满足不了用户分隔开数据的意愿,分区有数量限制, Hive会阻止过多小分区。
2.因此有了分桶,可将数据分到固定数目的桶中。没有数据波动。
只有动态分桶,开启
set hive.enforce.bucketing = true;
clustered by (employee_id) into 2 buckets;定义分桶
必须使用INSERT方式加载数据
随机抽样基于整行数据
select * from table_name tablesample(bucket 3 out of 32 on rand()) s;
随机抽样基于指定列(使用分桶列更高效)
select * from table_name tablesample(bucket 3 out of 32 on id) s;