ScalikeJDBCでMySQLにBulk Insertする

Bulk insert to MySQL Table by ScalikeJDBC

テーブル定義

CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

サンプルコード

import scalikejdbc._

object BulkInsert extends App {

  // initialize JDBC driver & connection pool
  Class.forName("com.mysql.jdbc.Driver")
  ConnectionPool.singleton("jdbc:mysql://hostname/daname?rewriteBatchedStatements=true", "user", "password")

  // ad-hoc session provider on the REPL
  implicit val session = AutoSession

  val params: Seq[Seq[Any]] = (1 to 1000).map(i => Seq(i, "user_" + i))
  SQL("insert into members values (?, ?)").batch(params: _*).apply()
}

• ポイントは rewriteBatchedStatements=true を使用すること

build.sbt

scalaVersion := "2.11.8"

libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"        % "2.4.+",
  "ch.qos.logback"  %  "logback-classic"    % "1.1.+",
  "mysql" % "mysql-connector-java" % "6.0.3"

)

MySQLのgeneral logでbulk insertを確認する

$ brew services stop mysql 
$ sudo mysql.server start --general-log
$ tail -f /usr/local/var/mysql/hostname.log

参考

GitHub - scalikejdbc/scalikejdbc: A tidy SQL-based DB access library for Scala developers. This library naturally wraps JDBC APIs and provides you easy-to-use APIs.

scalikejdbc/QueryInterfaceSpec.scala at 845c40025a7cfa146d7e9099321e0791ce554854 · scalikejdbc/scalikejdbc · GitHub

MacでMySQLのクエリログを確認する方法 | Macha's Dev Blog

MySQL Connector/Jにおける大量INSERTのチューニング - SH2の日記