- 3 Minutes to read
- DarkLight
MySQL as a Source
- 3 Minutes to read
- DarkLight
Getting your data to MySQL is an easy task to do with Dataddo.
Network setup required. Make sure to whitelist the following IP address to allow incoming connections from the Dataddo application to your server.
- 52.17.68.150
- 52.30.37.137
- 52.214.115.147
- 54.77.45.35
- 52.210.57.95
- 18.200.46.19
Step 1 - Add MySQL as a Destination
In order to connect a database as a source, first, you need to add it as a destination. Read more about the steps here.
Step 2 - Create a MySQL Data Source
Sign in to your Dataddo account and click on Sources at the top of the page.
Click on Create Source in the top right corner.
From the list of sources, choose MySQL. You can type the name of the connector into the search bar to find it faster.
From the drop-down menu, choose your MySQL Server connected to Dataddo and Click on Next to continue with the setup.
Choose a Name for your new Data Source and fill in your SQL Statement:
In the next step, you can set up some advanced optional settings. If are not sure what to write, skip to next.
Configure your snapshotting preferences by choosing your Sync type, Sync frequency, Time, and Timezone
Before saving the source, you can see a preview of your data by clicking on the Preview button. You can adjust the date range to a more specific view.
Broken View / Error Message
If you cannot see a preview of your data, go back a few steps and check your setup. The most common causes are:
- Date range - we recommend a smaller date range. If you need to load historical data, check our guide.
- Invalid metrics, attributes or breakdowns, or their combination - you may not have any values for them.
Click on Save.
Congratulations, your new Data Source is ready!
Everything in the query statement will be directly reflected in the database. Be especially careful when using commands such as Delete or Update, or anything that is not only Select or Feauds.
Dataddo needs the statement to get the right data. By using the specific statements you let Dataddo know what to do.
SQL Statement Examples
To extract all the data from a table called Clients on all clients, you can use this statement:
SELECT \* FROM clients
Replace the table name with your table's name.
To extract data from columns name, email, company from a table called Clients on all clients, you can use this statement:
SELECT name,email,company FROM clients
You can use this command to extract other data by replacing the name of the columns and the table with your information.
To retrieve data only from specific rows but from all the columns, from a table named clients starting from row 240000 to row 800000 use this query:
SELECT \* FROM clients
LIMIT 2400000,
800000
Replace the name of the table with your table's name and adjust the limit.
To extract all the data from all the columns for a specific country:
SELECT \* FROM clients WHERE countryCode = 'USA'
You can use this command to extract other data by replacing the name of the specified field and the table.
To retrieve all the data from all the columns if a job title contains a specific position:
SELECT \* FROM clients WHERE jobTitle = '%Manager%' OR company= 'Dataddo'
You can use this command to extract other data by replacing the name of the specified field and the table.
Delivering data to storage
Append-only
When data delivery is scheduled, Dataddo gets the actual contents of the data source and performs a set of INSERT operations (one or multiple) against your storage. Neither DELETE nor UPDATE operation is performed, therefore the data are inserted in append-only mode.
Deleting historic rows
In certain cases, the append-only solution might have drawbacks due to the growing size of the database. To cope with this, the best solution is to define an AFTER INSERT trigger that deletes the historic data. In the example below, the trigger deletes all the rows meeting the insert_date < DATE_SUB(NOW(), INTERVAL 1 DAY) condition. Dataddo connectors allow to extract various date information, so the retention period can be flexibly set.
DELIMITER $$
CREATE TRIGGER trigger\_name
AFTER INSERT
ON table\_name
BEGIN
DELETE FROM table\_name WHERE insert\_date < DATE\_SUB(NOW(), INTERVAL 1 DAY)
END$$
DELIMITER ;
Feel free to contact us and we will help you with the setup. To speed the process of resolving your issue, make sure you provide us with sufficient information.