This tutorial guides beginners to set up Presto and Hive Metastore on your local server to query data on S3.
Presto is the SQL Engine to plan and execute queries, S3 is the storage service for table partition files, and Hive Metastore is the catalog service for Presto to access table schema and location information. This tutorial will show you instructions step-by-step to install Presto, Hive MetaStore, and configure them to query data in a public S3 bucket.
Step1: Download and Launch Hive MetaStore
Download and extract the Hive binary tarball. In this tutorial, we download and use apache-hive-2.3.7-bin.tar.gz.
$ cd /path/to/tutorial/root
$ wget https://downloads.apache.org/hive/hive-2.3.7/apache-hive-2.3.7-bin.tar.gz
$ tar -zxf apache-hive-2.3.7-bin.tar.gz
$ cd apache-hive-2.3.7-bin
We only need to launch Hive Metastore to serve Presto catalog information such as table schema and partition location.
If it is the first time for you to launch Hive Metastore, prepare corresponding configuration files and environment, also initialize a new Metastore
$ export HIVE_HOME=`pwd`
$ cp conf/hive-default.xml.template conf/hive-site.xml
$ mkdir -p hcatalog/var/log/
$ bin/schematool -dbType derby -initSchema
Configure Hive to access S3 by appending the following lines in conf/hive-env.sh
. Hive needs the corresponding jars to access files with “s3a://
” addresses, and AWS credentials as well to access an S3 bucket (even it is public):
export HIVE_AUX_JARS_PATH=${HADOOP_HOME}/share/hadoop/tools/lib/aws-java-sdk-core-1.10.6.jar:${HADOOP_HOME}/share/hadoop/tools/lib/aws-java-sdk-s3-1.10.6.jar:${HADOOP_HOME}/share/hadoop/tools/lib/hadoop-aws-2.8.4.jar
export AWS_ACCESS_KEY_ID=<Your AWS Access Key>
export AWS_SECRET_ACCESS_KEY=<Your AWS Secret Key>
If you do not have a Hadoop installation which include above jars, you can also download them from maven central repository: aws-java-sdk-core-1.10.6.jar, aws-java-sdk-s3-1.10.6.jar and hadoop-aws-2.8.4.jar.
Start a Hive Metastore which will run in the background and listen on port 9083 (by default).
$ hcatalog/sbin/hcat_server.sh start
Started metastore server init, testing if initialized correctly...
Metastore initialized successfully on port[9083].
To verify if the MetaStore is running, check the Hive Metastore logs at hcatalog/var/log/
Step2: Download and Launch Presto Server
Goto Presto server installation, download and untar the pre-build server tarball. We will use 0.237.1 as an example in this tutorial.
$ cd /path/to/tutorial/root
$ wget https://repo1.maven.org/maven2/com/facebook/presto/presto-server/0.237.1/presto-server-0.237.1.tar.gz
$ tar -zxf presto-server-0.237.1.tar.gz
$ cd presto-server-0.237.1
Create a configuration file etc/config.properties
to include the basic Presto configuration.
coordinator=true
node-scheduler.include-coordinator=true
http-server.http.port=8080
discovery-server.enabled=true
discovery.uri=http://localhost:8080
Create etc/jvm.config
to specify the following JVM configuration.
-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError
Create etc/node.properties
to include the following lines.
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir=/tmp/presto/data
Lastly, configure Presto Hive connector in etc/catalog/hive.properties
, pointing to the Hive Metastore service that we just started. We also need to put AWS credentials here again so Presto can read the input files from S3.
connector.name=hive-hadoop2
hive.metastore.uri=thrift://localhost:9083
hive.s3.aws-access-key=<Your AWS Access Key>
hive.s3.aws-secret-key=<Your AWS Secret Key>
Run the Presto server in the background:
$ ./bin/launcher start
To verify Presto server is running, open http://localhost:8080
in your browser and check the server status from its web UI.
Step3: Launch a Presto CLI and Run Queries
Download Presto CLI which is a separate binary from the server.
$ cd /path/to/tutorial/root
$ wget https://repo1.maven.org/maven2/com/facebook/presto/presto-cli/0.237.1/presto-cli-0.237.1-executable.jar
$ mv presto-cli-0.237.1-executable.jar presto
$ chmod +x presto
Connect to the server started in the previous step.
$ ./presto --server localhost:8080 --catalog hive --debug
Use thedefault
schema
presto> use default;
USE
Create a new table based on the file in S3 with the schema. This information will be sent to Hive MetaStore.
presto:default> CREATE TABLE reason (
r_reason_sk integer,
r_reason_id varchar,
r_reason_desc varchar
) WITH (
external_location = 's3a://apc999/presto-tutorial/example-reason',
format = 'PARQUET'
);
CREATE TABLE
Scan the newly created table:
presto:default> SELECT * FROM reason limit 3;
r_reason_sk | r_reason_id | r_reason_desc
-------------+------------------+------------------------
1 | AAAAAAAABAAAAAAA | Package was damaged
2 | AAAAAAAACAAAAAAA | Stopped working
3 | AAAAAAAADAAAAAAA | Did not get it on time
(3 rows)Query 20200703_074406_00011_8vq8w, FINISHED, 1 node
http://localhost:8080/ui/query.html?20200703_074406_00011_8vq8w
Splits: 18 total, 18 done (100.00%)
CPU Time: 0.5s total, 6 rows/s, 2.06KB/s, 27% active
Per Node: 0.1 parallelism, 0 rows/s, 279B/s
Parallelism: 0.1
Peak User Memory: 0B
Peak Total Memory: 219B
Peak Task Total Memory: 219B
0:04 [3 rows, 1002B] [0 rows/s, 279B/s]
Step4: Stop Servers
$ cd /path/to/tutorial/root
$ presto-server-0.237.1/bin/launcher stop
$ apache-hive-2.3.7-bin/hcatalog/sbin/hcat_server.sh stop
Summary
In this tutorial, we demonstrated how to run setup Presto, Hive Metastore to run SQL queries with data in a public S3 bucket. Hope you find it useful.