Aqua Data Studio for Snowflake

This video will provide an introduction to using Aqua Data Studio version 20 with Snowflake Data Warehouse. Aqua Data Studio is a database IDE and now supports the Snowflake platform. The following list are some terms to become familiar you’re with Aquadata Studio and we can introduce these related to some of the main menus that you see in the background.

Connect Snowflake to Aqua Data Studio

The starting point for using Aqua Data Studio and connecting to Snowflake is to register and the Server main menu. Register Server or the very first icon will show the list of databases that now includes Snowflake. You have a flexible navigation tree where you can set up different groupings.

You can see some different Snowflake environments that I’ve connected to and then you can navigate and explore the different drill down areas so the database is drilled down. Security Management virtual warehouses are all areas in the navigation tree that we can explore. And then there’s embedded right click menus. As you might expect in a mature IDE like Aquadata Studio with many object editors, create alter drop object editors and then easy to use scripting features. So we’ll explore the right click menus.

Snowflake Query Analyzer

And then for working with SQL, either opening up existing SQL scripts or typing SQL. The query analyzer is the Aqua Data Studio for working with SQL or SQL statements and there’s automatic scripting and IntelliSense and a lot of powerful formatting features that you might expect in the query analyzer within Aqua Data Studio.

If you’re newer to building SQL, the query builder is a familiar drag and drop interface for bringing over Snowflake tables. Having Aqua Data Studio automatically generate statements or SQL syntax against the Snowflake environment.

Snowflake ER Modeler

The ERmodeler will allow you to reverse engineer and diagram the Snowflake data warehouse, see the relationships between tablet and generate scripts, generate reports, even comparison features from a model to a live database or data warehouse.

Snowflake Visual Analytics

The visual analytics is available for any data set in Aqua Data Studio. And so now you can access the Visual analytics against Snowflake data sets. There’s easy integration points in different areas for the visual analytics. Through the rightclick menus or the primary Tools menu you’ll see different Ways of invoking the import-export data wizards, some of the powerful scripting features for server scripts or Schema scripts. Also under the Tools menu are some of the powerful Compare features for comparing DDL, a Schema Computer Wizard event name file Compare and side by side comparison scripts.

So let’s dive in and explore these Areas within Aqua Data Studio. When you first install Aqua Data Studio you’ll see an empty database list and then You have the ability to connect or populate your database connection tree by invoking Server, Register Server or the very first icon for connecting to the different databases support of Aqua Data Studio so here. You can see an alphabetical list that now includes Snowflake. As you select a different server platform you can see what areas of information are needed to be populated. This is an area that you can name in a way that you recognize, provide your user credentials, user password, and then what’s the location of that Snowflake data warehouse.

So as you choose different platforms, this Information can change or vary depending on which platform you choose. Here if I was to choose, say, SQL Server, you can see how that changes here.

Go into the server properties there and then see how you’ve named that or customize that. Other features here, like filtering is a benefit. You build your connection, there is a feature to clone that connection. Something I like to do is have a parent navigation connection where I see all my databases and then I create a filtered view with just the databases that I’m responsible for, just the data warehouse environments that I’m responsible for. You have that flexibility of cloning a connection and then going into the server properties and perhaps setting up a filter for yourself for just certain databases. Here you can see in this connection I’ve set up a filter to just show these three databases aqua, DB, Ordering and Snowflake. So you have that customization capability and You’re encouraged to explore, right, if I expand or collapse the different database.

Platforms I’ve connected to, you can see some variations in terms of the architecture of those different platforms. For Amazon Redshift, you see databases, management, security. For SQL Server you see databases snapshots management, security. For the Snowflake environment you see databases, security management, virtual warehouses. As you start expanding those areas, you can explore the object navigation tree under the Schemas drill down. There you’ll see your object types, tablet views, user defined functions, file formats, stages and pipes. As I drill into the other areas you’ll see different groupings. Security has network policy roles and Users Management shows managed accounts and shares and Virtual Warehouses shows resource monitors and warehouses.

Database IDE for Snowflake

In these different areas and you’ll see different features like Create, Clone, alter, drop, as you might expect in a mature IDE. You have object editors that I can invoke and some of these I have opened, but if I open up, say a create table, you’ll see that will open up as a tab within the application interface. You have tabs within the navigation and then some windows are standalone. You have a combination of Popout or Dockable windows tabs within the user application. Some of the primary windows are standing or floating windows. The Query Builder, the Visual Analytics and the Idera Mother, those are examples of standalone or floating windows that we’ll take a look at more closely. Here’s the Query Builder, where you have a familiar draganddrop user interface. Here’s the Entity relationship diagram where you have different ways of navigating very large environments in the data warehouse.

Here’s an environment where I have over 120 tables and 114 relationships. Here’s a smaller environment, let’s say ten tables and 14 relationships, so you have that capability of generating entity relationship diagrams against very large or smaller environments.

Visual Analytics

And then the Visual Analytics is also a popular area within Aqua Data Studio now for slicing and dicing your data and building data visualizations. And now that supports the snowflake environment. You can easily drag and drop data sets and start to tell a story against your snowflake data environments.

You’re encouraged to explore the navigation tree, right click in different areas and you’ll see ways of invoking some of the object editors for creating users or shares or exploring resource monitors or warehouses. Another core area within aquidated studio for working with code or working with objects or building SQL is the Query Analyzer. Here if I just start typing SQL, you have different IntelliSense windows that you might expect in an IDE like Aquadata Studio. You have pop up windows where you can easily just select tables or fields and then different ways of executing one or multiple statements with the different green execution arrows. Here I could parse and parse through a Query Analyzer and see what statements make up that Query Analyzer, or I could execute one or multiple statements and then bring back result sets and take action on that data. You have the ability to save your sequel with the top portion of the screen and some of the icons here that you might expect.

Query Analyzer

If I right click in the Query Analyzer, you have lots of coding features and then the query main menu expands where you can see definition for the different green run arrows from parse to execute one or multiple. Other popular editor features including SQL History, where Aquadata Studio can keep track not only of your SQL against your snowflake environment, but any environment that you’re using aquidative studio against. This is a very powerful tool that has these type of mature features that you’d expect in a database IDE.

Okay, so the Query Analyzer allows you to type SQL and work on existing SQL. The Query Builder has more of a familiar drag and drop user interface where I can bring over tables and then start selecting fields. Here you can see you can automatically write select star statements for me and then I could execute that and bring back my result set, or I could individually start selecting fields or drag and drop to the white space and see SQL being written for me. If you’re a little newer to the SQL syntax against the snowflake environment, the Query Builder can be very helpful and then of course you can take location on your data in different ways. You have the right click features to save your result sets or integration here to the visual analytics. Wherever you have data sets, you see the icon to the visual analytics. This is an area that has grown quite a bit in the user base for Aqua Data Studio over the past six to ten years, where you have easy to use features for bringing your data into your visual analytics window and then easily adding additional data sets.

Here I can connect to existing data sets, see an existing result sets and then bring these data sets into visual analytics. If I wanted to work on new data sets or save these to existing dashboards and worksheets, these are powerful features and then these are shareable artifacts for any Aqua Data Studio user. Here save as would allow me to save this visual analytics workbook. Save as would allow me to save these query builder files and then the save as would allow me to save my entity relationship diagrams.

ER Modeler

So the Er Modeler is a primary Menu where you have forward engineering and reverse engineering. Here if I click on Er Modern New you’ll see Snowflake listed in the database selection and then I could choose the Drag and drop user interface for bringing new tables and defining relationships between tables and then generating the deployment script against the snowflake environment. Here would be forward engineering where I dan define relationships and define structure of tablet. The Tools menu would allow me to generate the deployment script or generate a report. Perhaps if we explore an existing Er diagram that I have opened, you can double click into the entities, actually see the Sequel tab for the snowflake environment. Here Tools generate script would easily step me through selecting which objects and then which tablet or views and related objects I’m building a script for. Here are some defaults and some easy to us preview options. Just few clicks here and here’s hundreds of lines of DDL for generating object structure scripts for the Snowflake platform.

Okay? As I was mentioning, there’s many rightclick features throughout Aquadata Studio as you might expect For invoking some of the popular features under the Tools menu you can drive from the primary Tools menu where you see the import and export data, schema Script Generator, Service, Script Generator.

Object Search and these are available in Rightclick menus as well. If I right click on a connection, there is a shortcut to these popular utilities in Aqua Data Studio that are now supported against the snowflake environment. And then another area that is very Popular and helpful in addition to the Schema scripts, I’m going to invoke these just so you can see the difference in the screen. Schema Script Generator will have my object el tables, views, related objects and then the Schema script generator will have the other areas of security management, virtual warehouses, and so you can see different groupings there. And then similar user interface. Here if I invoke the service script generator, you can see the differences between those two and how those are grouped, so an easy way of generating object scripts.

Moving object scripts from one platform to another. Easy features for managing these files. You can see on the right hand side files Navigator, where I have groupings and ways of recognizing Aqua Data Studio files. Or you can easily set up shortcuts to your own directories. Mount directory would allow you to access different environments that you have directed map path to. Version control integration is an area of interest for many users. You have version control integration from subversion CVS, perforce even Git.

Okay, so these are some popular areas in Aqua Data Studio that now are available against the snowflake environment under Help about you can see the link to.

The user community and there’s lots of resources there to help you become more familiar with what we’re doing in the Cloud and for other environments. Not only traditional DMs platforms, but no sequel and some of the newer data sources like snowflake and interface.