Hive开发--Hive--DML（五）

作者: 无剑_君 | 来源:发表于2019-12-18 14:09 被阅读0次

Hive开发--Hive--DML（五）
Hive | Hive 安装详解
Hive介绍与核心知识点
Hive的Transform和UDF
大数据开发之Hive优化篇8-Hive Job优化
Hive开发--Hive简介
Practical Hive - 2016.pdf Hive实战
hbase使用es做二级索引-hive同步
HIVE UDTF 自定义函数
##[优化]开发高效的hive程序，hive优化

一、DML 操作

操作手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual

# 启动服务
hive --service metastore &
hive --service hiveserver2 &
# 使用beeline
beeline -u jdbc:hive2://192.168.71.130:10000

（一） LOAD DATA

1. 默认记录和字段分隔符

最熟悉的应该是以逗号或制表符分隔的文本格式CSV、TSV，当然在Hive中，这些文本格式都是被支持的。
那么Hive中还会支持其他什么控制字符的文本格式呢？
默认：
\n 换行符是可以支持的，因为每一行都可以被认为是一条记录
^A（Ctrl+A）分隔字段，在 CREATE TABLE 语句中可以使用八进制编码（\001）表示
^B 分隔 ARRAY 、MAP或者 STRUCT 中的元素，键值对之间的分隔，使用八进制编码（\002）表示
^C 用于 MAP 中键和值之间的分隔，使用八进制编码（\003）表示

2. 加载复合数据类型

1)数组

创建数据库表，以array作为数据类型

create table 
person(name string,work_locations   array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY'\t'
COLLECTION ITEMS TERMINATED BY ',';

数据

biansutao    beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan

入库数据

LOAD DATA LOCAL INPATH '/root/person.txt' OVERWRITE INTO TABLE person;

查询

hive>select * from person;
hive>select name from  person;
hive>select work_locations[0] from person;
hive>select work_locations from person;  
hive>select work_locations[3] from person;
hive>select work_locations[4] from person;

2)Map

创建数据库表

create table
score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY'\t'
COLLECTION ITEMS TERMINATED BY','
MAP KEYS TERMINATED BY':';

数据

biansutao   '数学':80,'语文':89,'英语':95
jobs'语文':60,'数学':80,'英语':99

入库数据

LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;

查询

hive>select * from score;
hive>select name from  score;
hive>select t.score from score t;
hive>select t.score['语文'] from score t;
hive>select t.score['英语'] from score t;

3)Struct

创建数据表

CREATE TABLE
test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY'\t'
COLLECTION ITEMS TERMINATED BY ',';

数据

1 english,80
2 math,89
3 chinese,95

入库

LOADDATA LOCALINPATH '/home/hadoop/test.txt'OVERWRITE INTOTABLE test;

查询

hive>select* from test;
hive>select course from test;
hive>select t.course.course from test t;
hive>select t.course.score from test t;

2. 本地导入

将插入下列数据到表中。在/home/user目录中名为sample.txt的文件。

1201    Gpal    45000   Technica lmanager
1202    Manisha 45000    Proof reader
1203    Masthanvali     40000   Technical writer
1204    Kiran   40000   Hr Admin
1205    Kranthi 30000   Op Admin

加载本地文件：

hive> load data local inpath '/root/sample.txt' overwrite into table employee;

3. HDFS导入

如果不加local则为加载HDFS文件。
Hive的数据导入只是复制或移动文件，并不对数据的模式进行检查，对数据模式的检查要等到查询时才进行，这就是Hive采用的“schema on read”加载方式。这种方式可以大大提高加载数据的效率。
要先上传数据到HDFS中。

hive> dfs -put data.txt /hive_input_data

查看文件:

hive> dfs -ls /;

(二) 插入

1. insert into

Insert into语句，它的作用是在一个表格里面追加数据。
语法：

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
  
Where values_row is:
( value [, value ...] )
where a value is either null or any valid SQL literal

1) 普通记录

创建表：

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
  CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;

插入记录：

INSERT INTO TABLE students
  VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);

2) 分区记录

创建表：

CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING)
  PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;

插入带分区的记录：

INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23')
  VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);

多条记录带分区

INSERT INTO TABLE pageviews PARTITION (datestamp)
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');

多条记录不带分区

INSERT INTO TABLE pageviews
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');

2. insert overwrite

1)加载数据

hive> insert into table list1 partition (gender='male') select name, birthday from list where gender='male';
hive> insert into table list1 partition (gender='female') select name, birthday from list where gender='female';

在insert的时候需要注意，如果目标表有分区，那么需要指定当前插入的是哪个分区的数据；
在进行插入的时候，分区字段是不需要在后续的select中的，其会自动存储为partition后的字段指定的值，而后续的select语句也只需要包含其余的字段即可，如这里的select语句只包含了name和birthday字段。

2)导出数据

insert overwrite directory '/user/eg2/' select * from list;

这里overwrite不能省略；
overwrite后可接local关键字，如果有该关键字，那么后面的路径则表示本地路径，否则表示HDFS文件系统上的路径。

3. 多表插入

创建表，intable:
表结构：

eid                     int
ename                   string
salary              decimal(10,2)
desc                    string

表数据：

1       刘帽    10000.12        我是北京人
2       王这    5000.00 我是天津人
3       李想    7000.00 我是河北人

将intable表数据插入到：mutil1与mutil2中。
插入多表记录:

from intable1 
insert overwrite table mutil1 select eid,ename,salary,desc
insert overwrite table mutil2 select eid,ename,salary,desc;

insert导出数据
HDFS导出

insert overwrite directory '/user/eg2/' select * from list;

本地导出

hive> insert overwrite local directory '/testdata/data' select * from intable1;

这里overwrite不能省略；
overwrite后可接local关键字，如果有该关键字，那么后面的路径则表示本地路径，否则表示HDFS文件系统上的路径。

(三)事务配置

需设置：

set hive.support.concurrency=true;
set hive.enforce.bucketing=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.txn.manager= org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on= true;
set hive.compactor.worker.threads= 1;

修改：

2278行
<property>  
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
 2882行 
<property>
  <name>hive.txn.manager</name>
 <value>org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager</value>
</property>
为
<property>   
  <name>hive.txn.manager</name>  
  <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
2966 行
<property>      
  <name>hive.compactor.initiator.on</name>
  <value>true</value>
</property>
2976行
<property>  
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>

1. 创建表

只有ORCFileformat支持AcidOutputFormat，建表时必须指定参数('transactional' = true)
必须添加桶。

hive> create table test(id int ,name string ) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

插入数据：

hive>insert into table test values (1,'row1'),(2,'row2'),(3,'row3');

2. 临时设置

# 设置为nonstrict模式：
set hive.exec.dynamic.partition.mode=nonstrict;

# 启用桶：
hive> set hive.enforce.bucketing=true;

(四)更新

必须配置事务

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

示例：
要修改的列不能是桶定义列。

hive> update test set name='abc' where id=2;

（五）合并

语法：

MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

应用于数据清洗。
表1:

CREATE TABLE transactions(
ID int,
TranValue string,
last_update_user string)
PARTITIONED BY (tran_date string)
CLUSTERED BY (ID) into 5 buckets 
STORED AS ORC TBLPROPERTIES ('transactional'='true');

# 批量插入
INSERT INTO transactions PARTITION (tran_date) VALUES
(1, 'value_01', 'creation', '20170410'),
(2, 'value_02', 'creation', '20170410'),
(3, 'value_03', 'creation', '20170410'),
(4, 'value_04', 'creation', '20170410'),
(5, 'value_05', 'creation', '20170413'),
(6, 'value_06', 'creation', '20170413'),
(7, 'value_07', 'creation', '20170413'),
(8, 'value_08', 'creation', '20170413'),
(9, 'value_09', 'creation', '20170413'),
(10, 'value_10','creation', '20170413');

表2

CREATE TABLE merge_source(
ID int,
TranValue string,
tran_date string)
STORED AS ORC;

INSERT INTO merge_source VALUES 
(1, 'value_01', '20170410'),
(4, NULL, '20170410'),
(7, 'value_77777', '20170413'),
(8, NULL, '20170413'),
(8, 'value_08', '20170415'),
(11, 'value_11', '20170415');

合并:执行第一部分可以。

MERGE INTO 
transactions AS T 
USING merge_source AS S
ON T.ID = S.ID and T.tran_date = S.tran_date
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.TranValue, 'merge_insert', S.tran_date);
WHEN MATCHED AND (T.TranValue != S.TranValue AND S.TranValue IS NOT NULL) THEN UPDATE SET TranValue = S.TranValue, last_update_user = 'merge_update'
WHEN MATCHED AND S.TranValue IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.TranValue, 'merge_insert', S.tran_date);

显示合并

SELECT * FROM merge_data.transactions order by ID;

(六)删除

必须配置事务

delete:用于删除特定行条件,你可以从给定表中删除所有的行.

删除指定记录：

DELETE FROM tablename [WHERE expression]
hive> delete from test where id = 1;

（七）数据导入

导入到新表（也可以是EXTERNAL表）

 hive> IMPORT TABLE employee_imported FROM '/apps/ca/yanh/data';

分区表导入

 hive> IMPORT TABLE employee_partitioned_imported FROM '/apps/ca/yanh/data1';

二、常见问题:

Attempt to do update or delete on table default.test that does not use an AcidOutputFormat or is not bucketed
创建表时必须：
使用orc格式，必须填加桶。

clustered by (id) into 2 buckets stored as orc

Execution failed with exit status 2
mapred-site.xml
设置更大的堆大小

<property>         
<name>mapreduce.map.java.opts</name>
<value>-Xmx2014M</value>
 <description>maps中对jvm child设置更大的堆大小</description>
</property>

Container killed on request. Exit code is 143
问题原因： hadoop运行时使用的虚拟内存不足， hadoop物理内存默认与主机的内存一致，hadoop虚拟内存l默认是hadoop物理内存的2.1倍。
解决问题：
修改hadoop物理内存的大小可以在mapred-site.xml设置：

<property>
 <name>mapreduce.map.memory.mb</name>
    <value>2048</value>
 </property>
  <property>   <name>mapreduce.reduce.memory.mb</name>
    <value>2048</value>
 </property>

修改以上配置，完成了合并insert.或修改hadoop虚拟内存与hadoop物理内存的比率值可以在yarn-site.xml设置：

<property>
    <name>yarn.nodemanager.vmem-pmem-ratio</name>
    <value>3</value>
</property>

Hive开发--Hive--DML（五）
一、DML 操作操作手册：https://cwiki.apache.org/confluence/display...
Hive | Hive 安装详解
一、Hive 介绍二、准备工作三、Hive下载四、Hive 安装五、Hive 启动一、Hive 介绍 H...
Hive介绍与核心知识点
Hive Hive简介 Facebook为了解决海量日志数据的分析而开发了Hive，后来开源给了Apache软件基...
Hive的Transform和UDF
UDTF Hive中UDTF编写和使用 UDAF Hive udaf开发入门和运行过程详解 Hive通用型自定义聚...
大数据开发之Hive优化篇8-Hive Job优化
备注:Hive 版本 2.1.1 Hive job优化概述实际开发过程中，经常会遇到hive sql运行比较慢的...
Hive开发--Hive简介
一、Hive简介 Apache Hive™ 数据仓库软件为分布式存储的大数据集上的读、写、管理提供很大方便，同...
Practical Hive - 2016.pdf Hive实战
Practical Hive - 2016.pdf Hive“出身名门”，是最初由Facebook公司开发的数据仓...
hbase使用es做二级索引-hive同步
开发环境hive1.2.1hbase 1.3.1elasticsearch 7.1.1 创建hive表映射到hba...
HIVE UDTF 自定义函数
HIVE UDTF 自定义函数关键词：HIVE UDTF 开发实例Hive运行用户自定义函数对数据信息处理，...
##[优化]开发高效的hive程序，hive优化
//开发高效的hive程序，hive优化 - 博客搬家至 lxw1234.com - ITeye技术网站http:...