Presto and MySQL

What is MySQL?

MySQL is a popular open-source relational database management system (RDBMS) that data engineers use to organize data into tables. MySQL supports foreign keys to create relationships between tables. You can create, modify and extract data from the relational database using SQL commands.

MySQL is easy and intuitive to use; you only have to know a few SQL statements to use it, like SHOW DATABASES, CREATE DATABASE, USE, CREATE TABLE, SHOW TABLES, DESCRIBE, INSERT INTO … VALUES,  SELECT, WHERE, DELETE, ALTER TABLE…ADD, ALTER TABLE…DROP, and SHOW CREATE TABLE. 

MySQL has a client-server architecture, which means that many clients can communicate with the MySQL server to run queries, create tables, etc. You can scale with ease; MySQL is fully multithreaded and handles large data volumes. It supports over 20 platforms like Linux, Windows, UNIX, OS, etc. You can roll back transactions, commit and undo MySQL statements using the ROLLBACK statement. It has memory efficiency. You can partition your data, which improves query performance.

MySQL Use Cases

Data engineers don’t typically use MySQL to run queries against massive data volumes. Instead, more common use cases might include: 

•      Small web-based databases: an eCommerce store, a web-page database, and an app database (with a small data volume)

•      Online analytical processing and online transaction processing systems (OLAP/OLTP) (with small data volume)

•      Building a business intelligence tool that doesn’t have a massive data volume

If you want to query big data with your MySQL database, you can use PrestoDB.

What is PrestoDB?

PrestoDB is an open source SQL query engine to query data lakes and across data sources, without having to move your data. Presto runs queries directly on files stored in databases like MySQL. It joins multiple databases from different data sources like MySQL, Hadoop, Cassandra, etc.

Using MySQL and Presto together

Data engineers commonly use MySQL and PrestoDB for joining different systems like Hive, Cassandra, and MySQL and running queries across those data sources; it’s common to use partitioning to run queries efficiently.   

You can use MySQL and PrestoDB together to run queries against a massive data volume. PrestoDB works directly on files stored in MySQL storage.  

PrestoDB has a MySQL connector to run queries and create tables in your MySQL database. You also can join different MySQL databases or different systems like MySQL and Hive. 

PrestoDB is used to map the data in the MySQL storage engine to schemas and tables. Data engineers use the Hive metastore service to access the metadata catalog that lives in the MySQL database.

You create a catalog properties file named “catalog_name.properties” in “etc/catalog” to mount the MySQL connector. This file must have the connector.name, connection-url, connection-user, and the connection-password. You can have as many catalog files as you want.

In PrestoDB, we have schemas that contain database objects like tables. So, we have one schema for each database. To create, modify and extract data on your MySQL database using PrestoDB, you can use the next statements: 

•      USE dbname: it selects the database

•      CREATE DATABASE dbname: it creates a database

•      CREATE TABLE table_name: it creates a table

•      LOAD DATA: it loads data from a file

•      INTO TABLE table_name: it adds rows to a table

•      SHOW SCHEMAS FROM catalog_name: it shows all databases from that catalog. 

•      SHOW TABLES FROM catalog_name.dbname: it shows all tables from the database (dbname). 

•      DESCRIBE catalog_name.dbname.table_name: it shows a list of columns in the table (table_name) in the database (dbname).

•      SHOW COLUMNS FROM  catalog_name.dbname.table_name: it shows a list of columns in the table (table_name) in the database (dbname).

•      SELECT * FROM catalog_name.dbname.table_name: it shows the table (table_name) in the database (dbname). 

If you’re ready to use Presto to query MySQL but don’t want to worry about the complexities or overhead of managing Presto, you can use a managed service like Ahana Cloud. Ahana Cloud gives you the power of Presto without having to get under the hood. It’s a managed service for AWS and can integrate with MySQL.

Get started with a 14-day free trial of Ahana Cloud.