Skip to content

Using Athena with Data Access Server

Run Athena queries from the client.

Set Up JDBC Client#

  1. Download the Privacera JDBC Client.

  2. Create athena_jdbc.properties file with the below contents in the same directory of above jar file.

    AwsRegion=${REGION}
    # S3 Location to Store Query Results
    S3OutputLocation=
    # Uncomment this If you want to use default credentials from ~/.aws/credentials
    # And Comment UID and PWD Property
    # AwsCredentialsProviderClass=com.simba.athena.amazonaws.auth.DefaultAWSCredentialsProviderChain
    # Access Key Used to Hit Request
    UID=
    # Secret Key Used to Hit Request
    PWD=
    LogPath=/tmp/
    LogLevel=6
    ProxyHost=localhost
    #Use port 9191 for Dataserver V1
    ProxyPort=8181
    

    Use Privacera token if proxy mode enabled. - UID: Privacera access key
    - PWD: Privacera secrect key

  3. Execute the Curl command.

    curl -u ${portal_username}:${portal_password} http://${portal_host_ip}:6868/api/dataserver/cert?type=modifiedcacerts -o clientcacert
    

Create an 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 home page, click Access Management > Resource Policies.

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

  3. Enter the details.

    • Policy Name: athena_test_policy 

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

    • 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.) Note: We have excluded ssn column here.

    • Under Allow Conditions, click '+' icon and select the below:

      • User: User’s username to which you want to allow access.

      • Add Permission as: Select

  4. Click Save.

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_s3Add New Policy.

  2. Enter the 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, click '+' icon and select the below:

      • User: User’s username to which you want to allow access.

      • Add Permission as: read, metadata read, write, metadata write.

  3. Click Save.

Run the JDBC Client#

  1. Download Dataserver JKS file from Portal.

    curl -k -u padmin:${privacera-portal-password} http://${privacera-portal-host}:6868/api/dataserver/cert?type=dataserver_jks -o dataserver.jks
    
  2. Run JDBC test client.

    java -Djavax.net.ssl.trustStoreType=jks -Djavax.net.ssl.trustStore=${path-to-dataserver-jks} -Djavax.net.ssl.trustStorePassword=${dataserver-jks-password} -jar StandaloneAthenaJDBCTester.jar
    

    It will ask for your SQL queries to run.

Access Athena Data through the AWS CLI#

  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.

    Note

    The OutputLocation should be in the same location as mentioned in the query above (Example: query-results-bucket)

The system returns a QueryExecutionId.  

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

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

It shows the result of your query in JSON format.

Set up Tableau with Athena#

Set up the Athena Driver in Tableau#

  1. Download Athena Driver.

    1. JDK 7 - AthenaJDBC41_2.0.7

    2. JDK 8 - AthenaJDBC42_2.0.7

  2. Save the Amazon Athena JDBC jar in the \~/Library/Tableau/Drivers location.

  3. Create a configuration file for Athena.

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

  4. Set the following key value pairs in the file.

    • ProxyHost=${Dataserver_Host}

    • ProxyPort=9191

  5. Update Java trustStore to allow secure connection to Dataserver. You can take our trustStore and replace it at the client machine.

    curl -H "Authorization: Basic cHJpdmFjZXJhc2VydmljZTpSb2dlcjM2NQ==" http://<portal_host>:<portal_port>/api/dataserver/cert?type=modifiedcacerts -o modifiedcacerts
    sudo cp modifiedcacerts /Library/Internet\ Plug-Ins/JavaAppletPlugin.plugin/Contents/Home/lib/security/cacerts
    
  6. Restart Tableau.

  7. Click Amazon Athena.

  8. Enter the Athena configuration details and click Sign In.

    • Server : athena.<region>.amazonaws.com For Example: athena.us-east-1.amazonaws.com

    • Port: 443

    • S3 Staging Directory: S3 Location where you want to store your Athena queries results. For Example: s3://athena-results-bucket/

    • Access Key ID: Access Key which you have obtained from Privacera Portal.

    • Secret Access Key: Secret Key which you have obtained from Privacera Portal.

Athena Tableau Use Case#

Note: Queries with ending “;” will not work properly.

  1. Create an S3 policy for the Athena results storage bucket.

Glue Policy#

Create a Glue policy with these settings:

  • Policy Name: Tableau DB Access

  • Database: test_db

  • Table: *

    Note: Put * here because when Tableau requests DB tables, it required all the tables.

  • User: ${user}

  • Permission: GetTables

Access Athena Through Tableau#

  1. Go to Tableau.

  2. Select AWSDataCatalog from the Catalog dropdown options.

  3. Select the Database on which you want to perform operations. If you have permission to view the tables of database then it will list tables from selected database.

  4. You can click on the icon at the right of Table Name to view the data from table. Or You can click on New Custom SQL and write a SQL query to run.

    1. Run a select query.

      SELECT * FROM test_db.test_table LIMIT 5

    2. Click Preview Results.


Last update: July 23, 2021