5.4.2 Editing Loaded Data Using ALTER TABLE and Engine Attribute

Update the loaded table data using ALTER TABLE command to edit the engine_attribute, or using Auto Parallel Load interface. The modified data is refereshed automatically. This option is available from MySQL 9.1.2.

While modifying the engine_attribute:

• Ensure that a complete and valid engine_attribute is always added. When ever you update the loaded data, ensure that the engine_attribute includes all data files that are required for the load.

• You can modify only file parameters present in the engine_attribute option.

• If there is a failure during data refresh, the engine_attribute will not be updated, and an error is reported.

mysql> ALTER TABLE table_name ENGINE_ATTRIBUTE = '{new engine attribute JSON}';

Example to create a table by name supplier_pq, and add a file supplier.parquet using ALTER TABLE, after loading the table.

mysql> CREATE TABLE `supplier_pq` (
  `s_suppkey` int NOT NULL,
  `s_name` char(25) DEFAULT NULL,
  `s_address` varchar(40) DEFAULT NULL,
  `s_nationkey` int DEFAULT NULL,
  `s_phone` char(15) DEFAULT NULL,
  `s_acctbal` double DEFAULT NULL,
  `s_comment` varchar(101) DEFAULT NULL,
  PRIMARY KEY (`s_suppkey`),
  KEY `i_s_nationkey` (`s_nationkey`)
) ENGINE=Lakehouse 
SECONDARY_ENGINE=rapid 
ENGINE_ATTRIBUTE='{"file": [{"name": "src_data/parquet/tpch/supplier.parquet", "bucket": "myBucket", "region": "myRegion", "namespace": "myNamespace"}], "dialect": {"format": "parquet"}}';

mysql> ALTER TABLE `supplier_pq`
ENGINE_ATTRIBUTE='{"file": [{"name": "src_data/parquet/tpch/supplier.parquet", "bucket": "myBucket", "region": "myRegion", "namespace": "myNamespace"}, {"name": "src_data2/parquet/tpch/supplier.parquet", "bucket": "myBucket", "region": "myRegion", "namespace": "myNamespace"}], "dialect": {"format": "parquet"}}';

You can also alter the engine_attribute using Auto Parallel load interface.

• If the table is already created but does not have any engine attribute set, and if engine attribute is provided as input, then it is accepted and Auto Parallel Load will generate a new ALTER TABLE command to set the engine attribute for that table and then load it. This also works if the table is a created empty InnoDB table, and then changed to an external Lakehouse table.

• If the table is created with engine_attribute, but it is not yet loaded, then Auto Parallel Load accepts a newly passed engine_attribute and will generate a ALTER TABLE command to update the engine attribute accordingly.

• If the table with engine_attribute is already loaded, then Auto Parallel Load will allow the alteration ONLY if refresh_external_tables is enabled, else, an error is reported.

Example to update data using Auto Parallel Load:

mysql> SET @input_list = '[{
  "db_name": "tpch",
	"tables": [{
      "table_name": "supplier_pq",
      "engine_attribute": {
      "dialect": {"format": "parquet"},
      "file": [{
        "name": "src_data/parquet/tpch/supplier.parquet",
        "bucket": "myBucket",
        "namespace": "myNamespace",
        "region": "myRegion"
      }]
    }
  }]
}]';

-- Initial Load of the table
mysql> SET @options = JSON_OBJECT('mode', 'normal');
mysql> CALL sys.heatwave_load(@input_list, @options);

-- Changing the engine attribute to add another set of file
mysql> SET @input_list = '[{
  "db_name": "tpch",
	"tables": [{
      "table_name": "supplier_pq",
      "engine_attribute": {
      "dialect": {"format": "parquet"},
      "file": [{
        "name": "src_data/parquet/tpch/supplier.parquet",
        "bucket": "myBucket",
        "namespace": "myNamespace",
        "region": "myRegion"
      },
			{
        "name": "src_data2/parquet/tpch/supplier.parquet",
        "bucket": "myBucket",
        "namespace": "myNamespace",
        "region": "myRegion"
      }]
    }
  }]
}]';

mysql> SET @options = JSON_OBJECT('mode', 'normal', 'refresh_external_tables', TRUE);
mysql> CALL sys.heatwave_load(@input_list, @options);