This connector is available only to customers on the Enterprise Edition.
The PostgreSQL Push connector allows you to send data to any PostgreSQL database. There are many Open Source and commercial tools built on top of this popular database. DataSift offers a method to map fields in interactions to custom database schemas.
DataSift can deliver interactions in a format of your choice straight to your PostgreSQL server. To use the PostgreSQL connector, you must run a PostgreSQL server connected to the internet. You can set it up on your own hardware or you can use one of the PostgreSQL-as-a-service offerings such as Amazon AWS RDS. It is your responsibility to ensure that your PostgreSQL server can handle the volume of data sent by DataSift.
Here are some guidelines to help you use PostgreSQL data destinations successfully with Push. Read the step-by-step guide to the API to learn how to work with DataSift's APIs.
Here's an overview of the steps you need to follow:
- Make sure you have PostgreSQL. It doesn't matter whether you run it locally, or have a remote server, or use a cloud-based PostgreSQL installation.
- Create a database and set up the tables you want to use.
- Create a .INI file to define the mappings between the data coming from DataSift and the tables and columns you have defined in your database.
- Configure your PostgreSQL Push connector in DataSift.
Setting up your database
Before you can use the PostgreSQL Push connector to send data to your database, you will need to create the tables you want to use.
- Set up the table(s) for storage.
- Add indexes as necessary.
- Make a note of the username, password, host domain of the database server, and port number.
- For security, you might want to configure PostgreSQL to use Classless Inter-Domain Routing (CIDR) for the Push IP addresses. If you encounter problems when you first attempt to connect to your PostgreSQL database from DataSift, it may be that your CIDR is blocking DataSift. A CIDR setting of 0.0.0.0/0 allows unlimited access from any IP address.
You are now ready to set up the PostgreSQL connector.
Creating a .INI mapping file
To use the PostgreSQL Push connector you will need to create a .INI file that defines the mapping from the JSON elements in interactions coming from DataSift to the tables and columns that you plan to use in your database.
Read our Writing .INI Files page to learn about .INI files.
Configuring Push for delivery to PostgreSQL
To enable delivery, first define a stream or a Historics query. Both return important details required for a Push subscription. A succesful stream definition returns a hash, a Historics query returns an id. You will need either (but not both) to set the value of the hash or historic_id parameters in a call to /push/create. You need to make a call to /push/get or /historics/get to obtain that information or you can use the DataSift dashboard.
Once you have the stream hash or the Historics id, you can supply that information to /push/create. In the example below we are making that call using curl, but you are free to use any programming language or tool.
curl -X POST 'https://api.datasift.com/v1.6/push/create' \ -d 'name=connectorpostgresql' \ -d 'hash=42d388f8b1db997faaf7dab487f11290' \ -d 'output_type=postgresql' \ -d 'output_params.host=db.example.com' \ -d 'output_params.port=3306' \ -d 'output_params.mapping=W2ludGVyYWN0aW9uXQ0KaW50ZXJhY3Rpb25faWQgPSBpbnRlcmFjdGlvbi5pZA0KY3JlYXRlZF9hdCA9IGludGVyYWN0aW9uLmNyZWF0ZWRfYXQgKGRhdGFfdHlwZTogZGF0ZXRpbWUsIHRyYW5zZm9ybTogZGF0ZXRpbWUpDQpjcmVhdGVkX2F0X3VuaXggPSBpbnRlcmFjdGlvbi5jcmVhdGVkX2F0IChkYXRhX3R5cGU6IGludGVnZXIsIHRyYW5zZm9ybTogdW5peHRpbWVzdGFtcCkNCnVzZXJuYW1lID0gaW50ZXJhY3Rpb24uYXV0aG9yLnVzZXJuYW1lIA0KaXNfcmV0d2VldCA9IHR3aXR0ZXIucmV0d2VldGVkLmlkIChkYXRhX3R5cGU6IGludGVnZXIsIHRyYW5zZm9ybTogZXhpc3RzKQ0KdHdpdHRlcl9sYW5nID0gdHdpdHRlci5sYW5nDQp0d2l0dGVyX2xhbmcgPSAgdHdpdHRlci5yZXR3ZWV0LmxhbmcNCmZpcnN0X3RhZyA9IGludGVyYWN0aW9uLnRhZ3MgKHRyYW5zZm9ybTogZmlyc3Qp' \ -d 'output_params.database=mydb' \ -d 'output_params.auth.username=dsdb' \ -d 'output_params.auth.password=dspassword' \ -H 'Authorization: datasift-user:your-datasift-api-key'
For more information, read the step-by-step guide to the API to learn how to use Push with DataSift's APIs.
When a call to /push/create is successful, you will receive a response that contains a Push subscription id. You will need that information to make successful calls to all other Push API endpoints (/push/delete, /push/stop, and others). You can retrieve the list of your subscription ids with a call to /push/get.
You should now check that the data is being delivered to your database. Log in to your PostgreSQL database and examine your tables.
When DataSift is able to connect and deliver interactions to this directory, it uses the tables and columns defined in your .INI file.
If there is a delay, this might be due to the fact that the stream has no data in it or there is a problem with your server's configuration. If you want more information, make a call to /push/log and check the value of the success field, if it is set to failure, check the value of the message field for clues. Also, make sure to make a call to /push/get and see if the response includes information about DataSift retrying to deliver data to your data delivery destination.
Be sure to examine the last_request and last_success times. If last_success is null we have not received any data, so we have not tried to send anything yet.
Please make sure that you watch your usage and add funds to your account when it is running low. Also, stop any subscriptions that are no longer needed otherwise you will be charged for their usage. There is no need to delete them. You can can have as many stopped subscriptions as you like without paying for them. Remember that any subscriptions that were paused automatically due to insufficient funds, will resume when you add funds to your account.
- Familiarize yourself with the output parameters (for example, the host name) you'll need to know when you send data to a PostgreSQL server.
|The name of the PostgreSQL host that DataSift will connect to; for instance: db.example.com.|
|The port that you want DataSift to use on your PostgreSQL server.|
The name of the database in which to insert the data.
|The full mapping file as a string. You can use base64 encoding if you want to.|
The username for authorization.
|The password for authorization.|
Data format delivered:
You have complete control over the database schema you use. You use a .INI file to specify the mapping of the elements in a DataSift JSON object to your table columns. One JSON object can map to one or more tables and to one or more rows in each table.