Imagine you’ve just started a new job working as a business analyst. You’ve been given a new burning business question that needs an immediate answer. How long would it take you to find the data you need to even begin to come up with a data-driven response? Imagine how many iterations of query writing you’d have to go through.
In this scenario, you also have reports that need updating as well. Those contain some of the biggest hair-ball queries you’ve ever seen. What do they mean? Imagine how long it takes to unravel those queries just to understand them, let alone make modifications to fit new business requirements.
Also, those crazy queries don’t always run the most efficient way possible. Some are returning errors that are difficult to find—and if you’re missing KPIs you have to fix, optimize, and measure every bit of code, which can take a considerable amount of time and trial and error.
What a nightmare! Now imagine you had a personal assistant who knew everything about your data sets and was an expert in SQL, sitting alongside you every step of the way to help you quickly problem solve, write optimized code, explain queries, and much more. That would be amazing wouldn’t it? Well imagine it no longer, as Cloudera’s SQL AI Assistant is exactly that!
Creating a query when you’re new to a data model
Whether you’re new to a role, or just new to a given data source, finding data is 90 percent of the query creation problem. However, with the new SQL AI Assistant, this is no longer a chore. All you have to do is launch the SQL AI Assistant, and ask it to generate a query based on a natural language prompt.
In this example, we’re going to look for a list of stores ordered by their performance in terms of total sales. To do that, we’ll launch the SQL AI Assistant, select “generate” from the menu and enter “get store name, store id, manager, zip code, total sales of each store, and sort by total sales in ascending order“ as our prompt.
In the “assumptions” field, we see how the SQL AI Assistant looked over our data model; compared to what we’re looking for, it was able to find the right tables, columns, and joins needed to provide a query that will give us the list we’re looking for. No more searching for tables and columns and digging into cryptic metadata with time consuming trial and error just to find the right data sets. And as a bonus, we even get the query written for us, saving us even more time!
Editing an existing query to refine the results
Following along from the generation example above, let’s say we have a query and we want it to be a little more precise. We still need to examine the data to determine the right tables, columns, joins, and more to refine the query, and when we’re new to the data set this takes time. Even if the data are clear, if this isn’t a query we wrote in the first place; it can be hard to decide where to add additional joins and where clauses, etc., and not mess up the entire result. Have no fear, the SQL AI Assistant is here, and can help.
Let’s say that the list of stores by sales just isn’t helping us understand our performance measures quite right. Larger stores with more sales people will surely have larger sales. Maybe what we really want is a breakdown by sales representative by store, so we can see who has the best average sales per teammate, to get a better picture of what’s happening? So, to do that, with our original query in the query editor field, we can use the “edit” menu item from the SQL AI Assistant and write a prompt for just what we want to add—and not restate the entire problem we’re solving. In this case, we’re just going to ask the SQL AI Assistant to “add sales per employee and sort by sales per employee where sales per employee is total sales divided by the number of employees.”
Here, we see the difference between the original query (on the left) and the new query (on the right) so we can see exactly what the SQL AI Assistant is proposing as the change to the query itself. We also see an “assumptions” field that explains what it found for the additional data needed to refine the results. If we like these changes, we can “insert” them into the editor as our new query. Note, that we may also optionally include both the original prompt and the additional detail prompt in the comments of the new query so we keep track of the history of how we made this query as well.
Making sense of a complicated query
Quite often we come across queries we didn’t write, and the last known author cannot be found. Or, if you’re like me, it’s a query you wrote, but so long ago you cannot remember what it does. When it’s a simple query, that’s no big deal. But what if it is a complicated query with cryptic table and column names, and even when you run it and see the result set, you’ve got no idea how it works? And you’ve got to make a change to it to include more details or refine the result. Well the SQL AI Assistant still has you covered. Like an expert on both your data model and SQL, it will read the query and explain in natural language exactly what it does.
To do this, simply paste the query into the SQL editor field, and select “explain” from the SQL AI Assistant to get your explanation. In this example, we had this query to understand:
After running the explain process, you’ll see a natural language description of the query.
The SQL AI Assistant recognizes data-centric elements as well; where possible it will recognize things like comparing to the value 1.2 is the same as 20 percent above average. The explanation can be inserted into the SQL editor as a comment so we can keep, and modify, this explanation together with the query wherever we’re saving and documenting it.
Optimizing any query
Sometimes we are looking at a query that just seems overly complex. However, simplifying it for better readability or even faster performance can be a daunting, iterative task full of trial and error. Not anymore: with the SQL AI Assistant, you can easily ask for help to take any query and see if we can make it better. In this example, we have a query that contains many sub-selects and is hard to read and understand. If we paste this query into the SQL editor field and select “optimize” from the SQL AI Assistant menu, we will be given an optimized form of the query, if one is possible to create.
The result is a side-by-side comparison of the original query and an optimized form of it, together with the explanation of what we did to make it better: we made easier to read, easier to maintain, and possibly faster to execute. In this case we see the multiple sub-selects were converted into simple joins.
Fixing a query that won’t run
Sometimes we are struggling with a query that has a syntax error, but we can’t find it no matter how hard we stare at the code. The SQL AI Assistant can also help us in these cases as well. From anything as simple as a syntax error to anything as complex as a logical fault (such as a circular dependency), if you have the query in the SQL Editor you can simply select FIX from the menu, and see the recommendations the SQL AI Assistant finds for us.
In the example above, we see a side-by-side comparison of the query that wouldn’t run, and the fixed version. We see we forgot to close a bracket in the column list, we missed a space in the “group by” phrase, and we misspelled “limit” as “limits.”.
We also see one more correction that is interesting—in the “from” clause, we misspelled the table name as “stor_sales” instead of “store_sales.” That isn’t a syntax error, but certainly will be caught by the engine trying to run this query. The SQL AI Assistant also caught this mistake and offered us a correction for it, too.
After all the mistakes are caught, we can insert the corrected query into the editor, and will find it will now run.
Using the SQL AI Assistant, we can dramatically improve our work by having an intelligent SQL expert by our side, one that also knows our data schema very well. We can save time finding the right data, building the right syntax, and getting any new query started, with the generate feature. We can easily refine queries with the edit feature, make queries run better with the optimize feature, and eliminate errors with the fix feature. Using explain, we can rapidly document any query with rich natural language explanations of its function. All in all, we take the chore away from developing SQL, so we can focus on the fun part – answering tricky questions and using data to drive better decisions.
What’s next
The SQL AI Assistant is now available in tech preview on Cloudera Data Warehouse on Public Cloud. We encourage you to try it out and experience the benefits it can provide when it comes to working with SQL, please refer to the help document to find details. Additionally, check out the Cloudera Data Warehouse page to learn more about self-serve data analytics, or the enterprise AI page to find how Cloudera Data Platform can help you turn AI hype into business reality.
Editor's Choice
