Adding a database to MySQL HeatWave using Auto Parallel Load
If you are reading this post, more than likely you are either looking at Oracle’s MySQL HeatWave for the first time and trying to understand how to load your database into the HeatWave cluster. There are actually two approached to loading data into a HeatWave cluster. These are:
- Manually (Long way)
- Auto Parallel (quick way)
This post we are going to focus in on loading the data via Auto Parallel.
Auto Parallel facilities the process of loading data into a HeatWave Cluster by automating many of the steps involved, including:
Excluding Schemas, tables, and columns that cannot be loaded
Verifying that there is sufficient memory available for the data
Optimizing load parallelism base on machine-learning models
General loading of data into Heatwave
Auto Parallel Load is implemented via a stored procedure named heatwave_load which resides in the sys schema. Running Auto Parallel Load involves issuing a call statement for the sys.heatwave_load procedure. The arguments that need to be provided are the schema and options that are required. In the example below, we are loading the “airportdb” into a two-node HeatWave cluster:
MYSQL> CALL sys.heatwave_load(JSON_ARRAY(“airportdb”), NULL);
Once the database is loaded into a HeatWave Cluster, the store procedure will produce a load summary. Below is the load summary for the “airportdb”:
LOAD SUMMARY | -----------------------------------------------------------------------------+ | SCHEMA TABLES TABLES COLUMNS LOAD | NAME LOADED FAILED LOADED DURATION | ------ ------ ------ ------- ———— | `airportdb` 14 0 105 30.34 s | |
The “airportdb” is about 2GB in size and it 30.3 seconds to load into the HeatWave cluster. Although this database is small, that is pretty impressive when loading data into memory.
To confirm that these fourteen tables have been loaded, the following query is used to confirm the load status of the tables:
MYSQL> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
The result set indicates that all fourteen tables have been loaded successfully:
NAME |LOAD_STATUS | ---------------------------+-------------------+ airportdb.flight_log |AVAIL_RPDGSTABSTATE| airportdb.airport_geo |AVAIL_RPDGSTABSTATE| airportdb.flight |AVAIL_RPDGSTABSTATE| airportdb.passengerdetails |AVAIL_RPDGSTABSTATE| airportdb.passenger |AVAIL_RPDGSTABSTATE| airportdb.airplane |AVAIL_RPDGSTABSTATE| airportdb.weatherdata |AVAIL_RPDGSTABSTATE| airportdb.flightschedule |AVAIL_RPDGSTABSTATE| airportdb.booking |AVAIL_RPDGSTABSTATE| airportdb.employee |AVAIL_RPDGSTABSTATE| airportdb.airplane_type. |AVAIL_RPDGSTABSTATE| airportdb.airport |AVAIL_RPDGSTABSTATE| airportdb.airline |AVAIL_RPDGSTABSTATE| airportdb.airport_reachable|AVAIL_RPDGSTABSTATE|
Now that you now how to load tables using the Auto Parallel Load process, go have fun with MySQL HeatWave and OLAP queries.
Enjoy!!
