Overview

MySQL HeatWave on AWS is a fully managed database service, powered by the integrated HeatWave in-memory query accelerator. It’s the only cloud database service that combines transactions, analytics, and machine learning services across data stored in MySQL and data lake, delivering real-time, secure analytics without the complexity, latency, and cost of extract, transform, and load (ETL) duplication.

For organizations who want to store and process data in MySQL database, they first need to import the data to MySQL HeatWave. MySQL provides two MySQL Shell capabilities to import data to MySQL HeatWave:

1. Parallel Table Import – imports large data files in delimited text file format to MySQL table.
2. Dump Loading – imports MySQL dump files exported by MySQL Shell Dump utility.

Both provide fast import to MySQL HeatWave on AWS. However, it requires users to install MySQL shell on a client machine. In AWS environment, users typically install MySQL shell on an AWS EC2 client machine which has multiple drawbacks.

We are excited to introduce the new data import from Amazon S3 capability in MySQL HeatWave on AWS. Data Import for MySQL HeatWave on AWS allows users to import data directly to MySQL HeatWave from Amazon S3. It provides a simple and intuitive user interface on the MySQL HeatWave on AWS console, allowing users to easily import data from Amazon S3 to MySQL HeatWave.

The new data import capability provides much better usability with enhanced security and better performance:

• Ease of use: Data import only takes a few clicks on the MySQL HeatWave on AWS console. It supports the same data formats as MySQL Shell (MySQL shell dump and delimited text files).

• Reduced cost: No separate EC2 instance required to run MySQL shell. No data transfer costs to read data from S3 to MySQL HeatWave instance.
• Enhanced security: In addition to supporting AWS User Access Key for authentication to Amazon S3, the new data import supports AWS IAM Role. This grants exclusive access to the user data on Amazon S3 to specific MySQL HeatWave instance(s), providing tighter security which is better suited for enterprise production workloads.
• Better performance: Data on Amazon S3 is directly downloaded and imported to MySQL HeatWave instance, data is not transferred twice.

Use Cases

The new data import capability is suited for the following scenarios:

1. Migration from
   • MySQL-compatible databases such as AWS Aurora, AWS RDS, MySQL on AWS EC2 instance or MySQL on-premises – Data can be dumped using MySQL Shell and then imported to MySQL HeatWave on AWS.
   • AWS Redshift, Snowflake or Google BigQuery – Data can be exported in delimited text files, then imported to MySQL HeatWave on AWS.
2. Periodic data import from delimited text files
   • Organizations get data from their customers and partners – this data can now be easily imported into MySQL HeatWave.
   • Internal business application data and logs – organizations often need to process this data for downstream applications or for various business purposes.

Quick Start Example

This example walks through how to import a MySQL Dump, namely airportdb, into a MySQL HeatWave instance, load the data to HeatWave cluster, and run query in HeatWave.

1. On MySQL HeatWave on AWS console, navigate to the DB Systems tab and click on Create MySQL DB System.

2. Fill the Create MySQL DB System and HeatWave Cluster forms.
   • Use the following AWS IAM Role -it will allow the MySQL DB System to read from the Amazon S3 bucket in Step 5- as Data Import Role ARN:

arn:aws:iam::612981981079:role/oracle-mysql-heatwave-sample-data-role

3. Navigate to the Data Imports tab, click on Connect to MySQL DB System and log in to the DB system created in step 2.

4. Click on Import Data.

5. Fill the Import Data into MySQL DB System form and click on Import. Use the following Amazon S3 URI – an Oracle-owned demo bucket that provides sample data- that hosts the AirportDB MySQL Dump:

s3://oracle-mysql-heatwave-sample-data-us-east-1/published/airportdb/v1/mysqldump/

6. Click on AirportDB Data Import.

7. AirportDB Data Import Details include a per-schema, per-table progress report updated regularly until all schemas and tables have been loaded.

8. Navigate to the Manage Data in Heatwave tab, select all airportdb tables and click on Load into HeatWave.

9. Click on Load Tables to confirm the load operation.

10. AirportDB Load into HeatWave Import Details include a per-schema, per-table progress report updated regularly until all tables have been loaded into HeatWave.

11. Navigate to the Query Editor, type the following SQL query in the editor and click on Run Query.

USE airportdb;                                                                               

SELECT price, count(*) FROM booking WHERE price > 500 GROUP BY price ORDER BY price LIMIT 10;

12. The query completes in less than a second in HeatWave.

13. But the same query takes an order of magnitude more time in MySQL.

Summary

The new data import on MySQL HeatWave on AWS provides an easy, secured and cost-effective way to import data to MySQL HeatWave. It provides an intuitive web console for users to quickly import data directly to MySQL HeatWave from Amazon S3, enabling organizations to efficiently import their data to MySQL HeatWave for transactional, analytic and machine learning workloads.

Additional Resources:

• MySQL HeatWave AutoML Technical Brief
• MySQL HeatWave migration program
• Request a free MySQL HeatWave workshop