For each database you want to import from in the Database Import, you'll need to go through a one-time process of adding that database's connection information and username credentials to Civis.
There are two methods of adding a new database to Platform to be used in Imports and Exports. The first method should work for all database types and the second method should work for all database types except BigQuery. The second method may be more convenient as there are fewer steps.
Supported Databases
Platform supports the following database types for imports / exports. Additional database types may work if they support JDBC connections.
- Microsoft SQL Server
- MySQL
- Oracle
- Postgres
- Redshift
- Snowflake
- BigQuery
JDBC Connection URLs
A JDBC URL is required to set up the database connection. It contains information about the host, port, and database name to connect to and sometimes optional connection parameters.
The format of the URL will differ depending on the type of database you are connecting to. Here are example JDBC connection URLs for database types supported by Platform:
- jdbc:mysql://your.host.com:port/yourDbName
- jdbc:postgresql://your.host.com:port/yourDbName
- jdbc:bigquery://www.googleapis.com/bigquery/v2;ProjectId=yourProjectId
- jdbc:redshift://your.host.com:port/yourDbName
- jdbc:sqlserver://your.host.com:port;databaseName=yourDbName
- jdbc:oracle:thin:@//your.host.com:port/yourDbName
- jdbc:snowflake://your.host.snowflakecomputing.com:port/?db=yourDbName
If you are unsure what your JDBC URL should be, contact your database administrator or consult the documentation for your database.
Create a new Remote Host and Database Credential separately
- Create the Remote Host
- Navigate to the Remote Host Page
- Click your initials in the top right corner then “Remote Hosts” in the menu that appears
- Click “+ New Remote Host” in the top right corner
- Click “Database Connection through JDBC”
- Enter a Name for your Remote Host and the connection URL and a description if desired then click “Save”
- Navigate to the Remote Host Page
- Create the Credential
- For BigQuery see Adding a BigQuery Database Credential, and make sure to follow the instructions for adding a credential using a service account.
- For all other database types, follow the instructions below.
- Navigate to the Credentials Page
- Click “Admin” in the top navigation bar then “Credentials” in the menu that appears
- Click “Create Credential”
- Fill out these fields
- Name: give your credential a name
- Type: Database
- Remote Host: The Remote Host created above
- Username: the database username
- Password: the database user’s password
- Click “Save”
- Navigate to the Credentials Page
Create a new Remote Host and Database Credential Together
-
Navigate to the Database Imports page from the Data menu on the top navigation panel.
- Click "New Database"
-
Fill out these fields in the "Add Source" Panel:
- Username - The username of the account Civis will use to access the source data.
- Password - The password of the account Civis will use to access the source data.
- Optionally, you may test the connection by clicking “Test”
- Click “Add”
Considerations
To add databases, the following must first be configured or resolved:
-
Database access: Civis must have access to the database as an external system. If you only plan to import data from your database via the Database Import job, the database administrator will need to allowlist the following IP addresses:
- 35.171.100.200/29 (note this is a CIDR range)
-
If you also plan to write a custom script (Container, R, or Python script) that will connect to the database/server, the IPs below will also need to be allowlisted, and you will need to ask us to enable a NAT for your organization:
- 18.207.171.255
- 34.198.242.0
- 34.192.122.37
- 34.237.119.77
- 35.153.38.203
-
52.202.119.255
-
Client VPN Server
-
52.54.104.84
-
52.54.104.84
-
SSH Tunnel
-
54.164.3.51
-
54.164.3.51
-
Tableau Server: If you plan to publish reports to the Civis Tableau Server using data in your external database you will need to allow the following IP address
- 52.72.40.141
- Credentials: Civis only requires "read-only" credentials. Civis uses "SELECT" statements to sync tables and needs those privileges to those tables. Any system imposed limits on how much data a user can select in a query and/or session needs to be at a level commensurate with the size of the tables and the refresh schedules of the project.
- If your database uses SSL Key Pairs or a Server Certificate for authentication, additional configuration may be required. For guidance on adding the relevant authentication details, please view Adding SSL Key Pairs to DBSyncs or Adding a Server Certificate to Civis for an External Database Sync.
Database maintenance schedule: Civis database imports should be scheduled around any known maintenance or downtime.
Comments
0 comments
Please sign in to leave a comment.