Tuesday, May 28, 2013

New in Hive 0.11: Windowing and analytics functions

It's not getting a lot of press, but Hive 0.11, released May 15, 2013, introduced a ton of new keywords. Hive 0.11 supports the SQL analytic functions similar to what popular RDBMS's introduced a decade ago with the SQL2003 standard, with the following new Hive keywords:

  • LEAD
  • LAG
  • FIRST_VALUE
  • LAST_VALUE
  • OVER
  • WINDOW
  • UNBOUNDED
  • PRECEDING
  • FOLLOWING
  • RANK
  • ROW_NUMBER
  • DENSE_RANK
  • CUME_DIST
  • PERCENT_RANK
  • NTILE

Sadly, the documentation is scanty, to say the least. For now, documentation and tutorials (e.g. sqlatoms.com) on equivalent keywords in Oracle and SQL Server, for example, will have to suffice.

Being able to window obviates some of the need for subqueries (specifically, pre-SQL2003 subqueries that just provided a limited GROUP BY in a separate SELECT column), which of course Hive does not support.

Monday, May 20, 2013

Arbitrary cell formatting in YUI3 DataTable

YUI is an underrated JavaScript framework, in my opinion. I suspect it isn't very popular because it had dependencies on Flash as late as 2011, and because there were major API changes between YUI2 and YUI3, resulting in a split community and a paucity of documentation and forum help for YUI3. But YUI3 is a rich and free collection of rich web front-end components.

An example of weak documentation and examples is custom formatting of DataTable cells. What was the "formatter" property in YUI2 has been split in YUI3 into "formatter" for changing the just cell text and "nodeFormatter" for changing (actually, supplying) the cell text and the cell property. Since when using a nodeFormatter, one must explicitly transfer the contents of the cell data to the cell display, I decided: why not just embed the formatting right into the cell data? Normally, it is expected that you would maintain a separate external array containing the formatting (e.g. an array of Booleans indicating which cells should be highlighted in red), but with the technique below, the formatting information is embedded right with the cell data in the form of a JavaScript/JSON object for each cell.


<!DOCTYPE html>
<head>
 <script src="yui/build/yui/yui-min.js"></script>
 <script>
  var fmt = function(o) {
      if (o.value) {
          if (o.value.value)
              o.cell.set('text', o.value.value);
          if (o.value.classname)
              o.td.setAttribute("class", o.value.classname);
      }
      return false;
  }

  var columns = [{"key":"Part #", "nodeFormatter":fmt},
                 {"key":"Part Name", "nodeFormatter":fmt}];
  var data = [{"Part #":{"value":1234},
               "Part Name":{"value":"Capacitor"}},
              {"Part #":{"value":5678},
               "Part Name":{"value":"Resistor", "classname":"redbackground"}}];

  YUI().use('datatable', function(Y) {
   new Y.DataTable({columns: columns, data: data, render: "#table"});
  });
 </script>
 <style>
  .redbackground { background: red; }
 </style>
</head>
<body>
 <div id="table" class="yui3-skin-sam"></div>
</body>
</html>

UPDATE 2013-05-23: Thanks to Luke Smith's post at the YUI forum, it turns out it is possible to use formatter, which is more performant than nodeFormatter. The trick, though, is using formatter requires a more specific CSS selector. Full code below.

<!DOCTYPE html>
<head>
 <script src="yui/build/yui/yui-min.js"></script>
 <script>
  var fmt = function(o) {
      if (o.value.classname)
          o.className = o.value.classname;
      return o.value.value;
  }

  var columns = [{"key":"Part #", "formatter":fmt},
                 {"key":"Part Name", "formatter":fmt}];
  var data = [{"Part #":{"value":1234},
               "Part Name":{"value":"Capacitor"}},
              {"Part #":{"value":5678},
               "Part Name":{"value":"Resistor", "classname":"redbackground"}}];

  YUI().use('datatable', function(Y) {
   new Y.DataTable({columns: columns, data: data, render: "#table"});
  });
 </script>
 <style>
  #table .redbackground { background: red; }
 </style>
</head>
<body>
 <div id="table" class="yui3-skin-sam"></div>
</body>
</html>

Monday, May 13, 2013

Escaping the HTML5 sandbox with Qt

HTML5 has a large number of powerful APIs that make it a candidate for cross-platform development of rich desktop-type apps. One last missing piece stands in the way: unfettered access to the local filesystem: local files, thumb drives, and network shares. Perhaps someday there will be a facility to "sign" HTML5 apps to allow them access to the local filesystem, but it is not the case today.

Qt is a stopgap measure to allow you to keep 98% of your app code in HTML5, where the other 2% is Qt C++ to access the local filesystem. This preserves cross-platform capacity and potential, including to the cloud. (In contrast, a conventional Qt app without HTML5 would not be able to be hosted on the cloud.)

Below are the slides from the presentation I gave on May 13, 2013 at the Denver HTML5 Meetup group. And below that is a YouTube with audio narration, recreating the presentation.