您现在的位置是：首页 > 博文答疑 > SPARK 返回dataframe转成JSON格式的API测试博文答疑
SPARK 返回dataframe转成JSON格式的API测试
Leo2017-05-24【8】
简介SPARK 返回dataframe转成JSON格式的API测试（SPINGBOOT）.
测试用例：
1，scala 用SPARK读取数据后转成json字符串和json对象
2，JAVA 用SPARK读取数据后转成json字符串
3，SPRINGBOOT下control层直接调用例子2，直接返回Json。（只提供可行性，如需运用，可以给予自己项目API架构提供API服务会更好）
引入相关依赖：
name := "SparkLearning"
version := "1.0"
scalaVersion := "2.12.2"
val overrideScalaVersion = "2.10.4"
val sparkVersion = "2.1.1"
val sparkXMLVersion = "0.3.3"
val sparkCsvVersion = "1.4.0"
val sparkElasticVersion = "2.3.4"
val sscKafkaVersion = "2.0.1"
val sparkMongoVersion = "1.0.0"
val sparkCassandraVersion = "1.6.0"
ivyScala := ivyScala.value map {
_.copy(overrideScalaVersion = true)
}
libraryDependencies ++= Seq(
"org.springframework.boot" % "spring-boot-starter-parent" % "1.3.2.RELEASE",
"org.springframework.boot" % "spring-boot-starter-web" % "1.3.2.RELEASE",
"org.apache.spark" %% "spark-core" % sparkVersion exclude("jline", "2.12"),
"org.apache.spark" %% "spark-sql" % sparkVersion excludeAll(ExclusionRule(organization = "jline"), ExclusionRule("name", "2.12")),
"org.apache.spark" %% "spark-hive" % sparkVersion,
"org.apache.spark" %% "spark-streaming" % sparkVersion,
"net.liftweb" %% "lift-webkit" % "2.6.2",
"mysql" % "mysql-connector-java" % "5.1.38",
"org.json" % "json" % "20160810"
)开始：
1，scala 用SPARK读取数据后转成json字符串和json对象
代码：
package sql.sample
import org.apache.spark.sql.SparkSession
import scala.util.parsing.json.JSON
object MYSQLReadWrite {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder.master("local").appName("Java Spark SQL basic example").getOrCreate
// Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods
// Loading data from a JDBC source
val jdbcDF = spark.read
.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test")
.option("dbtable", "test")
.option("user", "root")
.option("password", "password")
.load()
jdbcDF.filter("ar_id > 2").show()
jdbcDF.show()
var result ="["
var i = 0
jdbcDF.toJSON.take(5).foreach(record => {
if(!result.endsWith("[") && !result.endsWith("{") ){
result += ","
}
result += record
i = i + 1
println("record:"+record)
})
result += "]"
println("result_length:"+result.length)
println("result_size:"+result.size)
val json_result:Option[Any] = JSON.parseFull(result)
println("string_result:" + result)
println("json_result:"+json_result)
println("my print end")
}
}输出:
record:{"control_code":58,"ar_id":1,"ar_description":"test1"}
record:{"control_code":58,"ar_id":2,"ar_description":"test2"}
record:{"control_code":58,"ar_id":3,"ar_description":"test3"}
record:{"control_code":68,"ar_id":1,"ar_description":"test3"}
record:{"control_code":68,"ar_id":2,"ar_description":"test4"}
result_length:276
result_size:276
string_result:[{"control_code":58,"ar_id":1,"ar_description":"test1"},{"control_code":58,"ar_id":2,"ar_description":"test2"},{"control_code":58,"ar_id":3,"ar_description":"test3"},{"control_code":68,"ar_id":1,"ar_description":"test3"},{"control_code":68,"ar_id":2,"ar_description":"test4"}]
json_result:Some(List(Map(control_code -> 58.0, ar_id -> 1.0, ar_description -> test1), Map(control_code -> 58.0, ar_id -> 2.0, ar_description -> test2), Map(control_code -> 58.0, ar_id -> 3.0, ar_description -> test3), Map(control_code -> 68.0, ar_id -> 1.0, ar_description -> test3), Map(control_code -> 68.0, ar_id -> 2.0, ar_description -> test4)))
my print end2，JAVA 用SPARK读取数据后转成json字符串
package com.spark;
import java.util.*;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
public class MysqlRead {
/** 主方法 测试结束后直接拿掉，这样在其他地方就可以直接调用了*/
public static void main(String[] args) {
list();
}
/** 返回两个整数变量较大的值 */
public static String list() {
SparkSession spark = SparkSession
.builder()
.master("local")
.appName("Java Spark SQL basic example")
.getOrCreate();
// Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods
// Loading data from a JDBC source
Dataset<Row> jdbcDF = spark.read()
.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test")
.option("dbtable", "test")
.option("user", "root")
.option("password", "leo")
.load();
String result = "[";
List<String> list2 = jdbcDF.filter("ar_id > 1").toJSON().collectAsList();
for ( String lists: list2){
if (!result.endsWith("{") && !result.endsWith("[")){
result += ",";
}
result += lists;
System.out.println("list2:"+lists);
}
result +="]";
System.out.println("string_result:"+result);
return result;
}
}输出结果：
list2:{"control_code":58,"ar_id":2,"ar_description":"test2"}
list2:{"control_code":58,"ar_id":3,"ar_description":"test3"}
list2:{"control_code":68,"ar_id":2,"ar_description":"test4"}
list2:{"control_code":68,"ar_id":3,"ar_description":"test5"}
string_result:[{"control_code":58,"ar_id":2,"ar_description":"test2"},{"control_code":58,"ar_id":3,"ar_description":"test3"},{"control_code":68,"ar_id":2,"ar_description":"test4"},{"control_code":68,"ar_id":3,"ar_description":"test5"}]3，SPRINGBOOT下control层直接调用例子2，直接返回Json。
controller直接call：
package com.controller;
/**
* Created by Administrator on 2017/5/24.
*/
import org.springframework.boot.*;
import org.springframework.boot.autoconfigure.*;
import org.springframework.stereotype.*;
import org.springframework.web.bind.annotation.*;
import com.spark.MysqlRead;
@Controller
@EnableAutoConfiguration
public class SampleController {
@RequestMapping("/")
@ResponseBody
String home() {
return "Hello World2!";
}
@CrossOrigin(origins = "http://localhost")
@RequestMapping("/api")
@ResponseBody
String api() {
String list = MysqlRead.list();
return list;
}
public static void main(String[] args) throws Exception {
SpringApplication.run(SampleController.class, args);
}
}输出结果:
运用ajax调用并显示在界面上，部分代码入下：
//HTML部分代码：
<div id="div_id">
<form name="myform" method="">
<div class="table-list">
<table width="100%" cellspacing="0">
<thead>
<tr>
<th width="30" align="left">control_code</th>
<th width="50">ar_id</th>
<th align="left">ar_description</th>
<th width="100">ar_description</th>
<th width="120" align="center">actions</th>
</tr>
</thead>
<tbody v-for="(list, key, index) in info">
<tr>
<td>{{list.control_code}}</td>
<td>{{list.ar_id}}</a></td>
<td>{{list.ar_description}}</a></td>
<td>{{list.ar_description}}</a></td>
<td align="center"><a href="javascript:void(0)" v-on:click="edit(list.id)">修改</a> |
<a href="javascript:void(0)" v-on:click="delete_act(list.id)">删除</a>
</td>
</tr>
</tbody>
</table>
</div>
</form>
</div>
//Jsp代码：
url = "http://localhost:8080/api";
$.get(url,
function(response,status){
var obj_response = JSON.parse(response);
Vue.set(vue_obj,'info',obj_response);
});显示结果如下：
