Hello, and welcome to our November update of our Toolkit blog series! For those in the USA, November brings about one of the year’s best holidays: Thanksgiving!
We want to thank all our readers and those who use the phData Toolkit. We have purposely built this tool based on projects with our customers and feedback from our staff and customers, and it aims to accelerate your data journey.
This may be things like platform migrations, designing your information architecture, auditing your Snowflake ecosystem, and providing recommendations based on best practices and customer experience (and much more). For those who have partnered with us to build this tool or provided feedback, we want to give you explicit “Kudos!!!”. Thank you.
Over the last month, we’ve added new functionality to our SQL translation tool and modified our Access Tool. Let’s dive in and take a look at the changes!
SQL Translation
Our SQL Translation tool, known as SQLMorph, supports translating SQL dialects between different platforms. This includes a variety of sources such as Teradata, Oracle, Hive, and Microsoft SQL Server (to name a few).
Lately, we’ve seen a significant increase in Oracle to Snowflake and SQL Server to Snowflake migrations from our customers. This has increased the need for a strong SQL translation option as these platforms leverage different SQL dialects. This can be a very tedious and time-consuming process to perform manually and often leads to small mistakes over thousands of files.
Our SQL translation tool is able to programmatically convert these and provide warnings and errors for developers to look further into where either a statement isn’t supported, or a translation isn’t perfect.
For example, if you were trying to translate an Oracle-stored procedure that leverages the dbms_output.put_line command, you would run into problems as this functionality doesn’t exist within Snowflake. Our tool is now able to capture this feature gap and provide a warning to developers.
PROCEDURE keepFunc (val1 NUMBER) IS
BEGIN
dbms_output.put_line( 'After loop: ' || val1 );
return val1;
END;
CREATE OR REPLACE PROCEDURE keepFunc(val1
-- WARNING: line 0, character 0: Syntax difference: Oracle NUMBER without precision and scale specifies the maximum range and precision for an Oracle number. In Snowflake this specifies NUMBER(38, 0). Based on customer feedback we translated this type to NUMBER(38, 8) which may not be appropriate for your use case.
NUMBER(38, 8))
RETURNS STRING
LANGUAGE SQL
as
$$
BEGIN
-- WARNING: line 3, character 2: Statement is not supported: DBMS_OUTPUT.
/* dbms_output.put_line( 'After loop: ' || val1 ); */
RETURN val1;
END;
$$
;
While this might not seem like a great translation, when you’re performing translations against thousands of files, it quickly lets developers know where to focus their time and attention. They can quickly search the directory for -- Warning: or -- Error: to see which files and areas need to be addressed.
We’ve also introduced some fixes for the following inside of Stored Procedures (aka Snowflake Scripting):
WHILEloop conditionsString concatenation
CURSORstatementsCURSOR FOR LOOPstatementsNULLstatementsCONTINUEandEXITstatements
These are just a few examples of the changes we’ve made. You can always look at the full list on our Toolkit updates page!
Access Tool
The phData Access Tool within our toolkit is purpose-built for auditing. This tool helps users and administrators better understand roles and privileges in the Snowflake Data Cloud using a searchable interface where you can drill down into your Snowflake account metadata, including users, roles, privileges, databases, and schemas.
This can be visualized in either a graph interface or a table interface, allowing administrators to drill down into what has access to what and how that access is granted.
As a general guide, here’s a common set of questions we see around Snowflake that the Access Tool allows you to solve:
Which users or roles have access to objects such as warehouses, databases, or schemas?
How do users have access to an object or resource?
Is my privilege model and information architecture being followed?
What role do I need to access a particular data set?
Who are my system administrators and account owners?
Who owns an object?
Which users are active in the environment?
How many objects of each object type are in my environment?
Over the last month, we’ve made a few modifications to the tool. One of the most important ones is really a quality of life improvement for people using the tool. Often, users will drill down to a particular set of filters and want to share what they’re looking at with a coworker (without taking a screenshot).
So, what do users do? They copy the URL in their browser and send it to a coworker. Well, if your URL doesn’t contain all the information about your search criteria, your coworker isn’t going to see the same results when they load that page in their browser. The application simply doesn’t know what the other user’s filters were. We’ve now resolved this issue by saving metadata in the URL to allow the application to rebuild those same filters!
We’ve also introduced some quality-of-life improvements for users who have larger Snowflake environments. It can take quite a while to calculate and render a very large graph, and previously, the application would warn you that the load time would be long, but once you said “okay” and the application started trying to render, it would look like the page just froze. We’ve added a loading spinner to provide the user peace of mind that the website/browser has not frozen!
Some other notable but more minor changes include:
Ordering filters in the sidebar by most commonly used
Schema-level objects in the graph now show fully qualified names
Removed the experimental flag from the application
Wrapping Up
We extend our heartfelt thanks to those who have partnered with us, provided feedback, and contributed to the evolution of the phData Toolkit. Your support has been instrumental in making these updates possible. We encourage you to explore the full list of changes on our Toolkit updates page.
As we head into December, we look forward to continuing this journey with you, providing tools and solutions that simplify and enhance your data experiences. Thank you for being a part of the phData community!
phData Toolkit
If you haven’t already explored the phData Toolkit, we highly recommend checking it out!
Please spend a few minutes browsing the apps and tools available in the phData Toolkit today to set yourself up for success in 2023.
Be sure to follow this series for more updates on the phData Toolkit tools and features.
