KNIME Column Rename Node offers a quick and painless way to rename column headers in your analytics process. This Column Rename Node tutorial uses the data from the previous blog about KNIME File Reader Node.
Becoming an analytics guru, you need to learn how to rename columns, without a software, in the SQL language, but you don’t need to worry about being the master of this today.
KNIME Column Rename Node handles this code or SQL for you. We will quickly go through basic SQL, and then dive into why it’s important to know basic SQL in the analytics profession. Followed by – how to use the KNIME Column Rename Node.
If you’re following along with the KNIME Blog series – welcome to lesson 4 and thanks for sticking with the series. It’s designed to help you walk through KNIME in a user-friendly format. Thanks!
I’m going to teach you the “how it was” OR you can quickly search for the “column rename” node in your node repository.
Double click on Nodes you want to add to your screen!
For the sake of learning how the software works, double click your column rename node, two times…
You must select the KNIME File Reader Node, or any node, before double clicking, IF you want your node to automatically connect. Currently Alteryx Desktop does not have a feature that allows this “double click throw” – add to workflow – and I’m starting to rather enjoy this part of the software in KNIME Analytics Platform.
If you don’t want two different column rename nodes, or in the future, if you make a mistake, you can hit the buttons CTRL+Z on your keyboard, and “undo” your additional “Column Rename Node.”
Press Ctrl+Z to remove the second Column Rename. CTRL+Z is your friend.
How to rename column headers in SQL because you need to know this part of analytics too.
Do you need to rename your column header in SQL? Cool story. I’m going to teach you, even though you don’t need to know, it’s important to understand WHY you should learn the basics.
Renaming column headers in SQL is very fundamental and simple. It’s likely you will learn how to rename column headers in your first database class in college.
However, 99% of every Tableau or PowerBI guru I’ve met… Has no experience with SQL queries and uses these products to maintain simple SQL changes.
There’s a massive need for people that understand remedial code and able to talk to business users or executives.
Learning how to query a database.
First we need to select all the columns. Checkout w3 SQL Select Statement because they offer a free SQL tool online.
SELECT * FROM CUSTOMERS
Now we can see we have all of our columns, the asterick means ALL in SQL.
GIMME ALL THEM COLUMNS.
Select * = “you speak the words select all and you don’t type all…”
Okay, now you know how to walk around the office and query database for all the columns and records.
Learning how to rename your column in your SQL Query
Learning how to rename your column in your SQL query… Let me repeat that again, just kidding..
Understanding column rename will set you apart from most analytics professionals. Crazy, I know.
Renaming in SQL is not complex – however keeping up with code takes time and time is not something we humans can take for granted.
So – here’s the classic approach to renaming, with SQL.
SELECT CustomerName as “Customer”,City
Although renaming columns is “simple” – maintaining code takes time, eventually it’s too much code takes too much time, and most of us completely forget support or remediation requires extra time too. We need to always consider the ramifications of “it’s here today,” and not let it stress the entire project. We can optimize tomorrow.
If it’s code – it’s likely faster BUT when you need to make changes or pass owners, considering a software driven data analytics tool might be a better solution that managing lots of SQL.
KNIME Column Rename Node helps you workaround maintaining code to rename columns.
Maintaining code to rename columns is a waste of time in lots of cases.
Analytics needs simple prep and blend, which most people are completely unwilling to accept as a learning requirement in analytics, and that stands to mean you can make a lot of money by simply sticking to these readings.
KNIME Column Rename Node, in a simple form, helps you automate code. Before this blog, I have not shared what it takes to build the solution in code because everything we have done up until this solution has been too much code to explain in a simple blog. Renaming columns in SQL happens to be a rather easy and not knowing how to rename columns in SQL may be considered a negative in the eyes of anyone with experience with a database.
Often times understanding this logic, may not benefit your ability to solve in your job, however knowing this information will help you build trust between you and other data owners.
Learning basic code to bridge new connections, impress your peers.
Data owners around the organization don’t care much for how you rename the column, and if you’re requesting access with a software that could potentially damage the environment, PLUS you don’t know the basics…
It’s not unusual for people to quiz you or have their ear to the ground for any potential reason to keep you from accessing. It’s in their best interest to keep rouge database querying machines off their environment.
Until you understand the basics of information systems… the data owners may not give you access to the database as quickly as you would like. So, learning the basics is not only a way to bridge new connections but also a way to help further your insights in your front-end analytics.
Slow processes or difficulty connection to databases is not untypical in enterprise organizations, rest assured most IT departments should be familiar with building you a replicated environment to query as much as you want. Without fear of hurting the database, network, or others using these tools.
Using the Column Rename
Okay, now that you’re completely exhausted with information. I will do my best to keep the rest of this blog, super simple!
Right click, to see what’s available, left click configure to open your configurations.
If you took time between series, be sure to execute your previous KNIME File Reader Node.
We want to see the data, our KNIME macro is B.
We need to get a snap shot of what columns are what…
We want to use our Column Rename node, to automate changing the title of the incoming data, the “title” is also the “header” of the column. Most analytics professionals call the column title “header.”
Now we are changing the column header, aka Renaming!
Above – we know Col0 = the beginning of the URL.
Col1 = the keyword “analytics.” That’s the word in the job search on LinkedIn.
Col2 = a bit of the URL, also works as a type of filter in the URL request.
Col3 = City and each space has a %20, this helps tell the URL how to process our Cities correctly, you can’t have spaces in a URL!
Col4 = More padding URL code, we don’t need to worry about it.
Col5 = State and %20 where a space lives.
Open configuration on the Column Rename Node
Okay, let’s open the configuration on the column rename… If you haven’t already.
Let’s use the above, to help us name the columns, in a sensible yet small order.
Massive column headers are annoying, slow, don’t get me started.by tyler garrett
We will double click 3 column headers.
Let’s start with Col0. Double click to send it to the right pane.
Left click Change checkbox…
We can rename the column…
But before we start, let’s do it all at once.
Double click Col0 through Col6.
Rename everything like…
Notice we have Col6.
Pretend your boss sent you an email right now.
Executives don’t care about Col6, click REMOVE.
Once you have these, click OK.
Execute your Column rename node.
You can now open the cached data source, my macro is B.
If you haven’t followed our previous lessons to understand how to get on the cached data source, no worries!
Find this icon and click the icon. It’s in the right click per node, and it’s in your toolbar.
Great, we can clearly see we are winning at column renaming. You can quick and easy resize your column headers, squeeze down the headers that do not make sense, view what is valuable, which is a GIF showing “renamed column headers.”
Now you are familiar with looking at cached data between nodes, and you’re able to determine what the data looks like downstream from your data input.
Open your configuration again.
If you have a large file or data source. Often times you can have column headers in the hundreds. I’ve seen plenty of data sources with over 100 columns. People making changes in that amount – need support.
The software offers this built into a drop down that is called “Filter Options.” Now – I hope you understand this naming convention may sound advanced, however, within the means of this NODE – it does not mean “filter…” like we filtered using the Row Filter Node.
Rather, this is talking about “what has been changed in this Column Rename Node.”
If you had 1000 columns to sort through, this will come in handy for those heavy lifting sources.
Understand, you have just accomplished a transformation in the process of becoming a full blown ETL badass.
The T, in ETL is not silent.
The T in ETL stands for transformation. Each time this file updates, you can apply your header transformation. The software calls this “Manipulation” and in the data warehousing or analytics community, you will hear “data prep and blend” or “transformation.”
Let me be the first to congratulate you on your first major step to automation. Let’s continue learning more nodes because we are not done with this data source!
KNIME – Column Rename – Dialog Config
- Rename column names or change their types.
- The dialog allows you to change the name of individual columns by editing the text field or to change the column type by picking one of the possible types in the combo box.
- Compatible types are those to which the cells in a column can be either safely cast or transformed to.
- A configuration with a red border indicates that the configured column does not longer exist.
KNIME – Column Rename – Ports
|0||Table whose columns shall be renamed/retyped.|
|0||Table from input but with customized column specifications.|
This node is contained in KNIME Core provided by KNIME AG, Zurich, Switzerland.