Aqua Data Studio Features
Aqua Data Studio is a product that’s been on the market for nearly 20 years and has added databases and features, and functionality along the way to help you as a database professional.
Aqua has supported some of these traditional installations. Now, as amazon has hosted these, we’ve added oracle SQL Server, MariaDB, MySQL, and PostgreSQL in the Amazon RDS environment.
just some new features here that we’re always adding and always like to show is that we’re staying current again. The database list has grown in terms of over 30, and then there are different configurations here as well. Just highlighting in red the amazon platforms are added recently. Prior to that was snowflake InterBase. We’re adding the MongoDB atlas. We have MongoDB. You are just staying current and adding platforms as users demand.
When we dive into the connection window. You can see the variations with the mouse over. just a screenshot here showing you where you can see different configurations of a certain platform, just in general, if you’re brand new Aqua Data Studio, it’s a time saver productivity tool.
It improves your accuracy, reduces errors, easy to use, and has a low footprint in terms of price point. so those are all good things. really for different users, different types of roles, right? here on the product page, we position aqua as a tool for developers, administrators, and now the analysts have really dove into the visual analytics portion so you might have different users that use the product in different ways, right? and that’s fine.
Aqua Data Studio for Mac, PC, and Linux
Windows traditional client, but also has a mac install Linux installed. It’s a java tool that has flexibility in terms of where it installs. Again the range of different environments that you can connect to in terms of database support.
Here’s an example of types of users, it’s just from our current list of job titles. really, anyone with the word data in their job name or team name would benefit from Aqua Data Studio. some users might use aqua more frequently than others here are some terminology within the main menus that we’ll talk about just maybe to tie this to the product itself. some of the main menus, when we talk about connecting, we use the term register a Server, so we’ll talk about that from the Server main menu, then exploring the navigation tree.
Database IDE
You might be familiar with different trees and navigation styles. you have flexibility in terms of the database ide, look and feel of aqua in terms of left or right or docked or pinned. all that flexibility that you’d expect in a mature tool.
The query analyzer is where you can either type SQL or open up existing SQL that you might have, bring in. SQL scripts, parse through those, move quickly, format, duplicate, execute one or multiple SQL, all the types of features you’d expect in a major SQL.
Drag And Drop Query Builder
Editor query builder really, the next few areas might be more data focused, so maybe less DBA, more analysts. The query builder is the drag and drop way to build SQL, right?
You drag the table to whitespace and then aqua can write SQL code for you.
Table Editor
Table data editor lets you insert, remove. rows, edit data cells within an excel-like grid, and then the visual analytics. this is grown quite a bit, right? here on the website we position a database ide and visual analytics. it’s a front end for database access and then the drag and drop visual.
Analytics for charts and graphs. we’ll look at some examples there, but that’s obviously a popular area. you might know some other tools in this space like tableau or power bi. you have that all in one here in aqua, which is nice and really a need for some users.
Aqua Data Studio Tools
Import, export, schema, sync, schema, compare, file compare, those are drilled downs in the tools menu or the right-click menus. day to day productivity areas for file comparisons, dips, even synchronizing of schemas, moving data, generating DDL, generating schema scripts or service scripts, things you’d expect in.
A mature tool relationship diagramming. so, er modeler is not enterprise-wide, but if you need to look at tablet and entities in the diagram, you can do that pretty easily with aqua. so there’s other idea tools that might.
Be more expensive or enterprise-wide in terms of VR studio WhereScape gate 3d, so those are other more robust modeling tools.
But if you need some quick table diagramming, that’s easy to do in aqua. and then administration really is throughout this list that i just mentioned, just connecting and looking at sessions or SQL, obviously, database administration areas, creating databases. here, the management security drill down right into permissions and roles, these are all DBA areas just last on the list here, probably tied to the DBA tools menu and just wanting to introduce those areas. but these are DBA focused, right? between the database administration areas, if you have those responsibilities, aca is very strong there, of course, as well, all right, so everything we show is usable and downloadable, right? Just from the website, you’ll see download here, start here, so you can download and kick the tires on all these features that we’re about to show.
Universal Database IDER
Maybe just to start with kind of a clean layout. when you first install aqua, it will look somewhat like this, where you have the ide type concept of dockable or pop out windows on the left or right. here you can see my database navigation tree, where i’ve connected to many different environments. you have flexibility in terms of how you’d like to build this tree and then you can move these pop out windows around left or right, top, bottom, split, docked right, things you’d expect.
On the right hand side is my files navigation, i can drill into my Aqua Data Studio files, my file system, and maybe even my version control system. so you have easy ways of setting. up map drives or pads, or if you work with a colleague, maybe you hand off operative studio files to other users. so, of course, you have that type of flexibility here within the product. you see visual icons signifying certain exclusive or native aqua dude studio files.
The middle area is a workspace, right? here i have some compare different windows that you might be using. those are invoked in different ways throughout the product. so we’ll talk about that. and then you also here, i just. ran a SQL statement just to give you a look and feel here.
Visual Analytics
So the visual analytics we mentioned. that’s a floating window, the er monitor is a floating window, the query builder is a floating window. you have the flexibility here in terms of working with a doctor pin window or a floating window. that gives you the flexibility to use the tool how you’d like. these are all saveable or shareable artifacts, right? file save as you can save any of these or share these. that ties into the file system navigation that i was mentioning on the right hand side of my environment.
Okay, so maybe just to back up, maybe I’ll just open up a query analyzer so we’re not distracted. here on the left hand side, the very first icon or Server register Server is how you get started using aqua. you can see an alphabetical list of databases. as you choose a different platform, you can see what area of information is needed here to be populated there’s bundled JDBC drivers for all these different platforms. and then as you select a platform.
You can see how information to be populated might vary. right? here for DB two or say, SQL Server, I’ll start to see maybe some Microsoft authentication, right? so here’s SQL Server authentication windows. single sign-on here. for oracle, you have some different choices. if i’m just using the oracle JDBC driver, then i see what’s my oracle connect as my connection type. so then you can explore these.
Like snowflake would just have your host database, warehouse and credentials. MongoDB, you’d see your hostname, your database. a lot of developments going on in this window.
Maybe if i right click on an existing connection going to serve properties, you can see, you can just name it in a way that makes sense for you. color coding. you can see at the top there, i have different tabs to keep track of where i’m at these are very easy to manage or clone.
I could just right click there’s clone. you have the concept of folders, right? i could create a folder or Server group and then organize my connections that way, right? here i have a folder where I have some of my DB two connections. here are my cloud Servers, here’s my rds environments.
You have flexibility in terms of how you like to set up your navigation tree and what’s available or what might be up or down or available for you in different ways. those are in a history for you.
Database Management
As i navigate my tree in my SQL environment, i see databases management.
I could drill into my databases, see my databases, and then see my object types. here in my SQL, I see my tables, constraints, procedures, stored, code functions. if i was drilling into snowflake, then i see databases. security management. right. if i drill in here, i see my schema drill down, right? maybe snowflakeDB, two, oracle, you’ll see schemas used, maybe not so much in Microsoft SQL Server, you see databases drill into databases and you see your object types that way.
Just exploring the tree starts to reveal different areas. if i was to right-click here are areas that are found perhaps only in the right click menus, right? so there’s a create object editor for.
Creating A Database In Sql Server
Creating a database in SQL Server. that would open up a tab here and i could step through the tabs for creating a database. maybe i was more at the tablet level, right? so here i joined my tables. right click on a table. there’s create there’s alter drop table. this should be familiar in terms of a database ide. alter table would open up an object editor for that table, right?
Here’s a create database in SQL Server. here’s an alter table in SQL Server. depending on where you are, you have that flexibility, right? if i was in my SQL here, drilling into databases or tables here, right-click, same concept right there’s, create table in MySQL.
Okay, so you have extensive object support and then perhaps if you’re more interested in scripting the tools drill down, Server script generator, schema script generator, these are the areas that you might be responsible for, right, so if I was to maybe just choose one of these windows just to populate. if you wanted to generate object scripts for multiple objects at once, you don’t have to drive from the tree there’s, right-click or tools menu drill-downs.
Okay, so we’ll dive into that shortly, but just letting explore the tree, right click on the tree and then maybe just to keep moving along here a core area within the tool after connecting and registering a Server would be the query analyzer, right? so from the Server main menu, you can really see your starting point using aqua. let me open up a query analyzer.
You drive from the main menu, you might be prompted, okay, what’s. the database you’re talking about? otherwise, if you right click, you save some shortcuts there. here, i just opened up a query analyzer against MySQL database. if I right click on a SQL Server, there’s a query analyzer, that would open.
Query Analyzer On MySQL Databases
Up a query analyzer on this, MySQL SQL Server database. then i could just start typing SQL. perhaps i’m familiar enough with SQL and syntax where i could just start typing code, and then you see IntelliSense and pop up windows the way you might expect. you could just execute SQL with the green run arrows, right? you have different execution arrows that might be helpful. they’re all defined here under the query main menu, right? there is just a single statement that’s pretty straightforward. let me maybe bring in multiple statements and then talk about executing against multiple tables or multiple SQL at the same time. right? so here’s a different table. i’ll just add that. now here, if i expand the query menu, parse would show me all the SQL in that editor. there are a couple of statements, and then i could run either individual or multiple. right?
Execute all would execute multiple statements, and then I have my result tabs in the bottom portion of the screen. okay, so bit of a foundation there, or starting point, it’s just connecting the query analyzer is where you can open code and look at code in different ways, right? maybe i had some more complex code if i opened up a query analyzer. maybe i’ll look at explain plan just to maybe start to show some of the areas. you can use a query analyzer in different ways depending on your job role. right, so here, if i run a statement, i have my results set.
Sql History
Now in the SQL history, right? so query SQL history, here’s a history of SQL i’ve executed. there’s some SQL Server code, there’s some tb, two code of executed. so this is something you might expect. aqua is keeping track of SQL that you’ve executed in different environments. you have a history, go back to that. i have mine wrapping to an archive. after 100 statements, mine writes to an archive.
So these are different bells and whistles you might expect formatting cloning. so i could right click here format. if i say format, current statement, format script, you see different options, right? lane breaks, case indentation, all that kind of good stuff is here as you might expect if you’re dealing with very large scripts let me just open up another query analyzer and just open up a script that has many lines of code within it or maybe exports. for example, right here you have to scroll ahead. this is kind of neat where i can just mouse over further in my editor and then just see statements on the fly. lots of flexibility in the query analyzer that you might expect.
Okay, and so you have different tabs open, right? you have multiple query analyzers, just right click query analyzer, or here, i could right click here and say lane window. and then you’ll have different areas of workspace at the same time. here you can see a lot of different things I’m doing at once. that’s what’s great about awkward, really lets you get things done.
Sql Scripting
Okay, so maybe just keeping an eye on the clock, if you’re a little newer to writing SQL, that’s fine. here i was just typing SQL if i navigate in the tree and then just start right clicking on objects, aqua can write code for you right so there i wrote a select statement on this product table.
What if i just right click on this table, right? i’ll click within the editor, right click and then here script object to window. to window just means the query analyzer. and then here you can see i.
Okay, so lots of flexibility there definitely. right click on objects, you have scripting features, right?
Talk about some of the compare features such as just comparing the clipboard. so here’s just copy compare. there is some more text compare diffs. there’s the wizard left and right hand side schema compare schema synchronize MySQL data of flexibility here that you might expect in a code editor like Aqua Data Studio.
Database Query Builder
Right click here on a database query builder and now here’s a drag and drop interface for me to build SQL against certain tables, right? here if i want to bring over say dan orders table or products table if i have referential integrity between them, i’ll see that and then i could run that SQL, bring back my results set. let me make sure i’m going against some valid SQL.
Here a valid table data, right? depending on which direction you’re going in, you can double click or select these. maybe i want my category id, category name, product id price so aqua can build the SQL ways that make sense to you. you have this and the ability to execute the SQL. save this, share this file. save as would save this as a qbw file. you can recognize that in your navigation tree on the left where you see these extensions and then different visual icons for those that might be helpful, maybe a little more junior for some users depending on your experience level. and then you can save this or bring the SQL back in different ways, or maybe share this in one node or google docs for certain users, all right? of course you can go from there to say the visual analytics, right? so here’s the icon above the Datagrid.
In the query builder, there’s a visual analytics icon. here, we had sent a data set to the visual analytics earlier. here if i just say data connect to data, this would show other SQL you’ve executed. here if I wanted to say maybe execute SQL in the query builder, or execute SQL in the query analyzer, you can bring data into the visual analytics from that icon, right?
Here’s the icon to visual analytics or visual analytics. new would then say, okay, what’s the available data sets? so connected data, it would show the SQL you’ve executed, right? here’s this order, SQL, you see that order SQL here if i just say load data set, that would bring this data into the visual analytics window. or here if i say data connect to data, i sent some data to excel, right?
Advanced Visual Analytics
A tool like this. of course, there’s very intense visual analytics and in depth analysis and aggregates and trend lines. we all see those on the news with charts and trends and graphs and slopes. we’re all becoming more familiar with how to interpret data or tell a story with your data, right?
So maybe just to look at some examples, so here if i bring over, say, a revenue value, and then i wanted to maybe build, say, a pie chart on that, right? maybe I’ll switch to a pie chart, and then you have drag and drop property boxes, right? maybe i want to drop my territory into a pie slice and then label that, right? here’s profit and pie slices in different areas so that i can double click into that. i mouse over, i see some values there where i dan, narrow that down, and then just say, all right, just view that subset of data, right? these are easy things you can do with a visual analytics tools.
So notice it’s developed a worksheet. so you can build multiple worksheets. there’s worksheet and worksheet when you combine worksheets, that creates a dashboard, right? maybe let’s build a couple of worksheets, right? so here, let me add another worksheet maybe, again, maybe we’ll look at let’s san Jose, maybe sales revenue, right? or maybe you want to change this to green for revenue, right? so that’s easy. maybe i’ll sort this, but least profitable and the most profitable, right? very easy drag and drop, as you might expect.
So there’s different tabs and worksheets. if you want to combine these into a dashboard, that’s just dashboard, new dashboard, or on the bottom portion of the screen, the plus sign here as well, right? if i just said new dashboard, notice the worksheets are available, then i could double click on those or drag and drop. now i’m telling the story with my data, right? here’s territory sales, here’s sales by product name. maybe i could share this with decision makers, right? export, you could export HTML or pdf. you could share this whole file as either a static Aqua Data Studio file or a package workbook.
ER Diagram Builder
Right click there was the query analyzer. there’s the query builder, well, here’s the, er, diagram generator. right, as you become familiar with aqua, you’ll find different shortcuts, right? so er modern new would just open.
ER Model
Up a new er model window. saying, all right, what database are you going to choose your platform? and then you have the white space. i could just say drop a table here or drop multiple tables and then start defining their structure here. or maybe create a relationship between these two tables and then define that tool. scripting, generate, document.
These are features you might expect if you’re forward engineering or starting from a new model, but maybe you’re responsible for inheriting an environment or seeing what’s out there or just documenting for other users you might drive from the generate, right? so you might generate or if i right click it’s going to say, what database do you want to reverse engineer?
Again through the navigation tree, you can dive into this in different ways. maybe I’ll just stick with this SQL environment just for the sake of demonstration. let’s see here. okay, so maybe I’ll just drive in here to maybe SQL Server environment, choose tables, views, and related objects like indexes and constraints. so, in this case, maybe I’ll just point to my tablet tables and views. you have that flexibility here’s, about a dozen or so tables. pretty quick for the sake of the demonstration.
This will hit the data dictionary or the catalog and then diagram that for you. maybe you’ve inherited an environment that you’re newer to, or maybe you’re a DBA and you get phone calls about what the data model looks like you could just easily reverse engineer this type of way and maybe post a report for them so they can stop calling you. you can share this as a diagram. you can save this file as just a diagram file, and then of course you have modeling features where i could drill in, look at the SQL for this. i could forward engineer from here and drop a table in here and make changes to this model.
Compare Data
In Aqua Data Studio you have by side comparison divs where you see code different between two environments. here was a memory compare. so compare tools, copy computer you’ll just say, okay, what’s in the clipboard there? i, dan do side by side compare and show what’s different that way the more robust compare wizards, schema, compare schema synchronize.
MySQL data would ask you for source and tablet, right? you can choose left and right hand side in terms of where you’re comparing against, and then that would be a familiar interface that you might expect, right? depending on where you are or what you’re connecting to, you have the side by side comparisons of these object types.
Sql Server DBA
Here for SQL Server DBA, i could right click on a connection or drive from the main menu. maybe i’ll just expand it from the right click. maybe some shortcut tips here, right? so DBA tools, there is a session manager. here’s how i could see active sessions in, say, a SQL Server environment. if i was looking at, say, amazon redshift, then i have the instance manager. this would show me active SQL in my amazon redshift environment so maybe a different look and feel.
Depending on what you’re responsible for. diving into last SQL or terminating a session obviously areas that you’d like to have in terms of resolving a bottleneck, for example, right? but then storage might vary, right? here in MySQL, i can right click DBA tools.
There’s storage manager in MySQL, right? so i see databases, engines, objects, open tables, whereas in SQL Server that might be different, right? here’s the storage manager in SQL Server, where i’ll see my databases, data files and oracle. you’ll see table spaces. in DB two, you’ll see table spaces so depending on the platform, you can see variations there in terms of the DBA drill-downs