HIVE 表&数据操作
一.HIVE
如需要查看HIVE知识点1000+,请点击下方链接。
链接: HIVE知识点1000+
本篇博客所用数据:
链接: 本篇博客所用数据,建议下载. 提取码: fyiu
二.创建内部表
- 默认内部表,会默认在指定的储存空间中建立对应的文件夹
- 只要把文件放入,表就可以读取到数据(文件需要和表结构匹配,否则会有很多空)
- 上传数据文件到该表所在的hdfs路径里
hdfs dfs -put /root/employee.txt /opt/hive/warehouse/zhu.db/employee
- 建表
create table employee(
name string,
address array<string>,
info struct<gender:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
三.CTAS – as select方式建表
create table tmp_employee as select * from employee;
四.创建外部表
- 创建表时指定的
location ''
数据储存位置只能是指定到目录,不能指定到具体文件。 - 注意:数据放在表的下一级,无论数据名是什么都不重要,数据内容是啥影响也不大顶多查询结构有很多空格这都不是事,关键是查询时
select * from 表名
此时的表名就是建表时的名字。 - 上传数据:
- 建表
create external table emp_id(
name string,
id int,
address array<string>,
personInfo struct<sex:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/usr/test/employee';
五.Hive分区
1.建表
create table employee_partition(
name string,
address array<string>,
info struct<gender:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
partitioned by (country string,add string)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
2.导入本地文件数据
load data local inpath '/root/employee.txt'
into table employee_partition
partition (country="china",add="LiaoNing");
3.导入hdfs文件数据
load data inpath '/usr/test/employee/employee_id.txt'
into table employee_partition
partition (country="china",add="NanJing");
4.浏览器显示结果
5.创建表添加分区并自行插入数据
- 建表
create table p_test(
pid int,
pname string)
partitioned by (person string)
row format delimited
fields terminated by ','
lines terminated by '\n';
- 插入数据
insert into p_test partition (person='sam') values(1,'a'),(2,'b'),(3,'c');
- 再一次插入数据前后比较
insert into p_test partition (person='bob') values(4,'d'),(5,'e'),(6,'f');
六.分桶(Buckets)
- 把一个大文件分成几个分片储存
- 分桶对应于HDFS中的文件
- 更高的查询处理效率
- 使抽样(sampling)更高效
- 根据“桶列”的哈希函数将数据进行分桶
- 分桶只有动态分桶
- SET hive.enforce.bucketing = true;
- 定义分桶
- CLUSTERED BY (employee_id) INTO 2 BUCKETS
- 分桶的列是表中已有的列,分桶数最好是2的n次方
- 必须使用INSERT方式加载数据
create external table emp_bucket(
name string,
id int,
address array<string>,
personInfo struct<sex:string,age:int>,
technol map<string,int>,
jobs map<string,string>)
clustered by(id) into 3 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/usr/test/bucket'
set hive.enforce.bucketing=true;
七.查询语句
1.*号查询
select * from employee;
2.指定字段,集合内下标查询
select name,address[0],info.gender,technol["Sales"] from employee;
3.条件查询
select name,address[0],info.gender,technol["Sales"] from employee where technol["Sales"] is not null;
八.未完待续
更多内容请点击下方链接
链接: HIVE高阶建表&分区&分桶.