First, this post is not a tutorial on how to use window functions in Denodo. I’ll come back and do that later. Rather, this post summarizes the issues I ran into when attempting to use window functions in Denodo….before I forget how I solved my problems. But, I will briefly explain what window functions are.
What Are Window Functions in SQL
In SQL, you can aggregate data, but that aggregation operates on a single level of data. SQL can’t handle more complex or multi-level aggregations without subqueries or window functions. You have limited flexibility in terms of complex logic or custom calculations.
Window functions enable calculations across a specific set of rows, known as a “window,” which you can also think of it as a subset of data. In my data shown below, I have several wells that have one or more wellbore descriptions. In this case, the window (subset of data) includes all records for each well and wellbore description.
Window functions use the OVER, PARTITION BY, and ORDER BY keywords, which you’ll see in the explanation below.
Basic Explanation of My Denodo Project
I have 7 Denodo views performing various data wrangling tasks.
- permian_devsurvey_01_select – This view joins together data from 4 different tables. This is my starting data set.
- permian_devsurvey_02_summarize_wellbore – Next, I need to know how many wellbore descriptions each well has and what they are, so in this view I create a wellbore description count and concatenate the names of all the wellbores using Denodo functions (shown below).
- permian_devsurvey_03_join_original_hole – I filtered permian_devsurvey_01_select to wells that only have an original hole. I don’t need to do anything with these, so they get to hang out until the end.
- permian_devsurvey_03_join_sidetracks – This view is permian_devsurvey_01_select filtered to wells that have more than one wellbore description. For these wells, I need to filter out some records based on the minimum measured depth of each wellbore description.
- permian_devsurvey_04_create_mins_sidetracks – Here I calculate the minimum measured depth for each wellbore description using window functions.
- permian_devsurvey_05_splice_sidetracks – In this view, I join the minimum measured depth for each wellbore description to the sidetracks data set and use it to filter out rows in the permian_devsurvey_03_join_sidetracks data set. That is the main purpose of this work. I am not going to go into great detail because it actually isn’t relevant to the post. Just know that I needed the window function to get a value to use in a filter.
- permian_devsurvey_99_final – Finally, I union the original hole data and the sidetrack data back together.
Here’s a small sample of the data.
Problems & Their Solutions
I started to have problems at permian_devsurvey_04_create_mins_sidetracks. Everything worked up to that point. Then, I got this error — Finished with error: Error executing view: Function min is not executable.
Initially, I thought this problem was because Denodo wasn’t pushing the min function to the database, but there were 3 other causes.
Problem 1 – Denodo Functions
In permian_devsurvey_02_summarize_wellbore, I used the code shown below to create a count and a concatenated summary of the wellbore descriptions. The GROUP_CONAT function is a Denodo function but not a SQL server function.
SELECT
devsurvey_01_select.idwell AS idwell,
COUNT(DISTINCT permian_devsurvey_01_select.wellboredes) AS wellbore_count,
GROUP_CONCAT(DISTINCT ',', permian_devsurvey_01_select.wellboredes) AS wellbore_concat
FROM permian_devsurvey_01_select as permian_devsurvey_01_select
GROUP BY idwell
According to AI (so take it with a grain of salt), when used in Denodo, window functions are typically executed on the Denodo server itself if they cannot be pushed down to the underlying data source, meaning Denodo will retrieve the necessary data from the source and perform the window function calculations internally; however, Denodo will attempt to “push down” the window function execution to the source database whenever possible for optimal performance.
So, basically, Denodo was attempting to push down all of the queries to the SQL Server database, and I was using a function that didn’t work there. The error message indicated the window function was the problem, but that wasn’t it. And, just in case it wasn’t clear. I was using the Denodo function in the “02_summarize” view, and that view executed fine. It became a problem in the “04_create_mins” view where I was using the window function.
To fix this problem, I just needed to not use the wellbore_concat field in 04_create_mins view. I needed it for information, but it wasn’t necessary for my final output, so this was okay. Also note, I did carry forward the wellbore_count field. It was not a problem because COUNT and DISTINCT are also SQL Server functions. If they were not, that field would also need to be removed.
Problem 2 – Bad Syntax
Since you haven’t seen it, here is the window function I was using. Now, I’m not new to window functions. I’ve used them for years in Spotfire, but I am new to window functions in SQL. I accidentally included an unnecessary GROUP BY clause at the end of my SQL that also created a problem. Even though we are aggregating, you don’t need GROUP BY. PARTITION BY fills that role.
MIN(measured_depth) OVER (PARTITION BY idwell, well_bore_description ORDER BY wellbore_rank)Problem 3 – Order By
And lastly, I was using ORDER by in all of my views leading up to the permian_devsurvey_04_create_mins_sidetracks view to make it easier to read the data. Apparently, you can’t do that with window functions, even if the ORDER BY is in a completely different view.
Even after I thought I fixed all these issues, I ran the code and still got errors because I forgot one of the ORDER BY clauses, and it had to come out. Interestingly enough, I was able to use ORDER BY in later views, like my “_99_final” view.
Conclusion
Eventually it all came together, and I learned a lot about working with window functions in Denodo.