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.
https://dbeaver.com/docs/wiki/Database-drivers/ Database drivers
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 is based on PostgreSQL. Amazon Redshift and PostgreSQL have a number of important differences. You can read about them here: https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html
You might also find https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_SQLCommandRef.html and https://www.postgresql.org/docs/current/sql.html helpful.
In the DBeaver menu, open Database → Driver Manager
In the Driver Manager dialog box, select AWS → Redshift, then click Edit…
In Edit Driver ‘Redshift' dialog box, click the Libraries tab
Select com.amazon.redshift:redshift-jdbc42:RELEASE
and click Delete
Click Add Folder
In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\Redshift
and click Select Folder
Click Find Class
From the Driver Class dropdown, select com.amazon.redshift.jdbc42.Driver
Click OK
Click Close
In the DBeaver toolbar, click the New Database Connection button
In the Connect to a database/Select your database dialog box, search for and select Redshift
Click Next
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
)
Click Test Connection
You should see a new Connection test dialog box giving information about the server. Similar to the following.
If you get an error, recheck the information you entered on step 4
If you continue to get an error, please contact the Enablement Team via the Service Desk.
Hive supports ANSI SQL. You may find their Language Manual useful.
In the DBeaver menu, open Database → Driver Manager
In the Driver Manager dialog box, select Hadoop → Apache Hive, then click Edit…
In Edit Driver ‘Redshift' dialog box, click the Libraries tab
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
Click Add Folder
In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\Hive
and click Select Folder
Click Find Class
From the Driver Class dropdown, select com.amazon.hive.jdbc42.HS2Driver
Click OK
Click Close
In the DBeaver toolbar, click the New Database Connection button
In the Connect to a database/Select your database dialog box, search for and select Hive
Click Next
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
)
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
Click Test Connection
You should see a new Connection test dialog box giving information about the server. Similar to the following.
If you get an error, recheck the information you entered on step 4
If you continue to get an error, please contact the Enablement Team via the Service Desk.
PostgreSQL is a relational database management system emphasizing extensibility and SQL compliance. You can find their documentation here: https://www.postgresql.org/docs/current/sql.html
In the DBeaver menu, open Database → Driver Manager
In the Driver Manager dialog box, select PostgreSQL, then click Edit…
In Edit Driver ‘Redshift' dialog box, click the Libraries tab
Select and Delete the followingorg.postgresql:postgresql:RELEASE
net.postgis:postgis-jdbc:RELEASE
net.postgis:postgis-geometry:RELEASE
Click Add Folder
In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\PostgreSQL
and click Select Folder
Click Find Class
From the Driver Class dropdown, select org.postgresql.Driver
Click OK
Click Close
In the DBeaver toolbar, click the New Database Connection button
In the Connect to a database/Select your database dialog box, search for and select PostgreSQL
Click Next
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
)
Click Test Connection
You should see a new Connection test dialog box giving information about the server. Similar to the following.
If you get an error, recheck the information you entered on step 4
If you continue to get an error, please contact the Enablement Team via the Service Desk.
SQL Server uses Microsoft’s Transact-SQL (T-SQL). You can find reference topics here: https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15
In the DBeaver menu, open Database → Driver Manager
In the Driver Manager dialog box, select MS SQL Server → SQL Server, then click Edit…
In Edit Driver ‘Redshift' dialog box, click the Libraries tab
Select and Delete the followingcom.microsoft.sqlserver:mssql-jdbc:RELEASE
com.microsoft.sqlserver:mssql-jdbc_auth:RELEASE
Click Add Folder
In the Open driver directory dialog box, navigate to: C:\Users\Public\JDBC Drivers\MicrosoftSQLServer\enu
and click Select Folder
Click Find Class
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
Click OK
Click Close
In the DBeaver toolbar, click the New Database Connection button
In the Connect to a database/Select your database dialog box, search for and select SQL Server
Click Next
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
)
Click Test Connection
You should see a new Connection test dialog box giving information about the server. Similar to the following.
If you get an error, recheck the information you entered on step 4
If you continue to get an error, please contact the Enablement Team via the Service Desk.