Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Build ETL from device to cloud: https://h3xagn.com
//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' )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment