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;
+------------------+----------------------------------------------------------------------------------------------------------+-----+
| 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
