RT Guide: Chapter 6, Connecting to the Data Warehouse Databases

Owned by Brandon Fitzpatrick
Last updated: 02 Sept 2022 by Christine Fitzgerald (Unlicensed)
5 min read

DBeaver

The SDC’s primary tool for data exploration of the data warehouse databases is DBeaver. You can find DBeaver’s documentation here: https://dbeaver.com/docs/wiki/ General User Guide

Depending on the datasets you have access to (eg. for Waze data - Redshift), not all of the following will be applicable to your research. A member of the Enablement Team should have included data warehouse connection information, like Host and Database names, in your welcome email. If you have any questions please contact them via the Service Desk or email sdc-support@dot.gov.

Driver Manager

https://dbeaver.com/docs/wiki/Database-drivers/ Database drivers

New Database Connection

https://dbeaver.com/docs/wiki/Create-Connection/ Create Connection

To make it easier for you, find Host and Database information here: C:\Users\Public\JDBC Drivers\Database Connections.yaml

Amazon Redshift

Amazon Redshift is based on PostgreSQL. Amazon Redshift and PostgreSQL have a number of important differences. You can read about them here:

You might also find and helpful.

Driver Manager

  1. In the DBeaver menu, open Database → Driver Manager

  2. In the Driver Manager dialog box, select AWS → Redshift, then click Edit…

  3. In Edit Driver ‘Redshift' dialog box, click the Libraries tab

  4. Select com.amazon.redshift:redshift-jdbc42:RELEASE and click Delete

  5. Click Add Folder

  6. In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\Redshift and click Select Folder

  7. Click Find Class

  8. From the Driver Class dropdown, select com.amazon.redshift.jdbc42.Driver

  9. Click OK

  10. Click Close

New Database Connection

  1. In the DBeaver toolbar, click the New Database Connection button

  2. In the Connect to a database/Select your database dialog box, search for and select Redshift

  3. Click Next

  4. In the Connect to a database/Connection Settings dialog box, on the Main tab, enter the Host/Instance, Port, Database, Username and Password that was send to you in the welcome email. (To make it easier for you, find Host and Database information here: C:\Users\Public\JDBC Drivers\Database Connections.yaml)

  5. Click Test Connection

  6. You should see a new Connection test dialog box giving information about the server. Similar to the following.

  7. If you get an error, recheck the information you entered on step 4

  8. If you continue to get an error, please contact the Enablement Team via the Service Desk.

Apache Hive

Hive supports ANSI SQL. You may find their Language Manual useful.

Driver Manager

  1. In the DBeaver menu, open Database → Driver Manager

  2. In the Driver Manager dialog box, select Hadoop → Apache Hive, then click Edit…

  3. In Edit Driver ‘Redshift' dialog box, click the Libraries tab

  4. Select https://github.com/timveil/hive-jdbc-uber-jar/releases/download/v1.9-2.6.5/hive-jdbc-uber-2.6.5.0-292.jar and click Delete

  5. Click Add Folder

  6. In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\Hive and click Select Folder

  7. Click Find Class

  8. From the Driver Class dropdown, select com.amazon.hive.jdbc42.HS2Driver

  9. Click OK

  10. Click Close

New Database Connection

  1. In the DBeaver toolbar, click the New Database Connection button

  2. In the Connect to a database/Select your database dialog box, search for and select Hive

  3. Click Next

  4. In the Connect to a database/Connection Settings dialog box, on the Main tab, enter the Host, Port, Database/Schema, Username and Password that was send to you in the welcome email. (To make it easier for you, find Host and Database information here: C:\Users\Public\JDBC Drivers\Database Connections.yaml)

  5. In the Connect to a database/Connection Settings dialog box, on the Driver properties tab, for AuthMech enter a value of 3
    Login will fail without AuthMech = 3

  6. Click Test Connection

  7. You should see a new Connection test dialog box giving information about the server. Similar to the following.

  8. If you get an error, recheck the information you entered on step 4

  9. If you continue to get an error, please contact the Enablement Team via the Service Desk.

PostgreSQL

PostgreSQL is a relational database management system emphasizing extensibility and SQL compliance. You can find their documentation here:

Driver Manager

  1. In the DBeaver menu, open Database → Driver Manager

  2. In the Driver Manager dialog box, select PostgreSQL, then click Edit…

  3. In Edit Driver ‘Redshift' dialog box, click the Libraries tab

  4. Select and Delete the following
    org.postgresql:postgresql:RELEASE
    net.postgis:postgis-jdbc:RELEASE
    net.postgis:postgis-geometry:RELEASE

  5. Click Add Folder

  6. In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\PostgreSQL and click Select Folder

  7. Click Find Class

  8. From the Driver Class dropdown, select org.postgresql.Driver

  9. Click OK

  10. Click Close

New Database Connection

  1. In the DBeaver toolbar, click the New Database Connection button

  2. In the Connect to a database/Select your database dialog box, search for and select PostgreSQL

  3. Click Next

  4. In the Connect to a database/Connection Settings dialog box, on the Main tab, enter the Host, Port, Database, Username and Password that was send to you in the welcome email. (To make it easier for you, find Host and Database information here: C:\Users\Public\JDBC Drivers\Database Connections.yaml)

  5. Click Test Connection

  6. You should see a new Connection test dialog box giving information about the server. Similar to the following.

  7. If you get an error, recheck the information you entered on step 4

  8. If you continue to get an error, please contact the Enablement Team via the Service Desk.

Microsoft SQL Server

SQL Server uses Microsoft’s Transact-SQL (T-SQL). You can find reference topics here:

Driver Manager

  1. In the DBeaver menu, open Database → Driver Manager

  2. In the Driver Manager dialog box, select MS SQL Server → SQL Server, then click Edit…

  3. In Edit Driver ‘Redshift' dialog box, click the Libraries tab

  4. Select and Delete the following
    com.microsoft.sqlserver:mssql-jdbc:RELEASE
    com.microsoft.sqlserver:mssql-jdbc_auth:RELEASE

  5. Click Add Folder

  6. In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\MicrosoftSQLServer\enu and click Select Folder

  7. Click Find Class

  8. From the Driver Class dropdown, select com.microsoft.sqlserver.jdbc.SQLServerDriver
    There may be multiple of the same driver class, select any one of them

  9. Click OK

  10. Click Close

New Database Connection

  1. In the DBeaver toolbar, click the New Database Connection button

  2. In the Connect to a database/Select your database dialog box, search for and select SQL Server

  3. Click Next

  4. In the Connect to a database/Connection Settings dialog box, on the Main tab, enter the Host, Port, Database/Schema, Username and Password that was send to you in the welcome email. (To make it easier for you, find Host and Database information here: C:\Users\Public\JDBC Drivers\Database Connections.yaml)

  5. Click Test Connection

  6. You should see a new Connection test dialog box giving information about the server. Similar to the following.

  7. If you get an error, recheck the information you entered on step 4

  8. If you continue to get an error, please contact the Enablement Team via the Service Desk.