JDBC Connection

Amorphic application provides the user the ability to connect to query engine from a third party application. SQL Workbench, DbVisualizer, Quicksight are some of the prominent third party applications being used.

Credentials

To initiate a jdbc connection to query engine, you would need the following credentials:

• api_gateway_url

  Click on documentation icon on top right corner of home page and navigate to API docs to find the base url.

  Eg: 1234567890.execute-api.us-west-2.amazonaws.com/dev

• personal_access_token

  Click on user profile icon on top right corner of home page and navigate to profile & settings -> Click on Access tokens and click create new token on the top right corner. This role should have fullaccess to run queries on query engine. (runquery.fullaccess)

  Eg: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJzdWIiOiI3MmY0NDEwZS0xZTMwLTRiYjItYTNkZi1kNzQ5ODczYjcyZjUiLC
     JhdWQiOiJkM213OHE3NWJrazZiMCIsImlzcyI6IlBBVF9HRU4iLCJ0b2tlbl91c2UiOiJwYXQiLCJuYW1lIjoic3VkaGVlciIsIm
     NvZ25pdG86dXNlcm5hbWUiOiJzdWRoZWVyIiTestImFkbWluLXJvbGUtM2RiMWFjMjETestYy00OTMyLWI5YzgtMTdjMj
     UwNjM5YzJhIiwiZXhwIjoxNjM4OTExMTYwLCJlbWFpbCI6InN1ZGhlZXIuYm9uZGFkYUBjbG91ZHdpY2suY29tIiwiaWF0IjoxNj

• role_id Click on Management button from lower left corner of home screen -> Navigate to roles and click on the role used in the previous step and make a note of role id from url.

  Eg: admin-role-123456-81cf-4dd2-b876-1234567890

Amorphic custom credentials jar

Along with the above mentioned credentials you will need to download Amazon Athena JDBC Driver and Amorphic custom credentials jar

Connecting to Query engine from SQL Workbench

1. In SQL Workbench, Open File -> Manage Drivers. Choose the Athena driver and add two libraries, Athena JDBC driver and the custom credentials provider by specifying the location where you downloaded them.

   

2. To enable running queries in Query engine through SQL Workbench, configure a query engine connection with the below URL:

   jdbc:awsathena://AwsRegion=<region>

   

3. Choose Extended Properties and enter the properties as follows:

   "AWSCredentialsProviderClass"="com.amazonaws.custom.athena.jdbc.AmorphicCustomCredentialsProvider"
   
   "AWSCredentialsProviderArguments"="<api_gateway_url>,<personal_access_token>,<role_id>"
   
   "S3OutputLocation"="s3://<bucket_where_query_results_are_stored>"
   
   "LogPath"="<local_path_where_logs_are_stored>"
   
   "LogLevel"="<Log_Level_from_0_to_6>"
   
   "Workgroup"="<primary/AmazonAthenaLakeFormation>"

   S3OutputLocation - The s3 location format should be s3://<project_name>-<region>-<accound_id>-<env_name>-athena/<user_id>

   Query Engine now supports Workgroups concept with the latest version of Athena JDBC driver

4. Choose Test to verify that you can successfully connect to query engine.

   

5. Run a query in SQL Workbench to verify that credentials are correctly applied.

   

   limits

   If the number of datasets and views are more than 220 then the custom jar may throw 'Missing Authentication Token' error. To get around this limit, say if a user has 500 datasets, create a dummy user and share some datasets (of your interest) with the dummy user and user the dummy user's credentials to access datasets.

Connecting to Query engine from IDE

If you require sample code to connect to query engine from IDE. Please create a Zendesk ticket requesting the same.