Presto: The Definitive Guide: SQL at Any Scale, on Any Storage, in Any Environment

Cover
Copyright
Table of Contents
Foreword
Preface
	About the Book
	Conventions Used in This Book
	Code Examples, Permissions, and Attribution
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Part I. Getting Started with Presto
	Chapter 1. Introducing Presto
		The Problems with Big Data
		Presto to the Rescue
			Designed for Performance and Scale
			SQL-on-Anything
			Separation of Data Storage and Query Compute Resources
		Presto Use Cases
			One SQL Analytics Access Point
			Access Point to Data Warehouse and Source Systems
			Provide SQL-Based Access to Anything
			Federated Queries
			Semantic Layer for a Virtual Data Warehouse
			Data Lake Query Engine
			SQL Conversions and ETL
			Better Insights Due to Faster Response Times
			Big Data, Machine Learning, and Artificial Intelligence
			Other Use Cases
		Presto Resources
			Website
			Documentation
			Community Chat
			Source Code, License, and Version
			Contributing
			Book Repository
			Iris Data Set
			Flight Data Set
		A Brief History of Presto
		Conclusion
	Chapter 2. Installing and Configuring Presto
		Trying Presto with the Docker Container
		Installing from Archive File
			Java Virtual Machine
			Python
			Installation
			Configuration
		Adding a Data Source
		Running Presto
		Conclusion
	Chapter 3. Using Presto
		Presto Command-Line Interface
			Getting Started
			Pagination
			History
			Additional Diagnostics
			Executing Queries
			Output Formats
			Ignoring Errors
		Presto JDBC Driver
			Downloading and Registering the Driver
			Establishing a Connection to Presto
		Presto and ODBC
		Client Libraries
		Presto Web UI
		SQL with Presto
			Concepts
			First Examples
		Conclusion
Part II. Diving Deeper into Presto
	Chapter 4. Presto Architecture
		Coordinator and Workers in a Cluster
		Coordinator
		Discovery Service
		Workers
		Connector-Based Architecture
		Catalogs, Schemas, and Tables
		Query Execution Model
		Query Planning
			Parsing and Analysis
			Initial Query Planning
		Optimization Rules
			Predicate Pushdown
			Cross Join Elimination
			TopN
			Partial Aggregations
		Implementation Rules
			Lateral Join Decorrelation
			Semi-Join (IN) Decorrelation
		Cost-Based Optimizer
			The Cost Concept
			Cost of the Join
			Table Statistics
			Filter Statistics
			Table Statistics for Partitioned Tables
			Join Enumeration
			Broadcast Versus Distributed Joins
		Working with Table Statistics
			Presto ANALYZE
			Gathering Statistics When Writing to Disk
			Hive ANALYZE
			Displaying Table Statistics
		Conclusion
	Chapter 5. Production-Ready Deployment
		Configuration Details
		Server Configuration
		Logging
		Node Configuration
		JVM Configuration
		Launcher
		Cluster Installation
		RPM Installation
			Installation Directory Structure
			Configuration
			Uninstall Presto
		Installation in the Cloud
		Cluster Sizing Considerations
		Conclusion
	Chapter 6. Connectors
		Configuration
		RDBMS Connector Example PostgreSQL
			Query Pushdown
			Parallelism and Concurrency
			Other RDBMS Connectors
			Security
		Presto TPC-H and TPC-DS Connectors
		Hive Connector for Distributed Storage Data Sources
			Apache Hadoop and Hive
			Hive Connector
			Hive-Style Table Format
			Managed and External Tables
			Partitioned Data
			Loading Data
			File Formats and Compression
			MinIO Example
		Non-Relational Data Sources
		Presto JMX Connector
		Black Hole Connector
		Memory Connector
		Other Connectors
		Conclusion
	Chapter 7. Advanced Connector Examples
		Connecting to HBase with Phoenix
		Key-Value Store Connector Example: Accumulo
			Using the Presto Accumulo Connector
			Predicate Pushdown in Accumulo
		Apache Cassandra Connector
		Streaming System Connector Example: Kafka
		Document Store Connector Example: Elasticsearch
			Overview
			Configuration and Usage
			Query Processing
			Full-Text Search
			Summary
		Query Federation in Presto
		Extract, Transform, Load and Federated Queries
		Conclusion
	Chapter 8. Using SQL in Presto
		Presto Statements
		Presto System Tables
		Catalogs
		Schemas
		Information Schema
		Tables
			Table and Column Properties
			Copying an Existing Table
			Creating a New Table from Query Results
			Modifying a Table
			Deleting a Table
			Table Limitations from Connectors
		Views
		Session Information and Configuration
		Data Types
			Collection Data Types
			Temporal Data Types
			Type Casting
		SELECT Statement Basics
		WHERE Clause
		GROUP BY and HAVING Clauses
		ORDER BY and LIMIT Clauses
		JOIN Statements
		UNION, INTERSECT, and EXCEPT Clauses
		Grouping Operations
		WITH Clause
		Subqueries
			Scalar Subquery
			EXISTS Subquery
			Quantified Subquery
		Deleting Data from a Table
		Conclusion
	Chapter 9. Advanced SQL
		Functions and Operators Introduction
		Scalar Functions and Operators
		Boolean Operators
		Logical Operators
		Range Selection with the BETWEEN Statement
		Value Detection with IS (NOT) NULL
		Mathematical Functions and Operators
		Trigonometric Functions
		Constant and Random Functions
		String Functions and Operators
		Strings and Maps
		Unicode
		Regular Expressions
		Unnesting Complex Data Types
		JSON Functions
		Date and Time Functions and Operators
		Histograms
		Aggregate Functions
			Map Aggregate Functions
			Approximate Aggregate Functions
		Window Functions
		Lambda Expressions
		Geospatial Functions
		Prepared Statements
		Conclusion
Part III. Presto in Real-World Uses
	Chapter 10. Security
		Authentication
			Password and LDAP Authentication
		Authorization
			System Access Control
			Connector Access Control
		Encryption
			Encrypting Presto Client-to-Coordinator Communication
			Creating Java Keystores and Java Truststores
			Encrypting Communication Within the Presto Cluster
		Certificate Authority Versus Self-Signed Certificates
		Certificate Authentication
		Kerberos
			Prerequisites
			Kerberos Client Authentication
			Cluster Internal Kerberos
		Data Source Access and Configuration for Security
		Kerberos Authentication with the Hive Connector
			Hive Metastore Thrift Service Authentication
			HDFS Authentication
		Cluster Separation
		Conclusion
	Chapter 11. Integrating Presto with Other Tools
		Queries, Visualizations, and More with Apache Superset
		Performance Improvements with RubiX
		Workflows with Apache Airflow
		Embedded Presto Example: Amazon Athena
		Starburst Enterprise Presto
		Other Integration Examples
		Custom Integrations
		Conclusion
	Chapter 12. Presto in Production
		Monitoring with the Presto Web UI
			Cluster-Level Details
			Query List
			Query Details View
		Tuning Presto SQL Queries
		Memory Management
		Task Concurrency
		Worker Scheduling
			Scheduling Splits per Task and per Node
			Local Scheduling
		Network Data Exchange
			Concurrency
			Buffer Sizes
		Tuning Java Virtual Machine
		Resource Groups
			Resource Group Definition
			Scheduling Policy
			Selector Rules Definition
		Conclusion
	Chapter 13. Real-World Examples
		Deployment and Runtime Platforms
		Cluster Sizing
		Hadoop/Hive Migration Use Case
		Other Data Sources
		Users and Traffic
		Conclusion
	Chapter 14. Conclusion
Index
About the Authors
Colophon