GETTING STARTED | DATABASES | KNIME ANALYTICS PLATFORM
To Write SQL or not to Write SQL? KNIME Gives You Both Options!
Accessing and manipulating data in databases with just a few clicks
Ah, the Summer of 2018. This is a very memorable time for me because I decided to finally learn programming. Fueled by drive and Herculean dedication, I started taking a Udemy course in Java programming.
After a couple of weeks…I abandoned the endeavor 😱
Fast-forward to Winter of the same year, I was doing data migration at a massive corporation. We dealt with millions of rows of data, and it quickly became clear to me that it would behoove me and my team to learn some SQL. I consider this to be my second attempt at learning a programming language, and it went swimmingly!
Now, while I love SQL in general, my love for it goes down ever so slightly every time I have to perform different types of joins on 10+ tables, as my current job demands. This is where KNIME comes in!
After connecting to your database, using the relevant connector node, there are two ways to access data in your database in KNIME. You can either write and execute the query with the DB Query Reader node, or you can visually program your queries, no matter how complex, using the numerous in DB nodes. I am using the DVD Rentals database for the examples in this article. You can learn more about this database and view the ERD here.
In the configuration dialogue of the DB Query Reader node, I have written the SQL code to pull in all the data from the category table. Notice that the panel on the left shows me all the tables in my database, and if I click on a table, I see a list of the columns in that table. The evaluate button at the bottom right side of the dialogue box allows you to preview the results of your query, which can be super useful for exploring a new database. Neat right?
After running the node, I access the result by right clicking on the node and selecting the last option “KNIME Data Table”, this brings up the full result of the query.
Now lets try a slightly more complex query using the no-code approach. Say we wanted to find out the top five in demand Sci-fi movies, by the number of rentals. This endeavour would involve five tables; Rental, Film Category, Category, Inventory and Film. Watch the video below to see how you can do this in KNIME without writing any SQL code.
There you have it! We have successfully gotten the top five Sci-Fi movies rented out in our dataset. Now might be a good time to mention that these movies are largely fictional.
But, I must confess that I would part with my hard earned cash to see a movie about “A Touching Tale of a Girl And a Crocodile who must Discover a Waitress in Nigeria.” Hollywood, are you listening? 😉
Here is the same result using the code writing option with the DB Query Reader Node:
In a typically KNIMEIFIC fashion, KNIME provides you with options to access and gain valuable information from data contained in your database. So whether you like writing code or not, you will be satisfied. You can find the workflow built in this video in my KNIME Hub here.
Personally, I go for a hybrid model; almost all my workflows involving database data leverage both SQL code, and several DB nodes performing a variety of task.
What is your preference?
