MySQL Queries
Aqua Data Studio
Hi everyone. I’m Kirsten from IDERA, and I’d like to welcome you today’s quick chat webinar titled Manage MySQL and MongoDB Queries with Confidence. This webcast will be recorded, so you’ll be able to do it again if you’d like, and the recording will be available in our resources on Aquafold.com in a few days. If you have any questions, please feel free to ask them in the Questions feature in the GoToWebinar Control Panel and we will get to them at the end of the presentation. We are excited to have Lisa WA, Aqua Data Studio Senior Product Manager, here to discuss the best approach for dealing with the growing challenges of accessing and managing data on different database platforms using Aqua Data Studio. With that, Lisa, over to you.
Hello, thank you for joining. I’m going to be discussing, like she had said, manage MySQL and MongoDB queries. I’m actually going to start with a baseline of managing these Mysql mongo database queries because they’re different database platforms with different disciplines. The days of different disciplines for different roles are gone, and multiple disciplines must work together, like when they talk about DevOps and being part of a team for communication and collaboration. The lack of appropriate skill sets can slow the process down because you have various skill sets from your bi who are just looking at queries and managing the data. As to people who are actually writing the queries, now they’re writing the queries. You have DBAs who are really advanced in writing scripts, so there are different levels. It’s rare to have a company that uses a single platform anymore, so companies and It must adapt to the different database platforms.
RDBMS
The considerations of the database are put into play, we’re talking about scalability and complexity. For SQL, like MySQL, which is a relational database, users have to scale a relational database on powerful servers, and the data has to be distributed handling tables across different servers. You want to take in these concepts when you’re thinking about writing queries. As to how that database discipline works, for no sequel, it automatically spreads your data onto multiple servers, and servers can be added or removed from the database at any point in time. Where does that go for the complexity? Well, for SQL, the design of the data needs to fit in tables and rows. You have a discipline, right? And the database structure can be complex. It’s not always, but it can be difficult to handle large volumes. That’s where NoSQL came into play, saying we automatically spread your data onto multiple servers without requiring a defined structure.
Although it says not a defined structure, there are disciplines for different databases and how you need to work, and there are rules that you have to follow whether you’re NoSQL or relational. For NoSQL, it can cache data and system memory, therefore making it faster to retrieve, which is in contrast to the SQL database. Where it has to be done using a separate infrastructure. We’re going to start a baseline because I’m not sure the level of people that are on the call to beginning running queries as opposed. We’re going to discuss what a relational and a NoSQL database are and how we correlate that to writing our scripts. A relational database like MySQL represents our data and tables and rows like I discussed before, and they’re based on a branch of algebra set theory known as relational algebra. They relate to one another. You have primary keys and foreign keys and the data relates to one another and NoSQL databases like MongoDB it uses JSON like documents with the schema.
MongoDB vs SQL
In a relational database you have tables and in Mongo you have collections. The terminology and concepts are different. How you can look at this is that you can take a dotted line from database, what it means in SQL versus what it means in Mongo. You have a database, which is a database in Mongo. You have a table in SQL, which is a collection inside a Mongo. You have a row inside a SQL, and then you have a document, a Python, a binary JSON document that is stored in MongoDB. You have a column and a field. You have your indexes, which are the same. When it gets to in the complexity and the difference in how you’re going to query the databases when it comes to table joins. Inside of MongoDB they added a lookup function, but on prior versions they didn’t have this for embedded documents.
You’re going to query the data different. For your joints, you have your primary keys and in Mongo the primary key is set to the underscore ID field. I added a link here for SQL to aggregation mapping for your where clauses and the differences between the two. If you’re beginning you’re not familiar with JavaScript, you do not forget familiar with JSON and how you handle these. You can click to the SQL to aggregation mapping and you can look them up. If you come from a relational standpoint, you’re just learning it. We talk about pulling data out of the database, we’re talking about a structured query language and we’re going to give an overview of what that actually means. You have your data definition language for your create, alter and drop statements for your tables. You want to pull some type of data out. You’re going to manipulate your data manipulation language, your DML statements that I’m going to select from this table and I’m going to insert into this table, maybe update a table or delete a table or do something with the data or the collection.
MongoDB Queries
If you’re using JSON, I’m going to grant and revoke privileges to those particular objects. Not only do you have privileges from the database side of what you can access. You also have privileges of what you can get to the particular objects for running your queries or creating your queries and scripts. You have your transaction control languages, which are your commit and rollback. You have a begin transaction and end transaction. When you want to reverse that, you kind of roll back your statements. In MongoDB you can’t do multiple rollbacks within the process. I took this for Mongo DBA database site and I also put a link down here which is a blog to discussing MongoDB joins, because joins was added in three two. If you’re on a prior version, like we support version two six, you don’t have this particular join. If you’re moving three two forward to the latest versions three six, then you’re going to do some joins and you’re going to make sure that you acquire, you keep these in mind.
There’s really no complex transaction and no constraint support. Meaning if I do this, else do this, depending on what your parameter returns the value of. If you look at this screenshot, you’re going to have a left collection and a right collection and you’re going to set your parameters here for doing some type of join and aggregate of where clause or whatever. You’re going to look up the values and you’re going to specify where you’re getting the values from the right collection, the local field, the foreign field. I kind of consider that like an outer join like you do in the relational database. Here’s some specifications on how you want to group these and what you want to do with them. Again, you can go to the blog discussing the series. This is the first part in the series and there’s three parts to that series.
This is what a no sequel or MongoDB database does. It does look ups instead of queries. The typical Lifecycle query would mean that you can think of the engine running behind the scenes to parse what you provided to optimize it. Hopefully you optimize those queries. We’re going to talk about some tool sets that we can give you to make sure that you’re optimizing your queries and then go ahead and execute those queries. And what do you get from those? You also have scripting languages versus programming languages. What are the differences between the two? For you people who are new to writing scripts, all scripting is are to automate certain tasks in a program, their automation, pulling data out, extracting or inserting data from a data set. They’re less code intensive as compared to traditional programming. They usually don’t require some type of compiling. Examples of typical interpreter languages where they have the compiling or not compiling are JavaScript and Aquascript.
JavaScript Editor
We do offer a JavaScript editor inside of Aqua Data Studio and we’re going to talk about that. We deliver a proprietary aqua scripts for doing some so giving you some templates and doing some routine tasks that we think you might be using, whether you’re a developer, a DBA, or somebody who’s just pulling data out for analyst. You have our hypertext processes, for example, BB scripts, Python and R. We use actually R for a visual analytics piece, for doing some scientific notations. I have some examples of that inside once we get into the product. I shared a blog, which I thought was interesting because as you get the different disciplines of all these people on a particular team who are trying to do their job and pull data out, you also have the hybrid environments of, companies have cloud databases, no sequel databases, relational databases, and they have these environments in these complex environments and how they need to manage it.
MySQL
This blog is done by Database trends and it talks about single versus multiple database use. And I thought it was pretty interesting. You guys can click on this blog to get more information moving forward. Also in MySQL, you have procedures in UDS, a procedure is a subroutine, like a regular scripting language and it’s actually stored in the database. You have user defined UDS that they’re referred to extend the MySQL functions that work. They’re built in like a catinade and do some things that you have those built-in ones, but you can add on top of that what you should be doing with your SQL statements to invoke a function. The return value can indicate the type of function that you’re trying to return. We’re not going to get into detail as much of that, but I will show you what you can do with some of those moving forward.
Note that MongoDB has no procedures and the closest thing they do has a stored JavaScript. I’m going to let you know what you can do with your JavaScript because we have a Java sequel editor or SQL Editor inside of Aqua Data Studio as well. We have some polling questions we’d like for you to do. I’m going to hand this over to Kristen.
Thank you, Lisa.
Yes.
To get to know our audience here and to make our chat session a bit more interactive, we have a few questions for you. Our first one is what is your role in your company? Are you a database administrator, a database development business or data analyst, an It manager or other? We’ve had a few responses come in. I’ll give people a few seconds to answer and we’ll move on to our next question. Our next question is which database platform do you work with the most often? A relational, so MySQL, SQL Server, et cetera, no sequel, data warehouse or relational and no sequel together or other. The answer will be right in the poll feature. GoToWebinar give everyone just a second to vote on this one. We’ll move on to our third and final question. The last question we have for you for the session. What is one of your primary tasks?
It database management, so, migrations capacity usage, et cetera. Database administration, security, database schema or er diagrams, database query creation and execution, or data analytics? Thank you all for voting in that, I’ll hand it back over to Lisa to continue our session.
Aqua Data Studio
Now that we kind of have a baseline of what database and what are the things that people thought about when they were implementing the database, what type of database we’re doing, how does Aquadata Studio help you with that? What is Aquadata Studio? Well, Aquadata Studio is a universal IDE and a visual analytics tool that works on and supports over 30 database platforms, whether relational, no sequel, or cloud databases. It allows you to develop and manage your databases, access, import, and export and visually see your data and analyze that data. It works on all major operating systems, whether Windows, Mac OS, or Linux. I talk about the 30 plus database platforms, we’re focusing on MongoDB and MySQL. There are different variations of my sequel that we support today. Whether you’re using My sequel, amazon Cloud or Google Cloud or whatever, they all work the same way with you just have to keep the disciplines of the database.
The particular sets inside of Aqua Data Studio, they all look the same, the forms look the same, everything looks the same. We’ll get into that of how Aqua Data Studio works. What are the primary features of Aquadata Studio? We register your server and your databases and we build the Database Navigation Explorer tree here on the left. It gets really advanced on what we can do with this Navigator Explorer tree. We have a SQL Editor or you can think of as a SQL editor. We also have a Query JavaScript editor for MongoDB for getting into using your JavaScript as well. We have a query Builder to automatically build your queries for you to help you with writing that sequel. Or if you just don’t have time to do it, you want it to be quick, you can use a Query builder to do that. We’re going to discuss that.
We have a Table Data Editor for editing your Table Data upset, update your insert your rows based on what your return values are. We have a visual analytics to explain what you want to do with your data. If you want to analyze what you did in the past versus what you’re going to do in the future, we have some graphs and charts that allow you to do that. We have some tool sets for import and exporting data, and DDDL for bringing data into your database and what we can do with that. The tool sets for schema and data and file compares, what you can do with the file comparison, how you can incorporate to build those into your queries and scripts and your other source code. We have an entity relationship modeling for reverse engineering your database to get a graphical representation. Let’s say for example, you have a new database platform and you’re not familiar with it, you can reverse engineer by connecting that database and it’ll give you a graphical representation.
If it’s a NoSQL database and it doesn’t have referential integrity, then we can infer that for you and to kind of give you an overview. We have some database administration tools for doing instance management, storage management, security management, and statute management on keys for particular database platforms that we offer inside of Aqua Data Studio. These are the highlights and now we’re going to discuss what we want to do is we want to pull the data out. How do we connect to all these servers with one interface? Well, as you can see, this is kind of a busy slide, but I wanted you to know how I work and what I tend to use Aquadata Studio for and kind of give you an overview so you can refer back to this. There’s Three Parts to Aquadata Studio. There’s the servers, which we’re going to get into the connections.
There’s files and there’s projects, and there’s a way to organize your work space. Multiple different ways of doing that. The first part is registering the server. The way I work is I keep my server registrations to the left hand side, my files for my scripts and everything to the right. This workstation, you can move these workspace to the right, to the left, upper and lower. It’s very customizable. What I’m currently doing is in the middle. In the middle I’m going to now build my registration and my server. As you can see on the left hand side, I have over 30 database platforms that we support. I have MySQL highlighted. We also support the generic ODBC and the JDBC Java. We also support connecting to an Excel file as a data source. I’m going to show you where that comes in really handy as we import data and what we do with the visual analytics piece for people who are constantly using Excel.
SQL Editor
Right here I have highlighted the MySQL and inside of Aqua Data Studio. Whether you’re on the tree or inside the registration, it’s context-sensitive to what you’re going to do with that. I have MySQL highlighted and to the right it pops up to the naming convention for what I want to name this particular connection and what type of database it is. I have production listed, but I changed this to test and I take the tab color. I think this is pretty informative when you’re setting up your database. Mine is green. So this is my test environment. And I have a green. So I’m my SQL Editor. When I’m writing scripts, I have my migration path up here in different color coordinates. If I’m on red and I set this to production red, I know that I’m updating inserting or deleting something on a production box.
It’s very informative for me to look right at it, to say, do I want to insert data on a production or do I want to do that in test or my development box. So I use the color coordination. I usually use development for my blue. My test is green. It’s good to go. Pass this test and I move it into production. That’s the way I work. I enter in for my color coordination, all my parameters, I test connection. Everything seems to go great. The minute I click save this particular connection, it’ll add this server to the database servers list over here, and it will also add this connection file for the servers over here inside of my user home directory connections. I can share these connection files with somebody. I can set these up. The only thing we don’t pass is the password for security purposes.
If I’m rolling out this to a large company, then I can say, okay, you’re going to use this connection file that I already created for you, and it’s clean, quick, just enter your password, save it, and you’re good to go. It will populate on the tree. Now, I talked about where it’s context sensitive. I switched to MongoDB. I set up my sequel and now I’m going to set up a MongoDB. I’ve highlighted MongoDB, and as you can see, the right hand side added the authentication. MongoDB has a different authentication purposes to it. I can select LDAP or Kerberos or whatever. However way I want to connect to it’s going to be contact sensitive to the right. Also up here there are tabs that can make this extremely powerful. For just on the connection stream, you have your general filter advanced. I’m going to go through each one of these and what they offer, because when you’re building your scripts or modifying your queries, not only are you doing that in the SQL Editor window and we give your capabilities, but we can also control how you set up this particular environment and have it use some standards as well.
So it can become extremely powerful. I could also mount a script at the time of this connection and email me on certain inserts that I want, insert a certain table and I can look at that table inside the database. So it can be very powerful. As we get into the server registration properties and what we have available for you, we have some highlights of what things you can do. You can filter the catalogs. If I am connecting into a particular database that has 50,000 objects or it’s extremely healed could be Salesforce application or it could be a apps or some type of application. I can streamline what I want to see, I include the filter dialogue in the catalog tables and only see what I need to connect to. I can also exclude what I don’t want to see if I want to. I can also control the folders tab that I have on the right side, only scripts that I care about, or only the directory structure that I care about, I’m going to load.
SQL Editor
I can also turn on. You can see on the right hand side in the advance, I can enable filters to carry on to my SQL editor so I don’t have to load all these objects if I do it in the SQL Editor. I get to the SQL Editor or SQL Editor window, I can have these filters translate to that window as well. It becomes very powerful that I don’t have to pull or waste time pulling information from here. I also have the drivers where I can specify a certain driver. We do native connectivity and we deliver the drivers with you. If there’s a new driver and you want to use that driver, it just came out in between our versions, then you can add it here. You can also set parameters for when you’re connecting into a network and you need a security file or whatever.
You can also specify a certain parameter to do that for you when you’re connecting, so it makes it extremely powerful. One other thing that we allow inside of here, because you have teams that are doing insert, you have different levels we’ve talked about. You could have just analyst, you might not want them to do insert statements if they’re connecting to a production database or update service. You can turn these options on or off and give them this connection file to be stored and automatically it’s set up for them and they don’t have to think about that. You can control what they can do in the Table Data Editor. I don’t want you automatically clicking on Inserts in the Table Data editor. I want you to see your data, but I don’t want you to allow you to do that. You can control what capability you have within here.
SQL Scripts
You can also control what scripts will be executed upon establishing that connection, like I said before, and save it. You can also put in we have command line driven, we use Fluid Shell for doing your unique shell, or if you want to do a command line driven tool, we have Fluid Shell. We also have a mongo shell for you, for people who are MongoDB, and I’ll show that later when we get into that. You have two options that are here and then you have a way of idle committing. You can set this maximum snooze time to be higher or lower depending on if you’re going through a network connection and you’re going through five or six hops and you know that you’re running a big query and you need the snooze time to be longer, or the maximum snooze time, the idle commits you can set and change those.
It can be very powerful when you create in your scripts to commit after each one, or commit after a certain period of time so that it runs faster. There’s a lot of performance enhancements you can do with controlling this specific connection file to the database. There’s also options that allow for syntax changes. Let’s say you can see them. I highlighted the MySQL appear where you have parameters for your auto completion, your auto commit, your maximum results that you want to show. You can specify a parameter script. These are pretty standard. The reason why I wanted to show the difference between Mongo and MySQL is that when you click on Mongo, you have specific parameters to the database where you have the JS grid results showing when you do the SQL Editor and we’re going to get into that you have a tree view that you can see for your JSON moving for those JavaScripts.
That will change based on the database platforms. Like in Snowflake, you have some different ones if you’re doing that particular database, for example, you also have general capabilities. If you guys standardize on making sure that your comments look a certain way, you have double quotes, you have double slashes at the beginning of the comments, and you have code reviews you can automatically specify for on your production database. When you move that code and you check it in from source control and it gets committed to this particular instance with production, then you can set those comments to be that way that it won’t work unless it’s specified. It kind of enforces a particular standards within your company. You can set it. It’s very powerful for doing scripts and writing your queries and helping you with your query. It can be pretty advanced on the setup based on the database platform that you’re connecting into.
MongoDB Queries
We talk about queries and more queries, we’re talking about different kinds of queries and what we can do with those. There’s a single query, there’s a multidatbase query that we allow you, or an instance query from within here. For people who are advanced multiple queries, to be safe, we’ve talked about scripts or a certain set of tasks and what we need to do with those tasks. We’re going to COVID each one of those options. I do want to highlight one tool that we offer.
Import Data
We have import and export data. We have many tool sets and I’ll cover those later on. The reason why I highlight the import is because that is always the baseline for getting to your data. Not only do you connect to the server, but I think 99.9% of people are importing from a CSV file into the database and they need to look at the data.
So we do have an import. As you can see, this is the SQL Editor window and I have my migration path here. I have blue for my development, green for my test box and red for my production. I added this data source over here, which is I’m going to import data into my test environment. If somebody gave me a CSV file, I’m going to click the CSV file. As you can see, I have this data, then I’m going to import, and then it’s going to automatically store in the database. Now I’m going to query that particular data. We query data with Aquadata Studio, we use a SQL Editor. This is the screen for the SQL Editor. There’s four major parts to Aqua Data Studio and how we get to Data and how we write our queries. There’s capabilities of making it fast for us and how we can manipulate those queries.
What I did was I created a new query. This is MySQL over here and I have a select star from a doctor’s table and a select star from invoice very basic, very beginning statement. I can also do some advanced automation. There’s an automation tree right here where I can automate and program keys to say SF is going to be select starfrom. I can do copies if I want to be fast with writing the sequel statements. I can also cut and paste my result sets that I get from here and incorporate those into a query that I’m writing or into a script and save for a later date or manipulate the script. As you can see, I have the SQL Editor and I have two results that I ran. I can run one of these at a time where I can run both of them. I have a text view that I can look at the text or grid view where I can actually double click on this data.
I can say, oh, that’s wrong, by looking at it. I can update it from within here. Now, if I specified the connection for you not to do this, it automatically is turned on. If I want people to be able to query the data without doing it, then they can do that from within here. I can save this output to an Excel file and manipulate it from there or any formats we offer a bunch of different formats, XML, a bunch of different formats for saving this. I can do things with this particular query. Now, what I’ve done here is that I have my working environment, like I told you, I have my tree view over here. You can also work from the. Tree view, which is faster in my opinion. In Aqua data Studio by using the right-click functionality. I wanted to show you my SQL database, but you can also run these queries with a MongoDB database.
SQL Editor
As you can see, I have collections here and I have a MongoDB database set up in my tree view to the left hand side. I’m going to click on Lisa test collection. Right now it knows that because it’s contact sensitive, I’m on a collection. It says what do you want to do with this collection? Do you want to create a new collection which is a table in a relational database or a new collection of mongo? What do you want to do with it or do you want to populate this information to the SQL Editor? If I use the tree view on the left hand side, it’s going to automatically pass in the information for my login information to the SQL Editor. It’s going to open up a new tab over here and it’s going to allow me to select from this table and it’s going to load all the tables in memory and everything that I have to do with this particular database.
I can switch databases here and specify to select to another, but the connection information is passed. The information is passed. I can also say load in this particular because it’s MongoDB. It sees that it’s MongoDB and it says you want to SQL Editor, but with MongoDB are you going to run the JavaScript or how are you going to query the data? It allows me this capability right from within here on what I want to do based on the context sensitive. Over here I have a relational database and over here I’m going to load a MongoDB database and I’m going to have that load from within here. It’s very powerful when you’re in the SQL Editor or the JavaScript Analyzer. They also have different tabs for the Java Analyzer and the result sets and what you get from doing this. If I were running a particular query in here and next week I came back and said, oh man, I forgot what queries, what did I do?
SQL History
We also store the SQL history in the SQL Editor. You can store up to however many statements you specify in the max history of the statements per entry that you want up here. It’s very customizable on what you want to see and then your archives from within here. These are files that are located in the file tab and I can say, God, what did I do? Let’s see, I was on SQL Server and managing multiple, I was on Excel, I loaded a file, I did a select statement, I was on sitebase. What did I do with SciBase? The powerful part of this is that if I click on each one of these, it will give me the SQL statement down here but the powerful part is I can select two statements and compare them with our tool set. Right click and the compares will open up and I compare those select statements.
I can populate the select statement back into the SQL Editor and incorporate it in my script. Or if I don’t want to do that or I want to work with it, I can work with it right away. It does keep a stored of what I’ve done for the last year. The last multiple max statements, I’m 100 and it kept my archive of what I want to do. It gets extremely powerful to how I work within the SQL Editor and storing the SQL history and what I can automate and what I can. Now I’m back to my SQL Editor and I’m going to go through with some of these tabs. We went over the grid view and what you can do with your data and update and whatever, but now you want to see was my SQL statement a good SQL statement? What did I get?
From what route did the database take, how many rows did I return? How many scans did I do for people are more familiar with how much time did it take me to run the sequel statement. If I click on the execution plan, then I can see okay, I ran a table scan for my select actors. This happens to be the Keyla database for MySQL I’m selecting from that particular database a couple of tables. I’m doing some distinct concatenation as I’m getting more advanced and I want to see what’s affecting or what route it’s taking. I can populate this into Explain plan whiteboard and I can copy the sequel and get some statistics on how I’m doing with my sequel to get some performance hit. So, as you can see, I have 200 rows that are highlighted over here. The actor is returning 27 rows. The overall it’s doing a table scan, an index scan, I could add a view to here and this would get more complex if I wanted to see what exactly I’m returning.
Optimize Queries
I can decide what I want to do with this query. This query optimized to the best of its ability? Not only can I run an exclaim plan, but I can also do client results and stats on this. As I’m creating these queries and I’m keeping the disciplines of the databases, I’m wondering, am I creating a big query? What am I doing with that query? Is it running? How is it going? How many number of insert updates or DML statements am I running from here? DDL, what type of rows are affected? How many select statements am I running and how long did it take some applications? Or if you’re running a service like Google BigQuery, you’re charged for the processing time of what it takes to run these queries. How do I decide whether that’s a good query to run before I actually run it actually in the database.
How do I get some information back on what I want to see from here? This gives me a clear definition of how long it takes to run these statements and then I can make sure that I’m confident in what I’m writing scripts or I’m writing queries that they’re going to run on time. You also get the information up here above as your account numbers. You can turn this on or turn this off, and all these can be notified down below, turned on or turned off. If you don’t really care about it, you just want to see the results. You can also turn these on and turn these off. You have the query builder for your relational databases and you also have the query builder for your non-relational databases, and then you have a Mongo JS query set for doing the Mongo JS, and I’m going to show you what that looks like as well.
You also have as you’re getting more advanced in writing your queries, you have what we call code folding for some of you are new to doing code. Let’s say that you have a big query and you don’t want to see all of it at once. You want to use your workspace and only work on a particular statement. You can have a roll up to create code folding to show you. This is a great table. I don’t want to see I don’t care about this table, but I want to see the all counts table. I’m going to actually drill into that. I can do code folding and click on these and open these to see exactly what is entailed in there. I have my workspace available for me as I’m adding this query and manipulating it so you don’t have to see everything at once. You also have subcategories and stubs that you can include.
SQL Scripts
Not only do you have the cut and paste from the result sets and the creating the queries, you also have add values and stubs to help you with writing some of these queries to speed up your queries and modifying them. Let’s get back to the SQL Editor and let’s talk about how we can save you time with doing some of this. We have a Schema script generator and we’re in the SQL Editor and we can execute multiple statements or one. In this particular example, I’m creating a table and then the key to a database of my sequel, Creating a Table Actor. I’m creating a table address, creating a Table category, and right here I can clearly see what I’m doing, what type of DDL it’s running, what it’s actually doing for me, and then I can cut and paste this and insert this into my query if I want directly from here.
Or I can say generate a script automatically for me based on what I have in here, I can rightclick and say, hey, I want you to generate, I have all my objects, tables, used, functions, procedures from here, and then I can specify to create this for me and store that. As you can see, my working directory, like I told you how I work is that there are three parts. I have the server, my connection to my database, I have my working environment in the middle, and then I have my folders tab as I generate the script generator and it saves it to a file. This script, I’m going to see it populate over here to the files because that’s the way I work and I have a directory structure that I set up. Now I’m going to see what’s going to happen with this. It’s going to give me some options and what I can do with it generating these, I can have the options to load crates, drops, descriptors, full table scans, I can set these two separate files for insert statements.
There’s a lot of parameters that I can do from within the Schema script generator and the options. You’re talking about scripts and more scripts, it’s automatically going to generate that for me. I wanted to show an example of MongoDB too, because you can also create the Schema script generator and any of the tools that no matter what database platform that you’re using, and it’ll generate it from the collections as well. I can also use a tree view, which is how I work, and highlight the areas that I want to and right click and say generate automatically these two if I want to narrow it down and then cut and paste and include those into my particular database and test and move them into production if I want. It’s very informative with creating the basic query, adding those queries. I feel confident now that I ran this query, I saw the statistics on this query and now I’m confident into adding that.
MongoDB Queries
Well, how do we query the data and SQL Editor for MongoDB? It’s a different discipline than the sequel, and although I can query using our sequel editor, there’s also some concepts that you like to use. Data is returned in a grid format for mongo SQL. If you want to use the mongo SQL, it also has a Mongo JS SQL Editor. It’s an interactive JavaScript editor, if you’re familiar with that. It allows you to execute your JavaScript commands and supports autocompletion and syntax highlighting. It’s the same view that you get, except for it does have a tree chiropractic grid view results at the bottom and I’ll show you, that what that looks like. That’s the thing that’s added to the SQL Editor for MongoDB. And then we have the Mongol shell. If you talk about, we have the Fluid shell. For the sequel, we have a Mongo shell, which is an interactive JavaScript shell.
It’s nearly identical syntax to the same as the Mongols shell, so you can use it. It’s built on top of Fluid shell and allows you to combine the functionality of the unit shell with a native mongo shell command line. For people who are very advanced to using command line tools, we offer this capability from within you. I talked about options. For the options you controlled, your connections, you can set the minimum size contact for code inside if you want to narrow this down. There are some things, this is just an example of how these options work. Whether you’re on JavaScript or whether you’re on your sequel script, there are options you can use for both. Here’s kind of an example of querying a MongoDB with mongo JS. As you can see, I’m not doing a select star from I’m now using the mongo JS script and I’m doing database orders and I’m finding the values that are in the database, the orders collection, the collections here, the orders from here, and that’s the terminology I’m using and it’s context sensitive.
I can do the same things that I do the sequel query window, I can cut and paste these, I can have it generate a script for me. There’s a lot of things that I can do with managing these scripts to make me more confident that they’re good. I can also see their results set in stats on it as well. As I’m going through these again, you can see, you can organize your desktop wherever you like to work. You have servers, files and projects. These are down below, those are the defaults that you’ll see coming through.
You have a multi database queries for people who are not familiar with writing queries, or you want a faster way of writing queries, we have a query builder that allows you to graphically grab your items to the right hand side and build your queries fast and gives you the SQL statements from within here.
Multiple RDBMS
As you can see, I’m collected to multiple databases. We allow you to do a multidaybased query, but they have to be the same type. You can’t do MySQL, the DB two, because constructs are not the same. You have to be MySQL to MySQL so you can load multiple databases. As you can see, I’m collected to two databases. I have the keys, I can set these keys up or not. I can specify some aggregates or where clauses or what I want to do from within here. It will give me the sequel statement. I can cut and paste this into the SQL Editor, or I can run it from within here if I’m not familiar with writing it, to see what I’m going to get, if I get any errors or any of that. So people are new to it. This is a quick way of writing queries, but if you’re not new and you just want it to make sure that the format is correct based on the disciplines that you set up.
Compare Schema
It will automatically do it here for you and it’s a quicker way of incorporating these into your scripts. You can also run the tool set for comparison. As you can see, I’m using one of the tools here. I’m connected to MySQL version eight in my test box. I have a MySQL version seven five seven that’s in my production. I’m going to compare these schemas and see if I didn’t load something in my test box to be appropriate. I can see that I have some differences, so obviously I missed something. I need to go back and compare these schemes and sync them up. I talked about a third part inside of Aquadata Studio for managing your projects. Projects are comprised of Aquascripts server connections and user files. This is a very powerful way of organizing your scripts and integrating them with source control. You can integrate anything in the source control, any file that you want.
They provide a selfcontrol contained program unit that allows you to create a set of Aquascripts. We deliver five with Aquadata Studio and if you’re familiar with working JavaScript, this will look very familiar to you. Aquascript is a proprietary language that uses inside of Aqua Data Studio. But we deliver these templates. So I created a template for you. We have five create an email and Excel file, data schema and data exporter, file transfer, remote command line excuse and multi. SOVERE script execution like we talked about before, random table and data generation. I use this all the time if I have a particular database and I want to see if it’s going to have a performance hit. Because I’m going through a network, I’ll randomly generate 500 rows in a particular table just to see what happens with that particular or a whole database. I can specify what I want to do.
A schema compare will do the template for that. In this example, I’m creating an email and Excel file. Like I said, my boss does everything in Excel over the weekend he sends me an Excel file. I want to email that Excel file, I want to pull the data out and I want to email that to myself every time, every week. I’m going to create a template to do that. I fill in all my information through the GUI. What I want to select from, I’m selecting from DVO Doctor’s table because I want to compare an Excel file from what’s actually into the database to what he updated. I’m going to select from the DVO doctors table and I’m going to email that to myself. I created through the GUI and said all the parameters in and this is what it gives me. It gives me a template for showing what’s existed.
As you can see, I connected to a SQL Server. That was my interested. It was a health care database I selected from the doctor’s table, and I’m going to specify the path of where this file was going to go, and then I’m going to email that Excel file to myself. We deliver these templates and we deliver a non. If you’re very familiar with creating JavaScript, you can create these and update it and execute it all on your own. It just depends on your level or we deliver these for you. What happens is it creates a project for me and it takes that Aquascript that I created an email and it opens up a project and it loads that Aquascript in this. It will automatically load the server that I specified, which was a SQL Server instance in here. If I want to add user files or any files that go along with that, any scripts or whatever, I can organize it this way as well.
If you want to work on a particular team and you want to make sure that you guys are organizing your projects, you can store these out in the server somewhere, or you can store these in some type of source control and I cover that . How does Aqua Gated Studio help you with your scripting options? It has a Schema script generator which generates that automatically for you. It has the scripting windows whether you’re no sequel or SQL Database, and allows you to do the Aqua scripts in a Java like scripting environment or a NoSQL MongoDB environment, and allows you to do that. From the SQL Editor you get to results from that. These also integrate in the source control and that will allow you to be able to check in and check out your code based on how you want to do that or file.
You would just set up your repository. This is a subversion example. We support Subversion, CBS, Git and Perforce, and you can set these up and integrate in with the projects as well. So we offer all these tools. I covered the import and export data. I showed you the compare, schema compare, but we have file compare, copy compare, results Compare. There’s a lot of way of doing the different data sets. There’s also a blog that I have out there and I believe I included it in the link, but I didn’t cover it. When you create these projects, you can go out and look at the blog and it’ll walk you through creating a project and Aqua script as well. We also have videos out there. Now that we have all this data loaded inside of Aquadata studio, or we have it actually using it from a file, how do we present that data to tell a data story?
Data Driven Decision Making
How do we make business decisions with what we have inside the database? You have a no sequel database, you have a relational database, and you want to pull data out and you want to make sure that you’re giving a clear concise representation of what that doing. You want to represent it in a bar chart, a pie chart, or some other layouts. We provide over 50 lady outs. How do we go about doing that? So I’m going to show an example. What I did was I took the Excel file and I loaded the Excel file, which was my data source that I loaded from importing the data. Or I connected to the Excel file, and I opened it up. And here’s the results set. I selected everything from my example, and automatically I can click on this little icon in the results set down here. Aquadata Studio will load the visual analytics from within Aqua Data Studio, and it automatically parses the data based on the data types and dimensions and measures.
It allows me, like I did with the query builder, to drag and drop my columns and rows over here based on what I want to see. It allows you to build charts based on what you want to see. Worksheets and dashboards. You can save these workbooks and dashboards as PDF or HTML files. You can use the extraction data that you use for building these as an example. Or if you’re pretty advanced and doing scientific notation or analytic, and you want to be able to do some scripts and you want to make sure that you’re putting some arguments in some of those fields, you can do that with using the reserves. So what does that look like? The example that I used was a bike shop. With that bike shop, what I did was I loaded three worksheets. I loaded profit by product category, which was my data.
I can clearly look at it and I can say, hey, what’s selling the most over here? Oh, my goodness. Locks is selling the most. Well, what particular region is that? What’s the sum profit that I’m doing? Where am I selling the most of this? California seems to be the most part. I built a pie chart on this, and this takes 2 seconds, literally, to drag these items over here, click on it, and change the results set to a pie chart, a geo region. There are many forms that you can do this. In California, what are they doing that’s making them sell more or why are we selling more? Well, let’s get a kind of a graph of what we’re doing or a summary over a period of time. Let’s see what sales over a period of time, what’s going up, what’s going down, what do we need to do from that so quickly?
I have a story that I’m telling with my database in California. We’re selling the most, the locks are selling the most. What are we doing over a period of time? You can do weekly, monthly, you can set these time periods to anything you want. I can send this to my boss and say, hey, based on our bike shop business, this is what we’re doing. Profit by product category. We’re selling the most of box. We need to order more of those. We’re selling bike strands. We can do this on inventory, see what we’re doing. I can deliver this dashboard to my boss along with the data, so he can drill into the data and see exactly what we’re doing. It’s very comprehensive to what we can do with Aquadata Studio from writing queries, managing queries, manipulating those queries to enhance performance, being able to show that particular data, and grids tables and charts.
SQL Editor
The SQL Editor, just to give of review, we modified the queries, the scripts and another source code. The SQL Editor is a sequel editor or JavaScript editor, depending on what you’re using. For MongoDB, you want to use the JavaScript. Or you can just use the SQL Editor and query the collections and every information. You can automate the sequel with the Query Builder and your Aqua scripts and your JavaScript. You can view the statistics of the execution plan or the stats on the tab based on your result sets. And then we have database. We didn’t really get into the DBA tools, but we do have DBA tools for your DBA’s, like instance monitor for MySQL store manager, security Manager, and Session Manager. Just in case you wanted to manage it happened to be you wanted to manage your storage or any of that, you can check out those and then you can quickly summarize your data and visually analyze large amounts of data with aquid data studio, with the visual analytics, using graphs and charts and sharing that data.
Q&A
Aqua Data Studio does all this and more for you. I’m going to open it up to questions. I hope this will lead you to download Aquadata Studio and check out all these capabilities. I know it was a quick overview today, so if you have any questions for me.
Fantastic, thank you, Lisa. We have had a few questions come in from attendees. The first question we have, can I share these queries scripts or source code with other users?
There’s multiple ways that you can do that. You can load them on a server if you want to. You can add them into source control if you want to, and control it that particular way. You can also send if you’re doing visual analytics, you can email the file like we showed how to create templates for email in the file if you want to. You can share them that way. Great.
Thanks, Lisa. Another question we had, how can I customize my charts and graphs to display the data?
There’s multiple ways you can do that too. With the visual analytics, you can right click and change your chart to be over 50 charts like I showed. You have examples. You can right click and change the charts, or you can drag and drop and specify a specific chart. If you want to customize the charts, you can attach the data along with that workbook so that your boss can go to the underlying data, or you can select not to do that. If you just want to give a picture, you can export those to text, HTML or spreadsheet if you want to send it in a spreadsheet form or send it in a PDF form if you don’t want anybody manipulating it.
Wonderful. Another question from the chat. How do I export the data?
It’s kind of the same way like I told you can export the data to a text editor or HTML or spreadsheet or PDF. You can do that right from within the Grid. You can use the export tool that we have. We have some tool sets for importing and export. You can do that as well, and you can export it in different formats, CSV, or however you want to do it. You can use the tool sets for doing that as well, or you can save the results in a file and do that. Great.
Thank you, Lisa. Let’s see if we have any other questions in our chat right now. Someone was asking if this is an open source tool. This is a paid tool. Lisa, I don’t know if you want to dive in more about the details.
Yeah. No, it’s not an open source tool. We don’t have an open source tool for Aquadata Studio. Aquadata Studio is a very advanced tool. It’s a very mature product. We’ve been working on it since 2004. You can see the release cycles. So it’s not open source. It’s a paid product.
Conclusion
Great. Thank you, Lisa. That is it for the questions we’ve had come in today. I’d like to thank Lisa for an informative presentation, and I invite you all to head over to Aquapole.com to learn more and download a free trial of Aqua Data Studio, which Lisa highlighted for us today. This webcast has been recorded, and we will send a followup email to all attendees with a link to our resource center, where the recording will be available for viewing in a few days. I hope everyone has a great week, and we hope to see you back at our next clock chat webinar. Thanks, everyone.