Connect to Abacus.AI Using JDBC/ODBC

The Abacus.AI platform provides an JDBC/ODBC connector as one of the options for you to attach datasets to your project from your own databases.

Connector Setup

• Azure Databricks
• AWS Redshift
• Oracle
• MS SQL

In order to connect to your database, the following information regarding the database is required:

• Driver: The specific driver required to connect to the database depending upon the type of database.
• Authentication Mechanism: Based on the authentication method associated with the database, authentication information might be required for configuration. It can be a personal access token, username and password, or no authentication at all. In this example, we'll be using a personal access token.
• Database Server URL/IP: The server hostname's IP address or URL.
• Database Name: The name of the specific database.
• Database Port: The port at which the database server is running.
• HTTP Path: The HTTP path to the specific cluster the database belongs to.
• Authentication Methods: The method by which the server requires user authentication. We support the following methods based on the specific driver:
  1. Username and Password
     • Database Username: The username assigned to access the database. If a personal access token or no authentication is being used, then this can be disregarded.
     • Database Password: The password used to access the database. If no authentication method is being used, then this can be disregarded. If a personal access token is being used, then that will act as the password for configuration.
  2. Personal Access Token
     • The personal access token to be used in database authentication if required by the selected authentication mechanism.

Steps to connect to Azure Databricks (JDBC)

1. Here's how to configure a connection to data hosted on the Azure Databricks platform. The first step is to set up authentication for the database. For this, navigate to "User Settings" and follow instructions to generate a personal access token within the Databricks platform:

2. Navigate to the "Compute" tab in order to extract the required information from the respective cluster. Click on "Advanced options" and "JDBC" to get the information.

3. Go to the Abacus.AI Connected Services Dashboard. Click on the "Add New Connector" button at the top right corner of the page.

4. To set up a new JDBC connector, click on the ODBC/JDBC option.

5. Fill out all the configuration information described above in this document and hit save, making sure to select the Databricks JDBC driver.

6. Click on the "Verify" button and wait for the connector to get verified. If there are any errors or exceptions, follow the message to troubleshoot your configuration.

7. Rename, edit configuration, or delete your connector(s) as you see fit from the Abacus.AI Connected Services Dashboard by clicking on the respective buttons.

Steps to connect to AWS Redshift (ODBC)

1. Navigate to the "Clusters" tab in order to extract the required information from the respective cluster. Click on "Properties" to get the information.

2. Go to the Abacus.AI Connected Services Dashboard. Click on the "Add New Connector" button at the top right corner of the page.

3. To set up a new ODBC connector, click on the ODBC/JDBC option.

4. Fill out all the configuration information described above in this document and hit save, making sure to select the Redshift ODBC driver.

5. Click on the "Verify" button and wait for the connector to get verified. If there are any errors or exceptions, follow the message to troubleshoot your configuration.

6. Rename, edit configuration, or delete your connector(s) as you see fit from the Abacus.AI Connected Services Dashboard by clicking on the respective buttons.

Whitelist an IP Address for an Amazon Redshift Cluster

To whitelist an IP address for your Amazon Redshift cluster, you need to modify the associated security group to allow inbound traffic from that IP address. Follow these steps:

1. Log in to the AWS Management Console and navigate to the Amazon Redshift dashboard.
2. Find your cluster by looking for the cluster identifier.
3. Identify the VPC security groups associated with your cluster. You can find this information in the details pane of your selected cluster.
4. Navigate to the VPC Dashboard by either searching for "VPC" in the AWS Management Console or by going to the "Security" section in the Redshift dashboard and clicking on the link to the security group.
5. Select Security Groups from the navigation pane on the left side of the VPC Dashboard.
6. Find and select the security group that your Redshift cluster is using.
7. Go to the Inbound Rules tab for the security group.
8. Edit the inbound rules by clicking on the "Edit inbound rules" button.
9. Add a new rule with the following settings:
   • Type: Redshift (or Custom TCP if Redshift is not an option)
   • Protocol: TCP
   • Port Range: The port your Redshift cluster is listening on (default is 5439)
   • Source: Enter the IP address you want to whitelist, followed by /32 to indicate a single IP address
10. Save the rule by clicking on the "Save rules" button.

Steps to connect to Oracle (JDBC)

Oracle has a different approach to database organization compared to some other databases like MySQL or SQL Server. In Oracle, what is often referred to as a "database" in other systems is more akin to a "schema" in Oracle. To connect to an Oracle database schema using the Abacus.AI platform, you should use the schema name as the "database name" in the connection configuration. Here's how to do it:

1. Obtain the schema name that you have access to and wish to connect to.
2. When filling out the configuration information for the Oracle database connection on the Abacus.AI platform, use the schema name as the "Database Name".
3. Ensure that all other required information, such as the database server URL/IP, port, and authentication details, are correctly provided and that the Abacus.AI IP address is whitelisted.

Steps to connect to MS SQL (JDBC/ODBC)

1. Navigate to your SQL database page in Microsoft Azure:

2. Click on "Connection strings", then select ODBC and note the server name after tcp:<name>.database.windows.net:

3. Whitelist the Abacus.AI IPs. Please reach out to Abacus.AI support for the most up-to-date addresses:

4. While filling the JDBC/ODBC connection:
   1. Choose ODBC Driver 18.
   2. Fill in the server name ending with <server_name>.database.windows.net.
   3. Enter the database name.
   4. Use port 1433.
   5. Select 'Username and Password' as the Authentication Mechanism/
   6. Enter a username with to access the database.
   7. Enter the password for the username.
   8. Click Save