scala mysql jdbc_scala mysql jdbc oper

最新推荐文章于 2022-07-30 10:43:00 发布
最新推荐文章于 2022-07-30 10:43:00 发布
阅读量110 收藏
点赞数
文章标签： scala mysql jdbc
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_28760171/article/details/113539889
版权
使用Scala连接MySQL数据库，读取dtspark表并展示数据，然后将数据写入数据库。展示了如何设置数据库连接属性，使用DataFrame进行读写操作。
摘要由CSDN通过智能技术生成

val properties= newProperties()

properties.put("user","root")

properties.put("password","xxx")

val url= "jdbc:mysql://127.0.0.1:3306/spark?useUnicode=true&characterEncoding=gbk&zeroDateTimeBehavior=convertToNull"val stud_scoreDF= sqlContext.read.jdbc(url,"dtspark",properties)

stud_scoreDF.show()///write/通过并行化创建RDD

val personRDD = sc.parallelize(Array("blm 5 144", "jerry 18 188", "kitty 5 166")).map(_.split(" "))//通过StrutType直接指定每个字段的schema

val schema =StructType(

List(

StructField("name",StringType,true),

StructField("age",IntegerType,true),

StructField("score",IntegerType,true)

)

)//将RDD映射到rowRDD

val rowRDD = personRDD.map(p => Row(p(0).trim, p(1).toInt, p(2).toInt))//将schema信息应用到rowRDD上

val personDataFrame =sqlContext.createDataFrame(rowRDD,schema)//创建Properties存储数据库相关属性//将数据追加到数据库

personDataFrame.write.mode("append").jdbc("jdbc:mysql://127.0.0.1:3306/spark","dtspark",properties)//停止SparkContext//print again

stud_scoreDF.show()

sc.stop()

}

}================================Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties

18/01/29 20:24:17 INFO SparkContext: Running Spark version 2.2.1

18/01/29 20:24:19INFO SparkContext: Submitted application: JdbcOperation18/01/29 20:24:19INFO SecurityManager: Changing view acls to: fangping18/01/29 20:24:19INFO SecurityManager: Changing modify acls to: fangping18/01/29 20:24:19INFO SecurityManager: Changing view acls groups to:18/01/29 20:24:19INFO SecurityManager: Changing modify acls groups to:18/01/29 20:24:19INFO SecurityManager: SecurityManager: authentication disabled; ui acls disabled; users with view permissions: Set(fangping); groups with view permissions: Set(); users with modify permissions: Set(fangping); groups with modify permissions: Set()18/01/29 20:24:19 INFO Utils: Successfully started service 'sparkDriver' on port 51005.18/01/29 20:24:19INFO SparkEnv: Registering MapOutputTracker18/01/29 20:24:19INFO SparkEnv: Registering BlockManagerMaster18/01/29 20:24:19 INFO BlockManagerMasterEndpoint: Using org.apache.spark.storage.DefaultTopologyMapper forgetting topology information18/01/29 20:24:19INFO BlockManagerMasterEndpoint: BlockManagerMasterEndpoint up18/01/29 20:24:19 INFO DiskBlockManager: Created local directory at C:\Users\fangping\AppData\Local\Temp\blockmgr-b8154e0d-e77b-4ba9-818b-71d3ffb8c55318/01/29 20:24:19 INFO MemoryStore: MemoryStore started with capacity 339.6MB18/01/29 20:24:20INFO SparkEnv: Registering OutputCommitCoordinator18/01/29 20:24:20 INFO Utils: Successfully started service 'SparkUI' on port 4040.18/01/29 20:24:20 INFO SparkUI: Bound SparkUI to 0.0.0.0, and started at http://172.18.3.13:4040

18/01/29 20:24:20INFO Executor: Starting executor ID driver on host localhost18/01/29 20:24:20 INFO Utils: Successfully started service 'org.apache.spark.network.netty.NettyBlockTransferService' on port 51014.18/01/29 20:24:20 INFO NettyBlockTransferService: Server created on 172.18.3.13:51014

18/01/29 20:24:20 INFO BlockManager: Using org.apache.spark.storage.RandomBlockReplicationPolicy forblock replication policy18/01/29 20:24:20 INFO BlockManagerMaster: Registering BlockManager BlockManagerId(driver, 172.18.3.13, 51014, None)18/01/29 20:24:20 INFO BlockManagerMasterEndpoint: Registering block manager 172.18.3.13:51014 with 339.6 MB RAM, BlockManagerId(driver, 172.18.3.13, 51014, None)18/01/29 20:24:20 INFO BlockManagerMaster: Registered BlockManager BlockManagerId(driver, 172.18.3.13, 51014, None)18/01/29 20:24:20 INFO BlockManager: Initialized BlockManager: BlockManagerId(driver, 172.18.3.13, 51014, None)18/01/29 20:24:20 INFO SharedState: Setting hive.metastore.warehouse.dir ('null') to the value of spark.sql.warehouse.dir ('file:/E:/back/scalaWs/Spark2Demo/spark-warehouse/').18/01/29 20:24:20 INFO SharedState: Warehouse path is 'file:/E:/back/scalaWs/Spark2Demo/spark-warehouse/'.18/01/29 20:24:21INFO StateStoreCoordinatorRef: Registered StateStoreCoordinator endpoint18/01/29 20:24:26 INFO CodeGenerator: Code generated in 239.883814ms18/01/29 20:24:27 INFO CodeGenerator: Code generated in 15.603579ms18/01/29 20:24:27 INFO SparkContext: Starting job: show at jdbc.scala:20

18/01/29 20:24:27 INFO DAGScheduler: Got job 0 (show at jdbc.scala:20) with 1output partitions18/01/29 20:24:27 INFO DAGScheduler: Final stage: ResultStage 0 (show at jdbc.scala:20)18/01/29 20:24:27INFO DAGScheduler: Parents of final stage: List()18/01/29 20:24:27INFO DAGScheduler: Missing parents: List()18/01/29 20:24:27 INFO DAGScheduler: Submitting ResultStage 0 (MapPartitionsRDD[2] at show at jdbc.scala:20), which has no missing parents18/01/29 20:24:29 INFO MemoryStore: Block broadcast_0 stored as values in memory (estimated size 7.5 KB, free 339.6MB)18/01/29 20:24:29 INFO MemoryStore: Block broadcast_0_piece0 stored as bytes in memory (estimated size 4.2 KB, free 339.6MB)18/01/29 20:24:29 INFO BlockManagerInfo: Added broadcast_0_piece0 in memory on 172.18.3.13:51014 (size: 4.2 KB, free: 339.6MB)18/01/29 20:24:29 INFO SparkContext: Created broadcast 0 from broadcast at DAGScheduler.scala:1006

18/01/29 20:24:29 INFO DAGScheduler: Submitting 1 missing tasks from ResultStage 0 (MapPartitionsRDD[2] at show at jdbc.scala:20) (first 15 tasks are for partitions Vector(0))18/01/29 20:24:29 INFO TaskSchedulerImpl: Adding task set 0.0 with 1tasks18/01/29 20:24:29 INFO TaskSetManager: Starting task 0.0 in stage 0.0 (TID 0, localhost, executor driver, partition 0, PROCESS_LOCAL, 4649bytes)18/01/29 20:24:29 INFO Executor: Running task 0.0 in stage 0.0 (TID 0)18/01/29 20:24:29INFO JDBCRDD: closed connection18/01/29 20:24:29 INFO Executor: Finished task 0.0 in stage 0.0 (TID 0). 1251bytes result sent to driver18/01/29 20:24:29 INFO TaskSetManager: Finished task 0.0 in stage 0.0 (TID 0) in 206 ms on localhost (executor driver) (1/1)18/01/29 20:24:29 INFO TaskSchedulerImpl: Removed TaskSet 0.0, whose tasks have all completed, frompool18/01/29 20:24:29 INFO DAGScheduler: ResultStage 0 (show at jdbc.scala:20) finished in 0.224s18/01/29 20:24:29 INFO DAGScheduler: Job 0 finished: show at jdbc.scala:20, took 2.466901s+-----+---+-----+

| name|age|score|

+-----+---+-----+

| swk|500|45455|

| blm| 5| 144|

|jerry| 18| 188|

|kitty| 5| 166|

+-----+---+-----+

18/01/29 20:24:30 INFO SparkContext: Starting job: jdbc at jdbc.scala:44

18/01/29 20:24:30 INFO DAGScheduler: Got job 1 (jdbc at jdbc.scala:44) with 1output partitions18/01/29 20:24:30 INFO DAGScheduler: Final stage: ResultStage 1 (jdbc at jdbc.scala:44)18/01/29 20:24:30INFO DAGScheduler: Parents of final stage: List()18/01/29 20:24:30INFO DAGScheduler: Missing parents: List()18/01/29 20:24:30 INFO DAGScheduler: Submitting ResultStage 1 (MapPartitionsRDD[9] at jdbc at jdbc.scala:44), which has no missing parents18/01/29 20:24:30 INFO MemoryStore: Block broadcast_1 stored as values in memory (estimated size 10.8 KB, free 339.6MB)18/01/29 20:24:30 INFO MemoryStore: Block broadcast_1_piece0 stored as bytes in memory (estimated size 5.8 KB, free 339.6MB)18/01/29 20:24:30 INFO BlockManagerInfo: Added broadcast_1_piece0 in memory on 172.18.3.13:51014 (size: 5.8 KB, free: 339.6MB)18/01/29 20:24:30 INFO SparkContext: Created broadcast 1 from broadcast at DAGScheduler.scala:1006

18/01/29 20:24:30 INFO DAGScheduler: Submitting 1 missing tasks from ResultStage 1 (MapPartitionsRDD[9] at jdbc at jdbc.scala:44) (first 15 tasks are for partitions Vector(0))18/01/29 20:24:30 INFO TaskSchedulerImpl: Adding task set 1.0 with 1tasks18/01/29 20:24:30 INFO TaskSetManager: Starting task 0.0 in stage 1.0 (TID 1, localhost, executor driver, partition 0, PROCESS_LOCAL, 4868bytes)18/01/29 20:24:30 INFO Executor: Running task 0.0 in stage 1.0 (TID 1)18/01/29 20:24:30 INFO CodeGenerator: Code generated in 13.199986ms18/01/29 20:24:30 INFO CodeGenerator: Code generated in 86.854105ms18/01/29 20:24:30 INFO Executor: Finished task 0.0 in stage 1.0 (TID 1). 970bytes result sent to driver18/01/29 20:24:30 INFO TaskSetManager: Finished task 0.0 in stage 1.0 (TID 1) in 472 ms on localhost (executor driver) (1/1)18/01/29 20:24:30 INFO DAGScheduler: ResultStage 1 (jdbc at jdbc.scala:44) finished in 0.473s18/01/29 20:24:30 INFO DAGScheduler: Job 1 finished: jdbc at jdbc.scala:44, took 0.496857s18/01/29 20:24:30 INFO TaskSchedulerImpl: Removed TaskSet 1.0, whose tasks have all completed, frompool18/01/29 20:24:30 INFO SparkContext: Starting job: show at jdbc.scala:49

18/01/29 20:24:30 INFO DAGScheduler: Got job 2 (show at jdbc.scala:49) with 1output partitions18/01/29 20:24:30 INFO DAGScheduler: Final stage: ResultStage 2 (show at jdbc.scala:49)18/01/29 20:24:30INFO DAGScheduler: Parents of final stage: List()18/01/29 20:24:30INFO DAGScheduler: Missing parents: List()18/01/29 20:24:30 INFO DAGScheduler: Submitting ResultStage 2 (MapPartitionsRDD[13] at show at jdbc.scala:49), which has no missing parents18/01/29 20:24:30 INFO MemoryStore: Block broadcast_2 stored as values in memory (estimated size 7.5 KB, free 339.6MB)18/01/29 20:24:30 INFO MemoryStore: Block broadcast_2_piece0 stored as bytes in memory (estimated size 4.2 KB, free 339.6MB)18/01/29 20:24:30 INFO BlockManagerInfo: Added broadcast_2_piece0 in memory on 172.18.3.13:51014 (size: 4.2 KB, free: 339.6MB)18/01/29 20:24:30 INFO SparkContext: Created broadcast 2 from broadcast at DAGScheduler.scala:1006

18/01/29 20:24:30 INFO DAGScheduler: Submitting 1 missing tasks from ResultStage 2 (MapPartitionsRDD[13] at show at jdbc.scala:49) (first 15 tasks are for partitions Vector(0))18/01/29 20:24:30 INFO TaskSchedulerImpl: Adding task set 2.0 with 1tasks18/01/29 20:24:30 INFO TaskSetManager: Starting task 0.0 in stage 2.0 (TID 2, localhost, executor driver, partition 0, PROCESS_LOCAL, 4649bytes)18/01/29 20:24:30 INFO Executor: Running task 0.0 in stage 2.0 (TID 2)18/01/29 20:24:30INFO JDBCRDD: closed connection18/01/29 20:24:30 INFO Executor: Finished task 0.0 in stage 2.0 (TID 2). 1217bytes result sent to driver18/01/29 20:24:30 INFO TaskSetManager: Finished task 0.0 in stage 2.0 (TID 2) in 26 ms on localhost (executor driver) (1/1)18/01/29 20:24:30 INFO TaskSchedulerImpl: Removed TaskSet 2.0, whose tasks have all completed, frompool18/01/29 20:24:30 INFO DAGScheduler: ResultStage 2 (show at jdbc.scala:49) finished in 0.027s18/01/29 20:24:30 INFO DAGScheduler: Job 2 finished: show at jdbc.scala:49, took 0.088025s+-----+---+-----+

| name|age|score|

+-----+---+-----+

| swk|500|45455|

| blm| 5| 144|

|jerry| 18| 188|

|kitty| 5| 166|

| blm| 5| 144|

|jerry| 18| 188|

|kitty| 5| 166|

+-----+---+-----+

18/01/29 20:24:30 INFO SparkUI: Stopped Spark web UI at http://172.18.3.13:4040

18/01/29 20:24:30 INFO MapOutputTrackerMasterEndpoint: MapOutputTrackerMasterEndpoint stopped!

18/01/29 20:24:30INFO MemoryStore: MemoryStore cleared18/01/29 20:24:30INFO BlockManager: BlockManager stopped18/01/29 20:24:30INFO BlockManagerMaster: BlockManagerMaster stopped18/01/29 20:24:30 INFO OutputCommitCoordinator$OutputCommitCoordinatorEndpoint: OutputCommitCoordinator stopped!

18/01/29 20:24:30INFO SparkContext: Successfully stopped SparkContext18/01/29 20:24:30INFO ShutdownHookManager: Shutdown hook called18/01/29 20:24:30 INFO ShutdownHookManager: Deleting directory C:\Users\\AppData\Local\Temp\spark-e888fd39-2e41-43e5-829b-ca203b786cef

企鹅吃喝指南
关注
Scala使用JDBC连接MySQL
菜鸟也学大数据的博客
10-30 487
这里使用的是JDK8、MySQL5.6.46、Idea2018.2 Scala操作步骤 object scalaToMySQL{ //连接配置信息 private val driver="com.mysql.jdbc.driver" private val url="jdbc:mysql://192.168.**.**/库名" private val username="mysql用户名" private val password="mysql密码" //创建connection对象 .
scala mysql jdbc_scala调用jdbc连接数据库
weixin_42525582的博客
02-22 126
from：http://mkaz.com/solog/scala/using-scala-with-jdbc-to-connect-to-mysql.htmlUsing Scala with JDBC to connect to MySQLDate: May 27, 2011A howto on connecting Scala to a MySQL database using JDBC. Th...
JDBC(详解)
一枚数学专业的小码农的博客
07-30 1384
JDBC超详细解
在scala中连接mysql时出现错误：Establishing SSL connection without server's identity verification is not recomm
canglan211的博客
11-28 1485
在Scala中连接mysql时，输入命令： scala> rebuyDF.write.mode("append").jdbc("jdbc:mysql://localhost:3306/dbtaobao", "dbtaobao.rebuy", prop) 出现错误如下： Wed Nov 28 20:41:12 CST 2018 WARN: Establishing SSL connect...
【scala 数据库操作】scala操作mysql数据库
赖德发的博客
12-24 1万+
主要内容： 1、scala连接mysql（jdbc方式） 2、scala查询mysql数据 3、scala更新mysql数据 4、scala插入mysql数据。 5、scala 删除mysql数据。 6、scala 调用存储过程。 在我的本地mysql 数据库中，有一张persons表，内容如下： 下面我们希望通过scala执行 mysql 查询数据和插入数据 和更新数据，删除数据、调用存储过程等
scala-xml_2.12-1.0.6-API文档-中文版.zip
06-07
赠送jar包：scala-xml_2.12-1.0.6.jar； 赠送原API文档：scala-xml_2.12-1.0.6-javadoc.jar； 赠送源代码：scala-xml_2.12-1.0.6-sources.jar； 赠送Maven依赖信息文件：scala-xml_2.12-1.0.6.pom； 包含翻译后的API...
jupyter-Scala_2.11.11_kernel_Windows.zip
05-21
"jupyter-Scala_2.11.11_kernel_Windows.zip" 是专为Windows操作系统设计的Jupyter Notebook Scala内核安装包，允许用户在Jupyter环境中编写和运行Scala代码。 Scala是一种多范式编程语言，结合了面向对象和函数式...
scala-xml_2.11-1.0.4-API文档-中英对照版.zip
06-07
赠送jar包：scala-xml_2.11-1.0.4.jar； 赠送原API文档：scala-xml_2.11-1.0.4-javadoc.jar； 赠送源代码：scala-xml_2.11-1.0.4-sources.jar； 赠送Maven依赖信息文件：scala-xml_2.11-1.0.4.pom； 包含翻译后的API...
scala-xml_2.11-1.0.5-API文档-中英对照版.zip
06-05
赠送jar包：scala-xml_2.11-1.0.5.jar； 赠送原API文档：scala-xml_2.11-1.0.5-javadoc.jar； 赠送源代码：scala-xml_2.11-1.0.5-sources.jar； 赠送Maven依赖信息文件：scala-xml_2.11-1.0.5.pom； 包含翻译后的API...
pi_scala.zip_scala_scala精度
09-14
标题中的"pi_scala.zip_scala_scala精度"暗示了我们关注的是一个使用Scala编程语言实现的计算圆周率（PI）精度提升的程序。这个程序利用了Scala的无限精度特性，来避免浮点数计算时可能出现的精度损失。描述中提到这...
Spark将DataFrame写入MySQL时遇到的问题
我的博客
07-11 3606
DataFrame如何写入MySQL val host = "localhost" val port = "3306" val user = "user" val password = "password" val database = "test" val table = "test" val saveMode = SaveMode.Overwrite // 支持4中写入方式Ap
spark：sparksql：jdbc测试（mysql）
不花的花和尚的博客
08-02 691
/** * 数据源：JDBC * @param spark */ def testJDBC(spark: SparkSession): Unit = { // 从机器1的mysql读取数据 println("========================第一种读取mysql方式================================") //默认partation为1 val url1: String = "jdbc:mys.
你真知道如何高效用mapPartitions吗？
大数据星球-浪尖
08-13 6234
做过一段时间spark的应用开发的小伙伴都会渐渐发现，很没趣，因为都是调API。那么，真的是没趣吗，还是说你本身没有去深入研究呢？通过本文你就会发现自己没成长是哪的问题了...
Jdbc连接
DearNingning的博客
06-03 62
import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet} import Utils.SparkUtils import org.apache.spark.SparkContext import org.apache.spark.rdd.RDD /* * 一个分区处理一次操作 * 在获取JDBC连接的时候 可以减少连接次数 * 占用内存比较大 ,有可能提升内存溢出的风险*/ object Demo0
Spark---算子调优之MapPartitions提升Map类操作性能
tianqinglei的博客
09-12 7777
spark中，最基本的原则，就是每个task处理一个RDD的partition。 1、MapPartitions操作的优点： 如果是普通的map，比如一个partition中有1万条数据；ok，那么你的function要执行和计算1万次。 但是，使用MapPartitions操作之后，一个task仅仅会执行一次function，function一次接收所有的partition
spark小技巧－mapPartitions
热门推荐
偷闲小苑
09-21 5万+
与map方法类似，map是对rdd中的每一个元素进行操作，而mapPartitions则是对rdd中的每个分区的迭代器进行操作。如果在map过程中需要频繁创建额外的对象(例如将rdd中的数据通过jdbc写入数据库,map需要为每个元素创建一个链接而mapPartition为每个partition创建一个链接),则mapPartitions效率比map高的多。Demo实现将每个数字变成原来的2倍的功能
Learning Spark——Spark连接Mysql、mapPartitions高效连接HBase
Trigl的博客
05-20 5321
执行Spark任务免不了从多个数据源拿数据，除了从HDFS获取数据以外，我们还经常从Mysql和HBase中拿数据，今天讲一下如何使用Spark查询Mysql和HBase1. Spark查询Mysql内容主要参考官方文档：http://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-to-other-databasesSpar
本地windows跑Scala程序调用Spark
weixin_33694172的博客
11-21 417
应用场景 spark是用scala写的一种极其强悍的计算工具，spark内存计算，提供了图计算，流式计算，机器学习，即时查询等十分方便的工具，所以利用scala来进行spark编程是十分必要的，下面简单书写一个spark连接mysql读取信息的例子。 操作流程 ...
Spark SQL CLI 运行
风行天下
03-04 1万+
1：运行 ./bin/spark-sql 需要先把hive-site.xml 负责到spark的conf目录下 [jifeng@feng02 spark-1.2.0-bin-2.4.1]$ ./bin/spark-sql Spark assembly has been built with Hive, including Datanucleus jars on classpath java.l
Spark JDBC并发优化：提升MySQL数据读取性能
"Spark JDBC 读取并发优化主要涉及如何提升 Spark 通过 JDBC 连接 MySQL 数据库时的数据加载性能，防止因单线程任务过重导致的任务挂起或内存溢出问题。优化方法通常包括调整分区策略和配置合适的并行度。" 在 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值