Add Snowflake External Table with Virtual Column support a kind of Computed Column as T-SQL
dmaresma opened this issue · 0 comments
dmaresma commented
I would like to support CREATE EXTERNAL TABLE statement support as the following test
here the reference in Snowflake documentation
https://docs.snowflake.com/en/sql-reference/sql/create-external-table
def test_virtual_column_table():
ddl = """
create or replace external table if not exists TABLE_DATA_SRC.EXT_PAYLOAD_MANIFEST_WEB(
"type" VARCHAR(255) AS (SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 1), '=', 2)),
"year" VARCHAR(255) AS (SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 2), '=', 2)),
"month" VARCHAR(255) AS (SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 3), '=', 2)),
"day" VARCHAR(255) AS (SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 4), '=', 2)),
"path" VARCHAR(255) AS (METADATA$FILENAME))
partition by ("type", "year", "month", "day", "path")
location=@ADL_Azure_Storage_Account_Container_Name/
auto_refresh=false
file_format=(TYPE=JSON NULL_IF=())
;
"""
result_ext_table = DDLParser(ddl, normalize_names=True, debug=True).run(
output_mode="snowflake"
)
expected_ext_table = [
{
"alter": {},
"checks": [],
"clone": None,
"columns": [
{
"name": "type",
"type": "VARCHAR",
"size": 255,
"comment": "",
"references": None,
"unique": False,
"nullable": True,
"default": None,
"check": None,
"generated" : {"as" : "SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 1), '=', 2)" , "stored": False, "always" : True}
},
{
"name": "year",
"type": "VARCHAR",
"size": 255,
"comment": "",
"references": None,
"unique": False,
"nullable": True,
"default": None,
"check": None,
"generated" : {"as" : "SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 2), '=', 2)" , "stored": False, "always" : True}
},
{
"name": "month",
"type": "VARCHAR",
"size": 255,
"comment": "",
"references": None,
"unique": False,
"nullable": True,
"default": None,
"check": None,
"generated" : {"as" : "SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 3), '=', 2)", "stored": False, "always" : True}
},
{
"name": "day",
"type": "VARCHAR",
"size": 255,
"comment": "",
"references": None,
"unique": False,
"nullable": True,
"default": None,
"check": None,
"generated" : {"as" : "SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 4), '=', 2)", "stored": False, "always" : True}
},
{
"name": "path",
"type": "VARCHAR",
"size": 255,
"comment": "",
"references": None,
"unique": False,
"nullable": True,
"default": None,
"check": None,
"generated" : {"as" : "METADATA$FILENAME)", "stored": False, "always" : True}
}
],
"constraints": {
"primary_keys": []
},
"index": [],
"partitioned_by": []
"partition_by" : ["type", "year", "month", "day", "path"],
"primary_key": [],
"primary_key_enforced": None,
"location" : "@ADL_Azure_Storage_Account_Container_Name/",
"auto_refresh" : False,
"schema": "TABLE_DATA_SRC",
"table_name": "EXT_PAYLOAD_MANIFEST_WEB",
}
]
assert result_ext_table == expected_ext_table
Implementing Virutal Column
"type" VARCHAR(255) AS (SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', 1), '=', 2))
improve generated as
columns attribute, location
, file_format
and auto_refresh
as table attributes
check partitioned_by still supported
Additional context
ICEBERG Table is comming soon 👍