CODEWITHSUNDEEP
pysparkamazon-athena
Nipun
Nipun

Reputation: 4319

spark Athena connector

I need to use Athena in spark but spark uses preparedStatement when using JDBC drivers and it gives me an exception "com.amazonaws.athena.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented"

Can you please let me know how can I connect Athena in spark

Upvotes: 3

Views: 23145

Answers (5)

André Claudino
André Claudino

Reputation: 588

Actually you can use B2W's Spark Athena Driver.

https://github.com/B2W-BIT/athena-spark-driver

Upvotes: 0

Wil
Wil

Reputation: 574

You can use this JDBC driver: SimbaAthenaJDBC

<dependency>
    <groupId>com.syncron.amazonaws</groupId>
    <artifactId>simba-athena-jdbc-driver</artifactId>
    <version>2.0.2</version>
</dependency>

to use:

SparkSession spark = SparkSession
    .builder()
    .appName("My Spark Example")
    .getOrCreate();

Class.forName("com.simba.athena.jdbc.Driver");

Properties connectionProperties = new Properties();
connectionProperties.put("User", "AWSAccessKey");
connectionProperties.put("Password", "AWSSecretAccessKey");
connectionProperties.put("S3OutputLocation", "s3://my-bucket/tmp/");
connectionProperties.put("AwsCredentialsProviderClass", 
    "com.simba.athena.amazonaws.auth.PropertiesFileCredentialsProvider");
connectionProperties.put("AwsCredentialsProviderArguments", "/my-folder/.athenaCredentials");
connectionProperties.put("driver", "com.simba.athena.jdbc.Driver");

List<String> predicateList =
    Stream
        .of("id = 'foo' and date >= DATE'2018-01-01' and date < DATE'2019-01-01'")
        .collect(Collectors.toList());
String[] predicates = new String[predicateList.size()];
predicates = predicateList.toArray(predicates);

Dataset<Row> data =
    spark.read()
        .jdbc("jdbc:awsathena://AwsRegion=us-east-1;",
            "my_env.my_table", predicates, connectionProperties);

You can also use this driver in a Flink application:

TypeInformation[] fieldTypes = new TypeInformation[] {
    BasicTypeInfo.STRING_TYPE_INFO,
    BasicTypeInfo.STRING_TYPE_INFO
};

RowTypeInfo rowTypeInfo = new RowTypeInfo(fieldTypes);

JDBCInputFormat jdbcInputFormat = JDBCInputFormat.buildJDBCInputFormat()
    .setDrivername("com.simba.athena.jdbc.Driver")
    .setDBUrl("jdbc:awsathena://AwsRegion=us-east-1;UID=my_access_key;PWD=my_secret_key;S3OutputLocation=s3://my-bucket/tmp/;")
    .setQuery("select id, val_col from my_env.my_table WHERE id = 'foo' and date >= DATE'2018-01-01' and date < DATE'2019-01-01'")
    .setRowTypeInfo(rowTypeInfo)
    .finish();

DataSet<Row> dbData = env.createInput(jdbcInputFormat, rowTypeInfo);

Upvotes: 6

Isaac
Isaac

Reputation: 423

The response of @Kirk Broadhurst is correct if you want to use the data of Athena. If you want to use the Athena engine, then, there is a lib on github that overcomes the preparedStatement problem.

Note that I didn't succeed to use the lib, due to my lack of experience with Maven etc

Upvotes: 1

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

I don't know how you'd connect to Athena from Spark, but you don't need to - you can very easily query the data that Athena contains (or, more correctly, "registers") from Spark.

There are two parts to Athena

  1. Hive Metastore (now called the Glue Data Catalog) which contains mappings between database and table names and all underlying files
  2. Presto query engine which translates your SQL into data operations against those files

When you start an EMR cluster (v5.8.0 and later) you can instruct it to connect to your Glue Data Catalog. This is a checkbox in the 'create cluster' dialog. When you check this option your Spark SqlContext will connect to the Glue Data Catalog, and you'll be able to see the tables in Athena.

You can then query these tables as normal.

See https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-spark-glue.html for more.

Upvotes: 12

Zerodf
Zerodf

Reputation: 2298

You can't directly connect Spark to Athena. Athena is simply an implementation of Prestodb targeting s3. Unlike Presto, Athena cannot target data on HDFS.

However, if you want to use Spark to query data in s3, then you are in luck with HUE, which will let you query data in s3 from Spark on Elastic Map Reduce (EMR).

See Also: Developer Guide for Hadoop User Experience (HUE) on EMR.

Upvotes: 1

Related Questions