当前位置:   article > 正文

hive插入多条数据sql_Hive的基本操作

hivejdbc sql values多条记录

内容提要

l Hive及beeline的命令行操作

l jdbc操作Hive

l Hive函数

3.1 Hive命令操作

3.1.1 Hive的基本操作

我们长久以来习惯于传统的关系型数据库,并且结构化查询语言(SQL)相对来说也比较容易学习,那么能否将类似于关系型数据库的架构应用到Hadoop文件系统,从而可以使用类SQL语言查询和操作数据呢?Hive应运而生。

Hive提供了一个被称为Hive查询语言(HQL)的SQL方言,来查询存储在Hadoop集群中的数据。Hive就相当于是Mysql,Mysql的底层存储引擎是InnoDB,而Hive的引擎就是Hadoop的MapReduce,或者Spark,Hive会将不多数的查询转换成MapReduce任务或者Spark任务,这样就巧妙地将传统SQL语言和Hadoop生态系统结合起来,使仅会SQL的人员就可以轻松编写数据分析任务。

Hive是一个数据仓库,OLAP在线分析处理,用于统计或聚合函数等,不支持行级别的删改。它的数据建立在Hadoop之上,数据存储在HDFS上,但是它的Metastore默认存到Derby数据库当中,也可以存到外部数据库Mysql中。

接下来是对Hive的一些基本操作命令,

(1)desc database(extended)mybase(数据库名称) ; //查看数据库信息(括号内的可加可不加)extended表示扩展信息。

(2)alter database mybase set dbproperties ('created'='xpc'); //修改数据库,增加属性。

(3)create database mybase comment 'this is my first base'; //为数据库增加描述信息。

(4)create database mybase location '/x/x/x/x' ; //指定数据库存放hdfs的位置。

(5)create table default.test1 like mybase.test; //复制表结构,将mybase中test表的结构复制到default中的test1表中。

(6)load data local inpath '/x/x/x' overwrite into table xx ; //上传本地数据到hdfs中。

(7)load data inpath '/x/x/x' into table xx; //移动hdfs文件系统上的数据文件。

(8)insert into mybase.test2 select * from default.test0 where id >1203; //从default.test0中查找id>1203,插入到mybase.test2中。

(9)create table mybase.test3 as select * from default.test0; //复制表(表结构+数据)。

(10)select distinct id,name from xx ; //查询数据时重复的数据不要。

(11)insert into test2(id,name,age) values(5,'kk',22); //向表中插入一组数据。

(12)select all id,name,age from test2; //查询相同字段。

(13)select a.*,b,* from customers a inner join orders b on a.id=b.cid;

select a.*,b,* from customers a , orders b where a.id=b.id; //内连接。

(14)select * from test2 union select * from test2; //join是连接操作,union(纵向),join(横向)。

(15)select id,name from customers union all select id,orderno from orders; //union all是将多个结果合并输出。

3.1.2 Hive视图-索引

本小节主要介绍如何创建和管理视图以及索引,以及一些简单的连接表的操作,比如union、join等。

当使用Hive的表数据作为输入源时,有些情况下,Hive中的表定义和数据并不能满足分析的需求,例如有些列的值需要进行处理,有些列的类型不满足需求,甚至有时候我们在创建Hive表时为了方便快捷,会将Hive表的所有列的字段类型都定义为string,因此很多情况下使用之前需要对Hive上的数据格式等问题进行适当的处理。但是如果在Hive中通过修改原表来解决上面的问题,比如使用alter table 的方式修改原始表的Schema信息未免会对其他依赖Hive的组件有所影响(例如可能导致通过Sqoop等方式导入数据失败),而且也有可能导致之前的作业无法正常运行。于是我们需要考虑在不改变原表的情况下解决这个问题,我们想到的方案是使用Hive的视图。 Hive视图有几个特点:(1)不支持物化视图,物理文件并不存在。虚拟表也是表,但能显示出来。(2)只能查询,不能做加载数据操作。(3)视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询。(4)view定义中若包含了ORDER BY/LIMIT语句,当查询视图时也进行ORDER BY/LIMIT语句操作,视图当中定义的优先级更高。(5)view支持迭代视图

接下来是对视图的一个简单的操作实例。

首先创建一个Hive表:

create table test2(id int,name String,age int)row format delimited fields terminated by ''(lines terminated by '' )stored as textfile;

然后建立一个test.txt文件,使用Hadoop的put命令将该文件上传到HDFS上的/user/hive/warehouse/myhive.db/test2目录下,其内容为:

1 tom 12

2 tomas 13

3 tomaslee 14

在创建好表并且在Hadoop上存储数据之后,可以使用load命令将该数据加载到表中,具体代码如下,

load data inpath ‘/user/hive/warehouse/myhive.db/test2/test.txt’ into table test2;

将数据加载到表中之后,可以使用select查询语句检查以上操作是否成功(此方法是通用方法,也可以在创建Hive表的时候使用location参数来指定HDFS上对应的数据目录,但此方法需要了解Hive的分区操作,关于Hive的分区操作将在3.1.4小节中做详细讲解。)。

如果数据已经可以正常访问到,即Hive的数据插入操作已成功,接下来需要把该Hive表作为两个表,使用自连接方法将两个表连接在一起,具体代码如下,

select a.*,b.* from test2 a,test2 b where a.id=b.id;

运行结果如下:

hive> select a.*,b.* from test2 a,test2 b where a.id=b.id;

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. tez, spark) or using Hive 1.X releases.

Query ID = lvqianqian_20181117015844_9f01950b-1f03-425d-9fcf-e721e2027419

Total jobs = 1

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/home/hadoop/software/apache-hive-2.0.0- bin/lib/hive-jdbc-2.0.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/hadoop/software/apache-hive-2.0.0- bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/home/hadoop/software/hadoop-2.7.3/share/ hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Execution log at: /tmp/lvqianqian/lvqianqian_20181117015844_9f01950b-1f03- 425d-9fcf-e721e2027419.log

2018-11-17 02:19:30 Starting to launch local task to process map join; maximum memory = 518979584

2018-11-17 02:19:43 Dump the side-table for tag: 0 with group count: 3 into file:file:/tmp/lvqianqian/eb51cfa6-0d1c-477d-a813-6d3d85788098/hive_2018-11-17_02-18-57_302_5305750774273816210-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile00--.hashtable

2018-11-17 02:19:44 Uploaded 1 File to: file:/tmp/lvqianqian/eb51cfa6-0d1c-477d-a813-6d3d85788098/hive_2018-11-17_02-18-57_302_5305750774273816210-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile00--.hashtable (334 bytes)

2018-11-17 02:19:44 End of local task; Time Taken: 13.762 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_1479375079365_0001, Tracking URL = http://hadoop0:8888/ proxy/application_1479375079365_0001/

Kill Command = /home/hadoop/software/hadoop-2.7.3/bin/hadoop job -kill job_1479375079365_0001

Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0

2018-11-17 02:27:33,482 Stage-3 map = 0%, reduce = 0%

2018-11-17 02:27:37,017 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 3.25 sec

MapReduce Total cumulative CPU time: 3 seconds 250 msec

Ended Job = job_1479375079365_0001

MapReduce Jobs Launched:

Stage-Stage-3: Map: 1 Cumulative CPU: 3.25 sec HDFS Read: 6661 HDFS Write: 377210 SUCCESS

Total MapReduce CPU Time Spent: 3 seconds 250 msec

OK

1 tom 12 1 tom 12

2 yons 13 2 yons 13

3 yarn 14 3 yarn 14

Time taken: 522.964 seconds, Fetched: 3 row(s)

在执行上面的语句之后,如果输出以上结果,代表语句输入成功,因此可以将该结果保存到一个新表中,用于以后的分析操作,该表命名为res,具体的代码以及运行过程如下所示,

create table res as select a.*,b.* from test2 a,test2 b where a.id=b.id;//会报错,显示id重复,于是create table res as select a.id,b.id from test2 a,test2 b where a.id=b.id;

hive> create table res as select a.id aid,b.id bid from test2 a,test2 b where a.id=b.id;

WARNING:

声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号