MySQL as a Source

Getting your data to MySQL is an easy task to do with Dataddo.

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

1. Sign in to your Dataddo account and click on Sources at the top of the page.

2. Click on Create Source in the top right corner.

3. From the list of sources, choose MySQL. You can type the name of the connector into the search bar to find it faster.
   

4. From the drop-down menu, choose your MySQL Server connected to Dataddo and Click on Next to continue with the setup.

5. Choose a Name for your new Data Source and fill in your SQL Statement:
   

6. In the next step, you can set up some advanced optional settings. If are not sure what to write, skip to next.
   

7. Configure your snapshotting preferences by choosing your Sync type, Sync frequency, Time, and Timezone

8. 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.

9. Click on Save.

Congratulations, your new Data Source is ready!

SQL Statement Examples

To extract all the data from a table called Clients on all clients, you can use this statement:

SELECT \* FROM clients

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

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

To extract all the data from all the columns for a specific country:

SELECT \* FROM clients WHERE countryCode = 'USA'

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'

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 ;