Handle CSV files with HeatWave MySQL
When it comes to loading data from CSV files into your MySQL environment, there’s no shortage of options.
In this post, I’ll walk you through two efficient, developer-friendly and MySQL-ish approaches:
- the powerful MySQL Shell with its built-in parallel import utility,
- and the fully automated HeatWave Auto Parallel Load feature.
Both can save you time and effort—especially when dealing with large datasets.
In this article, I’am using HeatWave 9.3.1:
SELECT version();
+----------------+
| version() |
+----------------+
| 9.3.1-u1-cloud |
+----------------+Also, I’m using the reviews.csv file for Paris, Île-de-France, France (03 March, 2025) from the Inside Airbnb dataset.
$ head /Data/project/source/reviews.csv
listing_id,date
3109,2017-10-28
3109,2017-11-03
3109,2018-07-24
3109,2019-10-24
5396,2009-06-30
5396,2009-07-03
5396,2009-07-08
5396,2009-09-10
5396,2009-12-02MySQL Shell’s parallel import utility
You must create the table that will store the data before importing it from the CSV file:
SQL>
CREATE SCHEMA homestays;
USE homestays;
CREATE TABLE `reviews_from_mysqlsh` (
`listing_id` bigint unsigned NOT NULL,
`date` date DEFAULT NULL
) ENGINE=InnoDB
;Import data from an object storage bucket
Now we can import the data. MySQL Shell supports importing input data files stored on a “local” disk or in Oracle Cloud Infrastructure (OCI) Object Storage buckets, using the routine util.importTable.
Here, my CSV file is stored on an OCI object storage bucket named Airbnb.
Leveraging MySQL Shell’s JavaScript syntax, you can do:
JS>
util.importTable("reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true, osBucketName: "Airbnb", osNamespace: "mynamespace"})
Importing from file 'reviews.csv' to table `homestays`.`reviews_from_mysqlsh` in MySQL Server at 10.0.1.2:3306 using 1 thread
[Worker000]: reviews.csv: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
99% (47.29 MB / 47.29 MB), 6.75 MB/s
File 'reviews.csv' (47.29 MB) was imported in 8.2168 sec at 5.76 MB/s
Total rows affected in homestays.reviews_from_mysqlsh: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0The main configuration options are:
- CSV file name: reviews.csv
- database name: homestays
- table name: reviews_from_mysqlsh
- dialect: CSV file created on a Unix systems
- Object storage bucket name: Airbnb
- Object storage namespace: mynamespace
And now you can query your data:
JS>
\sql SELECT * FROM homestays.reviews_from_mysqlsh LIMIT 5;
+------------+------------+
| listing_id | date |
+------------+------------+
| 3109 | 2017-10-28 |
| 3109 | 2017-11-03 |
| 3109 | 2018-07-24 |
| 3109 | 2019-10-24 |
| 5396 | 2009-06-30 |
+------------+------------+SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.5208 sec)SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=816457..816457 rows=1)
-> Table scan on reviews_from_mysqlsh (cost=0.267..551720 rows=2.06e+6)
1 row in set (0.0934 sec)As a side note, you can even do some basic transformations on the fly before loading the data. See decodeColumns and columns options.
Import data from a local disk
If the file is on a “local” disk, the syntax is the following:
JS>
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
Importing from file '/Data/project/source/reviews.csv' to table `homestays`.`reviews_from_mysqlsh` in MySQL Server at 10.0.1.2:3306 using 1 thread
[Worker000]: reviews.csv: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
99% (47.29 MB / 47.29 MB), 7.27 MB/s
File '/Data/project/source/reviews.csv' (47.29 MB) was imported in 6.9141 sec at 6.84 MB/s
Total rows affected in homestays.reviews_from_mysqlsh: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0Import data to a MySQL server
Obviously, util.importTable also works on a classic (I mean non HeatWave) MySQL instance. In this context, you will most likely need to set local_infile variable to 1. Its default value is OFF:
mysql>
SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+It allows you to avoid the following error:
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.importTable: Invalid preconditions (RuntimeError)Before changing this value I highly recommend to read: Security Considerations for LOAD DATA LOCAL. We don’t joke about security…
So basically, you’ll need to do something like this:
JS>
\sql SET GLOBAL local_infile = 1;
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
\sql SET GLOBAL local_infile = 0;Turning our attention back to HeatWave — as you may already know, a HeatWave cluster can dramatically accelerate your queries, enabling you to use the familiar MySQL API in analytics scenarios such as data warehousing and lakehousing.
To unlock these performance superpowers, you first need to load your data into the HeatWave cluster. Once that’s done, you can fully enjoy the incredible speed and efficiency it brings to your workloads!
Load data into the HeatWave Cluster
To load your data into your HeatWave Cluster from your MySQL table use the sys.heatwave_load stored procedure.
SQL>
CALL sys.heatwave_load(JSON_ARRAY("homestays"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0158 sec)
+-----------------------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+-----------------------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 0 0 1 table(s) are not loadable |
| |
| No offloadable schema found, HeatWave Auto Load terminating |
| |
| Total errors encountered: 1 |
| Total warnings encountered: 3 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+-----------------------------------------------------------------------------------------+
15 rows in set (0.0158 sec)Oops!!
It failed! There is an error. Let’s check it:
SQL>
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn');
+-------------------------------------------------------------------------------------------------------------------------+
| log |
+-------------------------------------------------------------------------------------------------------------------------+
| {"error": "Unable to load table without primary key", "table_name": "reviews_from_mysqlsh", "schema_name": "homestays"} |
| {"warn": "1 table(s) are not loadable", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found for given input target"} |
+-------------------------------------------------------------------------------------------------------------------------+{“error”: “Unable to load table without primary key”, “table_name”: “reviews_from_mysqlsh”, “schema_name”: “homestays”}
Well, I conveniently forgot to mention one important requirement: the table must have a primary key. 😉
If your table doesn’t have a natural or meaningful primary key, no worries — one option is to use Generated Invisible Primary Keys (GIPKs). This allows MySQL to automatically add an invisible primary key behind the scenes.
An ALTER TABLE operation is all it takes:
SQL>
ALTER TABLE homestays.reviews_from_mysqlsh ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL INVISIBLE AUTO_INCREMENT PRIMARY KEY FIRST;
Query OK, 0 rows affected (6.3596 sec)
Records: 0 Duplicates: 0 Warnings: 0You can read more about GIPKs in the official documentation.
Now, let’s examine the table’s current state:
SQL >
SHOW CREATE TABLE homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
Table: reviews_from_mysqlsh
Create Table: CREATE TABLE `reviews_from_mysqlsh` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`listing_id` bigint unsigned NOT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2068801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciWe can try again to load the data into the HeatWave Cluster:
SQL>
CALL sys.heatwave_load(JSON_ARRAY("homestays"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0124 sec)
+------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 1 3 |
| |
| Total offloadable schemas: 1 |
| |
+------------------------------------------------------------------------+
10 rows in set (0.0124 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 |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `homestays` 1 77.61 MiB 256.00 KiB 0 0 0 58.00 s |
| |
| Sufficient MySQL host memory available to load all tables. |
| Sufficient HeatWave cluster memory available to load all tables. |
| |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.0124 sec)
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXECUTING LOAD SCRIPT |
+---------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave Load script generated |
| Retrieve load script containing 3 generated DDL command(s) 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; |
| |
| Adjusting load parallelism dynamically per internal/external table. |
| Using current parallelism of 4 thread(s) as maximum for internal tables. |
| |
| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema |
| |
| Using SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| |
| Proceeding to load 1 table(s) into HeatWave. |
| |
| Applying changes will take approximately 57.79 s |
| |
+---------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.0124 sec)
+----------------------------------------------------+
| TABLE LOAD |
+----------------------------------------------------+
| TABLE (1 of 1): `homestays`.`reviews_from_mysqlsh` |
| Commands executed successfully: 3 of 3 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 3 |
| Table loaded using 4 thread(s) |
| Elapsed time: 7.92 s |
| |
+----------------------------------------------------+
8 rows in set (0.0124 sec)
+-------------------------------------------------------------------------------+
| LOAD SUMMARY |
+-------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `homestays` 1 0 3 7.92 s |
| |
| Total errors encountered: 0 |
| Total warnings encountered: 0 |
| |
+-------------------------------------------------------------------------------+
9 rows in set (0.0124 sec)Et voilà!
The new table structure is:
SQL>
SHOW CREATE TABLE reviews_from_mysqlsh\G
*************************** 1. row ***************************
Table: reviews_from_mysqlsh
Create Table: CREATE TABLE `reviews_from_mysqlsh` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`listing_id` bigint unsigned NOT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2068801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPIDPlease note the SECONDARY_ENGINE=RAPID new clause.
And you can still query your table according to your needs:
SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1160 sec)SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_mysqlsh in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0935 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed oneHeatWave’s Auto Parallel Load
HeatWave’s Auto Parallel Load is a key feature within HeatWave that automatically loads data into the HeatWave cluster, without requiring manual intervention or tuning. Data loading is performed using multiple threads across nodes in the HeatWave cluster, significantly speeding up the operation.
And guess what? you already know the command, it is sys.heatwave_load.
To use it, first we need to define the command using JSON syntax. We recommend assigning this JSON structure to a variable, such as @input_list:
SET @input_list = '
[
{
"db_name": "homestays",
"tables": [
{
"table_name": "reviews_from_HW_load",
"engine_attribute": {
"file": [
{
"uri": "oci://Airbnb@mynamespace/reviews.csv"
}
],
"dialect": {
"format": "csv",
"field_delimiter": "auto",
"record_delimiter": "auto",
"has_header": true
}
}
}
]
}
]';You’ll find the previously used configuration:
- Database name: homestays
- Table name: reviews_from_HW_load
- The file is find following the URI:
- Object storage bucket name: Airbnb
- Object storage namespace: mynamespace
- CSV file name: reviews.csv
- The dialect is CSV, so the only information HeatWave’s Auto Parallel Load requires from us is the presence of a header in the file.
And like we have seen previously, run the stored procedure using the CALL statement:
SQL>
CALL sys.heatwave_load(CAST(@input_list AS JSON), NULL);
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0128 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 |
| ------ ----- -------- --------- ------- --------- ---------- |
| `homestays` `reviews_from_HW_load` NO 45.10 MiB 2 2.07 M |
| |
| New schemas to be created: 0 |
| External lakehouse tables to be created: 1 |
| |
+--------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0128 sec)
+------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 1 2 |
| |
| Total offloadable schemas: 1 |
| |
+------------------------------------------------------------------------+
10 rows in set (0.0128 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 |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `homestays` 1 57.57 MiB 192.00 KiB 0 0 0 7.00 s |
| |
| Sufficient MySQL host memory available to load all tables. |
| Sufficient HeatWave cluster memory available to load all tables. |
| |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.0128 sec)
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXECUTING LOAD SCRIPT |
+---------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave Load script generated |
| Retrieve load script containing 2 generated DDL command(s) 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; |
| |
| Adjusting load parallelism dynamically per internal/external table. |
| Using current parallelism of 4 thread(s) as maximum for internal tables. |
| |
| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema |
| |
| Using SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| |
| Proceeding to load 1 table(s) into HeatWave. |
| |
| Applying changes will take approximately 7.01 s |
| |
+---------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.0128 sec)
+----------------------------------------------------+
| TABLE LOAD |
+----------------------------------------------------+
| TABLE (1 of 1): `homestays`.`reviews_from_HW_load` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 2 |
| Elapsed time: 30.95 s |
| |
+----------------------------------------------------+
7 rows in set (0.0128 sec)
+----------------------------------------------------------------------------------+
| LOAD SUMMARY |
+----------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `homestays` 1 0 2 30.95 s |
| |
| Total errors encountered: 0 |
| Total warnings encountered: 2 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+----------------------------------------------------------------------------------+
11 rows in set (0.0128 sec)The operation completed successfully; however, two warnings were generated.
Details regarding these warnings are available in the sys.heatwave_autopilot_report table:
SQL >
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn')\G
*************************** 1. row ***************************
log: {"message": "[WARNINGS SUMMARY] Lakehouse Schema Inference had 1 warning(s) out of which 1 were not recorded (due to max_error_count limit or filtering rules)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 1}
*************************** 2. row ***************************
log: {"message": "[WARNINGS SUMMARY] 1 warning(s) with code: 6095(ER_LH_WARN_INFER_SKIPPED_LINES)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 2}Fortunately, nothing critical.
And now you can query your table according to your needs:
SQL>
SELECT COUNT(*) FROM reviews_from_HW_load;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1147 sec)SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_HW_load\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_HW_load in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0933 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed onPeroration
Whether you’re working with traditional MySQL or taking advantage of the blazing-fast analytics capabilities of HeatWave, importing CSV data doesn’t have to be a bottleneck. With tools like MySQL Shell’s parallel import utility and HeatWave’s Auto Parallel Load, you have flexible, scalable options that fit a variety of use cases — from local file loading to seamless integration with object storage.
By combining these tools with features like Generated Invisible Primary Keys, you can streamline the ingestion process and get your data ready for powerful, real-time analytics with minimal overhead.
So next time you’re staring at a CSV file and a big dataset to analyze, you’re fully equipped to handle it — the MySQL way.
Stay tuned for more insights!
Watch my videos on my YouTube channel and subscribe.
Thanks for using HeatWave & MySQL!
Cloud Solutions Architect at Oracle
MySQL Geek, strong interest in data and AI, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
Leave a Reply