今天分享HiveSql的表的创建与数据插入大法~
什么是HIVE
- Hive 由 Facebook 实现并开源
- 是基于Hadoop的一个数据仓库工具
- 作用对象为结构化数据
- 提供HQL(Hive Sql)查询功能(类似与sql)
- 底层数据存储在HDFS上
- 本质将SQL语句转为MR任务执行
- 适用于数据仓库的统计分析
Hive与RDBMS的对比
图片来源(https://cwiki.apache.org/confluence/display/Hive/Home#Home-UserDocumentation)
![52e8b79f2fdd29325a0d15426c4c6d80.png](https://i-blog.csdnimg.cn/blog_migrate/049778e0bf8c2a5e1d33152e491b72c8.jpeg)
HIVE建表格式官方说明(三种方式):
- 直接建表法
- 查询建表法
- like建表法
详情如下:
直接创建表
create table tb1( id int, name string, likes ARRAY , address MAP ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY '-'
创建外部表
create EXTERNAL TABLE tb2( id int, name string, likes ARRAY , address MAP ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':' LOCATION 'user/tb2';
查找数据并创建表(有数据)
create table tb3 as (select c.ID,c.NAME,c.AGE,o.AMOUNT from customers c JOIN orders o ON (c.ID=o.CUSTOMER_ID));
like创建表(无数据)
create table tb4 like tb1;
创建分区
create table tb5( id int, name string, likes ARRAY , address MAP ) PARTITIONED BY (sex string) ROW FORMAT DELIMITED FIELDS TEMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';
创建多个分区
create table tb6( id int, name string, likes ARRAY , address MAP ) PARTITIONED BY (sex string,age int); ROW FORMAT DELIMITED FIELDS TEMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';
添加分区,仅对元数据进行修改
ALTER TABLE tb5 ADD partition (sex='weizhi');
删除分区(数据也随之删除)
alter table tb5 DROP PARTITION(sex='boy');
删除分区(此处注意:连同下级一起删除)
ALTER TABLE tb6 DROP PARTITION (sex='boy');
导入数据
在这里大家注意啦,HIVE导入数据可以用insert导入,但是这里面不推荐使用insert导入,主要原因是HIVE属于数据仓库型,处理的数据量巨大,导入不方便,我们来看看HIVE数据导入方法~
第一种导入数据方法:
数据在本地
load data local inpath 'yourPath' into table tb6 partition (sex='boy',age=1);
数据在HDFS上
load data inpath 'path' into table tb1;
第二种导入方法
利用from进行数据查找导入
from page_view_stg pvs insert into TABLE page_view select pvs.viewTime,pvs.userid;
from (select c.ID,c.NAME,c.AGE,o.AMOUNT from customers c JOIN orders o ON (c.ID=o.CUSTOMER_ID)) e insert into table abc select *;
嗯,今天的分享就到此结束,我们下节继续~~~