ADB1.kql

ADB1.kql

#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','[email protected]','Brisbane',"6 Ware Copse Doughertystad, NSW, 2687",'0425.061.371',
'PER01','Perth CBD','[email protected]','Perth',"Level 2 95 Jorge Vale St. Gary, NT, 2705",'08-9854-6006',
'CBR01','Canberra Airport','[email protected]','Canberra',"5 Julie Reach Lake Aaron, TAS, 2955",'0495-403-281',
'MEL01','Melbourne CBD','[email protected]','Melbourne',"3 Tiffany Round Stonehaven, WA, 2687",'(02) 6391 6842',
'MEL02','Melbourne Airport','[email protected]','Melbourne',"57 Bryan Circuit New Jason, ACT, 2968",'0777304662',
'AKL01','Auckland Airport','[email protected]','Auckland',"976 Reeves Street Webbland 2011",'666 5759',
'AKL02','Auckland CBD','[email protected]','Auckland',"85 Webb Street MacDonaldmouth 5504",'+64273484326',
'WLG01','Wellington CBD','[email protected]','Wellington',"7 Rowland Reserve RD 2 Arawaka 1086",'(04) 4059380',
'SYD01','Sydney CBD','[email protected]','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...