hive的基本操作

最新推荐文章于 2022-04-21 18:08:46 发布

Dream__Sky_

最新推荐文章于 2022-04-21 18:08:46 发布

阅读量379

点赞数 1

本文链接：https://blog.csdn.net/Dream__Sky_/article/details/79823492

版权

1.描述

hive的表对应着hdfs的目录/文件夹

hive的数据对应hdfs的文件

如果是分区表，则分区值是子文件夹

全表扫描，读取多少条不会执行mr任务 : select * from 表名

hive将元数据存在数据库中，目前只支持mysql,derby

显示数据库：show databases 默认的为: default库

查看当前数据库的有哪些表：show tables

hive仓库数据的存放目录：/user/hive/warehouse/

加载本地数据到hive ：load data local inpath '本地数据目录' into table 表

创建数据仓库：create database 数据仓库名

使用某个数据仓库：use 数据仓库名

hive的默认保存元信息数据库，运行hive后：bin下会产生文件夹：metastore_db/seg0 下面的文件即保存了元数据，但是乱码
另一个缺点：只支持一个连接。另外在哪个目录下运行hive就在哪个目录下产生metastore_db

mysql存放hive的元数据：配置好后，mysql下会生成一个hive数据库，本数据库默认有20多张表，查看元数据：
查看TBLS（表对应的id，类型，hdfs上的路径...），COLUMNS_V2（根据表对应的id查看表的字段及类型）,SDS（hdfs上的路径，比如此表下分区的路径）

外部表的用途：先有数据（比如历史数据），再创建表做分析

hive下支持hadoop的命令

外部表：hive下使用hadoop命令创建目录并上传文件，并指定表所加载的文件目录，使用hive可以查到。实际上内部表也可以。

当使用mysql存储元数据时候，并且当使用hadoop命令创建并上传文件的时候。另需要命令将元数据加载到mysql中

查看格式化后的表结构：desc formatted 表名

删除空的数据库：DROP DATABASE IF EXISTS 库名;

删除有数据的数据库：DROP DATABASE IF EXISTS 库名 CASCADE;

-----------------------------------------------------------

2.表操作

显示所有表：show tables;

显示表的描述信息：desc [extended,formatted] 表名;

显示建表语句：show create table 表名;

删除表及表中的数据：drop table if exists 表名;

建表方式：

1.直接建表。

例如：create table aaa (id bigint,order_id bigint,order_sn string,goods_name string,goods_sn string,goods_number int,market_price double,shop_price double,goods_price double,discount double,discount_fee double,payment double,outer_goods_sn string); （注意：字段与与属性之间为空格，而不是制表符）

2.由一个表创建另一张表：create table 表2 like 表1 but只是复制了表结构，没有数据。所以不执行mr

3.从其他表查询创建表：create table 表2 as select 字段1，字段2 from 表1 表结构，指定的数据都要复制，有mr

hive不同文件读取对比

stored as textfile 以文本文件格式存储
hadoop fs -text 查看文件

stored as sequencefile 以序列化格式存储
hadoop fs -text

stored as rcfile
hive -service rcfilecat path 使用hive命令读取

stored as inputformat 'class' 流的方式存储
outformat 'class'

hive分区表操作

增加分区
alter table 表名 add if not exits partition (分区名='xxx')

删除分区
alter table 表名 drop if exits partition (分区='xxx')

查看当前表的分区
show partitions 表名

--------------------------------------

3.数据加载

内表数据加载

创建表时加载：
create table 表2 as select 列1,列2 from 表1

创建表时指定数据位置
create table 表名() location ''

本地数据加载
load data local inpath '本地数据路径' [overwrite] into table 表名

加载hdfs数据
load data inpath 'hdfs数据路径' [overwrite] into table 表名（注意：这个操作是移动数据，而非复制）

使用hadoop命令拷贝数据到指定位置：

由查询语句加载数据

insert [overwrite|into] table 表1
select 列1,列2
from 表2
where

外表数据加载

创建表时指定数据位置
create external table 表名() location ''

插入查询，同内表
insert [overwrite|into] table 表1
select 列1,列2
from 表2
where

使用hadoop命令拷贝数据到指定位置

分区表数据加载

本地数据加载
load data local inpath '本地路径' [overwrite] into table 表名 partition (分区名='')

加载hdfs数据
load data inpath 'hfds数据路径' [overwrite] into table 表名 partition (分区名='')

由查询语句加载数据
insert [overwrite] into table 表1 partition (分区名='')
select 列1,列2
from 表2
where...

hive数据加载注意的问题

分隔符问题，且分隔符默认只有单个字符

数据类型对应问题
load数据，字段类型不能互相转化时，查询返回null
select查询插入，字段类型不能相互转化时，插入数据为null

select查询插入数据，字段值顺序与表中字段顺序一致，名称可不一致
hive在数据加载时不做检查，查询时检查

外部表分区需要添加分区才能看到数据

-------------------------------------------

4.数据导出

通过hadoop命令
get: hadoop fs -get hive目录本地目录

通过insert...
insert overwrite [local] directory '指定目录'
[row format delimited fields terminated by '\t']
select 列1,列2
from 表

---------------------------------

5.分区

如果分区是可以确定的话，千万不要用动态分区，动态分区的值是在reduce运行阶段确定的。也就是会把所有的记录distribute by。

可想而知表记录非常大的话，只有一个reduce去处理，那简直是疯狂的。如果这个值唯一或者事先已经知道，比如按天分区（i_date=20140819）

那就静态分区吧。静态分区在编译阶段已经确定，不需要reduce处理。

1.插入动态分区：

set hive.exec.dynamic.partition.mode=strict;

insert overwrite table a_test partition (i_date)

select id,page,extract,label_count,weight

from b.test_b where request_date_i = '20140817';

2.插入静态分区：

insert overwrite table a_test partition (i_date='20140817')

select id,page,extract,label_count,weight

from b.test_b where request_date_i='20140817';

增加分区
alter table 表名 add if not exits partition (分区名='xxx')

删除分区
alter table 表名 drop if exits partition (分区='xxx')

查看当前表的分区，没有就报错
show partitions 表名

查看表分区定义：

describe extended 表名 partition (ds='2008-08-08');.分区

-----------------------------------------------

6.表属性操作

修改表名
alter table 旧表名 raname to 新表名;

修改列名
alter table 表名 change column 旧列名,新列名 int(新列名类型) comment '声明解释'

更改某一个字段的类型：alter table ori_e3_by_order_info change 旧字段旧字段 int;

更改某一字段名称及类型：alter table ori_e3_by_order_info change 旧字段新字段 int;

增加列
alter table 表名 add columns (列1 string(列1类型) comment '声明解释',列2 long(列2类型) comment '声明解释');

修改location
alter table 表名 [partition()] set location 'path'

内部表转化为外部表
alter table 表名 set tblproperties ('external' = 'true');

外部表转化为内部表
alter table 表名 set tblproperties ('external' = 'false');

--------------------------------------------------

7.Order by

查询操作
group by , order by , join , distribute by , sort by , cluster by , union all

按照某些字段排序
样例：
select 列1,列2,列3
from
where condition
order by 列1,列2 [asc|desc]

注意：
order by 后面可以有多列进行排序，默认按字段排序
order by 为全局排序
order by 需要reduce操作，且只有一个reduce，与配置无关

---------------------------------------------------

8.Group by

按照某些字段的值进行分组，有相同值放到一起

样例
select 列1,列2 count(1),sel_expr(聚合操作)
from 表名
where 条件 //map中执行
group by 列1,列2 //reduce中执行

注意

select 后面非聚合列必须出现在group by 中
除了普通列就是一些聚合操作
group by 后面也可以跟表达式，比如substr(列)

特性：

使用了reduce操作，受限于reduce数量，设置reduce参数mapred.reduce.tasks
输出文件个数与reduce数相同，文件大小与reduce处理的数量有关

问题：网络负载过重

-------------------------------------------

9.join

表连接：

两个表m,n之间按照on条件连接，m中的一条记录和n中的一条记录组成新纪录

join等值连接，只有某个值在m和n中同时存在

left outer join左外连接，在表中的值无论是否在右表中存在时，都输出，右表中的值只有在左表存在时才输出

rigth outer join 和 left outer join相反

left semi join 类似exists

mapjoin在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作。

样例：

select m.col as col,m.col2 as col2,n.col3 as col3
from
(select col,col2
from test
where...(map端执行)
)m (左表)
[left outer|right outer|left semi] join
n (右表)
on m.col = n.col
where condition (右表)

mapjoin(map side join)：

在map端把小表加载到内存中，然后读取大表，和内存中的小表完成连接操作

其中使用了分布式缓存技术

优缺点：

不消耗集群的reduce资源(reduce相对紧缺)

减少了reduce操作，加快程序执行

降低网络负载

占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次

生成较小的文件

--------------------------------------------------

10.distribute sort

distribute分散数据：

distribute by col
按照col列把数据分割到不同的reduce

sort排序：

sort by col2
按照col列把数据排序

select col1,col2 from m
distribute by col1
sort by col1 asc,col2 desc;

两者结合出现，确保每个reduce的输出都是有序的

对比：

distribute by 与 group by

都是按key值划分数据
都使用reduce操作
唯一不同，distribute by只是单纯的分散数据，而group by把相同key的数据聚集到一起，后续必须是聚合操作

order by 与 sort by

order by是全局排序
sort by只是确保每个reduce上面输出的数据有序，如果只有一个reduce时，和order by作用一样

distribute by应用场景：

map输出的文件大小不均
reduce输出的文件不均
小文件过多

文件超大

----------------------------------------------

11.Union all

多个表的数据合并成一个表，hive不支持union

样例：

select col
from (
select a as col from t1
union all
select b as col from t2
) tmp

要求:

字段名称一样
字段类型一样
字段个数一样
子表不能有别名
如果需要从合并之后的表查询数据，那么合并的表必须要有别名

------------------------------------------------

12.hive函数基本分类

函数：

内置函数：

简单函数 map阶段
聚合函数 reduce阶段
集合函数 map阶段
特殊函数

自定义函数：

UDF map阶段
UDAF reduce阶段

cli命令：

显示当前会话有多少函数可用
show functions;

显示自定义函数：
show functions status

显示函数的描述信息
desc function 函数名;

显示函数的扩展描述信息
desc function extended 函数名;

------------------------------------------

14.索引

1.创建表

create table TBL_FACT_SYMBIAN_PACKET(
PROVICE_FK INT,
DATE_FK INT,
MODEL_FK INT,
COUNTRY_FK INT,
NETWORK_FK INT,
OSLAG_FK INT,
OS_FK INT,
NQ_SOFTWARE_VER INT,
SUBCOOP_FK INT,
NQ_SOFTWARE_TYPE_FK INT,
SCANTYPE_FK INT,
SCANID STRING,
NAME STRING,
SECURITY STRING,
CA_ISSUER STRING,
CA_SUBJECT STRING,
HAS_NOTE STRING,
VIRUS_NAME STRING
)
partitioned BY(dt STRING) row format delimited fields terminated by '\t'

STORED AS textfile;

2.加载本地数据到分区表
load data local inpath '/home/boss/symbian_pkgs_20110529.log.hive' overwrite into table TBL_FACT_SYMBIAN_PACKET partition(dt='2011-05-29');

3.创建索引
create index index_model_fk on table TBL_FACT_SYMBIAN_PACKET(model_fk) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED REBUILD;

4.更新数据
ALTER INDEX index_model_fk ON TBL_FACT_SYMBIAN_PACKET REBUILD;

5.删除索引
drop index index_model_fk on tbl_fact_symbian_packet;

6.查看索引
show index on tbl_fact_symbian_packet;

-------------------------------------------------------------

15.常用语句实例

1.按页面类型统计 pv

select pageType, count(pageType) from ext_trackflow where statDate = '20140521' group by pageType;

note：一般select查询会扫描整个表，使用partition by 子句建表，查询就可以利用分区剪枝（input pruning）的特性

hive：当前的实现是，只有分区断言出现在离from子句最近的那个where子句中，才会启用分区剪枝

2.导出查询结果到本地的两种模式

insert overwrite local directory '/home/jun06/tmp/110.112.113.115' select area1,count(area1) from ext_trackflow where statDate = '20140521' group by area1 having count(area1) > 1000;

hive -e 'use ecdata; select area1,count(area1) from ext_trackflow where statDate = '20140521' group by area1 having count(area1) > 1000;' > a.txt

3.map数据结构的查询与使用

select trackURLMap,extField,unitParamMap,queryParamMap from ext_trackflow where statDate = '20140521' and size(unitParamMap)!=0 limit 10;

4.下面的查询语句查询销售记录最大的5个代表

set mapred.reduce.task = 1;
select * from test sort by amount desc limit 5;

5.将同一表中数据插入到不同表，路径中：

from src

insert overwrite table dest1 select src.* where src.key <100

insert overwrite table dest2 select src.key,src.value where src.key >= 100 and src.key <200

insert overwrite table dest3 partition(ds='2008-04-08',hr='12') select src.key where src.key >= 200 and src.key < 300

insert overwrite local directory '/tmp/dest4.out' select src.value where src.key >= 300;

6.用streaming方式将文件流直接插入文件

from invites a insert overwrite table events select transform(a.foo,a.bar) as (oof,rab) using '/bin/cat' where a.ds > '2008-08-08';

7.hive只支持等值连接(equality joins),外连接(outer joins) 和(left semi joins).hive不支持所有非等值的连接，因为非等值连接非常难转化为mapreduce任务

8.join查询时，需要注意几个关键点

只支持等值join

select a.* from a join b on (a.id = b.id)

select a.* from a join b on (a.id = b.id and a.department = b.department)

可以join多于2个表，例如：

select a.val,b.val,c.val from a join b on (a.key = b.key1) join c on (c.key = b.key2);

note：如果join中多个表的join key是同一个，则join会转化为单个mapreduce任务

9.left ,right和full outer

select a.val,b.val from a left outer join b on (a.key=b.key)

如果你想限制join的输出，应该在where子句中写过滤条件--或者是join子句中写
容易混淆的问题是表分区的情况

select c.val,d.val from c left outer join d on (c.key = d.key) where c.ds='2010-07-07' and d.ds = '2010-07-07';

如果d表中找不到对应的c表的纪录，d表的所有列都会列出null，包括ds列。也就是说，join会过滤掉d表中不能找到匹配c表join key的所有记录。
这样的话，left outer就使得查询结果与where子句中的d.ds = '2010-07-07'无关

解决办法：

select c.val,b.val from c left outer join d on (c.key=d.key and d.ds='2010-07-07' and c.ds='2010-07-07');

这一查询的结果预先在join阶段过滤的，所以不会存在上述问题。这一逻辑也可以应用于rigth和full类型的join中。

hive不支持传统的等值连接

SQL中对两表内联可以写成：select * from dual a,dual b where a.key = b.key;

hive中应为：select * from dual a join dual b on a.key = b.key;

join中处理null值的语义区别：

SQL标准中，任何对null的操作（数值比较，字符串操作等）结果都为null。hive对null值处理的逻辑和标准基本一致，除了join时的特殊逻辑。

这里的特殊逻辑指的是，hive的join中，作为join key的字段比较，null = null是有意义的，且返回值为true。检查以下查询：

select u.uid,count(u.uid)

from t_weblog l join t_user u on (l.uid = u.uid) group by u.uid;

查询中，t_weblog表中uid为空的记录将和t_user表中uid为空的记录做连接，即l.uid=u.uid=null成立。

如果需要与标准一致的语义，我们需要修改查询手动过滤null值得情况：

select u.uid,count(u.uid)

from t_weblog l join t_user u

on (l.uid=u.uid and l.uid is not null and u.uid is not null)

group by u.uid;

实践中，这一语义区别也是经常导致数据倾斜的原因之一。