/******************************************************
 *
 * Name:         bulk-insert-from-blob-storage.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     08-25-2017
 *     Purpose:  Load the Azure SQL database
 *               using BULK INSERT and OPENROWSET.
 * 
 ******************************************************/


--
-- Master Key
--

-- Drop master key
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
DROP MASTER KEY;


-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Qkmof0SV3yxReKEP'; 
GO


--
-- Database Credential 
--

-- Drop db credential
IF EXISTS(SELECT * FROM sys.database_credentials WHERE name = 'CRD_AZURE_4_STOCKS')
DROP DATABASE SCOPED CREDENTIAL CRD_AZURE_4_STOCKS;  
GO 

-- Create db credential
CREATE DATABASE SCOPED CREDENTIAL CRD_AZURE_4_STOCKS
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2017-04-17&ss=b&srt=sco&sp=rwdlac&se=2018-08-01T21:32:58Z&st=2017-08-01T13:32:58Z&spr=https,http&sig=g6PVlNZuDjzyPAP6xHs9h%2Bv9LPRfrTy0cuSMGwpsQtA%3D';
GO


--
-- External data src 
--

-- Drop external data src
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE NAME = 'EDS_AZURE_4_STOCKS')
DROP EXTERNAL DATA SOURCE EDS_AZURE_4_STOCKS;
GO

-- Create external data src
CREATE EXTERNAL DATA SOURCE EDS_AZURE_4_STOCKS
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://sa4tips17.blob.core.windows.net/sc4tips17',
    CREDENTIAL = CRD_AZURE_4_STOCKS
);


--
-- One big string
--

SELECT *
FROM 
OPENROWSET
(
    BULK 'INBOUND/PACKING-LIST.TXT',
    DATA_SOURCE = 'EDS_AZURE_4_STOCKS', 
    SINGLE_CLOB
) AS RAW_DATA


--
-- Must be using 2016 compatibility level
--

ALTER DATABASE [PORTFOLIO] SET COMPATIBILITY_LEVEL = 130 
GO


--
-- Packing List
--

SELECT 
    CAST(LIST_DATA.VALUE AS VARCHAR(256)) AS PKG_LIST
FROM 
    OPENROWSET
    (
        BULK 'INBOUND/PACKING-LIST.TXT',
        DATA_SOURCE = 'EDS_AZURE_4_STOCKS', 
        SINGLE_CLOB
    ) AS RAW_DATA
CROSS APPLY 
    STRING_SPLIT(REPLACE(REPLACE(RAW_DATA.BulkColumn, CHAR(10), 'ş'), CHAR(13), ''), 'ş') AS LIST_DATA;


--
-- Use staging table
--

-- Clear data
TRUNCATE TABLE [STAGE].[STOCKS];

-- Load data
BULK INSERT [STAGE].[STOCKS]
FROM 'INBOUND/MSFT-FY2016.CSV'
WITH 
(  
DATA_SOURCE = 'EDS_AZURE_4_STOCKS',
FORMAT = 'CSV', 
CODEPAGE = 65001, 
FIRSTROW = 2,
TABLOCK
); 

-- Show data
SELECT * FROM [STAGE].[STOCKS];


--
-- Use auditing table
--

-- Insert test record
INSERT INTO [STAGE].[AUDIT] (AU_CMD_TEXT) VALUES ('TEST')
GO

-- View test record
SELECT * FROM [STAGE].[AUDIT] 
GO

-- Clear table
TRUNCATE TABLE [STAGE].[AUDIT];



--
-- Load from blob storage
--

-- Drop stored procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ACTIVE].[LOAD_FROM_BLOB_STORAGE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [ACTIVE].[LOAD_FROM_BLOB_STORAGE]
GO
 

-- Create stored procedure
CREATE PROCEDURE [ACTIVE].[LOAD_FROM_BLOB_STORAGE]
    @VAR_VERBOSE_FLAG CHAR(1) = 'N'
AS
BEGIN

    -- Error handling variables
    DECLARE @VAR_ERR_NUM INT;
    DECLARE @VAR_ERR_LINE INT;
    DECLARE @VAR_ERR_MSG VARCHAR(1024);
    DECLARE @VAR_ERR_PROC VARCHAR(1024);

	-- Declare variables
    DECLARE @VAR_NEXT_FILE VARCHAR(256);
    DECLARE @VAR_AZURE_BLOB VARCHAR(256);
    DECLARE @VAR_SQL_STMT NVARCHAR(1024);
 
    -- No counting of rows
    SET NOCOUNT ON;
  
	-- Debugging
    IF (@VAR_VERBOSE_FLAG = 'Y')
    BEGIN
        PRINT '[LOAD_FROM_BLOB_STORAGE] - STARTING TO EXECUTE STORED PROCEDURE.'
        PRINT ' '
    END

    -- ** ERROR HANDLING - START TRY **
    BEGIN TRY

	    -- Clear data
        TRUNCATE TABLE [STAGE].[STOCKS];

        -- Define cursor
        DECLARE VAR_FILE_CURSOR CURSOR FOR
            SELECT 
                CAST(LIST_DATA.VALUE AS VARCHAR(256)) AS PKG_LIST
            FROM 
                OPENROWSET
                (
                    BULK 'INBOUND/PACKING-LIST.TXT',
                    DATA_SOURCE = 'EDS_AZURE_4_STOCKS', 
                    SINGLE_CLOB
                ) AS RAW_DATA
            CROSS APPLY 
                STRING_SPLIT(REPLACE(REPLACE(RAW_DATA.BulkColumn, CHAR(10), 'ş'), CHAR(13), ''), 'ş') AS LIST_DATA
			WHERE
			    LTRIM(RTRIM(LIST_DATA.VALUE)) <> '';

        -- Open cursor
        OPEN VAR_FILE_CURSOR;

        -- Get first row
        FETCH NEXT FROM VAR_FILE_CURSOR INTO @VAR_NEXT_FILE;
		SET @VAR_AZURE_BLOB = CHAR(39) + 'INBOUND/' + @VAR_NEXT_FILE + CHAR(39);

        -- While there is data
        WHILE (@@fetch_status = 0)
        BEGIN

			-- Debugging
            IF (@VAR_VERBOSE_FLAG = 'Y')
            BEGIN
                PRINT '[LOAD_FROM_BLOB_STORAGE] - LOADING FILE ' + @VAR_AZURE_BLOB + '.'
                PRINT ' '
            END

            -- Create dynamic sql statement
            SELECT @VAR_SQL_STMT = '  
            BULK INSERT [STAGE].[STOCKS]  
            FROM ' + @VAR_AZURE_BLOB + '  
            WITH  
            (    
                DATA_SOURCE = ''EDS_AZURE_4_STOCKS'',  
                FORMAT = ''CSV'',  
                CODEPAGE = 65001,  
                FIRSTROW = 2,  
                TABLOCK  
            );'  

			-- Debugging
            IF (@VAR_VERBOSE_FLAG = 'Y')
            BEGIN			
			    PRINT @VAR_SQL_STMT
                PRINT ' '
            END

			-- Execute Bulk Insert
      	    EXEC SP_EXECUTESQL @VAR_SQL_STMT;

			-- Insert test record
            INSERT INTO [STAGE].[AUDIT] (AU_CMD_TEXT) VALUES (@VAR_SQL_STMT);

            -- Grab the next record
            FETCH NEXT FROM VAR_FILE_CURSOR INTO @VAR_NEXT_FILE;
    		SET @VAR_AZURE_BLOB = CHAR(39) + 'INBOUND/' + @VAR_NEXT_FILE + CHAR(39);

        END

        -- Close cursor
        CLOSE VAR_FILE_CURSOR;

        -- Release memory
        DEALLOCATE VAR_FILE_CURSOR;

    -- ** ERROR HANDLING - END TRY **
    END TRY

    -- ** Error Handling - Begin Catch **
    BEGIN CATCH
       
      -- Grab variables 
      SELECT 
          @VAR_ERR_NUM = ERROR_NUMBER(), 
          @VAR_ERR_PROC = ERROR_PROCEDURE(),
          @VAR_ERR_LINE = ERROR_LINE(), 
          @VAR_ERR_MSG = ERROR_MESSAGE();

      -- Raise error
      RAISERROR ('An error occurred within a user transaction. 
                  Error Number        : %d
                  Error Message       : %s  
                  Affected Procedure  : %s
                  Affected Line Number: %d'
                  , 16, 1
                  , @VAR_ERR_NUM, @VAR_ERR_MSG, @VAR_ERR_PROC, @VAR_ERR_LINE);       

    -- ** Error Handling - End Catch **    
    END CATCH                
            
END
GO


--
-- Test load process
--

-- Import blob files into stage
EXEC [ACTIVE].[LOAD_FROM_BLOB_STORAGE] @VAR_VERBOSE_FLAG = 'N';

-- Move from stage to active
INSERT INTO [ACTIVE].[STOCKS]
SELECT * 
FROM [STAGE].[STOCKS];



-- Validate data
SELECT 
    [ST_SYMBOL], 
	COUNT(*) AS TRADING_DAYS
FROM [ACTIVE].[STOCKS]
GROUP BY [ST_SYMBOL]
ORDER BY [ST_SYMBOL];




-- This command fails
BULK INSERT [ACTIVE].[STOCKS]
FROM 'INBOUND/MSFT-FY2016.CSV'
WITH 
(  
DATA_SOURCE = 'EDS_AZURE_4_STOCKS',
FORMAT='CSV', 
CODEPAGE = 65001, 
FIRSTROW = 2,
TABLOCK
); 


-- Clear data
TRUNCATE TABLE [ACTIVE].[STOCKS];

-- Insert directly into table
INSERT INTO [ACTIVE].[STOCKS]
SELECT 
    REPLACE([SYMBOL], '"', '') AS ST_SYMBOL,
    REPLACE([DATE], '"', '') AS ST_DATE,
    [OPEN] AS ST_OPEN,
    [HIGH] AS ST_HIGH,
    [LOW] AS ST_LOW,
    [CLOSE] AS ST_CLOSE,
    [ADJCLOSE] AS ST_ADJCLOSE,
    [VOLUME] AS ST_VOLUME
FROM
OPENROWSET
(
    BULK 'INBOUND/MSFT-FY2016.CSV',
    DATA_SOURCE = 'EDS_AZURE_4_STOCKS', 
    FORMATFILE = 'INBOUND/STOCK-DATA.FMT', 
	FORMATFILE_DATA_SOURCE = 'EDS_AZURE_4_STOCKS',
    FIRSTROW = 2
) AS RAW_DATA