SQL Database Import

Overview

Amity can import bulk data from SQL databases such as Amazon Redshift and Microsoft Azure SQL.

Getting Started

To get started, you need to do the following:

  1. Create a data source
  2. Provide access to the database 

Create a Data Source

Amity supports bulk data import of the following object types:

  • Accounts
  • Activities
  • People (participants)
  • Subscriptions 

A separate data source is required for Amity object type.

A data source can be a table or view. Amity does not have any restrictions on the name of the table or view. You can use any name you want.

Each data source must have a column named last_updated_utc. The column contains a timestamp representing when the record was last updated. The timestamp must be in the UTC timezone. The data type must be TIMESTAMP NOT NULL. For Azure SQL, the data type must be DATETIME NOT NULL.

The other columns in the data source vary depending on the Amity object type and data you want to import. See the file format tables in CSV Bulk Data Import article for more information.

For example, imagine you want Amity to import customer accounts from your application database. Let's assume that every account in your application has a name and unique ID as well as an optional description and website. The accounts are stored in a table called Accounts that likely has a schema similar to this:

CREATE TABLE Accounts(
  id          INT PRIMARY KEY NOT NULL,
  name        VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  website     VARCHAR(255),
  updated     TIMESTAMP NOT NULL
);

One of the easiest ways to create a data source is to create a view of the Accounts table.

CREATE VIEW AmityAccounts AS
SELECT id AS app_id, name, description, website, updated AS last_updated_utc
FROM Accounts; 

Notice that in the view, the id column is renamed to app_id and the updated column is renamed to last_updated_utc. That is necessary to match the columns that Amity will expect.

Provide Database Access

In order to access your SQL database, you must provide the following details to your Amity Customer Success Manager:

  1. Server hostname and port (if applicable)
  2. Database name
  3. User and password
  4. The names of the data sources for each Amity object type you want to import 

Amity can provide our IP address to your database administrators to facilitate network security whitelisting.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk