HeatWave External Table (Lakehouse) with Generated Column in 9.6.1

This is to share a tutorial how we can define a External Table in HeatWave 9.6.1+ for JSON data with Generated Column(s).

Pre-requisite

HeatWave : 9.6.1+

Lakehouse : Enabled with the HeatWave Cluster

Lakehouse in HeatWave is a feature to allow files with different formats to be loaded to HeatWave.  

Together with Event Based Incremental Loading feature with HeatWave Lakehouse, adding, changing or deleting files can be automated to update the HeatWave table.

Syntax :

CREATE EXTERNAL TABLE table_name (col_name data_type [ENGINE_ATTRIBUTE='Col_Engine_Attribute_JSON'], ...) ENGINE_ATTRIBUTE='Tbl_Engine_Attribute_JSON' [PARTITION BY KEY (partition_column_name)];

For JSON format : here is an example and sample output

JSON file :  demo.json 

{"a":1,"b":2}

{"a":3,"b":4}

SQL :

> create database lh;

> use lh;

> create table json_table ( col_1 json, _metadata_filename varchar(1024), mya int generated always as (col_1->"$.a") stored, myb int generated always as (col_1->"$.b") stored ) engine_attribute='{"dialect":{"format":"json"}, "file": [ {"uri":"oci://<bucket>@<namespace>/autorefresh/demo.json"}]}' engine=lakehouse  secondary_engine=rapid;

> alter table json_table secondary_load;

> select * from json_table;

+------------------+----------------------------------------------------------------------------------------------------------+-----+

| col_1            | _metadata_filename                                                                                       | mya |

+------------------+----------------------------------------------------------------------------------------------------------+-----+

| {"a": 1, "b": 2} | https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/autorefresh/demo.json |   1 |

| {"a": 3, "b": 4} | https://objectstorage.uk-london-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/autorefresh/demo.json |   3 |

+------------------+----------------------------------------------------------------------------------------------------------+-----+

Reference :

https://dev.mysql.com/doc/relnotes/heatwave/en/news-9-6-1.html

https://dev.mysql.com/doc/heatwave/en/mys-hw-lakehouse-key-partitioning.html