How to extract data from MySQL
  • 3 Minutes to read
  • Dark
    Light

How to extract data from MySQL

  • Dark
    Light

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

NOTE:

Network setup required. Make sure to white list 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

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.
    Data sources - choose connectors

  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:
    Universal SQL - source statement

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

  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.
    Data source - create - preview

    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!

WARNING

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
NOTE:

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
NOTE:

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
NOTE:

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'
NOTE:

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'
NOTE:

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 ;

Need assistance?

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.


Was this article helpful?