![\"\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2024\/04\/Azure_SQLDB_HTTP_REST_Sample_Solution_Architecture.png\")
<\/a><\/p>\nAlso, please note that I do not advocate for building high-volume, high-velocity, real-time pipelines using database triggers and SQL Server system stored procedures. Microsoft clearly outlines limits imposed on throttling for the number of concurrent connections to external endpoints as well as limitations in the HTTP request and response payload supported media types and size, URL length, header size etc., so it\u2019s clearly not a panacea for all your integration needs. However, for sporadic and limited use cases \u2013 think in-database Zapier – this can significantly cut development time and allow DBAs and database devs to reach into other corners of Azure ecosystem with little fuss.<\/p>\n
Solution Implementation<\/h3>\n
To start with, we need Azure Storage Account with the input container to store our incoming JSON files. Once we have one created, we can develop a small Azure Function which executes on blob being persisted in the target location and executes Azure SQL Database stored procedure responsible for data acquisition. The following is a small Python script calling our first stored procedure \u2013 usp_load_from_azure_blob \u2013 every time a new blob is created. For simplicity\u2019s sake, the code does not do any file schema validation or pre-processing and its sole role is to execute SQL Server stored procedure.<\/p>\n
\r\nimport logging\r\nimport pyodbc\r\nimport azure.functions as func\r\nfrom os import path\r\n\r\n# For demo only - any cryptographic keys should be stored in Secrets Store e.g. Azure Key Vault!\r\n_SQL_SERVER = 'Your_Azure_Server_Name'\r\n_SQL_DB = 'Your_Your_DB_Name'\r\n_USERNAME = 'Your_DB_User_Name'\r\n_PASSWORD = 'Your_DB_User_Name_Password'\r\n_DRIVER = '{ODBC Driver 18 for SQL Server}'\r\n_TARGET_TABLE_NAME = 'customer_interactions'\r\n_TARGET_SCHEMA_NAME ='dbo'\r\n_TARGET_STORED_PROC_NAME = 'usp_load_from_azure_blob'\r\n\r\n\r\ndef main(inputblob: func.InputStream):\r\n \r\n logging.info('Python blob trigger function processed blob {blob_name}'.format(blob_name = inputblob.name))\r\n try:\r\n cnxn = pyodbc.connect('DRIVER='+_DRIVER+';SERVER='+_SQL_SERVER +\r\n ';PORT=1433;DATABASE='+_SQL_DB+';UID='+_USERNAME+';PWD='+_PASSWORD)\r\n if cnxn:\r\n logging.info('Connection to {mssql} SQL Server succeeded!'.format(mssql=SQL_SERVER))\r\n except pyodbc.Error as e:\r\n sqlstate = e.args[1]\r\n logging.error(\r\n sqlstate)\r\n if cnxn:\r\n logging.info('Executing {stored_proc} stored procedure...'.format(stored_proc=_TARGET_STORED_PROC_NAME))\r\n cursor = cnxn.cursor()\r\n sql = '''\\\r\n DECLARE @Return_Code INT;\r\n EXEC @Return_Code = {stored_proc} ?,?,?;\r\n SELECT @Return_Code AS rc;'''.format(stored_proc = _TARGET_STORED_PROC_NAME)\r\n values = (path.basename(inputblob.name), _TARGET_SCHEMA_NAME, _TARGET_TABLE_NAME)\r\n cursor.execute(sql, values)\r\n rc = cursor.fetchval()\r\n if rc == 0:\r\n logging.info('Stored procedure {stored_proc} executed successfully!'.format(stored_proc=_TARGET_STORED_PROC_NAME))\r\n cursor.commit() \r\n<\/pre>\nNow that we have our function, let\u2019s create a small JSON file called \u2018customer12345.json\u2019 (I used ChatGPT for this), target table the stored procedure used in our Python script. Also, given that some REST endpoints require authentication in order to be properly invoked, we will need to create Database Scoped Credentials (DSC) to securely store authentication data (like a Bearer token for example) to call a protected endpoint. The following code creates Scoped Credential \u2018azblobstore\u2019 with SAS access token, a table called customer_interactions where unparsed JSON data will be stored, and the main stored procedure used for data acquisition. Notice that in line 42, there is also a reference to a table value function called tvf_compare_json_docs which is there to allow JSON payload comparison in the odd case the same file (with the same file name) is submitted more than once and we\u2019d like to update the original version and populated Update_DataTime field in the target table (the code behind this tvf and JSON file can be found in my OneDrive folder HERE<\/a>).<\/p>\n\r\n-- create encryption key\r\nIF NOT EXISTS\r\n(\r\n SELECT *\r\n FROM sys.symmetric_keys\r\n WHERE [name] = '##MS_DatabaseMasterKey##'\r\n)\r\nBEGIN\r\n CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$trong_Pa$$word';\r\nEND;\r\n\r\n-- create credential name\r\nIF EXISTS\r\n(\r\n SELECT TOP (1)\r\n 1\r\n FROM sys.database_credentials\r\n WHERE name = 'azblobstore'\r\n)\r\nBEGIN\r\n DROP DATABASE SCOPED CREDENTIAL azblobstore;\r\nEND;\r\n\r\n-- create database scoped credential\r\nCREATE DATABASE SCOPED CREDENTIAL [azblobstore]\r\nWITH IDENTITY = 'SHARED ACCESS SIGNATURE',\r\n SECRET = 'Your_Azure_Blob_Storage_SAS_Secret_Value';\r\nGO\r\n\r\n-- create target table\r\nDROP TABLE IF EXISTS [dbo].[customer_interactions]\r\nCREATE TABLE [dbo].[customer_interactions](\r\n\t[file_id] [UNIQUEIDENTIFIER] NOT NULL,\r\n\t[file_name] [NVARCHAR](1024) NULL,\r\n\t[payload] [NVARCHAR](MAX) NULL,\r\n\t[sentiment] [VARCHAR](20) NULL,\r\n\t[insert_datetime] [DATETIME2](7) NULL,\r\n\t[update_datetime] [DATETIME2](7) NULL,\r\n CONSTRAINT [file_name] PRIMARY KEY CLUSTERED \r\n(\r\n\t[file_id] ASC\r\n)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]\r\n) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]\r\nGO\r\nALTER TABLE [dbo].[customer_interactions] ADD CONSTRAINT [df_file_id] DEFAULT (NEWSEQUENTIALID()) FOR [file_id]\r\nGO\r\nALTER TABLE [dbo].[customer_interactions] ADD DEFAULT (NULL) FOR [update_datetime]\r\nGO\r\n\r\n-- create usp_load_from_azure_blob stored procedure\r\nCREATE OR ALTER PROCEDURE [dbo].[usp_load_from_azure_blob]\r\n(\r\n @file_name VARCHAR(1024),\r\n @schema_name sysname,\r\n @table_name sysname,\r\n @table_spec sysname = NULL\r\n)\r\nAS\r\nBEGIN\r\n SET NOCOUNT ON;\r\n DECLARE @has_identity_column INT;\r\n DECLARE @new_json NVARCHAR(MAX);\r\n DECLARE @old_json NVARCHAR(MAX);\r\n\tDECLARE @new_json_file_name NVARCHAR(1024);\r\n DECLARE @old_json_file_name NVARCHAR(1024);\r\n DECLARE @error_message VARCHAR(MAX);\r\n DECLARE @url NVARCHAR(MAX) = CONCAT('https:\/\/your_storage_account_name.blob.core.windows.net\/input-json\/', @file_name);\r\n DECLARE @response NVARCHAR(MAX);\r\n\tDECLARE @time_zone VARCHAR (128)\r\n\r\n IF @table_name IS NULL\r\n SELECT @table_name = PARSENAME(@table_spec, 1);\r\n IF @schema_name IS NULL\r\n SELECT @schema_name = PARSENAME(@table_spec, 2);\r\n IF @table_name IS NULL\r\n OR @schema_name IS NULL\r\n BEGIN\r\n SET @error_message = 'Target DB, schema or table name was not provided. Bailing out!';\r\n RAISERROR( @error_message, \r\n 16, \r\n 1 \r\n );\r\n RETURN;\r\n END;\r\n\r\n\tIF NOT EXISTS\r\n\t(\r\n\t SELECT current_utc_offset\r\n\t FROM sys.time_zone_info\r\n\t WHERE name = 'AUS Eastern Standard Time'\r\n\t)\r\n\tBEGIN\r\n\t SET @time_zone = 'UTC';\r\n\tEND\r\n\tELSE\r\n\tBEGIN\r\n\t SET @time_zone = 'AUS Eastern Standard Time';\r\n\tEND;\r\n\r\n EXEC sp_invoke_external_rest_endpoint @url = @url,\r\n @method = 'GET',\r\n @headers = '{"Accept":"application\/json"}',\r\n @credential = azblobstore,\r\n @response = @response OUTPUT;\r\n\r\n IF TRIM(JSON_VALUE(@response, '$.response.status.http.code')) <> '200'\r\n AND TRIM(JSON_VALUE(@response, '$.response.status.http.description')) <> 'OK'\r\n BEGIN\r\n SET @error_message = 'Rest call response was unsuccessfull. Bailing out!';\r\n RAISERROR( @error_message, \r\n 16, \r\n 1 \r\n );\r\n RETURN;\r\n END;\r\n\r\n\tSET @new_json =\r\n\t(\r\n\t SELECT JSON_QUERY(@response, '$.result')\r\n\t);\r\n\tSET @old_json =\r\n\t(\r\n\t SELECT payload FROM dbo.customer_interactions WHERE file_name = @file_name\r\n\t);\r\n\tSET @new_json_file_name = @file_name;\r\n\tSET @old_json_file_name =\r\n\t(\r\n\t SELECT file_name FROM dbo.customer_interactions WHERE file_name = @file_name\r\n\t);\r\n\r\n\r\n IF (ISJSON(@new_json) < 1)\r\n BEGIN\r\n SET @error_message\r\n = 'Provided source JSON payload is not properly formatted or the file does not exist. Bailing out!';\r\n RAISERROR( @error_message, \r\n 16, \r\n 1 \r\n );\r\n RETURN;\r\n END;\r\n\r\n DROP TABLE IF EXISTS #returntable;\r\n SELECT *\r\n INTO #returntable\r\n FROM dbo.tvf_compare_json_docs(@new_json, @old_json);\r\n\r\n DECLARE @select_sql NVARCHAR(200) =\r\n (\r\n SELECT 'SELECT * FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)\r\n );\r\n\r\n SELECT @has_identity_column = MAX(CONVERT(INT, is_identity_column))\r\n FROM sys.dm_exec_describe_first_result_set(@select_sql, NULL, 1) AS f;\r\n\r\n\tDECLARE @delete_cmd VARCHAR(MAX)\r\n\t = 'DELETE FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WHERE file_name = ''' + @file_name\r\n\t + ''';';\r\n\tDECLARE @update_cmd VARCHAR(MAX)\r\n\t = 'UPDATE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' SET payload = ''' + @new_json\r\n\t + ''', sentiment = NULL, update_datetime = SYSDATETIME() AT TIME ZONE ''UTC'' AT TIME ZONE '''+@time_zone+''' WHERE file_name = ''' + @file_name + ''';';\r\n\tDECLARE @insert_cmd VARCHAR(MAX)\r\n\t = 'INSERT INTO ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' (file_name, payload, insert_datetime) \r\n\t\t\tSELECT ''' + @file_name + ''', ''' + @new_json + ''', SYSDATETIME() AT TIME ZONE ''UTC'' AT TIME ZONE '''+@time_zone+''';';\r\n\r\n\r\n DECLARE @command NVARCHAR(MAX)\r\n =\r\n (\r\n SELECT CASE\r\n WHEN @old_json IS NOT NULL AND @old_json_file_name IS NOT NULL AND @old_json_file_name = @new_json_file_name\r\n AND EXISTS\r\n (\r\n SELECT TOP (1) 1 FROM #returntable WHERE SideIndicator = '<>'\r\n ) THEN\r\n @update_cmd\r\n\t\t\t\t\t\t\tWHEN @old_json IS NOT NULL AND @old_json_file_name IS NOT NULL AND @old_json_file_name = @new_json_file_name\r\n AND NOT EXISTS\r\n (\r\n SELECT TOP (1) 1 FROM #returntable WHERE SideIndicator = '<>'\r\n ) THEN ''\r\n ELSE \r\n CASE\r\n WHEN @old_json IS NOT NULL AND @old_json_file_name IS NOT NULL AND @old_json_file_name = @new_json_file_name THEN\r\n @delete_cmd\r\n ELSE\r\n ''\r\n END\r\n + CASE\r\n WHEN @has_identity_column > 0 THEN\r\n ' SET IDENTITY_INSERT ' + QUOTENAME(@schema_name) + '.'\r\n + QUOTENAME(@table_name) + ' OFF; '\r\n ELSE\r\n ''\r\n END + @insert_cmd\r\n + CASE\r\n WHEN @has_identity_column > 0 THEN\r\n ' SET IDENTITY_INSERT ' + QUOTENAME(@schema_name) + '.'\r\n + QUOTENAME(@table_name) + ' ON '\r\n ELSE\r\n ''\r\n END\r\n END\r\n );\r\n EXEC (@command);\r\nEND;\r\n<\/pre>\nThe main part is as per lines 100-104 where SQL Server sp_invoke_external_rest_endpoint system stored procedure is used for data acquisition. We\u2019re using GET HTTP method (must be one of the following values: GET, POST, PUT, PATCH, DELETE, HEAD), passing the previously created Database Scoped Credentials in the @credential parameter and using concatenated blob URL and file name as the @url parameter. All going well, execution will return 0 if the HTTPS call was done, the HTTP code received is of 2xx status (Success) and the returned JSON in the @response parameter can be further parsed (if required) using SQL Server JSON-specific syntax.<\/p>\n
We now have our JSON file content in the target table but, as per the original requirement, we also need to ascertain client\u2019s conversation sentiment which can help us get the overall gauge on how our customers\u2019 cohort is tracking with respect to the service satisfaction. Again, previously, that would have been a laborious, if not challenging task for someone who doesn\u2019t have a lot of experience in applications integration and Azure ecosystem of services. However, now it\u2019s just a matter of provisioning Azure Cognitive Service account (something we can be easily done from Azure portal) and creating a database trigger used to execute Cognitive Services API call using the same system stored procedure we used before.<\/p>\n
Let\u2019s go ahead and save our Azure Cognitive Services authentication key as a DSC, and wrap the sp_invoke_external_rest_endpoint call in a separate stored procedure which also parses JSON payload to extract sentiment value. We will also create a database trigger to automated procedure execution and invoke it every time a record is inserted or updated.<\/p>\n
\r\n-- create database scoped credential\r\nIF EXISTS\r\n(\r\n SELECT TOP (1)\r\n 1\r\n FROM sys.database_credentials\r\n WHERE name = 'Your_Cognitive_Services_Endpoint_URL'\r\n)\r\nBEGIN\r\n DROP DATABASE SCOPED CREDENTIAL [Your_Cognitive_Services_Endpoint_URL];\r\nEND;\r\n\r\nCREATE DATABASE SCOPED CREDENTIAL [Your_Cognitive_Services_Endpoint_URL]\r\nWITH IDENTITY = 'HTTPEndpointHeaders',\r\n SECRET = '{"Ocp-Apim-Subscription-Key":"Your_Key_Value"}';\r\nGO\r\n\r\n-- create usp_run_sentiment_analysis stored procedure\r\nCREATE OR ALTER PROCEDURE [dbo].[usp_run_sentiment_analysis]\r\n(@file_id UNIQUEIDENTIFIER)\r\nAS\r\nBEGIN\r\n DECLARE @error_message VARCHAR(MAX);\r\n DECLARE @url NVARCHAR(2000) = N'https:\/\/Your_Cognitive_Services_Endpoint_URL\/text\/analytics\/v3.0\/sentiment';\r\n DECLARE @response NVARCHAR(MAX);\r\n DECLARE @json NVARCHAR(MAX) =\r\n (\r\n SELECT payload FROM [dbo].[customer_interactions] WHERE file_id = @file_id\r\n );\r\n\r\n DECLARE @customer_text NVARCHAR(MAX) =\r\n (\r\n SELECT STRING_AGG(message, ' ') AS customer_text\r\n FROM\r\n OPENJSON(@json, '$.conversation')\r\n WITH\r\n (\r\n speaker NVARCHAR(100),\r\n message NVARCHAR(MAX) '$.message'\r\n )\r\n WHERE speaker = 'Customer'\r\n );\r\n\r\n DECLARE @payload NVARCHAR(MAX)\r\n = N'{"documents": [{"id": "1", "language": "en", "text": "' + @customer_text + N'"}]}';\r\n DECLARE @headers NVARCHAR(102) = N'{"Content-Type": "application\/json"}';\r\n\r\n\r\n EXEC sp_invoke_external_rest_endpoint @url = @url,\r\n @method = 'POST',\r\n @headers = @headers,\r\n @payload = @payload,\r\n @credential = [Your_Cognitive_Services_Endpoint_URL],\r\n @response = @response OUTPUT;\r\n\r\n IF TRIM(JSON_VALUE(@response, '$.response.status.http.code')) <> '200'\r\n BEGIN\r\n SET @error_message = 'Rest call response was unsuccessful. Bailing out!';\r\n RAISERROR( @error_message, \r\n 16, \r\n 1 \r\n );\r\n RETURN;\r\n END;\r\n ELSE\r\n BEGIN\r\n UPDATE [dbo].[customer_interactions]\r\n SET sentiment =\r\n (\r\n SELECT TOP (1) JSON_VALUE(@response, '$.result.documents[0].sentiment')\r\n )\r\n WHERE file_id = @file_id;\r\n END;\r\nEND;\r\nGO\r\n\r\n-- create trigger_sentiment_analysis database trigger\r\nCREATE OR ALTER TRIGGER [dbo].[trigger_sentiment_analysis]\r\nON [dbo].[customer_interactions]\r\nAFTER INSERT, UPDATE\r\nAS\r\nBEGIN\r\n SET NOCOUNT ON;\r\n DECLARE @file_id VARCHAR(128);\r\n SELECT @file_id = inserted.file_id\r\n FROM inserted;\r\n EXEC usp_run_sentiment_analysis @file_id = @file_id;\r\nEND;\r\nGO\r\n\r\nALTER TABLE [dbo].[customer_interactions] ENABLE TRIGGER [trigger_sentiment_analysis];\r\nGO\r\n<\/pre>\nThe 3-stage logic in the above stored procedure dictates that we extract customer\u2019s text from our JSON entry, omitting everything that relates to speaker dialog, call our sentiment analysis API with this data to determine sentiment value and finally, persist it in the target table against the file_id in question. All there\u2019s left to do is to create another database trigger which activates only if the sentiment value is negative and, you guessed it, calls a stored procedure responsible for running Azure Logic App.<\/p>\n
This is our third Azure services integration using REST endpoint in SQL DB and it just goes to show how versatile this functionality is and how it opens a world of possibilities, all within the confines of the database and with little to no development required outside of T-SQL.<\/p>\n
For this part let\u2019s create a small Logic App which triggers ‘Send an email (V2)’ task when a HTTP request is received, the final stored procedure calling this workflow and a database trigger to automate execution process. Also, to make it more interesting, we\u2019ll pass customer’s feedback text and date\/time this file was created at to our email content so that whoever receives this correspondence does not have to wonder what text triggered this workflow.<\/p>\n
Our Logic App and the final piece of SQL code will look like this:<\/p>\n