How to query the data from Amazon S3 via JDBC connection in Db2 Federation Server?

Created by Jun Hui Liu on Mon, 02/01/2021 - 03:44

Published URL:

https://www.ibm.com/support/pages/node/6410440

6410440

How To

Summary

When the user wants to query the data from Amazon S3 via JDBC in Db2 Federation Server, please follow the steps below:

1. Download the Amazon Athena JDBC driver;
2. Create JDBC Wrapper, Server, User Mapping, and Nickname, and then query values from Amazon Athena.

Objective

Query the data from Amazon S3 via Amazon Athena with JDBC connection in Db2 Federation Server

Environment

Linux,AIX

Steps

When user wants to query the data from Amazon S3, it uses Amazon Athena with JDBC connection in Db2 Federation Server.

1. Create table in Amazon Athena console for files in Amazon S3.

--- Run in Amazon Athena console
--- Create external table in Athena for data folder s3://federationdir/test in S3.

CREATE EXTERNAL TABLE testdbu.`test_int` (
  `col` int)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 

LOCATION
  's3://federationdir/test';
  
---insert into test_int values(-32768),(127);

select * from test_int;

2. Download JDBC driver from Amazon Athena official site with latest version.

For example, download the version AthenaJDBC42.jar

2. Registering JDBC wrapper

db2 => create wrapper jdbc
DB20000I  The SQL command completed successfully.

3. Create Server and User mapping for Amazon Athena

The details of connection properties in "url" can be got here in "JDBC Driver Installation and Configuration Guide".

db2 => CREATE SERVER ATHENA wrapper JDBC options (DRIVER_PACKAGE '/home/db2inst1/drivers/AthenaJDBC42.jar', driver_class 'com.simba.athena.jdbc.Driver', url 'jdbc:awsathena://S3OutputLocation=s3://federation/out;AwsRegion=us-east-2;Schema=testdbu', JDBC_LOG 'Y', PUSHDOWN 'Y', db2_maximal_pushdown 'Y')
DB20000I  The SQL command completed successfully.

-- REMOTE_AUTHID is set to be the access key provided by your AWS account
-- REMOTE_PASSWORD is set to be the secret key provided by your AWS account
db2 => create user mapping for user server ATHENA options ( REMOTE_AUTHID 'AKIAISQPTH36YLUUUUUU', REMOTE_PASSWORD 'lQ8kx/z/+9YPv7v1Dk+cegNEmk9RAmygsXXPPPPPP')
DB20000I  The SQL command completed successfully.

4. Create nickname for table of Athena, and query values from nickname

db2 => create nickname nk_int for ATHENA."testdbu"."test_int"
DB20000I  The SQL command completed successfully.
db2 => describe table nk_int

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
col                             SYSIBM    INTEGER                      4     0 Yes   

  1 record(s) selected.

db2 => select * from nk_int where "col" = 127

col        
-----------
        127

  1 record(s) selected.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"11.5.0"}]