Spark读写postgresql

最新推荐文章于 2022-08-15 16:35:24 发布

独孤尚亮dugushangliang

最新推荐文章于 2022-08-15 16:35:24 发布

阅读量8.3k

点赞数 10

分类专栏： Spark 文章标签： Spark postgresql

本文链接：https://blog.csdn.net/weixin_40450867/article/details/102613275

版权

Spark 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

记录spark读写postgresql的操作
读写mysql同理，个别地方可能需要修改

1 连接数据库的两种方式

其中一为spark的读取方式，二为通过结合java读取
读取结果为DataFrame

读方法一

val jdbcDF = spark.read
  .format("jdbc")
  .option("url", "jdbc:postgresql://127.0.0.1:5432/geodb")
  .option("dbtable", "shuihu")
  .option("user", "postgres")
  .option("password", "postgres")
  .load()

读方法二

import java.util.Properties

val prop = new Properties()
prop.put("user", "postgres") //表示用户名
prop.put("password", "postgres") //表示密码
prop.put("driver","org.postgresql.Driver") //表示驱动程序
//读取
val df2=spark.read.jdbc(url="jdbc:postgresql://127.0.0.1:5432/geodb",table="shuihu3",prop)

写方法一

append模式，当已有数据表时在原有的基础上追加，如果表不存在则会自动创建表

定义字段类型时当新建表时有效，如果表已经存在则已原有的为准
新建数据表时最好指定字段的长度范围

shuihuDF.write
  .mode("append")   //如果不用追加模式则报错表已经存在，换个不存在的表会自动创建并写入数据
  .format("jdbc")
  .option("createTableColumnTypes", "id int,xingzuo CHAR(64), chuohao char(64),name char(64)")
  .option("url", "jdbc:postgresql://127.0.0.1:5432/geodb")
  .option("dbtable", "shuihu2")
  .option("user", "postgres")
  .option("password", "postgres")
  .save()

写方法二

同样需要 import java.util.Properties

val prop = new Properties()
prop.put("user", "postgres") //表示用户名
prop.put("password", "postgres") //表示密码
prop.put("driver","org.postgresql.Driver") //表示驱动程序
    //写入数据，可以通过option设置列的类型，新建一个表的时候
shuihuDF.write
    .mode("append")
    .option("createTableColumnTypes", "id int,xingzuo CHAR(64), chuohao char(64),name char(64)")
    .jdbc("jdbc:postgresql://127.0.0.1:5432/geodb","shuihu4",prop)

2 读写数据库的示例

本实验使用idea开发，

2.1 添加jdbc的jar包

下载jar包 https://jdbc.postgresql.org/download.html
在idea中添加这个jar包

2.2 数据准备

以水浒传108将为例，数据为txt文本，制表符分割，utf-8编码，前几条数据为

1	天魁星	呼保义	宋江
2	天罡星	玉麒麟	卢俊义
3	天机星	智多星	吴用
4	天闲星	入云龙	公孙胜
5	天勇星	大刀		关胜
6	天雄星	豹子头	林冲
7	天猛星	霹雳火	秦明
8	天威星	双鞭		呼延灼

2.3 创建数据库和表

数据库在Windows系统下
创建数据库geodb和表shuihu，分别表示排名 id，星座 xingzuo，绰号 chuohao，名字 name
排名id为整型，其他为varchar型

 create database geodb;
 create table shuihu(id int,xingzuo varchar(40),chuohao varchao(40),name varchar(40));

2.4 写入数据方法1

方法一是设置数据和模式信息
方法二通过样例类

数据路径为 D:\data\shuihu1.txt

WritePostSQL.scala

import org.apache.spark.sql.SparkSession
import java.util.Properties
import org.apache.spark.sql.types._
import org.apache.spark.sql.Row

object WritePostSQL {
  def main(args: Array[String]): Unit = {
    //创建SparkSession
    val spark=SparkSession.builder().appName("haha")
      .master("local[1]").getOrCreate()
    //数据路径
    val datapath="D:\\data\\shuihu1.txt"

    //下面我们设置数据，rdd均用字符串表示，用\t拆分,数据可从scala创建或从文件读取
    //按照第一列排序,local[*]的时候排序没用，local[1]的时候按照顺序
    //val shuihuRDD = spark.sparkContext.parallelize(Array("2\t天罡星\t玉麒麟\t卢俊义")).map(_.split("\t"))
    val shuihuRDD=spark.sparkContext.textFile(datapath).map(_.split("\t")).sortBy(_(0).toInt)

    //下面要设置模式信息，List或其他的容器类型均可
    val schema = StructType(List(StructField("id", IntegerType, true),
                                 StructField("xingzuo", StringType, true),
                                 StructField("chuohao", StringType, true),
                                 StructField("name", StringType, true)))

    //下面创建Row对象，每个Row对象都是rowRDD中的一行
    val rowRDD = shuihuRDD.map(p => Row(p(0).toInt, p(1).trim, p(2).trim, p(3).trim))

    //建立起Row对象和模式之间的对应关系，也就是把数据和模式对应起来
    val shuihuDF = spark.createDataFrame(rowRDD, schema)

    //下面创建一个prop变量用来保存JDBC连接参数
    val prop = new Properties()
    prop.put("user", "postgres") //表示用户名
    prop.put("password", "postgres") //表示密码
    //prop.put("driver","com.mysql.jdbc.Driver") //表示驱动程序是com.mysql.jdbc.Driver

    //下面就可以连接数据库，采用append模式，表示追加记录到数据库shuihu表中
    shuihuDF.write.mode("append").jdbc("jdbc:postgresql://127.0.0.1:5432/geodb", "shuihu", prop)
    println("Insert Success")
  }
}

2.5 写入数据方法2

通过定义HaoHan的样例类，这种方式比较简洁
WritePostSQL2.scala

import org.apache.spark.sql.SparkSession

import org.apache.spark.sql.catalyst.ScalaReflection
import org.apache.spark.sql.types.StructType
import java.util.Properties

case class HaoHan(id:Int,xingzuo:String,chuohao:String,name:String)

object WritePostSQL2 {
  def main(args: Array[String]): Unit = {
    //创建SparkSession入口
    val spark=SparkSession
      .builder()
      .appName("haha")
      .master("local[1]")
      .getOrCreate()

    //隐式转换，自动将ds转行为df
    import spark.implicits._
    //数据路径
    val datapath="D:\\data\\shuihu1.txt"
    //创建df，并指定数据类型，最核心的部分
    val shuihuDF = spark.read.format("csv")
      .option("sep","\t")
      .schema(ScalaReflection.schemaFor[HaoHan].dataType.asInstanceOf[StructType])
      .load(datapath)
      //.as[HaoHan]  //如果不用schema全字符串字段 需要这一行直接转为样例类

    //写出到数据库，第一种连接数据库的方式
    shuihuDF.write
      .mode("append")   //如果不用追加模式则报错表已经存在，换个不存在的表会自动创建并写入数据
      .format("jdbc")
      .option("url", "jdbc:postgresql://127.0.0.1:5432/geodb")
      .option("dbtable", "shuihu2")
      .option("user", "postgres")
      .option("password", "postgres")
      .save()

//    //写出到数据库 第二种连接数据库的方式
//    val prop = new Properties()
//    prop.put("user", "postgres") //表示用户名
//    prop.put("password", "postgres") //表示密码
//    prop.put("driver","org.postgresql.Driver") //表示驱动程序
//    //写入数据
//    shuihuDF.write
//      .mode("append")
//      .jdbc("jdbc:postgresql://127.0.0.1:5432/geodb","shuihu3",prop)

    //结束
    println("Insert Success")
  }
}

在psql中查看数据

select count(*) from shuihu;

结果为108
表开头部分
表结尾

2.6 读取数据

比较简单了，直接读取为DataFrame即可
ConnetPostSQ.scala

import org.apache.spark.sql.SparkSession
import java.util.Properties
object ConnetPostSQL {
  def main(args: Array[String]): Unit = {
    //创建SparkSession
    val spark=SparkSession.builder().appName("haha")
      .master("local[*]").getOrCreate()
    //读取postgresql
    val jdbcDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:postgresql://127.0.0.1:5432/geodb")
      .option("dbtable", "shuihu")
      .option("user", "postgres")
      .option("password", "postgres")
      .load()
    jdbcDF.printSchema()
    jdbcDF.select("id","name").show()
    //创建临时表
    jdbcDF.createOrReplaceTempView("shuihu")
    //执行查询
    spark.sql("select * from shuihu limit 36").show()
    //插入数据

  }
}

附录 108将名单

1	天魁星	呼保义	宋江
2	天罡星	玉麒麟	卢俊义
3	天机星	智多星	吴用
4	天闲星	入云龙	公孙胜
5	天勇星	大刀	关胜
6	天雄星	豹子头	林冲
7	天猛星	霹雳火	秦明
8	天威星	双鞭	呼延灼
9	天英星	小李广	花荣
10	天贵星	小旋风	柴进
11	天富星	扑天	李应
12	天满星	美髯公	朱仝
13	天孤星	花和尚	鲁智深
14	天伤星	行者	武松
15	天立星	双抢将	董平
16	天捷星	没羽箭	张清
17	天暗星	青面兽	扬志
18	天佑星	金抢手	徐宁
19	天空星	急先锋	索超
20	天速星	神行太保	戴宗
21	天异星	赤发鬼	刘唐
22	天杀星	黑旋风	李逵
23	天微星	九纹龙	史进
24	天究星	没遮拦	穆弘
25	天退星	插翅虎	雷横
26	天寿星	混江龙	李俊
27	天剑星	立地太岁	阮小二
28	天平星	船火儿	张横
29	天罪星	短命二郎	阮小五
30	天损星	浪里白条	张顺
31	天败星	活阎罗	阮小七
32	天牢星	病关索	扬雄
33	天慧星	拼命三郎	石秀
34	天暴星	两头蛇	解珍
35	天哭星	双尾蝎	解宝
36	天巧星	浪子	燕青
37	地魁星	神机军师	朱武
38	地煞星	镇三山	黄信
39	地勇星	病尉迟	孙立
40	地杰星	丑郡马	宣赞
41	地雄星	井木犴	赦思文
42	地威星	百胜将军	韩滔
43	地英星	天目将军	彭玑
44	地奇星	圣水将军	单廷
45	地猛星	神火将军	魏定国
46	地文星	圣手书生	萧让
47	地正星	铁面孔目	裴宣
48	地辟星	摩云金翅	欧鹏
49	地阖星	火眼狻猊	邓飞
50	地强星	锦毛虎	燕顺
51	地暗星	锦豹子	扬林
52	地轴星	轰天雷	凌振
53	地会星	神算子	蒋敬
54	地佐星	小温候	吕方
55	地佑星	赛仁贵	郭盛
56	地灵星	神医	安道全
57	地兽星	紫髯伯	皇浦端
58	地微星	矮脚虎	王英
59	地慧星	一丈青	扈三娘
60	地暴星	丧门神	鲍旭
61	地默星	混世魔王	樊瑞
62	地猖星	毛头星	孔明
63	地狂星	独火星	孔亮
64	地飞星	八臂哪吒	项充
65	地走星	飞天大圣	李衮
66	地巧星	玉臂匠	金大坚
67	地明星	铁笛仙	马麟
68	地进星	出洞蛟	童威
69	地退星	翻江蜃	童猛
70	地满星	玉幡竿	孟康
71	地遂星	通臂猿	候建
72	地周星	跳涧虎	陈达
73	地隐星	白花蛇	扬春
74	地异星	白面郎	君郑天寿
75	地理星	九尾龟	陶宗旺
76	地俊星	铁扇子	宋清
77	地乐星	铁叫子	乐和
78	地捷星	花项虎	龚旺
79	地速星	中箭虎	丁得孙
80	地镇星	没遮拦	穆春
81	地嵇星	*刀鬼	曹正
82	地魔星	云里金刚	宋万
83	地妖星	摸着天	杜迁
84	地幽星	病大虫	薛永
85	地伏星	金眼彪	施恩
86	地僻星	打虎将	李忠
87	地空星	小霸王	周通
88	地孤星	金钱豹子	汤隆
89	地全星	鬼脸儿	杜兴
90	地短星	出林龙	邹渊
91	地角星	独角龙	邹润
92	地囚星	旱地忽律	朱贵
93	地藏星	笑面虎	朱富
94	地平星	铁臂膊	蔡福
95	地损星	一枝花	蔡庆
96	地奴星	催命判官	李立
97	地察星	青眼虎	李云
98	地恶星	没面目	焦挺
99	地丑星	石将军	石勇
100	地数星	小尉迟	孙新
101	地阴星	母大虫	顾大嫂
102	地刑星	菜园子	张青
103	地壮星	母夜叉	孙二娘
104	地劣星	活阎婆	王定六
105	地健星	险道神	郁保四
106	地耗星	白日鼠	白胜
107	地贼星	鼓上蚤	时迁
108	地狗星	金毛犬	段景住

参考资料

独孤尚亮dugushangliang

关注

10
点赞
踩
17

收藏

觉得还不错? 一键收藏
打赏
2
评论
Spark读写postgresql

记录spark读写postgresql的操作读写mysql同理，个别地方可能需要修改1 连接数据库的两种方式其中一为spark的读取方式，二为通过结合java读取读方法一val jdbcDF = spark.read .format("jdbc") .option("url", "jdbc:postgresql://127.0.0.1:5432/geodb") .option...
复制链接

扫一扫