Trino and Iceberg Reading/Watching Resources

 I'm currently digging into Trino with Iceberg instead of the Hive Metastore. These are some pages and videos that have been great.

The Trino Community Broadcast has some amazing episodes from Trino and Iceberg

14: Iceberg: March of the Trinos

15: Iceberg right ahead!

19: Data Ingestion to Iceberg and Trino

And the Trino Meetup recording at Apache Iceberg: A table format for data lakes with unforeseen use cases

And some good reading from Starburst (makers of a supported version of Trino, founded by the creators of Presto)

https://blog.starburst.io/trino-on-ice-i-a-gentle-introduction-to-iceberg

https://blog.starburst.io/trino-on-ice-ii-in-place-table-evolution-and-cloud-compatibility-with-iceberg

(see below for links to part 3 and 4)

And a great tutorial repo at https://github.com/bitsondatadev/trino-getting-started

The show notes for Trino Community Broadcast Ep 19 are great and have these awesome links:

• Iceberg at Adobe

• Trino on Ice I

• Trino on Ice II

• Trino on Ice III

• Trino on Ice IV

The Trino documentation for the Iceberg connector is at https://trino.io/docs/current/connector/iceberg.html

Trino and Zero-Length Parquet Files in HDFS Part 2

Continuing from Part 1.

The test application to write parquet files over the course of an hour as on github at https://github.com/awcoleman/example_trino_hdfs_zero_length

We can run the app in quick mode to populate some hive-style directories with data:

Create a table from those hive-style directories:

And tell the Hive Metastore about these partitions:

(Here we just tell hivemetastore to refresh all partitions, we could have just added single partitions)

And query data with Trino:

Now imagine if a legacy application opened a parquet file in an older partition directory and held that file open while waiting to see if there was any more incoming old data. We can use our test application to simulate that:

We can see the hdfs directory now has another file:

If we run the same query in Trino again, we get an error:

And the Trino server.log shows us the issue is in the footer:

Queries in other directories without open parquet files work fine:

Trino and Zero-Length Parquet Files in HDFS Part 1

 Trino (formerly Presto) is a great distributed query engine. It allows one to use SQL to query data in parquet files.

Parquet files have file metadata in a footer at the end of the file. The footer is written when the parquet file is closed.

I have a client with a legacy application that writes parquet files to HDFS or S3 in a hive partition structure. Parquet files written to S3 do not exist until they are closed, however those written to HDFS show as zero-length files until closed. This can be a problem for Trino since the parquet footer has not been written yet.

Trino can use Hive Metastore as the metastore to hold information linking tables to data files. Hive Metastore partitions operate at the directory level, not file level. This means that all the files listed in a directory are shown as part of the partition.

In later posts, we'll look at Apache Iceberg as an alternative to Hive Metastore to avoid this issue.

In the next posts, we set up a test environment to show this happening.

Apache Zeppelin Login Banner

Apache Zeppelin does not yet have an easy way to include a logon banner, login banner, warning banner, 'notice and consent banner', 'approved system use notification', security message, or whatever-you-want-to-call-it.

A client needed one so I put together these instructions for a workaround. I plan on submitting a proper patch to Zeppelin if I can find some spare time. Hopefully someone else will do it first :)

These instructions use Hortonworks HDP 2.6.4.0-91 for specific locations. If you are using something else, you just need to change the path of the war and html file.

Zeppelin serves the login popup from /usr/hdp/2.6.4.0-91/zeppelin/webapps/webapp/components/login/login.html
It is possible to just change that file, but every time the Zeppelin service is restarted, the Zeppelin war is unpacked and login.html is overwritten.

We will alter the login.html copy inside the war file. This will have to be redone every time Zeppelin  is upgraded (such as a new HDP release), but not during normal operations.

The result is a message that appears on the login popup.

Very quick Domain Controller Cert Auth for testing

I needed to test certain scenarios for a client against a Microsoft Active Directory Domain Controller and Intermediate Certificate Authority. The easiest way was to use Vagrant with the mwrock/Windows2012R2 box.

I wasn't able to automate the complete install, but did get it to a set of cut-and-paste lines.

Code is at [ https://github.com/awcoleman/vagrant_win_ad_dc_ca_test ]

Copy Vagrantfile into new directory
Follow directions in README.txt

The next iteration will probably use Ansible support for Windows (unfortunately there is no CA module)

Giraph Error: Could not find or load main class org.apache.giraph.yarn.GiraphApplicationMaster

Very old post from 2014 that got lost in my drafts. Posting so hopefully this helps out someone.

Often Google acts like magic for me: type in my error, and out pops the solution. Not so for a Giraph error I recently hit. Hopefully this post lets Google work like magic for someone else :)

After installing Giraph on a BigTop 0.7 VM, I was able to run the benchmark that takes no input or output but nothing more complicated.

This works:
hadoop jar /usr/share/doc/giraph-1.0.0.5/giraph-examples-1.0.0-for-hadoop-2.0.6-alpha-jar-with-dependencies.jar org.apache.giraph.benchmark.PageRankBenchmark -Dgiraph.zkList=127.0.0.1:2181 -libjars /usr/lib/giraph/giraph-1.0.0-for-hadoop-2.0.6-alpha-jar-with-dependencies.jar -e 1 -s 3 -v -V 50 -w 1

But this:
hadoop jar /usr/share/doc/giraph-1.0.0.5/giraph-examples-1.0.0-for-hadoop-2.0.6-alpha-jar-with-dependencies.jar org.apache.giraph.GiraphRunner -Dgiraph.zkList=127.0.0.1:2181 -libjars /usr/lib/giraph/giraph.jar org.apache.giraph.examples.SimpleShortestPathsVertex -vif org.apache.giraph.io.formats.JsonLongDoubleFloatDoubleVertexInputFormat -vip /user/acoleman/giraphtest/tiny_graph.txt -of org.apache.giraph.io.formats.IdWithValueTextOutputFormat -op /user/acoleman/giraphtest/shortestpathsC2 -ca SimpleShortestPathsVertex.source=2 -w 1

does not.

Looking at the latest container logs with:

cat $(ls -1rtd $(ls -1rtd /var/log/hadoop-yarn/containers/application_* | tail -1)/container_* | tail -1)/*

I find:

Error: Could not find or load main class org.apache.giraph.yarn.GiraphApplicationMaster

I beat my head against the wall trying to add to libjars, to -yj, copying jars into every directory i could find.

I stumbled across

http://mail-archives.apache.org/mod_mbox/giraph-user/201312.mbox/%3C198091226.KO6f1kuK42@chronos7%3E

which gives the answer. If https://issues.apache.org/jira/browse/GIRAPH-814 hasn't been applied, then mapreduce.application.classpath has to be hard set or Giraph simply won't work.

vi /etc/hadoop/conf.pseudo/mapred-site.xml

  <property>

    <name>mapreduce.application.classpath</name>

    <value>/usr/lib/hadoop-mapreduce/*,/usr/lib/hadoop-mapreduce/lib/*,/usr/lib/giraph/giraph-1.0.0-for-hadoop-2.0.6-alpha-jar-with-dependencies.jar

    </value>

  </property>

I did not need to restart yarn-resourcemanager or yarn-nodemanager for this to get picked up.

DropWizard and Hive (and/or Impala)

I have a small DropWizard/D3.js/jqGrid application to visualize the results of some analysis. I had been taking the results of the analysis from hdfs and shoveling it into mySQL (with sqoop) to examine samples. This is working well enough that I wanted to go straight to the source. With DropWizard this should be easy enough to wrap my data in a Hive external table and use the Hive JDBC driver instead of mySQL.

If you are already familiar with DropWizard and just need an example, examine the pom.xml and config-hive.yaml files in my example application on GitHub.

To pull in Hive JDBC and its dependencies, add to pom.xml:

       
  <dependency>
   <groupid>org.apache.hive</groupid>
   <artifactid>hive-jdbc</artifactid>
   <version>1.1.0</version>
   <exclusions>
    <exclusion>
     <groupid>org.slf4j</groupid>
     <artifactid>slf4j-log4j12</artifactid>
    </exclusion>
    <exclusion>
     <groupid>com.sun.jersey</groupid>
     <artifactid>*</artifactid>
    </exclusion>
   </exclusions>
  </dependency>
  <dependency>
   <groupid>org.apache.hadoop</groupid>
   <artifactid>hadoop-common</artifactid>
   <version>2.6.0</version>
   <exclusions>
    <exclusion>
     <groupid>org.slf4j</groupid>
     <artifactid>slf4j-log4j12</artifactid>
    </exclusion>
    <exclusion>
     <groupid>com.sun.jersey</groupid>
     <artifactid>*</artifactid>
    </exclusion>
   </exclusions>
  </dependency>

      
 

Pinebook!

Not Hadoop-related, but awesome all the same. A few months ago I stumbled on PINE64's website and saw the pinebook, a linux arm64 laptop. That and a PocketCHIP made a great late-birthday, early-father's-day set of presents.

Build and shipping takes a couple months, shipping was almost 1/3 of the laptop cost, and performance and keyboard quality is exactly what you would expect :) But it is still a fun bit of hardware.

If you decide to get one, make sure to add on a USB-to-H-barrel power cord (or make your own). The pinebook does come with a power supply, but no point in carting around yet another wall-wart when the pinebook happily charges off a phone charger.

Mine powered right up into Xenial. I'm normally RH-based since everywhere I've been employed in the last couple decades has been, so it's nice to jump back into Debian-based.

aarch64 wasn't in mainline rust, but was in nursery, so
curl -sSf https://raw.githubusercontent.com/rust-lang-nursery/rustup.rs/master/rustup-init.sh | bash

worked just fine and got me up and going with rust.

Update: HackADay has a great write-up. I didn't experience any of the screen issues they had since I have the 14", but the page has a great tear-down and overview of performance (which is not much :) )

Writing ORC files is easier than a few years ago

Several years ago I was asked to compare writing Parquet and ORCFile formats from standalone java (without using the Hadoop libraries). At the time ORC was not separated from Hive and it was much more involved than writing Parquet from java. It looks like that changed in 2015 but I only revisited the issue within the past few months.

To build ORC:
Download the current release (currently 1.3.2)
tar xzvf orc-1.3.2.tar.gz && cd ./orc-1.3.2/
cd ./java

mvn package

ls -la ./tools/target/orc-tools-1.3.2-uber.jar

A simple example of writing is:

And a simple example of reading is:

Github PrintGZHeader

I receive some data that is a gzip file made of concatenated smaller gzip files. The larger file is a valid gzip according to the rfc. Everything works fine unless I need to look at the original filename or mtime of the gz streams. So... I created a program to print out the original filename and mtime from the header information in all the gzip streams in a gz file.

https://github.com/awcoleman/PrintGZHeader

Compile: gcc -o printGZHeader printGZHeader.c -lz
And run: ./printGZHeader myGZfile.gz

My C is very rusty, I will happily accept any patches to clean up bad practices. My test files do not have header comments or extra fields, please send patches if you find that the code does not work appropriately on them (or send me a test file and I will try).

Hopefully this saves someone else a little bit of time.

Nostalgia in Clustering

The close of 2014 made me remember an old clustering project I did around 2004 on a shoestring budget. The project was correlating customers into families, with a sub-task of deduplicating customer records (from typos and other issues). The entire project team was… me.

I gathered up a server with a couple of old hard drives as a mySQL server and PXE boot server, and four other computers PXE-booting into linux with openMOSIX for clustering. I didn’t have budget for cases for the four slaves, so used old cookie sheets to mount them. I used wooden dowels to fix two cookie sheet nodes together so they could sit vertically.

My processing was done in perl. Once OpenMOSIX reported a slave was free, a perl process would spawn and grab a workload from mySQL. OpenMOSIX would migrate the process to the open slave.

Fortunately I was able to complete the project with only four slaves. I had figured out my power supplies could power two slaves. I was working on converting a couple of ATX power supply extension cables into a Y-splitter and only using one power supply per "cookie".

I found some pictures of the nodes from an old presentation:

Custom Writable

I have never tackled a custom Writable before. I am a huge Avro (http://avro.apache.org/) fan, so I usually try to get my data converted to avro early. A discussion got me interested in tackling it and I had my Bouncy Castle ASN.1 Hadoop example open, so I extended that to a basic custom Writable example.

This thread by Oded Rosen was invaluable:
http://mail-archives.apache.org/mod_mbox/hadoop-general/201005.mbox/%3CAANLkTinzP8-nnGg8Q5aaJ8gXCCg6Som7e8Xarc_2PGDD@mail.gmail.com%3E
(also at http://osdir.com/ml/general-hadoop-apache/2010-05/msg00073.html if above is down)

I put the code in package com.awcoleman.BouncyCastleGenericCDRHadoopWithWritable in github.

The basics from the thread above and a bit of other reading are:
If your class will only be used as a value and not a key, implement the Writable interface.
If your class will be used as a key (and possibly a value), implement the WritableComparable interface (which extends Writable).

A Writable must have 3 things:
An empty contructor. There can be other contructors with arguments, but there must be a no argument one as well.
An overridden write method to write variables out.
An overridden readFields method to populate an object from a previous write method output.

Hadoop reuses Writable objects, so cleaning all variables before populating them in readFields will stop surprises.

WritableComparable adds to Writable:
An overridden hashcode method to partition keys.
An overridden compareTo method.

The advice given in the 'How to write a complex Writable' thread adds:
Override the equals method
Implement RawComparator for your type. This post (http://vangjee.wordpress.com/2012/03/30/implementing-rawcomparator-will-speed-up-your-hadoop-mapreduce-mr-jobs-2/) has an example that extends WritableComparator, which implements RawComparator.

In my example in github, I only tested Writable since I pull individual fields and wrap them as Text or LongWritable for the keys.

Processing ASN.1 Call Detail Records with Hadoop (using Bouncy Castle) Part 3

Finally we get to the Hadoop Map/Reduce job...

We created the data and created a simple decoder to test, so now we can take the decoding logic and put it in a RecordReader.

The InputFormat we create is very simple - set isSplitable false and use our RecordReader named RawFileRecordReader.


The RecordReader does the bulk of the work.


RawFileRecordReader simply returns the filename and the count of the ASN.1 records in the file. We can change that to something more useful in a later post.

The Driver is also simple.


Get the full code on github. The code here is the simplest way to handle binary data files. There are lots of things to add for better performance. If the data files are large enough, adding in splitting logic may be worthwhile. If the data files are small, it may be worth using a map job to group them into sequence files, or convert them into avro files.

Update: Links to Part 1, Part 2, Part 3.

Processing ASN.1 Call Detail Records with Hadoop (using Bouncy Castle) Part 2

The Stand-alone Decoder

Now that we have created sample data, we can create a simple decoder with the Bouncy Castle library.


The decompressStream method is a little overkill, but will let the sample data be compressed and handle it fine. This causes a dependency on commons-compress but can also be removed easily (just change to return input).

To iterate through the ASN.1 file, we keep grabbing objects from ASN1InputStream with readObject. Once we have an object, we use it to create a CallDetailRecord instance.


Using Bouncy Castle requires some digging into the data format to get the expected set of classes. Now that the decoder is complete, we can move on to the Map/Reduce job. We didn't have to create a decoder and could have jumped straight into the Map/Reduce job, but creating a simple decoder for the first time I tackle a binary format has always saved me time.

Update: Links to Part 1, Part 2, Part 3.

Processing ASN.1 Call Detail Records with Hadoop (using Bouncy Castle)

In these posts I describe using the Bouncy Castle java library to process Call Detail Records (CDRs) in ASN.1 format (encoded as DER). The same process should work for any ASN.1 data encoded as DER.

I hope to replicate this with an ASN.1 Java compiler (BinaryNotes), but right now bnotes does not handle indefinite length. With an ASN.1 compiler, the compiler will create the Java classes from the ASN.1 specification so I don't have to manually create the classes to hold the data.

Creating some data

First, I need a specification I can work with and post. I created a "Simple Generic CDR" ASN.1 specification/schema:

GenericCDR-Schema DEFINITIONS IMPLICIT TAGS ::=
BEGIN
GenericCallDataRecord ::= SEQUENCE {
recordNumber [APPLICATION 2] IMPLICIT INTEGER,
callingNumber [APPLICATION 8] IMPLICIT UTF8String (SIZE(1..20)),
calledNumber [APPLICATION 9] IMPLICIT UTF8String (SIZE(1..20)),
startDate [APPLICATION 16] IMPLICIT  UTF8String (SIZE(8)),
startTime [APPLICATION 18] IMPLICIT UTF8String (SIZE(6)),
duration [APPLICATION 19] IMPLICIT INTEGER
}
END

For production data, the ASN.1 specification (also called grammar) would come from the vendor producing the data.

The awesome OSS Nokalva people have an online schema checker/compiler and data encoder/decoder. (If you are looking for support, I think you can fairly easily switch out Bouncy Castle with OSS Nokalva's ASN.1 Tools for Java but I haven't tried it).

To create data, paste the above schema into the Schema textbox at asn1-playground.oss.com and press Compile. "Compiled successfully." should show up below the textbox. If not the Console Output textbox on the page should give some clues to the problem.

Next paste in some text-formatted data to compile. In the Data: Encode text box, paste in:

first-cdr GenericCallDataRecord ::=
{
recordNumber 1,
callingNumber "15555550100",
calledNumber "15555550101",
startDate "20131016",
startTime "134534",
duration 65
}
second-cdr GenericCallDataRecord ::=
{
    recordNumber 2,
    callingNumber "15555550102",
    calledNumber "15555550104",
startDate "20131016",
startTime "134541",
duration 52
}
third-cdr GenericCallDataRecord ::=
{
    recordNumber 3,
    callingNumber "15555550103",
    calledNumber "15555550102",
startDate "20131016",
startTime "134751",
duration 62
}
fourth-cdr GenericCallDataRecord ::=
{
    recordNumber 4,
    callingNumber "15555550104",
    calledNumber "15555550102",
startDate "20131016",
startTime "134901",
duration 72
}
fifth-cdr GenericCallDataRecord ::=
{
    recordNumber 5,
    callingNumber "15555550101",
    calledNumber "15555550100",
startDate "20131016",
startTime "135134",
duration 32
}
And press Encode. The Console Output box should show 0 errors. To download the ASN.1 DER encoded data press the DER link below the Data: Encode textbox. The XML link is also nice to download since that is a human readable representation of the same data.

I put the files from all the encoding options in the asn1data folder of the github repo for this post.

Next I create a standalone decoder, then create a Hadoop InputFormat and RecordReader, and finally run the Hadoop job to process the ASN.1 DER-encoded data we just created above.

Update: Links to Part 1, Part 2, Part 3.

Pivotal HD pre-2.0 HDFS-Fuse workaround

I found an issue with HDFS-Fuse on Pivotal HD 1.1 (and the workaround). Hopefully this saves someone a few minutes of troubleshooting.

With a simple

/usr/bin/hadoop-fuse-dfs dfs://MyNameNode:8020 /mnt/hdfs

I received

bash: /usr/bin/hadoop-fuse-dfs: /bin/base: bad interpreter: No such file or directory

There are some typos in the file /usr/bin/hadoop-fuse-dfs. The following sed commands fix the file:

#Make a backup copy first
cp -p /usr/bin/hadoop-fuse-dfs /tmp/hadoop-fuse-dfs.`date +'%Y%m%d.%H%M'`.bak

#Now fix typos
sed -i 's|^#!/bin/base|#!/bin/bash|' /usr/bin/hadoop-fuse-dfs
sed -i 's|^/sbin/modprob fuse|/sbin/modprobe fuse|' /usr/bin/hadoop-fuse-dfs
sed -i '/^# Autodetect JAVA_HOME if not definedif \[ -e \/usr\/libexec\/bigtop-detect-javahome \]; then/ a\
if [ -e /usr/libexec/bigtop-detect-javahome ]; then
' /usr/bin/hadoop-fuse-dfs
sed -i '/^. \/usr\/lib\/bigtop-utils\/bigtop-detect-javahome/ a\
fi
' /usr/bin/hadoop-fuse-dfs
sed -i 's|\\\$|$|g' /usr/bin/hadoop-fuse-dfs
sed -i 's|\\`|`|g' /usr/bin/hadoop-fuse-dfs
sed -i 's|\$/|/|g' /usr/bin/hadoop-fuse-dfs

Now hadoop-fuse-dfs works properly:

/usr/bin/hadoop-fuse-dfs dfs://MyNameNode:8020 /mnt/hdfs

ls /mnt/hdfs/
apps  benchmarks hive  mapred  test  tmp  user  yarn

Pivotal support said this should be fixed in Pivotal HD 2.0, until then the simple sed workaround works fine.

Another option for machines that are not part of the cluster (eg client machines accessing HDFS) is to use HDFS-Fuse from the Bigtop repo, however this may make support from Pivotal more difficult if issues arise.

Testing Pivotal HD

I'm on a project where we are interfacing with EMC's Hadoop distribution Pivotal HD. Pivotal differentiates itself from the more traditional Hadoop distributions by adding on HAWQ (essentially Greenplum running on HDFS), USS (a remote datasource to Hadoop translator), and GemFire (an in-memory DB) - essentially it is "Hadoop with Greenplum on top". The SQL distributed query engine HAWQ looks extremely nice and brings all the maturity of Greenplum with it. On another project, I am working around some unsupported methods in the Hive JDBC driver. With HAWQ, I am able to use the standard Postgres JDBC driver (even psql from the RHEL/CentOS repos connects fine).

So far all my testing has been on local VMs so I don't have a feel for HAWQ performance. Once my work transitions to the cluster it will be interesting to see how it performs.

I normally shy away from closed source projects when there are good open source alternatives since I really like being able to grab the source and take a look at what is happening (among other reasons). I would be using Pivotal for this project regardless since that out of my hands. However, HAWQ seems to have a lead on SQL implementation versus the competitors (Stinger/Hive on Tez, Impala, and Presto).

Hopefully I'll have enough time to look at how a custom PXF (roughly equivalent to a Hive SerDe) compares to a custom InputFormat and Hive SerDe for a binary format like a telco switch cdr file.

CC licensed SS7 ISUP Call Flow Diagram

I needed a simple diagram showing the ISUP messages during a call for a visual aid for an analysis. Google images didn't turn up any diagrams permissively licensed, so I created one. Hopefully it can save someone else time not reinventing the wheel (or in this case redrawing it...)

The telephone icon is from AIGA Symbol Signs by AIGA License: Public Domain
The switch icon is from Typicons by Stephen Hutchings  License: Creative Commons (Attribution-Share Alike 3.0 Unported)
Both are untouched.

Image: Author: Andrew Coleman License: Creative Commons Attribution 4.0 Unported (CC BY 4.0)
Hopefully Google Image will pick up the license since it is in the comment section of the png...