Figure 5: Mysql database status

Task 1 : Run Autoload to infer the schema and estimate capacity
required for the DELIVERY table in the Object Store

        
MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL >  SET @db_list = '["mysql_customer_orders"]';
Query OK, 0 rows affected (0.0012 sec)      
        

MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL >  SET @dl_tables = '[{
                                                         '> "db_name": "mysql_customer_orders",
                                                         '> "tables": [{
                                                         '>     "table_name": "delivery_orders",
                                                         '>     "dialect": 
                                                         '>        {
                                                         '>        "format": "csv",
                                                         '>        "field_delimiter": "\t",
                                                         '>        "record_delimiter": "\n"
                                                         '>        },
                                                         '>     "file": [{"par": "(https://objectstorage.ca-toronto-1.oraclecloud.com/p/IZ7Lo3HMB42T0vfh5QPuCIkIZiTpayz3l1OBFC6kqQq3B_6yIFNPfhRYE2vcBfpd/n/yza00k7tuks0/b/lakehouse-files/o/delivery-orders-1.csv"}]
                                                         '> }] }]';
Query OK, 0 rows affected (0.0005 sec)
MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > SET @options = JSON_OBJECT('mode', 'dryrun',  'policy', 'disable_unsupported_columns',  'external_tables', CAST(@dl_tables AS JSON));
Query OK, 0 rows affected (0.0006 sec)
     

Now let’s load tables to heatwave nodes using
“CALL sys.heatwave_load(@db_list, @options)”

Once Autoload completes running, its output has several pieces of
information: a. Whether the table exists in the schema you have
identified. b. Auto schema inference determines the number of columns
in the table. c. Auto schema sampling samples a small number of rows
from the table and determines the number of rows in the table and the
size of the table. d. Auto-provisioning determines how much memory
would be needed to load this table into HeatWave and how much time
loading this data take.

     
MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > CALL sys.heatwave_load(@db_list, @options);             

MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > SET @options = JSON_OBJECT('mode', 'dryrun',  'policy', 'disable_unsupported_columns',  'external_tables', CAST(@dl_tables AS JSON));
Query OK, 0 rows affected (0.0005 sec)
MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > CALL sys.heatwave_load(@db_list, @options);
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 2.20                            |
|                                          |
| Load Mode: dryrun                        |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal                      |
|                                          |
+------------------------------------------+
6 rows in set (9.4463 sec)

+--------------------------------------------------------------------------------------------------------------------+
| LAKEHOUSE AUTO SCHEMA INFERENCE                                                                                    |
+--------------------------------------------------------------------------------------------------------------------+
| Verifying external lakehouse tables: 1                                                                             |
|                                                                                                                    |
| SCHEMA                   TABLE                    TABLE IS           RAW     NUM. OF      ESTIMATED     SUMMARY OF |
| NAME                     NAME                     CREATED      FILE SIZE     COLUMNS      ROW COUNT     ISSUES     |
| ------                   -----                    --------     ---------     -------      ---------     ---------- |
| `mysql_customer_orders`  `delivery_orders`        NO            1.10 GiB           7           30 M                |
|                                                                                                                    |
| New schemas to be created: 0                                                                                       |
| External lakehouse tables to be created: 1                                                                         |
|                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------+
10 rows in set (9.4463 sec)

+------------------------------------------------------------------------------------------------------------------------+
| OFFLOAD ANALYSIS                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------+
| Verifying input schemas: 1                                                                                             |
| User excluded items: 0                                                                                                 |
|                                                                                                                        |
| SCHEMA                       OFFLOADABLE    OFFLOADABLE     SUMMARY OF                                                 |
| NAME                              TABLES        COLUMNS     ISSUES                                                     |
| ------                       -----------    -----------     ----------                                                 |
| `mysql_customer_orders`                1              7     3 table(s) are not loadable, 6 table(s) are already loaded |
|                                                                                                                        |
| Total offloadable schemas: 1                                                                                           |
|                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------+
10 rows in set (9.4463 sec)

+-----------------------------------------------------------------------------------------------------------------------------+
| CAPACITY ESTIMATION                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------+
| Default encoding for string columns: VARLEN (unless specified in the schema)                                                |
| Estimating memory footprint for 1 schema(s)                                                                                 |
|                                                                                                                             |
|                                TOTAL       ESTIMATED       ESTIMATED       TOTAL     DICTIONARY      VARLEN       ESTIMATED |
| SCHEMA                   OFFLOADABLE   HEATWAVE NODE      MYSQL NODE      STRING        ENCODED     ENCODED            LOAD |
| NAME                          TABLES       FOOTPRINT       FOOTPRINT     COLUMNS        COLUMNS     COLUMNS            TIME |
| ------                   -----------       ---------       ---------     -------     ----------     -------       --------- |
| `mysql_customer_orders`            1      682.25 MiB      512.00 KiB           1              0           1         10.00 s |
|                                                                                                                             |
| Sufficient MySQL host memory available to load all tables.                                                                  |
| Sufficient HeatWave cluster memory available to load all tables.                                                            |
|                                                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (9.4463 sec)

+-------------------------------------------------------------------------------------------------------------+
| LOAD SCRIPT GENERATION                                                                                      |
+-------------------------------------------------------------------------------------------------------------+
| Dryrun mode only generates the load script                                                                  |
| Set mode to "normal" in options to load tables                                                              |
|                                                                                                             |
| Retrieve load script containing 2 generated DDL commands using the query below:                             |
| Deprecation Notice: "heatwave_load_report" will be deprecated, please switch to "heatwave_autopilot_report" |
|   SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id;  |
|                                                                                                             |
| Applying changes will take approximately 10.00 s                                                            |
|                                                                                                             |
| Caution: Executing the generated load script will affect the secondary engine flags in the schema           |
|                                                                                                             |
+-------------------------------------------------------------------------------------------------------------+
11 rows in set (9.4463 sec)

Query OK, 0 rows affected (9.4463 sec)
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL >  

Modify the columns and create the table in heatwave. If you need to
load this table again drop the table and load it again.

 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Load Script                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE `mysql_customer_orders`.`delivery_orders`( `col_1` int unsigned NOT NULL, `col_2` bigint unsigned NOT NULL, `col_3` tinyint unsigned NOT NULL, `col_4` varchar(9) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `col_5` tinyint unsigned NOT NULL, `col_6` tinyint unsigned NOT NULL, `col_7` tinyint unsigned NOT NULL) ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://yza00k7tuks0.objectstorage.ca-toronto-1.oci.customer-oci.com/p/CuWqb71FgRTcK37zrrYmOZQ_AokIUB2CCIg2fOO9tIpFSNEjrNfpRE5n7Q3lVhmO/n/yza00k7tuks0/b/lakehouse-files/o/delivery-orders-1.csv"}], "dialect": {"format": "csv", "field_delimiter": "\t", "record_delimiter": "\n"}}'; |
| ALTER TABLE `mysql_customer_orders`.`delivery_orders` SECONDARY_LOAD;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.0008 sec)

As the table is loaded to heatwave now let’s try to query the table.
As this is directly coming from memory query execution drops to
microseconds.

  CREATE TABLE `mysql_customer_orders`.`delivery_orders`
 ( `orders_delivery` int unsigned NOT NULL, `order_id` bigint unsigned NOT NULL, `customer_id` tinyint unsigned NOT NULL,
  `order_status` varchar(9) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `store_id` tinyint unsigned NOT NULL,
  `delivery_vendor_id` tinyint unsigned NOT NULL, `estimated_time_hours` tinyint unsigned NOT NULL) ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://objectstorage.ca-toronto-1.oraclecloud.com/p/IZ7Lo3HMB42T0vfh5QPuCIkIZiTpayz3l1OBFC6kqQq3B_6yIFNPfhRYE2vcBfpd/n/yza00k7tuks0/b/lakehouse-files/o/delivery-orders-1.csv"}], "dialect": {"format": "csv", "field_delimiter": "\t", "record_delimiter": "\n"}}';
  
  
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > desc delivery_orders;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| orders_delivery      | int unsigned     | NO   |     | NULL    |       |
| order_id             | bigint unsigned  | NO   |     | NULL    |       |
| customer_id          | tinyint unsigned | NO   |     | NULL    |       |
| order_status         | varchar(9)       | NO   |     | NULL    |       |
| store_id             | tinyint unsigned | NO   |     | NULL    |       |
| delivery_vendor_id   | tinyint unsigned | NO   |     | NULL    |       |
| estimated_time_hours | tinyint unsigned | NO   |     | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
7 rows in set (0.0015 sec)
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > 

 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > select count(*) from delivery_orders;
+----------+
| count(*) |
+----------+
| 29999998 |
+----------+
1 row in set (0.0621 sec)
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > select * from delivery_orders limit 5;
+-----------------+-----------+-------------+--------------+----------+--------------------+----------------------+
| orders_delivery | order_id  | customer_id | order_status | store_id | delivery_vendor_id | estimated_time_hours |
+-----------------+-----------+-------------+--------------+----------+--------------------+----------------------+
|         5283230 | 368181698 |          84 | COMPLETE     |       21 |                  6 |                   28 |
|         5283231 | 368181699 |          84 | COMPLETE     |       22 |                  5 |                   48 |
|         5283232 | 368181700 |          84 | COMPLETE     |       23 |                  5 |                   17 |
|         5283233 | 368181701 |          84 | COMPLETE     |       23 |                  6 |                   58 |
|         5283234 | 368181702 |          84 | COMPLETE     |       22 |                  2 |                   43 |
+-----------------+-----------+-------------+--------------+----------+--------------------+----------------------+
5 rows in set (0.0271 sec)
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL > select o.* ,d.*  from  orders o
                                                         -> join delivery_orders d on o.order_id = d.order_id
                                                         -> where o.order_id = 93751524;
+----------+---------------------+-------------+--------------+----------+-----------------+----------+-------------+--------------+----------+--------------------+----------------------+
| ORDER_ID | ORDER_DATETIME      | CUSTOMER_ID | ORDER_STATUS | STORE_ID | orders_delivery | order_id | customer_id | order_status | store_id | delivery_vendor_id | estimated_time_hours |
+----------+---------------------+-------------+--------------+----------+-----------------+----------+-------------+--------------+----------+--------------------+----------------------+
| 93751524 | 2022-08-23 00:00:00 |          81 | COMPLETE     |        3 |         1377370 | 93751524 |          81 | COMPLETE     |        3 |                  3 |                   65 |
+----------+---------------------+-------------+--------------+----------+-----------------+----------+-------------+--------------+----------+--------------------+----------------------+
1 row in set (0.3301 sec)
 MySQL  10.0.1.192:33060+ ssl  mysql_customer_orders  SQL >