Avro can append in HDFS after all

As an update to the previous post, it turns out Avro can append to an HDFS file after all. The capability is so obscure that even the experts on the Apache Avro mailing list weren't sure of the succinct way of accomplishing it. Sample code from GitHub.

But the demand for this capability will only grow, now that Hive supports Avro directly as of the Hive 0.10 release in January.

Avro Tips

Apache Avro is a binary compressed fixed-schema file format meant for Hadoop. As of Hive 0.9.1 and 0.10, Hive can read Avro files directly. Cloudera also back-ported Avro compatibility for its Cloudera Hive 0.9.0.

Avro is highly structured, supporting nested records and arrays, and that makes it a good match for Hive, whose HiveQL syntax adopted SQL1999-style records/structures and arrays.

Avro is a great space-saving alternative to JSON, especially since it's not possible for Apache Pig to read gz-compressed JSON.

This is a collection of tips on using Avro -- most of them can be found here and there on the web, but here they all are in one place.

Schema tips

Below is an example schema that shows nested structures, nullable fields, optional sub-structures, arrays of structures, and arrays of strings.

{"name": "event", "type": "record", "fields": [
  {"name": "logLevel", "type": ["null", "int"], "default": null},
  {"name": "details", "type": 
    {"type": "record", "name": "detailstype", "fields": [
      {"name": "eventname", "type": ["null", "string"], "default": null},
    ]}
  },
  {"name": "metadata", "type": ["null",
    {"type": "record", "name": "metadatatype", "fields": [
      {"name": "referrer", "type": "string"},
    ]}]
  },
  {"name": "messages",   "type": ["null",
    {"type": "array", "name": "messagestype", "items": 
      {"name": "message", "type": "string"}
    }],
    "default": null
  }
]}

• Declaring a 1-to-1 sub-record requires the verbose style above, where the "type" is declared to again to be a "type", which is of "record".
• In order for a field to be "nullable", it must be declared as a UNION with type "null" as shown above for "type" using the JSON array syntax.
• In order to avoid having to explicitly set a field when writing out an Avro record, it must be declared as "default": null
• Combining the above two points, in order to declare a field nullable and default: null, the "null" in the UNION must appear first because it is the first type in a UNION list that determines the data type for the default, and "null" is an invalid default for all data types (e.g. "string", "int", etc.) with the exception of the "null" data type.
• The above applies to sub-records as well. In the example above, "details" is a required sub-record (with an optional field "eventname"), whereas "metadata" is an optional sub-record (with, if the sub-record is present, a required field "referrer").
• When writing out sub-records using the "generic" schema rather than the "specific" schema, the Java code to instantiate an empty sub-record is

  GenericData.Record childdatum =
  new GenericData.Record(schema.getField(fieldname).schema());

  However, if the sub-record is optional (nullable), then to dig past the UNION, the Java code becomes

  GenericData.Record childdatum =
  new GenericData.Record(schema.getField(fieldname).schema().getTypes().get(1));

  In both cases, the childdatum is populated and added to the parentdatum via

  childdatum.put("aChildStringField", "A string value");
  parentdatum.put(fieldname, childdatum);

• Appending to existing Avro files in HDFS is not supported, even though appending to existing Avro files on the local filesystem and appending to existing non-Avro HDFS files are both supported. The open one-year-old unassigned JIRA ticket for such a feature is AVRO-1035.
• To view a .avro file:
  

  java -jar avro-tools-1.7.3.jar tojson mydata.avro

Hive using Avro

Example schema:

CREATE EXTERNAL TABLE mytable
  ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
  STORED as INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
  OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
  TBLPROPERTIES (
    'avro.schema.url'='hdfs:///user/cloudera/mytable.avsc');

[UPDATE 2013-07-18: The above Hive table creation TBLPROPERTIES no longer works with Hive 0.11. Instead one must use SERDEPROPERTIES.]

• Although Apache documentation gives examples of 'avro.schema.url'='file:///home/...' that actually doesn't work for any non-trivial Hive query because map/reduce doesn't have access to the local filesystem.
• It's not required to specify LOCATION for where the external Hive data will be stored. If it is not specified, then Hive creates the directory in HDFS of /user/hive/warehouse/<tablename>
• It is possible to arbitrarily dump a bunch of Avro files into a Hive table directory, and Hive will automatically read them all when executing a query.
• Not specific to Avro, but it is possible to specify one or more levels of PARTITION for a Hive table. Each level of PARTITION corresponds to another level of directory underneath /user/hive/warehouse/ (e.g. for a two-level PARTITION one might have a directory /user/hive/warehouse/mytable/datestring=2013-02-01/useridfirstchar=2). Using the point above, one can dump multiple Avro files into one such leaf directory of a PARTITIONed table.
  However, one cannot just merely create partition directories in /user/hive/warehouse/<tablename> because the partition names also need to be added to the Hive metastore, which can be done with HiveQL via either the Hive CLI or JDBC:

  ALTER TABLE mytable ADD IF NOT EXISTS PARTITION (ds='2013-02-01')

  Also, note that contrary to examples in Apache documentation, "date" is not a valid partition name as it is a reserved word. "ds" (for "date string") is a popular alternative.
• Hive supports SQL1999-style nested structures and nested arrays out of the box, and so is a good match for highly-structured Avro schemas. E.g., below is a valid Hive query against the example schema and CREATE TABLE from above:

  SELECT messages[0].functionname FROM event

  Hive also has functions for array handling such as "explode", which returns array elements from a single column as multiple result set rows. https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-explode
• Not specific to Avro, but SHOW CREATE TABLE is not supported in Hive until 0.10. To do so in Hive 0.9, download HiveShowCreateTable.jar from https://issues.apache.org/jira/browse/HIVE-967 and then

  hive --service jar HiveShowCreateTable.jar com.media6.hive2rdbms.job.ShowCreateTable -D db.name=default -D table.name=mytable 2>/dev/null

ElephantBird now enables Hello World from Pig

In learning Apache Pig, I was surprised at how difficult it is to write "Hello World." From http://pig.apache.org/docs/r0.7.0/piglatin_ref2.html#Constants I would have thought the code below would have been legal:

-- Illegal Pig syntax
A = {('Hello'),('World')};
DUMP A;

However, it produces syntax errors.  The only way to create a relation -- the basic "variable" in Pig -- is through LOADing a file. As a "language lawyer" within whatever team I was in dating back to the K&R era, it bothers me that a "Hello World" program is impossible to write within a single code file. It makes it seem like Pig Latin is an incomplete language.

The people at Twitter's ElephantBird project have come up with a custom solution in response to my request on the Pig User mailing list.
http://mail-archives.apache.org/mod_mbox/pig-user/201301.mbox/%3CCAE7pYjZtwuxYZs6Ov54P-6SFRCkKPuL9Jwac9i-Rr%2BYsdhasNw%40mail.gmail.com%3E

This ElephantBird Java class allows converting what normally would be the filename specified with the LOAD command into a tuple. A hack. That works. But not without invoking code not distributed with Pig and not without ugliness.

languages = load 'en,fr,jp' using LocationAsTuple(',');

SQL HAVING in R

Coming from a SQL background, I'm learning R, and wanted to do the equivalent of GROUP BY HAVING (well, really embedded in a subquery in order to subset the data), but the most obvious Google searches turned up nothing.  The answer is probably a no-brainer for R experts, but here it is in case future R-novices-SQL-experts Google for it.

Taking the example data set chickwts,

> data(chickwts)
> summary(chickwts)    
    weight          feed
Min.   :108.0   casein   :12
1st Qu.:204.5   horsebean:10
Median :258.0   linseed  :12
Mean   :261.3   meatmeal :11
3rd Qu.:323.5   soybean  :14
Max.   :423.0   sunflower:12

and supposing we want to exclude "low" popularity feeds that occur in the data set fewer than 12 times (yes, this is a contrived example), the below will discard those low-popularity feeds, leaving only the high-popularity feeds.

x <- sapply(split(chickwts,chickwts$feed), nrow)
chickwts <- chickwts[chickwts$feed %in% names(x[x>=12]),]

Laravel PHP/MySQL/CentOS garbled strings

A quick but obscure tidbit for something that consumed my day today (reported here only because it wasn't reported anywhere else on the web):

If you're using the Laravel PHP framework with MySQL running on CentOS, you probably need to change the charset in application/config/database.php to be "latin1" instead of Laravel's example of "utf8".  Otherwise, you'll get garbled strings.

CentOS (even the current 6.3 distribution) comes with MySQL 5.1, which is from before Oracle acquired it and modernized it (e.g. with things like foreign key constraints). Another modernization is that whereas MySQL 5.1 defaults to the latin1 character set, its successor MySQL 5.5 defaults to the utf8 character set. Laravel's example database.php connection specifies utf8, so unless you've manually upgraded CentOS to MySQL 5.5, you will need to change database.php to specify the latin1 character set.

Javascript Dagre & D3 to visualize Big Data dataflows

Big Data projects often involve a lot of stovepipe processing, and visualizing data flows is a powerful way to convey data provenance to the end user, and even allow control of the involved processes.

There are a number of tools available to visualize data flows, but most suffer from some limitation. Some allow labeling of only nodes, but not edges. Some do not have a provision for the node label to be inside of the node. Most use general-purpose graph layout algorithms such as "gravity", whereas dataflow diagrams have distinct start nodes and end nodes and are better represented mostly orthoganlly either top-down or left-right. (The well-known dataflow language G from LabVIEW is left-right, but top-down is better suited for web browsers.)  Graphviz can generate a nice top-down layout, but for the web can only at this time produce static images (albeit dynamically), with no mouseovers to facilitate drill-downs into processes or data stores.

Dagre, a Javascript library built on top of the D3.js visualization toolkit, is very well-suited to visualizing Big Data data flows. Below is an example (contrived, but illustrates the idea):

Dagre generated the above from the succinct input below:

digraph {
    A [label="Apache"];
    B [label="fetchlog.sh"]
    C [label="parseaccesslogs.pig"];
    D [label="parseerrorlogs.pig"];
    E [label="PageViewsPer503"];
    A -> B [label="access_log"];
    A -> B [label="error_log"];
    B -> C [label="/logs/access_log_$DATE.txt"]
    B -> D [label="/logs/error_log_$DATE.txt"]
    C -> E [label="MySQL:VISITORS"];
    D -> E [label="MySQL:ERRORS"];
}

You can paste it into Dagre's live demo yourself, and you can see that mouseover highlighting is possible, meaning it is possible to hook it to do drill-downs.

Apache Thrift in Windows

Apache Thrift, originally developed by Facebook, is an immensely useful general-purpose inter-process communication (IPC) code generation tool and library. Although it supports a variety of IPC mechanisms, sockets are its primary conduit, and as such, is naturally language agnostic and actually its tool can generate code for a dozen different languages.

I use it to provide PHP web interfaces to monitor and control C++ scientific/industrial semi-embedded systems (desktop PCs loaded with data acquisition and control hardware).

Sometimes, those PCs are running Windows. With the recent 0.90 release, Apache Thrift support for Windows is leaps and bounds beyond what it used to be, but it's still "only" 98%. Here are the missing steps:

1. First of all the good news: Use on Windows no longer requires Cygwin or MinGW, despite what the outdated documentation states.
2. You can download a pre-built Thrift compiler directly from apache.org
3. You will, however, still need to compile the Thrift libraries yourself, if you plan to use Thrift with a compiled language such as C++. Thankfully, the Thrift distribution comes with a Microsoft Visual C++ .sln solution file. The thing to know, however, is that it is a Visual C++ 2010 .sln file, and will not work work with Visual C++ 2008. You can use Visual Studio 2012, but recall Visual Studio 2012 does not work with XP, which I still use for development because of both data acquisition hardware drivers and some legacy software development tools (for some legacy codebases). Thankfully, you can use the freely available Visual C++ 2010 Express, which is still available for download even though Visual Studio 2012 has been released. To download an ISO (to preserve your ability to reinstall in the future) instead of a stub/Internet download, select the option for the "All-in-One ISO".
4. The \thrift-0.9.0\lib\cpp\thrift.sln contains two projects: libthrift and libthriftnb. The libthriftnb is for the non-blocking server, and if you want to use it from a server, you must link in both libthrift and libthriftnb, as well as utilize TNonblockingServer instead of TSimpleServer. Note that "non-blocking" means non-blocking from the client perspective. On the server side, the call server->serve() actually blocks. To make either TNonblockingServer or TSimpleServer non-blocking from the server code perspective, just wrap it inside a new boost::thread().
5. Compiling libthriftnb is trickier. First it requires libevent. To compile libevent, Start->All Programs->Microsoft Visual Studio 2010 Express->Visual Studio Command Prompt (2010), navigate to the libevent directory, and nmake -f Makefile.nmake. Second, libthriftnb pulls in Thrift library code that does #include <tr1/functional>, but since Visual C++ 2010 doesn't support TR1, you can just replace it with <boost/functional.hpp>.
6. To compile the libthrift project (and this applies to libthriftnb as well), from the Microsoft Visual C++ drop-down menu, Project->Properties and Configuration Properties->C++->General->Additional Include Directories: C:\Program Files\boost\boost_1_51 (of course download Boost first).
7. Then, to compile your Visual C++ server code that links to libthrift, from the Microsoft Visual C++ drop-down menu, Project->Properties:
   • Configuration Properties->C/C++->General->Additional Include Directories: C:\Program Files\boost\boost_1_41;C:\thrift-0.9.0\lib\cpp\src (for libthriftnb, also include C:\libevent-2.0.21-stable\include;C:\libevent-2.0.21-stable\WIN32-Code;C:\libevent-2.0.21-stable)
   • Configuration Properties->Linker->General->Additional Library Directories: C:\thrift-0.9.0\lib\cpp\Release;C:\Program Files\boost\boost_1_51\lib
   • Configuration Properties->Linker->Input->Additional Dependencies: libboost_thread-vc100-mt-1_51.lib;libboost_chrono-vc100-mt-1_51.lib;libthrift.lib. (For the Debug version, substitute mt-gd for mt.)
8. In your server code, include the following code prior to invocation of any of the Thrift code:

    WSADATA wsaData = {};
    WORD wVersionRequested = MAKEWORD(2, 2);
    int err = WSAStartup(wVersionRequested, &wsaData);