How to Create a MySQL 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 MySQL.
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 once it is created.
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 MySQL .
10] MySQL credential fields appear.
11] Enter the following information:
|
MySQL Host |
IP address of the host on which the database is running/hosted |
|
Port |
Port number on which the database can be accessed | Default : 3306 for MySQL |
|
Database Name |
Name of database from which data has to be fetched |
|
Username |
Username used to access the database |
|
Password |
Password used to access the database |
| NOTE: Only MySQL 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 MySQL database, enter a query into the Request to MySQL textbox.
| NOTE: The MySQL Smart Connector supports both basic and advanced READ queries like SELECT and CALL (this 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 campaign_name, calls, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%'", we would suggest you to update it that way : "SELECT campaign_name, call, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%' ORDER BY id. |
| 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. |