Data Pipeline - Google Colab → Local DB (MySQL)

Solution:

It might be challenging to access a locally hosted database from the Google Colab server.

Since the servers are geographically separated, they must connect online. When you wish to utilize the Python kernel, Google Colab creates instances on the fly.

If you wish to access the database from a Colab instance, you must first be able to connect to the remote server.

There are several tools and services that may be used to link the two servers. In this blog, we will expose the local host to the web using ngrok .

Without changing any network settings or opening any ports on our router, ngrok service has Secure Tunnels to provide immediate and open access to remote systems.

We may access our MySQL service across the TCP protocol with a safe, dependable tunnel provided by the ngrok service.

Also, check this Data Science Courses in Chennai to start a career in Data Science.

Steps to Connect Local DB with Google Colab:

• 1. Download ngrok
  Unzip the extracted software and save it in a folder on your local Windows machine.
• 2. Sign up and validate your email.
• 3. Follow the setup instructions sent to your registered email.
• 4. Connect your account: Establish an account connection by adding your authtoken to the standard ngrok.yml configuration file. Longer session times and access to additional features will result from this step.
• 5. To start a TCP tunnel forwarding to your local port 3306, run the below command.
  ngrok tcp 3306

The hostname and port combination URI is: 0.tcp.in.ngrok.io:19***, which maps to localhost 3306 of the local MySQL database installation.

To connect with Google Colab, this host and port combination exposes our database services to the internet.

The Google Colab notebook's code snippet for connecting to the local host DB (MySQL) server:

Conclusion

As organizations realize the importance of Data and move their business strategies to be data-driven, the importance of Data engineers in the market is on an upward trend. The availability of Data for deriving insights is a must. The experiments on the Data are performed using various processing platforms with Python IDEs, Google Colab is one of the many free services that are on offer. Data pipelines incorporating ETL or ELT as per the needs of the business must be built integrating the multiple tools involved in the technology stack.

What do you think is the most challenging task in developing these pipelines, we would love to hear your experiences and opinions/views.