Before you begin: The PostgreSQL Smart Connector enables a new source of data in the system. Data is retrieved based on a predefined SQL query that works similar to a pgAdmin SQL client, but allows only the data read operation.
How to Create a PostgreSQL Smart Connector
A Smart Connector is way of pulling data into TapClicks that doesn't already have an existing integration into the TapClicks platform. One of the delivery types available for Smart Connectors is PostgreSQL.
1] On the left side menu, under Administration, click Smart Connector.
2] The Smart Connector page appears. Click the "+".
3] The New Smart Connector modal appears.
4] Enter a name for the Smart Connector in the Name field.
5] Enter a drilldown name for the Smart Connector in the Drilldown field. Think of the Drilldown as the top-level way of organizing your Smart Connectors.
6] Select the Is active? checkbox to make the Smart Connector active.
7] From the Origin of Data dropdown, optionally select either Internal System or Third-party Software.
8] Optionally choose an icon and icon color to represent your Smart Connector.
9] From the Delivery type dropdown, select PostgreSQL .
10] PostgreSQL credential fields appear.
11] Enter the following information:
|
PostgreSQL Host |
IP address of the host on which the database is running/hosted |
|
PostgreSQL Port |
Port number on which the database can be accessed | Default : 5432 |
|
PostgreSQL database |
Name of database from which data has to be fetched |
|
PostgreSQL username |
Username used to access the database |
|
PostgreSQL password |
Password used to access the database |
| NOTE: Only PostgreSQL databases for which external connections over TCP/IP are allowed can be connected. The server on which the database rests must allow external connections. |
12] Check if the database is accessible by clicking the Test Connection button. If the connection is established, the check sign on the test connection turns green. If the connection is unsuccessful, an error message appears at the top of the screen.
| NOTE: Common causes of the error message are missing required fields (those with an asterisk) and insufficient permissions to access the host on which the database resides. |
13] Click the Save Changes button at the bottom of the screen.
14] The field to create a query request appears.
15] To retrieve the desired data from the remote PostgreSQL database, enter a query into the Request to PostgreSQL textbox.
| NOTE: The PostgreSQL Smart Connector supports both basic and advanced READ queries using the SELECT statement. (The CALL procedure is permitted, but please inform your Customer Success Manager if you intend to use this command to ensure that the Smart Connector meets this advanced use case). The commands INSERT, UPDATE and DELETE are not supported.
In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT command at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement an ORDER BY command to ensure the data integrity. Example : SELECT * FROM customer_value WHERE (effective_to_date BETWEEN '%START_DATE%' AND '%END_DATE%') OR 1=1 ORDER BY effective_to_date. |
| NOTE: Only columns selected in the query will be fetched by the Smart Connector. Therefore, you need to make sure you are including all necessary columns in the query. |
16] Click the Load Sample button.
| NOTE: Data types of all columns are detected automatically. It is recommended that you check all the data types before saving the configuration to avoid incorrect data types. See the Field Type column in the image below.
Auto detection may select a column with a number based on what is in that first row of data, but that might not be how you intend the field to be used. For example, Id is data type Number since it comes through as a 3 in the sample file, but it should be stored as Text since no mathematical operations (such as Sum/Average) are expected on an Id. |
17] Sample file data appear.
NOTE: The frequency of historic and daily fetches is as follows:
|
| NOTE: If certain fields in the query need to be modified (unique fields, dates, and assigning configurations), you will need to delete the data loaded to date through the Smart Connector before making the modifications. This is done through the Delete, which is placed next to the Smart Connector names in the Smart Connector list.
However, you can include additional fields in the query similar to if you were to add a new field from a .csv file to a Smart Connector. You can do this without deleting the previously loaded data. Standard partial/full fetch applies to existing and new assigning in regards to filling the database with these new fields. This functionality is in line with all other Connection Type/Delivery Type options in the system today. After creating the Smart Connector, you will have to map your Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts pouring into the system. |