Multi-Platform Database IDE
Aqua Data Studio
Hello everybody. Happy 2022. This is our first webinar for Aqua Data Studio for this year. Looking forward to showing you some of the latest features in Aqua Data Studio and then some momentum with the new things we’re working on and including this recorded webinar. We’ll talk more about that. Right now you should be seeing my full screen and the Aquafold.com homepage. And so we’ll start there. I have the product open and so just under help about, you can see the link to the home page as well. Aquafold.com has been the community site for nearly 20 years and has a lot of good help here. Just to start, here’s where we are right now, right on Aquafold.com. Here’s this Reoccurring Webcast. So we’d like to mix these up. We have a couple of personal themes coming up. This one focuses on just the universal IDE concept. We’ll introduce some of the core areas for different types of users.
Integrated Development Environment
For those of you perhaps new to what an IDE is, that stands for Integrated Development Environment.
And so there are different IDEs. There are developer IDEs and acquisition database IDE. The concept of, say, pop-out windows. You have pop-out windows that you can dock or pin. Here you can see some different ways of organizing. My desktop perhaps workspace in the middle is kind of a theme with IDEs, right? You can see some different coding tabs here that I have opened within my Aqua Data Studio environment. There are also some floating or standalone windows. Aqua Data Studio, kind of an all-in-one. And so the query builder. Visual analytics. ER Modeler. These are floating windows. I have a couple of those open here where you can work in full screen and individually and use Aqua in the way that’s most effective for you. Those are some concepts, what we mean by IDE. Also perhaps here just to expand this or maybe even unpack this complicated introduction line here.
Database IDE Use Cases
Acquisition tool for developers, DBAs and analysts to handle databases and data, right? That pretty much covers everybody that has a laptop, right? Or anybody that works remotely and has to connect to data sets or databases. Those are some of the areas that we like to maybe break down here. During these recorded webcasts, you might have different users that use ADS differently, right? The DBAs might focus here on the DBA drill-downs, the ER modelling, perhaps some of the tools drill-downs where analysts might focus on the data-centric areas, the visual analytics, perhaps import export data and so on and so forth. What’s great about Aqua Data Studio is it stays current. We’re always working on new features, new releases, and then it’s a lot of value, right? So here’s the pricing drill down. Individuals can download a version and install and pay for it with a credit card or perhaps bigger groups maybe reach out and talk to us where if you have large quantities of seats or licenses, there are multi-year licenses, there are discounts for numbers of users you can plug and play here.
Some different ways of saving. We’re in the fourth quarter, so there are all kinds of promotional discounts that you might see here. Definitely reach out and talk to us. You see IDERA in the background there. Idera.com is where you also see a link to Aqua Data Studio. You might know some of the other flagship products from IDERA, like ER Studio and SQL Diagnostic Manager. The partnership that we have with a lot of companies as a major software vendor has really expanded the footprint of Aqua Data Studio. Aquafold.com has a lot of content here, especially the resources drill down. This video or videos like this get posted and then you can even isolate or drill down and choose different types and help the learning curve for Aqua. A lot of collateral here on the community site. And then if we just get started. Or maybe here’s how Aqua Data Studio would look.
Connect Database to IDE
When you first install, you just have a clean layout and then you’d be prompted to connect to a database. This is kind of a clean layout that you might first see when you connect to Aqua. Or here, the very first icon on the top left would allow you to connect to a database or server, and register a server. You see the icon there as well. This is the starting point for Aqua. To connect to your different databases, you can expand this and then build your own folder and server groups. But here. Register server. This would be, say, the starting point.
Universal Database IDE
This is where there’s been a lot of development in Aqua over the years, right? Aqua started with the big platforms in the early two thousands and now has grown to support different environments. NoSQL, Cloud hosted environments, and then of course Amazon, right? So AWS environments.
Mac OS Database IDE
Also as a Java tool, Aqua installs in different OS, including a Mac. That’s one of the other demonstrations later this quarter that will be showing Aqua live on a Mac. That’s a nice separator. We have different users from different generations that might prefer one operating system or another.
Aqua covers the main ones for you with a Windows client, a Mac client, or even a Linux client. You can click on these hyperlinks from here. Maybe if I right-click here, maybe open up an Oracle tab. Here’s a Snowflake tab. This will show a summary of what Aqua does for that platform, right? So, just pointing that out. Again, here’s a list of all the platforms. We say over 40, but probably more than that now with the different versions of platforms. There you can see for Oracle, we’re back nine, I up to 19 C here for say, SQL Server.
SQL Server
Similarly, if I hover over, you’ll see the hover over text there where we go from say, SQL 2000 to 2019 and so on and so forth. The driver tab here is where there’s also a lot of value. With aqua. We bundle the JDBC driver. Connecting is very quick and easy, right?
Connect Server to Database IDE
So here you can see what’s needed. These are customized areas here. First name it, color code it in a way that’s recognizable for you and then your credentials, right? You’d either know these already or you’d have these from your DBA. Here’s where you can see for, say, Redshift, host name, the IP address for them, more of an endpoint URL, AWS region and so on and so forth. If I was to right-click, say on an existing connection, go into Server properties here, I can test that connection, see the two handshake check where first is your network, right, or your VPN correctly, where you’re talking to the server, and then are your credentials correct? Nice easy ways to help isolate if you are having a connection issue. And then these are simple connection files. If you ever get a new machine and you want to migrate your connection files, you just bring these over to that folder in Aqua and it’s very easy to keep all these connections organized.
RDBMS
Now if I expand the tree here’s a folder I created with some cloud environments to Amazon, some local on-premise RDBMS type in terms of MySQL or SQL Server. As you expand the nodes or the tree, there might be some differences for Amazon Redshift. I see databases, management, and security. For my SQL I see databases, management, and security. But then Redshift has external databases. You might see something specific to a platform like that example, and then depending on whether schemas are used, right? If I drill in here to say, my Redshift tree, I drill into my schemas and then I see my object types there. Tables, views, indexes, stored prox for those developers or more coder-centric users. You can see those differences as well, depending on the platform and then management security. This is more maybe DBA areas, right? Users privileges, roles, killing sessions, seeing current activity, and again, that ties to some of these DBA drill downs as well.
Duplicate Server Connections
Depending on your role or your job responsibilities and where you may use Aqua, you can customize this tree. Here’s a duplicate Register Server clone. You could just duplicate a connection and then maybe change the user credentials and then have your different environments in ways that make sense for you. Here what we’re looking at is say a Query Analyzer, right? The SQL editor in Aqua is called the Query Analyzer. You’ll have multiple Query Analyzers. Here’s a clone with content, right? That’s the workspace. Or the SQL editor area here. We’ll introduce the execution arrows, the parse execute all execute current, and those are tied to the main menu and the Query Analyzer as well. Okay, so, just getting started here. Hopefully the audio and visual are coming through okay. If I start to expand some of these areas, you can see different ways of organizing your Aqua artifacts right?
Create Mounted Directory
Here if I was to just right-click and create a mounted directory, perhaps I work as a team and we could hand off work or collaborate that way. Perhaps if you use a OneNote or Google Docs similar concept, version control integration is supported for some of the major platforms. Or you could manually just check in files for any type of version control system. Aqua has the concept of saveable files, shareable files, and then projects lean towards say, team development. Or maybe if you work in a group and do some scripting and automation areas so projects are documented and you can look into those. Right-click here and you can see some of the project capabilities and ways of maybe taking Aqua to another level and maybe some advanced features. If you’re new to Aqua, I’d say dive in and learn the tool. For those of you that are familiar with IDEs like this, projects might be appealing to you.
The Tools menu will expand some of those areas. Right here, for example, I mentioned projects here. Under the Tools menu, you could schedule a project to execute, right? Just introducing some of the scope and diversity of Aqua.
Query Analyzer
Let’s keep moving along here again for say, an IDE, a universal IDE-type theme. Let’s introduce some of the basics here. If I was to say, drill into one of these environments here, maybe I’ll drill into one of these test databases here, or my World database, right-click. And then there’s the Query Analyzer. Or as you have this highlighted, you can see the first grouping of icons is for connecting.
SQL Editor
Here you could see other icons for invoking a SQL editor connection, right? If you’re comfortable typing SQL, that’s great. Of course, you have some of the autocompletion and color coding and type windows, right? Here if I maybe select a field or a table and then execute that with the execution arrow, bring back a result set, boom, I’m good to go.
I can get to this data in different ways. There is comfortable typing if I just right-click here, maybe drill into these tables, and right-click here’s ways of Aqua automatically writing SQL for you, right? Maybe I’m not an expert in how to write SQL. Why don’t I right click here’s a select star, or here right click here’s with the qualified path?
DML and DDL
And then here’s Create. You have different DML and DDL options just from the right-click menu. If I expand the Query main toolbar menu, you see the definition of the green run arrows, right? So you have that as well. If you hover over it, parts would parse through that query analyzer. I can see I have three different statements here, execute all, would execute all of them and bring back your different result tabs. Or I could just select one line or one statement and then there’s execute current.
Customize Database Solutions
I could execute just an individual SQL and then work on that data. So, pretty standard. You have different separators. I’m from maybe an Oracle background with Semicolon, but Go statements are supported. Of course a tool like Aqua has many options, right? So just file options. You have 20 years’ worth of options here you can drill down into, but here’s maybe a big one, the semicolon separator if you like that, you can have that checked, so on, so forth. There’s a search here in the options, so that’s great as well. These areas you might expect in a universal IDE, where obviously a universal means we support different databases. And so there might be variations. Here’s an example, I drill into the scripts, drill down, and you might have customizations for the different platforms. That’s where you can really expand on the scope of what’s gone into the Aqua development.
It’s not just ported from platform to platform. These are customized areas that we’ve built for the different big vendor databases. All right, so maybe let’s keep moving here just to keep an eye on the clock, but perhaps diving into some of the other IDE areas here, I right-clicked and brought up a Query Analyzer.
SQL Query Builder
Further on the right-click menu is the Query Builder, right? So query analyzer type SQL. Query Builder. Maybe I’m newer to writing SQL. I’d like to take advantage of the drag-and-drop white space and then have Aqua build SQL for me again. Here, if I just brought over one table there, right, to select Star, boom, I could run that, get back to my data set that way. So, pretty easy. Whether you’re using an IDE window like the Query Builder or just typing SQL. If you have your own SQL scripts, that’s fine.
If this is a little more basic for some of you, then that’s okay. Maybe inherited a database you weren’t as familiar with. And look how Aqua can help now. It could execute, take its multiple tables and then work on this data set. Okay, so this is again saveable, right? File save as we’ve looked at say, a SQL statement. That would have a file saved as well. We’ve executed SQL either in the query analyzer or here in the query builder.
Export Data
If I wanted to send that data to Excel, I could do that with just a single click, right? Where you have these different IDE windows and the data grid here, you can see some similarities. Right? There’s an Excel icon above the data set in the Query Analyzer. Here’s the Excel icon in the Query builder. If I wanted to see this data in Excel, I could just click and view it there.
Here are ways of maybe highlighting data. There are different ways of bringing that back into Aqua. Just covering some of the basics here in terms of the Query Analyzer and Query Builder. So Server, Query Analyzer, Query Builder. Those are some themes. And then work. Taking action on your data might be the next step, right? There’s the data in Excel if I wanted to see it there. Here I could highlight my data, right-click there’s, say Results, and maybe a more traditional wizard where okay, I want to save this as a delimited file. I want to create insert statements. I want to send that to Excel as a directory. That’s fine. Here is maybe the next generation of functionality for working with data rather than maybe going to Excel and building your Pivot grids and charting from Excel.
Visual Analytics
Visual analytics has grown quite a bit over the years and Aqua has easy ways of showing that.
Some of the BI tools have features of dimensions and measures and charts and graphs and then worksheets and dashboards those fall under the visual analytics in Aqua. Some users might maximize this window and this is where they’ll spend their time, right? Here I could say data, connect to Data. Hereare the queries I just executed in Aqua, right? This might be helpful for some of you. I could choose, say, a query in the cloud and then start to tell a story here with this environment or this window in Aqua, right? Visual analytics would open up a new window and I can start there with the connected data or there’s the dropdown arrow. This would allow me to add this data set to say an existing workbook, right? It’s a similar way to get to the same area here, but maybe let’s keep moving.
Here if I said new worksheet, then this would allow me to say open up or bring over my dimensions and measures and build some kind of a chart or graph here with that data set, right? Here’s, say a pie chart here’s, say a bar graph. So maybe I’ll try something different here. So here’s a drop-down, right? Maybe I’ll switch to a pie chart here. Maybe I’ll bring over some of these values, right? Here’s my profit value. If I hover over here, drag dimension or measure here. Dimensions are non-numerical, measures are numerical, right? Here’s my profit value just in a pie chart. If I maybe drag and drop my state into the color box, this could create a chart for me that way, right? That might be a little more colorful or helpful. Here’s undo and redo so I can go back. Here’s the geo box, right?
Maybe I’d like to create some kind of a map of where I’m making sales or where there’s profit. From here I could right-click and take action. I could say maybe duplicate this worksheet. And then maybe I’ll just highlight. Say North America. Here I could say keep only. Now here’s a region and say the West Coast or North America. Different choices here to tell a story and then bring that into perhaps you have a presentation to your new boss, right? File export, you could export this or maybe post this in case you have some kind of web browser environment and you can post an HTML, some of these data sets and so on and so forth. Okay, so visual analytics, very exciting area. Again with the all-in-one and Aqua for a single license, you have all of these areas. And so it makes it very convenient.
Database Administration
Maybe this isn’t an area that you need to work on, right? Maybe you’re a DBA and you focus more on say, these DBA drill downs, right, looking at performance storage transactions. Maybe you might realize, oh yeah, what, our data analyst team needs this. That’s how you can leverage some economies of scale. Perhaps you have different users of Aqua using Aqua quite differently. All right, so maybe let’s keep moving there with some of those themes. If I’m a DBA, right-click here’s the Tools menu and the DBA tools. Those are invocable from the right click. When you drive from the main menu, Aqua will say, okay, what’s the database you’re working on? Right? If I say Tools, import data, then Aqua will say, okay, which connection are you using? You right-click, maybe a little easier and then that would step into a wizard. So here’s say the import data wizard.
Import Data
This would then open up a tab. And now I could undock this, right? Floating. Now here’s an easy-to-use five or six-step wizard to get this Excel data into a database table, right? It’s available for other users, right? You have some of these easy-to-use features here, save statements here, and maybe preview. Just with a couple of clicks, you have the hover over. Here are hundreds of lines of insert statements that are savable and rerunnable. And that’s really a theme for Aqua. It’s a very nimble tool, powerful tool, single toolbar, and lots of right-click menus, saves you time and the right price point, right? You get a lot of value for Aqua which saves you a lot of time compared to multiple freeware tools. It’s a cost-effective price point compared to some more expensive tools. Okay, so maybe a couple of other themes for DBA.
ER Diagrams
Right-click here. Here’s reverse engineering. So tools. ER diagram. Generator. Now this is just for tables and views, right? If you need enterprise data modelling, of course, ER Studio would be the tool from IDERA that I would like to recommend. Here is maybe for some just quick diagramming, maybe quick scripting to generate script. I could script out these objects, have those in perhaps a version control system or maybe make this available, right? Tools generate HTML reports. Maybe I need to just post this and tell the analysts, don’t call me, just check the link and you’ll know what tables to drag and drop into the query builder, right? Everybody’s working a little more effectively.
Data Comparison
And then here Compare features, right? Here maybe I have a more complex data model. If I wanted to do some comparison, you have Compare here, compare with model, compare with database. This brings in some helpful features of comparison that maybe leads us into the Tools menu.
Tools Menu
Where here okay, these are equal. This is new. On one side, this is different, but in both, so on and so forth. Here’s another one of the all-in-one areas, right? Obviously, the IDE connects to all these different databases with the Register Server, then the Query builder, visual analytics, maybe for the Data-centric user, then the DBA, the ER Modeler, or some of these drill-downs, right? Schema script generator. Service script generator. These areas that maybe a DBA would be very familiar with. Schema script generator. I can easily script out all my DBL, all my objects. Server script generator. Here’s Compare Tools, where we just showed the model Compare. Here’s Schema Compare. Data Compare. If I launch these from the Tools menu, you choose the left and right-hand sides, right? Or flip-flop. Those may be a little more time-saving just to multi-select from the tree.
If I just multi-select here and then right-click there, boom, it is a Schema Compare and saves some time. Here hit Compare and I can see what’s different within a few clicks. Maybe catch something that didn’t make it over or see what’s missing between one side and another, right? Okay. These objects are both different. These are only one side. These are only on the other side. Okay. All right, so maybe just to keep an eye on the clock here, the Tools menu has added some other more powerful features that might be appealing. I mentioned the compare tools. They added data. Compare wizard. That’s for Data Compare and Synchronization, automation continues to grow. And users asked for that. We have scheduled just a Windows task. And then there are scheduling scripts. For the developer, we have Debugging for some of the major RDBMS platforms. We added unit testing for Postgres and SQL Server.
Data Masking
Data Masking is kind of neat, just an easy way to hide data. If you run a query and maybe it has some sensitive information, you can mask that and then provide that data set with some of the data hidden for certain users. It’s just within the UI of Aqua.
FluidShell
There are other great features over the years. One of the neat ones for DBAs is FluidShell, right? Where there’s a shell utility listed for all the platforms, where you have command line features. Right? Some users can really get a lot done just for the command line and typing. We like to show that, but it’s really flexible here. I mentioned some of the collateral here for you. Here if I go in here to the online Wiki, mentioned some of the searchable topics, and some of those we just covered. Right? Query Analyzer, how do you use the query analyzer?
Database Administration Tool
The different themes there, so on and so forth. Right? Query builder, visual analytics, maybe DBA tools. Right? Database administration tools, there are compare tools, scripting options. So very helpful. Online Wiki. Some of the shorter recordings are easy as well. Right? Some of these are ten minutes, some of these are five minutes. And so look for these to continue. The team’s expanding. Of course, some of you may receive communications from Alison. Allison’s team is expanding. I think Sam Hutton might be joining. We have Neil and Tim. A lot of momentum from the Aqua side. This is how we keep current, listen to our customers, and we want to partner with you, and let you take advantage of different relationships that Aqua has with different product lines. Of course, IDERA being our parent company, you can really leverage some economies of scale. We have data warehouse automation, data lake functionality, and then tying those together and really talking to the same people within your company that have to manage all these different environments.