FAQ - Smart Connector
FAQ - Smart Connector
The MySQL connection using the Smart Connector enables a new source of data in the system. Data is fetched based on a predefined SQL query and would work similar to a MySQL workbench, allowing only data read operation. The requirements and functionality is similar to other Smart Connector Connection Type / Delivery Type options.
To Create a Smart Connector,
- Open TapClicks and in the left pane, click Administrator
- Click Smart Connector under Data Settings.
- Click the icon next to Smart Connector on the top of the screen.
- Enter a name and select MySQL from the Delivery type dropdown.
- After selecting the MySQL, the user will be prompted to connect to the database using credentials and other necessary information. Please enter the information as given below.
MySQL HostIP address of the host on which the database is running / hostedPortPort number on which the database can be accessed | Default : 3306 for MySQLDatabase NameName of database from which data has to be fetchedUsernameUsername used to access the databasePasswordPassword used to access the database
- Before creating an actual connection, check if the database is accessible by clicking test connection. Once the connection is established, the check sign on the test connection turns green.
- If the connection is unsuccessful, an error message appears on the screen. Some common things to check if this occurs are:
- Required Fields : Host, Username, Password, Database Name, Port Number
- Permissions : Check if you have necessary permissions to access the host on which the Database rests
- After you enter the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote MySQL Database. Smart Connectors support 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). Note: Only columns selected in the query will be fetched by the Smart Connector. Therefore, users need to make sure they are including all necessary columns in the query.
- The only commands that are not supported include are INSERT, UPDATE and DELETE. In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT 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 to ensure the data integrity. For 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
- Data types of all columns are detected automatically. It is recommended that users check all the data types before saving the configuration to avoid incorrect data types. Please see the Field Type column in the image below:
Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used. Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as Sum/Average is expected on a Id.
- Frequency of fetch
Historic Fetch and Daily fetches.
- Full : data within the last 6 months is fetched
- Partial : data within the last 3 days is fetched.
This is consistent with all Smart Connector delivery type options.
Note: This will depend on whether your Smart Connector is configured to fetch data based on Date or based on the Last Modified Date.
- Data Update If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications. This is done through the Delete button that can be seen next to the Smart Connector names in the Smart Connector list.
However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a Smart Connector. User can do this without deleting the previously loaded data. Standard partial / full fetch apply 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.