Day #2 of my course involved getting over view of data modelling. The course started off with basic introduction courses for relational and cloud databases. The course per se was touching only on basic terms and bit underwhelming w.r.t to intro to PostgreSQL database.
Cassandra database is the next hurdle to cross and work on.
Yesterday marked my first day in the Data Engineer Nanodegree course offered by Udacity. After thinking a lot on how to best equip myself and enrich my knowledge in the world of Big Data and taking the steps towards it, this course came along talking about THE essential things that I wanted to learn – Cloud data warehouses, Spark and Data Lakes.
What further sealed the deal was I am working on a project where we are using Spark and Data Lake as well. However, it is being handled by a separate team. My involvement so far has been to the extent of writing Impala queries, creating data structure, testing the sqoop queries and occasionally query tuning by looking at the logs to understand which partitioning is better. I reasoned that doing this course will give me a better ammo to pitch myself to get into the Data Lake team. Time will tell (fingers crossed)
I have been longing for an opportunity to pivot my career from the traditional BI to Data Engineering on Big Data Platforms. Here is a course that not only promises to teach the nitty gritties of being a Data Engineer with a proper structured methodical teaching but also help with shaping up my career via services like resume editing and LinkedIn page setup. Long way to go for that.
So here is what my Day 1 (yesterday) felt like so far- Absolutely wonderful!
In the first few videos I have really gotten to know what Data Engineer really means and what other titles actually mean and how they stack up.
What resonated me a lot was this article that was one of the materials to read up. It spoke volumes to me as this was exactly the path I had been following all through my career. I started off writing ETL packages via SSIS on traditional OLTP – OLAP databases, designing cubes off of it, designing and developing reports based on it.
All these have stopped about 3 years ago and it was only a year ago, I am completely off it. I am now working on data sources which are disparate in nature or are built on the Data Lake. This is a brand new world for me and am loving every part of it. The challenges are different, more exciting and there is SO much more to be done.
Looking at the evolution of how data has proliferated and how the traditional RDBMS technologies are not sufficient to cater the growing needs of business, I am happy to see the organic growth in me. Of course, to be where I am today, the forces that have shaped me are largely due to the work done in BI but stepping into new future I need more ammo.
Coming back to the course, I started off with Data Modelling basics and some intro into PostgreSQL.
Next post would be more structured. The purpose of this post with # tag is to motivate myself to read every day and share my thoughts on my learning.
One of the most common requirement that comes when creating Alteryx Apps involving a dropdown is to have (Select All) as one of the values. This value, if you have not inferred by now, would not be part of a data source but something we add to it. Basically I am trying to simulate what Excel does when ‘Sort & Filter’ is enabled as seen below – 
In this post I am going to demonstrate how to add this value in the drop down and how it then needs to be consumed in filtering the datasource. This post will have the following sections –
The test data which I am going to use for this post is the highest grossing Marvel Movies which I am getting from this link – Marvel Comics Movies at Box Office. I copied the first table and stored it in my local disk. In my Alteryx App, I have dragged this data as a Input Tool. Used DateTime Tool and Filter to create a unique Date Format.
Sample Data is shown below –
As next step I am going to create one Dropdown filter – ‘Studio’. For this post I am choosing the option – Manually set values (Name:Value – one per line). I have entered the values manually. As you can see I have kept the option ‘(Select All)’ as the first entry.
If the data is coming via connected tool, ensure that source data for the filter is joined with the ‘(Select All)’ manual text data. Likewise, if it’s coming from external source, add this entry. Essentially because this value doesn’t exist, we would need to add it.
We identified our column on which we need to filter. We have created the values for the filter. It is now time to actually ‘filter’. I have now dragged in a Filter transform. Too much repetitive, isn’t it. I will now ‘filter’ (pardon the pun) going forward. Put the following entry for the ‘Customer Filter’ option as shown below –
Here is what we need to do next –
1. Connect the ‘search’ icon of ‘Studio’ dropdown to the lightning bolt of ‘Filter on Studio’ Operator. A ‘Update value’ operator pops up in between.
2. Click on the operator. Under the ‘Value or Attribute to Update:’ section, click on “Expression – value = ….”
3. Click on the checkbox ‘Replace a specific string:’ and just keep the value as ‘<studio>’ (without quotes) as can be seen below- 
That’s it. Essentially the logic here is the inclusion of OR expression of place holder value equal to ‘(Select All)’. When a specific filter is selected, the select all expression goes false and when ‘(Select All)’ option is selected, well this one gets true and essentially the filter will flow through all the data.
Same logic can be used for filtering the data say in a Dynamic Input with data coming from a particular SQL data source. Code for that would look like this –
select *
from really_awesome_table
where (
that_parameter = ‘<this_value>’
or
‘(Select All)’ = ‘<this_value>’
)
To test it out I have put in a summarize transform to group by ‘Studio’ and ‘Total Movies’. Let’s run the workflow. Here is how the wizard looks like –
Here is how the output looks like, executing with (Select All) and individual Filter –
Have you come across the problem of not being able to select ‘Scala’ as part of the ‘New Project’ in IntelliJ as shown below (psst – there is Scala present, I know. For imagination sake let’s say you don’t see it, kapise?) ?
First thing when you google, everything it points out is for you to installing ‘Scala plugin’.
Let’s say you do have the plugin installed and available and enabled as shown below but even then you don’t find it in the ‘New Project’ –
This usually happens say when you have upgraded your IntelliJ or re-installed your IntelliJ with a different version at different time and have imported the old settings. The best thing to do to get over this error is navigate to the folder – C:\users\<username>\ and delete all the folders starting with .IdeaICxxxx as highlighted below –
That’s it. Now open up your IntelliJ again, all should be good now to get going. Of course this time around you would need to get your JDK and Scala libraries set up. Follow this blog – link
While working on one of the workflows of Alteryx with PostgreSQL data sources, I started getting this wierd error –
Error SQLExecute: Out of memory while reading tuples.; memory allocation error???
This was a unusual error. For one I was running this not on my local machine but on the Gallery which was governed by the server. Also I felt Alteryx folks could have done away with three question marks (???). It feels as if we are being questioned on the memory allocation and it’s just waiting for some confirmation.
This is one of those classic it-works-in-dev-not-in-prod kind of problem. I then went back and compared the two ODBC data source settings across both the environments. It was here I could see the problem. You need to set the setting for PostgreSQL ODBC data source to have ‘Use Declare/Fetch’ option set in.
Open up the ‘ODBC Data Source Administrator’ window after running it as administrator. Click on the ‘Data Source’ that you are using and click on ‘Configure’. In the ‘PostgreSQL ANSI ODBC Driver (psqlODBC) Setup’ window, click on ‘Datasource’. Ensure that the checkbox ‘Use Declare/Fetch’ is checked and then click on ‘OK’ and close the subsequent windows. This setting resolves the issue. Below is the screenshot
Figuring out how to run Powershell scripts using Alteryx has been a very harrowing experience for me. I had used ‘Run command’ tasks before but I remember even then it was quite a struggle to get it up and running.
The help files from Alteryx with regards to this task is abysmal. It gives an explanation of each task within the command but doesn’t really tell you how to go about doing it step by step. The nearest help from Alteryx forum that I could get is this. Here is my attempt to make it easier for folks who find themselves in similar situation.
Task Description
I need to create a sub-folder under multiple folders. This ‘sub-folder’ name is dynamically determined during run-time depending on the input file name to the workflow.
For example, say my input file is – movies_released_20180801.csv. I have a set of folders under the parent directory – Movies as shown below –
I need to create a folder called ‘20180801’ under each of the sub-folders, if it doesn’t exist already. As each month passes, new sub-folders would need to be created.
Solution
My first step for the solution is to create a powershell script that can perform the sub-folder creation. Here is my script –
#Get Parent directory
$root = "C:\Users\kvankada\Documents\Personal\Learning\output\Movies"
#Get all directories present in the parent folder
$folders = Get-ChildItem -Path $root -Directory
#Iterate through each item within the list of parent folder
foreach($folder in $folders)
{
#Get the subfolder full path
$Subfolder = $folder.FullName + "\20180801"
#Check to see if the folder exists
if(! (Test-Path -Path $Subfolder))
{
#If it does not exist, create new sub-folder under each item
New-Item -ItemType directory -Path $Subfolder
}
}
The root folder of the above script file comes from the Alteryx workflow. The next steps now starts looking crazy but bear with me. It will all make sense. Drag in a ‘Input Task’ with following two columns –
Path – C:\Users\kvankada\Documents\Personal\Learning\output\create_subfolders.ps1
Input – C:\Users\kvankada\Documents\Personal\Learning\input\movies_released_20180801.csv
The Path should be a valid path and accessible to the user. It can be a UNC path as well. Drag in a ‘Formula’. Create two new columns Subfolder and Powershell_Script with following values as shown below –
As can be seen the new column Powershell_Script is nothing but the entirety of powershell script with ‘Subfolder’ being dynamically sent. Drag in a ‘Select’ task. De-select all the fields excepting ‘Path’ and ‘Powershell_Script’. Drag in a ‘Run Command’ task. Click on ‘Output’ under ‘Write Source [Optional]’ in the configuration window as shown below –
In the ‘Write to File or Database’ file, click on the drop-down and select ‘File’. A ‘Save a data file’ window pops up. Here chose any location and save the file as say ‘temp_powershell.csv’. I have chosen a ‘temp’ folder as my file location. In the ‘Alterx Designer’ the following changes still needs to be done against following settings –
Delimiters – \0
First Row Contains Field Names – Unchecked
Quote Output Fields – Never
Take File/Table Name From Field – Tick the checkbox. Chose the option as ‘Change Entire File Path’
Field Containing File Name or Part of File Name – Path
Keep Field in Output – Unchecked
The final dialog would look like as shown below. Changes have been highlighted. Click on ‘OK’ post completion –
The reason we need to do this is because Alteryx doesn’t provide a way to save the output to Powershell script file. We need to get the content of Powershell first saved as CSV and then the whole file being renamed via our ‘Path’ value that we have provided in the workflow. It’s a roundabout way of doing things.
Continuing the ‘Run Command’ Task configuration and set them as shown below.
Pay particular attention to double quotes in the ‘Command Arguments [Optional]’ section. It is absolutely necessary.-
That’s it. Now you are set to run. There is no need to configure the output of ‘Run Command’ task. If you do need to use, then ‘Read Results’ path needs to be configured. I might do a continuation post later on for that. For now you can ignore the need for additional output configuration. Here is how the final workflow looks like –
I ran the workflow and here is my output –
For final workflow in the ‘Run Command’ task, you would need to set ‘Run Minimized’ and ‘Run Silent’ check boxes to have smoother run. As you are testing out, it would be important for debugging purpose to leave them unchecked. In that manner you would see if you have any errors popping out.
That’s it. That’s how it is done in Alteryx. It took me quite a lot of tinkering and experimenting to get the desired result. Hope this post has helped in solving your own problems while getting Powershell scripts run via Alteryx.
Yet another strange day to be in. I kept getting the following error –
Error – The Designer x64 reported: InboundNamedPipe::ReadFile: Not enough bytes read. The pipe has been ended¶
I tried several methods – clearing of temp files, restarting alteryx, restarting my system multiple times, logging on and logging off etc to no avail. It just kept failing.
What added to the confusion is, it was happening only for few tables whose volumes are much smaller in size. I mean to say Table 1 with about ~20M records from the same database, we were able to extract successfully whereas from Table 2 with only ~3.5M we were facing this problem. Seemed really strange. The good old internet hadn’t turned up with anything useful and neither the Alteryx Forums. All the info that I got was that during one stage of workflow , it was running into error.
The case for me though is in my workflow apart from the ‘Input Tool’ from where I was pulling in the data and the ‘Output Tool’, there was nothing in between. Still it was failing.
Anyway I just took a break, thought for a while and said to myself – “When was the last time you had such strange error with Alteryx and Postgres combo?” Oh yeah, right here.
Hmm, why not try the same fix? I sure did and you know what! It just god darn works! Don’t even ask me how or why, it works. So gents and ladies, here is the fix you gotta do –
Go to the, Pre SQL Statement of your Alteryx ‘Input Tool’ and insert the following –
set client_encoding to ‘UTF-8’
As simple as that! Thank me later.
It was one of those challenging tasks that had to be done and I felt it’s good to blog about it as well.
Task –
Extract data from a limited range of a sheet from various excel files. Each of the files have various tabs in it and the names of the tabs are not unique.
For instance, let’s say I have three excel files with the following name format – MonthlyReport_<<Month>>.xlsx where the Month is for Dec, Jan and Feb.
The data within my ‘Dec’ file is as follows –
Nice, well formatted data. Let’s look at Jan’s data –
Oh, oh! somebody left a comment in the Column E. Let’s now look at the Feb’s data –
Aargh..Another person left another comment this time at Column D.
Now my task is to get all the data from Columns A and B only across all the excel files with the differently named tabs.
Solution-
The first step for us is to get all the sheet names from each of the excel files. To achieve this we need to use a macro and the steps are listed below –
Now we have a macro which can give the full path of the excel file along with the query that we need to get the data from. We now need another macro where we can put the query in and get the data. Remember anytime we need looping, macro is the way to go. The first step we did was just getting the sheet names from each excel file and modifying the file path with the query.
The second macro to do is fairly simple –
February$A1:B100000
Okay, so we now have two macros, one for getting sheet names and modifying the full file path with the requisite query, the other for getting the data from the excel file. We now need another workflow to call these two macros and do our job.
Post-running the workflow here is the output from Browse –
That’s it. For my task I had to go through about 12 different excel files with various tab names and the out-of-the-box tools that Alteryx provides is so very powerful to get the job done.
Just falling in love with it.
This is one of the most troublesome errors that I had encountered for long time and finally at last I found a way to get past it.
I kept getting the following error when trying to fetch data from my PostgreSQL source. This error was an arbitrary one which used to pop based on the data within. Here is how the standard error message is shown in Messages window of Alteryx –
Error: Input Data (11): Error SQLExecute: ERROR: character with byte sequence 0xe2 0x80 0xad in encoding “UTF8” has no equivalent in encoding “WIN1252”;
Error while executing the query
The byte sequence that I highlighted corresponds to blank space. I tried trimming out blank spaces in my data. I then got another error this time indicating another character. The first workaround to this problem that I applied was to create one more ODBC data source with a Unicode driver. Now that resulted in another problem altogether. All the date format of the input data had changed to text.
Here is the actual fix that helped me. Reading online I did see that one would need to change the setting from WIN1252 to UTF8. Now that I didn’t want to do at database level and I wasn’t sure where to put it. It is after bit of tinkering I see putting that in the Pre-Execute SQL Statement does the trick as shown below –
Problem solved.
One of the tasks that I had needed me to have a proxy table of dates using which I had to achieve other business logic. Using PostgreSQL and recursive CTE I was able to achieve this.
Here is the code –
--cte to get range of numbers, which in this case is 1 to 90 with recursive ints (n) AS ( select 1 union all select n+1 from ints where n+1<= 90 ) --cte to get date ranges by using interval function and --multiplying the ranges ,cte_dates as ( select current_date - interval '90days' + interval '1day' * n as dt from ints ) --the query below will now get you last 90 days date ranges select dt from cte_dates</pre> <pre>
The point to note is you can multiply the intervals that you specify using the good old multiplier.
Karthik's BI Musings 