Presto (or PrestoDB) is a distributed, fast, reliable SQL Query Engine that fetches data from heterogeneous data sources querying large sets((TB, PB) of data and processes in memory. It originated in Facebook as a result of Hive taking a long time to execute queries of TB, and PB magnitude. The problem with Hive was that it would store intermediate results on disk which resulted in a significant I\/O overhead on disk. In 2015, Netflix showed PrestoDB was 10 times faster than Hive. Presto is written in Java. It resembles a massively parallel processing (MPP) system that facilitates the separation between storage and computing and allows it to scale its computing power horizontally by adding more servers.<\/p>\n
What Presto is Not?<\/strong><\/p>\n Since Presto understands SQL, it is not a general-purpose relational database. It is not a replacement for MySQL, Oracle, etc., though it provides the features of a standard database. It was not designed to handle OLTP. Its main benefit and value can be seen in Data Warehousing and Analytics where a large volume of data is collected from various sources to produce reports. They fit into the world of OLAP.<\/p>\n Presto Architecture<\/strong><\/p>\n Presto Concepts<\/strong><\/p>\n Coordinator<\/strong>: This is the brain of presto. It receives the query from the client, parses, plans, and manages the worker nodes. It keeps a track of activity on worker nodes and coordinates the execution of the query. It fetches the results from the worker nodes and returns the final result to the client. Additionally, Presto uses a discovery service that is running on the coordinator, where each worker can register and periodically send their heartbeat. This runs on the same HTTP server \u2014 including the same port.<\/p>\n Worker<\/strong>: Worker nodes are the nodes that execute the tasks and process data. They fetch data from connectors and exchange intermediate data with each other. HTTP is the communication between coordinators and workers, coordinators and clients, and between workers<\/p>\n Connector<\/strong>: Presto uses a connector to connect to various data sources. In the world of databases, this equates to DB drivers. Each connector needs to implement 4 SPI(Service Provider Interface)<\/p>\n Catalog<\/strong>: The catalog contains schemas and references to a data source via a connector.<\/p>\n Schema<\/strong>: A schema is a collection of tables. In RDBMS like PostgreSQL and MySQL, this translates to the concept of Schema or a Database.<\/p>\n Table<\/strong>: Collection of data in terms of rows, columns, and associated data types.<\/p>\n Statement<\/strong>: Statements are defined in the ANSI SQL standard, consisting of clauses, expressions, and predicates.<\/p>\n Query:\u00a0<\/strong>The previous SQL statement is parsed into a query and creates a distributed query plan consisting of a series of interconnected stages that contain all of the below elements.<\/p>\n Stage:\u00a0<\/strong>The execution is structured in a hierarchy of stages that resembles a tree. They model the distributed query plan but are not executed by the worker nodes.<\/p>\n Task:<\/strong>\u00a0Each stage consists of a series of tasks that are distributed over the Presto worker nodes. Tasks contain one or more parallel drivers.<\/p>\n Split:<\/strong>\u00a0Tasks operate on splits, which are sections of a larger data set.<\/p>\n Driver:<\/strong>\u00a0Drivers work with the data and combine operators to produce output that is aggregated by a task and delivered to another task in another stage. Each driver has one input and one output.<\/p>\n Operator:<\/strong>\u00a0An operator consumes, transforms, and produces data.<\/p>\n Exchange:<\/strong>\u00a0Exchanges transfer data between Presto nodes for different stages in a query.<\/p>\n Presto Connectors<\/strong><\/p>\n Overall there are 30+ known connectors that Presto supports. The following are a few well know connectors that Presto supports.<\/p>\n Event Listener<\/strong><\/p>\n One of the nice things about Presto is clean abstractions, one such clean abstraction is Event Listeners. Event Listener allows you to write custom functions that listen to events happening inside the engine. Event listeners are invoked for the following events:<\/p>\n To Create Custom Listeners we would need to do the following:<\/p>\n Query Optimization<\/p>\n PrestoDB uses two optimizers. The Rule-Based Optimizer (RBO) applies filters to remove irrelevant data and uses hash joins to avoid full cartesian joins. This includes strategies such as predicate pushdown, limit pushdown, column pruning, and decorrelation. It also uses a Cost-Based Optimizer (CBO). Here it uses statistics of the table (e.g., number of distinct values, number of null values, distributions of column data) to optimize queries and reduce I\/O and network overhead. The following are ways to see available statistics and see cost-based analysis of a query<\/p>\n SHOW STATS FOR table_name \u2014 Approximated statistics for the named table<\/p>\n SHOW STATS FOR ( SELECT query ) \u2014 Approximated statistics for the query result<\/p>\n EXPLAIN SELECT query \u2014 Execute statement and show the distributed execution plan with the cost of each operation.<\/p>\n EXPLAIN ANALYZE SELECT \u2014 Execute statement and show the distributed execution plan with the cost and duration of each operation.<\/p>\n SQL Language and SQL Statement Syntax<\/strong><\/p>\n We can use DDL, DML, DQL, DCL, TCL which modern databases support. The following are supported in PrestoDB<\/p>\n It also supports the following data types<\/p>\n Boolean, TINYINT, SMALLINT, INTEGER, BIGINT, DOUBLE, DECIMAL, VARCHAR, CHAR, JSON, DATE, TIME, TIMESTAMP, ARRAY, MAP, IPADDRESS<\/p>\n An Example with JMX Connector<\/p>\n Java Management Extensions (JMX) gives information about the Java Virtual Machine and software running inside JVM. With the JMX connector, we can query JMX information from all nodes in a Presto cluster. JMX is actually a connector that is figured so that chosen JMX information will be periodically dumped and stored in tables (in the \u201cjmx\u201d catalog) which can be queried. JMX is useful for debugging and monitoring Presto metrics.<\/p>\n To configure the JMX connector, create catalog properties file\u00a0etc\/catalog\/jmx.properties<\/em>\u00a0with the following<\/p>\n connector.name=jmx<\/p>\n JMX connector supports 2 schemas \u2014 current and history<\/p>\n To enable periodical dumps, define the following properties:<\/p>\n We will use jdbc driver to connect to presto \u2014 com.facebook.presto.jdbc.PrestoDriver<\/p>\n If we want to see all of the available MBeans by running SHOW TABLES, we can use SHOW TABLES FROM jmx.current<\/p>\n If we want to see the open and maximum file descriptor counts for each node then the following is the query \u2014 SELECT openfiledescriptorcount, maxfiledescriptorcount<\/p>\n FROM jmx.current.java.lang:type=operatingsystem`<\/p>\n Where can Presto be used?<\/strong><\/p>\n PrestoDB’s architecture, features, and use cases highlight its speed and scalability for large-scale data analytics and warehousing.<\/p>\n","protected":false},"author":11,"featured_media":7043,"comment_status":"open","ping_status":"open","sticky":false,"template":"single-engg.php","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[49],"tags":[],"class_list":["post-6905","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-engineering"],"acf":[],"yoast_head":"\n![\"\"](\"https:\/\/www.zeta.tech\/us\/wp-content\/uploads\/sites\/2\/2024\/11\/db-structure.png\")
<\/p>\n\n
\n
\n
\n
\n
connector.name=jmx\r\n\r\njmx.dump-tables=java.lang:type=Runtime,com.facebook.presto.execution.scheduler:name=NodeScheduler\r\n\r\njmx.dump-period=10s\r\n\r\njmx.max-entries=86400<\/pre>\n
the following is used to extract the JVM version of every node.\r\n
String dbUrl= \u201cjdbc:presto:\/\/localhost:9000\/catalogName\/schemaName\u201d;<\/span><\/em><\/code><\/pre>\n\n
Connection conn = null;<\/span><\/em><\/code> Statement stmt = null;<\/span><\/em><\/code> try {<\/span><\/em><\/code> Class.forName(\u201ccom.facebook.presto.jdbc.PrestoDriver\u201d);<\/span><\/em><\/code> conn = DriverManager.getConnection(dbUrl, \u201cusername\u201d, \u201cpassword\u201d);<\/span><\/em><\/code> stmt = conn.createStatement();<\/span><\/em><\/code> String sql = \u201cSELECT node, vmname, vmversion from jmx.current.java.lang:type=runtime\u201d;<\/span><\/em><\/code> ResultSet res = stmt.executeQuery(sql);<\/span><\/em><\/code> while (res.next()) {<\/span><\/em><\/code> String node= res.getString(\u201cnode\u201d);<\/span><\/em><\/code> String vmname= res.getString(\u201cvmname\u201d);<\/span><\/em><\/code> String vmversion= res.getString(\u201cvmversion\u201d);<\/span><\/em><\/code> }<\/span><\/em><\/code> res.close();<\/span><\/em><\/code> stmt.close();<\/span><\/em><\/code> conn.close();<\/span><\/em><\/code> } catch (SQLException se) {<\/span><\/em><\/code> se.printStackTrace();<\/span><\/em><\/code> } catch (Exception e) {<\/span><\/em><\/code> e.printStackTrace();<\/span><\/em><\/code> } finally {<\/span><\/em><\/code> try {<\/span><\/em><\/code> if (stmt != null) stmt.close();<\/span><\/em><\/code> } catch (SQLException sqlException) {<\/span><\/em><\/code> sqlException.printStackTrace();<\/span><\/em><\/code> }<\/span><\/em><\/code> try {<\/span><\/em><\/code> if (conn != null) conn.close();<\/span><\/em><\/code> } catch (Exception e) {<\/span><\/em><\/code> e.printStackTrace();<\/span><\/em><\/code> }<\/span><\/em><\/code> }<\/span><\/em><\/code> }<\/span><\/em><\/code> }<\/em><\/code><\/pre>\n<\/blockquote>\n\n