BigDW

Just enough python programming for interview

2017-02-04T22:13:00.001-08:00

Factorial of a number (using while loop)

Sum every 3rd number Adding consecutive numbers Checking if a number is prime Palindrome check List functions Average of list Perfect number LCM Recursive Algorithm - Fibonacci series Recursive Algorithm - Exponent Recursive algorithm - GCD

Taming the Data Lake

2016-09-04T11:34:00.000-07:00

Big data is not more a fad that geeks, major enterprises, start-ups alike are in love with - it is a reality driven by the dynamic and diverse nature of channels, business lines, innovative products and customer behavior. All the 4Vs – Volume, Velocity, Variety and Veracity of data are true and us analysts, data scientists, data professionals, strategists, business leaders have to live with. Investments are being made into Technology, Infastructure and Talents but like a wise man once said “all problems in the world cannot be solved by throwing money at it”.

Reality Check:

It is not as simple as creating a data lake where everything can be dumped and Data Scientists and Analysts can feed off of that. The adoption should not be just an Investment question (cost of data storage, data preparation, management and retrieval) on which predominantly the decisions are made. It is also a Returns question (Reports, Business Analytics, Advanced Analytics, Data Products, Decision Engines, etc.) which is usually ignored when making the decision. Investment only decisions usually create a sub-optimal experience for the end users, i.e., it may be efficient for Reporting but may be very slow and inefficient when an Analyst has to use it or vice versa. Adoption and Engagement needs a Strategic framework of key corporate needs, an Tactical Outcome Focused delivery approach and an iterative learning execution model.

Scalable Metrics Model:

RDBMS structure is still one of the most “go-to” framework for Enterprise Data Warehouse and has been so for decades. The reliability, stability, speed, ease of understanding makes it optimal for many core services. The downside is the flexibility, extensibility, cost of modifications and rigidity of the structure which is what Hadoop File System framework tries to address. But lack of structure brings its own problems of performance, reliability, error corrections, etc. and just forcing a structure via Metadata or Aggregates might not be sufficient for a wide variety of users. We need a hybrid framework which brings in the strengths of RDBMS with merits of HDFS whose key objective is to serve the diverse needs of users and is malleable enough to efficiently and effectively change with the needs. It has to be modular enough to predominantly address a bucket of needs (e.g, Reports/Decision Engines by functions) but also with connections that can help connect the dots (e.g., Deep Dive into drivers). The Scalable metrics model is one such option and we are discussing it at the Global Big Data Conference at Santa Clara on Sep 2nd.

More about the Global Big Data Conference:

http://globalbigdataconference.com/53/santa-clara/3rd-annual-global-big-data-conference/event.html

It brings together leaders and practitioners in the field of Big Data and provides a platform for sharing ideas, getting feedback and learning about the new trends and technologies that are in the industry today. We are excited to be a part of it and hope to have a very good chat and learning session.

The slides on Scalable Metrics Model can be found at:

http://www.slideshare.net/BharathiRajaChandras/taming-data-lake-scalable-metrics-model

Metrics on the fly – with Hadoop and Tableau

2015-07-12T19:33:00.002-07:00

Let’s take a use case, customer segmentation. How do we segment our customer base? - just accumulate whatever dimensions and metrics you can find for that user and store on the granularity of customer, then put a reporting layer on top of it for the Analyst to create reports and visualizations.

There are numerous ways to do aggregations - one way is to build Aggregates on top of a star schema. This approach might be costlier to process on a Databases (ELT) or in a ETL tool, especially if our customer base is huge.

Some challenges in building aggregates:

1. Required to do many full table scans - eg # transactions by user, #logins by user

2. The requirement for these metrics changes frequently - we have to make frequent DDL and ETL changes

How about using Hadoop? Hadoop/Hive is good in handling huge datasets, but bad in interactivity and ease of use, where Tableau excels. Let’s discuss how to marry these two technologies to create metric framework where we can add metrics on the fly and actually do data analysis. Assume the required base-tables for deriving dimensions and calculating metrics are injected (using sqoop) into Hadoop and available as Hive tables.

The idea is to create a metrics (final fact) table with a id columns and one metrics column(map datatype) which bags all the metrics. Since Metrics column is a Map datatype, it can hold column names and corresponding metrics values. In this way, we are not constrained to defined columns, we can add as many metrics as we go.

CREATE EXTERNAL TABLE `customer_metrics`(

`customer_id` int,

`metrics` map<string,string>)

Data Model

1. Determine the granularity of the metrics table - In our case its customerId. (can be multiple columns too)

2. Write hive queries to get metrics on the specified granularity. Just create separate hive queries - a query for one or more metrics. All these queries should have same granularity. (Login_attempts,Last_login_date,Transaction_count)

3. Create a staging table with 3 columns( id,key and value)

CREATE EXTERNAL TABLE `stg_customer_metrics`(

`customerId` int,

`key` string,

`value` string)

4. Create the target metrics table

CREATE EXTERNAL TABLE `customer_metrics`(

`customer_id` int,

`metrics` map<string,string>)

ETL framework:

Now, to create a scalable ETL framework in order to add metrics on the fly. The Metrics query is going to be a union of any number of subqueries. Each subquery should follow the standards

1. Should include columns of granularity and an array of key value pairs.

2. Each key value pair should be defined in the format ‘metric_name’=metric_value

3. This can be achieved by concatenating a static string to the aggregated value field.

4. The result will be a union of all multiple subqueries with grain columns and array of key value pairs.

5. Convert array column into multiple rows using hive explode function

6. Split the key value pairs into two columns key and value.

7. This entire query should be loaded in to the staging table created earlier.

INSERT OVERWRITE TABLE stg_customer_metrics

Select

customer_id as customer_id,

split(keyvalue,'=')[0] as key,

split(keyvalue,'=')[1] as value

from (

select u.customer_id, /* METRICS 1&2 - LOGIN ATTEMPTS AND LAST LOGIN DATE*/

array(

concat_ws('=','Login_attempts',cast(count (DISTINCT(case when i.CSTMR_STA_NM = 'LOGIN'

then i.CSTMR_ID End)) as STRING)),

concat_ws('=','Last_login_date',cast(Max(i.chg_dt) as STRING))) as mp

from customer_login

group by u.customer_id

UNION ALL

select u.customer_id, /* METRIC 3: TO GET TRANSACTION COUNT */

array(

concat_ws('=','Transaction_count',cast(count(distinct p.PYMT_ID) as STRING))

) as mp

from user_transaction

group by u.customer_id /* GRANULARITY: CUSTOMER_ID */

) b

LATERAL VIEW explode(mp) mptable as keyvalue

where 1=1

;

The stage table may look like..

Customer_id	Key	Value
1123	Login_attempts	10
1123	Last_login_date	2015-07-07
1123	Transaction_count	2
9989	Login_attempts	20
9989	Last_login_date	2015-06-01
9989	Transaction_count	5

To add new metrics, create a select query and append to the union at staging table load.

Load to final metrics table

ADD JAR udf-0.0.1-SNAPSHOT.jar;

INSERT OVERWRITE TABLE customer_metrics

select customer_id,UNION_MAP(MAP(key,value)) from stg_customer_metrics

group by customer_id;

Customer_id	Metrics
1123	{" Login_attempts":”10”," Last_login_date ":"2015-07-07"," Transaction_count ":"2"}
9989	{" Login_attempts":”20”," Last_login_date ":"2015-06-01"," Transaction_count ":"5"}

View:

Create a view on top of this final table. It helps in

1. Creating a reference metadata

2. Abstracts the complexity of selecting map data-type and changes happening to metrics table (you are free to add new metrics without impacting reporting layer)

3. Easy drag and drop in Tableau reporting layer

4. Different views for different user-groups

CREATE VIEW `vw_customer_metrics` AS

select `customer_id`

,`metrics`[" Login_attempts"] as ` Login_attempts`

,`metrics`[" Last_login_date"] as ` Last_login_date`

,`metrics`[" Transaction_count"] as ` Transaction_count`

From customer_metrics;

Reporting layer (tableau):

Pull this view into Tableau layer as an extract, so that the report will be faster and interactive. You can add new calculations on tableau layer especially for non-additive facts.

(If you are new to tableau, I strongly suggest to download Tableau public at : https://public.tableau.com/s/ and checkout the free learning videos on: http://www.tableau.com/learn/training for anyone who works on data)

Bringing this metrics to tableau layer provides the following advantages which Hadoop layer cannot provide

1. Interactive (faster response)

2. Slicing and dicing ( by not invoking MapReduce every time)

3. Blending with other data sets (quickly! Without IT J)

4. Create calculated columns

5. Visualization (Ofcourse!)

Conclusion:

We designed this idea with the mindset “Business logic at the cost of resources” – implementing the business logic is more important. We need to also think about file-formats, compression and incremental logic depending on specific use-case. Tableau layer is not effective on huge detailed data, aggregating dataset and reducing the rows (and size) matters a lot during data-analysis. Pushing down the business logic to Hadoop layer abstracts the complexity and reduce clutter at Tableau layer.

Hadoop summit 2014 - commentary

2014-06-06T07:09:00.000-07:00

3 days of continuous presentations, some really cool ones, many sales pitches and few useful networking, food and drinks :). Apart from these, My agenda is get the Datawarehousing side of Hadoop, to learn some more tricks for my long running quest to implement a BI solution in Hadoop. I do learnt some and got some benchmarks to prove some designs. I feel confident that a lots of effort put into this space from Hadoop community.

Like one of a presenter quoted Churchill
"Now this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning"

This conference was organized by HortonWorks and not cloudera.YARN and TEZ dominated the talks, I saw "mapreduce is dead" in couple of presentations, hope its for good. All the enterprise users must move to YARN in near future else they will miss the future tools and essential enhancements to current tools like Hive,spark.

In a way These conferences gives an idea how mature the technology is and how serious are the businesses in using it. But you can always relate to this quote "“Big data is like teen sex. Everybody is talking about it, everyone thinks everyone else is doing it, so everyone claims they are doing it.”

Coming to the overall theme - I could see 4 themes from my perspective.

Performance

Parallelism and mapreduce is old story, now getting results fast is the coolest thing.
Key concepts to look for in this area are YARN, TEZ, ORC (compressed columnar format), Parquet (another columnar format), In-memory query engines (Impala, spark), there were talks about collecting statistics to make Hive behave like databases.

Security
Most enterprises brought their data into Hadoop, there is a big gap to be filled in securing data. Some concepts on this line like

Keeping Hadoop behind the Database and restricting direct access to Hadoop. In this way you can utilize the security features of Database for Hadoop objects (teradata and Oracle already has appliances for this)
Hive 0.13 - Improved security features like grant/revoke, ROLE, columns and row level access, permissions for views.
LUKE (Linux unified key setup)
Apache sentry
Apache Knox

Federation
Data blending, this is where big players want to play a role like "We too do Hadoop" and making Hadoop coexists with traditional datawarehouse ecosystem.

Teradata hadoop connector
Oracle smart something!
Microsoft PDW (parallel data warehouse) and PolyBase
My favorite data-blending is by using Tableau (ofcourse on a smaller scale)

ACID
This is the topic Im mostly interested in, How to do ACID on Hive, if not how to mock, because I feel updates are very critical to truly deliver an Datawarehouse in Hadoop.

Store data as columnar format (ORC, Parquet)
Maintain a single partition using compaction(this table is going to be huge, but thats what Hadoop is made for)
Keep appending the history (dont delete).
Get the latest data while reading. There is a new Acidinput and Acidreader to accomplish this.

Have to write a seperate blog for this topic.I need to get deeper the architecture and need to do a POC on this.

Learning something new? - try varying your methods

2014-05-13T14:19:00.002-07:00

Now-a-days Learning is largely democratized and nothing stops you from learning or mastering a totally new subject, thanks to MOOC. (read Massive Open Online Courses.. infographic) These are learning systems which are already disrupting the traditional university structure. Im pretty sure that we are just in the beginning of a great education revolution. (very excited for my 3 year old daughter on her learning options!)

Although free is great, Three things are missing though in these learning platforms -- the planning, control and validation process of traditional methods.(These three are available in some MOOC like coursera). So its upto the learner to acquire the must 3's of any learning venture.

Lets assume you found the subject area let it be Micro economics, a programming language, a new language or natural history. Not sure what right for you or your career? I cannot answer this question as it is a personal thing. But have the attitude of "Whats the worst might happen?" -- "You may end up learning something new, however minuscule it might be" -- "You might loose 3-4 hrs of your life, take it as a wrong movie choice".

The road ahead is not going to be easy especially at the beginning, as I said be an explorer.

"All change is hard at first, messy in the middle and gorgeous at the end." - Robin Sharma

This formula might help in making sure you reach the goal which you are capable off or more than that.

warm-up

1. If its totally new or the one you scared about, cover the basics. Spend a lots of time in understanding the fundamentals. Its worth the time here as it will ease the learning of complex things comes later in process.

2. Dont try or target expert problems first - it may break your courage to venture very earlier in the process.

Finding

1. The method which suits your style. (Youtube, webminars, Books, online courses, community courses, podcasts, audiobooks, slideshare, meetups, specific learning blogs).

2. The material which suits your knowledge. All learning materials are not created equal. It should match your level of competency.

3. Vary your learning methods - Accept the fact that google's or Amazons top search may not find you the one you are looking for, try different methods. And the method should also suit your lifestyle. Like can you watch youtube at office!? read a pdf in your reader while commuting or listening to podcast.

4. Fail Faster - Move ahead, may be a different author, a different youtube playlist, a different course.

5. Find the "one" - the above steps will lead you to this, stuck to it, follow it and go until the level that method can take you.

Execution:

1. Create a "plan" and a achievable target.

2. Make incremental progress. Dont have targets like "learn and build a social site in a month." instead "learn to create a dynamic website using PHP in a week/month"

3. Track your progress based on the plan.This will prevent you from getting distracted.

4. "cost it" - there might be some $ involved in some learning methods, you might be surprised to know how cheaper it is to learn when compared to the returns. The biggest cost you will incur is your time.

5. "try it" - creating a sandbox, executing a sample code or trying to solve an existing problem.

6. "do it" - a small project, get certification if its available or publish it your personal blog. The options are limitless.

Stuck:

You are fortunate if you could find a human who can mentor and good in mentoring. The other options could be "joining a meetup" , following a niche blog, identifying a dedicated forum.. and your "GRIT" to get out of a stuck situation.

“When the pupil is ready, the Master appears”

Happy learning!

"Big Data" - Im a believer!

2014-05-13T13:59:00.003-07:00

What is the use case where Hadoop is "the" best technology to use?. Im working in Hadoop and Big-data for some years, using it for production analytics and learning and improving everyday .Attended numerous big data webminars and conferences. Talked great about the mighty of Big data to those who never used it or scared about it. But I haven't felt the real benefit of using Hadoop or big data ( Im talking about my case !) apart from its cost effective.Most of the stuff can be done using some other existing technology. With the traction Hadoop is getting in technology grounds there must be something really BIG use case here. Yeah there are stories about Facebook, twitter and other sites, but there must be something which Hadoop can only do. I couldn't find that unavoidable purpose for this technology, until...

I recently enrolled in "machine learning" course from Coursera. An awesome course from an awesome teacher (Andrew Ng). I never imagined that I could understand why all the loads of Math in school and college (why the heck differentiation, integration?). I could have learned just languages and computer language. But this course changed my perspective towards math like a slap across the face. All the things we see, use and consume are developed using some form of math. This course is an eye-opener. I would recommend this course to anyone who has a data background, you will not see any data as a waste of memory, you will see it as a gold-mine waiting for the right explorer.

Now while machine learning, there are lots of techniques to predict something (give 'x' and it will give you 'y' not simple like that :0). Im still a beginner but what I found is that we need to do thousands of, in some cases even millions of iterations just to find a simple parameter. And there could be thousands of parameters in some cases. That's the aha! moment for me, Hadoop/ Big data is the only place "where you can store and process humongous data" in a cost effective way. Previously engineers could have limited themselves in number of iteration because of resources, not anymore they have the power of HDFS and mapreduce to store and process respectively.

There are many compelling use-case I seen in numerous webniars, conferences and whitepapers, but machine learning / predictive modeling is the most compelling reason( at least for me) that Hadoop is indispensable for the future analytics world. Especially we are living in digital-social world where the factors(x1,x2,x3...Xn) which could affect any outcome(y) is ever increasing. Now I'm no more a practitioner but a believer!

HBase Conference 2013

2013-06-14T16:47:00.002-07:00

At my current workplace we are using Hadoop and related technologies for Datawarehousing. The main advantage we get out of this was ofcourse cost and scalability. But Hadoop and related technologies (especially Hive,PIG) are not perfect for DW and BI development projects. The major issue we faced are updates and capturing incremental data, since we are trying to do a Dimensional model with slowly changing dimensions.

I heard many saying about HBase, which can do updates in Hadoop. But my question is, Is HBase suitable for DW related updates? , Is it suitable for Dimensional modeling?, How about maintainance since DW systems constantly undergo change..

So got an oppurtunity to attend HBase conference to get my answer.. and certainly its a no.. There are some use cases where HBase is used to serve reports or an online reporting portal but certainly unsuitable for a Bus architecture based Dimensional models.

Anyway HBase seems to be serving magnifiently for online applications and nice to see some case studies which we use everyday like Groupon, jws player.

Some notes from the sessions Im able to attend..

apache hive and Hbase

Seems like data-retrieval and updates are very programatic and hard in HBase
Since Hive is becoming very popular because of SQL nature, this presentation talks about how to use Hive above HBase
Hive over Hbase - online,unstructured and used by programmers
Hive over HDFS - offline, structured and used by analyst
use case 1 -- keep dimensions in Hbase .. fact in Hive and query can derive data from both
use case 2 -- continuous updates to Hbase and periodic dump to HDFS
join the above with a single Hive query
Actual presentation link
http://www.slideshare.net/hortonworks/integration-of-hive-and-hbase

Phoenix Project

An SQL skin for HBase
embedded JDBC driver to run HBase

presentation link
http://www.slideshare.net/dmitrymakarchuk/phoenix-h-basemeetup

Impala and Hbase

Cloudera trying to help the analyst community by returning query data faster
problem could be on efficiency
it may not used for a batch process like ETL - as there is a possibility of failiure
can be used for analyst queries
not the best yet -- in other words not production ready yet
Im skeptical about the optimizer these tools use

Couldnt find the actual presentation, but this PPT has most of the info
http://www.slideshare.net/cloudera/cloudera-impala-a-modern-sql-engine-for-hadoop

Apache Drill

Another SQL for HBase, I liked this talk since the presenter from MapR accepted that this product is not yet production ready.
The design has a Distributed cache in each data-node, which means more data-transfer to build this cache.
Maybe suitable for small, quick analytical queries. Have to wait and see for production use cases.

http://www.slideshare.net/ApacheDrill/apache-drill-technical-overview

HBase in Pinterest - Use case

Interesting talk about pinning, unpinning, follow etc, Couldnt find the actual presentation

And there is WiBiData's Kiji ..maybe this link will give some idea
http://gigaom.com/2012/11/14/wibidata-open-sources-kiji-to-make-hbase-more-useful/

Cloudera Forum Live

2013-06-04T10:32:00.002-07:00

Im watching on live streaming http://cloudera.com/content/cloudera/en/campaign/unaccept-the-status-quo-live-feed.html

"Center of gravity is shifting" -- the message is that the center of gravity of a Data warehouse is moving to Hadoop from Relational DB.

Hadoop distribution is only the beginning, we need other services like meta-data management, High-level querying language, faster response.

Moving Hadoop from a batch processing mode to real-time querying.

Cloudera Search -- latest addition from Cloudera to Hadoop ecosystem
intreractive speed on big data scale
no demo yet!

This classification looks good
Enterprise users - Search
Developer - Mapreduce / PIG / HIVE
Analyst - Impala

Analyst from Dell puts it right -- " Its not about having Hadoop in our environment and having data in it.. its all about how use Hadoop for our advantage and get real value out of it"

Oracle is popping up in many of Cloudera's events, anything cooking up!!

Dimensional modeling using Hive ( is it effective? )

2013-04-22T15:13:00.002-07:00

I strongly believe that dimensional model cannot/should not be build (effectively) in Hive. First of all Hive is not for slicing and dicing ( as expected in a DataMart), Hive is a SQL wrapper which makes executing complex MR jobs very very easier.

Still there are many Projects ( small & large ) which are trying to accomplish Dimensional modeling in Hive.
With an open framework like Hive, its certainly not impossible. But the question is how effective it is?

Effective in the sense, How scalable? How flexible? and How easy it is for an BI team to implement this?
Mostly answers to all the above are negative. My belief is that a BI team should spend most of its time in solving business problems not technical problems. If we implement dimensional model in Hive we will end-up writing code for all Hive's not-haves w.r.t any Database. And that a big overhead especially on smaller BI teams with high expectations.

This is a very good paper on how to do implement http://dbtr.cs.aau.dk/DBPublications/DBTR-31.pdf

So what can be Hive's in a datawarehouse reporting and ETL? Hive can play two roles.
1. It can act as a staging which actually resembles the source, with incrementally captured partitions. Later data from this layer can be ETLed to a Datamart in Datawarehouse.
2. A flattened table which merges all the information into a single flattened table.
3. All reporting DB's will serve only for certain History data, The historical dimensional model can be flattened and imported into Hive partitions. This gives greater scalability of Archiving. You can actually query and report out of this archive. (certainly not in real-time !!)

All these solutions are because of Hive's biggest advantage of scalability and performance. And also taking into consideration that Hive is not suitable for Updates and historical revisits.

Black friday 2012 deals - google spreadsheet excel

2012-11-17T11:23:00.002-08:00

Excel is a fantastic tool to search and maintain a list.. Instead of scanning through multiple print adds, a single spreadsheet can be a nice tool to compare and to find actual deals

I will keep this file updated until Blackfriday with latest deals

Created a spreadsheet of all popular mart blackfriday deals..

Only the popular deals ( about 500 items )

https://docs.google.com/spreadsheet/ccc?key=0AlP4buxzoaxsdExjMkM2WmJBcFZ5TlExTFMyWU1Oc1E

If you have certain product or store or category use this detailed list ( more than 5000 items )

complete list

https://docs.google.com/spreadsheet/ccc?key=0AlP4buxzoaxsdFBteHJwMjFlczVESUlTNXNNbEtuMXc

Google spreadsheet has a search filtering option for all the columns.. it is very easy to search and compare price of a particular item between different stores ..

Please download a local copy as XLS or pdf or any other format from "file > Download as"

using the menu bar..

top Travel movies -- My favorite genre

2012-11-02T13:42:00.003-07:00

One of my colleagues , he is actually from Mediterranean . but he usually talks in detail about India, like he studied the country. Later he revealed about the travel he made in India during his early 20s with his friends. They had minimal money and certainly not enough to travel in planes and stay at palaces. This makes their travel intriguing and interesting.

Me.. I always made planned travels, maybe I dont have the heart to face the uncertainty of unplanned travels.. this uncertainty and exploration makes a travel movie very interesting..

Motorcycle Diaries
this movie can easily takes the top spot of travel movies .. I like the south american scenic beauty and Che's friend who traveled with him. How about travelling a continent with a close friend on a bike ?

Zindagi na milega do bara (Hindi)
Although it has the Hindi masala factor still an enjoyable travel movie. Friends exploring themselves on a aimless travel to spain.

Kikujiro (Japanese)
What about travelling with a kid aimlessly . this is a funny and Im sure it touches everyone watches it, I think its because the boy's life resembles some of our childhood.

Le Grand Voyage (French)
How special it is to travel with a father on his pilgrimage. I know the idea is no fun when he is alive , but I didnt have the oppurtunity to spend this quality time with my father . Im longing for a travel which I can never start.

Up
Risking a travel at >60 for a petty promise he made to his wife as a kid.. Although this is animation this has lot of heart

Into the wild
slow movie , can be a time for self exploration ... although I hate the idea of running away from humanity

127 Hours
Good music and plot

Finding Nemo
A fathers search for his son with the help of weird friend.. a must watch I would say

Pentaho solution for Sqoop call with dynamic partitions

2012-11-01T14:48:00.001-07:00

Pentaho big data release doesnt have a step for SQOOP as of this writing..
Simple solution is to use a "Shell script" stage to call sqoop..

But our requirement has a twist .. and most of sqoop users might have as well..
- capture incremental data for certain tables and keep it in a new hive partition
- Run the sqoop extract for a window of dates where each day's data goes to a partition (DAILY , HOURLY, MONTHLY etc)

Design

The solution is to use a DB for configuration and pentaho to frame sqoop calls

JOB

We are calling the pensqoop transformation to frame the list of sqoop calls to run

shell stage actually runs the sqoop script.. here check the "execute for each input row " so that this script will be called for each sqoop call framed in previous step.

TRANSFORMATION

1. get table configurations (like db connection, incremental column to use for incremental data capture etc)
2. switch the flow for DAILY partitions or FULL refresh
3. frame sqoop call using string manipulation at javascript stage
4. "copy rows to result" to send the output to calling job

Hive Maintenance

2012-06-20T14:18:00.003-07:00

Hive log filling up tmp space

/tmp is usually the neglected folder in any unix environment, but there is where Hive is going to place all its log files. If you didnt have enough space allocated for this then your scheduled sqoop or hive queries are going to fail because of silly reason - no temp space. Not worth it..

1. Create a cron to remove the /tmp/<username>/*.txt files frequenty

2. Change the hive log directory to a different location which is monitored by ops ..

I I prefer the 2^nd since this change will not remove any files in the process.

t To make the configuration change got to /etc/hive/conf/hive-site.xml and add the following property

<name>hive.querylog.location</name>

<description>Directory where structured hive query logs are created</description>

</property>

Freeing up unused HDFS memory

Two directories we can concentrate to free-up a lot of HDFS memory

1. /tmp/hive-<username>/
2. /local/hadoop/mapred/staging/user/.staging/job*

If you have Hue you can cleanup some of long unused saved reports
1. /tmp/hive-beeswax-<username>/

If you can limit the files by date less than the current month, you are safe..(since this is delete operation)
Hadoop, Hive and Hue does have demon or code to clean-up these folders (Im not sure) but
Usually all these files are there because of orphaned mapreduce jobs. Like when you CTRL+C instead of formally "hadoop -job kill <job_id>"

And ofcourse scanning the hdfs directory and hive directory for junk files will also help..

the following are default configuration directories

for beeswax cleanup -- hadoop fs -rmr /tmp/hive-beeswax-*/hive*

for hive tmp -- hadoop fs -rmr /tmp/hive-bhchandr/hive*
older job files -- hadoop fs -rmr /local/hadoop/mapred/staging/<application_user>/.staging/job_2012*

Free Sqoop temp space

remove the compile folders for sqoop job
/tmp/sqoop-<username>/compile/

remove log files older than 1/2 day at jobtracker and data-nodes

/var/log/hadoop-*-*/userlogs/*

recover from safemode

Hadoop can go into safemode when the local directory mapped to HDFS is full.. this usually happens when your HDFS files usedup all the space.. but the trick is you cannot remove files unless you recover from safemode.

So first remove some local files from
/opt/local/hadoop/mapred/local/taskTracker
/opt/local/hadoop/mapred/local/taskTracker/distcache
/opt/local/hadoop/mapred/local/taskTracker/<username>

then
hadoop dfsadmin -safemode leave

since Hadoop is out of safemode.. you can cleanup some HDFS using the steps in the first sections

/var/log/hadoop-0.20/history/done

Hive - digging deeper into metastore

2012-05-24T13:25:00.002-07:00

We usually use information_schema or database metadata tables to query the tables, columns, indexes ..in the case of traditional databases. How about the same in Hive?

In the case of Hive there is a metastore which acts as a metadata for the databases, Hive uses this database to store the tables, partitions, databases, serde in this database. Say you want to know the tables in a database or physical(HDFS) location of the tables or similar column names across the tables..

I have no idea about the default derby DB.. so letme talk about metastore in a traditional database like Mysql..( If you dont have your metastore in an external DB you can follow this link to do so.. https://ccp.cloudera.com/display/CDHDOC/Hive+Installation#HiveInstallation-ConfiguringtheHiveMetastore)

Hive metastore has minimal tables when compared to the metadata layer of a traditional database but Im sure the metadata schema will get bigger and complex in future.

Lets go through the most desirable tables ...

COLUMNS
DBS -- the list of schemas
PARTITIONS
TBLS - tables

Querying these tables will give a basic idea and the datamodel of Hive metastore ( I couldnt find any over the internet), understanding of these tables are essential if you are seriously into Hive and have some production data.

for eg.. In a weird case we found some partitions of one database is being loaded into HDS location of another database (may be a code issue ) but using the following query Im able to narrow down to the affected tables..

select distinct a.tbl_id,a.tbl_name
from TBLS a
join PARTITIONS c on (a.TBL_ID=c.TBL_ID)
join SDS b on (c.SD_ID=b.SD_ID)
where b.location like '%sbx.db%' and a.DB_ID=1;

Secondary Sort using Python and MRJob

2012-04-25T18:14:00.000-07:00

MRjob is an excellent module developed as open source project by Yelp.. I chosed mrjob because of the following features

It provides a seamless JSON reader and writer (i.e the mapper can read json lines and convert them into lists)
We can test hadoop job locally (in windows or unix) on a small dataset without actually using huge hdfs files (quick !!)
Can orchestrate many mappers and reducers in the same code

My task is to parse json formatted web log files and parse them, say the columns are sessionid,stepno and data. so the psuedo-code

Read the json files using mrjob protocol

DEFAULT_INPUT_PROTOCOL = 'json_value'

DEFAULT_OUTPUT_PROTOCOL = 'repr_value' # output is delimited

2. yield sessionid, (sessionid,stepno,data) from mapper

the Mapreduce will make sure that all sessionids(key) goes to same mapper.. with the remaining values sent as a dictionary (value) to make it easier for us to srt in reducer

3. Use sorted from itertools of python module to sort by stepnumber in reducer

def reducer(self, sessionId, details):

sdetail = sorted(details, key=lambda x: x[1]) # sorting by stepno for each session

for d in sdetail:

line_data='\t'.join(str(n) for n in d)

We are doing the secondary sort to scan through each events as the sequence is very important to do the funnel analysis of logs..

Complete code :

import sys,time

#sys.path.append('/usr/lib/python2.4/site-packages/')

from mrjob.job import MRJob

from mrjob.protocol import JSONValueProtocol

from itertools import groupby

from operator import itemgetter, attrgetter

class uet(MRJob):

DEFAULT_INPUT_PROTOCOL = 'json_value'

DEFAULT_OUTPUT_PROTOCOL = 'repr_value'

def mapper(self, _, line):

sessionId = line['sessionId']

data = line['data']

if len(sessionId) < 13:

for i in range(len(data)):

no = data[i]['no']

yield sessionId,(sessionId, no,data)

def reducer(self, sessionId, details):

sdetail = sorted(details, key=lambda x: x[1]) # sorting by stepno for each session

for d in sdetail:

line_data='\t'.join(str(n) for n in d)

print str(line_data)

if __name__ == '__main__':

uet.run()

Top NBA Players - by twitter followers

2012-04-17T17:56:00.008-07:00

I really dont have any idea of how advertising companies decide on the price for certain celebrities. Because its very hard to measure the direct relationship to the sales and to derive an ROI. Also Im not sure if any celebrity with enormous following can make a big impact. I did data collection for fun to see who is actually more popular in Twitter and have more following. I used infochimps rest api calls to get the aggregated information and formatted files to make it readable by Tableau. (used Python for ETL).. seems like SHAQ even after out of the league has a greater fan following than active player.. I havent included Kobe and Rose.. and I tried my best to get the official ids of each player..

DW in Hive - handling big dimensions

2012-04-10T15:19:00.004-07:00

This is a an issue bothering our reporting data quality. How to handle big dimension tables in Hive data warehouse. How to balance performance and data-quality..

Problem statement:

Currently dimension hive table (dim_customer) is partitioned by date. The daily incremental creates the new partition so that we can improve the performance at the reporting side. This poses 2 critical issues

1. slowly changing dimension is lost

2. Compromise in data-quality

3. Have to filter by the dimension table in the reporting

Solution:

The only way to solve this problem is to have the dimension table as one big Hive table instead of partitions. But this creates issues with the refresh strategy and overhead on reporting Hive query..

The following is a recipe to solve this block.. step.1 is certainly the priority

1. Increase processing power

Hadoop is not only about mega storage it is also about mega processing ..so if we process big files then we got to have good number of nodes. say we have 30 nodes to process partitioned dimension table..we have to move to 120 nodes for single dimension file strategy. Processing power is tripled - it is directly proportional !.

2. Use SQOOP merge

We cannot extract the whole table from transactional system every time..source transactional systems might not allow.. we can only capture the change data. SQOOP merge comes handy for this purpose. We can overwrite only the incremental records in the hive table (type 1 SCD). Again this is a Map reduce program.. we need processing power..

http://archive.cloudera.com/cdh/3/sqoop/SqoopUserGuide.html#_literal_sqoop_merge_literal

3. Use Bucketed Hive tables

Hive performance block comes while joining. We can create a table with bucketing.. like hashing index on the customer_id.

http://hive.apache.org/docs/r0.8.1/language_manual/working_with_bucketed_tables.html

If the tables being joined are bucketized, and the buckets are a multiple of each other, the buckets can be joined with each other. If table A has 8 buckets are table B has 4 buckets, the following join

SELECT /*+ MAPJOIN(b) */ a.KEY, a.value

FROM a JOIN b ON a.KEY = b.KEY

can be done on the mapper only. Instead of fetching B completely for each mapper of A, only the required buckets are fetched. For the query above, the mapper processing bucket 1 for A will only fetch bucket 1 of B. It is not the default behavior, and is governed by the following parameter

Bucketing and Sqoop merge requires good planning and metadata management..

Managing Hadoop from the scratch is challenging as we bump into the limits sooner, we need to adapt quickly else data might grow beyond limits. One advantage( and complexity) is that the internal processing (mapreduce) is open and its upto the developer to improve. And the biggest advantage of all is scaling out.. you can add nodes easily to really make a difference..

Data science -- the cool scientist without white gowns

2012-03-29T15:47:00.006-07:00

"Scientist" is a cool word during my school days, wanna become one but donno on what. All I see as scientists wore white gown with colorful liquids around. But later during college and working days scientists seemed to be boring people with no personal life, accumulated in educational institutes with college kids helping around.

Recently there is a profile called "Data scientist" all over the BI market and started appearing in every article where Hadoop / Big data is there. It must be some part what a BI/DW person is doing with some specialization. Yes it is the formula,as for me..

BI + big-data + statistics + scripting + visualization = Data scientist

Ok can be scientist and work for a corporate or invent something new for you name? possibly...

But seems like a lot to cover , learn and experience at work. Maybe not really if we are in right job. Im just listing the very higher level outline..(and it is not limited to..) and my intention is not to oversimplify, but certainly to simplify the puzzle..

- DW work like ETL , databases, SQL with exposure to enterprise setup. Collecting data from heterogenous data sources. Log analysis. Dimensional modelling, DW architecture. DB performance.

Big-data

Hadoop is the first thing comes to mind for Big-data.. but good to know about noSQL dbs.

Mapreduce - shared nothing architecture - need for MR - use cases - tools available - pros and cons

Statistics

Basics - application of statistics in real-world - R programming

Scripting

Perl, Python, Java

Visualization

Reporting (I like Tableau), Complex SQLs , Ability to tell a story with data - by whatever way you effectively deliver..

I would like to list some of the coolest learning materials available for above topics..

I believe the thirst for discovery, admiring the hidden secret in the boring pile of data would make a Data Scientist..

Dual table in Hive

2012-02-21T13:20:00.000-08:00

Since there is no Dual table in Hive..

I have created a dummy dual with dummy value ‘X’.

hive> CREATE TABLE dual (dummy STRING);

hive> load data local inpath '/local/user/dw/hive/dual.txt' overwrite into table dual;

Now we can use this table to select string values. Like

hive> select 'name','place','age' from dual;

to get current timestamp

hive> select unix_timestamp() from dual;

Talent is overrated - Geoff Colvin

2012-02-17T13:19:00.000-08:00

Simply to say this book is good and can be life changing for you or may be to your kid.

I will get some new idea everyday, while taking bath or watching some movie or during half-sleep @ office or even at workout. Some of them are great, turned out to be great things which are implemented by someone, some are already existing and I'm ignorant of it. Many self analyzing thought made what I'm now. But the point is I'm not great, maybe by the scale of this society. Then how to define greatness - a person achieved enviable status in a field. The word field is the key here.

Being a Software Engineer I cannot become maddeningly wealthy like Buffet just by reading 'the intelligent investor'. Im already a hard-wired employee in a field who's time is controlled by someone else. And if you are married with kids personal time is a joke. There are success stories everywhere in Tennis, basketball, investing and especially entrepreneurship. Just by looking at it or researching will not make us the same. You might have known this but yet human greed takeover sometimes and we will start day-dreaming. Mostly the end result is disappointment.

If I have to become a cricketer I should have that thought by at least age 10 and played relentlessly until my skin is totally tanned. At this point this book makes a clear the 10000 hours point which Malcolm Gladwell's 'Outliers' is also talking about. Reading "Talent is overrated" book I can recall Arnold Schwarzenegger's quote ..

"Number one, come to America. Number two, work your butt off. And number three, marry a Kennedy."

The book says these very clearly

1. Choose your field, if you are already in a field and earning from it, its hard to leave and try new. But children has the advantage to excel enormously.

2. Deliberate practice .. it must be conscious, measurable and improving

3. Dont stick in the OK plateau if you want to become world-class

4. Never look around to excel in other area which you cannot afford to spend time on.

5. Get family support for what you are doing

Its a revealing read and can be read again if you fail to succeed in some area. The world is so competitive that only the extraordinary can win. (even in a job interview ) Can be a great book if you are a parent which can change your kids life all together.

Shell script - snippets

2012-02-10T11:12:00.001-08:00

Loop through dates

startdate=`/bin/date --date="2007-07-01" +%Y-%m-%d`

enddate=`/bin/date --date="2011-07-01" +%Y-%m-%d`

foldate="$startdate"

until [ "$foldate" == "$enddate" ]

echo $foldate

foldate=`/bin/date --date="$foldate 1 month" +%Y-%m-%d`

done

Slowly changing dimensions in Hive

2012-01-24T16:52:00.000-08:00

If you keep you Datawarehouse in Hive,How to implement slowly changing dimensions?

http://en.wikipedia.org/wiki/Slowly_changing_dimension

This is not a solution blog, actually Im looking for an effective solution to implement SCD in Hive.

Since all the types uses Update one way or another. for eg Type 2 can be a good candidate but it requires to update the enddate of history record. The only option I can think off is to insert the rows into the dimension and query on the maxid or maxdate to get the latest dimensions on the analytics layer.. (seems like an overhead on mapreduce)

I posted this question to Cloudera professionals group at LinkedIn. And Jasper recommended an idea which is not using Hive queries, moreover he prefers mapreduce scripts to handle SCD situations. I modified the flow to suit Hive tables.

Idea is this

1. Open the underlying HDFS file or select all rows using Hive-ql

2. Pass the data through a mapper as key,value pair

3. End-date the old record and leave the new record with NULL end_date (for type2) through script

4. Overwrite the Hive table

But the process could be tricky if the data is present in different partitions.

tipping point - Book journal

2012-01-24T15:47:00.000-08:00

Malcolm Gladwell is an expert in writing book about the things we know already (very vaguly) and make an compelling book with storytelling and statistics.. he must be the perfect author for the current information overloaded culture.

Tipping point said to be his best, but for me listening in audio format felt 'outliers' was interesting and stories remained in memory for relatively more time.

the law of few - it is true that few people make a big difference and also some critical piece of work make a big difference in a big project. Here the point is not about innovation but marketing. I feel we play all these roles sometimes in different context. Gladwell categorizes these people, so it makes it easier to find who is who is future...

Stickiness factor - we are living in a era of short memory loss.. stickiness could be a day to a week. In the case of viral videos it could be an hour of fame. There is a war out there in advertising, web and entertainment to make a thing sticky. the point to make your stuff sticky specifically to your target audience. Sometimes the stickiness can come from a funny context or intelligence or experience.

new york crime example - this is a fascinating story of how new york subway handled crime by simply clearing the cars from graffiti everyday. You can make a big difference by making small spirited changes. Like if you allow small mistakes in a project to go by and didnt appreciate the people taking care of those stuff..then the manager is setting a wrong example. Its all about defined values. And always values are simple and to the core.

Connectivity - You are connected to anyone in this world in a factor of six. this may be reduced after facebook and linkedin. And 150 is the number where a group or organization can work effectively. I see the reason why my company split organizations without any reason and give meaningless names to those groups.

Teenage smoking issue - simple in one line "experimenting may not become a habit". We can protect our kids only to a certain extent from smoking. But anyway they are going to try at least once, but that doesn't means that they are hooked forever. More than 80% of kids who tried smoking quit is along the way. Chippers is the name for occasional and non-addictive smokers.

Prelude - Identifying mavens.. identifying the right context.. identifying minor signs before they tip..

How small things can bring big impact.. small things mostly are in our control..

Hive outer join issue

2012-01-23T14:38:00.000-08:00

Recently we faced a memory issue (java heap space) when joining two partitioned tables.

Both the tables are huge in millions of rows and each partitioned on hourly basis.

select a.*,b.*

from a left outer join b

on a.id = b.id

where

a.part > '2012-01-01'

and b.part > '2012-01-01';

this fails during peak times.

issue:

when we checked the logs we found that the mapper is trying to access all the partitions of table b. Since the system is scarce in resources the job failed due to heap space issue.

resolution:

Change the right table to a inline..

select a.*,b.*

from a left outer join (select * from b where b.part > '2012-01-01') c

on a.id = c.id

where

a.part > '2012-01-01';

the number of mappers reduced dramatically and execution is scanning only the required partitions from table b.

Learning:

Always check the number of mappers while running. If the numbers are not realistic take a peak.

Divide and conquer approach when writing complex hive queries.

Since Hive is not yet a completely matured SQL, we need to put extra effort to examine the execution plan.

Moonwalking with Einstein - Book Journal

2012-01-10T11:20:00.001-08:00

The book cover itself says its not another "mind power" book.. with boring technical and mostly impractical and sometimes with petty techniques which I already posses..

Author Foer takes us a journey which he takes to win the American memory championship by sprinkling some of the techniques he used to remember different stuff. Certainly the book is not for learning new memory techniques as the author just gives a glimpse and feel of those techniques, its upto the reader to dig-deeper those sprinkled techniques. Rather the book is successful in being interesting, funny and informative. Since Im reading the Malcolm Gladwell series, MWE narration comes closer to Outliers. And some of the information is there in both the books, which makes me think both Foer or Gladwell are originator of the original idea (like the 10,000 hrs deliberate work) they are just aggregators and presenters of existing facts..

Memory palace - eventhough this is a technique we all use to keep things in memory, we can deliberately build memory palaces (with the places we know well) for certain group of things. This is helpful to me in preserving things to do, passwords and some numbers. the trick is to assign weird images/persons or persons doing weird things.

remembering cards - the POA method seems to be very effective (person Object Activity). For each card in the deck assign a personality doing some action on an object

A-spade - Rajnikanth wearing a coolers
2-spade - Kamal kissing a girl
3-spade - vijay dancing on a table
4-spade - Ajith raising his red-shirts collar

And when you get a 3 card combination we can create a mental image of POA
Ajith dancing with a girl represents 4-spade,3-spade,2-spade..
Its hard to remember the image for each card, but once it is assigned with practice its possible to remember atleast 10 cards I think !!

remembering numbers - this can be done with the same technique above by assigning a image to each 2 digit combination (this is hard I think but possible with deliberate practise)

extreme peoples -
Other interesting part is about a man who forgets the breakfast he had 5 mins back and some info on the famous Rain man. The interesting information is our time will seems longer if you have more memories about the time-period. Like the vacations, the job changes, important events and successful events

Role of memory in education -
I feel education needs some memory to analyze the data.. the future is of-course how effectively we analyze the data, but memory also plays a part it seems..

Overall interesting read other than boring self-improvement books which always portraits an ideal personality (which in reality cant exists)