Data Platform/Systems/Presto

Presto is an SQL engine which you can use to query data in the Data Lake. We currently run Presto in read-only mode.

As of June 2025, we are running Presto 0.288.1, so the most accurate documentation is at prestodb.io/docs/0.288/.1.

Use

The Presto command-line interface is available on all the stat hosts. Here's how to use it:

$ kinit

$ presto --catalog analytics_hive

Presto can also be easily accessed through Python using Wmfdata:

#!/usr/bin/env python3
import wmfdata as wmf

# Returns a Pandas dataframe
wmf.presto.run("SHOW TABLES FROM event")

Catalogs

Presto can connect to many different data sources. The data from each source is represented by a different catalog.

For example, you can use Presto to query data in analytics_hive.wmf.mediawiki_history. In this case:

analytics_hive is the catalog
wmf is the schema (sometimes called the "database")
mediawiki_history is the table

List of catalogs

We have the following catalogs in our Presto setup:

analytics_hive: Data Lake tables using the Hive table format
analytics_iceberg: Data Lake tables using the Iceberg table format
thanos: experimental, Prometheus operational metrics, accessed via Thanos

Accessing a catalog

You can access a particular catalog by passing the right argument to your query tool:

for Wmfdata, wmf.presto.run(query, catalog=...)
- Wmfdata defaults to analytics_hive
for the command-line interface, presto --catalog ...

You can also always access a particular table by using the full name, including the catalog, in the query. For example, SELECT * FROM thanos.default.haproxy_frontend_http_responses_total will always work no matter what catalog has been specified by the query too.

Resource Groups

We have enabled a feature of Presto called Resource Groups, which allow us to set various parameters relating to concurrency, priority, job queueing etc. based on the client application or other elements. See task T424112 for the initial implementation.

The initial design of the resource groups is to try to ensure that GrowthBook and Superset are prioritised, over ad-hoc queries from Jupyter notebooks and stat hosts.

We use one resourcegroup called global, which uses the weighted policy. There are three subgroups, called high_priority, standard, and heavy. They have scheduling weights of 6, 3, and 1, respectively, for a total of 10. and that ratio decides which group global picks to start a query from when more than one has queries waiting. The memory limits are soft: a group may exceed its share when the cluster has spare memory, running queries are never killed for crossing the limit, and the limit instead throttles admission of new queries once passed. None of this takes effect until the cluster is contended.

The `high_priority` group

This group contains the Superset and GrowthBook traffic, matched by Kerberos principal. It runs up to 12 queries concurrently (hardConcurrencyLimit: 12), queues up to 40 more (maxQueued: 40), and may use up to 35% of cluster query memory (softMemoryLimit: 35%). Its weight of 6 is the highest of the three, so under contention global starts its queries roughly six times in ten, ahead of the other groups. Internally it uses the default fair policy, so queries start in arrival order.

The `standard` group

This group is the catch-all for everything not matched as Superset or GrowthBook and not tagged heavy, which in practice means JupyterHub and presto CLI queries. It allows 20 concurrent queries (hardConcurrencyLimit: 20), queues up to 100 (maxQueued: 100), and may use up to 45% of cluster memory (softMemoryLimit: 45%). Its weight of 3 wins about three of every ten scheduling decisions under contention. Queries run first-in-first-out under the default fair policy.

The `heavy` group

This group is an opt-in lane for large queries, used only when a client sets the heavy tag. It is the most constrained of the three, with 5 concurrent queries (hardConcurrencyLimit: 5), 60 queued (maxQueued: 60), and up to 20% of cluster memory (softMemoryLimit: 20%). Its weight of 1 is the lowest, so global starts a heavy query only about one time in ten., when the cluster is contended. The intended effect is to let large queries through slowly, without crowding out the interactive traffic.

If you are using the presto CLI, you can request that a particular session use the heavy group like this:

presto --client-tags heavy

SQL quirks

Strings in double quotes (e.g. "string") are treated as identifiers (e.g. the name of a table or field). Strings in single quotes (e.g. 'string') are treated as string literals.
Unlike with Hive or Spark, we run presto in read-only mode so you can only run SELECT queries.

Presto on Superset

Presto is available in Superset via the SQL Lab panel:

go to https://superset.wikimedia.org/sqllab
select database presto_analytics_hive
select schema event for Eventlogging (just an example, or any available one)
select any table to get a preview of the content, attributes, etc..

Please note: you will be able to see only the data that your user is allowed to, according to the POSIX permissions in puppet.

Background

We have been looking for a query-engine that would facilitate querying the mediawiki-history dataset. The dataset is somewhat "big" but not one of our largest (~750Gb, 3 billion rows) and the expected queries would be analytics-style queries (group by, count, sum rather than random-read of single rows). Our requirements are to provide a SQL compliant query interface, with interesting SQL-analytics features (window functions). This two requirements are "functionally" satisfied by Hive but Hive has significant issues when it comes to performance, there is a significant time-overhead for launching jobs and relying on MapReduce for computation makes the ratio of job-duration to data-size very bad for small-ish data.

We had several alternatives for this use-case: Hive, Druid, Clickhouse, and Presto.

Presto has been chosen as the best technology fitting our needs. It was developed by Facebook to solve Hive issues with speed.

Reasons why we choose Presto:

It matches all the SQL needs with the advantage of being SQL-ANSI compliant, by opposition to all other systems that use dialects
It is really faster than Hive for small/medium size data. A bit less fast than Clickhouse and Druid for the queries Druid can process (Druid is actually not a general SQL-engine^[1]).
It reads from HDFS and other big-data storage systems, making it easy to load/reload/maintain datasets (unlike Clickhouse and Druid).
It takes advantage of Parquet, the standard Hadoop columnar data format
It is the preferred tool of many other big players for querying analytics-oriented data in an exploratory way. It has a live ecosystem.

Administration

Please check Data_Platform/Systems/Presto/Administration.

↑ As of today (September 2018) there two drawbacks on using Druid as a general SQL query engine: there is a significant scope of SQL that Druid would not be able to parse, and a broad range of queries (nested group-by for instance) would fail at computation-stage.

[1] As of today (September 2018) there two drawbacks on using Druid as a general SQL query engine: there is a significant scope of SQL that Druid would not be able to parse, and a broad range of queries (nested group-by for instance) would fail at computation-stage.

[1]

Use