JDBC Connection

With Amorphic, you can connect to a query engine from numerous different programs. Examples of these programs include SQL Workbench, DbVisualizer, and Quicksight.

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: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6Ikpva
     G4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c

• 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/AmazonAthenaEngineV3>"

   S3OutputLocation - The s3 location format should be s3://<project_name>-<region>-<account_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 exceed 220, the custom jar may throw a 'Missing Authentication Token' error. To work around this limit you can create a dummy user and share some of the datasets of interest with the dummy user, and use the dummy user's credentials to access the datasets.

Connecting to Query engine from IDE

If you need sample code to connect to the query engine from your IDE, please create a Zendesk ticket to request it.