FunDA（2）－ Streaming Data Operation：流式数据操作

最新推荐文章于 2023-06-28 02:57:36 发布

TIGER_XC

最新推荐文章于 2023-06-28 02:57:36 发布

阅读量627

点赞数

分类专栏：编程技术 Scala Slick FRM-Functional Relation Mapper 函数式编程（Functional Programming）文章标签：编程 scala slick 函数式编程

本文链接：https://blog.csdn.net/TIGER_XC/article/details/53923447

版权

Scala 同时被 3 个专栏收录

270 篇文章 7 订阅

订阅专栏

编程技术

246 篇文章 4 订阅

订阅专栏

函数式编程（Functional Programming）

26 篇文章 0 订阅

订阅专栏

在上一集的讨论里我们介绍并实现了强类型返回结果行。使用强类型主要的目的是当我们把后端数据库SQL批次操作搬到内存里转变成数据流式按行操作时能更方便、准确、高效地选定数据字段。在上集讨论示范里我们用集合的foreach方式模拟了一个最简单的数据流，并把从数据库里批次读取的数据集转换成一串连续的数据行来逐行使用。一般来说完整的流式数据处理流程包括了从数据库中读取数据、根据读取的每行数据状态再对后台数据库进行更新，包括：插入新数据、更新、删除等。那么在上篇中实现的流式操作基础上再添加一种指令行类型就可以完善整个数据处理流程了，就像下面这个图示：

Database => Query -> Collection => Streaming -> DataRow => QueryAction(DataRow) -> ActionRow => execAction(ActionRow) -> Database

如果我们还是以Slick为目标FRM，那么这个ActionRow的类型就是Slick的DBIO[T]了：

package com.bayakala.funda.rowtypes
import slick.dbio._
object ActionType {
  type FDAAction[T] = DBIO[T]
}

记得有一次在一个Scala讨论区里遇到这样一个问题：如何把a表里的status字段更新成b表的status字段值，转化成SQL语句如下：

update a,b set a.status=b.status where a.id=b.id

那位哥们的问题是如何用Slick来实现对a表的更新，不能用sql"???" interpolation 直接调用SQL语句，可能因为要求compile time语法check保障吧。这个问题用Slick Query还真的不太容易解决（能不能解决就不想费功夫去想了），这是因为FRM的SQL批次处理弱点。如果用FunDA的流式操作思路就会很容易解决了，只要用join Query把b.status读出来再用b.id=a.id逐个更新a.status。刚好，下面我们就示范通过ActionRow来解决这个问题。先用下面这段代码来设置测试数据：

import slick.dbio.DBIO
import slick.driver.H2Driver.api._

import scala.concurrent.duration._
import scala.concurrent.{Await, Future}
import scala.util.{Failure, Success}
import scala.concurrent.ExecutionContext.Implicits.global
import slick.jdbc.meta.MTable
object ActionRowTest extends App {

  class ATable(tag: Tag) extends Table[(Int,String,Int)](tag,"TA")  {
    def id = column[Int]("id",O.PrimaryKey)
    def flds = column[String]("aflds")
    def status = column[Int]("status")
    def * = (id,flds,status)
  }
  val tableA = TableQuery[ATable]

  class BTable(tag: Tag) extends Table[(Int,String,Int)](tag,"TB")  {
    def id = column[Int]("id",O.PrimaryKey)
    def flds = column[String]("bflds")
    def status = column[Int]("status")
    def * = (id,flds,status)
  }
  val tableB = TableQuery[BTable]

  val insertAAction =
    tableA ++= Seq (
        (1,"aaa",0),
        (2,"bbb",3),
        (3,"ccc",1),
        (4,"ddd",0),
        (16,"kkk",16)
    )
   val insertBAction =
     tableB ++= Seq (
       (1,"aaa",1),
       (2,"bbb",2),
       (3,"ccc",3),
       (4,"ddd",4),
       (5,"kkk",5)
     )

   val db = Database.forConfig("h2db")


   def tableExists(tables: Vector[MTable], tblname: String) =
    tables.exists {t =>t.name.toString.contains(tblname)}

   def createSchemaIfNotExists(): Future[Unit] = {
    db.run(MTable.getTables).flatMap {
      case tables if !tableExists(tables,".TA") && !tableExists(tables,".TB") =>
        println("Creating schemas for TA and TB...")
        db.run((tableA.schema ++ tableB.schema).create)
      case tables if !tableExists(tables,".TA") =>
        println("Creating schema for TA ...")
        db.run(tableA.schema.create)
      case tables if !tableExists(tables,".TB") =>
        println("Creating schema for TB ...")
        db.run(tableB.schema.create)
      case _ =>
        println("Schema for TA, TB already created.")
        Future.successful()
    }
   }

   def insertInitialData(): Future[Unit] = {
    val cleanInsert = DBIO.seq(
      tableA.delete, tableB.delete,
      insertAAction,
      insertBAction)
    db.run(cleanInsert).andThen {
      case Success(_) => println("Data insert completed.")
      case Failure(e) => println(s"Data insert failed [${e.getMessage}]")
    }
   }

   Await.ready(db.run(sql"DROP TABLE TA; DROP TABLE TB".as[String]),Duration.Inf)

   val initResult = createSchemaIfNotExists().flatMap {_ => insertInitialData()}
   Await.ready(initResult,Duration.Inf)




}

用join query先把这两个表相关的字段值搬到内存转成强类型行FDADataRow：

 val selectAB = for {
     a <- tableA
     b <- tableB
     if (a.id === b.id)
   } yield (a.id,b.id,a.status,b.status)

   case class ABRow (id: Int, asts: Int, bsts: Int)
   def toABRow(raw: (Int,Int,Int,Int)) = ABRow(raw._1,raw._3,raw._4)

   import com.bayakala.funda.rowtypes.DataRowType
  
   val loader = FDADataRow(slick.driver.H2Driver, toABRow _)
   loader.getTypedRows(selectAB.result)(db).foreach {dataRow =>
     println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}")
   }

初始结果如下：

ID:1 Status A = 0, B = 1
ID:2 Status A = 3, B = 2
ID:3 Status A = 1, B = 3
ID:4 Status A = 0, B = 4

现在我们把每条数据行DataRow转成动作行ActionRow。然后把每条DataRow的asts字段值替换成bsts的字段值：

import com.bayakala.funda.rowtypes.ActionType.FDAAction
   def updateAStatus(row: ABRow): FDAAction[Int] = {
     tableA.filter{r => r.id === row.id}
          .map(_.status)
          .update(row.asts)
   }


   loader.getTypedRows(selectAB.result)(db).map(updateAStatus(_)).foreach {
     actionRow =>
       println(s"${actionRow.toString}")
   }

显示结果如下：

slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@492691d7
slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@27216cd
slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@558bdf1f
slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@8576fa0

现在每条DataRow已经被转化成jdbc action类型了。

下一步我们只需要运行这些ActionRow就可以完成任务了：

  def execAction(act: FDAAction[Int]) = db.run(act)
  
   loader.getTypedRows(selectAB.result)(db)
       .map(updateAStatus(_))
       .map(execAction(_))

现在再看看数据库中的TA表状态：

  loader.getTypedRows(selectAB.result)(db).foreach {dataRow =>
    println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}")
  }

结果：
ID:1 Status A = 1, B = 1
ID:2 Status A = 2, B = 2
ID:3 Status A = 3, B = 3
ID:4 Status A = 4, B = 4

我们看到已经正确更新了TA的status字段值。

在这个示范中明显有很多不足之处：如果a.status=b.status应该省略更新步骤。这是因为foreach只能模拟最基本的数据流动。如果我们使用了具备强大功能的Stream工具库如scalaz-stream-fs2，就可以更好控制数据元素的流动。更重要的是scalaz-stream-fs2支持并行运算，那么上面所描述的流程：

Database => Query -> Collection => Streaming -> DataRow => QueryAction(DataRow) -> ActionRow => execAction(ActionRow) -> Database

几个 => 环节：Query、Streaming、QueryAction、execAction将可以并行运算，从而实现充分利用多核CPU硬件资源，提高运算效率的目的。

下面是这次讨论涉及的源代码：

package com.bayakala.funda.rowtypes

import scala.concurrent.duration._
import scala.concurrent.Await
import slick.driver.JdbcProfile

object DataRowType {
  class FDADataRow[SOURCE, TARGET](slickProfile: JdbcProfile,convert: SOURCE  => TARGET){
    import slickProfile.api._

    def getTypedRows(slickAction: DBIO[Iterable[SOURCE]])(slickDB: Database): Iterable[TARGET] =
      Await.result(slickDB.run(slickAction), Duration.Inf).map(raw => convert(raw))
  }

  object FDADataRow {
    def apply[SOURCE, TARGET](slickProfile: JdbcProfile, converter: SOURCE => TARGET): FDADataRow[SOURCE, TARGET] =
      new FDADataRow[SOURCE, TARGET](slickProfile, converter)
  }

}

package com.bayakala.funda.rowtypes
import slick.dbio._
object ActionType {
  type FDAAction[T] = DBIO[T]
}

import slick.dbio.DBIO
import slick.driver.H2Driver.api._

import scala.concurrent.duration._
import scala.concurrent.{Await, Future}
import scala.util.{Failure, Success}
import scala.concurrent.ExecutionContext.Implicits.global
import slick.jdbc.meta.MTable
object ActionRowTest extends App {

  class ATable(tag: Tag) extends Table[(Int,String,Int)](tag,"TA")  {
    def id = column[Int]("id",O.PrimaryKey)
    def flds = column[String]("aflds")
    def status = column[Int]("status")
    def * = (id,flds,status)
  }
  val tableA = TableQuery[ATable]

  class BTable(tag: Tag) extends Table[(Int,String,Int)](tag,"TB")  {
    def id = column[Int]("id",O.PrimaryKey)
    def flds = column[String]("bflds")
    def status = column[Int]("status")
    def * = (id,flds,status)
  }
  val tableB = TableQuery[BTable]

  val insertAAction =
    tableA ++= Seq (
        (1,"aaa",0),
        (2,"bbb",3),
        (3,"ccc",1),
        (4,"ddd",0),
        (16,"kkk",16)
    )
   val insertBAction =
     tableB ++= Seq (
       (1,"aaa",1),
       (2,"bbb",2),
       (3,"ccc",3),
       (4,"ddd",4),
       (5,"kkk",5)
     )

   val db = Database.forConfig("h2db")


   def tableExists(tables: Vector[MTable], tblname: String) =
    tables.exists {t =>t.name.toString.contains(tblname)}

   def createSchemaIfNotExists(): Future[Unit] = {
    db.run(MTable.getTables).flatMap {
      case tables if !tableExists(tables,".TA") && !tableExists(tables,".TB") =>
        println("Creating schemas for TA and TB...")
        db.run((tableA.schema ++ tableB.schema).create)
      case tables if !tableExists(tables,".TA") =>
        println("Creating schema for TA ...")
        db.run(tableA.schema.create)
      case tables if !tableExists(tables,".TB") =>
        println("Creating schema for TB ...")
        db.run(tableB.schema.create)
      case _ =>
        println("Schema for TA, TB already created.")
        Future.successful()
    }
   }

   def insertInitialData(): Future[Unit] = {
    val cleanInsert = DBIO.seq(
      tableA.delete, tableB.delete,
      insertAAction,
      insertBAction)
    db.run(cleanInsert).andThen {
      case Success(_) => println("Data insert completed.")
      case Failure(e) => println(s"Data insert failed [${e.getMessage}]")
    }
   }

   Await.ready(db.run(sql"DROP TABLE TA; DROP TABLE TB".as[String]),Duration.Inf)

   val initResult = createSchemaIfNotExists().flatMap {_ => insertInitialData()}
   Await.ready(initResult,Duration.Inf)


   val selectAB = for {
     a <- tableA
     b <- tableB
     if (a.id === b.id)
   } yield (a.id,b.id,a.status,b.status)

   case class ABRow (id: Int, asts: Int, bsts: Int)
   def toABRow(raw: (Int,Int,Int,Int)) = ABRow(raw._1,raw._3,raw._4)

   import com.bayakala.funda.rowtypes.DataRowType.FDADataRow

   val loader = FDADataRow(slick.driver.H2Driver, toABRow _)
   loader.getTypedRows(selectAB.result)(db).foreach {dataRow =>
     println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}")
   }

   import com.bayakala.funda.rowtypes.ActionType.FDAAction
   def updateAStatus(row: ABRow): FDAAction[Int] = {
     tableA.filter{r => r.id === row.id}
          .map(_.status)
          .update(row.bsts)
   }


   loader.getTypedRows(selectAB.result)(db).map(updateAStatus(_)).foreach {
     actionRow =>
       println(s"${actionRow.toString}")
   }

   def execAction(act: FDAAction[Int]) = db.run(act)

   loader.getTypedRows(selectAB.result)(db)
       .map(updateAStatus(_))
       .map(execAction(_))

  loader.getTypedRows(selectAB.result)(db).foreach {dataRow =>
    println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}")
  }

}

TIGER_XC

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
FunDA（2）－ Streaming Data Operation：流式数据操作

在上一集的讨论里我们介绍并实现了强类型返回结果行。使用强类型主要的目的是当我们把后端数据库SQL批次操作搬到内存里转变成数据流式按行操作时能更方便、准确、高效地选定数据字段。在上集讨论示范里我们用集合的foreach方式模拟了一个最简单的数据流，并把从数据库里批次读取的数据集转换成一串连续的数据行来逐行使用。一般来说完整的流式数据处理流程包括了从数据库中读取数据、根据读取的每行数据状态再对后台数据
复制链接

扫一扫

专栏目录