Transcription
And so, friends, hello everyone. Recently, I encountered a task that was set for me, or rather, I was asked to do it. My good acquaintances needed me to prepare a system, deploy it, which would give them the opportunity to conveniently work with a certain small type of data. I spent some time on this task. It seemed to me that I did it well. I found interesting tools and managed to package them so that they could be deployed quickly on a server. Therefore, I decided to share my findings and, as it were, my solution, my experience, to save you time, because I spent about two days on it. Well, and to share my little experience.
And so, today we will create a small automated platform that will allow storing certain data. It will allow analyzing this data visually using tools directly from the internet. We will also build a small ETL pipeline and package all of this in Docker and deploy it on a server.
And so, today we will talk about how to create a simple platform for collecting, storing, and analyzing data. This will, of course, not be big data. This will be, well, in our case, about one and a half million rows, for example. Probably, such a platform can store and effectively work with up to 1 terabyte of data. And here I wrote that it would be suitable for startups, but of course, not just for startups. For any organization, for any, for any, for any person, for any company that needs to analyze, and analyze small volumes of data.
And the advantage of my solution is that it is, in principle, completely free to implement. I, for example, bought a server for 8 euros per month. This server should have about 8, at least 8 gigabytes of RAM, because today we will work with and launch Docker, we will launch Airflow, and these are quite resource-intensive services, software. Therefore, in my experience, at least 8 gigabytes of RAM are needed.
Well, as I said, we will build our data alligator using Airflow. Airflow is responsible for automating the work of collecting data, transforming it if necessary, and storing the transformed data somewhere. In our case, it will be a PostgreSQL database. PostgreSQL is the next tool. In principle, it is standard. I can say that it is a database that Python programmers work with well and has proven itself.
And as a tool for data analysis, for visualization, for working with this data, for visual work with this data, we will use DataGrip, a free tool. Metabase is something in between the first tool for simple SQL queries, and for simple SQL queries, or some real tools like Power BI. That is, here you can not write simple SQL queries, and you can get a visual picture of our data by clicking buttons.
We will package all of this into Docker and use some tools that will help us work, well, not even with very small data in our case, because ordinary Python is not as effective as other tools for the classic data path when we collect it and store it. Here I tried to depict it: we have a data source from which we want to get the data, denoted as a website. We have where we want to put the data, this is PostgreSQL. We have a user who will collect data using a tool. They will look at PostgreSQL, they can make SQL queries, or as I said, they can use some simpler tools. We have Airflow, which automates the data collection and transfer to PostgreSQL.
Well, in general, now I will quickly show you how it looks in the end, and then I will tell you how we will do it. In the end, we will get this picture. I, for example, went to a website, just for example, took a set of data for 20 and 21 years. When I downloaded it, it looked like this.
And by the way, I am immediately showing you the code that you will receive. This cost me two days of work and experience that I already have, and you will get it for free and can further advance your research.
And so, now I will quickly show you visually what I want to show, and then we will look. I will tell you what I did. So, I have, well, in principle, I can immediately tell you that I packaged everything in Docker, as I said. The next service is Metabase. This is a tool for viewing data, called Metabase. Yes, for example, it automatically built some visual representation for me by default of my data that I have in the database. For example, you can access it, you can immediately see what cities and countries are present in this data. Here we have, again, a tool, a tool, we can look at this data directly like this from PostgreSQL. Yes, these are the data [music] that will be in my PostgreSQL, and we put them here. As you can see, about one and a half million rows. And we have Airflow, with the help of which we will do the data transfer from here to here.
I have prepared a complete instruction that you just need to upload to your server. Run one command, and all this beauty that I have here will be deployed. You can use it in the same way.
What is Airflow? Airflow, as I said, is a tool that transfers data from one resource to another using so-called DAGs. DAGs are executable commands. That is, we write commands in regular Python, load them, of course, according to a certain scenario, load them into Airflow, and they perform a specific action.
For the beginning, when I was doing this work for my friends and acquaintances, what I wanted to say in addition to this, in addition to the main material that I prepared, is that what distinguishes a junior developer, especially, I think, from a middle developer, let alone a senior. Well, I am certainly not a senior yet, I consider myself a middle developer, but I strive to become a senior. I think about who a senior is.
And so, for the last two or three months, I have been observing such an interesting thing. I see how inefficient my movements can be and how inefficiently I spend my time as a developer. I see that, in principle, this can be applied in any area of our life. As a developer, I think it is very important to clearly understand what needs to be done and to find the optimal path and optimal actions to choose in order to meet the requirements, and on the other hand, not to waste your time. Because when I started this task, I heard from the client that he needed to parse all this data, plus this data will be updated regularly. And therefore, I went down such a long path that I set up Airflow. Although it is beautiful and cool, it was not necessary in principle. Because when I did this work, or rather started doing it, I talked to the client again, and it turned out that in principle, they don't need to update it that often. The data can be, and not all of it, but for example, just take data for 21 years, and then load it later. That is, in our case, our Airflow would already be redundant.
Here, in principle, I spent more developer time than was necessary for this task, because I could have downloaded it, written a small parser in Python, loaded the data into PostgreSQL, and then looked at it. That is, Airflow was already a slightly redundant tool. Well, never mind, I'm using it because it was, in principle, interesting to create such a product, that everything is deployed, and it will be useful in the future.
But there was also one redundant moment when I started writing this Python code that was collecting this data. I encountered very big, for me, big problems that I didn't know how to deal with. Because at first, I went the route of downloading data with a script from this site, then I wanted to save it in a directory, which seemed quite logical, and then from this directory, I wanted to download it with another script and process this data. But when I started writing the code that saves, downloads, and saves this data, and does it regularly, I probably spent 3 or 4 days solving the problems of how to save this data. Because I have never done it like this before, that in Docker it is not needed. In short, I had a lot of moments where I couldn't save these files in Docker. Well, I don't work with it, but when the server, supposedly everything works through Docker, I spent two or three hours figuring out how to do it. And on the third hour, when I was sitting and realized that I was tired of figuring it out, I realized, why am I even doing this if it's enough for me to just download this data, just download it, put it in a directory on the server, and just take it from there. I don't need to store it anywhere else, especially since it's not a regular task. These would be my redundant actions.
And this is exactly what I'm talking about: it's very important to precisely understand the task and understand where we, as developers, can cut corners, essentially, save our time, but at the same time solve the task. And this was the first case where I realized that a lot of time was wasted here. And by the way, this gave me the thought that I would probably like to do a regular review of the mistakes I make with my time at work, that is, the moments when I do things inefficiently. For example, I started remembering, and in the last two weeks or so, I remembered three such cases where I spent a lot of time, and the solution was simple, and it could have been solved simply. This led me to the thought again that it would probably be good to tell other people about this. Firstly, so that I don't forget about it myself, and secondly, so that other people who are going down the same path can possibly change their development philosophy a little. Because there are developers who strive to do everything perfectly, and there are those who may not think about the value of time.
So, I thought about this and decided to talk about it. I hope you will write in the comments whether you will be interested in this or not. That is, about the mistakes that I encounter on my path as a developer. In general, in general, there is probably nothing more to tell. In essence, because I have given you a small presentation. I have shown the possibilities of how it all works beautifully. I have shown you the code that will be available to you. I will comment on it again.
By the way, there is also a very important moment, this is also a mistake that I made when I was doing this project, which forced me to rework it a little. Here, I have also done it so that my project is divided into 3 components, 3 parts. This part is responsible for Airflow. Here, we include the Airflow itself, its visual part. Here is the scheduler that sets the schedule for the jobs. Here is Redis. Here are various workers, initialization of this Airflow. And here is Flower, a tool for viewing current tasks that are pending in our Airflow. This is one component, one component. The second component is Metabase itself. It is here in Docker. How did it get into Docker? It's not just that, it also has volumes, connected folders, so that the data that will be created in Metabase, for example, my authorization, is not lost upon reboot, but is saved. And separately, I am also launching PostgreSQL, where the data will be stored, where this kind of data is stored. And again, I created a volume so that this data is not lost, so that it is always saved, even if Docker restarts.
Why did I do it this way? Because when I was doing the first version, I combined all these three components into one Docker Compose. And then, when I was developing this application, I constantly restarted it, updated it, restarted Docker, turned on Docker. All my data was constantly lost and re-created in PostgreSQL and Metabase. And if with PostgreSQL it's not so simple, well, not so difficult, there is a volume, it connects and the data is saved normally. With Metabase, it was not so easy to connect a volume so that the data was not lost, and its authorization, and settings, and database connection, and database connection. And therefore, every time I restarted Docker, for example, to change something in Airflow, or to change something in Airflow, my entire Metabase disappeared. Therefore, I took this mistake into account when developing the second version of this product. And I separated Airflow, Redis, and PostgreSQL. That is, now I can start or restart everything related to Airflow tasks, with DAGs, I can add new DAGs, and it will not affect the work of Metabase and PostgreSQL at all, because they are launched by other Docker containers and are constantly working.
I have also written a convenient bash script that launches all three of my components at once. That is, I just go to the server, type `bash run docker-compose.sh`, and it launches all my Docker containers. It launches all my Docker containers in literally 5 minutes, the system starts.
Yes, I also wanted to tell you separately about one cool library that I use, and even about two cool libraries that I use. Here I have included them, I almost forgot. The libraries are called `datatable` and `dataset`. What is the advantage of the `datatable` library? It somewhat replaces the Pandas library because I will show you a method. I did some small research on this library, and it works much faster than Pandas with such large data, especially when there are gigabytes of rows, especially when there are gigabytes of these rows. I worked with a small volume of 100 megabytes here, it's just a little bit. But when I had to work with large volumes, Pandas, you can say that Pandas fell. There is loading through chunks, but still, it works much slower. But this library, `datatable`, works much faster and much more stably with large data. And again, it allows you to convert the data that it processed in its internal format to Pandas. Well, and then you can work with it just like with Pandas.
And there is also a separate library, `dataset`, which allows you not to bother with creating tables in your PostgreSQL, especially if, for example, you don't know the structure of the data. For example, you don't know what structure the data you received has. And in principle, I am not saying that you don't need to study SQL. Of course, these libraries are good when you understand what you are doing and what is under the hood. They just speed things up and save time. Time is very important, but of course, if you don't understand how it all works under the hood, then I strongly recommend you to study what SQL is, how tables are created, and so on, and then use such high-level libraries.
So, what does this `dataset` library do? You essentially pass it a dictionary that you received earlier, for example, using Pandas. And based on this dictionary, it itself creates a database where you told it to, for example, in PostgreSQL. It itself forms tables, columns of this database. Well, and then it stores this data itself in this database. This is how it turned out in my case. I just wrote a small script using my `datatable`, it converted the data to JSON format, then took it from there, converted it to Pandas, and then I passed it to `dataset`, and it itself indicated to me what table name to use, and it itself connected, parsed my data, and created all the tables that I showed you, that I showed you here.
It is very convenient. Now, I am also looking for, perhaps you don't know, perhaps you don't know how to make connections in Airflow so that you can connect to other sources. Here there is Connections. You select Connections. I have already created connections. And we select the type, PostgreSQL. And then we specify the necessary parameters for connecting to an external database. In our case, it is this. Again, this is with the help of Docker, which deploys all of this on one server. It is already connected and works with all components. But still, you need to specify some connection. And here, as you can see, I specify the connection parameters here. It takes them from these connections for connecting to PostgreSQL. And already here, we call this connection. Here, it returns this connection. It is repeated when loading into the database. In short, it's very convenient.
In short, it's very convenient. And I also want to show a nuance about Metabase connections. Metabase by default is created without any connection to any table. You go into Metabase settings and click on "Tables". I have already created them here. You also specify all the connection parameters here, the same ones that you specified here. And that's it, the whole system is set up and works.
In general, I will not bore you anymore and take up your time. Thank you very much. I was glad to share this information with you. I will post all the code on GitHub. I will attach the link. And please use it for your health, improve your data engineer or analytical skills. And all the best to everyone.