MYSQL Workbench
Thus far I’ve discussed general features of relational databases without actually working in an actual environment where one might work with data. We’ll begin to do that here. In another blog, I’ll discuss SQL in a python environment (Jupyter notebook) but here we’ll look at another environment.
In my blog on relational databases, I mentioned that there were a number of different databases utilizing the SQL architectures. Some of these are open source (MySQL, SQLite, PostgressSQL), and some are proprietary (Oracle DB and SQL Server). Here I’ll look at MySQL, one of the most commonly used relational database systems.
MySQL has a tool called Workbench which provides a clean interface for data storage and analysis. It isn’t necessary to use as may data engineers will choose to operate right from a python environment. But it’s useful for rapidly creating tables and querying the results.
Workbench connects to a remote server that actually stores the data. There are many remote servers such as Amazon Web Services (AWS). If you haven’t done so, you’ll first have to create a profile here.
AWS provides clear, step by step guidelines for creating and connecting to a MySQL database with AWS. The first step is in creating a MySQL instance. You won’t have too much difficulty if you follow those instructions so I won’t elaborate much here. The only thing I’ll add is that when you set up your instance, you’ll create a username and password. You’ll have to save those two along with the host/endpoint; all three will be needed the sync your workbench to AWS. The endpoint will just be a long URL that identifies your instance.
Once you’ve successfully created an ASW instance, you’ll have to set up the workbench. If you click on that link you’ll get taken to a download page that will look like this:
Great. Now that you’ve successfully downloaded the workbench, You’ll open it up by clicking the workbench icon:
When your workbench opens up it will look like this:
Search for the encircled + next to MySQL Connections. Click on that and when you do, a window will pop up:
First, you’ll name your connection before providing the hostname, username and password you created and saved from AWS earlier. After clicking ok, on your home page a box will appear under MySQL connections with the connection/s you’ve set up.
You're now ready to start working with data within the workbench! Stay tuned till next time!
¹ From MySQL doc page: “The name of the MySQL Dolphin (our logo) is “Sakila,” which was chosen from a huge list of names suggested by users in our “Name the Dolphin” contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Eswatini (formerly Swaziland), Africa. According to Ambrose, the feminine name Sakila has its roots in SiSwati, the local language of Eswatini. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose’s country of origin, Uganda.”
