Data Science: Processing Relational Data for Business or Research

Hello, I'm Daniel Carvajo, I'm from Brazil. I'm here to talk about data science. I will talk about how to automate your process with Wolfram language connecting to relational databases. Hello, thank you for attending this talk. My name is Daniel Carvajo, I'm from Brazil. I run business there for Wolfram. Well, I did classes in college there too for computer engineering for data science students. Today I'm going to talk about data science with relational database. In fact, there is a hype from data science nowadays and this is good because big companies, usually they have research and development area in those companies, but small size and medium sized companies don't. And what's happening now is this hype of data science. In fact, in certain ways, the companies they are thinking about to use size. They see that computation and size can help the companies to solve problems, find patterns, develop a better quality from their products and so on. And then with Wolfram language, we can process data, we can get data from database. Basically we can connect from any kind of database. You may think those databases and softwares we have in companies, usually all of them, we can connect. I didn't know any of those enterprise applications that we have any difficulty to use and to connect. One thing that is important to notice is that we have this concept of big data and small data. I am doing everything here in my computer and so it's a small data, but we can project just to other environments in the cloud and so on. But in many cases the companies, they have that traditional systems, they have storage, they have databases on premises in their network, they have a VPN for the users to connect. It's a protected environment inside the company. And a lot of stuff they have now externally, in terms of big data, they have been using the data lake. Okay, the data lake is something relatively new where you put all the files and everything there is a mess. Different files, different formats. Doesn't matter what you have you put there in your data lake to get information from the data lake, we should have a data scientist because it's very messy. And then we spend a lot of time in this process of data science, dealing with the data, trying to transform the data, to find keys in the data, get just the small data that is there. Because the fact is that we are looking for the small data, not all that messy data doesn't matter. There is something there that's important. We are looking for. You want to extract that information in order to have knowledge, to find value. And then Gartner had a paper telling that by 2015, the companies they will focus more on small data than in the big data itself. It's not mean that the the big data going to stop or something. But as Stephen told in the keynote, there is a kind of wave, there are fashions. So something is in fashion now and later it's not. And something older gets back and we move on in this cyclic manner. So we have a lot of databases nowadays. This is a database index. The most popular stay Oracle. But MySQL is really used. Most of websites on the web are made of MySQL they use Linux, Apache, PHP, MySQL and so on. And it has been used a lot for data science too. But basically all those, I see all those databases we can basically connect with Wolfram language. We have other types of database document database like MongoDB, column, key value, graph and most of those no SQL database nowadays usually the companies use at the cloud, but we have a lot of stuff inside the companies in relational database. And so what I want to do here, I would like to have a super function that I get a CSV file and I just can load it in the database. Easy, easy. Get a CSV and put in the database. I don't care. I don't want to know how many columns they have, what is the type of each column. I want to this thing. I don't want all of the columns as a string because I want to do calculation. Okay? So I want to get the CSV file and put inside my database, simple like that. The fact is that we spend a lot of time doing this thing. Perhaps we spend more time doing this kind of stuff or manipulating the files and text files and databases, whatever than doing machine learning and the modeling. So the part that is really important is you can do it quickly, but you spend a lot of time doing this boilerplate here in order to have the data. So what is my dream? I wanted this function and so I built it. I gonna show you how it work. Okay, I did this thing here today. I'm showing you with MySQL and SQLite running locally in my machine, okay. But usually I connect to Oracle, to MySQL from Microsoft. Whatever database the client have in their company, I build this thing. But every time I should to fix this program for that database. Because those databases, they use SQL. It should be very common use the same SQL in different databases. But it doesn't work. The databases have different syntax for SQL and we should to fix something to work in different, in different environments. And so I usually use SQLite Studio or MySQL Working Bench to help. But every database has a tool like this in order to help you with the database. And so how can I do to digest a CSV directly? Esqlite okay, easy, easy. Well, first thing I load the two. The just the two lines. The first two lines of a CSV file because I can have CSV files of one terabyte, for example. I don't want to dig in all that thing and figure out what we have there. I just want to use the first two. Of course there are risks. The first line I will get the name, the header. The header to use as a database field name. And the second line I will use to understand what are the types of fields we have in that table in that columns. The text file we basically what have been doing is using three types of data, okay? The real integer and text, not varchar. It breaks up everything. It breaks up everything. Think that I have 200 CSV files to load. If two or three of them break up, I gonna take a look at them. But most of them I will be able to just to read. Doesn't matter, just put this in the database. But it happens, it breaks. Well, I use three types of database data type, okay? For the fields real integer and text. Not varshar because varshar usually I need to tell in varshar what is the size of the text. But I don't want to know that they want to go open the file and figure out what is the biggest size. Or perhaps you truncated this thing. I don't care, I want to just upload this thing. Moreover, I usually read those three types of data because of the data I can do calculation about date. Date is a beast. Date is terrible. I have files with the date in epoch, that is a number, an integer number. I have dates in the Portuguese version, that is the Latin version. They have the day and the month. And then we have files from English that is the opposite. We have dates that we have one field with the date and the other with the time. It's a hell. And so you should. Usually I load whatever it is as text and later I can create a column and by query I update that query and transforming in just one kind of data. But data is really messy. It's really messy. Well, I'm going to show you here how I work with this thing. Moreover, those three data types for database it works in MySQL tree. It's funny because the databases they have been evolving all of them and they have many data types but usually you don't see companies using those things. They are very traditional, the database types used on the industry. But we have a lot of new stuff we can that can be good for us. For example, this text in fact doesn't matter the size. Of course there is a limit, but it's a big limit. And so it works very well for data science. Well, what we need to do here, we need to create the table and load the data. Okay. And so now I'm gonna show you how to do that breaking down. And later I'm gonna show you just one function that does all the magic. Okay. First I use regular expressions to identify the data type with the first line of the second line that is the first line of data in a cvs. It's simple. Okay, I don't do any rocket science here with regular expressions. But regular expressions has a very weird syntax. In the day you are working is fine, the next day you look at that thing and say what? It's very hard to work with regular expressions. And so I try to identify what's an integer if it's not an integer. I try to convert that to check if it is a float. If it's not a float, whatever, it's a string. And I put the data back there in the database. And so I have this simple function here. I send the data to run regular expressions is very costly. It's a finite automata. For those from computing regular expression is affinity automata. You build a finite automata when you define this rule and then it's costly. This is the reason I just look at the first line of the data. Because if I try to do that, every line I will process in one terabyte. You're going to take hours. And so I just use regular expressions once in the beginning when I need to identify the types of the data. And so I assume that is integer, real or text. Those fields that works on both ASCII light and MySQL. And here we have test. Okay, I put a hard codes list to check if they are able to identify correctly the type of data. And it works. And then I use this function that is readline. This is strange because usually we don't use this kind of function at Wolfram Language. Right? Usually we read the file in a list or in a data set and start working. But I'm telling you that I'm working with very big files. I have hundred files of one terabyte each one. So I can't use import. Sport is beautiful, all these issues we have import has resolved. But I don't have so many terabytes of memory. So I need to to read the files line by line. And then I read the first first line. I do some adjustment here, put everything in lowercase, replace white spaces by underscore and so on. And there is another issue here. I can't create a column name as number. So I add something here because there are years as a column name for a table. It doesn't work. So I put an I in the front of this thing in order to be able to load here I'm checking the first line, the first line of data that is the second line of the file. And then this is what I have for this case here we have lots of strings and floats. And then usually we can use comma, okay to break down the line. This should be very simple thing, right? But it's not because in many cases there are commas inside double quotes or stuff like that single quarter double quote or whatever. And so it breaks. And then I need to use regular expression again in order to to get commas. But that are not inside double quotes and it's costly. In this case, I will run the state for every line, every line I read, I will run this regular expression. And so I am able here to go down and check the data types. I get that first line of data, I run in my check data type and I have this list what is text, what is real. And then I'm going to use this thing twice. One to create the table and second to build the insert text, the insert query. We can see how it behaves. And so I'm going, oh, this is the shaking with this data, this file we are using here an example. And then okay, this is the table, the create table that we should just send. Notice that we are not going to create primary key index is not new. Whatever. We will do the query the create table more simple as possible. In order to run many different files, to load many different files. And then this is our create table. Usually codes that create code is ugly. I never see a beautiful code that create code. And so here we are using woofer language to create an insert from SQL. So it's a little bit messy, but it works. Okay, I'm going to here I do the database connection. This is for SQLite. Here I do a test. I connect to the database and do a general query here to check it is connected. And then we run the create table and then we select the create table and it is empty and it's working, is there I can for example, if you are using Oracle, you, you, there is a command you say describe the table name and then can give you the structure of the table. Here I'm doing this thing for the, for the SQLite. So this is the table. The fields that were created, the data types that, that we gonna to load. And here is the insert. The first, the first impression is that okay, it's just insert and put the fields there and run. But it doesn't work. Doesn't work because there are fields that we should to embrace with double quotes in order to read. But sometimes there are strings that they are already with double quotes in the file. Sometimes they are, sometimes no, you will never know. So we should to process this kind of stuff. And so it's very simple, but there are lots of details. When we start working with the data we're going to find it fails and fails again when you're going to find how to solve those, those issues. And so finally we have an insert that works and then we can move on. Oh, this is that example from acquiring a text with a coma inside. And so it's better for you to use hagexp then just look for a coma. If you know your data and if your data is good created systematically, perhaps you can trust in the coma. But I never trust in a comma. And then let me show here. Oh, this is the read line breaking up the data with the regular expression. And here we are finally building the insert, checking if there are not double quotes and using it and removing it as needed. Well, we did a lot here line by line. And then we can go and see this thing running. Here is the test. We get the data inside the table beautifully with different data types and so on. Here is a test by read, just 100 lines, just check. And then I build the function putting it all together. I get a file from the Internet I want to load. Okay, and here, okay, okay, here I have that function that check the types. And later here I have the function where I put everything together. All this code I did in notebook, step by step. I get together in a very messy code inside a model to this to work separately and not mess up my notebook. And here they process the header, process the first line, get the data type, get the table name. I use the file name as the table name. And so I need to have sure that the files don't have the same name. Here is the create table. Here open the connection and then run the create table. And here I insert the first line because the first line of data as I read to check the data. So I need to insert it first. And then I do a loop to the end of the file. I don't know how many lines they have or the, the total file size. And so I, I use a do loop here in order to read every line and, and have it load it read a line, create and insert that data and then moves on to the next line. I close the the file and the database connection and this is it. Well, it's really easy. I connect to the database and then I just call this function here. All the complexity is inside this function. I pass the function where is my database and what is my file and it does the magic and load this thing. But sometimes it can break up. Well, here I can check the tables that I have there in the database. The table is there. I can check the data loaded. I can of course query the data. This is just a sample. If I have one terabyte, it's going to breaks out and then I can load for my data set. That of course depends on how many, how many memory I have. I can query something and start working here in my, inside my notebook with my small data instead of all the data that is downloaded in the server. Okay, now I'm gonna show you another thing that is related that is this. We have in my SQL 8a JSON data type and a long text data type. This is really cool, this JSON. What we can do, we can just learn a JSON as is inside the database. I connect here to the database here. Usually I do a query to check my connection. And then here it's just some queries. Okay, I can create the table. Notice that here in my SQL I have this integer data type, the real data type. And the text data type is opposed to the varshar that I need to specify the size. And then I can insert the lines and do queries and everything. As it works in any database. We kind of the relational database, we have the JSON data type. Look here, it's a database type. Okay, so this J zone data type, it's not a text, it's a live JSON. I search a JSON there and then in the query I can select some items in the JSON. Even in a nested JSON, I can do it. And so it creates here an id, an index here for me to know when this thing was inserted in the database. And the only field I need is this message here. And then. I get a bitcoin. I got a bitcoin message here from web service. They Have a JSON with the values. It's a string. I read this thing as a string. I force here, okay, as a string. And then I just send this to the database concatenating here to build the query and then it goes to the database. I can do it again, for example, with another API, another web API. And then I can get this data here and send it to the same table. Even being different structures, different queries, the different data. I can store this thing inside this JSON field for MySQL there's a good documentation about that, how you retrieve this thing combine. It's really cool. And that you can get inside Wolfram language. As data set here. One thing that is important, I'm reading here an API and I use this raw JSON and put this thing in a data set. Okay, and then we have our data set here. One important thing here, when you do that with Python with Pandas in the jupyter. Okay, you can't do that. Pandas do not support nested data. We have nested the data in. In JSON and xml and we can do this in. In you. You can be perhaps a dictionary. You can look these things a dictionary in Python, but not in the. The Pandas to do the data science process. Okay, well, but if you look at this thing, the data set is an expression as everything. As everything in Woofer language, right? And so using this long text field, all the databases they have something like that as opposed to varshar to additional variable. This kind of field, I can save there a big text file, a big. A big information text information. They have the binary version that's blob. But I am using here the. The text version. And so what I can do, I build here two simple functions. I create this table in MySQL and then now I have two functions to put information there and to get information back using that long text field. And then I name this object like file name, for example. And then what I can do, I can put that. I can put that information from Oops. I can put this data set. I can put and load the data set I have already put there. I will get back Oops. Okay, I didn't run the function. Okay, just a second here. Okay, I can just load. I can save any expression from Wolfram language to the database and read it back. It's not alive in the database is a text inside the database. Okay, but I can save and get back stuff using the relational database. It works with basically all the databases we do have. And we can do it with images. For example, I can save the image. The image in orphan language is an expression too. Oh, it's duplicated. I have already sent this thing. If I save here, I don't know doggy, dois dog 2 and then I can have back this dog 2 from the database. Or I can save, manipulate or any expression from woeful language. I can do that. Let me try here. I will call this. Oops. Oh, I lose everything here. It's at the end. Here. I can call this thing here version 2. I save this manipulate and very quickly I can get it back. Oops, let me go there down again. What is this thing? Oh, here. Okay, true here. And so I got it back. Any expression I can save in the database. I get this idea from. We have a function that. Oh, here I get a. A list of cats from the web and I save it in the database and get it back from the database as always expressions. In fact, I get this idea from this cloud put and cloud get. When I see this new field from the database that is long text. I thought it would be easy to do it and it really is. In a couple of minutes it was working and is very useful. When you work with many people sharing data running many people running the same notebook. We can everybody get the data from the central place. Or of course you can use Wolfram Cloud to store and get your data. But it's really really fast. When I'm working locally or I am working my network if really fast to get and save those those files. This notebook is there in the website if you want to somebody want to take a look. Usually at every project we should to do some adjustments by the kind of database the query the data types we have available. What is useful because you spend some time doing it create this super functions and then it save a lot of time for all the team in order to load data, save data, move data to site database. It's really useful. Thank you very much.