title: HBase schema/key for real-time analytics solution
body: <p>We are looking at using HBase for real-time analytics. </p>
  
  <p>Prior to HBase, we will be running a Hadoop Map Reduce job over our log files and aggregating the data, and storing the fine-grained aggregate results in HBase to enable real-time analytics and queries on the aggregated data. So the HBase tables will have pre-aggregated data (by date).</p>
  
  <p>My question is: how to best design the schema and primary key design for the HBase database to enable fast but flexible queries.</p>
  
  <p>For example, assume that we store the following lines in a database:</p>
  
  <pre><code>timestamp, client_ip, url, referrer, useragent
  </code></pre>
  
  <p>and say our map-reduce job produces three different output fields, each of which we want to store in a separate "table" (HBase column family):</p>
  
  <ul>
  <li>date, operating_system, browser </li>
  <li>date, url, referrer </li>
  <li>date, url, country</li>
  </ul>
  
  <p>(our map-reduce job obtains the operating_system, browser and country fields from the user agent and client_ip data.)</p>
  
  <p>My question is: how can we structure the HBase schema to allow fast, near-realtime and flexible lookups for any of these fields, or a combination? For instance, the user must be able to specify:</p>
  
  <ul>
  <li>operating_system by date ("How many iPad users in this date range?")</li>
  <li>url by country and date ("How many users to this url from this country for the last month?")</li>
  </ul>
  
  <p>and basically any other custom query?</p>
  
  <p>Should we use keys like this:</p>
  
  <ul>
  <li>date_os_browser</li>
  <li>date_url_referrer</li>
  <li>date_url_country</li>
  </ul>
  
  <p>and if so, can we fulfill the sort of queries specified above?</p>