DianGermishuizen

create or replace procedure CREATE_EXTERNAL_DELTA_TABLE 
( 
     p_schema_name string /*the schema where the external table will be created*/ 
    , p_table_name string /*the name of the target table*/ 
    , p_stage string /*The schema_name.stage name of the snowflake stage used to interact with the cloud storage container*/ 
    , p_directory string /*the directory in the cloud storage container, nested in the stage, that contains the delta table files.*/ 
) 
returns string 
language sql 
comment = 'Stored procedure used to create a Delta external table.' 

DianGermishuizen

# import the regex python library
import re
import dlt
from pyspark.sql.functions import *
import json

def to_snake_case(name):
    name = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
    name = re.sub('__([A-Z])', r'_\1', name)
    name = re.sub('([a-z0-9])([A-Z])', r'\1_\2', name)

DianGermishuizen

# pDataLakeContainer: "silver" 
# pTableName": "Product" 
# vDeltaTablePath: "/mnt/silver/WorldWideImporters/Batch/Product/" 


vDeltaTableCreateStatement = 'CREATE TABLE IF NOT EXISTS ' \ 
+ pDataLakeContainer + '.' + pTableName + ' \n' \ 
+ 'USING DELTA ' + '\n' \ 
+ 'LOCATION \'' + vDeltaTablePath + '\' ' + '\n' \ 
+ 'PARTITIONED BY ( ProductCategory )' + '\n' \ 

DianGermishuizen

/*Variable to hold the value we will filter on at runtime*/ 
DECLARE @vCustomerID INT; 

/*Variable to hold the query string we will alter at runtime then execute*/ 
DECLARE @vSqlQuery VARCHAR(4000); 

/*Define the base version of the query*/ 
SET @vSqlQuery = ' 

SELECT * 

DianGermishuizen

CREATE OR REPLACE PROCEDURE "PROCEDURE_PERSIST_FILES_IN_STAGE"
(
	STAGE_NAME VARCHAR
)
	RETURNS VARCHAR
    LANGUAGE SQL
    EXECUTE AS CALLER
AS
$$BEGIN

DianGermishuizen

CREATE OR REPLACE PROCEDURE "PROCEDURE_LIST_FILES_IN_STAGE"
(
	STAGE_NAME VARCHAR
)
	RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
AS
$$

DianGermishuizen

CREATE OR ALTER FUNCTION [dbo].[utvf_Generate_VirtualDateTable]
(
    @pStartDate AS DATE
    , @pEndDate AS DATE
) 
RETURNS TABLE
AS
/*=====================================================================================================================================================
Author:			Dian Germishuizen
Description:	Generate a list of numbers between the LowerLimit and the UpperLimit

DianGermishuizen

CREATE OR ALTER FUNCTION [dbo].[ufn_Get_AllIterationsOfWeekdayInYearMonth]
(
    @pYear INT /*The year to investigate*/
    , @pMonth INT /*The month to investigate*/
    , @pWeekdayName VARCHAR(255) /*The week day to return e.g. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday*/
)
RETURNS TABLE
AS
/*=====================================================================================================================================================
Author:			Dian Germishuizen

DianGermishuizen

# pDataLakeContainer: "silver"
# pTableName": "Product"
# vDeltaTablePath: "/mnt/silver/WorldWideImporters/Batch/Product/"

vDeltaTableCreateStatement = 'CREATE TABLE IF NOT EXISTS ' \
+ pDataLakeContainer + '.' + pTableName + ' \n' \
+ 'USING DELTA ' + '\n' \
+ 'LOCATION \'' + vDeltaTablePath + '\' ' + '\n' \
+ 'PARTITIONED BY ( ProductCategory )' + '\n' \
+ 'AS' + '\n' \

DianGermishuizen

/*Variable to hold the value we will filter on at runtime*/
DECLARE @vCustomerID INT;

/*Variable to hold the query string we will alter at runtime then execute*/
DECLARE @vSqlQuery VARCHAR(4000);

/*Define the base version of the query*/
SET @vSqlQuery = '
SELECT *
FROM [dbo].[Customers]