zaneli/scalikejdbc-athena

Library for using Amazon Athena JDBC Driver with ScalikeJDBC

scalikejdbc-athena

Library for using Amazon Athena JDBC Driver with ScalikeJDBC

setup

• Download Athena JDBC 2.x driver
  • This library is basically depends on Athena JDBC 2.x driver.
    Choose your preferred or latest version in 2.x.
    If you encounter problems with a particular version, please feel free to report it.

> mkdir lib
> curl -L -O https://downloads.athena.us-east-1.amazonaws.com/drivers/JDBC/SimbaAthenaJDBC-2.1.1.1000/AthenaJDBC42-2.1.1.1000.jar
> mv AthenaJDBC42-2.1.1.1000.jar lib/

• Add library dependencies to sbt build settings

libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc" % "4.0.0",
  "com.zaneli" %% "scalikejdbc-athena" % "0.3.0"
)

• Configure the JDBC Driver Options on resources/application.conf

athena {
  default {
    driver="com.simba.athena.jdbc.Driver"
    url="jdbc:awsathena://AwsRegion={REGION}"
    readOnly="false"
    S3OutputLocation="s3://query-results-bucket/folder/"
    AwsCredentialsProviderClass="com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider"
    LogPath="logs/application.log"
    LogLevel=3
  }
}

If you need to update partitions etc., set readOnly="false"

Usage

Run query

import scalikejdbc._
import scalikejdbc.athena._

val name = "elb_demo_001"
DB.athena { implicit s =>
  val r = sql"""
          |SELECT * FROM default.elb_logs_raw_native
          |WHERE elb_name = $name LIMIT 10;
         """.stripMargin.map(_.toMap).list.apply()
  r.foreach(println)
}

Delete S3OutputLocation after run query

• set S3OutputLocationPrefix instead of S3OutputLocation

athena {
  default {
    driver="com.simba.athena.jdbc.Driver"
    url="jdbc:awsathena://AwsRegion={REGION}"
    readOnly="false"
    S3OutputLocationPrefix="s3://query-results-bucket/folder"
    AwsCredentialsProviderClass="com.simba.athena.amazonaws.auth.profile.ProfileCredentialsProvider"
    LogPath="logs/application.log"
    LogLevel=3
  }
}

• use aws-java-sdk-s3

import com.amazonaws.auth.profile.ProfileCredentialsProvider
import com.amazonaws.services.s3.AmazonS3ClientBuilder
import com.amazonaws.services.s3.model.DeleteObjectsRequest

import scalikejdbc._
import scalikejdbc.athena._

import scala.collection.JavaConverters._

val s3Client = AmazonS3ClientBuilder.standard().withCredentials(new ProfileCredentialsProvider()).build()
val regex = """s3://(.+?)/(.+)""".r

DB.athena { implicit s =>
  val r = sql"...".map(_.toMap).list.apply()
  r.foreach(println)

  s.getTmpStagingDir.foreach { // Some("s3://query-results-bucket/folder/${java.util.UUID.randomUUID}")
    case regex(bucketName, path) =>
      val keys = s3Client.listObjects(bucketName, path).getObjectSummaries.asScala
        .map(s => new DeleteObjectsRequest.KeyVersion(s.getKey))
      if (keys.nonEmpty) {
        val delReq = new DeleteObjectsRequest(bucketName)
        delReq.setKeys(keys.asJava)
        s3Client.deleteObjects(delReq)
      }
  }
}

---

scalikejdbc-athena is inspired by scalikejdbc-bigquery.

scalikejdbc-athena's Issues

Update JDBC version from 4.1 to 4.2

According to a Athena JDBC driver document, JDBC version 4.1 will be EOL.
https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

Update AthenaJDBC version to 2.0.7

Add Driver Configuration Options added in AthenaJDBC 2.0.6 and 2.0.7

new feature

• SAML authentication
• Workgroup
• CATS statements

etc

Simba Athena JDBC Driver with SQL Connector

DateTime conversion issue

Hi!

Thanks for this project, it's quite convenient to be able to use bound variables with Athena.

I've noticed there's an issue when making use of sqlikejdbc's datetime conversion feature. I get a nullpointer here if I try to use rs.zonedDateTime("myColumnName")

This is because scalikejdbc sets the connectionAttributes to null here

I'm not sure if there's a work around or what's the best way to fix this.

maven repository

thanks for the library! what're your thoughts about publishing it to maven repository?

Support Athena JDBC 3.x driver

https://docs.aws.amazon.com/athena/latest/ug/jdbc-v3-driver.html

Perhaps we need to re-examine the handling of connection parameters.

Update AthenaJDBC version to 2.0.5

I want to use AthenaJDBC41_2.0.5. It is different from the configuration of AthenaJDBC41-1.1.0.
Migration from Previous Version of the JDBC Driver

Installation and Configuration Guide

New feature in Version 2.0.5

Amazon Athena releases an updated JDBC driver with improved performance when retrieving results