gistfile1.txt

-- cleanup previous demo
-- drop database and permissions
drop database if exists marketing cascade INCLUDING PERMISSIONS;
drop database if exists demo cascade INCLUDING PERMISSIONS;
-- ensure roles are all created cleanly
drop role if exists analyst_role;
create role if not exists analyst_role;
grant role analyst_role to group analyst_group;
-- create US role for old view row filter demo. 
-- TODO remove once we are only using 2.1 to demo 
drop role if exists us;
create role if not exists us;
grant role us to group analyst_group;
-- drop crawlers
drop crawler if exists demo CASCADE;
drop crawler if exists marketing CASCADE;
-- grant workspace to analyst
grant role okera_workspace_role to group analyst_group;
-- create databases
create database IF NOT EXISTS demo;
create database IF NOT EXISTS marketing;
-- create attributes
CREATE ATTRIBUTE IF NOT EXISTS marketing.approved;
CREATE ATTRIBUTE IF NOT EXISTS marketing.restricted;
-- create one table in marketing database with the right attributes
CREATE EXTERNAL TABLE `marketing`.`user_account_data`(
	`name` STRING ATTRIBUTE pii.person,
	`phone` STRING,
	`email` STRING ATTRIBUTE pii.domain_name pii.email_address,
	`userid` STRING ATTRIBUTE misc.guid,
	`lastlogin` STRING,
	`creditcardnumber` STRING ATTRIBUTE pii.credit_card,
	`location` STRING ATTRIBUTE pii.gps,
	`ipv4_address` STRING ATTRIBUTE pii.ip_address,
	`ipv6_address` STRING ATTRIBUTE pii.ip_address
)
COMMENT 'Discovered by Okera crawler'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'adl://okeratestdata.azuredatalakestore.net/okera-demo/user_account_data'
TBLPROPERTIES('skip.header.line.count'='1');
-- add marketing.approved tag at the table level
ALTER TABLE `marketing`.`user_account_data` ADD ATTRIBUTE marketing.approved;