Big Data

YMC has acquired the startup Sentric, which is also based in Kreuzlingen. Sentric has been the first Swiss provider of services in the field of Big Data. The team has already accomplished numerous successful projects for customers, including applications for the analysis of log files and user behavior, as well as recommendation systems. The employees of Sentric are frequently requested as speakers on conferences, they are also internationally known as the developers of Hannibal, an open source solution for monitoring HBase.

Among Sentric’s personnel are Christian Gügi and Jean-Pierre König, the founders of the Swiss Big Data User Group. YMC was recently certified as the first German-speaking training partner of Cloudera, a leading distributor of the Hadoop platform.

“High traffic web applications automatically generate large amounts of data”, said Jean-Pierre König, who will lead the business unit big data analytics from now on. “The fact that analysis has an intrinsic value is not yet widely recognized.” But especially for large customers it is attractive to be able to purchase recommender systems and analytics from the same supplier as the core application.

Mobile Applications

YMC has been developing web projects for mobile devices for quite some time. Now, a specialized team for the development of mobile applications begins its work: With the acquisition of Sentric, several experts in the areas of interface design and user experience are joining YMC. André Bohna, a respected iOS expert and until recently technically responsible for the iOS development at HolidayCheck AG, takes the lead.

YMC already has a high expertise in developing on the Android platform. “I am delighted to be able to lead an interdisciplinary team that is able to cover the entire value chain, from the first ideas to the implementation on the main mobile platforms.” André Bohna says.

About YMC

YMC AG was founded in 2001 as a specialized service provider for web technologies. The company employs 24 experts at Kreuzlingen with specializations such as software engineering, project management / development methodology, creation and design as well as server administration and operation.

YMC provides consulting, creation, custom development and safe (highly-available) hosting as well as operation and maintenance for online projects. Reference customers include Swiss Radio and Television (SRF), ETH Zurich, SOS-Kinderdorf Germany, WWF Switzerland and the publishing group Georg von Holtzbrinck. The project references include web portals with a focus on publishing systems and e-commerce, mobile and big data applications. The service also includes the technical integration of project solutions into existing IT systems and business processes as well as long-term maintenance and support contracts.

YMC sets a high value on standards compliance and usability and prefers to use agile development methods such as Scrum or Kanban. YMC also has a specialization in open source. Participating in relevant conferences and community events but also publishing articles and source code belong to the range of responsibilities of each employee and thus to the working culture. YMC also acts as a sponsor and grants its employees the freedom to create their own open source initiatives.

Newest Features

Easier Configuration

Thanks to another contribution from Alexandre Normand, Hannibal is now able to detect most logfile-patterns by itself. This means Hannibal will now display compaction-metrics without further configuration in most cases.

This feature has actually been included in Hannibal for two months, but I thought it is really worth mentioning it.

Easy Deployment

Thanks to GitHub-Issue 17, Hannibal can now be built into a single tgz file. This way you are able to build Hannibal on a different machine from where you run it. See the README for details.

Maybe we could also provide precompiled packages in the future. What do you think?

Sort Options for the Table-View

Thanks to another GitHub-Issue 12, the sort-order for the Region Sizes Chart can now be changed. This could be handy when you need to see the size of the neighbours for a region.

Table chart with new sort options

Experimental Compactions Graph

When I thought about new features for Hannibal, I thought one of the reasons to use Hannibal is to lessen the occurence of compaction storms. But actually, there is no way to see them. So I tried to create an experimental graph that visualizes the overall compactions over the cluster:

The new experimental Compaction History Chart

I don’t know wether that is really helpful though. What do you think about it? If you are keen you can test the graph by yourself (it’s available in the next-branch). The chart is currently a bit hidden: it’s located right below the Region Distribution chart). The chart is located on the page Compactions, which is available in the main menu.

I think the graph would be really great if you could see what impact the compactions have on your read/write performance.

Future Development

We keep asking ourselves: “What is the main goal for Hannibal?”. The answer is simply that it provides stuff to help tuning the HBase setup, that doesn’t come out of the box with HBase.

After attending Lars George’s talk: HBase Sizing Notes at Hadoop Summit in Amsterdam, I believe that we are on the right track. We already addressed things like region-splits and compaction-storms. But there is so much more that can be done wrong and many things can only be detected by looking at the logfile, nowhere else -  that’s where I believe Hannibal should jump in.

I think about concentrating on getting more out of the logs. One great example is the forced memstore flushes. These flushes occur when the number of HLogs is too high or under memory pressure. For the first case, a log-entry is written: “Too many hlogs: logs=33, maxlogs=32; forcing flush of 1 regions(s)“. For Hannibal, it should be easy to record and put warnings like these on a graph.

I would love to hear from you what you think the right direction could be for Hannibal.

#HadoopSummit great that #hadoop has landed on the european mainland. Thank you @hortonworks

— Rob Dielemans (@robdielemans) March 21, 2013

This event was a great opportunity to meet the leaders, share development and administrative experiences and finally to see great new stuff in action.

From noSQL to proSQL

The Hadoop ecosystem is now stronger than ever. We have seen more and more expansion in development tools and abstractions as well as administrative support over the last year. Nevertheless some parts are still missing or simply not usable for certain users. The Hadoop community is working hard on this.

I can’t help noticing that the NoSQL movement is now the proSQL movement #HadoopSummit

— Steve Jones (@mosesjones) March 21, 2013

The major focus of the community is on fast query capabilities. We have seen different initiatives over the last year with Impala and Apache Drill. Now Hortonworks introduced Stinger and Tez.

#hadoopsummitStinger is a community initiative to make SQL queries on Hadoop run faster, not a single project

— Bence Arato (@BenceArato) March 21, 2013

According to Hortonworks, 50% of the Hadoop users depend on Hive as structured query engine. Some of the major drivers behind the scenes are today’s data warehouse or analytic platforms. SQL is the de-facto standard for business intelligence use cases such as interactive data exploration, visualization and parameterized reporting. To ensure Hive remains the “de-facto standard” for SQL queries with Hadoop, Hive’s SQL capabilities and its query performance must be enhanced. That’s what Stinger/Tez is about.

#hadoopsummit @t3rmin4t0r on stinger: “Aim is to get interactive queries responsibly, not ‘performance at any cost’! twitter.com/acmurthy/statu…

— Arun C Murthy (@acmurthy) March 21, 2013

Hadoop and Enterprise Data Warehouse

We have seen many projects in the early stages, prototypes and proof of concepts, where companies learn and try Hadoop. It somehow very often starts with downloading a sandbox and leads to small/midsize Hadoop cluster deployments for ETL/ELT processing next to a data warehouse or analytics platform. But where does this lead us?

I wonder when I will hear the first time that “#Hadoop is no longer a addition to your traditional #DWH, it’s an replacement” #hadoopsummit

— jpkoenig (@jpkoenig) March 20, 2013

My question has been answered by Patrick Angeles’s talk: “Hadoop and the Enterprise Data Warehouse”. He outlines that Hadoop will take an increasingly larger role in the enterprise data environment. We will live with data warehouses side-by-side with Hadoop for a long time to come. It’s also equally likely that existing data warehouse products and solutions will evolve to become more Hadoop-ish.

#hadoopsummit panel: the easiest way to find the money for a Hadoop cluster is cut back on oracle and netezza support

— Steve Loughran (@steveloughran) March 21, 2013

Try it, learn it, do it!

CC 2.0 by Chang’r | http://flic.kr/p/qxaCm

Intersection of Business and People

This is where emotional innovation occurs. Where there is a real connection between the business and the people, two way dialogue will occur.  This will lead to the people becoming more and more loyal as the business listens and adapts their portfolio of products/services to the people.  A great balance of desirability and viability are achieved and your customers become your greatest evangelists.

Marketing is an exercise in relationship building. Too often, the marketer gets caught up in the idea that the customer experience is just about customers interacting with a product or service. This is simply not the case. One of the chief opportunities frequently overlooked is the ability to experience customers. Most companies are operating blind at this point. They deliver products and services to a customer as a stickman. What do they really know about their customers? Are your customers your greatest evangelists?

Big Data

Today we have affordable solutions to store huge amount of data from different sources. We even have the capabilities to process data in parallel in order to do analytics, fraud and security detection, mining, risk management, business intelligence and many more. But how could Big Data support an organisation to build a better customer experience model?

Results-oriented customer experience marketing relies on effectively communicating how the product or service solves problems through use of the product or service. Turning your marketing efforts to addressing the problems and needs of customers is the foundational tenet of delivering incredible customer experience.

But what is the core you want to do with your business? You have to demonstrate value to produce loyal customers, to increase revenue by getting people to purchase more from you, and to attract new customers. You have to be committed to delivering value as you reduce your operating/production costs whilst keeping product quality high. Does the result-oriented customer experience model address this? Not in most of the cases. It focuses efforts to produce loyal customers that keep coming back. And here is one way to support this initiative.

The Hollywood Principle

Most of our digital devices nowadays are connected to the Internet and we will buy more and more with built-in Internet connectivity in the future. One driver for this is the need of personalisation or customisation. As an example, think about a coffee machine. A housing hiding the internals, a few buttons, a water tank, coffee container and a cable. As the manufacturers of this product, you have no details about your customers’ interactions with the product, their preferred settings and the condition of the coffee machine itself. Now, imagine it’s equiped with LAN or WiFi hardware and connected to the Internet. Besides the fact that you are now able to experience customers’ behaviour and usage patterns, the coffee machine is in a position to submit its condition back to you. This information is almost always most successful when doing failure pattern detection. As a customer, I do not have to call a service hotline any longer. I would expect a pre-fail service for all my devices. This opens up a complete new business model for services and products and drives emotional innovation.

That’s what we call the Hollywood Principle: Don’t call us. We’ll call you!

From a wider perspective, this model could be applied to almost any digital device in the consumer and business to business market. It doesn’t matter whether its a TV set-top box, a coffee machine, a TAN generator or a production line.

Big Data can help you build sustainable products with a built-in pre-failure handling by collecting and analysing it’s runtime conditions. There is no question about whether manufacturers will do this, it’s just a question of time.

Think about your products and what you actually know about how they work for your customers…maybe it’s time to look for more information – before your competitors do!

Nathan Marz defines the most general data system as a system that runs arbitrary functions on arbitrary data. This leads to the following equation “query =  function(all data)” which is the basis of all data systems. The Lambda Architecture defines a clear set of architectural principles for building robust and scalable data systems that obey the equation above. He is also currently writing the book “Big Data – Principles and best practices of scalable realtime data systems”.

The Lambda Architecture is based on three main design principles:

• human fault-tolerance – the system is unsusceptible to data loss or data corruption because at scale it could be irreparable.
• data immutability – store data in it’s rawest form immutable and for perpetuity.
• recomputation – with the two principles above it is always possible to (re)-compute results by running a function on the raw data.

In general the Lambda Architecture is composed of three layers: the batch layer, the serving layer and the speed layer.

Batch layer

The batch layer contains the immutable, constantly growing master dataset stored on a distributed file system like HDFS. With batch processing (MapReduce) arbitrary views – so called batch views are computed from this raw dataset. So Hadoop is a perfect fit for the concept of the batch layer.

Serving layer

The job of the serving layer is to load  and expose the batch views in a datastore so that they can be queried. This serving layer datastore does not require random writes – but must support batch updates and random reads – and can therefore be extraordinarily simple (candidates could be ElephantDB or Voldemort).

Speed layer

This layer deals only with new data and compensates for the high latency updates of the serving layer. It leverages stream processing systems (Storm, S4, Spark) and random read/write datastores to compute the realtime views (HBase). These views remain valid until the data have found their way through the batch and serving layer.

To get a complete result, the batch and realtime views must be queried and the results merged together .

Conclusion

The Lambda Architecture is the first approach that handles the complexity of Big Data systems by defining a clear set of principles. Sentric adopted these architectural principles (or at least part of them) for our customers as they are great approach that can be applied to any Big Data system. Specifically immutability, human fault-tolerance and recomputation are really nice principles that can be easily adopted with the Hadoop platform.
Depending on realtime requirements, often enough the speed layer is not even needed.  If omitted, it makes the whole system even less complex, but the beauty of the Lambda Architecture is that the speed layer can be integrated later on without a huge hassle.

In part II of our series we’ll write about the batch layer with an example case. So stay tuned!

[root@cdh-master ~]# hadoop fs -cat /user/hive/warehouse/routerlogs/part-00000
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,MLME,MLME-AUTHENTICATE.indication(98:0c:82:dc:8b:15, OPEN_SYSTEM)
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,MLME,MLME-DELETEKEYS.request(98:0c:82:dc:8b:15)
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,IEEE 802.11,authenticated
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,IEEE 802.11,association OK (aid 2)
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,IEEE 802.11,associated (aid 2)
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,MLME,MLME-ASSOCIATE.indication(98:0c:82:dc:8b:15)
1358756939,2013,1,21,9,28,59,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,MLME,MLME-DELETEKEYS.request(98:0c:82:dc:8b:15)
1358757010,2013,1,21,9,30,10,+01:00,buffalo,hostapd,wlan0,98:0c:82:dc:8b:15,IEEE 802.11,deauthenticated

As you can see, there is still some noise in the last column. We are interested in “authentication OK‘ and “deauthenticated” messages only. The messages from the router are not standardized (as are protocols such as TCP). We found that those two status messages are the closest ones to our understanding of a “login”/”logout” on the router. Let’s reduce the data set to those lines. During this step we do the duration calculation as well.

We used Cloudera’s real time query engine Impala for this task and here is how the query looks like (ts refers as timestamp):

SELECT A.ts, MIN(B.ts - A.ts), A.host, A.mac_address FROM routerlogs A, routerlogs B WHERE A.host = B.host AND A.mac_address = B.mac_address AND A.ts <= B.ts AND A.message LIKE '%authentication OK%' AND B.message LIKE '%deauthenticated%' GROUP BY A.host, A.mac_address, A.ts;

We already talked about Impala’s early state of development and that it lacks the ability to CREATE a table from a query output. At this point we did copy & paste the results into a CSV file, created a new Hive table called ‘visit_duration’ and loaded the CSV file into it. Here is how we did it:

create table visit_duration (
ts int,
duration_in_seconds int,
router string,
mac_address string)
row format delimited
fields terminated by ',';

Now we have the data we need to answer the following questions:

• How many people visited the store (unique visitors)?
  Note: Unlike the traditional customer frequency counter at the doors we have mac addresses at the log files that are unique for mobile phones. Supposed people do not change their mobile phones we can recognize unique visitors and not just visits.
• How many visits did we have?
• What is the average visit duration?
• How many people are new vs. returning?

While we had a setup with 2 WiFi routers to simulate different stores we continue to describe the process for just one of them called “buffalo”, aka store number one.

Counting the visits for store number one is very simple:

Copyright (c) 2012 Cloudera, Inc. All rights reserved.

(Build version: Impala v0.3 (3cb725b) built on Fri Nov 23 13:51:59 PST 2012)
[localhost:21000] > SELECT COUNT(*) FROM visit_duration WHERE router  = "buffalo";                    
135

The plot (figure 1) indicates that about 85% of the visits were detected in store number one and about 15% in store number two. One might draw the conclusion that store number one is in a much better location with more occasional customers. But let’s gain more insights by analysing the number of unique visitors.

Figure 1 – Visits for stores number one & two

Collecting the number of unique visitors is even simpler as we have the mac addresses of visitors that make them unique:

[localhost:21000] > SELECT COUNT(DISTINCT(mac_address)) FROM visit_duration WHERE router = "buffalo";
9

This plot (figure 2) gives us more details about the customers. It turns out that the 135 visits in store number one were caused by just 9 unique visitors while store number two encountered 5 unique visitors.

Figure 2 – Unique visitors

This shows us how important it is to have a thorough look at your data. We realize now, that the big difference shown in figure 1 is not that big anymore. It also shows us that there must have been some customers who returned to store number one. So let’s go into more details here.

To answer the new vs. returning ratio we had to perform this query:

[localhost:21000] > SELECT count(distinct(A.mac_address)) FROM visit_duration A, visit_duration B WHERE A.mac_address = B.mac_address AND A.ts != B.ts AND A.router = "buffalo";
7

This result gives us the number of returning users. Since we already know the total number of visitors (which is 9 for store number one), we are able to calculate the proportion of new users and plot a graph (figure 3).

Figure 3 – New vs. returning users

This plot (figure 3) indicates that we have more returning than new users in both stores. In store number two we didn’t see a new user over the past 4 days at all. It’s probably a good idea to start a marketing campaign which aims at new customers, e.g. to give out vouchers for the first purchase.
But maybe there are other reasons behind this figures. Store number one might be located in a shopping mall and store number two might be located somewhere in town where people like to walk around when the sun is shining. Perhaps it was raining during the last 4 days and store number one encountered the visits of some new customers because they chose the mall to go shopping and decided to visit store number one out of comfort, as they were “trapped” in the mall anyway. This assumption gives a perspective of what is possible with our BigData approach: why don’t we include weather data and investigate the effects on our visitors?

To investigate whether a customer just popped into our store out of boredom, let’s have a look how long he stayed in it. Answering the question about visit duration is done by using the aggregate function:

[localhost:21000] > SELECT AVG(duration_in_seconds) FROM visit_duration WHERE router = "buffalo";
976.6666666666666

The average visit duration in store number one was around 00:16:16h while the average visit duration in store number two was 00:06:06h.

Figure 4 – Visit duration over the past 4 days

The plot (figure 4) for the last 4 days vividly visualizes that the visit duration in store number one was evenly distributed while the distribution in store number two shows some peaks. We can also see that visitors tend to stay in shop number one much longer. Assuming that both shops sell the same product (which seems to need some consultation) one might think that store number two did not sell a single product. But maybe the customers just enjoyed consultation in store number one and then bought the product in store number two. We would need to include sales figures to investigate this.

During our work of writing queries we acquired a better understanding of the data and the information it carries. Unsurprisingly, we realized that we can answer a different question as well:

• What is the average length of time between two visits?

And here is how it goes:

[localhost:21000] > SELECT B.ts, MIN(B.ts - A.ts), A.router, A.mac_address FROM visit_duration A, visit_duration B WHERE A.router = B.router AND A.router = "buffalo" AND A.mac_address = B.mac_address AND A.ts + A.duration_in_seconds <= B.ts GROUP BY A.router, A.mac_address, B.ts;
1358758959	5045	buffalo	10:68:3f:40:20:2d	
1358771917	754	buffalo	d8:d1:cb:e9:ed:6c	
1358766344	628	buffalo	d8:d1:cb:e9:ed:6c	
1358764299	47	buffalo	d8:d1:cb:e9:ed:6c	
1358771935	18	buffalo	d8:d1:cb:e9:ed:6c	
1358517188	400	buffalo	24:ab:81:91:c8:62	
1358764341	89	buffalo	d8:d1:cb:e9:ed:6c  
…

Since aggregated functions can not be nested, we calculated the average by hand: 7332.484127 seconds which is around 02:02:12h for store number one and 71053.64286 seconds which is around 19:44:14h for store number two.

Now let’s analyse the behavior of one particular user over both stores:

SELECT B.ts, MIN(B.ts - A.ts), A.router, A.mac_address FROM visit_duration A, visit_duration B WHERE A.router = B.router AND A.mac_address = B.mac_address AND A.ts + A.duration_in_seconds <= B.ts AND A.mac_address = "10:68:3f:40:20:2d" GROUP BY A.router, A.mac_address, B.ts ORDER BY B.ts LIMIT 100;
...
1358759467	11	buffalo	10:68:3f:40:20:2d
1358760760	1293	buffalo	10:68:3f:40:20:2d
1358760892	132	buffalo	10:68:3f:40:20:2d
1358761202	326626	fonera	10:68:3f:40:20:2d
1358761459	257	fonera	10:68:3f:40:20:2d
1358761492	33	fonera	10:68:3f:40:20:2d
1358761552	60	fonera	10:68:3f:40:20:2d
1358761596	704	buffalo	10:68:3f:40:20:2d
...

The calculated average duration between visits over both stores for this particular user is 15009.77778  seconds which is around 04:10:09h.

Figure 5 – Average Duration Between Visits of one particular user

There is a lot of useful information that can be derived from this plot (figure 5). Firstly, there is a repeating pattern of step-ins and step-outs within a short period of time. Perhaps this user was having a mobile phone conversation somewhere around the door of this store. Secondly, there was a step-out of store number one and a step-in into store number two within just 28 seconds. Imagine for a while both routers were in the same store on different floors. This pattern is a clear indicator that this particular user went from one floor to another, which should be interpreted as one visit.

Conclusion

• Analysing WiFi router log files could be done with a traditional RDBMS database approach as well. But one of the main benefits of this architecture is the ability to store a variety of semi structured files and to apply a schema afterwards. As the raw data contains a lot of information beyond our questions, it’s easy to answer different questions ad hoc. This effect could be leveraged whenever new log data from other sources can be processed and joined together.
• Answering such questions based on WiFi router log files can be done without programming software by using graphical designers from existing BI/analysis and reporting tools with a BigData platform integration.
• Given the fact that one can quickly ramp up a test cluster with a few nodes, similar problems can be solved within one day with a handful of engineers. The Cloudera Manager makes it very easy to install, maintain and monitor a Hadoop cluster and it can be used without profound understanding of the whole ecosystem.
• Impala as a query engine is still in beta phase but querying massive amounts of data in real time is definitely the future. Hive does not support implicit JOINs that we used here. Furthermore we used JOIN with the “=” condition, where the left and right side comes from the same table which is not supported in Hive.
• It’s possible to track paths from people based on WiFi router signals using triangulation. There a few projects following this idea. You can find some links below.
• Assuming that a retail store has several floors, each of which equipped with a WiFi router, each visit interpreted as “login”/”logoff” on a particular router is not correct anymore. Additional data processing is required to identify visitors that just walk through the store and visit different floors as they “login” and “logout” within a short period of time between the levels, e.g. within 30 seconds.

Similar projects/solutions

• (German) Handysignale verraten Wege der HB-Passanten
  Wo wird es für die Bevölkerung eng am Hauptbahnhof, woher strömen die Menschen in den Bahnhof, wohin gehen sie und wo staut es? Diese Fragen soll eine neue Technologie beantworten, die frei verfügbare Signale von Mobiltelefonen ortet. Zürcher Oberländer – 09.10.12
• (German) MagicMap  - Ein System zur kooperativen Positionsbestimmung über  WLAN
  MagicMap ist eine reine Softwarelösung, die bei den mobilen Systemen außer einer konventionellen WLAN-Ausstattung keine weitere Hardware erfordert. Die WLAN Access Points können beliebig verteilt sein und es sind weder Eingriffe an der AP-Hardware noch an der Software nötig. magicmap.de 
• (German) Euclid Zero: US-Startup überträgt Monitoring für Onlineshops auf lokalen Handel
  Das US-Startup Euclid Analytics nutzt die WLAN-Funktion von Smartphones, um das Einkaufsverhalten von Kunden im lokalen Handel zu verfolgen und auszuwerten. So entstehen umfangreiche Statistiken, vergleichbar mit den Besucherstatistiken eines Onlineshops aus Google Analytics. t3n – 22.01.2013
• PlaceLab Geopositioning system
  The Place Lab software listens for wireless network base-stations, it can then look-up the coordinates of whatever networks it finds and will use triangulation to calculate its position. 4BA2 Technology Survey
• OsmocomBB Projct
  OsmocomBB is an Free Software / Open Source GSM Baseband software implementation. It intends to completely replace the need for a proprietary GSM baseband software, such as a) drivers for the GSM analog and digital baseband (integrated and external) peripherals b) the GSM phone-side protocol stack, from layer 1 up to layer 3. osmocom.org
• Indoor positioning system (IPS)
  An indoor positioning system (IPS) is a network of devices used to wirelessly locate objects or people inside a building. Wikipedia

Our Moment

And this is how a hackathon at YMC/Sentric looks like:

Jean-Pierre König, CTO Sentric

This is the final post of this series. If you have questions or feedback: jean(minus)pierre(dot)koenig(at)sentric(dot)ch.

Continue reading:

Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 1
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 2
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 3 

Let’s start by looking at the raw data sample on HDFS.

2013-01-17T15:50:41+01:00 192.168.201.197 dropbear[1172]: Child connection from 192.168.201.99:55001
2013-01-17T15:50:46+01:00 192.168.201.197 dropbear[1172]: Password auth succeeded for 'root' from 192.168.201.99:55001
2013-01-17T15:50:52+01:00 192.168.201.197 dropbear[1172]: Exit (root): Disconnect received
2013-01-17T15:52:14+01:00 fonera hostapd: wlan0: STA 8c:64:22:3a:74:1f IEEE 802.11: disassociated due to inactivity
2013-01-17T15:52:14+01:00 fonera hostapd: wlan0: STA 8c:64:22:3a:74:1f MLME: MLME-DISASSOCIATE.indication(8c:64:22:3a:74:1f, 4)
2013-01-17T15:52:14+01:00 fonera hostapd: wlan0: STA 8c:64:22:3a:74:1f MLME: MLME-DELETEKEYS.request(8c:64:22:3a:74:1f)

In order to import the raw data to the Hive data warehouse we need to parse the raw data into a comma separated format. From the Data Scientist perspective we would like to accomplish this task with a proper tool. There are quite a few open-source BI tools on the market for this: Palo, SpargoBI, Pentaho, Talend and many more. We did a short evaluation and finally used Pentaho Data Integration. It’s Cloudera Distribution support enabled us to design a MapReduce job for distributed processing across multiple nodes for this task without any programming environment.

Pentaho Data Integration’s Graphical Designer

The map phase will read all the raw log files collected by Flume on HDFS. The input is interpreted as TextInputFormat and therefore every line will go through a regex evaluation during the map phase. With this step we accomplished two things in one step: filtering and transformation.

Transformation

By matching a particular line against a regular expression here we can group information that we are interested in. We use this to split up the line in fields that will be used as columns later on. This is the regex for the transformation:

^((\d{4})-(\d{2})-(\d{2})\w(\d{2}):(\d{2}):(\d{2})([+-]\d{2}:\d{2})) ([.a-zA-Z_0-9]*?) (.*?): (.*?): \w*? ([\w+:]{0,18}) (.*?): (.*)$

Matching lines have how a pseudo schema like this:

iso_8601 String
year Integer
month Integer
day Integer
hour Integer
minute Integer
second Integer
timezone String
host String
facility_level String
service_level String
mac_address String
protocol String
message String

Filtering

All lines that do not match the regular expression are filtered. We are not interested in those lines because they carry useless information for this case. Here some examples:

2013-01-17T15:50:41+01:00 192.168.201.197 dropbear[1172]: Child connection from 192.168.201.99:55001
2013-01-17T15:50:46+01:00 192.168.201.197 dropbear[1172]: Password auth succeeded for 'root' from 192.168.201.99:55001
2013-01-17T15:50:52+01:00 192.168.201.197 dropbear[1172]: Exit (root): Disconnect received

During the next step ‘Filter Rows’ we remove empty lines. This is to ensure there are no empty lines in the output after matching against the regular expression.

In order to produce a comma separated file we used a ‘User Defined Java Expression’ and concatenate the emitted fields delimiting by ‘,’. At this point we did a further transformation: ISO 8601 string to unix timestamp, a very important one. To answer time related questions, e.g. average visit duration we need values to calculate with. The unix timestamp is suitable for this.

Here is the ‘User Defined Java Expression’:

(javax.xml.bind.DatatypeConverter.parseDateTime(iso_8601).getTimeInMillis()/1000) + "," + year + "," + month + "," + day + "," + hour + "," + minute + "," + second + "," + timezone + "," + host + "," + facility_level + "," + service_level + "," + mac_address + "," + protocol + "," + message

At the very end, the transformed and parsed raw data lands in HDFS once the MapReduce job has finished. Let’s look at a random sample:

1358765267,2013,1,21,11,47,47,+01:00,buffalo,hostapd,wlan0,10:68:3f:40:20:2d,IEEE 802.1X,authorizing port
1358765267,2013,1,21,11,47,47,+01:00,buffalo,hostapd,wlan0,10:68:3f:40:20:2d,WPA,pairwise key handshake completed (RSN)

Now we have parsed log files on HDFS. We used Pentaho Data Integration once again to import the data to Hive’s warehouse. Before doing so we created a table that matches the previously defined schema with the query editor of the beeswax user interface:

Create the ‘routerlogs’ table in Hive

Loading data into the table is basically done by copying files on HDFS from hdfs://cdh-master.cdh-cluster:8020/user/jpkoenig/routerlogs/parse to hdfs://cdh-master.cdh-cluster:8020/user/hive/warehouse/routerlogs with a wildcard part.* for the file mask.*

* We configured our Pentaho MapReduce job to clean output path before execution. Every time the job is executed it will produce newly created files on HDFS with the same file mask. Here is what the ouptut folder looks like.

[root@cdh-master ~]# hadoop fs -ls /user/jpkoenig/routerlogs/parse
Found 91 items
drwxrwxrwx   - jpkoenig jpkoenig          0 2013-01-21 15:24 /user/jpkoenig/routerlogs/parse/_logs
-rw-r--r--   3 jpkoenig jpkoenig     118963 2013-01-21 15:25 /user/jpkoenig/routerlogs/parse/part-00000
-rw-r--r--   3 jpkoenig jpkoenig     100500 2013-01-21 15:25 /user/jpkoenig/routerlogs/parse/part-00001
-rw-r--r--   3 jpkoenig jpkoenig      11826 2013-01-21 15:25 /user/jpkoenig/routerlogs/parse/part-00002
…

Importing this into the Hive warehouse by copying files on HDFS is not suitable for incremental updates. This procedure will replace the table data every time. For a production system you should consider the following things:

• automating the MapReduce job on a scheduled base, e.g with Oozie
• ensure incremental updates on the Hive table by using partitioned table technique or unique output file naming

That’s it for this step. You can download the Pentaho jobs here:

Pentaho project files: Parse Mapper, MapReduce Job, Load to Hive Job

Jean-Pierre Koenig, CTO at Sentric

Now we have everything in place. It’s time to write queries! We will write about querying the data with Impala in the next post. Stay tuned! And, again, contact me if you have questions or feedback: jean(minus)pierre(dot)koenig(at)sentric(dot)ch.

Continue reading:

Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 1
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 2 
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 4

As you can see in figure 2 from the previous post, first of all we had to collect log data as a data source to be read by Flume afterwards. There were two WiFi access points available, a Buffalo WZR-HP-G300NH2 and a Fonera. Both of them installed with OpenWRT as operating system.
Therefore we configured the WiFi access points to send all their local syslog messages to a central syslog server. This can easily be done via OpenWRT’s Unified Configuration Interface, simply called UCI. Assuming your syslog server listens on address 192.168.0.1, and we want the most detailed log output, the configuration looks like:

#>uci set system.@system[0].log_ip=192.168.0.1
#>uci set system.@system[0].conloglevel=8
#>uci commit
#>reboot

Additionally, the syslog server needs to be configured to accept messages from remote hosts and instructed where to write those messages to. In our scenario we just wanted to write them to a text file. Since we were using Rsyslog as syslog server, the corresponding configuration file is /etc/rsyslog.conf.

The messages are being sent by UDP packages, so we had to enable syslog reception by UDP on port 514. Assuming that each of the WiFi access points has an IP address starting with 192.168.0 the configuration settings are:

$ModLoad imudp
$UDPServerRun 514
if $fromhost-ip startswith '192.168.0.' then /var/log/hackathon-logs.log
& ~

The syslog daemon needs to be restarted by executing /etc/init.d/rsyslog restart to apply the changes. Afterwards you’ll see an excerpt of the log-file:

2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: start authentication
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy IEEE 802.1X: unauthorizing port
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: sending 1/4 msg of 4-Way Handshake
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: received EAPOL-Key frame (2/4 Pairwise)
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: sending 3/4 msg of 4-Way Handshake
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: received EAPOL-Key frame (4/4 Pairwise)
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy IEEE 802.1X: authorizing port
2013-01-21T13:39:51+01:00 buffalo hostapd: wlan0: STA 10:68:3f:40:xx:yy WPA: pairwise key handshake completed (RSN)
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy IEEE 802.11: authentication OK (open system)
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy MLME: MLME-AUTHENTICATE.indication(24:ab:81:91:c8:62, OPEN_SYSTEM)
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy MLME: MLME-DELETEKEYS.request(24:ab:81:91:c8:62)
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy IEEE 802.11: authenticated
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy IEEE 802.11: association OK (aid 1)
2013-01-21T13:41:25+01:00 fonera hostapd: wlan0: STA 24:ab:81:91:xx:yy IEEE 802.11: associated (aid 1)

Now that we have a log-file as data source we set up Flume to stream the incoming content to HDFS. Due to the Flume terminology we had the components:

• data from the log-file as source
• HDFS folder /user/flume/hackathon-datastream as sink
  we preferred a flat directory layout to simplify the access/processing of the files later on
• a channel, c1, to connect the source to the sink

The easiest way of streaming the incoming log messages to the channel (and additionally keeping the log-file on the local hard disk) is to configure the source of type exec. Thereby you just have to configure a linux command that “listens” on that file, yes, it is just a tail command. Additionally we wanted to have the data as plain datastream files in HDFS, not as so called sequence files. Processing those files afterwards was much easier in our particular hackathon environment. Here is how the overall flume configuration looks like:

a1.sources = r1
a1.sinks = k1
a1.channels = c1
a1.channels.c1.type = memory
a1.channels.c1.capacity = 1000
a1.channels.c1.transactionCapacity = 100 
# get data from exec command
a1.sources.r1.type = exec
a1.sources.r1.command = tail -F /var/log/hackathon-logs.log
a1.sources.r1.interceptors = i1 i2
a1.sources.r1.interceptors.i1.type = org.apache.flume.interceptor.HostInterceptor$Builder
a1.sources.r1.interceptors.i1.preserveExisting = false
a1.sources.r1.interceptors.i1.hostHeader = hostname
a1.sources.r1.interceptors.i2.type = org.apache.flume.interceptor.TimestampInterceptor$Builder
# define hdfs sink
a1.sinks.k1.type = hdfs
a1.sinks.k1.hdfs.path = hdfs://cdh-master.cdh-cluster:8020/user/flume/hackathon-datastream
a1.sinks.k1.hdfs.rollInterval = 120
a1.sinks.k1.hdfs.rollCount = 100
a1.sinks.k1.hdfs.rollSize = 0
a1.sinks.k1.hdfs.fileType = DataStream
# bind source and sink to channel
a1.sources.r1.channels = c1
a1.sinks.k1.channel = c1

The lines containing “interceptors” are needed to put additional header information in the data stream, in our case the hostname and timestamp.

Flume was finally started manually by executing:

flume-ng agent --conf-file ./flume-datastream.conf --name a1 -Dflume.root.logger=INFO,console

Since Flume is able to collect data from various sources it is possible to configure Flume as “syslog server” itself. The WiFi access points would send their log messages directly to the Flume agent. The pro of this configuration is, that you don’t need additional software (the syslog server). Why we’ve chosen the method described above is that we had a running syslog server already and our first attempt was to use that.

Gerd Koenig, Certified Hadoop Administrator

Now that we have the log data in HDFS, the next article of this series will proceed with transforming, parsing and filtering them. Stay tuned for part 3 and feel free to drop me your feedback, gerd(dot)koenig(at)sentric(dot)ch.

Continue reading:

Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 1
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 3
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part4 

We thought the most interesting sensor data (that doesn’t require additional hardware/software) could be the WiFi access points. Especially given that many visitors will have WiFi enabled mobile phones. With it’s log files we should be able to answer at least the following questions for a particular store:

• How many people visited the store (unique visits)?
• How many visits did we have in total?
• What is the average visit duration?
• How many people are new vs. returning?

How do we answer these questions?

Before we started designing a blueprint solution we first of all asked ourselves:

• Who would be asked to answer questions like this?
• Who is this person?
• What tools does this person expect to use?
• And what is a typical skill set of this person?
• How do they work?

From an interview with a industry leading company we knew that these questions will be answered by analysts. They use data warehouses and they typically have a business intelligence (BI), analysis and report tool with access to the data warehouse. They are used to useing SQL to answer questions.

With our experience at Sentric, we knew that solving the problem with a Big Data approach will introduce a new person – the “Data Scientist”. Right, at that point we slightly adjusted our mission.

So, how do we answer these questions as a Data Scientist?

From a high level of abstraction the answer is simple. We need a data management system with three pieces: ingest, store and process.

Traditional Data Management System Approach

We take this basis architecture and replace the generic terms while mapping it onto the Hadoop ecosystem.

Blueprint for a Data Management System with Hadoop

With this Hadoop architecture a Data Scientist should be able to answer the questions without any programming environment. He/she can also use familiar BI, analysis and reporting tools as well.

Setup

We planned a hackathon together with our partner company YMC to prove this concept. Here are the ingredients:

• 2 WiFi access points to simulate two different stores with OpenWRT, a linux based firmware for routers, installed *
• A virtual machine acting as central syslog daemon collecting all log messages from the WiFi routers
• Flume to move all log messages to HDFS, without any manual intervention (no transformation, no filtering)
• A 4 node CDH4 cluster running on virtual machines (CentOS, 2 GB RAM, 100 GB HDD), installed and monitored with Cloudera Manager
• Pentaho Data Integration‘s graphical designer for data transformation, parsing, filtering and loading to the warehouse (Hive)
• Hive as data warehouse system on top of Hadoop to project structure onto data
• Impala for querying data from Hive in real time
• Microsoft Excel to visualize results **

* We actually fired up the two WiFi routers before the hackathon to collect some data for a period of around 4 days.
** Since Impala is still beta it only supports SELECT statements. Therefore it’s not able to CREATE new tables from query results in Hive’s warehouse. With this restriction we decided to copy & paste query results into MS Excel for further analysis and visualization. Once Impala can CREATE tables a Data Scientist can access that data from their BI, analysis and reporting tools.

Jean-Pierre Koenig              CTO at Sentric

In part 2 of this series you find details of data ingestion. If you would like to give us feedback or you want some more details, do not hesitate to contact me, jean(minus)pierre(dot)koenig(at)sentric.ch.

[Update]

Continue reading:

Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 2
Case Study: Retail WiFi Log-file Analysis with Hadoop and Impala, Part 3

To put it in a nutshell:
This course is perfectly suited to get a basic understanding of the concepts behind Hadoop. HDFS and MapReduce are explained very well. I have benefited from it in that I now understand how the different daemons relate to each other, what they are for, as well as what to do in case of an (Hadoop related) emergency.
Now it’s time to gain experience and share it with the community.

If you have any feedback, feel free to contact me by mail or twitter.