Infobright Blog

I just recently had the experience of working with a few datasets that were quite sizeable to manage on my laptop. And while I found the challenges similar to those that many of you have already experienced, I did identify a few steps that make things a little easier. Of the most important are process and preparation. I found that the more I knew about what I was going to need to do, the smoother the general process was in each of the steps.

Most of the common challenges people are familiar with surround getting the data prepared for an analysis environment or tool. Usually common ETL tools are used to do this, but what if you are preparing data for the first time and you are not sure what that process is? Outlining your requirements for each step of the process is key in avoiding common pitfalls.

* What is the data?
* Size of the data?
* Is it compressed?
* What are the staging environment storage requirements?
* Tools for analysis?
* Preparing the data
* Moving it around.
* Putting it in production.

In my scenario, I had a single file of Web log data that was 47GB in file size. I knew that with a file this size, no matter what decisions I made or outlined, it was going to take some time to accomplish each stage given the hardware resources I had (the aforementioned laptop). I did not have enough disk space to extract it and load it into a database on my computer and I would expect the same issue with a lot of people when working with file sizes like this. So then next step was to rar the file, which took about an hour and SCP it over to a local server in my network. Using an SSH program, i was able to unrar the file and then connect to an Infobright command prompt and start to load the data into a staging table.

It ultimately was 157 million records. Where I ran into problems were with the format of the line terminators of the file. Because it came from a Windows machine and I moved it down and rarred it onto a Linux box, the line terminators were different, but I did not find that out until 30 minutes into the first load data exercise. I made the correction and then 38 minutes later, I had a count of 157 million records...... Something to note, I did use the reject file parameters for the data load and was actually surprised that it encountered no data errors during the load.

Now I was able to use Toad and take a look at the actual data and sample it accordingly. This allowed me to decide what data points I could optimize and what points were important. I had to look into making this data more optimal for the analysis environment so it could be queried in a timely fashion. I made a few DDL changes with the table structure and was able to test the response time to my satisfaction.

Now since this was going to a production machine, i had to dump the data to a file, rar it up, send it to the server and load data again. I do remember thinking to myself, this was an exercise. By the end of the day, I had the file in a production database and quite optimized. Looking back at what I did to accomplish this taught me this:

1. Always flowchart each step and document it
2. Be patient, but know as much as you can about the data itself
3. Know your environment(s) implicitly
4. Be prepared for something to error and how to identify where the breakdown is

The key point I learned is process and preparation. Whether you are working with 47GB of data or 47TB, the point is still valid. Stay tuned and I'll let you know what I am doing with this data...

These days I am visiting an amazing place: King Abdullah University of Science and Technology (KAUST, http://www.kaust.edu.sa/). It was established quite recently to strengthen research and academic activities in Saudi Arabia. It shows that, fortunately, there are still people who understand that an investment in science is the best kind of investment that one can imagine.

I am sure I will have an occasion to write more about KAUST in the next couple of days. For now, let me explain the reason why I am here. Namely, to attend the workshop on Three Approaches to Data Mining (http://trees.kaust.edu.sa/Pages/NewsWorkshop-8-06-2012.aspx) organized by the group of Dr. Mikhail Moshkov (http://www.informatik.uni-trier.de/~ley/db/indices/a-tree/m/Moshkov:Mikhail_Ju=.html). The workshop is a continuation of an excellent book on Test Theory, Rough Sets and Logical Data Analysis (http://www.springer.com/engineering/computational+intelligence+and+complexity/book/978-3-642-28666-7). The book compares three approaches to data analysis by focusing on similarities between their goals, foundations and applications.

Let me share with you a couple of citations:

These three approaches have much in common. For example, they are all related to Boolean functions and Boolean reasoning with the roots in works by George Boole. However, we quite often observe that researchers active in one of these areas have a limited knowledge about the results and methods developed in the other two. All three data analysis approaches use decision tables for data representation. A decision table T is a rectangular table with n columns labeled with conditional attributes. This table is filled with values of (conditional) attributes and each row of the table is labeled by a value of the decision attribute d. There are different problems of data and different problems associated with them. A typical problem in all three approaches is the problem of revealing functional dependencies between conditional attributes and the decision attribute. However, all three approaches use different terminology related to this problem.

Indeed, when reading the book and listening to the workshop talks (particularly those introductory ones, such as the one by Dr. Endre Boros (http://www.informatik.uni-trier.de/~ley/db/indices/a-tree/b/Boros:Endre.html)), I can see a number of commonalities. In the book, the authors actually made an effort to identify such commonalities. (For instance, the notion of a reduct, known from Rough Sets as a minimal subset of conditional attributes determining decision, is comparable to a dead-end test in Test Theory. In the same way, the notion of a super-reduct, where some attributes are allowed to be redundant while determining decision, can be compared to a support set in Logical Data Analysis).

This reminds me of another book published this year - Rough Sets: Selected Methods and Applications in Management and Engineering (http://rd.springer.com/book/10.1007/978-1-4471-2760-4/page/1) – where we attempted to gather some practical examples of how Rough Sets can be put together with other approaches in order to efficiently solve the real-world data analysis projects. In the case of both books, the crucial aspect is to understand how different methodologies can complement each other at the level of foundations and applications.

Of course, as human beings, we are often tempted to say that our approach or product is the best one. I could see such attitude many times both in business and science. (Maybe even more often in science...) However, we need to remember that the most valuable solutions emerge usually at an edge of different areas. I think that this is also a part of the vision of the founders of KAUST. And this is also why I’m going to listen to the rest of the workshop presentations very carefully…

Best regards,

Dominik