Overview
KORE Data Warehouse & Analytics utilizes Amazon Redshift for data storage. This provides a fast and reliable back end which can be queried using SQL. Redshift SQL is based on PostgreSQL—see Amazon's SQL documentation to learn about the differences.
Below, we cover the basics of connecting to DWA using DBeaver Community Edition, a free and open-source database tool. If you prefer, you may use any other tool that supports Redshift (such as DBeaver Enterprise Edition, PowerBI, or Aginity). If you use Tableau Desktop or Tableau Server, you can connect it to your KORE Data Warehouse in the same way.
For additional help, see Amazon's Connecting to Amazon Redshift guide.
Requirements
No matter which database tool you choose, you'll need the following details to connect. These example values are typical, but some may vary. Your KORE Success Manager can provide specific details for your organization.
- Host: [org-name].data.dwa.koresoftware.com
- Port: 5439
- Database: [org-name]
- Username: [org-name]dwa
- Password: ********
- SSL enabled: yes
- SSL mode: verify-ca (if the tool allows you to specify this)
Connect Tableau to DWA
Follow Tableau's instructions for connecting to your data using Tableau Desktop or Server using their Amazon Redshift connector. Be sure to tick the Require SSL checkbox. You do not need to enter any custom driver parameters.
Create a new connection in DBeaver
In DBeaver, click the "New Database Connection" icon in the toolbar (or select Database > New Database Connection in the menu.) This opens a window for configuring the connection. First, select "Redshift" from the list (you can use the search bar to make this easier) and click the Next button.
Next, enter the connection details in the Connection Settings main tab. Do not click the Finish button yet.
Finally, open the SSL tab and tick the "Use SSL" box. For SSL mode, select "verify-ca" (certificate authority). Click the Finish button to save the connection.
Open the connection in DBeaver
After creating the connection, it will be listed in the Database Navigator panel. Confirm that the lock icon is shown next to the connection's name to ensure SSL is enabled for security. Then you can open the connection by double-clicking its name or by using the Connect icon. Once the connection is successful (indicated by a green checkmark), you can click the arrow symbols to navigate the database and examine its schemas.
To try out our sample queries or write your own, select Open SQL console from the SQL Editor menu.
For more about how to use DBeaver, refer to the DBeaver documentation.
ODBC and JDBC connection strings
Advanced users may use ODBC or JDBC to connect to DWA. These are example connection strings:
odbc_conn = "Driver={Amazon Redshift (x64)};
Server=dwa-XXXX.XXXXXX.us-west-2.redshift.amazonaws.com;
Database=XXXX; Port=5439; sslmode=verify-ca; UID=XXXXXX; PWD=XXXXXXXX"
jdbc_conn =
"jdbc:redshift://dwa-XXXX.XXXXXX.us-west-2.redshift.amazonaws.com:5439/XXXX:" +
"user=XXXXXX;password=XXXXXXXX&sslmode=verify-ca"