spark从mysql效率_Spark SQL & Spark Hive编程开发，并和Hive执行效率对比

最新推荐文章于 2022-11-10 14:49:41 发布

零溢

最新推荐文章于 2022-11-10 14:49:41 发布

阅读量202

点赞数

文章标签： spark从mysql效率

本文链接：https://blog.csdn.net/weixin_35504293/article/details/114470050

版权

3台DataNode，2台NameNode，每台机器20G内存，24核

数据都是lzo格式的，共336个文件，338.6 G

无其他任务执行

如果想及时了解Spark、Hadoop或者Hbase相关的文章，欢迎关注微信公共帐号：iteblog_hadoop

三个测试都是执行 select count(*), host, module

from ewaplog

group by host, module

order by host, module;

下面我们先来看看Spark SQL核心的代码(关于Spark SQL的详细介绍请参见Spark官方文档，这里我就不介绍了。)： /**

* User: 过往记忆

* Date: 14-8-13

* Time: 下午23:16

* bolg:

* 本文地址：/archives/1090

* 过往记忆博客，专注于hadoop、hive、spark、shark、flume的技术博客，大量的干货

* 过往记忆博客微信公共帐号：iteblog_hadoop

public static class Entry implements Serializable {

private String host;

private String module;

private String content;

public Entry(String host, String module, String content) {

this.host = host;

this.module = module;

this.content = content;

}

public String getHost() {

return host;

}

public void setHost(String host) {

this.host = host;

}

public String getModule() {

return module;

}

public void setModule(String module) {

this.module = module;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

@Override

public String toString() {

return "[" + host + "\t" + module + "\t" + content + "]";

}

.......

JavaSparkContext ctx = ...

JavaSQLContext sqlCtx = ...

JavaRDD stringJavaRDD = ctx.textFile(args[0]).map(

new Function() {

@Override

public Entry call(String str) throws Exception {

String[] split = str.split("\u0001");

if (split.length < 3) {

return new Entry("", "", "");

}

return new Entry(split[0], split[1], split[2]);

}

});

JavaSchemaRDD schemaPeople = sqlCtx.applySchema(stringJavaRDD, Entry.class);

schemaPeople.registerAsTable("entry");

JavaSchemaRDD teenagers = sqlCtx.sql("select count(*), host, module " +

"from entry " +

"group by host, module " +

"order by host, module");

List teenagerNames = teenagers.map(new Function() {

public String call(Row row) {

return row.getLong(0) + "\t" +

row.getString(1) + "\t" + row.getString(2);

}

}).collect();

for (String name : teenagerNames) {

System.out.println(name);

}

Spark /**

* User: 过往记忆

* Date: 14-8-23

* Time: 下午23:16

* bolg:

* 本文地址：/archives/1090

* 过往记忆博客，专注于hadoop、hive、spark、shark、flume的技术博客，大量的干货

* 过往记忆博客微信公共帐号：iteblog_hadoop

JavaHiveContext hiveContext =....;

JavaSchemaRDD result = hiveContext.hql("select count(*), host, module " +

"from ewaplog " +

"group by host, module " +

"order by host, module");

List collect = result.collect();

for (Row row : collect) {

System.out.println(row.get(0) + "\t" + row.get(1) + "\t" + row.get(2));

}

大家可以看到Spark Hive核心代码里面的SQL语句和直接在Hive上面执行一样，在执行这个代码的时候，需要确保ewaplog存在。而且在运行这个程序的时候需要依赖Hive的一些jar包，需要依赖Hive的元数据等信息。对Hive的依赖比较大。而Spark SQL直接读取lzo文件，并没有涉及到Hive，相比Spark Hive依赖性这方便很好。Spark SQL直接读取lzo文件，然后将数据存放在RDD中，applySchema方法将JavaRDD转换成JavaSchemaRDD，我们来看看文档是怎么来描述的At the core of this component is a new type of RDD, SchemaRDD. SchemaRDDs are composed Row objects along with a schema that describes the data types of each column in the row. A SchemaRDD is similar to a table in a traditional relational database. A SchemaRDD can be created from an existing RDD, Parquet file, a JSON dataset, or by running HiveQL against data stored in Apache Hive.

转换成JavaSchemaRDD之后，我们可以用用registerAsTable将它注册到表中，之后就可以通过JavaSQLContext的sql方法来执行相应的sql语句了。

用Maven编译完上面的程序之后，放到Hadoop集群上面运行： iteblog@Spark $ spark-submit --master yarn-cluster

--jars lib/spark-sql_2.10-1.0.0.jar

--class SparkSQLTest

--queue queue1

./spark-1.0-SNAPSHOT.jar

/home/wyp/test/*.lzo

分别经过了20分钟左右的时间，Spark SQL和Spark Hive都可以运行完，结果如下： 39511517bokingserver1CN1_hbase_android_client

59141803bokingserver1CN1_hbase_iphone_client

39544052bokingserver2CN1_hbase_android_client

59156743bokingserver2CN1_hbase_iphone_client

23472413bokingserver3CN1_hbase_android_client

35251936bokingserver3CN1_hbase_iphone_client

23457708bokingserver4CN1_hbase_android_client

35262400bokingserver4CN1_hbase_iphone_client

19832715bokingserver5CN1_hbase_android_client

51003885bokingserver5CN1_hbase_iphone_client

19831076bokingserver6CN1_hbase_android_client

50997314bokingserver6CN1_hbase_iphone_client

30526207bokingserver7CN1_hbase_android_client

50702806bokingserver7CN1_hbase_iphone_client

54844214bokingserver8CN1_hbase_android_client

88062792bokingserver8CN1_hbase_iphone_client

54852596bokingserver9CN1_hbase_android_client

88043401bokingserver9CN1_hbase_iphone_client

54864322bokingserver10CN1_hbase_android_client

88041583bokingserver10CN1_hbase_iphone_client

54891529bokingserver11CN1_hbase_android_client

88007489bokingserver11CN1_hbase_iphone_client

54613917bokingserver12CN1_hbase_android_client

87623763bokingserver12CN1_hbase_iphone_client

为了比较基于Spark的任务确实比基于Mapreduce的快，我特意用Hive执行了同样的任务，如下： hive> select count(*), host, module from ewaplog

> group by host, module order by host, module;

Job 0: Map: 2845 Reduce: 364 Cumulative CPU: 17144.59 sec

HDFS Read: 363542156311 HDFS Write: 36516 SUCCESS

Job 1: Map: 1 Reduce: 1 Cumulative CPU: 4.82 sec

HDFS Read: 114193 HDFS Write: 1260 SUCCESS

Total MapReduce CPU Time Spent: 0 days 4 hours 45 minutes 49 seconds 410 msec

39511517bokingserver1CN1_hbase_android_client