首页  思维导图  详情

Hive

2020-06-09 09:45:14   2  举报





AI智能生成

Hive知识点覆盖

hive

数据分析

软件开发

作者其他创作

大纲/内容

hive的架构

表操作

create database[ if not exists ] db_hive;

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] row format delimited fields terminated by “分隔符” [STORED AS file_format] [LOCATION hdfs_path]

alter table student set tblproperties('EXTERNAL'='TRUE');

create table student_partition2( id int, name string, age int) partitioned by (month string, day string) row format delimited fields terminated by '\t';

desc [ formatted ] student_partition3;

alter table student_partition3 add columns(address string);

alter table student_partition3 change column address address_id int;

alter table student_partition3 replace columns(deptno string, dname string, loc string);

alter table student_partition1 add partition(dt='20170602') partition(dt='20170603');

alter table student_partition1 drop partition (dt='20170602'),partition (dt='20170603');

show partitions student_partition1;

复合类型

array

例如：存入 a:b:c

collection items terminated by ':'

array[0], array_contains('a'),   size(colName)

map

例如：存入    f:张#m:黄#s:李

collection items terminated by '#'

may keys terminated by  ':'

map_keys(colName),  map_values(colName),  map['key'],    size(colName)

struct

表数据导入方式

load data local inpath '/,,,'  overwrite | into table  tbName partition（dt="20190505"）

insert into table student_partition1 partition(dt="2019-07-08") select * from student1;

create table if not exists tableName as select id, name from tableName;

create table if not exists student1( id int, name string) row format delimited fields terminated by '\t' location '/user/hive/warehouse/student1';

create table student2 like student1; export table student1 to '/export/student1'; import table student2 from '/export/student1';

表数据的导出方式

insert overwrite local directory '/opt/bigdata/export/student' row format delimited fields terminated by ',' select * from student;

hdfs dfs -get /user/hive/warehouse/student/student.txt /opt/bigdata/data

bin/hive -e 'select * from default.student;' > /opt/bigdata/data/student1.txt

bin/hive -f sql文件 > /opt/bigdata/data/student1.txt

export table default.student to

表分区

静态分区

动态分区

创建普通表t_order

创建目标分区表  order_dynamic_partition

向普通表t_order加载数据

动态加载数据到分区表中

set hive.exec.dynamic.partition=true; //使用动态分区

set hive.exec.dynamic.partition.mode=nonstrict; //非严格模式

注意字段查询的顺序，分区字段放在最后面。否则数据会有问题。

insert into table order_dynamic_partition partition(order_time) select order_number,order_price,order_time from t_order;

将数据直接上传到分区目录（hdfs）上，让分区表和数据产生关联有哪些方式

上传数据后修复表

dfs -mkdir  -p  分区目录

dfs -put 分区目录

msck repair table 表名  #刷新元数据

上传数据后添加分区

dfs -mkdir -p 分区目录

dfs -put 分区目录

alter table  表名  add partition()

分桶表

分桶是相对分区进行更细粒度的划分

set hive.enforce.bucketing=true;== 开启对分桶表的支持

set mapreduce.job.reduces=4;== 设置与桶相同的reduce个数（默认只有一个reduce）

--分桶表 create table user_buckets_demo(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';

--普通表 create table user_demo(id int, name string) row format delimited fields terminated by '\t';

load data local inpath '/opt/bigdata/data/buckets.txt' into table user_demo;

insert into table user_buckets_demo select * from user_demo;

抽样查询桶表的数据：tablesample抽样语句，语法：tablesample(bucket x out of y) - x表示从第几个桶开始取数据 - y表示桶数的倍数，一共需要从 ==桶数/y== 个桶中取数据

select * from user_buckets_demo tablesample(bucket 1 out of 2) -- 需要的总桶数=4/2=2个 -- 先从第1个桶中取出数据 -- 再从第1+2=3个桶中取出数据

表数据压缩

Hive表中间数据压缩：shuffle阶段落地到磁盘的数据

#设置为true为激活中间数据压缩功能，默认是false，没有开启 set hive.exec.compress.intermediate=true;

#设置中间数据的压缩算法 set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

Hive表最终输出结果压缩：输出到HDFS的数据

set hive.exec.compress.output=true;

set mapred.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

文件存储格式

textfile

sequencefile

orc

parquet

自定义函数

UDF 一进一出

定义一个类继承==org.apache.hadoop.hive.ql.UDF

重载evaluate方法

将程序打成jar包上传到linux服务器

在hive的命令行窗口创建函数

add jar xxxxx.jar== (linux上jar包的路径)

create [temporary] function [dbname.]function_name AS class_name;

- hive命令行中删除函数

Drop [temporary] function [if exists] [dbname.]function_name;

创建永久函数

create function toUpper as 'com.kaikeba.udf.MyUDF' using jar 'hdfs://node1:9000/jars/hive_udf.jar';

UDAF 多进一出

UDTF一进多出

序列化/反序列化：SerDe

通过MultiDelimitSerDe 解决多字符分割场景

create table t1 (id String, name string) row format serde 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ("field.delim"="##");

通过RegexSerDe 解决多字符分割场景

create table t2(id int, name string) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "^(.*)\\#\\#(.*)$");

通过JsonSerDe格式存储text文件

CREATE TABLE t3(id int, name string) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;

使用 json函数操作json格式数据

get_json_object

select get_json_object(jsoncontext,"$.name") as name from t4;

json_tuple

select json_tuple(jsoncontext,"id","name") as (id,name) from t4;

企业级调优

Fetch抓取：在全局查找、字段查找、limit查找等都不走mapreduce

hive.fetch.task.conversion设置成 =》 more

本地模式

set hive.exec.mode.local.auto=true;

--设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式， --默认为134217728，即128M set hive.exec.mode.local.auto.inputbytes.max=50000000;

--设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式， --默认为4 set hive.exec.mode.local.auto.input.files.max=5;

表的优化

小表、大表 join

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率

使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce

大表 join 大表

空 key 过滤

空 key 转换

可以将表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的 reducer 上

map join

把小表全部加载到内存在map端进行join，避免reducer处理

set hive.auto.convert.join = true;

大表小表的阈值设置（默认25M一下认为是小表）

set hive.mapjoin.smalltable.filesize=25000000;

group By

开启Map端聚合参数设置

--是否在Map端进行聚合，默认为True set hive.map.aggr = true; --在Map端进行聚合操作的条目数目 set hive.groupby.mapaggr.checkinterval = 100000; --有数据倾斜的时候进行负载均衡（默认是false） set hive.groupby.skewindata = true;

count(distinct)

ount distinct使用先group by 再count的方式替换

--每个reduce任务处理的数据量默认256000000（256M） set hive.exec.reducers.bytes.per.reducer=32123456;

select count(distinct ip ) from log_text; 转换成 select count(ip) from (select ip from log_text group by ip) t;

尽量避免笛卡尔积，即避免join的时候不加on条件，或者无效的on条件

使用分区剪裁、列剪裁

只获取需要的列的数据，减少数据输入

行过滤

并行执行

set hive.exec.parallel=true;

--同一个sql允许最大并行度，默认为8。 set hive.exec.parallel.thread.number=16;

严格模式