Created
January 19, 2024 17:07
-
-
Save hfleitas/587d5ae3ddff58196d86c089e540bc32 to your computer and use it in GitHub Desktop.
ADB1.kql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#connect cluster('mypersonalcluster').database('mydb') | |
// ingested blob by cloning repo & used oneclick | |
// this is a sample from git url | |
.drop table stores ifexists; | |
.create table stores (id:string, name:string, email:string, city:string, hq_address:string, phone_number:string) | |
.create table stores ingestion csv mapping 'stores_mapping' '[{"column":"id", "Properties":{"Ordinal":"0"}},{"column":"name", "Properties":{"Ordinal":"1"}},{"column":"email", "Properties":{"Ordinal":"2"}},{"column":"city", "Properties":{"Ordinal":"3"}},{"column":"hq_address", "Properties":{"Ordinal":"4"}},{"column":"phone_number", "Properties":{"Ordinal":"5"}}]' | |
.ingest //async | |
into table stores | |
(h'https://raw.githubusercontent.com/hfleitas/apjbootcamp2022/main/Datasets/dimensions/stores.csv') | |
with ( | |
format='csv', | |
ingestionMappingReference='stores_mapping', | |
ingestionMappingType='csv', | |
ignoreFirstRecord=true | |
); | |
stores | count; | |
//via datatable inline | |
.drop table stores ifexists; | |
.set stores <| datatable(id:string, name:string, email:string, city:string, hq_address:string, phone_number:string)[ | |
'BNE02','Brisbane Airport','castillojoseph@example.net','Brisbane',"6 Ware Copse Doughertystad, NSW, 2687",'0425.061.371', | |
'PER01','Perth CBD','yhernandez@example.com','Perth',"Level 2 95 Jorge Vale St. Gary, NT, 2705",'08-9854-6006', | |
'CBR01','Canberra Airport','feliciasalas@example.com','Canberra',"5 Julie Reach Lake Aaron, TAS, 2955",'0495-403-281', | |
'MEL01','Melbourne CBD','lori00@example.net','Melbourne',"3 Tiffany Round Stonehaven, WA, 2687",'(02) 6391 6842', | |
'MEL02','Melbourne Airport','moralesbrandon@example.com','Melbourne',"57 Bryan Circuit New Jason, ACT, 2968",'0777304662', | |
'AKL01','Auckland Airport','nfuller@example.com','Auckland',"976 Reeves Street Webbland 2011",'666 5759', | |
'AKL02','Auckland CBD','jeremysmith@example.com','Auckland',"85 Webb Street MacDonaldmouth 5504",'+64273484326', | |
'WLG01','Wellington CBD','jonathan47@example.net','Wellington',"7 Rowland Reserve RD 2 Arawaka 1086",'(04) 4059380', | |
'SYD01','Sydney CBD','wardchristopher@example.com','Sydney',"Flat 95 2 Ernest Laneway Alyssaburgh, WA, 2675",'44428748', | |
]; | |
stores | |
// next add column | |
// set values of that column using case statemet | |
// rls due to pii | |
// query as of version | |
// soft-clone / snapshot table / deep-clone... | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment