Import data from Digital Ocean MySQL

Import data from Digital Ocean MySQL

Zoho DataPrep supports importing data from Digital Ocean under the cloud database category. Digital Ocean is a cloud computing platform that provides developers and businesses with simple, affordable, and scalable infrastructure to host and manage their applications and databases.

Alert
This connector is currently available only in the US data center.

Before looking into how to import data, let's take a look at the prerequisites to connect to the MySQL database.

Prerequisites for MySQL

Please ensure the following conditions are met before proceeding with connecting to your MySQL.

1. Verify that the required IP addresses are whitelisted. Click here to know the IP addresses.

2. Provide database read permissions to the Zoho DataPrep server.

3. Ensure you have the correct login credentials for your database. As a best practice, try connecting to your target database from the Zoho DataPrep server, using the native database management software available for the respective database.

Enable remote connections 

Login to your MySQL database as a root user. Navigate to the <MySql_Home>\bin folder in terminal/command prompt, and run the following command.

mysql -u root -p

  • Enter your MySQL root password. 
  • Run the following command to enable GRANT access.

mysql> GRANT ALL ON <Database Name>.* TO <Username>@<IP address> IDENTIFIED BY <Password>;

Note:

  • <IP address> must be replaced with the IP address of the Zoho DataPrep server.
  • <Database Name> must be replaced with the name of the database with which you are trying to establish a connection.
  • <Username> must be replaced with the database user name.
  • <Password> must be replaced with the corresponding password.

To import data from Digital Ocean MySQL

1. Create a pipeline or open an existing pipeline from the Home Page, Pipelines tab or Workspaces tab and click the Add data option. You can also click the Import data option under the Workspaces tab to import data.  
InfoInfo: You can also click the Import data  icon at the top of the pipeline builder and bring data from multiple sources into the pipeline.



2. Choose the Cloud databases category from the left pane and click Digital Ocean. You can also search Digital Ocean in the search box.



Notes
Note: If you have already added a Digital Ocean connection, choose the required connection under the Saved connections category and proceed to import. To learn more about Saved connections, click here.
3. Select Digital Ocean in the Database service name dropdown and MySQL in the database type.

4. Enter the End point, Database name, Connection name, Port, Username, and Password to authenticate the database connection.

Notes
Note: Zoho DataPrep supports SQL server authentication.


Notes
Note: The Connection name must be unique for each connection.

5. You can also select the Use SSL checkbox if your database server has been setup to serve encrypted data through SSL.

6. Click the Connect button.

Notes
Note: The connection configuration will be saved for importing data in the future. Credentials are securely encrypted and stored. 

7. Select the tables that need to be imported.



8. You can also Use SQL query option to select and import data.




WarningThe incremental fetch option is not available when the data is imported using a query from databases. Click here to know more about incremental fetch from cloud database.

9. Click Import to begin importing data from your Digital Ocean account.

To edit the Digital Ocean MySQL connection 

DataPrep saves your data connections to avoid the hassle of keying in the credentials every time you need to connect to a data source or destination. You can always edit the saved data connection and update them with new parameters or credentials using the Edit connection option.

Idea
Note: You can also edit the data source and connection details from the Pipeline builder page. Click here to learn more

1. Choose Saved connections from the left pane from the Choose your data source box while creating a new dataset.



2. You can manage your saved data connections right from the data import screen. Click the ellipses icon to share, edit, view the connection overview, or remove the connection.



3. Click the Edit connection option to update the saved connection with new parameters or credentials.



Notes
Note: Adding Digital Ocean as destination and exporting data to Digital Ocean from DataPrep will be supported soon.

SEE ALSO