Cloudera Forum Live

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? )

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

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

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

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

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

<property>

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

  <value>/var/<username>/tmp</value>

  <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

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;