Build ETL from device to cloud: https://h3xagn.com

build-etl-part6-ext-table.py

//create dimension table the Site and Equipment
.create table meta_site_eqmt (Site: string, EqmtIp: string, ProductType: string, EqmtId: string, Active: bool)

//create dimension table the System and Tag
.create table meta_system_tag (System: string, Tag: string, EngUnit: string, Min: int, Max: int)

//create link to external table for exporting: factOilData
.create external table factOilData (TimeStamp: datetime, TagName: string, Value: real, Site: string, System: string, EqmtIp: string, Tag: string) 
    kind=blob 
    partition by (HH: datetime = bin(TimeStamp, 1h))
    pathformat = ("demosite/oil/" datetime_pattern("yyyy\'/\'MM\'/\'dd\'/\'HH", TimeStamp))
    dataformat=parquet 
    ( h@'https://<name>.blob.core.windows.net/dfcuploads;StorageAccountKey' )

//create link to external table for exporting: dimSiteEqmt
.create external table dimSiteEqmt (Site: string, EqmtIp: string, ProductType: string, EqmtId: string, Active: bool) 
    kind=blob 
    dataformat=parquet 
    ( h@'https://<name>.blob.core.windows.net/dfcuploads;StorageAccountKey' )

//create link to external table for exporting: dimSystemTag
.create external table dimSystemTag (System: string, Tag: string, EngUnit: string, Min: int, Max: int) 
    kind=blob 
    dataformat=parquet 
    ( h@'https://<name>.blob.core.windows.net/dfcuploads;StorageAccountKey' )