Skip to main content

Connecting DBeaver to the Analytics account

Only use a Hackney managed device

Only follow this guide on a laptop issued and managed by Hackney Council. Do not set it up on a personal device.

When you run a query in DBeaver, the results are downloaded to your device. They no longer stay inside AWS. This includes any personal or sensitive data in the results. Handle query results in line with Hackney's data protection and information security policies. Do not copy or export them to personal storage.

Objective​

Query data in the Analytics account from DBeaver, a desktop SQL client, rather than the Athena query editor in the AWS console.

Intended audience​

  • Data Analyst

Before you start​

You need:

  • Access to the Analytics account. When you can see the Analytics account in the AWS access portal, you have access. If you cannot see it, ask the Data Platform team.
  • Your team's Athena workgroup name. It is dap- followed by your team name, for example dap-data-insight or dap-nec-housing.
  • Permission to install software on your laptop, or help from IT to install it.

How it works​

DBeaver does not log in to AWS itself. Instead, you set up an AWS profile on your laptop. The profile records which AWS account and role to use. You log in through the AWS CLI, and DBeaver uses that login when it runs queries.

You do steps 1 to 4 once. After that, you only need to log in again when your session expires (see Day-to-day use).

1. Install the AWS CLI​

The AWS CLI is a command-line tool from AWS. You use it to create the profile and to log in.

  1. Download the AWS CLI version 2 installer for your operating system from the AWS CLI install page.

    • Windows: download and run the .msi installer.
    • macOS: download and run the .pkg installer.

  2. Open a new terminal window.

    • Windows: open the Start menu, type PowerShell and open Windows PowerShell.
    • macOS: open Terminal from Applications > Utilities.

  3. Check that the CLI is installed:

    aws --version

    You should see a version that starts with aws-cli/2.

PowerShell Terminal session showing the AWS version aws-cli/2.36.32

2. Find your account and role in the AWS access portal​

  1. Log in to your Hackney Google account in your browser.
  2. Open the AWS access portal.
  3. Expand the Analytics account (account ID 577817260960).
  4. Note the name of the role listed under it, for example data-insight-data-analyst. You will choose this role in the next step.

AWS start page demonstrating where to find the roles you have available for the Analytics account

3. Create the AWS profile​

  1. In your terminal, run:

    aws configure sso

  2. Answer each question as follows. Press Enter after each answer.

    QuestionAnswer
    SSO session name (Recommended)hackney
    SSO start URL [None]https://hackney.awsapps.com/start
    SSO region [None]eu-west-1
    SSO registration scopes [sso:account:access]Press Enter to accept the default

  3. Your browser opens and asks you to allow the AWS CLI to access your data. Select Allow access.

Browser page asking you to allow access for the AWS CLI

  1. Go back to the terminal. If you have access to more than one account, the CLI lists them. Use the arrow keys to select the Analytics account (577817260960) and press Enter.

  2. If you have more than one role in that account, select your team role, for example data-insight-data-analyst.

  3. Answer the remaining questions:

    QuestionAnswer
    Default client Region [None]eu-west-2
    CLI default output format [None]json
    Profile nameanalytics or some other descriptive name

  4. Check that the profile works:

    aws sts get-caller-identity --profile analytics

    The output includes "Account": "577817260960" and your role name in the ARN value.

The CLI saves the profile in a file called config:

  • Windows: C:\Users\<your username>\.aws\config
  • macOS: ~/.aws/config

It looks like this. Your sso_role_name will match your team.

[profile analytics]
sso_session = hackney
sso_account_id = 577817260960
sso_role_name = data-insight-data-analyst
region = eu-west-2
output = json

[sso-session hackney]
sso_start_url = https://hackney.awsapps.com/start
sso_region = eu-west-1
sso_registration_scopes = sso:account:access

If you prefer, you can create this file in a text editor instead of running aws configure sso. Then run aws sso login --profile analytics to log in.

4. Set up DBeaver​

Download and install DBeaver Community if you do not have it.

Why not use the Athena connection type?​

The Analytics account's team workgroups store query results in Athena managed storage, not in an S3 bucket that you choose. DBeaver's built-in Athena connection type expects you to give it an S3 results location, and fails without one. To get around this, you copy the Athena driver and use it as a generic JDBC driver. It is still the official AWS Athena JDBC driver, but DBeaver no longer asks for a results location.

Create the driver​

  1. In DBeaver, select Database > Driver Manager.
  2. Select Athena in the list, then select Copy.

Driver Manager with the Athena driver selected and the Copy button visible. 3. In the driver settings:

  • Set Driver Name to Athena Managed Storage.
  • Set Driver Type to Generic.
  • Leave the other settings and the Libraries tab as they are.

Driver settings for &quot;Athena Managed Storage&quot; with Driver Type set to Generic.

  1. Select OK, then close the Driver Manager.

Create the connection​

  1. Select Database > New Database Connection.

  2. Search for Athena Managed Storage, select it, then select Next.

  3. In JDBC URL, enter the following. Replace dap-data-insight with your team's workgroup and if you set your profile name to a value other than analytics when setting up your AWS profile then that value also needs to be replaced.

    jdbc:athena://Region=eu-west-2;Workgroup=dap-data-insight;Catalog=AwsDataCatalog;CredentialsProvider=ProfileCredentials;ProfileName=analytics;

  4. Leave Username and Password empty.

New connection window with the JDBC URL filled in.

  1. Select Test Connection. If DBeaver asks to download driver files, select Download.
  2. When the test succeeds, select Finish.

The connection appears in the Database Navigator. Expand it to see the databases you have access to.

Database Navigator showing the new connection expanded, with databases listed.

5. Run a query​

  1. Right-click the connection and select SQL Editor > Open SQL script.

  2. Enter a query, for example:

    SELECT * FROM "<database name>"."<table name>" LIMIT 10;

  3. Press Ctrl+Enter (Cmd+Enter on macOS) to run it.

Day-to-day use​

Your AWS login expires after a few hours. When it expires, DBeaver shows an error about expired or missing credentials. To log in again:

  1. Open a terminal and run:

    aws sso login --profile analytics <or the profile name you chose>

  2. Select Allow in the browser.

  3. In DBeaver, right-click the connection and select Invalidate/Reconnect.

Troubleshooting​

ProblemWhat to do
aws is not recognisedClose and reopen your terminal after installing the AWS CLI. If it still fails, restart your laptop.
The Analytics account is not listed in aws configure ssoCheck that you can see it in the AWS access portal. If you cannot, ask the Data Analytics Platform team for access.
DBeaver says the token has expired or the profile cannot be loadedRun aws sso login --profile analytics, then reconnect in DBeaver.
DBeaver asks for an S3 output locationYou are using the built-in Athena connection type. Create the connection with the Athena Managed Storage driver instead.
Access denied on the workgroupCheck the Workgroup value in the JDBC URL matches your team's workgroup. Your role can only use its own team's workgroup.
You cannot see a database or tableYour team does not have access to it. Ask the Data Analytics Platform team.