How to Auto-Generate Snowflake SQL

Snowflake is a data platform and data warehouse that supports ANSI SQL, which is the most widely used standardized version of SQL. This means you can harness Snowflake SQL to do all the usual stuff with your data — like creating, updating, inserting, querying, and deleting it. Snowflake SQL also has some nifty Snowflake-specific features like data sharing, streams, tasks, and user-defined functions.

But let’s face it, writing SQL code by hand can be a drag, and it’s super easy to make mistakes, especially when dealing with complex queries or massive datasets. That’s where a tool that auto-generates SQL code based on your data and needs would be a must-have. In this blog, we’ll dive into how you can use Datameer, SnowSQL and Snowpark to auto-generate SQL for Snowflake like a pro.

What is SnowSQL

To get started with SnowSQL, install it on your local machine or a server that can access Snowflake. You can download the installer from the Snowflake web interface or their official website. Once you’ve got SnowSQL installed, just type snowsql in your terminal or command prompt. You’ll be prompted to enter your login credentials and the name of the database, schema, and warehouse you want to use.

Executing a SQL statement in SnowSQL is a breeze — just type it in the prompt and hit Enter. You can also use some special commands starting with a backslash () to do various things like listing objects, changing contexts, displaying history, or exiting the shell. For instance, \dt will list all the tables in the current schema.

To auto-generate SQL code in SnowSQL, use the \generate command.

Give it a table name as an argument, and it’ll whip up a CREATE TABLE statement with the right columns and data types based on the table data. For example, \generate my_table might generate something like this:

CREATE TABLE my_table ( id INTEGER, name VARCHAR(100), age INTEGER, gender VARCHAR(10), salary FLOAT );

You can also use the \generate command with a query instead of a table name.

This will create a CREATE TABLE AS SELECT statement with the columns and data types based on the query result. For instance, \generate SELECT * FROM my_table WHERE age > 30 might generate something like this:

CREATE TABLE temp_table AS SELECT * FROM my_table WHERE age > 30;

The \generate command is super handy for creating tables quickly without manually specifying columns and data types. But it has some limitations, like not supporting generating SQL code for views, stored procedures, functions, or other objects. It also doesn’t handle generating SQL code for complex queries involving joins, subqueries, aggregations, or other operations. That’s where Snowpark comes in.

What is Snowpark?

Snowpark is a cool new Snowflake feature that lets you write data transformations using familiar programming languages like Scala or Python instead of SQL. With Snowpark, you can create user-defined functions (UDFs), stored procedures (SPs), or scripts that manipulate data in Snowflake using native data structures and APIs.

To use Snowpark, you’ll need a Snowflake account with access to a virtual warehouse that supports Snowpark. You’ll also need a compatible version of Scala or Python on your local machine or a server that can access Snowflake. Download the required libraries and dependencies from the official website.

To write a Snowpark program, start by importing the com.snowflake.snowpark package and create a Session object to connect to your Snowflake account. Then, use various methods of the Session object to access and manipulate data in Snowflake.

For example, check out this Snowpark program written in Scala that creates a DataFrame object, reads data from a table called my_table in Snowflake, filters rows where the age column is greater than 30, and writes the result to another table called filtered_table:

import com.snowflake.snowpark._ // Create a session object that connects to Snowflake val session = Session.builder.configFile("snowflake.conf").create // Create a DataFrame object that reads data from my_table val df = session.table("my_table") // Filter the rows where age > 30 val filtered_df = df.filter(df("age") > 30) // Write the result to filtered_table filtered_df.write.mode(SaveMode.Overwrite).saveAsTable("filtered_table")

To auto-generate SQL code in Snowpark, use the explain method of the DataFrame object. This method returns a string with the SQL query equivalent to the data transformation performed by the DataFrame object.

For instance, filtered_df.explain() might return something like this:

SELECT * FROM my_table WHERE age > 30;

You can also use the show method of the DataFrame object to display the result of the data transformation in a tabular format. For example, filtered_df.show() might display something like this:

The explain and show methods are great for debugging and testing your Snowpark programs. However, they don’t actually execute the SQL queries on Snowflake. To do that and perform data transformations on Snowflake, use the write method of the DataFrame object, as shown in the previous example.

Snowpark is a powerful tool for writing data transformations using familiar programming languages instead of SQL. It also supports generating SQL code for complex queries involving joins, subqueries, aggregations, or other operations. But it has some limitations too, like not supporting generating SQL code for views, stored procedures, functions, or other objects.

It also doesn’t support generating SQL code for some Snowflake-specific features like data sharing, streams, tasks, or user-defined functions. For these cases, you might want to use SQL directly or try another tool like a graphical user interface (GUI) or a code editor.

Datameer: The Chill and User-Friendly Approach

While SnowSQL and Snowpark have their perks, they still require you to get your hands dirty with some technical stuff and coding. Datameer, on the other hand, is the buddy who takes care of everything for you. No coding needed, just a user-friendly environment that makes working with Snowflake a walk in the park.

It’s an awesome data integration and transformation platform that connects to different data sources, performs complex data transformations, and auto-generates SQL code for Snowflake without making you write any code. Plus, its interface is so user-friendly that even SQL rookies can feel like pros.

Getting Started with Datameer

Setting up Datameer is a piece of cake. First, you’ll need to connect it to your Snowflake account. Just follow the on-screen instructions, and you’ll be up and running in no time.

Once you’re connected, it’s time to let Datameer work its magic. You can visually create complex data transformations using its super-intuitive drag-and-drop interface. Datameer supports a bunch of data transformation functions, so you can go wild with your data manipulation tasks.

And here’s the best part: as you create your data transformations, Datameer auto-generates the corresponding SQL code for Snowflake in the background. You don’t have to worry about writing any SQL code yourself — Datameer has got you covered.

Going Deeper into Datameer

Alright, let’s see how Datameer makes your life easier. Imagine you want to join two tables, filter some rows, and then aggregate the data based on a specific column. Here’s how you’d do it with Datameer:

• Connect your tables: Just drag and drop the tables you want to work with onto the canvas. Datameer will display a visual representation of your tables, showing columns and data types.
• Join the tables: To join the tables, simply drag a “Join” transformation from the list of available functions and drop it onto the canvas. Connect the output of the two tables to the Join transformation, and specify the join condition using the user-friendly interface.
• Filter the rows: Drag a “Filter” transformation and connect it to the output of the Join transformation. Now, you can easily set up your filtering conditions using the intuitive interface — no need to write any SQL!
• Aggregate the data: Finally, drag an “Aggregate” transformation and connect it to the output of the Filter transformation. Choose the column you want to aggregate and the aggregation function you want to use (e.g., sum, average, count). Voilà!
• You’ve just created a complex data transformation without writing a single line of code.
• Auto-generate SQL: As you create your masterpiece, Datameer is working hard behind the scenes, auto-generating the SQL code for your transformation. You can access this code anytime and use it directly in Snowflake. How cool is that?

Wrapping Up

SnowSQL is perfect for those who love the command-line approach and are comfortable working with SQL. Snowpark caters to users who prefer writing data transformations using familiar programming languages like Scala or Python. And finally, Datameer is the ideal choice for those who want a user-friendly, code-free approach to auto-generate SQL for Snowflake.

References: Snowflake SQL Made Easy: 101 Guide — Learn | Hevo (hevodata.com)

Getting Started With Snowflake SQL API

New SQL tool for Analysts on Snowflake: Datameer — Datameer

Managing your Snowflake Integration — Datameer User Guide