-- takes an encoded URL and attempts to decode it
-- encoded URLs are where characters that can't be ASCII encoded (+, ?, /, #, & etc) are replaces with a % followed by a hexidecimal number (ie where letters A-F are used as numbers after 0-9)
-- DBR 16.0, https://docs.databricks.com/en/sql/language-manual/functions/try_url_decode.html

-- this will work as it's a valid URL, and will return the decoded URL
SELECT try_url_decode('http%3A%2F%2Fnotebooks.databricks.com%2Fdevrel%2F') as decoded

UNION 
--this *won't* work as there's a missing F at the end, and will return a null value
SELECT try_url_decode('http%3A%2F%2Fnotebooks.databricks.com%2Fdevrel%2') as decoded;

-- returns a null value if the expression is zero, and vice versa
-- DBR 16.0, https://docs.databricks.com/en/sql/language-manual/functions/zeroifnull.html

--pass zeroifnull a null and it'll return a zero
SELECT null as input, "zeroifnull" as function, zeroifnull(null) as output

UNION
--pass zeroifnull a *non* null and it'll return that value
SELECT 42 as input, "zeroifnull" as function, zeroifnull(42) as output

UNION
--pass nullifzero a zero and it'll return a null
SELECT 0 as input, "nullifzero" as function, nullifzero(0) as output

UNION
--pass nullifzero a *non* null and it'll return that value
SELECT 42 as input, "nullifzero" as function, nullifzero(42) as output

-- Returns the day of the month from a valid date expression.
-- This is a synonym for extract(DAY FROM expr)
-- DBR 16.1, https://docs.databricks.com/en/sql/language-manual/functions/day.html

-- using the new day function
SELECT day('2019-01-07') as result
UNION ALL

-- the old synonym
SELECT extract(DAY FROM '2019-01-07') as result
UNION ALL

--checks for a valid date
SELECT day('2019-02-30') as result;

-- generates a random number between two specified values with a uniform distribution, ie even chances of getting any number 
-- there's an option to specify a seed to consistently get the same random number
-- DBR 16.1, https://docs.databricks.com/en/sql/language-manual/functions/uniform.html

-- select a random number between 10 and 20
-- if you run this cell multiple times the number will update
SELECT uniform(10, 20) as random_no
UNION ALL 

-- select a consistently random number between 10 and 20
-- if you run this cell multiple times the number *won't* update
SELECT uniform(10, 20, 42) as random_no

-- returns a random string of specified length
-- the string can contain uppercase, lowercase and numeric values 
-- there's an option to specify a seed to consistently get the same random string
-- DBR 16.1, https://docs.databricks.com/en/sql/language-manual/functions/randstr.html

-- creates a random string of length 10
SELECT randstr(10) as random_string
UNION ALL

-- creates a consistently random string of length 10
SELECT randstr(10, 42) as random_string;

-- The following functions now support named parameter invocation: variant_explode, variant_explode_outer, inline, inline_outer, posexplode, posexplode_outer
-- this means you can have flexibility in the order and improve readability

--instead of this
SELECT pos, col FROM posexplode(array(10, 20));

-- you can do this
SELECT pos, col FROM posexplode(collection => array(10, 20));

CREATE TABLE ham_types(ham_type STRING NOT NULL, is_edible BOOLEAN, --standard table definition
                CONSTRAINT ham_key PRIMARY KEY(ham_type)); --name and define the constraint

CREATE TABLE sandwich_feasibility(ham_type STRING NOT NULL, is_feasible BOOLEAN,
                CONSTRAINT ham_sandwich_key FOREIGN KEY(ham_type) REFERENCES ham_types);

select * from  information_schema.key_column_usage
where constraint_name in ('ham_key','ham_sandwich_key');

%python
(spark.readStream.format("rate") # generate some random data of timestamps and a value
  .load()
  .writeStream 
  .clusterBy("timestamp") # <- this bit is new and now possible 
  .option("checkpointLocation", "dbfs:/tmp/checkpoint/random_numbers")
  .trigger(once=True)  # let's not have this run forever
  .toTable("random_numbers") # new table created as part of this syntax
)

describe extended random_numbers --check out rows 3-5, 24

%sh
curl -v -X PUT -H "Authorization: Bearer <Developer Access Token>" "https://<your workspace url>.cloud.databricks.com/api/2.0/unity-catalog/metastores/<metastore id found in UC>/systemschemas/compute"

select * from system.compute.warehouses
--sorry removing this result for sensitive data
--but just imagine the best practices you've ever seen

CREATE MATERIALIZED VIEW ham_feasibility
SCHEDULE EVERY 1 HOUR --new schedule syntax
AS SELECT `🐖`.ham_type, `🥪`.is_feasible 
FROM ham_types AS `🐖`
LEFT JOIN sandwich_feasibility AS `🥪`
ON `🐖`.ham_type = `🥪`.ham_type;

VACUUM ham_types LITE;