Tuesday, May 20, 2014

Facebook's Presto

In November 2013 Facebook published their Presto engine as Open Source, available at GitHub. Presto is a distributed interactive SQL query engine, able to run over dozens of modern BigData stores, based on Apache Hive or Cassandra. Presto comes with a limited JDBC Connector, supports Hive 0.13 with Parquet and Views.


Just a few specialties. Presto runs only with Java7, does not support Kerberos and does not have built-in user authentication, neither. To protect data a user should not be able to read, the use of HDFS Acl's / POSIX permissions should be considered. The setup of Presto is pretty easy and well documented. Just follow the documentation, use "uuidgen" to generate a unique ID for your Presto Node (node.id in node.properties) and add "hive" as datasource (config.properties: datasources=jmx,hive). I used user "hive" to start the server with:
export PATH=/usr/jdk64/jdk1.7.0_45/bin:$PATH && presto-server-0.68/bin/launcher start

After the successful start you should be able to connect to Presto's Webinterface (discovery.uri in config.properties). The UI is pretty simple, but a good point to see what happens with your queries, how many splits are created and what time each step takes.

The CLI is a stand-alone self-executing jar file and can be placed on any computer which has installed Java7 and can connect to the Presto Instance. To be sure that the client is using the correct Java version a PATH inclusion may make sense:
export PATH=/usr/jdk64/jdk1.7.0_45/bin:$PATH && /software/presto --server [your-presto-server]:[port] --catalog hive --schema default

presto:default> show tables;

Now let's test if Presto is really fast and can compare with Impala. To make the tests more simple I wrote a small script which uses MR to generate sample data. Its available in my git-repo. Just run it as the user you want to be, maybe make it executable or use "sh". With the script I mentioned before I created a table called transactions, and this table we want to query. I post only 2 exemplary queries, but the script has a few more.

1. Finding highest gainers

select id, sum(amount) as amount from (select sender as id, amount * -1 as amount from transactions union all select recipient as id, amount from transactions) unionResult group by id order by amount desc limit 10;

Hive: 39.078 seconds, Fetched: 10 row(s)
Tez: 18.227 seconds, Fetched: 10 row(s)
Presto: 0:02 [1.2M rows, 38.2MB] [720K rows/s, 22.9MB/s]

2. Finding fraudsters

select count(*) from (select a.sender, a.recipient, b.recipient as c from transactions a join transactions b on a.recipient = b.sender where a.time < b.time and b.time - a.time < 5) i;

Hive: 208.065 seconds, Fetched: 1 row(s)
Tez: 101.758 seconds, Fetched: 1 row(s)
Presto: 1:02 [600K rows, 19.1MB] [9.7K rows/s, 317KB/s]


Since Tez brings a significant better performance, Presto brings light speed into Hadoop based SQL and can be measured with Impala. The advantage of Presto is the flexibility of connectors - the Presto Team will add more connectors for Oracle, MySQL, PostgresSQL and HBase very soon. Also Authentication (Kerberos), Authorization and SQL Grants will be supported within the next month [1].