Loading [MathJax]/jax/output/CommonHTML/jax.js
Skip to main content

Use Athena with Data Access Server on Privacera Platform

This topic describes how to set up tools like SQL Workbench, DBeaver, and Tableau to connect to Athena through Privacera Data Access Server.

Create Athena policy

Create a policy to allow the user to give select permission on test_table which has columns id, name, email_address, address, and ssn.

  1. From the navigation menu, select Access Management > Resource Policies.

  2. On the Resource Policies page, click privacera_athena > Add New Policy.

  3. Enter the following details:

    • Policy Name: athena_test_policy

    • workgroup: primary (Athena workgroup on which you want to allow the user to execute queries).

    • Datasource: test_ds (Datasource Name)

    • Database: test_db (Database Name)

    • Table: test_table (Table Name)

    • Column: id, name, email_address, address (Either put * or you can specify column names on which you want to allow the user to access it.) ssn column is excluded here.

    • In the Allow Conditions section, click '+' icon and do the following:

      • In the Select User field , from the dropdown, select a user which you want to allow access.

      • In the Permission field, click Add Permissions + and then click the Select checkbox.

  4. Click Save.

Create S3 access manager policy

Create a policy to allow the user to give access to query-results-bucket for READ and WRITE operations. This bucket will be used by Athena to store the results of executed queries.

  1. On the Resource Policies page, click privacera_s3 > Add New Policy.

  2. Enter the following details:

    • Policy Name: s3_athena_test_policy

    • Bucket Name: query-results-bucket (S3 Bucket Name)

    • Object Path: * (File/Directory/Object Path Inside Bucket)

    • Under Allow Conditions section, click '+' icon and do the following:

      • In the Select User field , from the dropdown, select a user which you want to allow access.

      • In the Permission field, click Add Permissions + and then select read, metadata read, write, metadata write checkbox.

  3. Click Save.

Access Athena data through the AWS CLI

You need to Enable AWS CLI on Privacera Platform to perform the below steps.Enable AWS CLI on Privacera Platform

  1. From a terminal prompt, fetch the table rows.

    aws athena start-query-execution --query-string "SELECT * FROM test_db.test_table LIMIT 5;" --result-configuration "OutputLocation=s3://query-results-bucket/ --region ${REGION}"

    Options:

    • –result-configuration : Configuration parameters for query result.

    • OutputLocation : Location of S3 bucket in which we can store executed query results. The OutputLocation should be in the same location as mentioned in the query above (Example: query-results-bucket)

    The system returns a QueryExecutionId.

  2. Read the result of the query using the QueryExecutionId.

    aws athena get-query-results --query-execution-id "<query_execution_id>" --region ${REGION}

    It shows the result of your query in JSON format.

Access Athena with SQL workbench

Access Athena with SQL workbench on Privacera Platform

  1. Download the driver for your JDK and JDBC data standards version:

  2. Add the driver in the SQL Workbench, and connect to AWS Athena.

    1. Start SQL Workbench.

    2. Go to File menu and click Manage Drivers.

    3. On the Manage Driver window, click page icon on the upper right corner to create the Athena driver.

    4. In the Name field, enter the preferred name.

    5. Click the folder icon next to the Library text box to browse the downloaded Athena Simba JDBC driver.

      When you add driver to the Library, the classname appears automatically.

    6. You can provide Sample URL and click the OK button. For example the sample URL is jdbc:awsathena://AwsRegion=us-east-1;.

    7. Go to File menu and click Connect Window.

    8. Click page icon on the upper right corner to create a new connection.

    9. In the Driver field, select previously created Athena Driver and add the following details:

      • URL: jdbc:awsathena://EndpointOverride=<dataserver-hostname>:<dataserver-port>;AwsRegion=us-east-1;UseResultsetStreaming=0

      • Username: Enter "privacera-access-key".

      • Password: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    10. Get the global-truststore.p12 from ~/privacera/privaera-manager/config/ssl/ folder.

    11. Click the Extended Properties button and add the following Java trustore properties:

      javax.net.ssl.trustStore=<path-to-global-truststore.p12-file>javax.net.ssl.trustStorePassword=<global-truststore-password>

    12. Select the Copy to System properties before connecting checkbox.

    13. Save the properties and then click the OK button.

Access Athena with SQL workbench on PrivaceraCloud

  1. Download the driver for your JDK and JDBC data standards version:

  2. Add the driver in the SQL Workbench, and connect to AWS Athena.

    1. Start SQL Workbench.

    2. Go to File menu and click Manage Drivers.

    3. On the Manage Driver window, click page icon on the upper right corner to create the Athena driver.

    4. In the Name field, enter the preferred name.

    5. Click the folder icon next to the Library text box to browse the downloaded Athena Simba JDBC driver.

      When you add driver to the Library, the classname appears automatically.

    6. You can provide Sample URL and click the OK button. For example the sample URL is jdbc:awsathena://AwsRegion=us-east-1;.

    7. Go to File menu and click Connect Window.

    8. Click page icon on the upper right corner to create a new connection.

    9. In the Driver field, select previously created Athena Driver and add the following details:

      • URL: jdbc:awsathena://EndpointOverride=<dataserver-hostname>:<dataserver-port>;AwsRegion=us-east-1;UseResultsetStreaming=0

      • Username: Enter "privacera-access-key".

      • Password: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    10. Click the OK button.

Access Athena with DBeaver

Access Athena with DBeaver on Privacera Platform

  1. Get the global-truststore.p12 from ~/privacera/privaera-manager/config/ssl/ folder.

  2. Open dbeaver.ini file and update the following properties:

    -Djavax.net.ssl.trustStore=<path-to-global-truststore.p12-file>-Djavax.net.ssl.trustStorePassword=<global-trustore-password>

  3. Connect to AWS Athena.

    1. Start DBeaver.

    2. On the dashboard, go to Database > New Database Connection.

    3. On the Connect to database page, select Athena and then click the Next button.

    4. In the Main tab, add in the following information in the appropriate fields:

      • Region: us-east-1

      • S3 Location: Enter your S3 location to store Athena query results.

      • Username: Enter "privacera-access-key".

      • Password: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    5. Click the Edit Driver Settings button. The Edit Driver 'Athena' window appears.

    6. Under Settings tab, add following information in the appropriate fields:

      • URL Template: jdbc:athena://AWSRegion={region};UseResultsetStreaming=0

      • Default Port: {dataserver-port}

    7. In the Driver Properties tab, under User Properties, add the following properties:

      EndpointOverride=<dataserver-hostname>:<dataserver-port>UseResultsetStreaming=0

    8. Click the OK button, and then click the Finish button.

Access Athena with DBeaver on PrivaceraCloud

  • Connect to AWS Athena.

    1. Start DBeaver.

    2. On the dashboard, go to Database > New Database Connection.

    3. On the Connect to database page, select Athena and then click the Next button.

    4. In the Main tab, enter in the following information in the appropriate fields:

      • Region: us-east-1

      • S3 Location: Enter your S3 location to store Athena query results.

      • Username: Enter "privacera-access-key".

      • Password: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    5. Click the Edit Driver Settings button. The Edit Driver 'Athena' window appears.

    6. Under Settings tab, add following information in the appropriate fields:

      • URL Template: jdbc:athena://AWSRegion={region};UseResultsetStreaming=0

      • Default Port: {dataserver-port}

    7. In the Driver Properties tab, under User Properties, add the following properties:

      EndpointOverride=<dataserver-hostname>:<dataserver-port>UseResultsetStreaming=0

    8. Click the OK button, and then click the Finish button.

Access Athena with Tableau

Access Athena with Tableau on Privacera Platform

  1. Download the driver for your JDK and JDBC data standards version:

  2. Copy the driver to the ~/Library/Tableau/Drivers location.

  3. Download the DataServer certificate:

    curl -v -k https://<dataserver-hostname>:<dataserver-port>/services/certificate -o ds.pem

  4. Add the DataServer certificate into the cacerts used by Tableau.

    cd /Applications/Tableau Desktop 2021.3.app/Contents/Plugins/jre/lib/security/
sudo keytool -importcert -keystore cacerts -alias dataserver -file ds.pem

  5. Create a configuration file for Athena.

    vi ~/Documents/My\ Tableau\ Repository/Datasources/athena.properties

  6. Set the following properties and save.

    AwsRegion=us-east-1EndpointOverride=<dataserver-hostname>:<dataserver-port>UseResultsetStreaming=0

  7. Connect to AWS Athena.

    1. Start Tableau.

    2. On the left navigation menu, click Amazon Athena.

    3. In the General tab, add the following information in the appropriate fields:

      • Server: DataServer IP address.

      • Port: DataServer Port

      • S3 Storage Directory: Enter your S3 location to store Athena query results.

      • Access Key ID: Enter "privacera-access-key".

      • Secret Access Key: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    4. Click the Sign In button.

Access Athena with Tableau on PrivaceraCloud

  1. Download the driver for your JDK and JDBC data standards version:

  2. Copy the driver to the ~/Library/Tableau/Drivers location.

  3. Create a configuration file for Athena.

    vi ~/Documents/My\ Tableau\ Repository/Datasources/athena.properties

  4. Set the following properties and save.

    AwsRegion=us-east-1EndpointOverride=<dataserver-hostname>:<dataserver-port>UseResultsetStreaming=0

  5. Connect to AWS Athena.

    1. Start Tableau.

    2. On the left navigation menu, click Amazon Athena.

    3. In the General tab, add the following information in the appropriate fields:

      • Server: DataServer IP address.

      • Port: DataServer Port

      • S3 Storage Directory: Enter your S3 location to store Athena query results.

      • Access Key ID: Enter "privacera-access-key".

      • Secret Access Key: Enter "privacera-secret-key".

      To learn more about generating the token, see Generate token, access key, and secret key on Privacera Platform under Token Generator section.

    4. Click the Sign In button.