{"id":7822,"date":"2025-06-26T15:17:04","date_gmt":"2025-06-26T14:17:04","guid":{"rendered":"https:\/\/dasini.net\/blog\/?p=7822"},"modified":"2025-06-26T15:22:49","modified_gmt":"2025-06-26T14:22:49","slug":"handle-csv-files-with-heatwave-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2025\/06\/26\/handle-csv-files-with-heatwave-mysql\/","title":{"rendered":"Handle CSV files with HeatWave MySQL"},"content":{"rendered":"\n<p>When it comes to loading data from CSV files into your MySQL environment, there\u2019s no shortage of options. <br>In this post, I\u2019ll walk you through two efficient, developer-friendly and MySQL-ish approaches:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>the powerful <strong><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/9.3\/en\/\" target=\"_blank\" rel=\"noopener\" title=\"MySQL Shell\">MySQL Shell<\/a><\/strong> with its built-in <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/9.3\/en\/mysql-shell-utilities-parallel-table.html\" target=\"_blank\" rel=\"noopener\" title=\"Parallel Table Import Utility\">parallel import utility<\/a>, <\/li>\n\n\n\n<li>and the fully automated <strong><a href=\"https:\/\/dev.mysql.com\/doc\/heatwave\/en\/mys-hw-auto-parallel-load.html\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave Auto Parallel Load\">HeatWave Auto Parallel Load<\/a><\/strong> feature. <\/li>\n<\/ul>\n\n\n\n<p>Both can save you time and effort\u2014especially when dealing with large datasets.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69d5c2bcb6692&quot;}\" data-wp-interactive=\"core\/image\" class=\"aligncenter size-thumbnail wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"830\" height=\"271\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on-async--click=\"actions.showLightbox\" data-wp-on-async--load=\"callbacks.setButtonStyles\" data-wp-on-async-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=150%2C150&amp;ssl=1\" alt=\"HeatWave\" class=\"wp-image-7222\" srcset=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?w=830&amp;ssl=1 830w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=300%2C98&amp;ssl=1 300w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=800%2C261&amp;ssl=1 800w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/12\/HeatWave_logo.png?resize=768%2C251&amp;ssl=1 768w\" sizes=\"auto, (max-width: 830px) 100vw, 830px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Agrandir\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on-async--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure><\/div>\n\n\n<p>In this article, I\u2019am using HeatWave 9.3.1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SELECT version();\n+----------------+\n| version()      |\n+----------------+\n| 9.3.1-u1-cloud |\n+----------------+<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Also, I&rsquo;m using the <em>reviews.csv<\/em> file for Paris, \u00cele-de-France, France (03 March, 2025) from the <a href=\"https:\/\/insideairbnb.com\/get-the-data\" target=\"_blank\" rel=\"noopener\" title=\"Inside Airbnb\">Inside Airbnb<\/a> dataset.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ head \/Data\/project\/source\/reviews.csv\nlisting_id,date\n3109,2017-10-28\n3109,2017-11-03\n3109,2018-07-24\n3109,2019-10-24\n5396,2009-06-30\n5396,2009-07-03\n5396,2009-07-08\n5396,2009-09-10\n5396,2009-12-02<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL Shell&rsquo;s parallel import utility<\/h2>\n\n\n\n<p>You must create the table that will store the data before importing it from the CSV file:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nCREATE SCHEMA homestays;\n\nUSE homestays;\n\nCREATE TABLE `reviews_from_mysqlsh` (\n  `listing_id` bigint unsigned NOT NULL,\n  `date` date DEFAULT NULL\n) ENGINE=InnoDB \n;<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Import data from an object storage bucket<\/h3>\n\n\n\n<p>Now we can import the data. MySQL Shell supports importing input data files stored on a \u00ab\u00a0local\u00a0\u00bb disk or in <a href=\"https:\/\/www.oracle.com\/cloud\/\" target=\"_blank\" rel=\"noopener\" title=\"Oracle Cloud Infrastructure\">Oracle Cloud Infrastructure (OCI)<\/a> <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/Object\/Tasks\/managingbuckets.htm\" target=\"_blank\" rel=\"noopener\" title=\"OCI Object Storage buckets\">Object Storage buckets<\/a>, using the routine <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/9.3\/en\/mysql-shell-utilities-parallel-table.html\" target=\"_blank\" rel=\"noopener\" title=\"Parallel Table Import Utility\">util.importTable<\/a>. <br>Here, my CSV file is stored on an OCI object storage bucket named <em>Airbnb<\/em>.<\/p>\n\n\n\n<p>Leveraging MySQL Shell&rsquo;s JavaScript syntax, you can do:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"javascript\" class=\"language-javascript\">JS&gt;\nutil.importTable(\"reviews.csv\", {schema: \"homestays\", table: \"reviews_from_mysqlsh\", dialect: \"csv-unix\", skipRows: 1, showProgress: true, osBucketName: \"Airbnb\", osNamespace: \"mynamespace\"})\nImporting from file 'reviews.csv' to table `homestays`.`reviews_from_mysqlsh` in MySQL Server at 10.0.1.2:3306 using 1 thread\n[Worker000]: reviews.csv: Records: 2068800  Deleted: 0  Skipped: 0  Warnings: 0\n99% (47.29 MB \/ 47.29 MB), 6.75 MB\/s                    \nFile 'reviews.csv' (47.29 MB) was imported in 8.2168 sec at 5.76 MB\/s\nTotal rows affected in homestays.reviews_from_mysqlsh: Records: 2068800  Deleted: 0  Skipped: 0  Warnings: 0<\/code><\/pre>\n\n\n\n<p>The main configuration options are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CSV file name: <em>reviews.csv<\/em><\/li>\n\n\n\n<li>database name: <em>homestays<\/em><\/li>\n\n\n\n<li>table name: <em>reviews_from_mysqlsh<\/em><\/li>\n\n\n\n<li>dialect: CSV file created on a Unix systems<\/li>\n\n\n\n<li>Object storage bucket name: <em>Airbnb<\/em><\/li>\n\n\n\n<li>Object storage namespace: <em>mynamespace<\/em><\/li>\n<\/ul>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>And now you can query your data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"javascript\" class=\"language-javascript\">JS&gt;\n\\sql SELECT * FROM homestays.reviews_from_mysqlsh LIMIT 5;\n+------------+------------+\n| listing_id | date       |\n+------------+------------+\n|       3109 | 2017-10-28 |\n|       3109 | 2017-11-03 |\n|       3109 | 2018-07-24 |\n|       3109 | 2019-10-24 |\n|       5396 | 2009-06-30 |\n+------------+------------+<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nSELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;\n+----------+\n| COUNT(*) |\n+----------+\n|  2068800 |\n+----------+\n1 row in set (0.5208 sec)<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nEXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\\G\n*************************** 1. row ***************************\nEXPLAIN: -&gt; Aggregate: count(0)  (cost=816457..816457 rows=1)\n    -&gt; Table scan on reviews_from_mysqlsh  (cost=0.267..551720 rows=2.06e+6)\n\n1 row in set (0.0934 sec)<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>As a side note, you can even do some basic transformations on the fly before loading the data. See <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/9.3\/en\/mysql-shell-utilities-parallel-table.html\" target=\"_blank\" rel=\"noopener\" title=\"Options for Importing Tables\"><em>decodeColumns<\/em> and <em>columns<\/em> options<\/a>.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Import data from a local disk<\/h3>\n\n\n\n<p>If the file is on a \u00ab\u00a0local\u00a0\u00bb disk, the syntax is the following:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"javascript\" class=\"language-javascript\">JS&gt;\nutil.importTable(\"\/Data\/project\/source\/reviews.csv\", {schema: \"homestays\", table: \"reviews_from_mysqlsh\", dialect: \"csv-unix\", skipRows: 1, showProgress: true})\nImporting 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\n[Worker000]: reviews.csv: Records: 2068800  Deleted: 0  Skipped: 0  Warnings: 0\n99% (47.29 MB \/ 47.29 MB), 7.27 MB\/s                    \nFile '\/Data\/project\/source\/reviews.csv' (47.29 MB) was imported in 6.9141 sec at 6.84 MB\/s\nTotal rows affected in homestays.reviews_from_mysqlsh: Records: 2068800  Deleted: 0  Skipped: 0  Warnings: 0<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Import data to a MySQL server<\/h3>\n\n\n\n<p>Obviously, <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/9.3\/en\/mysql-shell-utilities-parallel-table.html\" target=\"_blank\" rel=\"noopener\" title=\"Parallel Table Import Utility\">util.importTable<\/a> also works on a classic (I mean non HeatWave) MySQL instance. In this context, you will most likely need to set <em><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/9.3\/en\/server-system-variables.html#sysvar_local_infile\" target=\"_blank\" rel=\"noopener\" title=\"local_infile\">local_infile<\/a><\/em> variable to 1. Its default value is OFF:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">mysql&gt; \nSHOW GLOBAL VARIABLES LIKE 'local_infile';\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| local_infile  | OFF   |\n+---------------+-------+<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>It allows you to avoid the following error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.\nUtil.importTable: Invalid preconditions (RuntimeError)<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Before changing this value I highly recommend to read: <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/9.3\/en\/load-data-local-security.html\" target=\"_blank\" rel=\"noopener\" title=\"Security Considerations for LOAD DATA LOCAL\"><strong>Security Considerations for LOAD DATA LOCAL<\/strong><\/a>. We don&rsquo;t joke about security&#8230;<\/p>\n\n\n\n<p>So basically, you&rsquo;ll need to do something like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"javascript\" class=\"language-javascript\">JS&gt;\n\\sql SET GLOBAL local_infile = 1;\n\nutil.importTable(\"\/Data\/project\/source\/reviews.csv\", {schema: \"homestays\", table: \"reviews_from_mysqlsh\", dialect: \"csv-unix\", skipRows: 1, showProgress: true})\n\n\\sql SET GLOBAL local_infile = 0;<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Turning our attention back to  <strong>HeatWave<\/strong> \u2014 as you may already know, a HeatWave cluster can <strong>dramatically accelerate your queries<\/strong>, enabling you to use the familiar MySQL API in analytics scenarios such as data warehousing and lakehousing.<br>To unlock these performance superpowers, you first need to load your data into the HeatWave cluster. Once that\u2019s done, you can fully enjoy the incredible speed and efficiency it brings to your workloads!<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Load data into the HeatWave Cluster<\/h3>\n\n\n\n<p>To load your data into your HeatWave Cluster from your MySQL table use the<strong> <a href=\"https:\/\/dev.mysql.com\/doc\/heatwave\/en\/mys-hw-auto-parallel-load.html\" target=\"_blank\" rel=\"noopener\" title=\"Loading Data Using Auto Parallel Load\">sys.heatwave_load<\/a><\/strong> stored procedure.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nCALL sys.heatwave_load(JSON_ARRAY(\"homestays\"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));\n\n+------------------------------------------+\n| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |\n+------------------------------------------+\n| Version: 4.31                            |\n|                                          |\n| Load Mode: normal                        |\n| Load Policy: disable_unsupported_columns |\n| Output Mode: normal                      |\n|                                          |\n+------------------------------------------+\n6 rows in set (0.0158 sec)\n\n+-----------------------------------------------------------------------------------------+\n| OFFLOAD ANALYSIS                                                                        |\n+-----------------------------------------------------------------------------------------+\n| Verifying input schemas: 1                                                              |\n| User excluded items: 0                                                                  |\n|                                                                                         |\n| SCHEMA                       OFFLOADABLE    OFFLOADABLE     SUMMARY OF                  |\n| NAME                              TABLES        COLUMNS     ISSUES                      |\n| ------                       -----------    -----------     ----------                  |\n| `homestays`                            0              0     1 table(s) are not loadable |\n|                                                                                         |\n| No offloadable schema found, HeatWave Auto Load terminating                             |\n|                                                                                         |\n| Total errors encountered: 1                                                             |\n| Total warnings encountered: 3                                                           |\n| Retrieve the associated logs from the report table using the query below:               |\n|   SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn');        |\n|                                                                                         |\n+-----------------------------------------------------------------------------------------+\n15 rows in set (0.0158 sec)<\/code><\/pre>\n\n\n\n<p>Oops!!<br>It failed! There is an error. Let&rsquo;s check it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt; \nSELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); \n+-------------------------------------------------------------------------------------------------------------------------+\n| log                                                                                                                     |\n+-------------------------------------------------------------------------------------------------------------------------+\n| {\"error\": \"Unable to load table without primary key\", \"table_name\": \"reviews_from_mysqlsh\", \"schema_name\": \"homestays\"} |\n| {\"warn\": \"1 table(s) are not loadable\", \"schema_name\": \"homestays\"}                                                     |\n| {\"warn\": \"No offloadable tables found\", \"schema_name\": \"homestays\"}                                                     |\n| {\"warn\": \"No offloadable tables found for given input target\"}                                                          |\n+-------------------------------------------------------------------------------------------------------------------------+<\/code><\/pre>\n\n\n\n<p><em><strong>{\u00ab\u00a0error\u00a0\u00bb: \u00ab\u00a0Unable to load table without primary key\u00a0\u00bb, \u00ab\u00a0table_name\u00a0\u00bb: \u00ab\u00a0reviews_from_mysqlsh\u00a0\u00bb, \u00ab\u00a0schema_name\u00a0\u00bb: \u00ab\u00a0homestays\u00a0\u00bb}<\/strong><\/em><\/p>\n\n\n\n<p>Well, I <em>conveniently<\/em> forgot to mention one important requirement: <strong>the table must have a primary key<\/strong>. \ud83d\ude09 <\/p>\n\n\n\n<p>If your table doesn&rsquo;t have a natural or meaningful primary key, no worries \u2014 one option is to use <strong>Generated Invisible Primary Keys (GIPKs)<\/strong>. This allows MySQL to automatically add an invisible primary key behind the scenes.<\/p>\n\n\n\n<p>An ALTER TABLE operation is all it takes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt; \nALTER TABLE homestays.reviews_from_mysqlsh ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL INVISIBLE AUTO_INCREMENT PRIMARY KEY FIRST;\nQuery OK, 0 rows affected (6.3596 sec)\n\nRecords: 0  Duplicates: 0  Warnings: 0<\/code><\/pre>\n\n\n\n<p> You can read more about GIPKs <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/9.3\/en\/create-table-gipks.html\" target=\"_blank\" rel=\"noopener\" title=\"Generated Invisible Primary Keys (GIPK)\">in the official documentation<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Now, let&rsquo;s examine the table&rsquo;s current state:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL &gt; \nSHOW CREATE TABLE homestays.reviews_from_mysqlsh\\G\n*************************** 1. row ***************************\n       Table: reviews_from_mysqlsh\nCreate Table: CREATE TABLE `reviews_from_mysqlsh` (\n  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT \/*!80023 INVISIBLE *\/,\n  `listing_id` bigint unsigned NOT NULL,\n  `date` date DEFAULT NULL,\n  PRIMARY KEY (`my_row_id`)\n) ENGINE=InnoDB AUTO_INCREMENT=2068801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>We can try again to load the data into the HeatWave Cluster:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt; \nCALL sys.heatwave_load(JSON_ARRAY(\"homestays\"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));\n+------------------------------------------+\n| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |\n+------------------------------------------+\n| Version: 4.31                            |\n|                                          |\n| Load Mode: normal                        |\n| Load Policy: disable_unsupported_columns |\n| Output Mode: normal                      |\n|                                          |\n+------------------------------------------+\n6 rows in set (0.0124 sec)\n\n+------------------------------------------------------------------------+\n| OFFLOAD ANALYSIS                                                       |\n+------------------------------------------------------------------------+\n| Verifying input schemas: 1                                             |\n| User excluded items: 0                                                 |\n|                                                                        |\n| SCHEMA                       OFFLOADABLE    OFFLOADABLE     SUMMARY OF |\n| NAME                              TABLES        COLUMNS     ISSUES     |\n| ------                       -----------    -----------     ---------- |\n| `homestays`                            1              3                |\n|                                                                        |\n| Total offloadable schemas: 1                                           |\n|                                                                        |\n+------------------------------------------------------------------------+\n10 rows in set (0.0124 sec)\n\n+-----------------------------------------------------------------------------------------------------------------------------+\n| CAPACITY ESTIMATION                                                                                                         |\n+-----------------------------------------------------------------------------------------------------------------------------+\n| Default encoding for string columns: VARLEN (unless specified in the schema)                                                |\n| Estimating memory footprint for 1 schema(s)                                                                                 |\n|                                                                                                                             |\n|                                TOTAL       ESTIMATED       ESTIMATED       TOTAL     DICTIONARY      VARLEN       ESTIMATED |\n| SCHEMA                   OFFLOADABLE   HEATWAVE NODE      MYSQL NODE      STRING        ENCODED     ENCODED            LOAD |\n| NAME                          TABLES       FOOTPRINT       FOOTPRINT     COLUMNS        COLUMNS     COLUMNS            TIME |\n| ------                   -----------       ---------       ---------     -------     ----------     -------       --------- |\n| `homestays`                        1       77.61 MiB      256.00 KiB           0              0           0         58.00 s |\n|                                                                                                                             |\n| Sufficient MySQL host memory available to load all tables.                                                                  |\n| Sufficient HeatWave cluster memory available to load all tables.                                                            |\n|                                                                                                                             |\n+-----------------------------------------------------------------------------------------------------------------------------+\n12 rows in set (0.0124 sec)\n\n+---------------------------------------------------------------------------------------------------------------------------------------+\n| EXECUTING LOAD SCRIPT                                                                                                                 |\n+---------------------------------------------------------------------------------------------------------------------------------------+\n| HeatWave Load script generated                                                                                                        |\n|   Retrieve load script containing 3 generated DDL command(s) using the query below:                                                   |\n| Deprecation Notice: \"heatwave_load_report\" will be deprecated, please switch to \"heatwave_autopilot_report\"                           |\n|   SELECT log-&gt;&gt;\"$.sql\" AS \"Load Script\" FROM sys.heatwave_autopilot_report WHERE type = \"sql\" ORDER BY id;                            |\n|                                                                                                                                       |\n| Adjusting load parallelism dynamically per internal\/external table.                                                                   |\n| Using current parallelism of 4 thread(s) as maximum for internal tables.                                                              |\n|                                                                                                                                       |\n| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema                         |\n|                                                                                                                                       |\n| 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 |\n|                                                                                                                                       |\n| Proceeding to load 1 table(s) into HeatWave.                                                                                          |\n|                                                                                                                                       |\n| Applying changes will take approximately 57.79 s                                                                                      |\n|                                                                                                                                       |\n+---------------------------------------------------------------------------------------------------------------------------------------+\n16 rows in set (0.0124 sec)\n\n+----------------------------------------------------+\n| TABLE LOAD                                         |\n+----------------------------------------------------+\n| TABLE (1 of 1): `homestays`.`reviews_from_mysqlsh` |\n| Commands executed successfully: 3 of 3             |\n| Warnings encountered: 0                            |\n| Table load succeeded!                              |\n|   Total columns loaded: 3                          |\n|   Table loaded using 4 thread(s)                   |\n|   Elapsed time: 7.92 s                             |\n|                                                    |\n+----------------------------------------------------+\n8 rows in set (0.0124 sec)\n\n+-------------------------------------------------------------------------------+\n| LOAD SUMMARY                                                                  |\n+-------------------------------------------------------------------------------+\n|                                                                               |\n| SCHEMA                          TABLES       TABLES      COLUMNS         LOAD |\n| NAME                            LOADED       FAILED       LOADED     DURATION |\n| ------                          ------       ------      -------     -------- |\n| `homestays`                          1            0            3       7.92 s |\n|                                                                               |\n| Total errors encountered: 0                                                   |\n| Total warnings encountered: 0                                                 |\n|                                                                               |\n+-------------------------------------------------------------------------------+\n9 rows in set (0.0124 sec)<\/code><\/pre>\n\n\n\n<p>Et voil\u00e0!<\/p>\n\n\n\n<p>The new table structure is:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt; \nSHOW CREATE TABLE reviews_from_mysqlsh\\G\n*************************** 1. row ***************************\n       Table: reviews_from_mysqlsh\nCreate Table: CREATE TABLE `reviews_from_mysqlsh` (\n  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT \/*!80023 INVISIBLE *\/,\n  `listing_id` bigint unsigned NOT NULL,\n  `date` date DEFAULT NULL,\n  PRIMARY KEY (`my_row_id`)\n) ENGINE=InnoDB AUTO_INCREMENT=2068801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPID<\/code><\/pre>\n\n\n\n<p>Please note the <strong>SECONDARY_ENGINE=<a href=\"https:\/\/dev.mysql.com\/doc\/heatwave\/en\/mys-hw-metadata-queries-secondary-engine.html\" target=\"_blank\" rel=\"noopener\" title=\"Secondary Engine Definitions\">RAPID<\/a><\/strong> new clause.<\/p>\n\n\n\n<p>And you can still query your table according to your needs:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nSELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;\n+----------+\n| COUNT(*) |\n+----------+\n|  2068800 |\n+----------+\n1 row in set (0.1160 sec)<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nEXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\\G\n*************************** 1. row ***************************\nEXPLAIN: -&gt; Aggregate: count(0)  (cost=16.6e+6..16.6e+6 rows=1)\n    -&gt; Table scan on reviews_from_mysqlsh in secondary engine RAPID  (cost=0..0 rows=2.07e+6)\n\n1 row in set, 1 warning (0.0935 sec)\nNote (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed one<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">HeatWave&rsquo;s Auto Parallel Load<\/h2>\n\n\n\n<p><strong><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/releasenotes\/changes\/2bacd747-267d-49e9-a2db-e5d080d45df5\/index.htm\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave's Auto Parallel Load\">HeatWave&rsquo;s Auto Parallel Load<\/a><\/strong> 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 <strong>multiple threads<\/strong> across nodes in the HeatWave cluster, significantly speeding up the operation.<\/p>\n\n\n\n<p>And guess what? you already know the command, it is <strong><a href=\"https:\/\/dev.mysql.com\/doc\/heatwave\/en\/mys-hw-auto-parallel-load.html\" target=\"_blank\" rel=\"noopener\" title=\"Loading Data Using Auto Parallel Load\">sys.heatwave_load<\/a><\/strong>.<\/p>\n\n\n\n<p>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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"json\" class=\"language-json\">SET @input_list = '\n[\n    {\n        \"db_name\": \"homestays\",\n        \"tables\": [\n            {\n                \"table_name\": \"reviews_from_HW_load\",\n                \"engine_attribute\": {\n                    \"file\": [\n                        {\n                            \"uri\": \"oci:\/\/Airbnb@mynamespace\/reviews.csv\"\n                        }\n                    ],\n                    \"dialect\": {\n                        \"format\": \"csv\",\n                        \"field_delimiter\": \"auto\",  \n                        \"record_delimiter\": \"auto\",\n                        \"has_header\": true\n                    }\n                }\n            }\n        ]\n    }\n]';<\/code><\/pre>\n\n\n\n<p>You&rsquo;ll find the previously used configuration:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database name: <em>homestays<\/em><\/li>\n\n\n\n<li>Table name: <em>reviews_from_HW_load<\/em><\/li>\n\n\n\n<li>The file is find following the URI:\n<ul class=\"wp-block-list\">\n<li>Object storage bucket name: <em>Airbnb<\/em><\/li>\n\n\n\n<li>Object storage namespace: <em>mynamespace<\/em><\/li>\n\n\n\n<li>CSV file name: <em>reviews.csv<\/em><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>The dialect is <em>CSV<\/em>, so the only information HeatWave&rsquo;s Auto Parallel Load requires from us is the presence of a <em>header<\/em> in the file.<\/li>\n<\/ul>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>And like we have seen previously, run the stored procedure using the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/9.3\/en\/call.html\" target=\"_blank\" rel=\"noopener\" title=\"CALL Statement\">CALL<\/a> statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt; \nCALL sys.heatwave_load(CAST(@input_list AS JSON), NULL);\n+------------------------------------------+\n| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |\n+------------------------------------------+\n| Version: 4.31                            |\n|                                          |\n| Load Mode: normal                        |\n| Load Policy: disable_unsupported_columns |\n| Output Mode: normal                      |\n|                                          |\n+------------------------------------------+\n6 rows in set (0.0128 sec)\n\n+--------------------------------------------------------------------------------------------------------------------+\n| LAKEHOUSE AUTO SCHEMA INFERENCE                                                                                    |\n+--------------------------------------------------------------------------------------------------------------------+\n| Verifying external lakehouse tables: 1                                                                             |\n|                                                                                                                    |\n| SCHEMA                   TABLE                    TABLE IS           RAW     NUM. OF      ESTIMATED     SUMMARY OF |\n| NAME                     NAME                     CREATED      FILE SIZE     COLUMNS      ROW COUNT     ISSUES     |\n| ------                   -----                    --------     ---------     -------      ---------     ---------- |\n| `homestays`              `reviews_from_HW_load`   NO           45.10 MiB           2         2.07 M                |\n|                                                                                                                    |\n| New schemas to be created: 0                                                                                       |\n| External lakehouse tables to be created: 1                                                                         |\n|                                                                                                                    |\n+--------------------------------------------------------------------------------------------------------------------+\n10 rows in set (0.0128 sec)\n\n+------------------------------------------------------------------------+\n| OFFLOAD ANALYSIS                                                       |\n+------------------------------------------------------------------------+\n| Verifying input schemas: 1                                             |\n| User excluded items: 0                                                 |\n|                                                                        |\n| SCHEMA                       OFFLOADABLE    OFFLOADABLE     SUMMARY OF |\n| NAME                              TABLES        COLUMNS     ISSUES     |\n| ------                       -----------    -----------     ---------- |\n| `homestays`                            1              2                |\n|                                                                        |\n| Total offloadable schemas: 1                                           |\n|                                                                        |\n+------------------------------------------------------------------------+\n10 rows in set (0.0128 sec)\n\n+-----------------------------------------------------------------------------------------------------------------------------+\n| CAPACITY ESTIMATION                                                                                                         |\n+-----------------------------------------------------------------------------------------------------------------------------+\n| Default encoding for string columns: VARLEN (unless specified in the schema)                                                |\n| Estimating memory footprint for 1 schema(s)                                                                                 |\n|                                                                                                                             |\n|                                TOTAL       ESTIMATED       ESTIMATED       TOTAL     DICTIONARY      VARLEN       ESTIMATED |\n| SCHEMA                   OFFLOADABLE   HEATWAVE NODE      MYSQL NODE      STRING        ENCODED     ENCODED            LOAD |\n| NAME                          TABLES       FOOTPRINT       FOOTPRINT     COLUMNS        COLUMNS     COLUMNS            TIME |\n| ------                   -----------       ---------       ---------     -------     ----------     -------       --------- |\n| `homestays`                        1       57.57 MiB      192.00 KiB           0              0           0          7.00 s |\n|                                                                                                                             |\n| Sufficient MySQL host memory available to load all tables.                                                                  |\n| Sufficient HeatWave cluster memory available to load all tables.                                                            |\n|                                                                                                                             |\n+-----------------------------------------------------------------------------------------------------------------------------+\n12 rows in set (0.0128 sec)\n\n+---------------------------------------------------------------------------------------------------------------------------------------+\n| EXECUTING LOAD SCRIPT                                                                                                                 |\n+---------------------------------------------------------------------------------------------------------------------------------------+\n| HeatWave Load script generated                                                                                                        |\n|   Retrieve load script containing 2 generated DDL command(s) using the query below:                                                   |\n| Deprecation Notice: \"heatwave_load_report\" will be deprecated, please switch to \"heatwave_autopilot_report\"                           |\n|   SELECT log-&gt;&gt;\"$.sql\" AS \"Load Script\" FROM sys.heatwave_autopilot_report WHERE type = \"sql\" ORDER BY id;                            |\n|                                                                                                                                       |\n| Adjusting load parallelism dynamically per internal\/external table.                                                                   |\n| Using current parallelism of 4 thread(s) as maximum for internal tables.                                                              |\n|                                                                                                                                       |\n| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema                         |\n|                                                                                                                                       |\n| 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 |\n|                                                                                                                                       |\n| Proceeding to load 1 table(s) into HeatWave.                                                                                          |\n|                                                                                                                                       |\n| Applying changes will take approximately 7.01 s                                                                                       |\n|                                                                                                                                       |\n+---------------------------------------------------------------------------------------------------------------------------------------+\n16 rows in set (0.0128 sec)\n\n+----------------------------------------------------+\n| TABLE LOAD                                         |\n+----------------------------------------------------+\n| TABLE (1 of 1): `homestays`.`reviews_from_HW_load` |\n| Commands executed successfully: 2 of 2             |\n| Warnings encountered: 0                            |\n| Table load succeeded!                              |\n|   Total columns loaded: 2                          |\n|   Elapsed time: 30.95 s                            |\n|                                                    |\n+----------------------------------------------------+\n7 rows in set (0.0128 sec)\n\n+----------------------------------------------------------------------------------+\n| LOAD SUMMARY                                                                     |\n+----------------------------------------------------------------------------------+\n|                                                                                  |\n| SCHEMA                          TABLES       TABLES      COLUMNS         LOAD    |\n| NAME                            LOADED       FAILED       LOADED     DURATION    |\n| ------                          ------       ------      -------     --------    |\n| `homestays`                          1            0            2      30.95 s    |\n|                                                                                  |\n| Total errors encountered: 0                                                      |\n| Total warnings encountered: 2                                                    |\n| Retrieve the associated logs from the report table using the query below:        |\n|   SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |\n|                                                                                  |\n+----------------------------------------------------------------------------------+\n11 rows in set (0.0128 sec)<\/code><\/pre>\n\n\n\n<p>The operation completed successfully; however, two warnings were generated. <br>Details regarding these warnings are available in the <em>sys.heatwave_autopilot_report<\/em> table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL &gt; \nSELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn')\\G\n*************************** 1. row ***************************\nlog: {\"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}\n*************************** 2. row ***************************\nlog: {\"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}<\/code><\/pre>\n\n\n\n<p>Fortunately, nothing critical.<\/p>\n\n\n\n<p>And now you can query your table according to your needs:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nSELECT COUNT(*) FROM reviews_from_HW_load;\n+----------+\n| COUNT(*) |\n+----------+\n|  2068800 |\n+----------+\n1 row in set (0.1147 sec)<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SQL&gt;\nEXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_HW_load\\G\n*************************** 1. row ***************************\nEXPLAIN: -&gt; Aggregate: count(0)  (cost=16.6e+6..16.6e+6 rows=1)\n    -&gt; Table scan on reviews_from_HW_load in secondary engine RAPID  (cost=0..0 rows=2.07e+6)\n\n1 row in set, 1 warning (0.0933 sec)\nNote (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed on<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Peroration<\/h2>\n\n\n\n<p>Whether you&rsquo;re working with traditional <strong>MySQL<\/strong> or taking advantage of the blazing-fast analytics capabilities of <strong>HeatWave<\/strong>, importing CSV data doesn&rsquo;t have to be a bottleneck. With tools like <strong>MySQL Shell\u2019s parallel import utility<\/strong> and <strong>HeatWave\u2019s Auto Parallel Load<\/strong>, you have flexible, scalable options that fit a variety of use cases \u2014 from local file loading to seamless integration with object storage.<\/p>\n\n\n\n<p>By combining these tools with features like <strong>Generated Invisible Primary Keys<\/strong>, you can streamline the ingestion process and get your data ready for powerful, real-time analytics with minimal overhead.<\/p>\n\n\n\n<p>So next time you&rsquo;re staring at a CSV file and a big dataset to analyze, you&rsquo;re fully equipped to handle it \u2014 <em>the MySQL way<\/em>.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Stay tuned for more insights!<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p><a href=\"https:\/\/www.linkedin.com\/groups\/12524512\/\" target=\"_blank\" rel=\"noopener\" title=\"Olivier DASINI on Linkedin\">Follow me on Linkedin<\/a><\/p>\n\n\n\n<p>Watch my videos on my <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier's MySQL Channel\">YouTube channel<\/a> and <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\/?sub_confirmation=1\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Subscribe\">subscribe<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/www.slideshare.net\/freshdaz\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Slideshare\">Slideshare account<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/speakerdeck.com\/freshdaz\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Speaker Deck\">Speaker Deck account<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using HeatWave &amp; MySQL!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Efficiently loading CSV data into your MySQL environment is a crucial step for many analytical workloads, especially when dealing with the substantial datasets common in today&rsquo;s data-driven world. Both MySQL Shell&rsquo;s parallel import utility and HeatWave&rsquo;s Auto Parallel Load feature offer robust and effective solutions, each catering to slightly different needs and environments.<\/p>\n<p>MySQL Shell&rsquo;s util.importTable provides a flexible and powerful way to import data from various sources, including local disks and OCI Object Storage, into your standard MySQL tables. It&rsquo;s a developer-friendly tool that offers granular control over the import process.<\/p>\n<p>For those leveraging the analytical power of MySQL HeatWave, the sys.heatwave_load stored procedure, particularly with its Auto Parallel Load capabilities, simplifies the often complex task of getting data into the in-memory columnar store.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[1713,1694,1719,203],"tags":[1697,202],"class_list":["post-7822","post","type-post","status-publish","format-standard","hentry","category-cloud-en","category-heatwave-en","category-mds-en","category-mysql-en","tag-heatwave-fr-en","tag-performance"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-22a","jetpack-related-posts":[{"id":4462,"url":"https:\/\/dasini.net\/blog\/2021\/04\/13\/heatwave-a-mysql-cloud-feature-to-speed-up-your-queries\/","url_meta":{"origin":7822,"position":0},"title":"HeatWave &#8211; A MySQL cloud feature to speed up your queries","author":"Olivier DASINI","date":"13 avril 2021","format":false,"excerpt":"If you have (too) long running select queries it is probably because of lack of relevant indexes, problematic schema that lead to poor queries or inadequate hardware. That said, sometime even if you doing it right, the query execution time could be too long regarding of what the application or\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":6650,"url":"https:\/\/dasini.net\/blog\/2024\/07\/16\/get-your-heatwave-mysql-data-on-premises-with-replication\/","url_meta":{"origin":7822,"position":1},"title":"Get Your HeatWave MySQL Data On-Premises with Replication","author":"Olivier DASINI","date":"16 juillet 2024","format":false,"excerpt":"This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer.","rel":"","context":"Dans &quot;HeatWave&quot;","block_context":{"text":"HeatWave","link":"https:\/\/dasini.net\/blog\/category\/heatwave-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":5762,"url":"https:\/\/dasini.net\/blog\/2022\/06\/09\/explore-visualize-your-mysql-heatwave-data-with-superset\/","url_meta":{"origin":7822,"position":2},"title":"Explore &#038; visualize your MySQL HeatWave data with Superset","author":"Olivier DASINI","date":"9 juin 2022","format":false,"excerpt":"In this article I will show you how to properly configure Apache Superset in order to take advantage of a high performance, in-memory query accelerator: MySQL HeatWave.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=700%2C400 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=1050%2C600 3x"},"classes":[]},{"id":5915,"url":"https:\/\/dasini.net\/blog\/2022\/07\/05\/interactively-explore-visualize-your-mysql-heatwave-data-with-apache-zeppelin\/","url_meta":{"origin":7822,"position":3},"title":"Interactively explore &#038; visualize your MySQL HeatWave data with Apache Zeppelin","author":"Olivier DASINI","date":"5 juillet 2022","format":false,"excerpt":"In this article I will show you how to properly configure Apache Zeppelin in order to take advantage of the Analytics and Machine Learning capabilities of MySQL HeatWave the MySQL in-memory query accelerator.","rel":"","context":"Dans &quot;Machine Learning&quot;","block_context":{"text":"Machine Learning","link":"https:\/\/dasini.net\/blog\/category\/machine-learning-en\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=700%2C400 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/Configure-Heatwave-Cluster.png?resize=1050%2C600 3x"},"classes":[]},{"id":6061,"url":"https:\/\/dasini.net\/blog\/2022\/07\/18\/iris-data-set-with-mysql-heatwave-machine-learning-zeppelin\/","url_meta":{"origin":7822,"position":4},"title":"Iris Data Set with MySQL HeatWave Machine Learning  &#038; Zeppelin","author":"Olivier DASINI","date":"18 juillet 2022","format":false,"excerpt":"During the last 20 years MySQL has democratized the usage of transactional databases. Now with MySQL HeatWave we are in the process of democratizing Analytics and Machine Learning. With MySQL HeatWave, valorizing your data has never been so easy! In this article I will show you how easy it is\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"MySQL HeatWave a MySQL in-Memory Query Accelerator","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/HW_ML.png?fit=1012%2C431&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/HW_ML.png?fit=1012%2C431&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/HW_ML.png?fit=1012%2C431&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/06\/HW_ML.png?fit=1012%2C431&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":4151,"url":"https:\/\/dasini.net\/blog\/2020\/12\/17\/mysql-shell-the-new-era\/","url_meta":{"origin":7822,"position":5},"title":"MySQL SHELL &#8211; The new era","author":"Anastasia Papachristopoulou","date":"17 d\u00e9cembre 2020","format":false,"excerpt":"In one of our previous articles - Setting up Replication with various methods for MySQL 8 - we reviewed how to create a replica with multiple tools. Now, it is time to perform the same action but with MySQL Shell.","rel":"","context":"Dans &quot;Backup \/ Restore&quot;","block_context":{"text":"Backup \/ Restore","link":"https:\/\/dasini.net\/blog\/category\/backup-restore\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/7822","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=7822"}],"version-history":[{"count":104,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/7822\/revisions"}],"predecessor-version":[{"id":7927,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/7822\/revisions\/7927"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=7822"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=7822"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=7822"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}