Using Presto to combine data from Hive and MySQL in one SQL-like query

Tutorial: Using Presto to combine data from Hive and MySQL in one SQL-like query

 Tutorials •  Comments (4)

Presto (originated at Facebook) is a yet another distributed SQL query engine for Hadoop that has recently generated huge excitement. What makes Presto so interesting, especially, in comparison to other existing engines like Cloudera Impala or Hive on Tez? The list of key differentiators includes:

• Presto seems to be great for ad-hoc analysis – it runs queries quickly and scales from gigabytes to petabytes.
• A single Presto query can process data from multiple sources e.g. HDFS, MySQL, Cassandra or even Kafka.
• Large-scale companies like Facebook, Netflix, Airbnb or Dropbox like it and actively contribute to it.

Because (a) the excitement about Presto is evident, (b) running own benchmarks is time-consuming, in this tutorial we focus on the feature that allow us to process data from HDFS and MySQL in a single Presto query. Such a feature is quite unique, because it’s hasn’t been provided by other open-source projects like Hive and Impala (Impala, however, can process Hive and HBase table in a single query).

Presto Connectors

First, a bit of technical background. Presto supports pluggable connectors that provide metadata and data for queries. There is Hive connector (currently supports Text, SequenceFile, RCFile, ORC and, in a limited way, Parquet formats), MySQL connector and several others. You can even implement a custom one.

A coordinator (a master daemon) uses connectors to get metadata (such as table schema) that is needed to build a query plan. Workers use connectors to get actual data that will be processed by them.

Our use-case

To demonstrate this feature, we use the Last.fm Dataset – 1K users dataset provided by Last.fm. It contains profile information (such as gender, age, country) about 1K users plus tracks that these users had been listening to.

In our tutorial, user profile information (40 kilobytes) will be kept in a MySQL table (something that actually makes sense in real-world scenarios when user base is small enough), while information about streamed tracks (2.4 gigabytes) will be uploaded to a Hive table.

Training environment

For simplicity and quick iterations, we use an already pre-configured single-node Hadoop YARN cluster with Hive and MySQL. An easy way to get it is to download Hortonworks Sandbox (we used Hortonworks Sandbox 2.2).

Presto installation

Because the installation of Presto is very smooth and well documented, we don’t duplicate these steps here. Should you follow the instructions carefully, you have a running Presto server in less then 10 minutes.

Please note that Presto requires Java 8.

Unfortunately, Presto on YARN is not “officially” supported yet. This means that a Presto daemon should be installed on each node of your cluster (and share resources with NodeManager and YARN containers, if you happen to run YARN on the same nodes). There is, however, an interesting blog post that describes how to run Presto on YARN over Apache Twill.

Integration between Presto and MySQL and Hive

Please follow the instructions needed to integrate Presto with MySQL and Hive. In case of Hortonworks Sandbox 2.2, files shown below contain exemplary (and working) configuration settings.

1 2 3 4 5

$ cat etc / catalog / mysql .properties connector .name = mysql connection - url = jdbc : mysql : / / localhost : 3306 connection - user = root connection - password =

1 2 3

$ cat etc / catalog / hive .properties connector .name = hive - hadoop2 hive .metastore .uri = thrift : / / localhost : 9083

When these files are created, you can start Presto server.

1	[ root @ sandbox presto - server - 0.89 ] $ bin / launcher run

Uploading input datasets into MySQL and Hive

First we create a MySQL database and table. Then, we upload information about users into it.

1 2 3 4 5 6 7 8 9 10 11 12

[root@sandbox presto - server -0.89]$ mysql mysql > CREATE DATABASE tutorial; mysql > USE tutorial; mysql > CREATE TABLE user (     - > userid VARCHAR(15), gender CHAR(1), age SMALLINT,     - > country VARCHAR(50), signup VARCHAR(50)     - > ); mysql > LOAD DATA     - > LOCAL INFILE '/root/lastfm-dataset-1K/userid-profile.tsv'     - > INTO TABLE user     - > FIELDS TERMINATED BY '\t'; mysql > SELECT * FROM user LIMIT 3;

Next, we create a Hive database and table. Then we upload streamed tracks into it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

$ cat / tmp / create - and - populate - stream - table .hql CREATE DATABASE IF NOT EXISTS tutorial ; USE tutorial ; CREATE TABLE IF NOT EXISTS stream (          userid STRING ,          timestamp STRING ,          artistid STRING ,          artistname STRING ,          trackid STRING ,          trackname STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE ;   LOAD DATA LOCAL INPATH '/root/lastfm-dataset-1K/userid-timestamp-artid-artname-traid-traname.tsv' OVERWRITE INTO TABLE stream ;   $ hive - f / tmp / create - and - populate - stream - table .hql

Using Presto to query both MySQL and Hive tables

Finally, we are ready to process data!