Data Science: Exploring Relational Database Objects Visually with Mathematica GUI and SQL

Hello, my name is Daniel, I'm from Brazil. I run business here for Wolfram. I attain clients and colleges, researchers, companies and startups that want to useful technology with docs training and check out what we can offer for them to add advanced technology in their business. I've worked in the past as a Oracle DBA for a long time. So I have a passion for relational databases and then how to deal with those information with Wolfram language. And the companies usually they have a lot of information there in their databases. Basically from small size to big size companies, they have some kind of application or integrated application that can handle their business. And all those applications they use relational databases. I like this diagram. And then we have this updated from now from this month. They keep this updated from times to times. Then we see that most popular databases they are relational databases. MongoDB, that's a document database has became popular. But Oracle stays there on the top. And then MySQL the Microsoft database postgres that is free and so on. Oracle brought MySQL but there are a community version and the version from Oracle with support and additional resources. But the MySQL is really popular, a lot popular. This is a survey about developers and they have been using it. The Internet is basically made of WordPress running PHP over MySQL and so every company have something like that. Even those companies that they have Oracle or other run a database or something, they have some MySQL running there. Moreover, it's important there's a survey setting that is really important for data scientists to know and to use SQL in their projects. In particular, I like to get my data load on SQL databases, organize my data, clean my data and then put that on the database to make my projects on consulting, for example, the clients they ever have some databases relational database we need to enter to connect to get information. Combine information from Wolfram, from the created data from Wolfram with the client database. And then information client uses from the web from Internet stuff that's public. Sometimes they buy information connecting to some like a Bloomberg Newsay or some of those companies that provide data. And. And then we can do a mix combining all this thing with Wolfront to do a data science project. But in particular I like those databases relational database. Because the clients they have terabytes and terabytes and terabytes of data in their environment. And most of those data they have that is usually on online data from their online systems. In fact they are not used. There are a lot of lots of patterns there. We can Figure out behavior of clients, products, employees, whatever we may think and then extract information, extract knowledge from this data. It's very important to be able to connect to databases in order to do data science. Another thing is with Wolfram, Mathematic and Wolfram language we can connect to many different kinds of databases. Doesn't matter where they are. We have database at the cloud or on premises or local, doesn't matter. We usually can connect to the client's database by VPN or something connecting to their internal network. And usually it's easy to connect to the client's relational databases. And my inspiration for this idea to build something graphical for database is in those Mathematica palettes. We have many of them in Mathematica for different proposals. And there are some that are very sophisticated for math. For you to write down the math using the notation like we do in pen and paper. But there are some stuff more simple, like a slideshow that is very useful. Is more simple, but it's very useful. And then writing assist and so on. There are a lot of those palettes that help us to be productive. And I would like to have to have something related to databases. And then I started to write down some things that work in progress to see what is interesting, what we can do. For me, the important thing is to connect the database. And the second thing is to see the schemes we have the database we have available and the tables we have available. For example, in our drill down interface, I built this thing. I'm going to show you how I made it into those data science projects for enterprises for business. Usually I like to work with Mathematica and Wolfram language. I like to work with MySQL this is the one the database I've been using here for this project. But we can connect to Oracle SQL Server for Microsoft or whatever. This is not new matemashka does this I don't know ever since for a long time this is a resource available. Usually I use some tools usually related to the database brand the client have. For MySQL we have MySQL Workbench. There is Oracle SQL Developer told you and there is a lot VSCode. You can use VS Code to do those browsing kind of browsing in the database to see database objects. Depending the client, the project, whatever database they have. I use a different tool. And those clients they have terabytes of data. Terabytes and terabytes of data. Sometimes in their online systems. Sometimes they the clients have a data warehouse data mart that is perfect to to work With Wolfram language and get the curated data and graphics, we can work with language. Everything we have available can combine and, and make a interesting analysis for from those enterprise data. And here's a sample from the, the Wolfram website, a sample of the, the databases we can connect. Okay, but we. There is this thing of JDBC that I'm using here to connect to the database. There are lots of databases, but when we work with JDBC we basically can connect to any database you find out because every group or company that develop their databases, they create their JDBC drivers. So basically you go there to their website, the database website, download the JDBC driver and then you can, you can put this in your class path in your computer and then you can connect to any exotic or rare database. It works, it just works. And so you call your driver the database you wanna, you wanna work with. And then they can connect. We have a function here that show us those, those, those drivers that are available in my mathematical setup. But it's pretty standard, I didn't add anything here. This is the basic standard, but they have here this MySQL connector J that is the one for JDBC. They can connect directly from MySQL and there is the skill light SQLite is very interesting, a good database for data science. It is a local database. It's a single user local database that runs in your own computer. And it's very popular use it a lot in mobile application. For example, probably in your cell phone. There are a lot of instance of ESK Light running there from the applications you have in your computer on your cell phone. But we can use this for data science too, for small scale. And so I first thing I import the database link and then I connect to the database here. In this example I'm connecting to my local database for this project. We can see here I open the database and here create those variable db that is a SQL connection. And here it's okay, it's done. But something I like to do when I connect, just connect. I like to check my connection. So I make some kind of query where I execute something without thinking that, that I'm. I am going to connect the client database. I don't know the tables they have there. I will look for this thing for the first time. And so I just want to check the connection and then I send some calculation, run the calculations to see if I get the data back. And so this query sent by the external evaluation for this database, I opened the connection and this computation is made on the database. And that they send me back when we get the data back here. This object here is a data set. Everything that came from the database for the. My notebook came as a, as a data set. That's very, very useful here it's a more interesting case. I'm getting the current time, the database version and I'm processing some random, some random numbers here just to check if the connection is okay. Let me see. External sessions. No, I don't have it here. Oh, here I can select a table table. Contacts have the information here as I said in a data set. And then we have here the head is data set and here we see the full form. Good. I can do a lot about the database with the data dictionary. If I do this thing show tables. I can see the tables I have available and I can use describe to see the details from a table. The describe or these show tables. They came to my notebook as data set too. Both they are data sets. Then I can run more interesting queries in the data dictionary and bring more information from the database Here the idea is I can get those, those queries that get data from the data dictionary from the database to give me details of the structure and the metadata from the database with a toolbar. It's a kind of two bar with a user interface. And so here I have a more complicated query to get a list of all the schemes the user have access all the tables there, all the primary keys from those tables and the name of the column with a primary key. And then I run this thing and here is the list. I have tried to go deep and get the name of the fields of every database with the type, but it doesn't work well, because when I run this, this query like I want to know the schemes, I want to know the tables, fields on tables and data types of each field. When I go down to this a lot of information, sometimes it gets really slow. And so it's better to stop getting the table names. And then I can select the table I wanted. And then I get the detail for each table. It's a lot faster getting this information by steps. And then here I do a lot of work in order to organize the information to create a user interface. For example, here I have a list of all the objects I bring with this query. I put this in a simple list. Here is in the data frame data set. Then here I have it like the name of the scheme dot the name of table. And so in this scheme here, Data science we have two tables. But for example, I have this database for F1 races and then there are a lot of tables and they are all here the name of the scheme and the name of scheme means the database. And then the table name. So far so good. I built a set of functions to be called by the user interface. Like putting buttons there and graphical objects and then get the information back. And then what I have here, I have this function here. Print the cells. And so I will have the user interface when I click in a button, they just below will create a new output notebook with the result we get from the the database. And then here I have a ping. For example, I, I would like to know if I'm connected to the database. So I have here that query that gets some information without querying a table. And so they, they use this function to get the, the current time and so on. And so the, the query is embedded here. Here we have the external evaluate and here that function that prints this output as a data set in my notebook. And then I give a name for this thing here to call in the user interface. And so I have a couple of them I have here about this ping to check if it is connected here, to get the version here, to get the describe of a table here, to get the count of a table, to check how many lines there are in the table. We can do a lot of stuff here. Okay, for example, I can get how many terabytes there is in a table. There is a lot of information we can get. And then we can build new functions here I can show the external sessions and so on. And then to start building the interface I get here from the, from the, from my data set with information about the data dictionary. I separate here just the database name that the scheme name and the tables we have available. So we have two in this data size. We have a lot of tables here, for example in this F1 data and so on in the database. Of course there are a lot of more schemes that my user don't have access as the DBA granted access for me for other databases. I will be able to see it here. Here organizing the data again the data about schemes and tables in. In. In a association and then in this association using rules. This is the name of scheme and those are the tables that are inside this scheme. This is the data I'm using in order to create the interface. And then, okay, oh, here I have a simple list of the schemes that I have here. I get again that list and I use this variable here to do tests. Let me see here again the list of schemes and the tables I have in every scheme in a data set. Here I did a group by in the the data set in order to organize the information at this way. And here it's the same thing. It's the same thing. But I'm getting the information out of the the data set. Here is a function to get data given a rule with the scheme and the table name. They goes there to the database and bring the top of the the database. In fact I get just 100 lines. I limit in 100 lines just for you to check what what is the to what the to face the data. To see the data available in the table. How it is how is organized not just by the labels but to see the data itself. As the databases they can have terabytes of information to kind of inspect the data. I put here just 100. But is a thing that we can put putting parameters or we can change this thing for more data if you wanted. But for. Usually for my applications this is a good. A good thing. And then testing this function I asked for example at the scheme data size. I want to see the table contacts and then it brings for me this data set. If I delete this. This thing here. You're gonna see that is really fast. It goes there to the database query the data, transform the data set and show it for me here in my. In my notebook. So this is really really fast. Okay, this is another test with the F1 data I can get information about the drivers. I bring just 100 lines and then it's a data set and I can use that function I built called named Database Cell print that I'm going to use inside the graphical interface. And then let's move on here. Oh, here. It's a test with a button. Now I'm mixing. I'm mixing the. The data and the functions that process the data connect to database and everything. Start mixing it with graphical user interface objects. So pressing the button I can get the data here. So far so good. Now. Now I have a set of button. Okay, there is an object that gonna show all the. We're gonna show all. All the schemes and tables inside these schemes in a. In a kind of tree not three view. And then when you click on the table name we can get a sample of data from that. From that table. And then I build this custom bottom here in order to do that. In fact I'm using this parameter here appearance known for the table names. Don't see like a boot on itself. It's just a drill down and you click in the name and then you see the data you wanted. And here I start working with the layout how to, to build that, that kind of toolbar using grid organizing. Here the ideas. And here I am, I'm using this component order view to, to create this drill down of tables and schemes. Okay, here I can show you how it works. Just this component. Okay, so here at this graphical component I have all the database schemes. And then when I click here, I can see the table names. And then if I click in one of those, one of those tables, it brings me that hundred line sample of the data. Build this component here. And now I'm going to get them together with other buttons to call the other functions. We showed them here. It's another test combining some buttons here with, with that this drill down view or something, something broken here. And then here we have one version of it. It's a small interface. Think that we can put this out in another window with Mathematica that is basically a new notebook. But you're going to see as we can see it as a new window. Then it's a small interface. Then here I can drill down the data. There's something wrong. Then if I click here, if I click here, we can have the table. We can have the table data. And so I close here. Get another, another table here. Let me close this one. I don't know about drivers. And brings me the data here. Okay, this thing is broken. Well, now I can click here and ping the database to check if my connection is working. Oh, I think that he went down there. Okay, let me clear this thing. I can ping the database. I can check the version. This, this information came from the database data dictionary. I can for example check sessions. I can describe a table. I can count the number of lines in a table here. Okay, this is a test. Let's move forward here. I put all those functions together. The idea is to have it published in the function repository. And then I use this dynamic model to get together all those functions I have been doing and testing. Okay, in this application here, let me run this thing here. The notebook is available, you can download. Let's see the interface. And then we get back here to the code. To call this interface I need to pass what is the database? The kind of database I want to connect. I need to, to send information. Where is this database? Like the IP port. And here is the scheme, the main scheme I would like to use. And then a user and password. Let me run this thing here. Okay, so if I have here a combo box, I can select a table and then I can have described from this table. I can have the count. That's the number of lines there are in this table. And additional information. This information came from the data dictionary. So they scheme the kind of engine dates and so on. Okay, let me. Let me remove this thing. Okay. And then we can ping the database. We can see the details about the connection. We can get from the data dictionary the version of the database. We can see the sessions. I'm not sure why the session is empty. It should be at least one. And here we have the list of schemes and the list of. For some reason they are not. They are not organizing as a column, but it works anyway. If you click here, you have a data sample from the database for each table. So far so good. What I can do here, the idea is the data dictionary for each database is different how they call the tables inside the data dictionary. And so those queries, you should have queries appropriate for each database. MySQL and MariaDBay is equal. Okay. But for Postgres, SQLite, Oracle SQL Server I need to build the queries according each data dictionary and then add this detail here in the application. I will publish this is there for download, but I will publish this in. In the. In the function repositories. And I have some new features to add here like upload CS CSV Sometimes to. To upload CSV in database is tricky. What I would like to have something really easy. You select the file and it creates a new table and loads that thing to the database. Doesn't matter what they. They figure out what kind of data for each column and then is able to create the appropriate table to support that data. It can be tricky to code, but very useful when it's ready to go. We can export data, automate data analytics for tables or automate data analytics for database columns. There's a lot of stuff we can do. Perhaps we gonna have many different versions of this user interface. This is it. Thank you for attending this talk. Yes, I have some suggestions here to Eric. I think Eric, this is an endless project. Endless project because we can do a lot of stuff to add statistics, histogram, graphics. There's a lot of things we can do. Okay, thank you. Thank you for attending this talk. Bye bye.