- 3 Minutes to read
MySQL as a Source
- 3 Minutes to read
MySQL is an open-source relational database management system. It is widely used for storing, managing, and retrieving structured data, making it a popular choice for web applications, content management systems, and various other software solutions.
Authorize the Connection to MySQL
In order to connect MySQL as a data source, you will need to first authorize the connection to your MySQL.
Create a MySQL Data Source
- On the Sources page, click on the Create Source button and select the connector from the list.
- From the drop-down menu, choose your account.Didn't find your account?
Click on Add new Account at the bottom of the drop-down and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.
- Name your data source, choose your MySQL server and fill in your SQL statement.
- Configure your snapshotting preferences. Choose your sync frequency or the exact synchronization time under Show advanced settings.DATADDO TIP
If you need to load historical data, please refer to the Data Backfilling article.
- Preview your data by clicking on the Test Data button in the top right corner. You can adjust the date range for a more specific time frame.
- Click on Save and congratulations, your new data source is ready!
Dataddo relies on your query statements to accurately retrieve the right data. By choosing specific statements, you guide Dataddo's actions on your data.
Every command in your query statement directly affects your database. Special attention is required when using commands like
UPDATE, which are more impactful than a simple
SQL Statement Examples
- Extract all data from a table called
SELECT * FROM clients
- Extract name, email, company columns from a table called
SELECT name,email,company FROM clients
- Extract data from specific rows (rows 240,000 to 800,000) from all columns, from a table named
SELECT \* FROM clients LIMIT 2400000, 800000
- Extract all data from the columns for a specific country from a table called
SELECT * FROM clients WHERE countryCode = 'USA'
- Extract all data from all columns if a job title contains a specific position from a table called
SELECT * FROM clients WHERE jobTitle = '%Manager%' OR company= 'Dataddo'
Only Append is Possible
As neither delete or update operations are perfomed during data delivery, data is inserted in append-only mode.
In certain cases, the append-only solution might have drawbacks due to the growing size of the database. The best solution is to define an
AFTER INSERT trigger that will delete historical data as the retention period can be customized. In the example below, the trigger deletes all the rows meeting the
insert_date < DATE_SUB(NOW(), INTERVAL 1 DAY) conditions.
CREATE TRIGGER trigger_name
DELETE FROM table_name WHERE insert_date < DATE_SUB(NOW(), INTERVAL 1 DAY)
Context Deadline Exceeded Error
rpc error: code = DeadlineExceeded desc = context deadline exceeded
This issue may be caused by extracting data over an extended timeframe. Use
LIMIT clauses in your SQL query to manage size and scope of the data extraction.
WHEREto specify the date range.
SELECT * FROM your_table WHERE date_column BETWEEN '202X-01-01' AND '202X-01-31';
LIMITto specify the specify the maximum number of records to return.
SELECT * FROM your_table LIMIT 1000;
LIMITfor more precise control, e.g. this query will return the first 1000 records where the date is after January 1, 202X.
SELECT * FROM your_table WHERE date_column > '202X-01-01' LIMIT 1000;
Now that you have successfully created a data source, see how you can connect your data to a dashboarding app or a data storage.