Hive DML、SerDe

最新推荐文章于 2021-07-04 10:02:36 发布
最新推荐文章于 2021-07-04 10:02:36 发布
阅读量104 收藏
点赞数
分类专栏： HIVE 文章标签： hadoop
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chooser007/article/details/118250720
版权

Hive DML

1、加载文件到数据表，当使用LOCAL时是拷贝，使用HDFS上文件时时移动(原有数据会被删除)

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
 
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

加载HDFS文件到数据表，HDFS文件数据被移掉 

删除表数据：
hive> truncate table student;
OK
Time taken: 0.347 seconds

加载文件到表：
hive> load data inpath '/user/age=10/student' into table student;
Loading data to table default.student
OK
Time taken: 0.514 seconds

查询导入的数据：
hive> select * from student;
OK
1	小红1	["王者","book","movie"]	{"modu":"renminglu","shenzheng":"futian"}
2	小红2	["王者","book","movie"]	{"modu":"renminglu","xizhang":"lasha"}
3	小红3	["吃鸡","book","movie"]	{"chongqing":"renminglu","shenzheng":"futian"}
4	小红4	["王者","book","movie"]	{"modu":"renminglu","dongguang":"changan"}
5	小红5	["walking","book","movie"]	{"modu":"renminglu","shenzheng":"futian"}
6	小红6	["王者","book","movie"]	{"nanchang":"renminglu","shenzheng":"futian"}
7	小红7	["王者","book","movie"]	{"modu":"renminglu","shenzheng":"futian"}
8	小红8	["walking","book","movie"]	{"nanchang":"renminglu","guangzhou":"niwan"}
9	小红9	["王者","book","movie"]	{"modu":"renminglu","shenzheng":"futian"}
10	小红10	["王者","book","movie"]	{"shanghai":"renminglu","shenzheng":"futian"}
Time taken: 0.185 seconds, Fetched: 10 row(s)
hive>

2、查询插入hive数据表

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
 
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;
 
Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
1、创建2张待插入数据的表
create external table student_dml
(
id int,
name String
)
partitioned by (age int)
row format delimited
fields terminated by ',';

create external table student_dml2
(
id int,
name String,
address map<String, String>
)
partitioned by (age int)
row format delimited
fields terminated by ','
map keys terminated by ':';

2、执行查询插入数据SQL
hive> from student
    > insert into table student_dml partition(age=12) select id,name
    > insert into table student_dml2 partition(age=13) select id,name,address;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20210626163657_faeea658-b9ce-4da2-bf62-a7186d5ba3b2
Total jobs = 5
Launching Job 1 out of 5
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1624671436790_0003, Tracking URL = http://node03:8088/proxy/application_1624671436790_0003/
Kill Command = /opt/software/hadoop-2.10.1/bin/hadoop job  -kill job_1624671436790_0003
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 0
2021-06-26 16:37:13,831 Stage-2 map = 0%,  reduce = 0%
2021-06-26 16:37:25,455 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 1.49 sec
MapReduce Total cumulative CPU time: 1 seconds 490 msec
Ended Job = job_1624671436790_0003
Stage-5 is selected by condition resolver.
Stage-4 is filtered out by condition resolver.
Stage-6 is filtered out by condition resolver.
Stage-11 is selected by condition resolver.
Stage-10 is filtered out by condition resolver.
Stage-12 is filtered out by condition resolver.
Moving data to directory hdfs://mycluster/user/hive/warehouse/student_dml/age=12/.hive-staging_hive_2021-06-26_16-36-57_079_5102616348730834889-1/-ext-10000
Moving data to directory hdfs://mycluster/user/hive/warehouse/student_dml2/age=13/.hive-staging_hive_2021-06-26_16-36-57_079_5102616348730834889-1/-ext-10002
Loading data to table default.student_dml partition (age=12)
Loading data to table default.student_dml2 partition (age=13)
MapReduce Jobs Launched: 
Stage-Stage-2: Map: 1   Cumulative CPU: 1.49 sec   HDFS Read: 6028 HDFS Write: 704 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 490 msec
OK
Time taken: 31.724 seconds


3、查询数据：
hive> select * from student_dml;
OK
1	小红1	12
2	小红2	12
3	小红3	12
4	小红4	12
5	小红5	12
6	小红6	12
7	小红7	12
8	小红8	12
9	小红9	12
10	小红10	12
Time taken: 0.262 seconds, Fetched: 10 row(s)


hive> select * from student_dml2;
OK
1	小红1	{"modu":"renminglu","shenzheng":"futian"}	13
2	小红2	{"modu":"renminglu","xizhang":"lasha"}	13
3	小红3	{"chongqing":"renminglu","shenzheng":"futian"}	13
4	小红4	{"modu":"renminglu","dongguang":"changan"}	13
5	小红5	{"modu":"renminglu","shenzheng":"futian"}	13
6	小红6	{"nanchang":"renminglu","shenzheng":"futian"}	13
7	小红7	{"modu":"renminglu","shenzheng":"futian"}	13
8	小红8	{"nanchang":"renminglu","guangzhou":"niwan"}	13
9	小红9	{"modu":"renminglu","shenzheng":"futian"}	13
10	小红10	{"shanghai":"renminglu","shenzheng":"futian"}	13
Time taken: 0.209 seconds, Fetched: 10 row(s)
hive>

2、Hive SerDe - Serializer and Deserializer，使用在比较复杂的场景下（非结构化规则表）

        SerDe 用于做序列化和反序列化

        构建在数据存储和执行引擎之间，对两者实现解耦

        Hive通过ROW FORMAT DELIMITED以及SERDE进行内容的读写

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

 

1、建表：
CREATE TABLE apachelog 
(
  host STRING,
  identity STRING,
  t_user STRING,
  time STRING,
  request STRING,
  referer STRING,
  agent STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
STORED AS TEXTFILE;

2、准备一个日志数据文件
[root@node04 data]# cat apachelog.txt 
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET /tomcat.css HTTP/1.1" 304 -

3、加载数据
hive> load  data local inpath '/opt/software/data/apachelog.txt' into table apachelog;
Loading data to table default.apachelog
OK
Time taken: 0.422 seconds
hive> 


4、查询插入的数据信息：
hive> select * from apachelog;
OK
192.168.57.4	-	-	29/Feb/2016:18:14:35 +0800	GET /bg-upper.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:35 +0800	GET /bg-nav.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:35 +0800	GET /asf-logo.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:35 +0800	GET /bg-button.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:35 +0800	GET /bg-middle.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET / HTTP/1.1	200	11217
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET / HTTP/1.1	200	11217
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.css HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /asf-logo.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-middle.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-button.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-nav.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-upper.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET / HTTP/1.1	200	11217
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.css HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET / HTTP/1.1	200	11217
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.css HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /tomcat.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-button.png HTTP/1.1	304	-
192.168.57.4	-	-	29/Feb/2016:18:14:36 +0800	GET /bg-upper.png HTTP/1.1	304	-
Time taken: 0.188 seconds, Fetched: 22 row(s)

A6-母婴小店-第6分店
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive DML
01-07
Hive不支持update的操作。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。 load：加载数据到表 load data [local] inpath ‘filepath’ overwrite | into table 表名 [partition ...
分区 读取参数错误_如何修复“警告：重新读取分区表失败，错误22：参数无效”在Linux上错误...
culunyi0802的博客
09-14 3064
分区 读取参数错误If you are getting the following error while trying to change the default fat32 partition on a new drive to another linux partition type, I might have the answer for you. 如果在尝试将新驱动器上的默认fat32...
Hive的4种导入和3种导出方式
Allenzyg的博客
04-29 2400
接上篇文章《Hive集群安装》 一、使用beeline连接hive 1. 启动Hadoop：start-dfs.sh、start-yarn.sh [root@node4 hadoop-2.6.4]# sbin/start-dfs.sh [root@node4 hadoop-2.6.4]# sbin/start-yarn.sh 2. 连接hive [root@node4 apache-h...
大数据开发之Hive篇4-Hive数据操作语言
只是甲的博客
12-23 1287
备注: Hive 版本 2.1.1 文章目录一.Hive的DML(数据操作语言)概述二.Load 命令2.1 数据准备2.2 将服务器文件加载到hive表2.3 将HDFS文件加载到hive表三.INSERT INTO TABLE FROM Query3.1 Insert into select 语句3.2 Insert overwrite select 语句3.3 multiple inserts3.4 动态分区插入四.CTAS五.Writing data into the filesystem fro
Hive-XML-SerDe-VTD:适用于Apache Hive XML SerDe的VTD处理器
04-29
适用于Apache Hive XML SerDe的VTD处理器 使用基于VTD-XML的处理器代替默认的JDK DOM XPath处理器可以显着提高性能。 参见 使用以下DDL创建Apache Hive表，注意VTD-XML处理器的“ xml.processor.class”， 创建...
Hive之DML数据操作
01-07
Hive简介：https://blog.csdn.net/weixin_45102492/article/details/104418259 Hive安装部署 ： https://blog.csdn.net/weixin_45102492/article/details/104418666 Hive数据类型： ...Hive
hive 表 dml 操作.zip
06-22
在这个“hive 表 DML 操作.zip”压缩包中，我们关注的是Hive对数据操作的语言特性，特别是DML（Data Manipulation Language），即数据操纵语言。在Hive中，DML主要包括INSERT、UPDATE、DELETE以及SELECT等操作，但...
hive 表 dml 操作.md
最新发布
06-22
在 Hive 中，可以使用 DML（数据操作语言）来对表进行增删改操作。
Hive 权限管理
chooser007的博客
06-30 2318
官方页面 Hive 三种授权模型： 1、Storage Based Authorization in the Metastore Server :基于存储的授权 - 可以对Metastore中的元数据进行保护，但是没有提供更加细粒度的访问控制（例如：列级别、行级别） 2、SQL Standards Based Authorization in HiveServer2 :基于SQL标准的Hive授权 - 完全兼容SQL的授权模型，推荐使用该模式 ...
Hive 分区--静态分区、动态分区
chooser007的博客
06-26 1774
Hive分区 1、Hive 分区 partition：必须在表定义的时候指定对应的partition字段 单分区建表语句：create table day_table (id int, content string) partitioned by (dt string); 单分区表，按天分区，在表结构中存在id，content，dt三列 以dt为文件...
Hive HA (高可用)搭建
chooser007的博客
07-04 1032
官方文档 hive的搭建方式有三种，分别是 1、Local/Embedded Metastore Database (Derby) 2、Remote Metastore Database 3、Remote Metastore Server 一般情况下，我们在学习的时候直接使用hive –service metastore的方式启动服务端，使用hive的方式直接访问登录客户端，除了这种方式之外，hive提供了hiveserver2的服务端启动方式，提供了beeline和jdbc的支持，并且官网也提出
Hive 优化（通用版）
chooser007的博客
07-03 741
hive优化 Hive 优化核心思想：把Hive SQL 当做Mapreduce程序去优化 以下SQL不会转为Mapreduce来执行： select仅查询本表字段 where仅对本表字段做条件过滤 Explain 显示执行计划：EXPLAIN [EXTENDED] query hive> explain extended select * from student; OK Explain STAGE DEPENDENCIES: ...
Hive 多节点搭建(Remote Metastore Server)
chooser007的博客
06-14 619
1、在上一篇的基础之上继续：hive单节点搭建
Hive 客户端 Beeline 、IDEA|Eclipse使用JDBC连接hiveserver2
chooser007的博客
06-26 608
1、Beline Beeline 要与HiveServer2配合使用 服务端启动hiveserver2 客户的通过beeline两种方式连接到hive a、beeline -u jdbc:hive2://localhost:10000/default -n root b、beeline> !connect jdbc:hive2://:/<db>;auth=noSasl ...
Hive 参数
chooser007的博客
06-27 365
hive 参数、变量：hive中的参数、变量都是以命名空间开头 命名空间 读写权限 含义 hiveconf 可读写 hive-site.xml当中的各配置变量 例：hive --hiveconf hive.cli.print.header=true system 可读写 系统变量，包含JVM运行参数等 例：sys
Hive 分桶
chooser007的博客
06-28 361
Hive分桶官方文档 分桶适用场景：抽样 概念： 1、分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储 2、对于hive中每一个表、分区都可以进一步进行分桶 3、由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中 使用方式： 开启分桶支持：set hive.enforce.bucketing=true; 【默认：false；设置为true之后，mr运行时会根据bucket的个数自动分配reduce task...
Hive Lateral View & 视图 & 索引
chooser007的博客
07-01 341
Hive Lateral View 1、Lateral View用于和UDTF函数（explode、split）结合来使用 2、首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表 3、主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题 4、语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias) Hive...
Hive运行方式&GUI接口
chooser007的博客
06-29 304
Hive运行方式： 1、命令行方式cli(控制台模式) 2、脚本运行方式（生产环境主要使用这种模式） 3、JDBC方式：hiveserver2 4、Web Gui 接口(hwi、hue等) 命令行（控制台模式），可以与hdfs交互--通过执行dfs 命令、与Linux交互-- 使用！开头 1、与hdfs交互 hive> dfs -cat /user/hive/warehouse/bucket_sample/000001...
Linux 下Hive 安装(Remote Metastore Database 单节点)
chooser007的博客
06-14 279
1、Linux下安装好mysql：Linux 下Mysql 安装
hive 建表 serde是什么意思
06-03
在 Hive 中，SerDe（Serializer/Deserializer）是数据序列化和反序列化的过程。它可以将 Hive 中的结构化数据与文件系统中的非结构化数据进行转换。在建表时指定 SerDe，可以让 Hive 知道如何将数据序列化成文件或从...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值