java.sql.DriverManager Scala Examples
The following examples show how to use java.sql.DriverManager.
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example.
Example 1
| Source File: DNSstat.scala From jdbcsink with Apache License 2.0 | 6 votes |
|
import org.apache.spark.sql.SparkSession
import java.util.Properties
import org.apache.spark.sql.types._
import org.apache.spark.sql.functions.{from_json,window}
import java.sql.{Connection,Statement,DriverManager}
import org.apache.spark.sql.ForeachWriter
import org.apache.spark.sql.Row
class JDBCSink() extends ForeachWriter[Row]{
val driver = "com.mysql.jdbc.Driver"
var connection:Connection = _
var statement:Statement = _
def open(partitionId: Long,version: Long): Boolean = {
Class.forName(driver)
connection = DriverManager.getConnection("jdbc:mysql://10.88.1.102:3306/aptwebservice", "root", "mysqladmin")
statement = connection.createStatement
true
}
def process(value: Row): Unit = {
statement.executeUpdate("replace into DNSStat(ip,domain,time,count) values("
+ "'" + value.getString(0) + "'" + ","//ip
+ "'" + value.getString(1) + "'" + ","//domain
+ "'" + value.getTimestamp(2) + "'" + "," //time
+ value.getLong(3) //count
+ ")")
}
def close(errorOrNull: Throwable): Unit = {
connection.close
}
}
object DNSstatJob{
val schema: StructType = StructType(
Seq(StructField("Vendor", StringType,true),
StructField("Id", IntegerType,true),
StructField("Time", LongType,true),
StructField("Conn", StructType(Seq(
StructField("Proto", IntegerType, true),
StructField("Sport", IntegerType, true),
StructField("Dport", IntegerType, true),
StructField("Sip", StringType, true),
StructField("Dip", StringType, true)
)), true),
StructField("Dns", StructType(Seq(
StructField("Domain", StringType, true),
StructField("IpCount", IntegerType, true),
StructField("Ip", StringType, true)
)), true)))
def main(args: Array[String]) {
val spark=SparkSession
.builder
.appName("DNSJob")
.config("spark.some.config.option", "some-value")
.getOrCreate()
import spark.implicits._
val connectionProperties = new Properties()
connectionProperties.put("user", "root")
connectionProperties.put("password", "mysqladmin")
val bruteForceTab = spark.read
.jdbc("jdbc:mysql://10.88.1.102:3306/aptwebservice", "DNSTab",connectionProperties)
bruteForceTab.registerTempTable("DNSTab")
val lines = spark
.readStream
.format("kafka")
.option("kafka.bootstrap.servers", "10.94.1.110:9092")
.option("subscribe","xdr")
//.option("startingOffsets","earliest")
.option("startingOffsets","latest")
.load()
.select(from_json($"value".cast(StringType),schema).as("jsonData"))
lines.registerTempTable("xdr")
val filterDNS = spark.sql("select CAST(from_unixtime(xdr.jsonData.Time DIV 1000000) as timestamp) as time,xdr.jsonData.Conn.Sip as sip, xdr.jsonData.Dns.Domain from xdr inner join DNSTab on xdr.jsonData.Dns.domain = DNSTab.domain")
val windowedCounts = filterDNS
.withWatermark("time","5 minutes")
.groupBy(window($"time", "1 minutes", "1 minutes"),$"sip",$"domain")
.count()
.select($"sip",$"domain",$"window.start",$"count")
val writer = new JDBCSink()
val query = windowedCounts
.writeStream
.foreach(writer)
.outputMode("update")
.option("checkpointLocation","/checkpoint/")
.start()
query.awaitTermination()
}
}
Example 2
| Source File: RowDataSourceStrategySuite.scala From multi-tenancy-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources
import java.sql.DriverManager
import java.util.Properties
import org.scalatest.BeforeAndAfter
import org.apache.spark.SparkFunSuite
import org.apache.spark.sql.{DataFrame, Row}
import org.apache.spark.sql.sources._
import org.apache.spark.sql.test.SharedSQLContext
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
class RowDataSourceStrategySuite extends SparkFunSuite with BeforeAndAfter with SharedSQLContext {
import testImplicits._
val url = "jdbc:h2:mem:testdb0"
val urlWithUserAndPass = "jdbc:h2:mem:testdb0;user=testUser;password=testPass"
var conn: java.sql.Connection = null
before {
Utils.classForName("org.h2.Driver")
// Extra properties that will be specified for our database. We need these to test
// usage of parameters from OPTIONS clause in queries.
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("create schema test").executeUpdate()
conn.prepareStatement("create table test.inttypes (a INT, b INT, c INT)").executeUpdate()
conn.prepareStatement("insert into test.inttypes values (1, 2, 3)").executeUpdate()
conn.commit()
sql(
s"""
|CREATE TEMPORARY TABLE inttypes
|USING org.apache.spark.sql.jdbc
|OPTIONS (url '$url', dbtable 'TEST.INTTYPES', user 'testUser', password 'testPass')
""".stripMargin.replaceAll("\n", " "))
}
after {
conn.close()
}
test("SPARK-17673: Exchange reuse respects differences in output schema") {
val df = sql("SELECT * FROM inttypes")
val df1 = df.groupBy("a").agg("b" -> "min")
val df2 = df.groupBy("a").agg("c" -> "min")
val res = df1.union(df2)
assert(res.distinct().count() == 2) // would be 1 if the exchange was incorrectly reused
}
}
Example 3
| Source File: Database.scala From lighthouse with Apache License 2.0 | 5 votes |
|
package be.dataminded.lighthouse.common
import java.sql.{Connection, DriverManager}
import be.dataminded.lighthouse.datalake._
class Database(val driverClassName: String, url: String, properties: Map[String, String] = Map.empty) {
def withConnection[A](autoCommit: Boolean)(block: (Connection) => A): A = {
val connection = createConnection(autoCommit)
try {
block(connection)
} finally {
connection.close()
}
}
def withConnection[A](block: (Connection) => A): A = withConnection(autoCommit = true)(block)
private def createConnection(autoCommit: Boolean): Connection = {
Class.forName(driverClassName)
val connection = DriverManager.getConnection(url, properties)
connection.setAutoCommit(autoCommit)
connection
}
}
object Database {
def apply(driver: String, url: String, properties: Map[String, String] = Map.empty): Database =
new Database(driver, url, properties)
def inMemory(name: String, urlOptions: Map[String, String] = Map.empty): Database = {
val urlExtra = urlOptions.map { case (k, v) => s"$k=$v" }.mkString(";", ";", "")
val url = s"jdbc:h2:mem:$name$urlExtra;"
new Database("org.h2.Driver", url)
}
}
Example 4
| Source File: DriverRegistry.scala From BigDatalog with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.Logging
import org.apache.spark.util.Utils
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 5
| Source File: PostgreSqlMain.scala From ingraph with Eclipse Public License 1.0 | 5 votes |
|
package ingraph.compiler.sql
import java.sql.DriverManager
import ingraph.compiler.sql.Util.withResources
import org.apache.commons.lang3.StringUtils
object PostgreSqlMain extends App {
// https://github.com/yandex-qatools/postgresql-embedded/tree/ea26f6945478da8e8b48e382f8869896da2fda30#howto
withResources(new EmbeddedPostgresWrapper) { postgres =>
withResources(DriverManager.getConnection(postgres.Url)) { conn =>
withResources(conn.createStatement()) {
_.execute("CREATE TABLE films (code char(5));")
}
withResources(conn.createStatement()) {
_.execute("INSERT INTO films VALUES ('movie');")
}
withResources(conn.createStatement) { statement =>
assert(statement.execute("SELECT * FROM films;"))
assert(statement.getResultSet().next())
val code = statement.getResultSet().getString("code")
val separator = StringUtils.repeat('=', 42)
println(separator)
println(code)
println(separator)
assert(code == "movie")
}
}
}
}
Example 6
| Source File: RowDataSourceStrategySuite.scala From Spark-2.3.1 with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources
import java.sql.DriverManager
import java.util.Properties
import org.scalatest.BeforeAndAfter
import org.apache.spark.SparkFunSuite
import org.apache.spark.sql.{DataFrame, Row}
import org.apache.spark.sql.sources._
import org.apache.spark.sql.test.SharedSQLContext
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
class RowDataSourceStrategySuite extends SparkFunSuite with BeforeAndAfter with SharedSQLContext {
import testImplicits._
val url = "jdbc:h2:mem:testdb0"
val urlWithUserAndPass = "jdbc:h2:mem:testdb0;user=testUser;password=testPass"
var conn: java.sql.Connection = null
before {
Utils.classForName("org.h2.Driver")
// Extra properties that will be specified for our database. We need these to test
// usage of parameters from OPTIONS clause in queries.
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("create schema test").executeUpdate()
conn.prepareStatement("create table test.inttypes (a INT, b INT, c INT)").executeUpdate()
conn.prepareStatement("insert into test.inttypes values (1, 2, 3)").executeUpdate()
conn.commit()
sql(
s"""
|CREATE OR REPLACE TEMPORARY VIEW inttypes
|USING org.apache.spark.sql.jdbc
|OPTIONS (url '$url', dbtable 'TEST.INTTYPES', user 'testUser', password 'testPass')
""".stripMargin.replaceAll("\n", " "))
}
after {
conn.close()
}
test("SPARK-17673: Exchange reuse respects differences in output schema") {
val df = sql("SELECT * FROM inttypes")
val df1 = df.groupBy("a").agg("b" -> "min")
val df2 = df.groupBy("a").agg("c" -> "min")
val res = df1.union(df2)
assert(res.distinct().count() == 2) // would be 1 if the exchange was incorrectly reused
}
}
Example 7
| Source File: DriverRegistry.scala From Spark-2.3.1 with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.internal.Logging
import org.apache.spark.util.Utils
DriverManager.getDrivers
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 8
| Source File: JdbcConnectionUriSuite.scala From Spark-2.3.1 with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.thriftserver
import java.sql.DriverManager
import org.apache.hive.jdbc.HiveDriver
import org.apache.spark.util.Utils
class JdbcConnectionUriSuite extends HiveThriftServer2Test {
Utils.classForName(classOf[HiveDriver].getCanonicalName)
override def mode: ServerMode.Value = ServerMode.binary
val JDBC_TEST_DATABASE = "jdbc_test_database"
val USER = System.getProperty("user.name")
val PASSWORD = ""
override protected def beforeAll(): Unit = {
super.beforeAll()
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"CREATE DATABASE $JDBC_TEST_DATABASE")
connection.close()
}
override protected def afterAll(): Unit = {
try {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"DROP DATABASE $JDBC_TEST_DATABASE")
connection.close()
} finally {
super.afterAll()
}
}
test("SPARK-17819 Support default database in connection URIs") {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/$JDBC_TEST_DATABASE"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
try {
val resultSet = statement.executeQuery("select current_database()")
resultSet.next()
assert(resultSet.getString(1) === JDBC_TEST_DATABASE)
} finally {
statement.close()
connection.close()
}
}
}
Example 9
| Source File: DriverRegistry.scala From spark1.52 with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.Logging
import org.apache.spark.util.Utils
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
def getDriverClassName(url: String): String = DriverManager.getDriver(url) match {
case wrapper: DriverWrapper => wrapper.wrapped.getClass.getCanonicalName
case driver => driver.getClass.getCanonicalName
}
}
Example 10
| Source File: PostgresReader.scala From DataQuality with GNU Lesser General Public License v3.0 | 5 votes |
|
package it.agilelab.bigdata.DataQuality.utils.io.db.readers
import java.sql.{Connection, DriverManager, ResultSet}
import it.agilelab.bigdata.DataQuality.sources.DatabaseConfig
case class PostgresReader(config: DatabaseConfig) extends TableReader {
override val connectionUrl: String = "jdbc:postgresql://" + config.host
override def runQuery[T](query: String,
transformOutput: ResultSet => T): T = {
val connection = getConnection
val statement = connection.createStatement()
statement.setFetchSize(1000)
val queryResult = statement.executeQuery(query)
val result = transformOutput(queryResult)
statement.close()
result
}
override def getConnection: Connection = {
val connectionProperties = new java.util.Properties()
config.user match {
case Some(user) => connectionProperties.put("user", user)
case None =>
}
config.password match {
case Some(pwd) => connectionProperties.put("password", pwd)
case None =>
}
connectionProperties.put("driver", "org.postgresql.Driver")
DriverManager.getConnection(connectionUrl, connectionProperties)
}
}
Example 11
| Source File: WithStandardCodegen.scala From quill with Apache License 2.0 | 5 votes |
|
package io.getquill.codegen
import java.sql.DriverManager
import io.getquill.codegen.jdbc.gen.JdbcGeneratorBase
import io.getquill.codegen.jdbc.model.JdbcTypes.JdbcQuerySchemaNaming
import io.getquill.codegen.model.Stereotyper.Namespacer
import io.getquill.codegen.model._
import io.getquill.codegen.util.SchemaConfig
import io.getquill.codegen.util.StringUtil._
trait WithStandardCodegen {
def defaultNamespace: String
def standardCodegen(
schemaConfig: SchemaConfig,
tableFilter: RawSchema[JdbcTableMeta, JdbcColumnMeta] => Boolean = _ => true,
entityNamingStrategy: NameParser = LiteralNames,
entityNamespacer: Namespacer[JdbcTableMeta] = ts => ts.tableSchem.getOrElse(defaultNamespace),
entityMemberNamer: JdbcQuerySchemaNaming = ts => ts.tableName.snakeToLowerCamel
) =
new JdbcGeneratorBase(
() => { DriverManager.getConnection(s"jdbc:h2:mem:sample;INIT=RUNSCRIPT FROM 'classpath:h2_schema_precursor.sql'\\;RUNSCRIPT FROM 'classpath:${schemaConfig.fileName}'", "sa", "sa") }
) {
override def filter(tc: RawSchema[TableMeta, ColumnMeta]): Boolean = super.filter(tc) && tableFilter(tc)
override def nameParser: NameParser = entityNamingStrategy
override val namespacer: Namespacer[TableMeta] = entityNamespacer
override def querySchemaNaming: QuerySchemaNaming = entityMemberNamer
override def packagingStrategy: PackagingStrategy = super.packagingStrategy
}
}
Example 12
| Source File: SingletonConnection.scala From gimel with Apache License 2.0 | 5 votes |
|
package com.paypal.gimel.jdbc.utilities
import java.sql.{Connection, DriverManager}
object SingletonConnection {
private var connection: Connection = null
def getConnection(url: String, username: String, password: String): Connection = synchronized {
if (connection == null || connection.isClosed) {
connection = DriverManager.getConnection(url, username, password)
}
connection
}
def getConnection(jdbcConnectionUtility: JDBCConnectionUtility): Connection = synchronized {
if (connection == null || connection.isClosed) {
connection = jdbcConnectionUtility.getJdbcConnectionAndSetQueryBand()
}
connection
}
}
Example 13
| Source File: DriverRegistry.scala From multi-tenancy-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.internal.Logging
import org.apache.spark.util.Utils
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 14
| Source File: JdbcConnectionUriSuite.scala From multi-tenancy-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.thriftserver
import java.sql.DriverManager
import org.apache.hive.jdbc.HiveDriver
import org.apache.spark.util.Utils
class JdbcConnectionUriSuite extends HiveThriftServer2Test {
Utils.classForName(classOf[HiveDriver].getCanonicalName)
override def mode: ServerMode.Value = ServerMode.binary
val JDBC_TEST_DATABASE = "jdbc_test_database"
val USER = System.getProperty("user.name")
val PASSWORD = ""
override protected def beforeAll(): Unit = {
super.beforeAll()
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"CREATE DATABASE $JDBC_TEST_DATABASE")
connection.close()
}
override protected def afterAll(): Unit = {
try {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"DROP DATABASE $JDBC_TEST_DATABASE")
connection.close()
} finally {
super.afterAll()
}
}
test("SPARK-17819 Support default database in connection URIs") {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/$JDBC_TEST_DATABASE"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
try {
val resultSet = statement.executeQuery("select current_database()")
resultSet.next()
assert(resultSet.getString(1) === JDBC_TEST_DATABASE)
} finally {
statement.close()
connection.close()
}
}
}
Example 15
| Source File: H2Utils.scala From morpheus with Apache License 2.0 | 5 votes |
|
package org.opencypher.morpheus.testing.utils
import java.sql.{Connection, DriverManager, ResultSet, Statement}
import org.apache.spark.sql._
import org.opencypher.morpheus.api.io.sql.SqlDataSourceConfig
object H2Utils {
implicit class ConnOps(conn: Connection) {
def run[T](code: Statement => T): T = {
val stmt = conn.createStatement()
try { code(stmt) } finally { stmt.close() }
}
def execute(sql: String): Boolean = conn.run(_.execute(sql))
def query(sql: String): ResultSet = conn.run(_.executeQuery(sql))
def update(sql: String): Int = conn.run(_.executeUpdate(sql))
}
def withConnection[T](cfg: SqlDataSourceConfig.Jdbc)(code: Connection => T): T = {
Class.forName(cfg.driver)
val conn = (cfg.options.get("user"), cfg.options.get("password")) match {
case (Some(user), Some(pass)) =>
DriverManager.getConnection(cfg.url, user, pass)
case _ =>
DriverManager.getConnection(cfg.url)
}
try { code(conn) } finally { conn.close() }
}
implicit class DataFrameWriterOps(write: DataFrameWriter[Row]) {
def maybeOption(key: String, value: Option[String]): DataFrameWriter[Row] =
value.fold(write)(write.option(key, _))
}
implicit class DataFrameSqlOps(df: DataFrame) {
def saveAsSqlTable(cfg: SqlDataSourceConfig.Jdbc, tableName: String): Unit =
df.write
.mode(SaveMode.Overwrite)
.format("jdbc")
.option("url", cfg.url)
.option("driver", cfg.driver)
.options(cfg.options)
.option("dbtable", tableName)
.save()
}
}
Example 16
| Source File: EmbeddedPostgreSQL.scala From akka-http-microservice-templates with MIT License | 5 votes |
|
package utils
import java.nio.file.Paths
import java.util
import ru.yandex.qatools.embed.postgresql.distribution.Version.V9_6_8
object EmbeddedPostgreSQL {
import ru.yandex.qatools.embed.postgresql.EmbeddedPostgres
val postgres = new EmbeddedPostgres(V9_6_8)
def start = {
val url: String = postgres.start(EmbeddedPostgres.cachedRuntimeConfig(Paths.get("/tmp/postgres")), "localhost", 5432, "users", "user", "password", util.Arrays.asList())
import java.sql.{Connection, DriverManager}
Class.forName("org.postgresql.Driver")
val conn: Connection = DriverManager.getConnection(url)
conn.createStatement().execute(
"""
CREATE SEQUENCE public.users_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
""")
conn.createStatement().execute("""ALTER SEQUENCE public.users_id_seq OWNER TO "user";""")
conn.createStatement().execute(
"""
CREATE TABLE public.users (id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
username character varying(255) COLLATE pg_catalog."default" NOT NULL,
user_age integer NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (id))
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.users OWNER to "user";
""")
}
def stop =
postgres.stop()
}
Example 17
| Source File: EmbeddedPostgreSQL.scala From akka-http-microservice-templates with MIT License | 5 votes |
|
package utils
import java.nio.file.Paths
import java.util
import ru.yandex.qatools.embed.postgresql.distribution.Version.V9_6_8
object EmbeddedPostgreSQL {
import ru.yandex.qatools.embed.postgresql.EmbeddedPostgres
val postgres = new EmbeddedPostgres(V9_6_8)
def start = {
val url: String = postgres.start(EmbeddedPostgres.cachedRuntimeConfig(Paths.get("/tmp/postgres")), "localhost", 5432, "users", "user", "password", util.Arrays.asList())
import java.sql.{Connection, DriverManager}
Class.forName("org.postgresql.Driver")
val conn: Connection = DriverManager.getConnection(url)
conn.createStatement().execute(
"""
CREATE SEQUENCE public.users_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
""")
conn.createStatement().execute("""ALTER SEQUENCE public.users_id_seq OWNER TO "user";""")
conn.createStatement().execute(
"""
CREATE TABLE public.users (id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
username character varying(255) COLLATE pg_catalog."default" NOT NULL,
age integer NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (id))
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.users OWNER to "user";
""")
}
def stop =
postgres.stop()
}
Example 18
| Source File: MysqlSpec.scala From testcontainers-scala with MIT License | 5 votes |
|
package com.dimafeng.testcontainers
import java.sql.DriverManager
import org.scalatest.FlatSpec
class MysqlSpec extends FlatSpec with ForAllTestContainer {
override val container = MySQLContainer()
"Mysql container" should "be started" in {
Class.forName(container.driverClassName)
val connection = DriverManager.getConnection(container.jdbcUrl, container.username, container.password)
val prepareStatement = connection.prepareStatement("select 1")
try {
val resultSet = prepareStatement.executeQuery()
resultSet.next()
assert(1 == resultSet.getInt(1))
resultSet.close()
} finally {
prepareStatement.close()
}
connection.close()
}
}
Example 19
| Source File: PostgresqlSpec.scala From testcontainers-scala with MIT License | 5 votes |
|
package com.dimafeng.testcontainers
import java.sql.DriverManager
import org.scalatest.FlatSpec
class PostgresqlSpec extends FlatSpec with ForAllTestContainer {
override val container = PostgreSQLContainer()
"PostgreSQL container" should "be started" in {
Class.forName(container.driverClassName)
val connection = DriverManager.getConnection(container.jdbcUrl, container.username, container.password)
val preparedStatement = connection.prepareStatement(container.testQueryString)
try {
val resultSet = preparedStatement.executeQuery()
resultSet.next()
assert(1 == resultSet.getInt(1))
resultSet.close()
} finally {
preparedStatement.close()
connection.close()
}
}
}
Example 20
| Source File: OracleSpec.scala From testcontainers-scala with MIT License | 5 votes |
|
package com.dimafeng.testcontainers
import java.sql.DriverManager
import org.scalatest.FlatSpec
class OracleSpec extends FlatSpec with ForAllTestContainer {
override val container: OracleContainer = OracleContainer("oracleinanutshell/oracle-xe-11g")
"Oracle container" should "be started" in {
Class.forName(container.driverClassName)
val connection = DriverManager.getConnection(
container.jdbcUrl,
container.username,
container.password
)
val prepareStatement = connection.prepareStatement(container.testQueryString)
try {
val resultSet = prepareStatement.executeQuery()
resultSet.next()
assert(1 == resultSet.getInt(1))
resultSet.close()
} finally {
prepareStatement.close()
}
connection.close()
}
}
Example 21
| Source File: BackupHandlerSpec.scala From eclair with Apache License 2.0 | 5 votes |
|
package fr.acinq.eclair.db
import java.io.File
import java.sql.DriverManager
import java.util.UUID
import akka.actor.ActorSystem
import akka.testkit.{TestKit, TestProbe}
import fr.acinq.eclair.channel.ChannelPersisted
import fr.acinq.eclair.db.sqlite.SqliteChannelsDb
import fr.acinq.eclair.wire.ChannelCodecsSpec
import fr.acinq.eclair.{TestConstants, TestKitBaseClass, TestUtils, randomBytes32}
import org.scalatest.funsuite.AnyFunSuiteLike
class BackupHandlerSpec extends TestKitBaseClass with AnyFunSuiteLike {
test("process backups") {
val db = TestConstants.inMemoryDb()
val wip = new File(TestUtils.BUILD_DIRECTORY, s"wip-${UUID.randomUUID()}")
val dest = new File(TestUtils.BUILD_DIRECTORY, s"backup-${UUID.randomUUID()}")
wip.deleteOnExit()
dest.deleteOnExit()
val channel = ChannelCodecsSpec.normal
db.channels.addOrUpdateChannel(channel)
assert(db.channels.listLocalChannels() == Seq(channel))
val handler = system.actorOf(BackupHandler.props(db, dest, None))
val probe = TestProbe()
system.eventStream.subscribe(probe.ref, classOf[BackupEvent])
handler ! ChannelPersisted(null, TestConstants.Alice.nodeParams.nodeId, randomBytes32, null)
handler ! ChannelPersisted(null, TestConstants.Alice.nodeParams.nodeId, randomBytes32, null)
handler ! ChannelPersisted(null, TestConstants.Alice.nodeParams.nodeId, randomBytes32, null)
probe.expectMsg(BackupCompleted)
val db1 = new SqliteChannelsDb(DriverManager.getConnection(s"jdbc:sqlite:$dest"))
val check = db1.listLocalChannels()
assert(check == Seq(channel))
}
}
Example 22
| Source File: PStatementTest.scala From yoda-orm with MIT License | 5 votes |
|
package in.norbor.yoda.orm
import java.sql.{Connection, DriverManager, ResultSet, Timestamp}
import com.typesafe.scalalogging.LazyLogging
import in.norbor.yoda.implicits.JavaSqlImprovement._
import mocks.People
import org.joda.time.DateTime
import org.scalatest.funsuite.AnyFunSuite
class PStatementTest extends AnyFunSuite {
Class.forName("org.h2.Driver")
private implicit val conn: Connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "")
test("0) apply") {
val ps = PStatement("SELECT 1")(conn)
assert(ps !== null)
ps.equals(null)
ps.canEqual(null)
ps.hashCode
ps.toString
ps.productPrefix
ps.productArity
ps.productElement(0)
ps.productIterator
ps.copy()
}
test("0) query") {
PStatement("DROP TABLE IF EXISTS yoda_sql; CREATE TABLE yoda_sql (id INTEGER);")
.update
}
test("0) update") {
val rs = PStatement("""select 1""")
.query
assert(rs !== null)
}
test("0) queryOne with non index parameter") {
val result = PStatement("""select ?, ?, ?, ?, ?, ?, ?, ?""")
.setBoolean(true)
.setInt(1)
.setLong(1L)
.setDouble(1)
.setString("YO")
.setDateTime(DateTime.now)
.setTimestamp(new Timestamp(System.currentTimeMillis))
.setTimestamp(null)
.queryOne(parse)
assert(result.head._1 === true)
}
test("3) queryList with parse method") {
val peoples = PStatement("""select 1 as id, 'Peerapat' as name, now() as born;""")
.queryList(parsePeople)
assert(peoples.head.id === 1)
assert(peoples.head.name === "Peerapat")
assert(peoples.head.born.getMillis <= DateTime.now.getMillis)
}
test("5) batch") {
val insert = PStatement("INSERT INTO yoda_sql VALUES(?)")
.setInt(1)
.addBatch()
.setInt(2)
.addBatch()
.executeBatch
assert(insert.length === 2)
}
private def parse(rs: ResultSet): (Boolean, Int, Long, Double, String, DateTime, Timestamp) = (rs.getBoolean(1)
, rs.getInt(2)
, rs.getLong(3)
, rs.getDouble(4)
, rs.getString(5)
, rs.getDateTime(6)
, rs.getTimestamp(7)
)
private def parsePeople(rs: ResultSet): People = People(id = rs.getLong("id")
, name = rs.getString("name")
, born = rs.getDateTime("born")
)
}
Example 23
| Source File: Main.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
package com.example
import com.example.db.Tables
import java.sql.DriverManager
import org.jooq.impl.DSL
object Main extends App {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try {
val e = Tables.EMPLOYEE.as("e")
val query = DSL.using(conn)
.selectFrom(e)
.limit(2).offset(1)
println(s"query:\n${query.getSQL}")
println(s"params:\n${query.getParams}")
val result = query.fetch()
println(s"result:\n$result")
} finally {
conn.close()
}
}
Example 24
| Source File: Setup.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
import java.sql.DriverManager
import org.h2.tools.RunScript
import sbt.Keys._
import sbt._
object Setup extends AutoPlugin {
override def trigger: PluginTrigger = allRequirements
object autoImport {
val setup = taskKey[Unit]("Setup databse")
}
import autoImport._
override def globalSettings: Seq[Setting[_]] = Seq(
setup := setupTask.value,
onLoad ~= { _.andThen("setup" :: _) }
)
private def setupTask = Def.task {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try
IO.reader(file("setup.sql")) {
RunScript.execute(conn, _)
}
finally
conn.close()
}
}
Example 25
| Source File: Main.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
package com.example
import com.example.db.Tables
import java.sql.DriverManager
import org.jooq.impl.DSL
object Main extends App {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try {
val e = Tables.EMPLOYEE.as("e")
val query = DSL.using(conn)
.selectFrom(e)
.limit(2).offset(1)
println(s"query:\n${query.getSQL}")
println(s"params:\n${query.getParams}")
val result = query.fetch()
println(s"result:\n$result")
} finally {
conn.close()
}
}
Example 26
| Source File: Setup.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
import java.sql.DriverManager
import org.h2.tools.RunScript
import sbt.Keys._
import sbt._
object Setup extends AutoPlugin {
override def trigger: PluginTrigger = allRequirements
object autoImport {
val setup = taskKey[Unit]("Setup databse")
}
import autoImport._
override def globalSettings: Seq[Setting[_]] = Seq(
setup := setupTask.value,
onLoad ~= { _.andThen("setup" :: _) }
)
private def setupTask = Def.task {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try
IO.reader(file("setup.sql")) {
RunScript.execute(conn, _)
}
finally
conn.close()
}
}
Example 27
| Source File: Main.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
package com.example
import com.example.db.Tables
import java.sql.DriverManager
import org.jooq.impl.DSL
object Main extends App {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try {
val e = Tables.EMPLOYEE.as("e")
val query = DSL.using(conn)
.selectFrom(e)
.limit(2).offset(1)
println(s"query:\n${query.getSQL}")
println(s"params:\n${query.getParams}")
val result = query.fetch()
println(s"result:\n$result")
} finally {
conn.close()
}
}
Example 28
| Source File: Setup.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
import java.sql.DriverManager
import org.h2.tools.RunScript
import sbt.Keys._
import sbt._
object Setup extends AutoPlugin {
override def trigger: PluginTrigger = allRequirements
object autoImport {
val setup = taskKey[Unit]("Setup databse")
}
import autoImport._
override def globalSettings: Seq[Setting[_]] = Seq(
setup := setupTask.value,
onLoad ~= { _.andThen("setup" :: _) }
)
private def setupTask = Def.task {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try
IO.reader(file("setup.sql")) {
RunScript.execute(conn, _)
}
finally
conn.close()
}
}
Example 29
| Source File: Main.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
package com.example
import com.example.db.Tables
import java.sql.DriverManager
import org.jooq.impl.DSL
object Main extends App {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try {
val e = Tables.EMPLOYEE.as("e")
val query = DSL.using(conn)
.selectFrom(e)
.limit(2).offset(1)
println(s"query:\n${query.getSQL}")
println(s"params:\n${query.getParams}")
val result = query.fetch()
println(s"result:\n$result")
} finally {
conn.close()
}
}
Example 30
| Source File: Setup.scala From sbt-jooq with Apache License 2.0 | 5 votes |
|
import java.sql.DriverManager
import org.h2.tools.RunScript
import sbt.Keys._
import sbt._
object Setup extends AutoPlugin {
override def trigger: PluginTrigger = allRequirements
object autoImport {
val setup = taskKey[Unit]("Setup databse")
}
import autoImport._
override def globalSettings: Seq[Setting[_]] = Seq(
setup := setupTask.value,
onLoad ~= { _.andThen("setup" :: _) }
)
private def setupTask = Def.task {
Class.forName("org.h2.Driver")
val conn = DriverManager.getConnection("jdbc:h2:./test")
try
IO.reader(file("setup.sql")) {
RunScript.execute(conn, _)
}
finally
conn.close()
}
}
Example 31
| Source File: DriverRegistry.scala From spark-athena with Apache License 2.0 | 5 votes |
|
package io.github.tmheo.spark.athena
import java.sql.{Driver, DriverManager}
import io.github.tmheo.spark.athena.util.Utils
import org.apache.spark.internal.Logging
import scala.collection.mutable
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 32
| Source File: HiveJDBCUtils.scala From gimel with Apache License 2.0 | 5 votes |
|
package com.paypal.gimel.hive.utilities
import java.security._
import java.sql.{Connection, DriverManager, Statement}
import org.apache.hadoop.conf.Configuration
import org.apache.hadoop.security.UserGroupInformation
import com.paypal.gimel.common.conf.{GimelConstants, GimelProperties}
object HiveJDBCUtils {
def apply(conf: GimelProperties, cluster: String): HiveJDBCUtils = {
new HiveJDBCUtils(conf, cluster)
}
}
class HiveJDBCUtils(val props: GimelProperties, cluster: String = "unknown_cluster") {
val logger = com.paypal.gimel.logger.Logger()
logger.info("Using Supplied KeyTab to authenticate KDC...")
val conf = new Configuration
conf.set(GimelConstants.SECURITY_AUTH, "kerberos")
UserGroupInformation.setConfiguration(conf)
val ugi: UserGroupInformation = UserGroupInformation.loginUserFromKeytabAndReturnUGI(props.principal, props.keytab)
UserGroupInformation.setLoginUser(ugi)
def withStatement(fn: Statement => Any): Any = {
def MethodName: String = new Exception().getStackTrace.apply(1).getMethodName
logger.info(" @Begin --> " + MethodName)
withConnection {
connection =>
val statement = connection.createStatement
var output: Any = None
try {
output = fn(statement)
} catch {
case e: Throwable =>
e.printStackTrace
throw e
}
finally {
if (!statement.isClosed) {
statement.close
}
}
output
}
}
}
Example 33
| Source File: CustomerTimerDemo.scala From flink-rookie with Apache License 2.0 | 5 votes |
|
package com.venn.stream.api.timer
import java.io.File
import java.sql.{Connection, DriverManager, PreparedStatement, SQLException}
import java.util
import java.util.{Timer, TimerTask}
import org.apache.flink.api.scala._
import com.venn.common.Common
import com.venn.util.TwoStringSource
import org.apache.flink.api.common.functions.RichMapFunction
import org.apache.flink.api.common.serialization.SimpleStringSchema
import org.apache.flink.configuration.Configuration
import org.apache.flink.runtime.state.filesystem.FsStateBackend
import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.streaming.api.{CheckpointingMode, TimeCharacteristic}
import org.apache.flink.streaming.connectors.kafka.FlinkKafkaProducer
import org.slf4j.LoggerFactory
def query() = {
logger.info("query mysql")
try {
Class.forName(driverName)
conn = DriverManager.getConnection(jdbcUrl, username, password)
ps = conn.prepareStatement("select id,name from venn.timer")
val rs = ps.executeQuery
while (!rs.isClosed && rs.next) {
val id = rs.getString(1)
val name = rs.getString(2)
map.put(id, name)
}
logger.info("get config from db size : {}", map.size())
} catch {
case e@(_: ClassNotFoundException | _: SQLException) =>
e.printStackTrace()
} finally {
if (conn != null) {
conn.close()
}
}
}
})
// .print()
val sink = new FlinkKafkaProducer[String]("timer_out"
, new SimpleStringSchema()
, Common.getProp)
stream.addSink(sink)
env.execute(this.getClass.getName)
}
}
Example 34
| Source File: MysqlSink1.scala From flink-rookie with Apache License 2.0 | 5 votes |
|
package com.venn.stream.api.jdbcOutput
import java.sql.{Connection, DriverManager, PreparedStatement, SQLException}
import org.apache.flink.api.common.io.OutputFormat
import org.apache.flink.configuration.Configuration
import org.slf4j.{Logger, LoggerFactory}
class MysqlSink1 extends OutputFormat[User]{
val logger: Logger = LoggerFactory.getLogger("MysqlSink1")
var conn: Connection = _
var ps: PreparedStatement = _
val jdbcUrl = "jdbc:mysql://192.168.229.128:3306?useSSL=false&allowPublicKeyRetrieval=true"
val username = "root"
val password = "123456"
val driverName = "com.mysql.jdbc.Driver"
override def configure(parameters: Configuration): Unit = {
// not need
}
override def open(taskNumber: Int, numTasks: Int): Unit = {
Class.forName(driverName)
try {
Class.forName(driverName)
conn = DriverManager.getConnection(jdbcUrl, username, password)
// close auto commit
conn.setAutoCommit(false)
} catch {
case e@(_: ClassNotFoundException | _: SQLException) =>
logger.error("init mysql error")
e.printStackTrace()
System.exit(-1);
}
}
override def writeRecord(user: User): Unit = {
println("get user : " + user.toString)
ps = conn.prepareStatement("insert into async.user(username, password, sex, phone) values(?,?,?,?)")
ps.setString(1, user.username)
ps.setString(2, user.password)
ps.setInt(3, user.sex)
ps.setString(4, user.phone)
ps.execute()
conn.commit()
}
override def close(): Unit = {
if (conn != null){
conn.commit()
conn.close()
}
}
}
Example 35
| Source File: MysqlSink.scala From flink-rookie with Apache License 2.0 | 5 votes |
|
package com.venn.stream.api.jdbcOutput
import java.sql.{Connection, DriverManager, PreparedStatement, SQLException}
import org.apache.flink.configuration.Configuration
import org.apache.flink.streaming.api.functions.sink.{RichSinkFunction, SinkFunction}
import org.slf4j.{Logger, LoggerFactory}
class MysqlSink extends RichSinkFunction[User] {
val logger: Logger = LoggerFactory.getLogger("MysqlSink")
var conn: Connection = _
var ps: PreparedStatement = _
val jdbcUrl = "jdbc:mysql://192.168.229.128:3306?useSSL=false&allowPublicKeyRetrieval=true"
val username = "root"
val password = "123456"
val driverName = "com.mysql.jdbc.Driver"
override def open(parameters: Configuration): Unit = {
Class.forName(driverName)
try {
Class.forName(driverName)
conn = DriverManager.getConnection(jdbcUrl, username, password)
// close auto commit
conn.setAutoCommit(false)
} catch {
case e@(_: ClassNotFoundException | _: SQLException) =>
logger.error("init mysql error")
e.printStackTrace()
System.exit(-1);
}
}
override def invoke(user: User, context: SinkFunction.Context[_]): Unit = {
println("get user : " + user.toString)
ps = conn.prepareStatement("insert into async.user(username, password, sex, phone) values(?,?,?,?)")
ps.setString(1, user.username)
ps.setString(2, user.password)
ps.setInt(3, user.sex)
ps.setString(4, user.phone)
ps.execute()
conn.commit()
}
override def close(): Unit = {
if (conn != null){
conn.commit()
conn.close()
}
}
}
Example 36
| Source File: SparkNRedshiftUtil.scala From SqlShift with MIT License | 5 votes |
|
package com.goibibo.sqlshift
import java.sql.{Connection, DriverManager}
import java.util.Properties
import com.databricks.spark.redshift.RedshiftReaderM
import com.typesafe.config.Config
import org.apache.spark.sql.{DataFrame, SQLContext}
import org.apache.spark.{SparkConf, SparkContext}
import org.scalatest.{BeforeAndAfterAll, Suite}
import org.slf4j.{Logger, LoggerFactory}
trait SparkNRedshiftUtil extends BeforeAndAfterAll {
self: Suite =>
private val logger: Logger = LoggerFactory.getLogger(this.getClass)
@transient private var _sc: SparkContext = _
@transient private var _sqlContext: SQLContext = _
def sc: SparkContext = _sc
def sqlContext: SQLContext = _sqlContext
private def getRedshiftConnection(config: Config): Connection = {
val mysql = config.getConfig("redshift")
val connectionProps = new Properties()
connectionProps.put("user", mysql.getString("username"))
connectionProps.put("password", mysql.getString("password"))
val jdbcUrl = s"jdbc:redshift://${mysql.getString("hostname")}:${mysql.getInt("portno")}/${mysql.getString("database")}?useSSL=false"
Class.forName("com.amazon.redshift.jdbc4.Driver")
DriverManager.getConnection(jdbcUrl, connectionProps)
}
val getSparkContext: (SparkContext, SQLContext) = {
val sparkConf: SparkConf = new SparkConf().setAppName("Full Dump Testing").setMaster("local")
val sc: SparkContext = new SparkContext(sparkConf)
val sqlContext: SQLContext = new SQLContext(sc)
System.setProperty("com.amazonaws.services.s3.enableV4", "true")
sc.hadoopConfiguration.set("fs.s3a.endpoint", "s3.ap-south-1.amazonaws.com")
sc.hadoopConfiguration.set("fs.s3a.fast.upload", "true")
(sc, sqlContext)
}
def readTableFromRedshift(config: Config, tableName: String): DataFrame = {
val redshift: Config = config.getConfig("redshift")
val options = Map("dbtable" -> tableName,
"user" -> redshift.getString("username"),
"password" -> redshift.getString("password"),
"url" -> s"jdbc:redshift://${redshift.getString("hostname")}:${redshift.getInt("portno")}/${redshift.getString("database")}",
"tempdir" -> config.getString("s3.location"),
"aws_iam_role" -> config.getString("redshift.iamRole")
)
RedshiftReaderM.getDataFrameForConfig(options, sc, sqlContext)
}
def dropTableRedshift(config: Config, tables: String*): Unit = {
logger.info("Droping table: {}", tables)
val conn = getRedshiftConnection(config)
val statement = conn.createStatement()
try {
val dropTableQuery = s"""DROP TABLE ${tables.mkString(",")}"""
logger.info("Running query: {}", dropTableQuery)
statement.executeUpdate(dropTableQuery)
} finally {
statement.close()
conn.close()
}
}
override protected def beforeAll(): Unit = {
super.beforeAll()
val (sc, sqlContext) = getSparkContext
_sc = sc
_sqlContext = sqlContext
}
override protected def afterAll(): Unit = {
super.afterAll()
_sc.stop()
}
}
Example 37
| Source File: MySQLUtil.scala From SqlShift with MIT License | 5 votes |
|
package com.goibibo.sqlshift
import java.net.URL
import java.sql.{Connection, DriverManager}
import java.util.Properties
import com.typesafe.config.Config
import org.slf4j.{Logger, LoggerFactory}
import scala.io.Source
object MySQLUtil {
private val logger: Logger = LoggerFactory.getLogger(this.getClass)
private def getMySQLConnection(config: Config): Connection = {
val mysql = config.getConfig("mysql")
val connectionProps = new Properties()
connectionProps.put("user", mysql.getString("username"))
connectionProps.put("password", mysql.getString("password"))
val jdbcUrl = s"jdbc:mysql://${mysql.getString("hostname")}:${mysql.getInt("portno")}/${mysql.getString("db")}?createDatabaseIfNotExist=true&useSSL=false"
Class.forName("com.mysql.jdbc.Driver")
DriverManager.getConnection(jdbcUrl, connectionProps)
}
def createTableAndInsertRecords(config: Config, tableName: String, psvFile: URL): Unit = {
logger.info("Inserting records in table: {}", tableName)
val records = Source.fromFile(psvFile.toURI).getLines().toList.drop(1) // removing header
val conn = getMySQLConnection(config)
val statement = conn.createStatement()
try {
val tableCreateQuery = config.getString("table.tableCreateQuery").replace("${tableName}", tableName)
logger.info("Running query: {}", tableCreateQuery)
statement.executeUpdate(tableCreateQuery)
val insertIntoQuery = config.getString("table.insertIntoQuery").replace("${tableName}", tableName)
logger.info("Running query: {}", insertIntoQuery)
records.foreach { record: String =>
val columns = record.split("\\|")
val query = insertIntoQuery.format(columns: _*)
statement.executeUpdate(query)
}
} finally {
statement.close()
conn.close()
}
}
}
Example 38
| Source File: ConnectionUtils.scala From azure-sqldb-spark with MIT License | 5 votes |
|
package com.microsoft.azure.sqldb.spark.connect
import java.sql.{Connection, DriverManager, SQLException}
import java.util.Properties
import com.microsoft.azure.sqldb.spark.config.{Config, SqlDBConfig}
/**
* Helper and utility methods used for setting up or using a connection
*/
private[spark] object ConnectionUtils {
/**
* Retrieves all connection properties in the Config object
* and returns them as a [[Properties]] object.
*
* @param config the Config object with specified connection properties.
* @return A connection [[Properties]] object.
*/
def createConnectionProperties(config: Config): Properties = {
val connectionProperties = new Properties()
for (key <- config.getAllKeys) {
connectionProperties.put(key.toString, config.get[String](key.toString).get)
}
connectionProperties
}
/**
* Adds the "jdbc:sqlserver://" suffix to a general server url
*
* @param url the string url without the JDBC prefix
* @return the url with the added JDBC prefix
*/
def createJDBCUrl(url: String): String = SqlDBConfig.JDBCUrlPrefix + url
/**
* Gets a JDBC connection based on Config properties
*
* @param config any read or write Config
* @return a JDBC Connection
*/
def getConnection(config: Config): Connection = {
Class.forName(SqlDBConfig.SQLjdbcDriver)
DriverManager.getConnection(
createJDBCUrl(config.get[String](SqlDBConfig.URL).get), createConnectionProperties(config))
}
/**
* Retrieves the DBTable or QueryCustom specified in the config.
* NOTE: only one property can exist within config.
*
* @param config the Config object with specified properties.
* @return The specified DBTable or QueryCustom
*/
def getTableOrQuery(config: Config): String = {
config.get[String](SqlDBConfig.DBTable).getOrElse(
getQueryCustom(config.get[String](SqlDBConfig.QueryCustom).get)
)
}
/**
* The JDBC driver requires parentheses and a temp variable around any custom queries.
* This adds the required syntax so users only need to specify the query.
*
* @param query the default query
* @return the syntactically correct query to be executed by the JDBC driver.
*/
def getQueryCustom(query: String): String = s"($query) QueryCustom"
}
Example 39
| Source File: DBFunctions.scala From albedo with MIT License | 5 votes |
|
package ws.vinta.albedo.closures
import java.sql.DriverManager
import java.util.Properties
import scala.collection.mutable.ArrayBuffer
object DBFunctions {
def selectUserStarredRepos(userId: Int, limit: Int, offset: Int): Array[Int] = {
val dbUrl = "jdbc:mysql://127.0.0.1:3306/albedo?verifyServerCertificate=false&useSSL=false&rewriteBatchedStatements=true"
val props = new Properties()
props.setProperty("driver", "com.mysql.jdbc.Driver")
props.setProperty("user", "root")
props.setProperty("password", "123")
val connection = DriverManager.getConnection(dbUrl, props)
val statement = connection.createStatement()
val resultSet = statement.executeQuery(s"""
SELECT repo_id
FROM app_repostarring
WHERE user_id = $userId
ORDER BY starred_at DESC
LIMIT $limit
OFFSET $offset;
""".stripMargin(' '))
val repoIds = ArrayBuffer.empty[Int]
while (resultSet.next()) {
val repoId = resultSet.getInt("repo_id")
repoIds += repoId
}
connection.close()
repoIds.toArray
}
}
Example 40
| Source File: MySqlDemo.scala From Hands-On-Deep-Learning-with-Apache-Spark with MIT License | 5 votes |
|
package org.googlielmo.sparkdatabase
import java.sql.DriverManager
import java.util.Properties
import org.apache.spark.sql.SparkSession
object MySqlDemo {
@throws[Exception]
def main(args: Array[String]): Unit = {
var jdbcUsername = "myslus"
var jdbcPassword = "your_password"
Class.forName("com.mysql.jdbc.Driver")
val jdbcHostname = "your_db_hostname_or_ip"
val jdbcPort = 3306
val jdbcDatabase ="sparkdb"
// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:mysql://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}"
// Create a Properties() object to hold the parameters.
val connectionProperties = new Properties()
connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
connection.isClosed()
val spark = SparkSession
.builder()
.master("local[*]")
.appName("Spark MySQL basic example")
.getOrCreate()
import spark.implicits._
val jdbcDF = spark.read
.format("jdbc")
.option("url", jdbcUrl)
.option("dbtable", s"${jdbcDatabase}.sparkexample")
.option("user", jdbcUsername)
.option("password", jdbcPassword)
.load()
jdbcDF.printSchema()
println("Record count = " + jdbcDF.count())
val filteredJDBC = jdbcDF.select("MerchantCountryCode", "TransactionAmountUSD")
.groupBy("MerchantCountryCode")
.avg("TransactionAmountUSD")
filteredJDBC.collect.foreach { println }
spark.close()
}
}
Example 41
| Source File: SqlitePeersDbSpec.scala From eclair with Apache License 2.0 | 5 votes |
|
package fr.acinq.eclair.db
import java.sql.DriverManager
import fr.acinq.bitcoin.Crypto.PublicKey
import fr.acinq.eclair.db.sqlite.SqlitePeersDb
import fr.acinq.eclair.randomKey
import fr.acinq.eclair.wire.{NodeAddress, Tor2, Tor3}
import org.scalatest.funsuite.AnyFunSuite
class SqlitePeersDbSpec extends AnyFunSuite {
def inmem = DriverManager.getConnection("jdbc:sqlite::memory:")
test("init sqlite 2 times in a row") {
val sqlite = inmem
val db1 = new SqlitePeersDb(sqlite)
val db2 = new SqlitePeersDb(sqlite)
}
test("add/remove/get/list peers") {
val sqlite = inmem
val db = new SqlitePeersDb(sqlite)
case class TestCase(nodeId: PublicKey, nodeAddress: NodeAddress)
val peer_1 = TestCase(randomKey.publicKey, NodeAddress.fromParts("127.0.0.1", 42000).get)
val peer_1_bis = TestCase(peer_1.nodeId, NodeAddress.fromParts("127.0.0.1", 1112).get)
val peer_2 = TestCase(randomKey.publicKey, Tor2("z4zif3fy7fe7bpg3", 4231))
val peer_3 = TestCase(randomKey.publicKey, Tor3("mrl2d3ilhctt2vw4qzvmz3etzjvpnc6dczliq5chrxetthgbuczuggyd", 4231))
assert(db.listPeers().toSet === Set.empty)
db.addOrUpdatePeer(peer_1.nodeId, peer_1.nodeAddress)
assert(db.getPeer(peer_1.nodeId) === Some(peer_1.nodeAddress))
assert(db.getPeer(peer_2.nodeId) === None)
db.addOrUpdatePeer(peer_1.nodeId, peer_1.nodeAddress) // duplicate is ignored
assert(db.listPeers().size === 1)
db.addOrUpdatePeer(peer_2.nodeId, peer_2.nodeAddress)
db.addOrUpdatePeer(peer_3.nodeId, peer_3.nodeAddress)
assert(db.listPeers().map(p => TestCase(p._1, p._2)).toSet === Set(peer_1, peer_2, peer_3))
db.removePeer(peer_2.nodeId)
assert(db.listPeers().map(p => TestCase(p._1, p._2)).toSet === Set(peer_1, peer_3))
db.addOrUpdatePeer(peer_1_bis.nodeId, peer_1_bis.nodeAddress)
assert(db.getPeer(peer_1.nodeId) === Some(peer_1_bis.nodeAddress))
assert(db.listPeers().map(p => TestCase(p._1, p._2)).toSet === Set(peer_1_bis, peer_3))
}
}
Example 42
| Source File: JdbcSource.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.{Connection, DriverManager, PreparedStatement}
import com.sksamuel.exts.Logging
import com.sksamuel.exts.io.Using
import com.sksamuel.exts.metrics.Timed
import io.eels.{Row, Source}
import io.eels.component.jdbc.dialect.{GenericJdbcDialect, JdbcDialect}
import io.eels.datastream.Publisher
import io.eels.schema.StructType
object JdbcSource {
def apply(url: String, query: String): JdbcSource = JdbcSource(() => DriverManager.getConnection(url), query)
}
case class JdbcSource(connFn: () => Connection,
query: String,
bindFn: (PreparedStatement) => Unit = stmt => (),
fetchSize: Int = 200,
providedSchema: Option[StructType] = None,
providedDialect: Option[JdbcDialect] = None,
partitionStrategy: JdbcPartitionStrategy = SinglePartitionStrategy)
extends Source with JdbcPrimitives with Logging with Using with Timed {
override lazy val schema: StructType = providedSchema.getOrElse(fetchSchema())
def withBind(bind: (PreparedStatement) => Unit): JdbcSource = copy(bindFn = bind)
def withFetchSize(fetchSize: Int): JdbcSource = copy(fetchSize = fetchSize)
def withProvidedSchema(schema: StructType): JdbcSource = copy(providedSchema = Option(schema))
def withProvidedDialect(dialect: JdbcDialect): JdbcSource = copy(providedDialect = Option(dialect))
def withPartitionStrategy(strategy: JdbcPartitionStrategy): JdbcSource = copy(partitionStrategy = strategy)
private def dialect(): JdbcDialect = providedDialect.getOrElse(new GenericJdbcDialect())
override def parts(): Seq[Publisher[Seq[Row]]] = partitionStrategy.parts(connFn, query, bindFn, fetchSize, dialect())
def fetchSchema(): StructType = {
using(connFn()) { conn =>
val schemaQuery = s"SELECT * FROM ($query) tmp WHERE 1=0"
using(conn.prepareStatement(schemaQuery)) { stmt =>
stmt.setFetchSize(fetchSize)
bindFn(stmt)
val rs = timed(s"Executing query $query") {
stmt.executeQuery()
}
val schema = schemaFor(dialect(), rs)
rs.close()
schema
}
}
}
}
Example 43
| Source File: RowDataSourceStrategySuite.scala From XSQL with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources
import java.sql.DriverManager
import java.util.Properties
import org.scalatest.BeforeAndAfter
import org.apache.spark.SparkFunSuite
import org.apache.spark.sql.{DataFrame, Row}
import org.apache.spark.sql.sources._
import org.apache.spark.sql.test.SharedSQLContext
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
class RowDataSourceStrategySuite extends SparkFunSuite with BeforeAndAfter with SharedSQLContext {
import testImplicits._
val url = "jdbc:h2:mem:testdb0"
val urlWithUserAndPass = "jdbc:h2:mem:testdb0;user=testUser;password=testPass"
var conn: java.sql.Connection = null
before {
Utils.classForName("org.h2.Driver")
// Extra properties that will be specified for our database. We need these to test
// usage of parameters from OPTIONS clause in queries.
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("create schema test").executeUpdate()
conn.prepareStatement("create table test.inttypes (a INT, b INT, c INT)").executeUpdate()
conn.prepareStatement("insert into test.inttypes values (1, 2, 3)").executeUpdate()
conn.commit()
sql(
s"""
|CREATE OR REPLACE TEMPORARY VIEW inttypes
|USING org.apache.spark.sql.jdbc
|OPTIONS (url '$url', dbtable 'TEST.INTTYPES', user 'testUser', password 'testPass')
""".stripMargin.replaceAll("\n", " "))
}
after {
conn.close()
}
test("SPARK-17673: Exchange reuse respects differences in output schema") {
val df = sql("SELECT * FROM inttypes")
val df1 = df.groupBy("a").agg("b" -> "min")
val df2 = df.groupBy("a").agg("c" -> "min")
val res = df1.union(df2)
assert(res.distinct().count() == 2) // would be 1 if the exchange was incorrectly reused
}
}
Example 44
| Source File: DriverRegistry.scala From XSQL with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.internal.Logging
import org.apache.spark.util.Utils
DriverManager.getDrivers
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 45
| Source File: JdbcConnectionUriSuite.scala From XSQL with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.thriftserver
import java.sql.DriverManager
import org.apache.hive.jdbc.HiveDriver
import org.apache.spark.util.Utils
class JdbcConnectionUriSuite extends HiveThriftServer2Test {
Utils.classForName(classOf[HiveDriver].getCanonicalName)
override def mode: ServerMode.Value = ServerMode.binary
val JDBC_TEST_DATABASE = "jdbc_test_database"
val USER = System.getProperty("user.name")
val PASSWORD = ""
override protected def beforeAll(): Unit = {
super.beforeAll()
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"CREATE DATABASE $JDBC_TEST_DATABASE")
connection.close()
}
override protected def afterAll(): Unit = {
try {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"DROP DATABASE $JDBC_TEST_DATABASE")
connection.close()
} finally {
super.afterAll()
}
}
test("SPARK-17819 Support default database in connection URIs") {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/$JDBC_TEST_DATABASE"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
try {
val resultSet = statement.executeQuery("select current_database()")
resultSet.next()
assert(resultSet.getString(1) === JDBC_TEST_DATABASE)
} finally {
statement.close()
connection.close()
}
}
}
Example 46
| Source File: PostgresConnection.scala From darwin with Apache License 2.0 | 5 votes |
|
package it.agilelab.darwin.connector.postgres
import java.sql.{Connection, DriverManager}
import com.typesafe.config.Config
trait PostgresConnection {
private var connectionUrl : String = ""
private val driverName : String = "org.postgresql.Driver"
protected def setConnectionConfig(config : Config) = {
val db = config.getString(ConfigurationKeys.DATABASE)
val host = config.getString(ConfigurationKeys.HOST)
val user = config.getString(ConfigurationKeys.USER)
val password = config.getString(ConfigurationKeys.PASSWORD)
connectionUrl = s"jdbc:postgresql://$host/$db?user=$user&password=$password"
}
protected def getConnection: Connection = {
Class.forName(driverName)
val connection: Connection = DriverManager.getConnection(connectionUrl)
connection
}
}
Example 47
| Source File: JdbcExampleSuite.scala From gihyo-spark-book-example with Apache License 2.0 | 5 votes |
|
package jp.gihyo.spark.ch05
import java.sql.DriverManager
import java.util.Properties
import jp.gihyo.spark.{SparkFunSuite, TestSparkContext}
import org.scalatest.BeforeAndAfter
class JdbcExampleSuite extends SparkFunSuite with TestSparkContext with BeforeAndAfter {
val user = "testUser"
val pass = "testPass"
val url = "jdbc:h2:mem:testdb;MODE=MySQL"
val urlWithUserAndPass = s"jdbc:h2:mem:testdb;user=${user}};password=${pass}"
var conn: java.sql.Connection = null
override def beforeAll(): Unit = {
super.beforeAll()
Class.forName("org.h2.Driver")
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("CREATE SCHEMA gihyo_spark").executeUpdate()
conn.prepareStatement(
"""
|CREATE TABLE gihyo_spark.person (
| id INTEGER NOT NULL,
| name TEXT(32) NOT NULL,
| age INTEGER NOT NULL
|)
""".stripMargin.replaceAll("\n", " ")
).executeUpdate()
conn.prepareStatement("INSERT INTO gihyo_spark.person VALUES (1, 'fred', 23)").executeUpdate()
conn.prepareStatement("INSERT INTO gihyo_spark.person VALUES (2, 'mary', 22)").executeUpdate()
conn.prepareStatement("INSERT INTO gihyo_spark.person VALUES (3, 'bob', 23)").executeUpdate()
conn.prepareStatement("INSERT INTO gihyo_spark.person VALUES (4, 'ann', 22)").executeUpdate()
conn.commit()
}
override def afterAll(): Unit = {
super.afterAll()
conn.close()
}
test("run") {
JdbcExample.run(sc, sqlContext, url, user, pass)
}
}
Example 48
| Source File: TiDBUtils.scala From tispark with Apache License 2.0 | 5 votes |
|
package com.pingcap.tispark
import java.sql.{Connection, Driver, DriverManager}
import java.util.Properties
import com.pingcap.tispark.write.TiDBOptions
import org.apache.spark.sql.execution.datasources.jdbc.{DriverRegistry, DriverWrapper}
import scala.util.Try
object TiDBUtils {
private val TIDB_DRIVER_CLASS = "com.mysql.jdbc.Driver"
def createConnectionFactory(jdbcURL: String): () => Connection = {
import scala.collection.JavaConverters._
val driverClass: String = TIDB_DRIVER_CLASS
() => {
DriverRegistry.register(driverClass)
val driver: Driver = DriverManager.getDrivers.asScala
.collectFirst {
case d: DriverWrapper if d.wrapped.getClass.getCanonicalName == driverClass => d
case d if d.getClass.getCanonicalName == driverClass => d
}
.getOrElse {
throw new IllegalStateException(
s"Did not find registered driver with class $driverClass")
}
driver.connect(jdbcURL, new Properties())
}
}
}
Example 49
| Source File: RangePartitionStrategyTest.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.DriverManager
import org.scalatest.{Matchers, WordSpec}
import scala.util.Random
class RangePartitionStrategyTest extends WordSpec with Matchers {
Class.forName("org.h2.Driver")
private val conn = DriverManager.getConnection("jdbc:h2:mem:rangetest")
conn.createStatement().executeUpdate("create table bucket_test (a integer)")
for (k <- 0 until 20) {
conn.createStatement().executeUpdate(s"insert into bucket_test (a) values (${Random.nextInt(10000)})")
}
"BucketPartitionStrategy" should {
"generate evenly spaced ranges" in {
RangePartitionStrategy("a", 10, 2, 29).ranges shouldBe List((2, 4), (5, 7), (8, 10), (11, 13), (14, 16), (17, 19), (20, 22), (23, 25), (26, 27), (28, 29))
RangePartitionStrategy("a", 2, 2, 30).ranges shouldBe List((2, 16), (17, 30))
RangePartitionStrategy("a", 1, 4, 5).ranges shouldBe List((4, 5))
RangePartitionStrategy("a", 1, 4, 4).ranges shouldBe List((4, 4))
RangePartitionStrategy("a", 6, 1, 29).ranges shouldBe List((1, 5), (6, 10), (11, 15), (16, 20), (21, 25), (26, 29))
}
"return correct number of ranges" in {
JdbcSource(() => DriverManager.getConnection("jdbc:h2:mem:rangetest"), "select * from bucket_test")
.withPartitionStrategy(RangePartitionStrategy("a", 4, 0, 10000))
.parts().size shouldBe 4
}
"return full and non overlapping data" in {
JdbcSource(() => DriverManager.getConnection("jdbc:h2:mem:rangetest"), "select * from bucket_test")
.withPartitionStrategy(RangePartitionStrategy("a", 4, 0, 10000))
.toDataStream().collect.size shouldBe 20
}
}
}
Example 50
| Source File: JdbcSourceTest.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.DriverManager
import io.eels.schema._
import org.scalatest.{Matchers, WordSpec}
class JdbcSourceTest extends WordSpec with Matchers {
Class.forName("org.h2.Driver")
"JdbcSource" should {
"read schema" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:test")
conn.createStatement().executeUpdate("create table mytable (a integer, b bit, c bigint)")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('1','2','3')")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('4','5','6')")
JdbcSource("jdbc:h2:mem:test", "select * from mytable").schema shouldBe
StructType(
Field("A", IntType(true), true),
Field("B", BooleanType, true),
Field("C", LongType.Signed, true)
)
}
"use supplied query" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:test3")
conn.createStatement().executeUpdate("create table mytable (a integer, b bit, c bigint)")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('1','2','3')")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('4','5','6')")
JdbcSource(() => DriverManager.getConnection("jdbc:h2:mem:test3"), "select * from mytable where a=4").toDataStream().size shouldBe 1
val a = JdbcSource("jdbc:h2:mem:test3", "select a,c from mytable where a=4").toDataStream().toVector
a.head.values.head shouldBe 4
a.head.values(1) shouldBe 6L
}
"read decimal precision and scale" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:decimal")
conn.createStatement().executeUpdate("create table mytable (a decimal(15,5))")
conn.createStatement().executeUpdate("insert into mytable (a) values (1.234)")
val schema = JdbcSource(() => DriverManager.getConnection("jdbc:h2:mem:decimal"), "select * from mytable").schema
schema shouldBe
StructType(Vector(Field("A",DecimalType(Precision(15),Scale(5)))))
}
"read numeric precision and scale" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:numeric")
conn.createStatement().executeUpdate("create table mytable (a numeric(3,2))")
conn.createStatement().executeUpdate("insert into mytable (a) values (1.234)")
val schema = JdbcSource(() => DriverManager.getConnection("jdbc:h2:mem:numeric"), "select * from mytable").schema
schema shouldBe
StructType(Vector(Field("A",DecimalType(Precision(3),Scale(2)))))
}
"read from jdbc" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:test4")
conn.createStatement().executeUpdate("create table mytable (a integer, b bit, c bigint)")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('1','2','3')")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('4','5','6')")
JdbcSource("jdbc:h2:mem:test4", "select * from mytable").toDataStream().size shouldBe 2
}
"support bind" in {
val conn = DriverManager.getConnection("jdbc:h2:mem:test5")
conn.createStatement().executeUpdate("create table mytable (a integer, b bit, c bigint)")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('1','2','3')")
conn.createStatement().executeUpdate("insert into mytable (a,b,c) values ('4','5','6')")
JdbcSource("jdbc:h2:mem:test5", "select * from mytable where a=?").withBind { it =>
it.setLong(1, 4)
}.toDataStream().size shouldBe 1
}
}
}
Example 51
| Source File: HashPartitionStrategyTest.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.DriverManager
import org.scalatest.{Matchers, WordSpec}
class HashPartitionStrategyTest extends WordSpec with Matchers {
Class.forName("org.h2.Driver")
private val db = "hash_test"
private val uri = s"jdbc:h2:mem:$db"
private val conn = DriverManager.getConnection(uri)
conn.createStatement().executeUpdate("create table hash_test (a integer)")
for (k <- 0 until 20) {
conn.createStatement().executeUpdate(s"insert into hash_test (a) values ($k)")
}
"HashPartitionStrategy" should {
"return correct number of ranges" in {
JdbcSource(() => DriverManager.getConnection(uri), "select * from hash_test")
.withPartitionStrategy(HashPartitionStrategy("mod(a)", 10))
.parts().size shouldBe 10
}
"return full and non overlapping data" in {
JdbcSource(() => DriverManager.getConnection(uri), "select * from hash_test")
.withPartitionStrategy(HashPartitionStrategy("mod(a, 10)", 10))
.toDataStream().collect.flatMap(_.values).toSet shouldBe
Vector.tabulate(20) { k => k }.toSet
}
}
}
Example 52
| Source File: JdbcAlarm.scala From XSQL with Apache License 2.0 | 5 votes |
|
package org.apache.spark.alarm
import java.sql.{Connection, DriverManager}
class JdbcAlarm extends Alarm {
override val name: String = "mysql"
private val conn: Connection = getConnect
private def getConnect(): Connection = {
org.apache.spark.util.Utils.classForName("com.mysql.jdbc.Driver")
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/xsql_monitor?useSSL=true",
"xsql_monitor",
"xsql_monitor")
}
override def alarm(msg: AlertMessage): AlertResp = {
msg.toJdbc(conn)
AlertResp.success("")
}
override def finalAlarm(msg: AlertMessage): AlertResp = {
msg.toJdbc(conn)
AlertResp.success("")
}
}
Example 53
| Source File: JdbcSink.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.{Connection, DriverManager}
import com.sksamuel.exts.Logging
import com.typesafe.config.ConfigFactory
import io.eels.Sink
import io.eels.component.jdbc.dialect.{GenericJdbcDialect, JdbcDialect}
import io.eels.schema.StructType
import com.sksamuel.exts.OptionImplicits._
object JdbcSink extends Logging {
private val config = ConfigFactory.load()
private val warnIfMissingRewriteBatchedStatements = config.getBoolean("eel.jdbc.sink.warnIfMissingRewriteBatchedStatements")
def apply(url: String, table: String): JdbcSink = {
if (!url.contains("rewriteBatchedStatements")) {
if (warnIfMissingRewriteBatchedStatements) {
logger.warn("JDBC connection string does not contain the property 'rewriteBatchedStatements=true' which can be a major performance boost when writing data via JDBC. " +
"Add this property to your connection string, or to remove this warning set eel.jdbc.warnIfMissingRewriteBatchedStatements=false")
}
}
JdbcSink(() => DriverManager.getConnection(url), table)
}
}
case class JdbcSink(connFn: () => Connection,
table: String,
createTable: Boolean = false,
dropTable: Boolean = false,
batchSize: Int = 1000, // the number of rows before a commit is made
batchesPerCommit: Int = 0, // 0 means commit at the end, otherwise how many batches before a commit
dialect: Option[JdbcDialect] = None,
threads: Int = 4) extends Sink with Logging {
private val config = ConfigFactory.load()
private val bufferSize = config.getInt("eel.jdbc.sink.bufferSize")
private val autoCommit = config.getBoolean("eel.jdbc.sink.autoCommit")
def withCreateTable(createTable: Boolean): JdbcSink = copy(createTable = createTable)
def withDropTable(dropTable: Boolean): JdbcSink = copy(dropTable = dropTable)
def withBatchSize(batchSize: Int): JdbcSink = copy(batchSize = batchSize)
def withThreads(threads: Int): JdbcSink = copy(threads = threads)
def withBatchesPerCommit(commitSize: Int): JdbcSink = copy(batchesPerCommit = batchesPerCommit)
def withDialect(dialect: JdbcDialect): JdbcSink = copy(dialect = dialect.some)
override def open(schema: StructType) =
new JdbcSinkWriter(schema, connFn, table, createTable, dropTable, dialect.getOrElse(new GenericJdbcDialect), threads, batchSize, batchesPerCommit, autoCommit, bufferSize)
}
Example 54
| Source File: JdbcPrimitives.scala From eel-sdk with Apache License 2.0 | 5 votes |
|
package io.eels.component.jdbc
import java.sql.{Connection, DriverManager, ResultSet}
import com.sksamuel.exts.Logging
import io.eels.component.jdbc.dialect.JdbcDialect
import io.eels.schema.StructType
trait JdbcPrimitives extends Logging {
def connect(url: String): Connection = {
logger.debug(s"Connecting to jdbc source $url...")
val conn = DriverManager.getConnection(url)
logger.debug(s"Connected to $url")
conn
}
def schemaFor(dialect: JdbcDialect, rs: ResultSet): StructType = {
val schema = JdbcSchemaFns.fromJdbcResultset(rs, dialect)
logger.trace("Fetched schema:\n" + schema.show())
schema
}
}
Example 55
| Source File: SqlAlertTriggerTest.scala From pulse with Apache License 2.0 | 5 votes |
|
package io.phdata.pulse.alertengine.trigger
import java.sql.{ DriverManager, Statement }
import io.phdata.pulse.alertengine.{ AlertsDb, TestObjectGenerator }
import io.phdata.pulse.solr.TestUtil
import org.scalatest.{ BeforeAndAfterAll, BeforeAndAfterEach, FunSuite }
class SqlAlertTriggerTest extends FunSuite with BeforeAndAfterEach with BeforeAndAfterAll {
private val applicationName: String = "sql_test_" + TestUtil.randomIdentifier()
private val dbUrl = s"jdbc:h2:mem:$applicationName;DB_CLOSE_DELAY=-1"
override def beforeEach(): Unit = {
super.beforeEach()
AlertsDb.reset()
prepareDatabase()
}
override def afterAll(): Unit =
withStatement(statement => statement.execute("DROP ALL OBJECTS DELETE FILES;"))
private def withStatement(function: Statement => Unit): Unit = {
val connection = DriverManager.getConnection(dbUrl)
try {
val statement = connection.createStatement()
try {
function.apply(statement)
} finally {
statement.close()
}
} finally {
connection.close()
}
}
private def prepareDatabase(): Unit =
withStatement { statement =>
statement.execute("DROP ALL OBJECTS DELETE FILES;")
statement.execute(s"""CREATE TABLE $applicationName (
|id int not null,
|error boolean not null,
|message varchar(255) not null,
|);""".stripMargin)
}
test("query returns matching documents") {
withStatement { statement =>
statement.execute(s"""INSERT INTO $applicationName (id, error, message) VALUES
|(1, true, 'sad'),
|(3, true, 'very sad'),
|(2, false, 'happy');""".stripMargin)
}
val alertRule =
TestObjectGenerator.alertRule(
query = s"""select * from $applicationName
|where error = true
|order by id""".stripMargin,
retryInterval = 1,
resultThreshold = Some(1),
alertProfiles = List("[email protected]")
)
val expectedDocuments = Seq(
Map("id" -> 1, "error" -> true, "message" -> "sad"),
Map("id" -> 3, "error" -> true, "message" -> "very sad")
)
val trigger = new SqlAlertTrigger(dbUrl)
val result = trigger.query(applicationName, alertRule)
assertResult(expectedDocuments)(result)
}
test("query returns no documents") {
val alertRule = TestObjectGenerator.alertRule(query = s"select * from $applicationName")
val trigger = new SqlAlertTrigger(dbUrl)
assertResult(Seq.empty)(trigger.query(applicationName, alertRule))
}
test("invalid query") {
val alertRule = TestObjectGenerator.alertRule()
val trigger = new SqlAlertTrigger(dbUrl)
assertThrows[Exception](trigger.query(applicationName, alertRule))
}
test("connection with options") {
val alertRule = TestObjectGenerator.alertRule(query = s"select * from $applicationName")
val trigger = new SqlAlertTrigger(dbUrl, dbOptions = Map("hello" -> "stuff"))
trigger.query(applicationName, alertRule)
}
test("dbUrl null") {
assertThrows[IllegalArgumentException](new SqlAlertTrigger(null))
}
test("dbUrl empty") {
assertThrows[IllegalArgumentException](new SqlAlertTrigger(""))
}
}
Example 56
| Source File: PostgresInteropTest.scala From spark-alchemy with Apache License 2.0 | 5 votes |
|
package com.swoop.alchemy.spark.expressions.hll
import java.sql.{DriverManager, ResultSet, Statement}
import com.swoop.alchemy.spark.expressions.hll.functions._
import com.swoop.test_utils.SparkSessionSpec
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.scalatest.{Matchers, WordSpec}
case class Postgres(user: String, database: String, port: Int) {
val con_str = s"jdbc:postgresql://localhost:$port/$database?user=$user"
def execute[T](query: String, handler: ResultSet => T): T =
execute(stm => handler(stm.executeQuery(query)))
def update(query: String): Unit =
execute(_.executeUpdate(query))
def sparkRead(schema: String, table: String)(implicit spark: SparkSession): DataFrame =
spark.read
.format("jdbc")
.option("url", s"jdbc:postgresql:${database}")
.option("dbtable", s"${schema}.${table}")
.option("user", user)
.load()
def sparkWrite(schema: String, table: String)(df: DataFrame): Unit =
df.write
.format("jdbc")
.option("url", s"jdbc:postgresql:${database}")
.option("dbtable", s"${schema}.${table}")
.option("user", user)
.save()
private def execute[T](fn: Statement => T): T = {
val conn = DriverManager.getConnection(con_str)
try {
val stm = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
fn(stm)
} finally {
conn.close()
}
}
}
class PostgresInteropTest extends WordSpec with Matchers with SparkSessionSpec {
import testImplicits._
lazy val pg = Postgres("postgres", "postgres", 5432)
"Postgres interop" should {
"calculate same results" in {
// use Aggregate Knowledge (Postgres-compatible) HLL implementation
spark.conf.set(IMPLEMENTATION_CONFIG_KEY, "AGKN")
// init Postgres extension for database
pg.update("CREATE EXTENSION IF NOT EXISTS hll;")
// create some random not-entirely distinct rows
val rand = new scala.util.Random(42)
val n = 100000
val randomDF = sc.parallelize(
Seq.fill(n) {
(rand.nextInt(24), rand.nextInt(n))
}
).toDF("hour", "id").cache
// create hll aggregates (by hour)
val byHourDF = randomDF.groupBy("hour").agg(hll_init_agg("id", .39).as("hll_id")).cache
// send hlls to postgres
pg.update("DROP TABLE IF EXISTS spark_hlls CASCADE;")
pg.sparkWrite("public", "spark_hlls")(byHourDF)
// convert hll column from `bytea` to `hll` type
pg.update(
"""
|ALTER TABLE spark_hlls
|ALTER COLUMN hll_id TYPE hll USING CAST (hll_id AS hll);
|""".stripMargin
)
// re-aggregate all hours in Spark
val distinctSpark = byHourDF.select(hll_cardinality(hll_merge(byHourDF("hll_id")))).as[Long].first()
// re-aggregate all hours in Postgres
val distinctPostgres = pg.execute(
"SELECT CAST (hll_cardinality(hll_union_agg(hll_id)) as Integer) AS approx FROM spark_hlls",
(rs) => {
rs.next;
rs.getInt("approx")
}
)
distinctSpark should be(distinctPostgres)
}
}
}
Example 57
| Source File: DockerPostgresService.scala From crm-seed with Apache License 2.0 | 5 votes |
|
package com.dataengi.crm.common.docker
import java.sql.DriverManager
import com.spotify.docker.client.{DefaultDockerClient, DockerClient}
import com.whisk.docker.impl.spotify.SpotifyDockerFactory
import com.whisk.docker.{
DockerCommandExecutor,
DockerContainer,
DockerContainerState,
DockerFactory,
DockerKit,
DockerReadyChecker
}
import org.slf4j.LoggerFactory
import scala.concurrent.{ExecutionContext, Future}
import scala.util.Try
trait DockerPostgresService extends DockerKit {
import scala.concurrent.duration._
private lazy val log = LoggerFactory.getLogger(this.getClass)
private val client: DockerClient = DefaultDockerClient.fromEnv().build()
override implicit val dockerFactory: DockerFactory = new SpotifyDockerFactory(client)
def PostgresAdvertisedPort = 5432
def PostgresExposedPort = 44444
val PostgresUser = "nph"
val PostgresPassword = "suitup"
lazy val DockerPostgresHost: String = postgresContainer.hostname.getOrElse("localhost")
lazy val DockerPostgresPort: Int = PostgresExposedPort
lazy val DockerDatabaseName: String = "crm"
val postgresContainer: DockerContainer = DockerContainer("postgres:9.5")
.withPorts((PostgresAdvertisedPort, Some(PostgresExposedPort)))
.withEnv(s"POSTGRES_USER=$PostgresUser", s"POSTGRES_PASSWORD=$PostgresPassword")
.withCommand()
.withReadyChecker(
PostgresReadyChecker(DockerDatabaseName, PostgresUser, PostgresPassword, Some(PostgresExposedPort))
.looped(15, 1.second)
)
lazy val dockerTestDataBaseConf: Map[String, Any] = Map[String, Any](
"slick.dbs.default.driver" -> "slick.driver.PostgresDriver$",
"slick.dbs.default.db.driver" -> "org.postgresql.Driver",
"slick.dbs.default.db.user" -> PostgresUser,
"slick.dbs.default.db.password" -> PostgresPassword,
"slick.dbs.default.db.url" -> s"jdbc:postgresql://$DockerPostgresHost:$DockerPostgresPort/crm",
"slick.dbs.default.db.properties.url" -> s"jdbc:postgresql://$DockerPostgresHost:$DockerPostgresPort/crm"
)
override def dockerContainers: List[DockerContainer] = postgresContainer :: super.dockerContainers
}
case class PostgresReadyChecker(databaseName: String, user: String, password: String, port: Option[Int] = None)
extends DockerReadyChecker {
override def apply(container: DockerContainerState)(implicit docker: DockerCommandExecutor,
ec: ExecutionContext): Future[Boolean] =
container
.getPorts()
.map(ports =>
Try {
Class.forName("org.postgresql.Driver")
val url = s"jdbc:postgresql://${docker.host}:${port.getOrElse(ports.values.head)}/"
println(s"[postgres][docker][url] $url")
Option(DriverManager.getConnection(url, user, password))
.map { connection =>
println(s"[posgres][docker][create-db][connection] isClosed=${connection.isClosed}")
val statements = connection.createStatement()
val result = statements.executeUpdate(s"CREATE DATABASE $databaseName")
println(s"[posgres][docker][create-db] result=$result")
connection
}
.map(_.close)
.isDefined
}.getOrElse(false))
}
Example 58
| Source File: RowDataSourceStrategySuite.scala From drizzle-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources
import java.sql.DriverManager
import java.util.Properties
import org.scalatest.BeforeAndAfter
import org.apache.spark.SparkFunSuite
import org.apache.spark.sql.{DataFrame, Row}
import org.apache.spark.sql.sources._
import org.apache.spark.sql.test.SharedSQLContext
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
class RowDataSourceStrategySuite extends SparkFunSuite with BeforeAndAfter with SharedSQLContext {
import testImplicits._
val url = "jdbc:h2:mem:testdb0"
val urlWithUserAndPass = "jdbc:h2:mem:testdb0;user=testUser;password=testPass"
var conn: java.sql.Connection = null
before {
Utils.classForName("org.h2.Driver")
// Extra properties that will be specified for our database. We need these to test
// usage of parameters from OPTIONS clause in queries.
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("create schema test").executeUpdate()
conn.prepareStatement("create table test.inttypes (a INT, b INT, c INT)").executeUpdate()
conn.prepareStatement("insert into test.inttypes values (1, 2, 3)").executeUpdate()
conn.commit()
sql(
s"""
|CREATE TEMPORARY TABLE inttypes
|USING org.apache.spark.sql.jdbc
|OPTIONS (url '$url', dbtable 'TEST.INTTYPES', user 'testUser', password 'testPass')
""".stripMargin.replaceAll("\n", " "))
}
after {
conn.close()
}
test("SPARK-17673: Exchange reuse respects differences in output schema") {
val df = sql("SELECT * FROM inttypes")
val df1 = df.groupBy("a").agg("b" -> "min")
val df2 = df.groupBy("a").agg("c" -> "min")
val res = df1.union(df2)
assert(res.distinct().count() == 2) // would be 1 if the exchange was incorrectly reused
}
}
Example 59
| Source File: DriverRegistry.scala From drizzle-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.internal.Logging
import org.apache.spark.util.Utils
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 60
| Source File: JdbcConnectionUriSuite.scala From drizzle-spark with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.thriftserver
import java.sql.DriverManager
import org.apache.hive.jdbc.HiveDriver
import org.apache.spark.util.Utils
class JdbcConnectionUriSuite extends HiveThriftServer2Test {
Utils.classForName(classOf[HiveDriver].getCanonicalName)
override def mode: ServerMode.Value = ServerMode.binary
val JDBC_TEST_DATABASE = "jdbc_test_database"
val USER = System.getProperty("user.name")
val PASSWORD = ""
override protected def beforeAll(): Unit = {
super.beforeAll()
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"CREATE DATABASE $JDBC_TEST_DATABASE")
connection.close()
}
override protected def afterAll(): Unit = {
try {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"DROP DATABASE $JDBC_TEST_DATABASE")
connection.close()
} finally {
super.afterAll()
}
}
test("SPARK-17819 Support default database in connection URIs") {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/$JDBC_TEST_DATABASE"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
try {
val resultSet = statement.executeQuery("select current_database()")
resultSet.next()
assert(resultSet.getString(1) === JDBC_TEST_DATABASE)
} finally {
statement.close()
connection.close()
}
}
}
Example 61
| Source File: derbyTools.scala From examples-scala with Apache License 2.0 | 5 votes |
|
package io.github.streamingwithflink.chapter8.util
import java.sql.DriverManager
import java.util.Properties
import scala.util.Random
class DerbyWriter(stmt: String, paramGenerator: Random => Array[Any], interval: Long) extends Runnable {
// connect to embedded in-memory Derby and prepare query
private val conn = DriverManager.getConnection("jdbc:derby:memory:flinkExample", new Properties())
private val prepStmt = conn.prepareStatement(stmt)
private val rand = new Random(1234)
override def run(): Unit = {
while(true) {
Thread.sleep(interval)
// get and set parameters
val params = paramGenerator(rand)
for (i <- 1 to params.length) {
prepStmt.setObject(i, params(i - 1))
}
// update the Derby table
prepStmt.executeUpdate()
}
}
}
Example 62
| Source File: JdbcConnectionUriSuite.scala From sparkoscope with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.thriftserver
import java.sql.DriverManager
import org.apache.hive.jdbc.HiveDriver
import org.apache.spark.util.Utils
class JdbcConnectionUriSuite extends HiveThriftServer2Test {
Utils.classForName(classOf[HiveDriver].getCanonicalName)
override def mode: ServerMode.Value = ServerMode.binary
val JDBC_TEST_DATABASE = "jdbc_test_database"
val USER = System.getProperty("user.name")
val PASSWORD = ""
override protected def beforeAll(): Unit = {
super.beforeAll()
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"CREATE DATABASE $JDBC_TEST_DATABASE")
connection.close()
}
override protected def afterAll(): Unit = {
try {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
statement.execute(s"DROP DATABASE $JDBC_TEST_DATABASE")
connection.close()
} finally {
super.afterAll()
}
}
test("SPARK-17819 Support default database in connection URIs") {
val jdbcUri = s"jdbc:hive2://localhost:$serverPort/$JDBC_TEST_DATABASE"
val connection = DriverManager.getConnection(jdbcUri, USER, PASSWORD)
val statement = connection.createStatement()
try {
val resultSet = statement.executeQuery("select current_database()")
resultSet.next()
assert(resultSet.getString(1) === JDBC_TEST_DATABASE)
} finally {
statement.close()
connection.close()
}
}
}
Example 63
| Source File: Databases.scala From eclair with Apache License 2.0 | 5 votes |
|
package fr.acinq.eclair.db
import java.io.File
import java.sql.{Connection, DriverManager}
import fr.acinq.eclair.db.sqlite._
import grizzled.slf4j.Logging
import org.sqlite.SQLiteException
trait Databases {
val network: NetworkDb
val audit: AuditDb
val channels: ChannelsDb
val peers: PeersDb
val payments: PaymentsDb
val pendingRelay: PendingRelayDb
def backup(file: File): Unit
}
object Databases extends Logging {
def sqliteJDBC(dbdir: File): Databases = {
dbdir.mkdir()
var sqliteEclair: Connection = null
var sqliteNetwork: Connection = null
var sqliteAudit: Connection = null
try {
sqliteEclair = DriverManager.getConnection(s"jdbc:sqlite:${new File(dbdir, "eclair.sqlite")}")
sqliteNetwork = DriverManager.getConnection(s"jdbc:sqlite:${new File(dbdir, "network.sqlite")}")
sqliteAudit = DriverManager.getConnection(s"jdbc:sqlite:${new File(dbdir, "audit.sqlite")}")
SqliteUtils.obtainExclusiveLock(sqliteEclair) // there should only be one process writing to this file
logger.info("successful lock on eclair.sqlite")
databaseByConnections(sqliteAudit, sqliteNetwork, sqliteEclair)
} catch {
case t: Throwable => {
logger.error("could not create connection to sqlite databases: ", t)
if (sqliteEclair != null) sqliteEclair.close()
if (sqliteNetwork != null) sqliteNetwork.close()
if (sqliteAudit != null) sqliteAudit.close()
throw t
}
}
}
def databaseByConnections(auditJdbc: Connection, networkJdbc: Connection, eclairJdbc: Connection) = new Databases {
override val network = new SqliteNetworkDb(networkJdbc)
override val audit = new SqliteAuditDb(auditJdbc)
override val channels = new SqliteChannelsDb(eclairJdbc)
override val peers = new SqlitePeersDb(eclairJdbc)
override val payments = new SqlitePaymentsDb(eclairJdbc)
override val pendingRelay = new SqlitePendingRelayDb(eclairJdbc)
override def backup(file: File): Unit = {
SqliteUtils.using(eclairJdbc.createStatement()) {
statement => {
statement.executeUpdate(s"backup to ${file.getAbsolutePath}")
}
}
}
}
}
Example 64
| Source File: DockerTmpDB.scala From akka-stream-extensions with Apache License 2.0 | 5 votes |
|
package com.mfglabs.stream
package extensions.postgres
import java.sql.{DriverManager, Connection}
import org.postgresql.util.PSQLException
import org.scalatest.{Suite, BeforeAndAfter}
import scala.sys.process._
import scala.util.{Failure, Success, Try}
import com.typesafe.config.ConfigFactory
trait DockerTmpDB extends BeforeAndAfter { self: Suite =>
import Debug._
val version: PostgresVersion = PostgresVersion(ConfigFactory.load().getString("postgres.version"))
Class.forName("org.postgresql.Driver")
implicit var conn : Connection = _
val dockerInstances = collection.mutable.Buffer.empty[String]
def newPGDB(): Int = {
val port: Int = 5432 + (math.random * (10000 - 5432)).toInt
Try {
s"docker pull postgres:${version.value}".pp.!!.trim
val containerId =
s"""docker run -p $port:5432 -e POSTGRES_PASSWORD=pwd -d postgres:${version.value}""".pp.!!.trim
dockerInstances += containerId.pp("New docker instance with id")
port
} match {
case Success(p) => p
case Failure(err) =>
throw new IllegalStateException(s"Error while trying to run docker container", err)
}
}
lazy val dockerIp: String =
Try("docker-machine ip default".!!.trim).toOption
.orElse {
val conf = ConfigFactory.load()
if (conf.hasPath("docker.ip")) Some(conf.getString("docker.ip")) else None
}
.getOrElse("127.0.0.1") // platform dependent
//ugly solution to wait for the connection to be ready
def waitsForConnection(port : Int) : Connection = {
try {
DriverManager.getConnection(s"jdbc:postgresql://$dockerIp:$port/postgres", "postgres", "pwd")
} catch {
case _: PSQLException =>
println("Retrying DB connection...")
Thread.sleep(1000)
waitsForConnection(port)
}
}
before {
val port = newPGDB()
println(s"New postgres ${version.value} instance at port $port")
Thread.sleep(5000)
conn = waitsForConnection(port)
}
after {
conn.close()
dockerInstances.toSeq.foreach { dockerId =>
s"docker stop $dockerId".pp.!!
s"docker rm $dockerId".pp.!!
}
}
}
object Debug {
implicit class RichString(s:String){
def pp :String = pp(None)
def pp(p:String) :String = pp(Some(p))
private def pp(p:Option[String]) = {
println(p.map(_ + " ").getOrElse("") + s)
s
}
}
}
Example 65
| Source File: BatchInsertBenchmark.scala From memsql-spark-connector with Apache License 2.0 | 5 votes |
|
package com.memsql.spark
import java.sql.{Connection, Date, DriverManager}
import java.time.LocalDate
import java.util.Properties
import org.apache.spark.sql.types._
import com.github.mrpowers.spark.daria.sql.SparkSessionExt._
import org.apache.spark.sql.{SaveMode, SparkSession}
import scala.util.Random
// BatchInsertBenchmark is written to test batch insert with CPU profiler
// this feature is accessible in Ultimate version of IntelliJ IDEA
// see https://www.jetbrains.com/help/idea/async-profiler.html#profile for more details
object BatchInsertBenchmark extends App {
final val masterHost: String = sys.props.getOrElse("memsql.host", "localhost")
final val masterPort: String = sys.props.getOrElse("memsql.port", "5506")
val spark: SparkSession = SparkSession
.builder()
.master("local")
.config("spark.sql.shuffle.partitions", "1")
.config("spark.driver.bindAddress", "localhost")
.config("spark.datasource.memsql.ddlEndpoint", s"${masterHost}:${masterPort}")
.config("spark.datasource.memsql.database", "testdb")
.getOrCreate()
def jdbcConnection: Loan[Connection] = {
val connProperties = new Properties()
connProperties.put("user", "root")
Loan(
DriverManager.getConnection(
s"jdbc:mysql://$masterHost:$masterPort",
connProperties
))
}
def executeQuery(sql: String): Unit = {
jdbcConnection.to(conn => Loan(conn.createStatement).to(_.execute(sql)))
}
executeQuery("set global default_partitions_per_leaf = 2")
executeQuery("drop database if exists testdb")
executeQuery("create database testdb")
def genDate() =
Date.valueOf(LocalDate.ofEpochDay(LocalDate.of(2001, 4, 11).toEpochDay + Random.nextInt(10000)))
def genRow(): (Long, Int, Double, String, Date) =
(Random.nextLong(), Random.nextInt(), Random.nextDouble(), Random.nextString(20), genDate())
val df =
spark.createDF(
List.fill(1000000)(genRow()),
List(("LongType", LongType, true),
("IntType", IntegerType, true),
("DoubleType", DoubleType, true),
("StringType", StringType, true),
("DateType", DateType, true))
)
val start = System.nanoTime()
df.write
.format("memsql")
.option("tableKey.primary", "IntType")
.option("onDuplicateKeySQL", "IntType = IntType")
.mode(SaveMode.Append)
.save("testdb.batchinsert")
val diff = System.nanoTime() - start
println("Elapsed time: " + diff + "ns")
}
Example 66
| Source File: LoadDataBenchmark.scala From memsql-spark-connector with Apache License 2.0 | 5 votes |
|
package com.memsql.spark
import java.sql.{Connection, Date, DriverManager}
import java.time.{Instant, LocalDate}
import java.util.Properties
import org.apache.spark.sql.types._
import com.github.mrpowers.spark.daria.sql.SparkSessionExt._
import org.apache.spark.sql.{SaveMode, SparkSession}
import scala.util.Random
// LoadDataBenchmark is written to test load data with CPU profiler
// this feature is accessible in Ultimate version of IntelliJ IDEA
// see https://www.jetbrains.com/help/idea/async-profiler.html#profile for more details
object LoadDataBenchmark extends App {
final val masterHost: String = sys.props.getOrElse("memsql.host", "localhost")
final val masterPort: String = sys.props.getOrElse("memsql.port", "5506")
val spark: SparkSession = SparkSession
.builder()
.master("local")
.config("spark.sql.shuffle.partitions", "1")
.config("spark.driver.bindAddress", "localhost")
.config("spark.datasource.memsql.ddlEndpoint", s"${masterHost}:${masterPort}")
.config("spark.datasource.memsql.database", "testdb")
.getOrCreate()
def jdbcConnection: Loan[Connection] = {
val connProperties = new Properties()
connProperties.put("user", "root")
Loan(
DriverManager.getConnection(
s"jdbc:mysql://$masterHost:$masterPort",
connProperties
))
}
def executeQuery(sql: String): Unit = {
jdbcConnection.to(conn => Loan(conn.createStatement).to(_.execute(sql)))
}
executeQuery("set global default_partitions_per_leaf = 2")
executeQuery("drop database if exists testdb")
executeQuery("create database testdb")
def genRow(): (Long, Int, Double, String) =
(Random.nextLong(), Random.nextInt(), Random.nextDouble(), Random.nextString(20))
val df =
spark.createDF(
List.fill(1000000)(genRow()),
List(("LongType", LongType, true),
("IntType", IntegerType, true),
("DoubleType", DoubleType, true),
("StringType", StringType, true))
)
val start = System.nanoTime()
df.write
.format("memsql")
.mode(SaveMode.Append)
.save("testdb.batchinsert")
val diff = System.nanoTime() - start
println("Elapsed time: " + diff + "ns [CSV serialization] ")
executeQuery("truncate testdb.batchinsert")
val avroStart = System.nanoTime()
df.write
.format(DefaultSource.MEMSQL_SOURCE_NAME_SHORT)
.mode(SaveMode.Append)
.option(MemsqlOptions.LOAD_DATA_FORMAT, "Avro")
.save("testdb.batchinsert")
val avroDiff = System.nanoTime() - avroStart
println("Elapsed time: " + avroDiff + "ns [Avro serialization] ")
}
Example 67
| Source File: BinaryTypeBenchmark.scala From memsql-spark-connector with Apache License 2.0 | 5 votes |
|
package com.memsql.spark
import java.sql.{Connection, DriverManager}
import java.util.Properties
import com.github.mrpowers.spark.daria.sql.SparkSessionExt._
import com.memsql.spark.BatchInsertBenchmark.{df, executeQuery}
import org.apache.spark.sql.types.{BinaryType, IntegerType}
import org.apache.spark.sql.{SaveMode, SparkSession}
import scala.util.Random
// BinaryTypeBenchmark is written to writing of the BinaryType with CPU profiler
// this feature is accessible in Ultimate version of IntelliJ IDEA
// see https://www.jetbrains.com/help/idea/async-profiler.html#profile for more details
object BinaryTypeBenchmark extends App {
final val masterHost: String = sys.props.getOrElse("memsql.host", "localhost")
final val masterPort: String = sys.props.getOrElse("memsql.port", "5506")
val spark: SparkSession = SparkSession
.builder()
.master("local")
.config("spark.sql.shuffle.partitions", "1")
.config("spark.driver.bindAddress", "localhost")
.config("spark.datasource.memsql.ddlEndpoint", s"${masterHost}:${masterPort}")
.config("spark.datasource.memsql.database", "testdb")
.getOrCreate()
def jdbcConnection: Loan[Connection] = {
val connProperties = new Properties()
connProperties.put("user", "root")
Loan(
DriverManager.getConnection(
s"jdbc:mysql://$masterHost:$masterPort",
connProperties
))
}
def executeQuery(sql: String): Unit = {
jdbcConnection.to(conn => Loan(conn.createStatement).to(_.execute(sql)))
}
executeQuery("set global default_partitions_per_leaf = 2")
executeQuery("drop database if exists testdb")
executeQuery("create database testdb")
def genRandomByte(): Byte = (Random.nextInt(256) - 128).toByte
def genRandomRow(): Array[Byte] =
Array.fill(1000)(genRandomByte())
val df = spark.createDF(
List.fill(100000)(genRandomRow()).zipWithIndex,
List(("data", BinaryType, true), ("id", IntegerType, true))
)
val start1 = System.nanoTime()
df.write
.format("memsql")
.mode(SaveMode.Overwrite)
.save("testdb.LoadData")
println("Elapsed time: " + (System.nanoTime() - start1) + "ns [LoadData CSV]")
val start2 = System.nanoTime()
df.write
.format("memsql")
.option("tableKey.primary", "id")
.option("onDuplicateKeySQL", "id = id")
.mode(SaveMode.Overwrite)
.save("testdb.BatchInsert")
println("Elapsed time: " + (System.nanoTime() - start2) + "ns [BatchInsert]")
val avroStart = System.nanoTime()
df.write
.format(DefaultSource.MEMSQL_SOURCE_NAME_SHORT)
.mode(SaveMode.Overwrite)
.option(MemsqlOptions.LOAD_DATA_FORMAT, "Avro")
.save("testdb.AvroSerialization")
println("Elapsed time: " + (System.nanoTime() - avroStart) + "ns [LoadData Avro] ")
}
Example 68
| Source File: H2Sandbox.scala From redshift-fake-driver with Apache License 2.0 | 5 votes |
|
package jp.ne.opt.redshiftfake
import java.sql.{DriverManager, Connection}
import java.util.Properties
import jp.ne.opt.redshiftfake.util.Loan.using
import org.scalatest.{Outcome, fixture}
trait H2Sandbox { self: fixture.TestSuite =>
type FixtureParam = Connection
override def withFixture(test: OneArgTest): Outcome = {
val url = "jdbc:h2redshift:mem:redshift;MODE=PostgreSQL;DATABASE_TO_UPPER=false"
val prop = new Properties()
prop.setProperty("driver", "org.h2.jdbc.FakeH2Driver")
prop.setProperty("user", "sa")
Class.forName("org.h2.jdbc.FakeH2Driver")
using(DriverManager.getConnection(url, prop))(test)
}
}
Example 69
| Source File: JdbcSQLite.scala From Scientific-Computing-with-Scala with MIT License | 5 votes |
|
import java.sql.DriverManager
import java.sql.Connection
object JdbcSqlite {
def main(args: Array[String]) {
var c: Connection = null
try {
Class.forName("org.sqlite.JDBC")
c = DriverManager.getConnection("jdbc:sqlite:planets.sqlite")
} catch {
case e: Throwable => e.printStackTrace
}
c.close()
}
}
Example 70
| Source File: RowDataSourceStrategySuite.scala From sparkoscope with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources
import java.sql.DriverManager
import java.util.Properties
import org.scalatest.BeforeAndAfter
import org.apache.spark.SparkFunSuite
import org.apache.spark.sql.{DataFrame, Row}
import org.apache.spark.sql.sources._
import org.apache.spark.sql.test.SharedSQLContext
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
class RowDataSourceStrategySuite extends SparkFunSuite with BeforeAndAfter with SharedSQLContext {
import testImplicits._
val url = "jdbc:h2:mem:testdb0"
val urlWithUserAndPass = "jdbc:h2:mem:testdb0;user=testUser;password=testPass"
var conn: java.sql.Connection = null
before {
Utils.classForName("org.h2.Driver")
// Extra properties that will be specified for our database. We need these to test
// usage of parameters from OPTIONS clause in queries.
val properties = new Properties()
properties.setProperty("user", "testUser")
properties.setProperty("password", "testPass")
properties.setProperty("rowId", "false")
conn = DriverManager.getConnection(url, properties)
conn.prepareStatement("create schema test").executeUpdate()
conn.prepareStatement("create table test.inttypes (a INT, b INT, c INT)").executeUpdate()
conn.prepareStatement("insert into test.inttypes values (1, 2, 3)").executeUpdate()
conn.commit()
sql(
s"""
|CREATE TEMPORARY TABLE inttypes
|USING org.apache.spark.sql.jdbc
|OPTIONS (url '$url', dbtable 'TEST.INTTYPES', user 'testUser', password 'testPass')
""".stripMargin.replaceAll("\n", " "))
}
after {
conn.close()
}
test("SPARK-17673: Exchange reuse respects differences in output schema") {
val df = sql("SELECT * FROM inttypes")
val df1 = df.groupBy("a").agg("b" -> "min")
val df2 = df.groupBy("a").agg("c" -> "min")
val res = df1.union(df2)
assert(res.distinct().count() == 2) // would be 1 if the exchange was incorrectly reused
}
}
Example 71
| Source File: DriverRegistry.scala From sparkoscope with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.execution.datasources.jdbc
import java.sql.{Driver, DriverManager}
import scala.collection.mutable
import org.apache.spark.internal.Logging
import org.apache.spark.util.Utils
object DriverRegistry extends Logging {
private val wrapperMap: mutable.Map[String, DriverWrapper] = mutable.Map.empty
def register(className: String): Unit = {
val cls = Utils.getContextOrSparkClassLoader.loadClass(className)
if (cls.getClassLoader == null) {
logTrace(s"$className has been loaded with bootstrap ClassLoader, wrapper is not required")
} else if (wrapperMap.get(className).isDefined) {
logTrace(s"Wrapper for $className already exists")
} else {
synchronized {
if (wrapperMap.get(className).isEmpty) {
val wrapper = new DriverWrapper(cls.newInstance().asInstanceOf[Driver])
DriverManager.registerDriver(wrapper)
wrapperMap(className) = wrapper
logTrace(s"Wrapper for $className registered")
}
}
}
}
}
Example 72
| Source File: MySqlPool.scala From BigData-News with Apache License 2.0 | 5 votes |
|
package com.vita.spark
import java.sql.{Connection, DriverManager}
import java.util
import org.apache.log4j.{LogManager, Logger}
/**
* 从mysql连接池中获取连接
*/
class MySqlPool(url: String, user: String, pwd: String) extends Serializable {
//连接池连接总数
private val max = 3
//每次产生连接数
private val connectionNum = 1
//当前连接池已产生的连接数
private var conNum = 0
private val pool = new util.LinkedList[Connection]() //连接池
val LOGGER :Logger = LogManager.getLogger("vita")
//获取连接
def getJdbcConn(): Connection = {
LOGGER.info("getJdbcConn")
//同步代码块，AnyRef为所有引用类型的基类，AnyVal为所有值类型的基类
AnyRef.synchronized({
if (pool.isEmpty) {
//加载驱动
preGetConn()
for (i <- 1 to connectionNum) {
val conn = DriverManager.getConnection(url, user, pwd)
pool.push(conn)
conNum += 1
}
}
pool.poll()
})
}
//释放连接
def releaseConn(conn: Connection): Unit = {
pool.push(conn)
}
//加载驱动
private def preGetConn(): Unit = {
//控制加载
if (conNum < max && !pool.isEmpty) {
LOGGER.info("Jdbc Pool has no connection now, please wait a moments!")
Thread.sleep(2000)
preGetConn()
} else {
Class.forName("com.mysql.jdbc.Driver")
}
}
}
Example 73
| Source File: ThriftServerBaseTest.scala From incubator-livy with Apache License 2.0 | 5 votes |
|
package org.apache.livy.thriftserver
import java.sql.{Connection, DriverManager, Statement}
import org.apache.hive.jdbc.HiveDriver
import org.scalatest.{BeforeAndAfterAll, FunSuite}
import org.apache.livy.LivyConf
import org.apache.livy.LivyConf.{LIVY_SPARK_SCALA_VERSION, LIVY_SPARK_VERSION}
import org.apache.livy.server.AccessManager
import org.apache.livy.server.recovery.{SessionStore, StateStore}
import org.apache.livy.sessions.InteractiveSessionManager
import org.apache.livy.utils.LivySparkUtils.{formatSparkVersion, sparkScalaVersion, sparkSubmitVersion}
object ServerMode extends Enumeration {
val binary, http = Value
}
abstract class ThriftServerBaseTest extends FunSuite with BeforeAndAfterAll {
def mode: ServerMode.Value
def port: Int
val THRIFT_SERVER_STARTUP_TIMEOUT = 30000 // ms
val livyConf = new LivyConf()
val (sparkVersion, scalaVersionFromSparkSubmit) = sparkSubmitVersion(livyConf)
val formattedSparkVersion: (Int, Int) = {
formatSparkVersion(sparkVersion)
}
def jdbcUri(defaultDb: String, sessionConf: String*): String = if (mode == ServerMode.http) {
s"jdbc:hive2://localhost:$port/$defaultDb?hive.server2.transport.mode=http;" +
s"hive.server2.thrift.http.path=cliservice;${sessionConf.mkString(";")}"
} else {
s"jdbc:hive2://localhost:$port/$defaultDb?${sessionConf.mkString(";")}"
}
override def beforeAll(): Unit = {
Class.forName(classOf[HiveDriver].getCanonicalName)
livyConf.set(LivyConf.THRIFT_TRANSPORT_MODE, mode.toString)
livyConf.set(LivyConf.THRIFT_SERVER_PORT, port)
// Set formatted Spark and Scala version into livy configuration, this will be used by
// session creation.
livyConf.set(LIVY_SPARK_VERSION.key, formattedSparkVersion.productIterator.mkString("."))
livyConf.set(LIVY_SPARK_SCALA_VERSION.key,
sparkScalaVersion(formattedSparkVersion, scalaVersionFromSparkSubmit, livyConf))
StateStore.init(livyConf)
val ss = new SessionStore(livyConf)
val sessionManager = new InteractiveSessionManager(livyConf, ss)
val accessManager = new AccessManager(livyConf)
LivyThriftServer.start(livyConf, sessionManager, ss, accessManager)
LivyThriftServer.thriftServerThread.join(THRIFT_SERVER_STARTUP_TIMEOUT)
assert(LivyThriftServer.getInstance.isDefined)
assert(LivyThriftServer.getInstance.get.getServiceState == STATE.STARTED)
}
override def afterAll(): Unit = {
LivyThriftServer.stopServer()
}
def withJdbcConnection(f: (Connection => Unit)): Unit = {
withJdbcConnection("default", Seq.empty)(f)
}
def withJdbcConnection(db: String, sessionConf: Seq[String])(f: (Connection => Unit)): Unit = {
withJdbcConnection(jdbcUri(db, sessionConf: _*))(f)
}
def withJdbcConnection(uri: String)(f: (Connection => Unit)): Unit = {
val user = System.getProperty("user.name")
val connection = DriverManager.getConnection(uri, user, "")
try {
f(connection)
} finally {
connection.close()
}
}
def withJdbcStatement(f: (Statement => Unit)): Unit = {
withJdbcConnection { connection =>
val s = connection.createStatement()
try {
f(s)
} finally {
s.close()
}
}
}
}
Example 74
| Source File: BaseThriftIntegrationTestSuite.scala From incubator-livy with Apache License 2.0 | 5 votes |
|
package org.apache.livy.test.framework
import java.sql.{Connection, DriverManager, ResultSet}
class BaseThriftIntegrationTestSuite extends BaseIntegrationTestSuite {
private var jdbcUri: String = _
override def beforeAll(): Unit = {
cluster = Cluster.get()
// The JDBC endpoint must contain a valid value
assert(cluster.jdbcEndpoint.isDefined)
jdbcUri = cluster.jdbcEndpoint.get
}
def checkQuery(connection: Connection, query: String)(validate: ResultSet => Unit): Unit = {
val ps = connection.prepareStatement(query)
try {
val rs = ps.executeQuery()
try {
validate(rs)
} finally {
rs.close()
}
} finally {
ps.close()
}
}
def withConnection[T](f: Connection => T): T = {
val connection = DriverManager.getConnection(jdbcUri)
try {
f(connection)
} finally {
connection.close()
}
}
}
Example 75
| Source File: ExasolDockerContainerSuite.scala From spark-exasol-connector with Apache License 2.0 | 5 votes |
|
package com.exasol.spark
import java.sql.DriverManager
import java.sql.SQLException
import org.scalatest.funsuite.AnyFunSuite
class ExasolDockerContainerSuite extends AnyFunSuite with BaseDockerSuite {
test("exasol/docker-db container should be started") {
Class.forName(container.driverClassName) // scalastyle:ignore classForName
val connectionStr =
s"${container.jdbcUrl};user=${container.username};password=${container.password}"
val connection = DriverManager.getConnection(connectionStr)
val prepareStatement = connection.prepareStatement(container.testQueryString)
try {
val resultSet = prepareStatement.executeQuery()
while (resultSet.next()) {
assert(resultSet.getInt(1) == 1)
}
resultSet.close()
} catch {
case ex: SQLException => ex.printStackTrace()
} finally {
prepareStatement.close()
}
connection.close()
}
}
Example 76
| Source File: SparkExecutionPlanProcessForRdbmsQuerySuite.scala From spark-atlas-connector with Apache License 2.0 | 5 votes |
|
package com.hortonworks.spark.atlas.sql
import org.scalatest.{BeforeAndAfter, FunSuite, Matchers}
import java.sql.DriverManager
import com.hortonworks.spark.atlas.{AtlasClientConf, AtlasUtils, WithHiveSupport}
import com.hortonworks.spark.atlas.AtlasEntityReadHelper._
import com.hortonworks.spark.atlas.sql.testhelper.{AtlasQueryExecutionListener, CreateEntitiesTrackingAtlasClient, DirectProcessSparkExecutionPlanProcessor, ProcessEntityValidator}
import com.hortonworks.spark.atlas.types.{external, metadata}
import org.apache.atlas.model.instance.AtlasEntity
class SparkExecutionPlanProcessForRdbmsQuerySuite
extends FunSuite
with Matchers
with BeforeAndAfter
with WithHiveSupport
with ProcessEntityValidator {
val sinkTableName = "sink_table"
val sourceTableName = "source_table"
val databaseName = "testdb"
val jdbcDriver = "org.apache.derby.jdbc.EmbeddedDriver"
val atlasClientConf: AtlasClientConf = new AtlasClientConf()
var atlasClient: CreateEntitiesTrackingAtlasClient = _
val testHelperQueryListener = new AtlasQueryExecutionListener()
before {
// setup derby database and necesaary table
val connectionURL = s"jdbc:derby:memory:$databaseName;create=true"
Class.forName(jdbcDriver)
val connection = DriverManager.getConnection(connectionURL)
val createSinkTableQuery = s"CREATE TABLE $sinkTableName (NAME VARCHAR(20))"
val createSourceTableQuery = s"CREATE TABLE $sourceTableName (NAME VARCHAR(20))"
val insertQuery = s"INSERT INTO $sourceTableName (Name) VALUES ('A'), ('B'), ('C')"
val statement = connection.createStatement
statement.executeUpdate(createSinkTableQuery)
statement.executeUpdate(createSourceTableQuery)
statement.executeUpdate(insertQuery)
// setup Atlas client
atlasClient = new CreateEntitiesTrackingAtlasClient()
sparkSession.listenerManager.register(testHelperQueryListener)
}
test("read from derby table and insert into a different derby table") {
val planProcessor = new DirectProcessSparkExecutionPlanProcessor(atlasClient, atlasClientConf)
val jdbcProperties = new java.util.Properties
jdbcProperties.setProperty("driver", jdbcDriver)
val url = s"jdbc:derby:memory:$databaseName;create=false"
val readDataFrame = sparkSession.read.jdbc(url, sourceTableName, jdbcProperties)
readDataFrame.write.mode("append").jdbc(url, sinkTableName, jdbcProperties)
val queryDetail = testHelperQueryListener.queryDetails.last
planProcessor.process(queryDetail)
val entities = atlasClient.createdEntities
// we're expecting two table entities:
// one from the source table and another from the sink table
val tableEntities = listAtlasEntitiesAsType(entities, external.RDBMS_TABLE)
assert(tableEntities.size === 2)
val inputEntity = getOnlyOneEntityOnAttribute(tableEntities, "name", sourceTableName)
val outputEntity = getOnlyOneEntityOnAttribute(tableEntities, "name", sinkTableName)
assertTableEntity(inputEntity, sourceTableName)
assertTableEntity(outputEntity, sinkTableName)
// check for 'spark_process'
validateProcessEntityWithAtlasEntities(entities, _ => {},
AtlasUtils.entitiesToReferences(Seq(inputEntity)),
AtlasUtils.entitiesToReferences(Seq(outputEntity)))
}
private def assertTableEntity(entity: AtlasEntity, tableName: String): Unit = {
val tableQualifiedName = getStringAttribute(entity, "qualifiedName")
assert(tableQualifiedName.equals(s"$databaseName.$tableName"))
}
}
Example 77
| Source File: ShopProfilesTest.scala From schedoscope with Apache License 2.0 | 5 votes |
|
package schedoscope.example.osm.datamart
import java.sql.DriverManager
import org.schedoscope.dsl.Field._
import org.schedoscope.test.{SchedoscopeSpec, rows, test}
import schedoscope.example.osm.datahub.{Restaurants, Shops, Trainstations}
class ShopProfilesTest extends SchedoscopeSpec {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver")
val dbConnection = DriverManager.getConnection("jdbc:derby:memory:TestingDB;create=true")
val shops = new Shops() with rows {
set(v(id, "122546"),
v(shopName, "Netto"),
v(shopType, "supermarket"),
v(area, "t1y87ki"))
set(v(id, "274850441"),
v(shopName, "Schanzenbaeckerei"),
v(shopType, "bakery"),
v(area, "t1y87ki"))
set(v(id, "279023080"),
v(shopName, "Edeka Linow"),
v(shopType, "supermarket"),
v(area, "t1y77d8"))
}
val restaurants = new Restaurants() with rows {
set(v(id, "267622930"),
v(restaurantName, "Cuore Mio"),
v(restaurantType, "italian"),
v(area, "t1y06x1"))
set(v(id, "288858596"),
v(restaurantName, "Jam Jam"),
v(restaurantType, "japanese"),
v(area, "t1y87ki"))
set(v(id, "302281521"),
v(restaurantName, "Walddoerfer Croque Cafe"),
v(restaurantType, "burger"),
v(area, "t1y17m9"))
}
val trainstations = new Trainstations() with rows {
set(v(id, "122317"),
v(stationName, "Hagenbecks Tierpark"),
v(area, "t1y140d"))
set(v(id, "122317"),
v(stationName, "Boenningstedt"),
v(area, "t1y87ki"))
}
"datamart.ShopProfiles" should "load correctly from datahub.shops, datahub.restaurants, datahub.trainstations" in {
new ShopProfiles() with test {
configureExport("schedoscope.export.jdbcConnection", "jdbc:derby:memory:NullDB;create=true")
configureExport("schedoscope.export.dbUser", null)
configureExport("schedoscope.export.dbPass", null)
basedOn(shops, restaurants, trainstations)
then()
numRows shouldBe 3
row(v(id) shouldBe "122546",
v(shopName) shouldBe "Netto",
v(shopType) shouldBe "supermarket",
v(area) shouldBe "t1y87ki",
v(cntCompetitors) shouldBe 1,
v(cntRestaurants) shouldBe 1,
v(cntTrainstations) shouldBe 1)
}
}
it should "export data to JDBC as well" in {
new ShopProfiles() with test {
configureExport("schedoscope.export.jdbcConnection", "jdbc:derby:memory:TestingDB")
configureExport("schedoscope.export.dbUser", null)
configureExport("schedoscope.export.dbPass", null)
basedOn(shops, restaurants, trainstations)
then()
numRows shouldBe 3
}
val statement = dbConnection.createStatement()
val resultSet = statement.executeQuery("SELECT COUNT(*) FROM DEV_SCHEDOSCOPE_EXAMPLE_OSM_DATAMART_SHOP_PROFILES")
resultSet.next()
resultSet.getInt(1) shouldBe 3
resultSet.close()
statement.close()
}
}
Example 78
| Source File: SQLiteConnectionPool.scala From airframe with Apache License 2.0 | 5 votes |
|
package wvlet.airframe.jdbc
import java.io.File
import java.sql.{Connection, DriverManager}
import wvlet.log.Guard
class SQLiteConnectionPool(val config: DbConfig) extends ConnectionPool with Guard {
private var conn: Connection = newConnection
private def newConnection: Connection = {
// Prepare parent db folder
Option(new File(config.database).getParentFile).map { p =>
if (!p.exists()) {
info(s"Create db folder: ${p}")
p.mkdirs()
}
}
val jdbcUrl = config.jdbcUrl
info(s"Opening ${jdbcUrl}")
// We need to explicitly load sqlite-jdbc to cope with SBT's peculiar class loader
Class.forName(config.jdbcDriverName)
val conn = DriverManager.getConnection(jdbcUrl)
conn.setAutoCommit(true)
conn
}
def withConnection[U](body: Connection => U): U = {
guard {
if (conn.isClosed) {
conn = newConnection
}
// In sqlite-jdbc, we can reuse the same connection instance,
// and we have no need to close the connection
body(conn)
}
}
def stop: Unit = {
info(s"Closing the connection pool for ${config.jdbcUrl}")
conn.close()
}
}
Example 79
| Source File: DockerPostgresService.scala From docker-it-scala with MIT License | 5 votes |
|
package com.whisk.docker
import java.sql.DriverManager
import scala.concurrent.ExecutionContext
import scala.util.Try
trait DockerPostgresService extends DockerKit {
import scala.concurrent.duration._
def PostgresAdvertisedPort = 5432
def PostgresExposedPort = 44444
val PostgresUser = "nph"
val PostgresPassword = "suitup"
val postgresContainer = DockerContainer("postgres:9.5.3")
.withPorts((PostgresAdvertisedPort, Some(PostgresExposedPort)))
.withEnv(s"POSTGRES_USER=$PostgresUser", s"POSTGRES_PASSWORD=$PostgresPassword")
.withReadyChecker(
new PostgresReadyChecker(PostgresUser, PostgresPassword, Some(PostgresExposedPort))
.looped(15, 1.second)
)
abstract override def dockerContainers: List[DockerContainer] =
postgresContainer :: super.dockerContainers
}
class PostgresReadyChecker(user: String, password: String, port: Option[Int] = None)
extends DockerReadyChecker {
override def apply(container: DockerContainerState)(implicit docker: DockerCommandExecutor,
ec: ExecutionContext) =
container
.getPorts()
.map(ports =>
Try {
Class.forName("org.postgresql.Driver")
val url = s"jdbc:postgresql://${docker.host}:${port.getOrElse(ports.values.head)}/"
Option(DriverManager.getConnection(url, user, password)).map(_.close).isDefined
}.getOrElse(false))
}
Example 80
| Source File: ThriftServerTest.scala From Hive-JDBC-Proxy with Apache License 2.0 | 5 votes |
|
package com.enjoyyin.hive.proxy.jdbc.test
import java.sql.{Connection, DriverManager, ResultSet, Statement}
import com.enjoyyin.hive.proxy.jdbc.util.Utils
private object ThriftServerTest extends App {
val sql = """show tables"""
val test_url = "jdbc:hive2://localhost:10001/default"
Class.forName("org.apache.hive.jdbc.HiveDriver")
def test(index: Int) = {
var conn: Connection = null
var stmt: Statement = null
var rs: ResultSet = null
Utils.tryFinally {
conn = DriverManager.getConnection(test_url, "hduser0009", "")
stmt = conn.createStatement
rs = stmt.executeQuery(sql)
while(rs.next) {
println ("Date: " + Utils.dateFormat(System.currentTimeMillis) + ", " + index + ".tables => " + rs.getObject(1))
}
println("Date: " + Utils.dateFormat(System.currentTimeMillis) + ", ready to close " + index)
} {
if(rs != null) Utils.tryIgnoreError(rs.close())
if(stmt != null) Utils.tryIgnoreError(stmt.close())
if(conn != null) Utils.tryIgnoreError(conn.close())
}
}
(0 until 8).foreach(i => new Thread {
setName("thread-" + i)
override def run(): Unit = {
Utils.tryCatch(test(i)) { t =>
println("Date: " + Utils.dateFormat(System.currentTimeMillis) + ", " + i + " has occur an error.")
t.printStackTrace()
}
}
}.start())
}
Example 81
| Source File: SapThriftJdbcTest.scala From HANAVora-Extensions with Apache License 2.0 | 5 votes |
|
package org.apache.spark.sql.hive.sap.thriftserver
import java.sql.{DriverManager, Statement}
import org.apache.hive.jdbc.HiveDriver
import org.scalatest.{BeforeAndAfterAll, FunSuite}
abstract class SapThriftJdbcTest(val thriftServer: SapThriftServer2Test){
def jdbcUri: String
def withMultipleConnectionJdbcStatement(fs: (Statement => Unit)*) {
val user = System.getProperty("user.name")
val connections = fs.map { _ => DriverManager.getConnection(jdbcUri, user, "") }
val statements = connections.map(_.createStatement())
try {
statements.zip(fs).foreach { case (s, f) => f(s) }
} finally {
statements.foreach(_.close())
connections.foreach(_.close())
}
}
def withJdbcStatement(f: Statement => Unit): Unit = {
withMultipleConnectionJdbcStatement(f)
}
}
class SapThriftJdbcHiveDriverTest(override val thriftServer: SapThriftServer2Test)
extends SapThriftJdbcTest(thriftServer) {
Class.forName(classOf[HiveDriver].getCanonicalName)
override def jdbcUri: String = if (thriftServer.mode == ServerMode.http) {
s"""jdbc:hive2://${thriftServer.getServerAdressAndPort()}/
|default?
|hive.server2.transport.mode=http;
|hive.server2.thrift.http.path=cliservice
""".stripMargin.split("\n").mkString.trim
} else {
s"jdbc:hive2://${thriftServer.getServerAdressAndPort()}/"
}
}
