1.3.1 Snowflake環境のセットアップ
1.3.1.1 アカウントの作成
https://snowflake.com に移動します。 無料で開始 をクリックします。
詳細を入力し、「続行」をクリックします。
詳細を入力し、クラウドプロバイダーを選択して、「開始」をクリックします。
詳細を入力するか、「スキップ （x2）」をクリックします。
その後、これが表示されます。 メールを確認し、送信された確認メールをクリックします。
確認メールに記載されているリンクをクリックして、アカウントを有効にし、ユーザー名とパスワードを定義します。 「使ってみる」クリックします。次の演習では、このユーザー名とパスワードを使用する必要があります。
その後、Snowflakeにログインします。 今はスキップ をクリックします。
1.3.1.2 データベースの作成
データ/データベース に移動します。 「+ データベース」をクリックします。
データベースに CITISIGNAL という名前を使用します。 作成 をクリックします。
1.3.1.3 テーブルの作成
これで、Snowflakeでテーブルの作成を開始できます。 以下にスクリプトを示します。このスクリプトを実行して、テーブルを作成します。
テーブル --aepUserLdap--_PERSONS
「+作成」をクリックし、「テーブル」をクリックして、「標準」をクリックします。
その後、これが表示されます。 以下のクエリをコピーして、Snowflakeに貼り付けます。 テーブルを作成する前に、画面の左上隅にある CITIGNAL データベースを選択してください。
create or replace TABLE CITISIGNAL.PUBLIC.--aepUserLdap--_PERSONS (
PERSON_ID NUMBER(38,0) NOT NULL,
NAME VARCHAR(255),
AGE NUMBER(38,0),
EMAIL VARCHAR(255),
PHONE_NUMBER VARCHAR(20),
GENDER VARCHAR(10),
OCCUPATION VARCHAR(100),
ISMOBILESUB BOOLEAN,
primary key (PERSON_ID)
);
テーブルを作成 をクリックします。
スクリプトが実行されると、データベース/CITIGNAL/PUBLIC の下にテーブルが表示されます。
テーブル --aepUserLdap--_HOUSEHOLDS
「+作成」をクリックし、「テーブル」をクリックして、「標準」をクリックします。
その後、これが表示されます。 以下のクエリをコピーして、Snowflakeに貼り付けます。 テーブルを作成する前に、画面の左上隅にある CITIGNAL データベースを選択してください。
create or replace TABLE CITISIGNAL.PUBLIC.--aepUserLdap--_HOUSEHOLDS (
HOUSEHOLD_ID NUMBER(38,0) NOT NULL,
ADDRESS VARCHAR(255),
CITY VARCHAR(100),
STATE VARCHAR(50),
POSTAL_CODE VARCHAR(20),
COUNTRY VARCHAR(100),
ISELIGIBLEFORFIBER BOOLEAN,
PRIMARY_PERSON_ID NUMBER(38,0),
ISFIBREENABLED BOOLEAN,
primary key (HOUSEHOLD_ID)
);
テーブルを作成 をクリックします。
スクリプトが実行されると、データベース/CITIGNAL/PUBLIC の下にテーブルが表示されます。
テーブル --aepUserLdap--_USERS
「+作成」をクリックし、「テーブル」をクリックして、「標準」をクリックします。
その後、これが表示されます。 以下のクエリをコピーして、Snowflakeに貼り付けます。 テーブルを作成する前に、画面の左上隅にある CITIGNAL データベースを選択してください。
create or replace TABLE CITISIGNAL.PUBLIC.--aepUserLdap--_USERS (
USER_ID NUMBER(38,0) NOT NULL,
PERSON_ID NUMBER(38,0),
HOUSEHOLD_ID NUMBER(38,0),
primary key (USER_ID),
foreign key (PERSON_ID) references CITISIGNAL.PUBLIC.--aepUserLdap--_PERSONS(PERSON_ID),
foreign key (HOUSEHOLD_ID) references CITISIGNAL.PUBLIC.--aepUserLdap--_HOUSEHOLDS(HOUSEHOLD_ID)
);
テーブルを作成 をクリックします。
スクリプトが実行されると、データベース/CITIGNAL/PUBLIC の下にテーブルが表示されます。
テーブル --aepUserLdap--_MONTHLY_DATA_USAGE
「+作成」をクリックし、「テーブル」をクリックして、「標準」をクリックします。
その後、これが表示されます。 以下のクエリをコピーして、Snowflakeに貼り付けます。 テーブルを作成する前に、画面の左上隅にある CITIGNAL データベースを選択してください。
create or replace TABLE CITISIGNAL.PUBLIC.--aepUserLdap--_MONTHLY_DATA_USAGE (
USAGE_ID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 noorder,
USER_ID NUMBER(38,0),
MONTH DATE,
DATA_USAGE_GB NUMBER(10,2),
primary key (USAGE_ID)
);
テーブルを作成 をクリックします。
スクリプトが実行されると、データベース/CITIGNAL/PUBLIC の下にテーブルが表示されます。
テーブル --aepUserLdap--_MOBILE_DATA_USAGE
「+作成」をクリックし、「テーブル」をクリックして、「標準」をクリックします。
その後、これが表示されます。 以下のクエリをコピーして、Snowflakeに貼り付けます。 テーブルを作成する前に、画面の左上隅にある CITIGNAL データベースを選択してください。
create or replace TABLE CITISIGNAL.PUBLIC.--aepUserLdap--_MOBILE_DATA_USAGE (
USAGE_ID NUMBER(38,0) NOT NULL autoincrement start 1 increment 1 noorder,
USER_ID NUMBER(38,0),
DATE DATE,
TIME TIME(9),
APP_NAME VARCHAR(255),
DATA_USAGE_MB NUMBER(10,2),
NETWORK_TYPE VARCHAR(50),
DEVICE_TYPE VARCHAR(50),
COUNTRY_CODE VARCHAR(10),
primary key (USAGE_ID)
);
テーブルを作成 をクリックします。
スクリプトが実行されると、データベース/CITIGNAL/PUBLIC の下にテーブルが表示されます。
これで、すべてのテーブルが作成されました。
1.3.1.4 サンプルデータの取り込み
これで、データベースへのサンプルデータの読み込みを開始できます。
「+作成」をクリックし 「SQL ワークシート」を選択します。
すると、次のようになります。
以下のクエリをコピーして、Snowflakeのワークシートに貼り付けます。
USE SCHEMA CITISIGNAL.PUBLIC;
/*
- HELPER FUNCTIONS
Simplifies SQL queries to fetch random/calculated data.
*/
CREATE OR REPLACE FUNCTION --aepUserLdap--_RANDOM_NAME(NAME_TYPE STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
var FIRST_NAMES = ['Colly','Aubert','Berkeley','Debra','Caressa','Van','Gerome','Romonda','Clywd','Noemi','Heather','Benji','Gwenore','Robinson','Kathye','Roanne','Ainsley','Far','Chrissy','Adelina','Joseito','Shelbi','Stafani','Tabatha','Beverie','Constancia','Shalna','Ryan','Den','Iorgos','Monique','Becka','Justinian','Raimondo','Had','Noami','Karie','Rubin','Culver','Claudine','Petronilla','Frayda','Ketty','Rafa','Marlane','Jimmy','Ricoriki','Alfie','Velma','Dame','Ham','Westley','Ad','Nathan','Kris','Reba','Kort','Owen','Reynard','Gabie','Sandy','Evangelin','Sidonnie','Garrek','Uta','Morganica','Fannie','Glynnis','Denyse','Sharyl','Bard','Gillian','Martguerita','Catrina','Kameko','Flo','Gregory','Neille','Ninnette','Pamella','Riccardo','Georgi','Martainn','Dona','Frasco','Cordelie','Chloette','Rosette','Brena','Avictor','Webster','Elianora','Meridith','Jacky','Jena','Loise','Charmine','Adela','Letisha','Lyell','Tyrus','Eugenio','Bancroft','Quintus','Estelle','Bourke','Dick','Emmaline','Base','Tarrance','Baryram','Roldan','Holt','Silvia','Bonny','Fidelia','Gregor','Fran','Lloyd','Ann','Granny','Hali','Gusella','Misti','Cristobal','Seward','Myrwyn','Nani','Jaclin','Dorri','Denney','Ole','Maxy','Lorrin','Huey','Celle','Nixie','Rollins','Ethan','Staffard','Ynez','Galen','Gradeigh','Quinn','Liesa','Gustave','Janaya','Winthrop','Leonard','Matti','Pavel','Cyrillus','Jecho','Nicola','Misha','Locke','Ethyl','Caterina','Charles','Lewes','Guntar','Raoul','Gallagher','Melamie','Corney','Matty','Jocko','Faina','Sherlocke','Dermot','Bret','Elwood','Tootsie','Alissa','Arabela','Carolin','Christiane','Patty','Konstantin','Perri','Orrin','Graehme','Tallie','Kaiser','Camila','Ailyn','Myranda','Bertrand','Danna','Jeramie','Jillane','Tadeas','Kiel','Corey','Tedda','Jason','Bailey','Vinny','Rafael','Damon','Binky','Etan','Mike','Othella','Karissa','Adler','Gusti','Allix','Moira','Merrie','Elane','Krystle','Shep','Glyn','Yance','Anna-diane','Janene','Konstantine','Luciano','Beltran','Jamil','Shermie','Lazarus','Brewster','Brodie','Fidel','Quill','Dimitry','Michel','Daven','Carlin','Monroe','Cicily','Danny','Bentlee','Berta','Nari','Odo','Carleton','Vivianne','Lita','Carmencita','Sharia','Kaile','Brnaba','Corinna','Juan','Immanuel','Helen-elizabeth','Jacklin','Jefferey','Malanie','Skipton','Farr','Travus','Penny','Miles','Victoir','Darryl','Sheeree','Gabriel','Gilbert','Abba','Keefe','Rozalie','Amos','Tamqrah','Raddy','Allene','Boyce','Delano','Avigdor','Robenia','Rafi','Felicle','Cart','Bendick','Tremayne','Arlee','Jennette','Ty','Gare','Cornelle','Rand','Annissa','Lyn','Zebedee','Garek','Colleen','Tiena','Stacee','Gil','Chance','Killy','Bobine','Kahlil','Mac','Rowland','Lorine','Leoine','Booth','Karalynn','Hortensia','Randolph','Leo','Boyd','Ulberto','Mariquilla','Giulietta','Elga','Louisette','Leeanne','Rafe','Esme','Maddalena','David','Randie','Ianthe','Mead','Carlotta','Hasty','Dagmar','Drucy','Kingsley','Madeleine','Jarad','Micah','Sylvester','Ingra','Johannes','Harold','Rania','Friederike','Brandy','Sheffield','Dov','Jerrold','Rosemary','Reade','Morissa','Niven','Ruperta','Dacia','Allegra','Floyd','Bill','Sheila','Faunie','Devland','Brice','Saunderson','Jodi','Lane','Ray','Bondon','Daria','Tommie','Nealon','Charlena','Ivy','Evin','Judon','Wallace','Burch','Novelia','Aleen','Farlee','Erika','Isidora','Jessa','Lydon','Cass','Tandie','Giana','Patrica','Zacharie','Boycie','Tana','Madlin','Tildi','Keriann','Maxim','Vikki','Starlene','Sarine','Bennett','Mariette','Charmion','Binny','Shelton','Kristo','Micky','Bear','Garvin','Grady','Fay','Jane','Jeremie','Diandra','Branden','Franni','Leontine','Mendel','Noella','Leupold','Worthington','Melantha','Saul','Sunny','Marleen','Pryce','Lucy','Tabor','Anjela','Lulu','Juana','Wesley','Virginie','Candide','Sandye','Ronni','Ilyssa','Modesta','Dannie','Adena','Hildagarde','Rubetta','Joelynn','Car','Danyette','Kelsi','Loy','Kessiah','Karol','Evita','Neal','Jamey','Georgeta','Gino','Pammi','Jobina','Elston','Ayn','Price','Agnesse','Sarah','Lennie','Xenia','Jakie','Joellyn','North','Korney','Adrian','Zsazsa','Clarisse','Cad','Myrilla','Gill','Uri','Brannon','Meriel','Maryrose','Colet','Sheelah','Sande','Palmer','Ivar','Nomi','Corabelle','Warde','Morton','Homer','Eleanora','Kimmi','Ginelle','Rochella','Neilla','Amelita','Kevon','Risa','Karlyn','Garrott','Clovis','Foss','Cassius','Gerardo','Donall','Shawn','Jan','Gay','Angel','Datha','Domenic','Vonni','Mannie','Faye','Shane','Grove','Annabelle','Anita','Jeramey','Hurleigh','Ingelbert','Rhea','Chaddie','Tam','Lorry','Fedora','Ilsa','Jeannie','Geri','Lillian','Derrick','Rog','Rosella','Kirsten','Gonzales','Chuck','Ody','Dexter','Jerrylee','Jarrad','Wilmette','Amerigo','Vinnie','Urbano','Inigo','Rose','Teador','Burton','Bird','Bud','Jacki','Alberta','Felipe','Shadow','Judi','Rockey','Trueman','Freeland','Leela','Aurthur','Boot','Bucky','Anetta','Rhiamon','Lorna','Yule','Bary','Magdalene','Kalie','Robinett','Erasmus','Kellen','Reese','Jolie','Ruprecht','Sam','Shirlee','Jessie','Quinton','Shelagh','Blair','Debbi','Concordia','Bent','Brady','Carmen','Dionis','Skipp','De witt','Bastian','Napoleon','Cordie','Flor','Frannie','Carlye','Bondie','Anestassia','Andre','Mauricio','Bobbee','Hastings','Prudy','Willie','Geordie','Alphonso','Mamie','Casi','Sterne','Shelli','Hill','Jaymee','Harmonia','Brita','Charil','Zeb','Ekaterina','Ailis','Barde','Ellen','Hersch','Shae','Ronica','Gaultiero','Lissie','Alanah','Matt','Erie','Domenico','Free','Cly','Libbi','Mason','Terese','Helena','Brittan','Cyrille','Wells','Birk','Nessie','Kalila','Bel','Bartholomeo','Hinze','Faythe','Midge','Alaine','Wilmar','Sawyer','Karrah','Rabi','Jervis','Charla','Maryanne','Leola','Beckie','Thacher','Staci','Johnny','Melvyn','Carrie','Micki','Armin','Brooke','Valdemar','Halette','Malina','Haydon','Katusha','Ferrel','Sidnee','Shirleen','Forest','Magdalena','Lillis','Lock','Dominik','Vernen','Lib','Giordano','Jareb','Town','Doris','Chevalier','Arte','Smith','Carlee','Davine','Beaufort','Corbin','Janeva','Spencer','Melicent','Alfy','Emerson','Gerladina','Margo','Elyssa','Tyler','Dru','Care','Kirk','Lambert','Julietta','Brett','Lida','Cody','Kass','Dalenna','Maud','Garrick','Broderick','Darill','Maia','Reed','Lyndel','Tremaine','Angy','Eldredge','Mara','Brien','Nancey','Carmita','Siouxie','Winslow','Ezra','Robyn','Pearce','Hussein','Zacharia','Bernie','Darb','Truman','Felisha','Seth','Nikita','Milo','Rivi','Krisha','Adlai','Ursula','Korey','Valencia','Randene','Anya','Aimil','Zulema','Aundrea','Yoshiko','Adelaide','Berni','Nessa','Alys','Evyn','Fernanda','Linus','Eugine','Mareah','Maryl','Dolley','Debora','Sonny','Isac','Kerrill','Hershel','Berti','Fairfax','Guinna','Randee','Brantley','Mariann','Kaela','Lamond','Vasili','Chancey','Isidro','Amabel','Liane','Nikkie','Aldus','Alonso','Louis','Kimbell','Cami','Hanni','Morie','Ceil','Farrand','Romeo','Flint','Meaghan','Marquita','Lucienne','Terri','Rubie','Garnette','Fawn','Victor','Nikolaos','Aurore','Sly','Archy','Lazaro','Valerie','Prent','Mira','Rriocard','Felice','Hastie','Mel','Lenard','Renee','Paolina','Murdoch','Bellanca','Merlina','Clary','Izaak','Cherida','Uriel','Megen','Susette','Karney','Philipa','Karna','Charmain','Jennine','Jorrie','Gawain','Lishe','Darnell','Blake','Lina','Thorstein','Sydelle','Lind','Wandie','Pierrette','Johnath','Dianna','Wallis','Lemmie','Maddi','Ardra','Akim','Burg','Patsy','Alverta','Cristina','Chloris','Aindrea','Vi','Boigie','Harman','Stanfield','Hilario','Tova','Vida','Smitty','Arman','Ewell','Cos','Sibbie','Corri','Laurence','Marion','Doralynne','Dominique','Hailey','Rem','Doe','Cthrine','Dina','Nola','Pepito','Fredelia','Wileen','Thane','Christoffer','Uriah','Caldwell','Claudio','Lucho','Darby','Papagena','Hy','Lindsy','Gena','Natale','Dwight','Clea','Molly','Brandea','Gaby','Stesha','Etty','Margarethe','Grace','Melisande','Cathrin','Khalil','Jasmina','Niccolo','Maurise','Elana','Leonie','Chadd','Justina','Lindon','Ofilia','Cooper','Grantham','Georgetta','Jacobo','Cinderella','Julian','Monte','Sawyere','Saunders','Josi','Salvatore','Mylo','Missie','Jaquelyn','Orsola','Gareth','Denver','Bobby','Naoma','Gretal','Reid','Alexine','Rodrick','Rori','Rudyard','Norean','Bobinette','Lynett','Ronny','Theresina','Anabelle','Alana','Mahala','Joletta','Vonnie','Jordan','Adella','Ermentrude','Garrard','Helge','Raynor','Clevie','Sibby','Clementine','Michelina','Rosene','Peyton','Malchy','Rosalie','Rudie','Dominga','Berte','Phebe','Chrysa','Doroteya','Suzanna','Bren','Lionel','Clarke','Nils','Bridgette','Alysa','Abigail','Augustine','Zollie','Barnie','Veda','Row','Anet','Ryon','Cleopatra','Alejandro','Alfonse','Janos','Glynn','Andrei','Tannie','Latashia','Gertie','Daniele','Damien','Samson','Rockie','Theobald','Delainey','Itch','Charlie','Inglis','Florence','Kay','Fonz','Ezekiel','Max','Ruth','Nichols','Nellie','Reena','Candace','Beatrix','Templeton','Aloin','Mitchel','Noell','Lorinda','Alley','Gard','Sybila','Tressa','Nanny'];
var LAST_NAMES = ['Fayer','MacKereth','Boddington','Balassi','Trustrie','Casin','Spry','Dashkovich','Hopkyns','Tant','Birkenshaw','Grube','Ricoald','Gudd','Chesnut','Eilers','Cadwell','Frayn','Filpi','Skypp','Medhurst','Bidgod','Giovannelli','Annon','Benedicte','Killigrew','Oldfield','Donwell','Charter','Rainsden','Farrants','Boniface','Chillingworth','Langstone','Cubuzzi','Hubner','Peye','Spada','Dwelly','Krysztofiak','Brazener','Kingman','Ceyssen','Wheelwright','McGarry','Novakovic','Gietz','Blenkharn','Mesnard','Twiddy','McPaik','Wonter','Cabrara','Catterson','De Bruyn','Charke','Salan','Rayne','Pollington','Gascoyen','Giannazzi','Peggs','Bigly','Connow','Noddings','Tuminelli','Puleston','Meere','Palumbo','Veevers','Matyushkin','Austin','Winsom','Yacobsohn','Flawn','Stanlock','Rookledge','Sherewood','Penhall','Barcroft','Menat','Driffill','Egarr','Funnell','Motte','Hender','Aron','Hutten','Borris','McGeorge','Perillo','Burdon','Reina','Fillingham','Errol','Winger','Morlon','Isaacson','Senogles','Clewes','Lafee','Jasik','Kernoghan','Newbatt','Mariault','Walls','Jubert','Bunclark','Perryman','Brough','Aickin','Dumphries','Mauger','Dabinett','Donizeau','Oakwell','Coppo','Rodder','Grigoriev','Veitch','Campo','Stoddard','Ferruzzi','MacAfee','Grindle','Mussotti','Hebburn','Cornils','Thornton-Dewhirst','Spofforth','Alyonov','Harrema','Yarnton','Stinchcombe','Spafford','Smout','Schlagman','Lethibridge','MacGuigan','Fuke','Lohrensen','Leat','Hedgeman','Jachimiak','Lockey','Cuolahan','Aitcheson','Kelemen','Kneaphsey','Housaman','Maffezzoli','Reay','Haggleton','Bavester','Barratt','Albrecht','Pattillo','Surplice','Adams','Shone','Rigglesford','Pinxton','Hexham','Calafato','Hillin','O\'Lone','Buntain','Masey','Thunderchief','Derkes','Kenwood','Gleave','Garretson','Gorghetto','Studdeard','Malam','Haquin','Allsebrook','Bethell','Diver','Grancher','Whittaker','Balasin','Copcote','Bartelli','Selman','Matousek','De Vaan','Hoolaghan','Elcoat','Medforth','Mullineux','Parminter','Elliott','Sherland','Kivelhan','Toplin','Cobello','Helsdon','Burberye','Jeske','Petrowsky','Eslinger','Challens','Senyard','Bucklee','Vidloc','Goldup','Rousel','Pendleton','Husset','Tonbridge','Dufour','Lisamore','Sinfield','Southall','Penna','Yeliashev','Edsall','Jiruca','Cunnington','Rathe','Outhwaite','Pooke','Quinnelly','Roja','Krysztofowicz','Iglesia','Mawd','Sive','Whife','Wymer','Gravey','Befroy','Shitliffe','Pierro','Ginnane','McFayden','Creaven','Dutnall','Garfield','Bilton','Bramwell','Ram','Sheldrake','Pittendreigh','Sondon','Larret','Navein','Rames','Trobridge','Bakhrushkin','Leer','Garratt','Culshaw','Jayme','Croisdall','Cooling','Cavy','Grandisson','Minear','Spoward','Isakson','Normabell','Klimpt','Cheeke','Peeke-Vout','Lippitt','Wason','Beyer','MacIlhagga','Sterley','Arguile','Ingamells','Beininck','O\'Connell','Mulles','Owbrick','Jerzycowski','Ferreres','Cribbin','Bodill','Folke','McDonnell','Whittles','Gelletly','Merali','Karolczyk','Pes','Borsnall','Wakefield','Dulake','Risdale','Garvagh','Robiot','Klarzynski','O\'Brien','Cordeix','Keling','Camden','Bellay','Pimlett','Vodden','Merfin','Longland','Cometson','Menel','O\'Connor','Swallow','Challinor','Dalyell','Huscroft','Sherlock','Greneham','Tizard','Hetterich','Ethersey','Faragan','Lorkin','Mewis','Gwyneth','Mansion','Crook','Aloshkin','Vondrasek','Went','Vittery','Scarman','Tynan','Alessandrucci','Housam','Pluck','Clever','Ritchard','Dunham','Domnin','Bollon','Bavister','Sketcher','Conyers','Chaize','Fernanando','Minchell','Berni','Barrim','Arnaudi','Buesnel','Belcham','Poolman','Lovie','Dorset','Thaller','Dellenty','Sidwick','Gawne','Butteris','Sawl','Malley','Thomason','Poundsford','Sumner','Minchinton','Pietersen','Hrynczyk','Narrie','Palfreman','Blees','Galia','Fredi','Endricci','Beard','Penbarthy','Sparkes','Dilks','Piesing','Lerhinan','Cattroll','Durnell','Coppard','Littlecote','Grant','Dionisetti','Mager','Gynn','Ellett','Warrener','Ahrend','Strasse','Diben','Shalloe','Radwell','Shingles','Benton','Lutman','Heppenspall','Firpo','Marioneau','Rubenov','Seyler','Woodley','Dowbekin','Nason','Brownscombe','Gronou','Cluckie','Moreside','Hirsch','Raffon','Girardet','McDermid','Fenwick','Layfield','Dabernott','Doyle','Burborough','McLewd','Jevons','James','McCoish','Chiplin','Dalbey','Bodiam','Morforth','Rickaert','Taylour','Daglish','Teasell','Carlsson','Kennerley','Provost','Harome','Battram','Howels','Brunt','Abry','Wiltshier','Astill','Wither','Schwander','Kleinsmuntz','Clausner','Kroon','Pitcock','Lerven','Lorens','Jupe','Connechie','Dennington','Leehane','Dohmer','Townley','Michelet','Gorrick','Varney','Allin','Agget','Lown','Sterke','Chatain','Jurczik','Kemwal','Houldcroft','Le febre','Smerdon','Alderman','Gimber','Kilgrew','Berriball','Sparshatt','Plester','Bartoleyn','Kelmere','Doelle','Jaimez','Trodden','Sandiland','Treneer','Iannazzi','Danihel','Gallyon','Teasdale-Markie','Spinney','Colnet','Bugg','Cochran','Schimoni','Lakey','Nuscha','Cottingham','Arnaudin','Dory','Tejada','Plewman','Diego','Swarbrick','Wilse','Axtonne','Armytage','Kinzel','Tomankiewicz','Schanke','Chitson','Lange','Totaro','Clausen-Thue','Barham','Aikin','McIlmorie','Sains','Burgan','Nanelli','Niblock','Matthewson','Dovidaitis','Claremont','Frostdicke','Ilyunin','Wright','Padbery','Lightwing','Warden','Higgonet','Worsnup','Gergolet','Sellack','Standell','Iddens','Jeandeau','Renforth','Tanti','Henzley','Goby','Cornels','Hazeldean','Pringell','Holwell','Picken','Sima','Puddicombe','d\'Elboux','Muscott','Seville','MacAughtrie','Merton','Empleton','Dadd','Lawrenz','Vaneschi','Witheford','Walworth','Dodell','Kopke','Lauritzen','Rudwell','Klyner','Franceschielli','Gilbank','Birkbeck','Rivallant','Sweeney','Thurston','Strethill','Franklyn','Adamovicz','Glaum','Brockman','Basso','Kimmings','Rockcliffe','Alphege','Greensall','Canario','Brickett','Eacle','Woller','Weond','McKeaveney','Portlock','Piggott','Davidge','Schrieves','Reubbens','Woodrup','McGowan','Romme','Bohlens','Standfield','Poacher','Mateiko','Bezzant','Copin','male','Cereceres','Senn','Ganter','Allberry','Ife','Uvedale','Pretsell','Maryman','Folcarelli','McAuliffe','Wansbury','Glasby','Ingle','Culham','Line','Cristol','Stronghill','Medlin','Rowledge','Dowdeswell','Conneely','Halhead','Vivien','Winstone','Guitt','Walbridge','Escala','Anton','Bernardoux','MacGrath','Fieldstone','Hearl','Roels','Gallandre','Bauldrey','Keningham','Blything','Fontin','O\'Shea','Escreet','Kennelly','Cushion','Randalston','Ravilus','Worvell','Gulliver','Micklewright','Voas','Grimmert','Ashton','Horsewood','Scrivens','Gammel','Casado','Juniper','Shacklady','Hacard','Duddell','Stitwell','Kelle','Richemont','Mendes','Gerauld','Bowcher','Fischer','Snare','Grayham','Selvester','Babbe','Coalburn','Denzey','Featherstone','Carillo','Kinvan','Vater','Garrish','Keeltagh','Belleny','Jerzak','Loveredge','MacMichael','McAlinion','Pykett','Tendahl','Cackett','Rainforth','Closs','Mellish','Ronayne','Gatch','Moynham','Talman','Stow','Gleed','Apted','Swannie','Flahy','Bugdall','Clowes','Staniford','Sauvage','Yates','Pirri','Fairebrother','Townsend','Vasilyevski','Downey','Crotch','Toffano','Harefoot','Fownes','Theurer','Parffrey','Hargrave','Dorgon','Saben','Inwood','Hars','Juorio','Darcey','Blasi','Toft','Rodenborch','Pickthall','Chastelain','Brettoner','Goodie','Fransewich','Maplethorpe','Bierton','Minchella','Keyse','Moody','Pietz','Petroff','Ismirnioglou','Somersett','Byne','Lathleiffure','Nield','Burbudge','Wibrew','Goldney','Van der Kruijs','Nutter','Giacopetti','Dufore','Blackhall','Hallas','Cullabine','Newling','Lengthorn','Blandford','Alred','Phillcock','Twinbrow','Ipplett','Bluck','Minchin','McShea','Flecknoe','Renehan','Sproat','Lujan','Deerness','Vaines','Aldins','Bewick','Lambrook','Kaman','Mathias','Rampley','Kops','Jeanneau','Grebner','Comizzoli','Jossum','Netherwood','Edgeson','Fattorini','Tunnadine','Stoppe','Pasquale','Node','Kleinstein','Teather','Ledwich','Etty','Gownge','Blyth','Thies','Beldan','Durber','Giuron','Tether','Callingham','Murden','Sarrell','Licciardiello','Dommersen','Steuart','Saxton','Gobbett','Halleybone','Duplan','Novak','Burnip','Keri','Redparth','Body','Sollam','Powlesland','Waghorn','Geeson','Kidde','Magill','Duberry','Godding','Gudgion','Burney','Lean','Perigoe','Shearmur','Doblin','Deniscke','Orring','Jull','Berardt','Stookes','Thiem','Trevain','D\'Elias','Guilloud','Chasteau','Oboy','Klemz','Savidge','Nation','Silburn','Coldridge','Paskerful','Bicknell','Handke','Aldwick','Ostridge','Pascho','Brotherick','Millican','O\'Donnelly','Serginson','Gosse','Filyukov','Moyles','Rembrant','Poolton','Jeacock','Totton','Burkart','Charles','Storah','Newark','Baden','Manifold','Meacher','Tolomei','Andrick','Dyke','Bordis','Neno','Murray','Calrow','Leicester','Durek','Pilkington','Deme','Francescuccio','Leftridge','Rubel','Loren','McCay','Bellard','d\'Eye','Gilder','Krahl','Corbyn','Leades','Bewly','Adamovsky','Yeowell','Jeacocke','Gaitung','Maffia','Delahunty','Liddicoat','Durward','Solomonides','Bidewell','Capstake','Cutajar','Erwin','Remington','Coole','Birkinshaw','Dewdeny','Artois','Dunkley','Dungee','Gait','Minton','Cowherd','Rattenbury','Suche','Comfort','Manuaud','Gut','Siemandl','Aleshintsev','Baty','Matushevich','Bernardin','Stoaks','Lanmeid','O\'Concannon','Riping','Grand','Rivel','Middell','Sibbons','Mouser','Dudenie','Horrigan','McPhelimy','Heintsch','Sabater','Genn','Brydon','Merrell','Maudling','Purseglove','Gisbey','Wormleighton','Linge','Daniel','Owlner','Kohrsen','Ray','Coyett','Meir','Moyser','Mizen','Speechly','Barck','Gossage','De la croix','Shute','Woodham','Gedge','Biermatowicz','Whitchurch','Curmi','Chave','Bingham','Muncey','Giles','Yeude','Dissman','Mariet','Kintish','MacPeake','Atkinson','Gaw','Tryhorn','Rendle','Everil','Milan','Tyler','MacIntosh','Pridmore','Shyres','Aucock','Gott','Arnow','Highton','Richardes','Yateman','Gergely','Westover','Ellerker','Whitmore','Robak','Say','Bisatt','Doumerque','Tommaseo','Vasyunin','Colbourne','Marrow','Paulson','Hathorn','Hartman','Lyman','Leband','Glowinski','Beccles','Kildale','Noblett','Gobourn','Kissock'];
if (NAME_TYPE.toLowerCase().trim() === 'first')
return FIRST_NAMES[(Math.floor(Math.random() * FIRST_NAMES.length))];
if (NAME_TYPE.toLowerCase().trim() === 'last')
return LAST_NAMES[(Math.floor(Math.random() * LAST_NAMES.length))];
return FIRST_NAMES[(Math.floor(Math.random() * FIRST_NAMES.length))] + ' ' + LAST_NAMES[(Math.floor(Math.random() * LAST_NAMES.length))];
$$;
CREATE OR REPLACE FUNCTION --aepUserLdap--_RANDOM_ADDRESS(LOC_TYPE STRING, CITY STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
var LOCATIONS = [
{ "city": "Pune", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "89847" },
{ "city": "Los Angeles", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "81280" },
{ "city": "San Francisco", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "99840" },
{ "city": "Foshan", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "21579" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "60712" },
{ "city": "Foshan", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "52870" },
{ "city": "Mumbai", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "31893" },
{ "city": "Paris", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "50967" },
{ "city": "Shinjuku", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "75700" },
{ "city": "Los Angeles", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "88979" },
{ "city": "Shenzhen", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "39336" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "64890" },
{ "city": "Mumbai", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "80106" },
{ "city": "Augsburg", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "82025" },
{ "city": "Houston", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "56054" },
{ "city": "Augsburg", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "79325" },
{ "city": "New York", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "23525" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "32643" },
{ "city": "Houston", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "20728" },
{ "city": "Versailles", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "94255" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "24381" },
{ "city": "San Francisco", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "35891" },
{ "city": "Shinjuku", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "57425" },
{ "city": "Chiyoda", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "20908" },
{ "city": "Versailles", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "54605" },
{ "city": "New York", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "83120" },
{ "city": "Austin", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "20947" },
{ "city": "Munich", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "43801" },
{ "city": "Foshan", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "98660" },
{ "city": "Paris", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "52348" },
{ "city": "Augsburg", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "30496" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "57780" },
{ "city": "Dallas", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "86819" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "55787" },
{ "city": "Versailles", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "98976" },
{ "city": "Mumbai", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "11270" },
{ "city": "San Francisco", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "95082" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "78474" },
{ "city": "Buffalo", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "69713" },
{ "city": "Manchester", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "63192" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "37839" },
{ "city": "Nuremberg", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "30748" },
{ "city": "Houston", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "78363" },
{ "city": "Versailles", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "94358" },
{ "city": "Shenzhen", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "18200" },
{ "city": "Mumbai", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "12997" },
{ "city": "Nagpur", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "50806" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "53856" },
{ "city": "Nagpur", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "66905" },
{ "city": "Nuremberg", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "18334" },
{ "city": "Versailles", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "84015" },
{ "city": "Munich", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "43580" },
{ "city": "Albany", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "11824" },
{ "city": "Chiyoda", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "73129" },
{ "city": "London", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "39677" },
{ "city": "Chiyoda", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "50078" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "45942" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "47718" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "22691" },
{ "city": "Shinjuku", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "29946" },
{ "city": "Albany", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "29990" },
{ "city": "London", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "32487" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "92014" },
{ "city": "London", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "37411" },
{ "city": "Albany", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "64737" },
{ "city": "London", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "16935" },
{ "city": "Shenzhen", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "71090" },
{ "city": "Los Angeles", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "41428" },
{ "city": "Los Angeles", "state": "California", "statecode": "CA", "country": "United States", "countrycode": "US", "postalcode": "75721" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "63059" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "38026" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "80720" },
{ "city": "Nagpur", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "75033" },
{ "city": "Chiyoda", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "78620" },
{ "city": "Pune", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "43180" },
{ "city": "Munich", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "91340" },
{ "city": "Pune", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "64069" },
{ "city": "Foshan", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "78737" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "85770" },
{ "city": "Chiyoda", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "60044" },
{ "city": "Dallas", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "77590" },
{ "city": "Munich", "state": "Bavaria", "statecode": "BY", "country": "Germany", "countrycode": "DE", "postalcode": "66003" },
{ "city": "Dallas", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "48590" },
{ "city": "Albany", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "53375" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "61355" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "10854" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "71714" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "98879" },
{ "city": "Buffalo", "state": "New York", "statecode": "NY", "country": "United States", "countrycode": "US", "postalcode": "49238" },
{ "city": "Pune", "state": "Maharashtra", "statecode": "MH", "country": "India", "countrycode": "IN", "postalcode": "97134" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "74784" },
{ "city": "Tokyo", "state": "Tokyo", "statecode": "TK", "country": "Japan", "countrycode": "JP", "postalcode": "89242" },
{ "city": "Guangzhou", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "80931" },
{ "city": "Birmingham", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "78054" },
{ "city": "Shenzhen", "state": "Guangdong", "statecode": "GD", "country": "China", "countrycode": "CN", "postalcode": "24361" },
{ "city": "Manchester", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "85786" },
{ "city": "Boulogne", "state": "\u00cele-de-France", "statecode": "IDF", "country": "France", "countrycode": "FR", "postalcode": "77298" },
{ "city": "Austin", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "47941" },
{ "city": "London", "state": "England", "statecode": "ENG", "country": "United Kingdom", "countrycode": "GB", "postalcode": "81595" },
{ "city": "Austin", "state": "Texas", "statecode": "TX", "country": "United States", "countrycode": "US", "postalcode": "12236"}
];
if (LOC_TYPE.toLowerCase().trim() === 'city')
return LOCATIONS[(Math.floor(Math.random() * LOCATIONS.length))]['city'];
if (LOC_TYPE.toLowerCase().trim() === 'state' && CITY) {
var selected_loc = LOCATIONS.filter(r => r.city===CITY);
return selected_loc[(Math.floor(Math.random() * selected_loc.length))]['state'];
}
if (LOC_TYPE.toLowerCase().trim() === 'statecode' && CITY) {
var selected_loc = LOCATIONS.filter(r => r.city===CITY);
return selected_loc[(Math.floor(Math.random() * selected_loc.length))]['statecode'];
}
if (LOC_TYPE.toLowerCase().trim() === 'country' && CITY) {
var selected_loc = LOCATIONS.filter(r => r.city===CITY);
return selected_loc[(Math.floor(Math.random() * selected_loc.length))]['country'];
}
if (LOC_TYPE.toLowerCase().trim() === 'countrycode' && CITY) {
var selected_loc = LOCATIONS.filter(r => r.city===CITY);
return selected_loc[(Math.floor(Math.random() * selected_loc.length))]['countrycode'];
}
if (LOC_TYPE.toLowerCase().trim() === 'postalcode' && CITY) {
var selected_loc = LOCATIONS.filter(r => r.city===CITY);
return selected_loc[(Math.floor(Math.random() * selected_loc.length))]['postalcode'];
}
return 'NOT AVAILABLE';
$$;
/*
- GENERATE DATA
Use the following queries to insert data into tables.
*/
INSERT INTO --aepUserLdap--_PERSONS (
person_id,
name,
age,
email,
phone_number,
gender,
occupation,
ismobilesub
)
SELECT person_id,
CONCAT(first_name, ' ', last_name) AS name,
age,
email,
phone_number,
gender,
occupation,
ismobilesub
FROM (
SELECT CAST(CONCAT(UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM())) AS NUMBER) AS person_id,
--aepUserLdap--_RANDOM_NAME('first') AS first_name,
--aepUserLdap--_RANDOM_NAME('last') AS last_name,
UNIFORM(20, 80, RANDOM()) AS age,
CONCAT(LOWER(first_name),'.',LOWER(last_name),'+',LEFT(MD5(RANDOM()), 5),'@emailsim.io') AS email,
CONCAT('+',UNIFORM(1, 999, RANDOM()),UNIFORM(100, 999, RANDOM()),UNIFORM(100, 999, RANDOM()),UNIFORM(1000, 9999, RANDOM())) AS phone_number,
ARRAY_CONSTRUCT('male', 'female', 'NA', NULL)[UNIFORM(0, 3, RANDOM())] AS gender,
ARRAY_CONSTRUCT('Chief Executive Officer', 'Marketing Manager', 'Software Developer', 'Financial Analyst', 'Product Manager', 'Human Resources Manager', 'Sales Executive', 'Operations Manager', 'Customer Service Representative', 'Graphic Designer', 'Business Development Manager', 'Supply Chain Analyst', 'Data Scientist', 'IT Support Specialist', 'Quality Assurance Engineer', 'Content Strategist', 'Project Manager', 'Accountant', 'Logistics Coordinator', 'Creative Director', 'Medical Assistant', 'Event Coordinator', 'UX/UI Designer', 'Digital Marketing Specialist', 'Corporate Trainer', 'Legal Advisor', 'Environmental Consultant', 'Public Relations Manager', 'Investment Analyst', 'Talent Acquisition Specialist', NULL)[UNIFORM(0, 30, RANDOM())] AS occupation,
ARRAY_CONSTRUCT(true, true, false, true, true, true, true, true, true, true)[UNIFORM(0, 9, RANDOM())] AS ismobilesub
FROM TABLE(GENERATOR(ROWCOUNT => 20000))
) vw;
-- Relationship with --aepUserLdap--_PERSONS as 1:1
-- Assuming data is available for 95% of Profiles.
-- You may choose to adjust the percentage OR remove `SAMPLE` clause, if you wish to include all Profiles.
INSERT INTO --aepUserLdap--_HOUSEHOLDS (
household_id,
address,
city,
state,
postal_code,
country,
iseligibleforfiber,
primary_person_id,
isfibreenabled
)
SELECT CAST(CONCAT(UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM())) AS NUMBER) AS household_id,
NULL AS address,
--aepUserLdap--_RANDOM_ADDRESS('city', null) AS city,
--aepUserLdap--_RANDOM_ADDRESS('state', city) AS state,
--aepUserLdap--_RANDOM_ADDRESS('postalCode', city) AS postal_code,
--aepUserLdap--_RANDOM_ADDRESS('country', city) AS country,
ARRAY_CONSTRUCT(true, true, false, true, true, true, true, true, true, true)[UNIFORM(0, 9, RANDOM())] AS iseligibleforfiber,
person_id,
CASE
WHEN iseligibleforfiber = true
THEN ARRAY_CONSTRUCT(true, true, false, true, true, true, true, true, true, true)[UNIFORM(0, 9, RANDOM())]
ELSE false
END AS isfibreenabled
FROM --aepUserLdap--_persons
SAMPLE (95);
-- Relationship with --aepUserLdap--_PERSONS as 1:N
-- Upto 4 users
-- Assuming data is available for 90% of Profiles.
-- You may choose to adjust the percentage OR remove `SAMPLE` clause, if you wish to include all Profiles.
INSERT INTO --aepUserLdap--_USERS (
user_id,
person_id,
household_id
)
SELECT CAST(CONCAT(UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM()),UNIFORM(1000, 9999, RANDOM())) AS NUMBER) AS user_id,
primary_person_id AS person_id,
household_id
FROM (
SELECT a.primary_person_id, a.household_id,
UNIFORM(1, 4, RANDOM()) AS repeater
FROM (SELECT primary_person_id, household_id FROM --aepUserLdap--_households SAMPLE (90)) a
) p,
(SELECT ROW_NUMBER() OVER(PARTITION BY NULL ORDER BY NULL) AS loops
FROM TABLE(GENERATOR(ROWCOUNT => 4))
) i
WHERE p.repeater >= i.loops;
-- Assuming relationship with --aepUserLdap--_USERS as 1:N
-- Assuming data is available for 95% of Profiles.
-- You may choose to adjust the percentage OR remove `SAMPLE` clause, if you wish to include all Profiles.
INSERT INTO --aepUserLdap--_MONTHLY_DATA_USAGE (
user_id,
month,
data_usage_gb
)
SELECT user_id,
DATEADD(day, -1*UNIFORM(0, 30, RANDOM()), CURRENT_TIMESTAMP)::DATE AS month,
UNIFORM(1, 10::NUMBER(10,2), RANDOM()) AS data_usage_gb
FROM (
SELECT a.user_id, a.person_id, a.household_id,
UNIFORM(1, 50, RANDOM()) AS repeater
FROM (SELECT user_id, person_id, household_id FROM --aepUserLdap--_users SAMPLE (95)) a
) p,
(SELECT ROW_NUMBER() OVER(PARTITION BY NULL ORDER BY NULL) AS loops
FROM TABLE(GENERATOR(ROWCOUNT => 50))
) i
WHERE p.repeater >= i.loops;
INSERT INTO --aepUserLdap--_MOBILE_DATA_USAGE (
user_id,
date,
time,
app_name,
data_usage_mb,
network_type,
device_type,
country_code
)
SELECT user_id,
DATEADD(day, -1*UNIFORM(0, 30, RANDOM()), CURRENT_TIMESTAMP)::DATE AS "date",
TO_CHAR(DATEADD('minute', UNIFORM(0, 1440, RANDOM()), "date"), 'HH24:MI:SS.FF9') AS "time",
ARRAY_CONSTRUCT('Facebook', 'Instagram', 'Snapchat', 'TikTok', 'YouTube', 'WhatsApp', 'Twitter', 'Spotify', 'Netflix', 'Amazon', 'Google Maps', 'Uber', 'Venmo', 'Gmail', 'Zoom', 'Cash App', 'Pinterest', 'LinkedIn', 'Disney+', 'Reddit')[UNIFORM(0, 19, RANDOM())] AS network_type,
UNIFORM(1, 5120::NUMBER(10,2), RANDOM()) AS data_usage_mb,
ARRAY_CONSTRUCT('2G', '3G', '4G', '5G', 'LTE', 'WiFi', '3G', '3G', '4G', '5G', '5G', '5G', '5G')[UNIFORM(0, 12, RANDOM())] AS network_type,
ARRAY_CONSTRUCT('Mobile', 'Tablet', 'TV', 'Watch', 'Mobile', 'Mobile', 'Mobile')[UNIFORM(0, 6, RANDOM())] AS device_type,
--aepUserLdap--_RANDOM_ADDRESS('countryCode', h.city) AS country_code
FROM (
SELECT a.user_id, a.person_id, a.household_id,
UNIFORM(1, 50, RANDOM()) AS repeater
FROM (SELECT user_id, person_id, household_id FROM --aepUserLdap--_users SAMPLE (95)) a
) p,
(SELECT ROW_NUMBER() OVER(PARTITION BY NULL ORDER BY NULL) AS loops
FROM TABLE(GENERATOR(ROWCOUNT => 50))
) i,
--aepUserLdap--_households h
WHERE p.repeater >= i.loops
AND p.household_id = h.household_id;
この画像が表示されます。 クリックして ドロップダウン リストを開き、「すべて実行」を選択してすべてのクエリを実行します。
これには数分かかり、その後、これが表示されます。
これで、デモデータがSnowflake データベースに読み込まれました。 任意のテーブルを開いて、自由にデータを調べることができます
これで、Snowflakeでの設定が完了しました。
次の手順
1.3.2 スキーマ、データモデル、リンクの作成を参照してください。
Federated Audience Composition に戻る
すべてのモジュールに戻る
recommendation-more-help
4bbf020c-24db-4a43-b239-88fab142f02d