azure-cognitive-services.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Call an Azure Cognitive Services\n",
                "\n",
                "Make sure to have an Azure Cognitive Service Anomaly Detector running. [Create an Anomaly Detector resource](https://portal.azure.com/#create/Microsoft.CognitiveServicesAnomalyDetector) in the Azure portal to get your key and endpoint. Wait for it to deploy and select the Go to resource button. You can use the free pricing tier (F0) to try the service, and upgrade later to a paid tier for production. For more details please refer to the documentation here: [Anomaly Detector API Documentation](https://learn.microsoft.com/en-us/azure/cognitive-services/anomaly-detector/)\n",
                "\n",
                "In the next samples is assumed that the Anomaly Detector's endpoint is available at `https://azure-sql-anomaly-detector.cognitiveservices.azure.com/`. To have the samples working in your environment make sure to use the endpoint of your Anomaly Detector"
            ],
            "metadata": {
                "azdata_cell_guid": "cba5ef59-eaf6-468e-9e78-6281339299df"
            },
            "attachments": {}
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Load sample data\r\n",
                "\r\n",
                "First thing needes is some sample data. You can use the one provided by the anomaly detection sample [Quickstart: Use the Univariate Anomaly Detector client library](https://learn.microsoft.com/en-us/azure/cognitive-services/anomaly-detector/quickstarts/client-libraries?pivots=rest-api&tabs=command-line) here: [Request body sample](https://westus2.dev.cognitive.microsoft.com/docs/services/AnomalyDetector/operations/post-timeseries-entire-detect).\r\n",
                "Using Azure SQL JSON capabilities, it is very easy to load everything into a sample table named `datapoints`:\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "efd662c7-6f56-49b3-8c4d-dab1f013ea74"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @s nvarchar(max) = N'{ \r\n",
                "  \"series\": [\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-01-01T00:00:00Z\",\r\n",
                "    \"value\": 826\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-02-01T00:00:00Z\",\r\n",
                "    \"value\": 799\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-03-01T00:00:00Z\",\r\n",
                "    \"value\": 890\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-04-01T00:00:00Z\",\r\n",
                "    \"value\": 900\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-05-01T00:00:00Z\",\r\n",
                "    \"value\": 961\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-06-01T00:00:00Z\",\r\n",
                "    \"value\": 935\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-07-01T00:00:00Z\",\r\n",
                "    \"value\": 894\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-08-01T00:00:00Z\",\r\n",
                "    \"value\": 855\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-09-01T00:00:00Z\",\r\n",
                "    \"value\": 809\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-10-01T00:00:00Z\",\r\n",
                "    \"value\": 810\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-11-01T00:00:00Z\",\r\n",
                "    \"value\": 766\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1972-12-01T00:00:00Z\",\r\n",
                "    \"value\": 805\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-01-01T00:00:00Z\",\r\n",
                "    \"value\": 821\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-02-01T00:00:00Z\",\r\n",
                "    \"value\": 773\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-03-01T00:00:00Z\",\r\n",
                "    \"value\": 883\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-04-01T00:00:00Z\",\r\n",
                "    \"value\": 898\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-05-01T00:00:00Z\",\r\n",
                "    \"value\": 957\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-06-01T00:00:00Z\",\r\n",
                "    \"value\": 924\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-07-01T00:00:00Z\",\r\n",
                "    \"value\": 881\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-08-01T00:00:00Z\",\r\n",
                "    \"value\": 837\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-09-01T00:00:00Z\",\r\n",
                "    \"value\": 784\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-10-01T00:00:00Z\",\r\n",
                "    \"value\": 791\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-11-01T00:00:00Z\",\r\n",
                "    \"value\": 760\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1973-12-01T00:00:00Z\",\r\n",
                "    \"value\": 802\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-01-01T00:00:00Z\",\r\n",
                "    \"value\": 828\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-02-01T00:00:00Z\",\r\n",
                "    \"value\": 1030\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-03-01T00:00:00Z\",\r\n",
                "    \"value\": 889\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-04-01T00:00:00Z\",\r\n",
                "    \"value\": 902\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-05-01T00:00:00Z\",\r\n",
                "    \"value\": 969\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-06-01T00:00:00Z\",\r\n",
                "    \"value\": 947\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-07-01T00:00:00Z\",\r\n",
                "    \"value\": 908\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-08-01T00:00:00Z\",\r\n",
                "    \"value\": 867\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-09-01T00:00:00Z\",\r\n",
                "    \"value\": 815\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-10-01T00:00:00Z\",\r\n",
                "    \"value\": 812\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-11-01T00:00:00Z\",\r\n",
                "    \"value\": 773\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1974-12-01T00:00:00Z\",\r\n",
                "    \"value\": 813\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-01-01T00:00:00Z\",\r\n",
                "    \"value\": 834\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-02-01T00:00:00Z\",\r\n",
                "    \"value\": 782\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-03-01T00:00:00Z\",\r\n",
                "    \"value\": 892\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-04-01T00:00:00Z\",\r\n",
                "    \"value\": 903\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-05-01T00:00:00Z\",\r\n",
                "    \"value\": 966\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-06-01T00:00:00Z\",\r\n",
                "    \"value\": 937\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-07-01T00:00:00Z\",\r\n",
                "    \"value\": 896\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-08-01T00:00:00Z\",\r\n",
                "    \"value\": 858\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-09-01T00:00:00Z\",\r\n",
                "    \"value\": 817\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-10-01T00:00:00Z\",\r\n",
                "    \"value\": 827\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-11-01T00:00:00Z\",\r\n",
                "    \"value\": 797\r\n",
                "  },\r\n",
                "  {\r\n",
                "    \"timestamp\": \"1975-12-01T00:00:00Z\",\r\n",
                "    \"value\": 843\r\n",
                "  }\r\n",
                "  ],\r\n",
                " \"maxAnomalyRatio\": 0.25,\r\n",
                " \"sensitivity\": 95,\r\n",
                " \"granularity\": \"monthly\"\r\n",
                "}';\r\n",
                "\r\n",
                "drop table if exists dbo.datapoints;\r\n",
                "create table dbo.datapoints \r\n",
                "(\r\n",
                "\tid int not null identity primary key nonclustered,\r\n",
                "\tsample_date datetime2 not null,\r\n",
                "\tsample_value numeric(18,3) not null\r\n",
                ")\t\r\n",
                ";\r\n",
                "\r\n",
                "insert into \r\n",
                "\tdbo.datapoints ([sample_date], [sample_value])\r\n",
                "select \r\n",
                "\tsample_date,\r\n",
                "\tsample_value\r\n",
                "from openjson(@s, '$.series') with \r\n",
                "\t(\r\n",
                "\t\t[sample_date] datetime2 '$.timestamp',\r\n",
                "\t\t[sample_value] numeric(18,3) '$.value'\r\n",
                "\t)\r\n",
                ";\r\n",
                "\r\n",
                "update [dbo].[datapoints] set [sample_date] = dateadd(year, 46, [sample_date]);\r\n",
                "\r\n",
                "select * from dbo.[datapoints]\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "29d317f6-0cfc-421b-ad1a-82e1afd69605",
                "language": "sql",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(48 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(48 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(48 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.107"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 1,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "sample_date"
                                    },
                                    {
                                        "name": "sample_value"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "2018-01-01 00:00:00.0000000",
                                    "2": "826.000"
                                },
                                {
                                    "0": "2",
                                    "1": "2018-02-01 00:00:00.0000000",
                                    "2": "799.000"
                                },
                                {
                                    "0": "3",
                                    "1": "2018-03-01 00:00:00.0000000",
                                    "2": "890.000"
                                },
                                {
                                    "0": "4",
                                    "1": "2018-04-01 00:00:00.0000000",
                                    "2": "900.000"
                                },
                                {
                                    "0": "5",
                                    "1": "2018-05-01 00:00:00.0000000",
                                    "2": "961.000"
                                },
                                {
                                    "0": "6",
                                    "1": "2018-06-01 00:00:00.0000000",
                                    "2": "935.000"
                                },
                                {
                                    "0": "7",
                                    "1": "2018-07-01 00:00:00.0000000",
                                    "2": "894.000"
                                },
                                {
                                    "0": "8",
                                    "1": "2018-08-01 00:00:00.0000000",
                                    "2": "855.000"
                                },
                                {
                                    "0": "9",
                                    "1": "2018-09-01 00:00:00.0000000",
                                    "2": "809.000"
                                },
                                {
                                    "0": "10",
                                    "1": "2018-10-01 00:00:00.0000000",
                                    "2": "810.000"
                                },
                                {
                                    "0": "11",
                                    "1": "2018-11-01 00:00:00.0000000",
                                    "2": "766.000"
                                },
                                {
                                    "0": "12",
                                    "1": "2018-12-01 00:00:00.0000000",
                                    "2": "805.000"
                                },
                                {
                                    "0": "13",
                                    "1": "2019-01-01 00:00:00.0000000",
                                    "2": "821.000"
                                },
                                {
                                    "0": "14",
                                    "1": "2019-02-01 00:00:00.0000000",
                                    "2": "773.000"
                                },
                                {
                                    "0": "15",
                                    "1": "2019-03-01 00:00:00.0000000",
                                    "2": "883.000"
                                },
                                {
                                    "0": "16",
                                    "1": "2019-04-01 00:00:00.0000000",
                                    "2": "898.000"
                                },
                                {
                                    "0": "17",
                                    "1": "2019-05-01 00:00:00.0000000",
                                    "2": "957.000"
                                },
                                {
                                    "0": "18",
                                    "1": "2019-06-01 00:00:00.0000000",
                                    "2": "924.000"
                                },
                                {
                                    "0": "19",
                                    "1": "2019-07-01 00:00:00.0000000",
                                    "2": "881.000"
                                },
                                {
                                    "0": "20",
                                    "1": "2019-08-01 00:00:00.0000000",
                                    "2": "837.000"
                                },
                                {
                                    "0": "21",
                                    "1": "2019-09-01 00:00:00.0000000",
                                    "2": "784.000"
                                },
                                {
                                    "0": "22",
                                    "1": "2019-10-01 00:00:00.0000000",
                                    "2": "791.000"
                                },
                                {
                                    "0": "23",
                                    "1": "2019-11-01 00:00:00.0000000",
                                    "2": "760.000"
                                },
                                {
                                    "0": "24",
                                    "1": "2019-12-01 00:00:00.0000000",
                                    "2": "802.000"
                                },
                                {
                                    "0": "25",
                                    "1": "2020-01-01 00:00:00.0000000",
                                    "2": "828.000"
                                },
                                {
                                    "0": "26",
                                    "1": "2020-02-01 00:00:00.0000000",
                                    "2": "1030.000"
                                },
                                {
                                    "0": "27",
                                    "1": "2020-03-01 00:00:00.0000000",
                                    "2": "889.000"
                                },
                                {
                                    "0": "28",
                                    "1": "2020-04-01 00:00:00.0000000",
                                    "2": "902.000"
                                },
                                {
                                    "0": "29",
                                    "1": "2020-05-01 00:00:00.0000000",
                                    "2": "969.000"
                                },
                                {
                                    "0": "30",
                                    "1": "2020-06-01 00:00:00.0000000",
                                    "2": "947.000"
                                },
                                {
                                    "0": "31",
                                    "1": "2020-07-01 00:00:00.0000000",
                                    "2": "908.000"
                                },
                                {
                                    "0": "32",
                                    "1": "2020-08-01 00:00:00.0000000",
                                    "2": "867.000"
                                },
                                {
                                    "0": "33",
                                    "1": "2020-09-01 00:00:00.0000000",
                                    "2": "815.000"
                                },
                                {
                                    "0": "34",
                                    "1": "2020-10-01 00:00:00.0000000",
                                    "2": "812.000"
                                },
                                {
                                    "0": "35",
                                    "1": "2020-11-01 00:00:00.0000000",
                                    "2": "773.000"
                                },
                                {
                                    "0": "36",
                                    "1": "2020-12-01 00:00:00.0000000",
                                    "2": "813.000"
                                },
                                {
                                    "0": "37",
                                    "1": "2021-01-01 00:00:00.0000000",
                                    "2": "834.000"
                                },
                                {
                                    "0": "38",
                                    "1": "2021-02-01 00:00:00.0000000",
                                    "2": "782.000"
                                },
                                {
                                    "0": "39",
                                    "1": "2021-03-01 00:00:00.0000000",
                                    "2": "892.000"
                                },
                                {
                                    "0": "40",
                                    "1": "2021-04-01 00:00:00.0000000",
                                    "2": "903.000"
                                },
                                {
                                    "0": "41",
                                    "1": "2021-05-01 00:00:00.0000000",
                                    "2": "966.000"
                                },
                                {
                                    "0": "42",
                                    "1": "2021-06-01 00:00:00.0000000",
                                    "2": "937.000"
                                },
                                {
                                    "0": "43",
                                    "1": "2021-07-01 00:00:00.0000000",
                                    "2": "896.000"
                                },
                                {
                                    "0": "44",
                                    "1": "2021-08-01 00:00:00.0000000",
                                    "2": "858.000"
                                },
                                {
                                    "0": "45",
                                    "1": "2021-09-01 00:00:00.0000000",
                                    "2": "817.000"
                                },
                                {
                                    "0": "46",
                                    "1": "2021-10-01 00:00:00.0000000",
                                    "2": "827.000"
                                },
                                {
                                    "0": "47",
                                    "1": "2021-11-01 00:00:00.0000000",
                                    "2": "797.000"
                                },
                                {
                                    "0": "48",
                                    "1": "2021-12-01 00:00:00.0000000",
                                    "2": "843.000"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>id</th><th>sample_date</th><th>sample_value</th></tr>",
                            "<tr><td>1</td><td>2018-01-01 00:00:00.0000000</td><td>826.000</td></tr>",
                            "<tr><td>2</td><td>2018-02-01 00:00:00.0000000</td><td>799.000</td></tr>",
                            "<tr><td>3</td><td>2018-03-01 00:00:00.0000000</td><td>890.000</td></tr>",
                            "<tr><td>4</td><td>2018-04-01 00:00:00.0000000</td><td>900.000</td></tr>",
                            "<tr><td>5</td><td>2018-05-01 00:00:00.0000000</td><td>961.000</td></tr>",
                            "<tr><td>6</td><td>2018-06-01 00:00:00.0000000</td><td>935.000</td></tr>",
                            "<tr><td>7</td><td>2018-07-01 00:00:00.0000000</td><td>894.000</td></tr>",
                            "<tr><td>8</td><td>2018-08-01 00:00:00.0000000</td><td>855.000</td></tr>",
                            "<tr><td>9</td><td>2018-09-01 00:00:00.0000000</td><td>809.000</td></tr>",
                            "<tr><td>10</td><td>2018-10-01 00:00:00.0000000</td><td>810.000</td></tr>",
                            "<tr><td>11</td><td>2018-11-01 00:00:00.0000000</td><td>766.000</td></tr>",
                            "<tr><td>12</td><td>2018-12-01 00:00:00.0000000</td><td>805.000</td></tr>",
                            "<tr><td>13</td><td>2019-01-01 00:00:00.0000000</td><td>821.000</td></tr>",
                            "<tr><td>14</td><td>2019-02-01 00:00:00.0000000</td><td>773.000</td></tr>",
                            "<tr><td>15</td><td>2019-03-01 00:00:00.0000000</td><td>883.000</td></tr>",
                            "<tr><td>16</td><td>2019-04-01 00:00:00.0000000</td><td>898.000</td></tr>",
                            "<tr><td>17</td><td>2019-05-01 00:00:00.0000000</td><td>957.000</td></tr>",
                            "<tr><td>18</td><td>2019-06-01 00:00:00.0000000</td><td>924.000</td></tr>",
                            "<tr><td>19</td><td>2019-07-01 00:00:00.0000000</td><td>881.000</td></tr>",
                            "<tr><td>20</td><td>2019-08-01 00:00:00.0000000</td><td>837.000</td></tr>",
                            "<tr><td>21</td><td>2019-09-01 00:00:00.0000000</td><td>784.000</td></tr>",
                            "<tr><td>22</td><td>2019-10-01 00:00:00.0000000</td><td>791.000</td></tr>",
                            "<tr><td>23</td><td>2019-11-01 00:00:00.0000000</td><td>760.000</td></tr>",
                            "<tr><td>24</td><td>2019-12-01 00:00:00.0000000</td><td>802.000</td></tr>",
                            "<tr><td>25</td><td>2020-01-01 00:00:00.0000000</td><td>828.000</td></tr>",
                            "<tr><td>26</td><td>2020-02-01 00:00:00.0000000</td><td>1030.000</td></tr>",
                            "<tr><td>27</td><td>2020-03-01 00:00:00.0000000</td><td>889.000</td></tr>",
                            "<tr><td>28</td><td>2020-04-01 00:00:00.0000000</td><td>902.000</td></tr>",
                            "<tr><td>29</td><td>2020-05-01 00:00:00.0000000</td><td>969.000</td></tr>",
                            "<tr><td>30</td><td>2020-06-01 00:00:00.0000000</td><td>947.000</td></tr>",
                            "<tr><td>31</td><td>2020-07-01 00:00:00.0000000</td><td>908.000</td></tr>",
                            "<tr><td>32</td><td>2020-08-01 00:00:00.0000000</td><td>867.000</td></tr>",
                            "<tr><td>33</td><td>2020-09-01 00:00:00.0000000</td><td>815.000</td></tr>",
                            "<tr><td>34</td><td>2020-10-01 00:00:00.0000000</td><td>812.000</td></tr>",
                            "<tr><td>35</td><td>2020-11-01 00:00:00.0000000</td><td>773.000</td></tr>",
                            "<tr><td>36</td><td>2020-12-01 00:00:00.0000000</td><td>813.000</td></tr>",
                            "<tr><td>37</td><td>2021-01-01 00:00:00.0000000</td><td>834.000</td></tr>",
                            "<tr><td>38</td><td>2021-02-01 00:00:00.0000000</td><td>782.000</td></tr>",
                            "<tr><td>39</td><td>2021-03-01 00:00:00.0000000</td><td>892.000</td></tr>",
                            "<tr><td>40</td><td>2021-04-01 00:00:00.0000000</td><td>903.000</td></tr>",
                            "<tr><td>41</td><td>2021-05-01 00:00:00.0000000</td><td>966.000</td></tr>",
                            "<tr><td>42</td><td>2021-06-01 00:00:00.0000000</td><td>937.000</td></tr>",
                            "<tr><td>43</td><td>2021-07-01 00:00:00.0000000</td><td>896.000</td></tr>",
                            "<tr><td>44</td><td>2021-08-01 00:00:00.0000000</td><td>858.000</td></tr>",
                            "<tr><td>45</td><td>2021-09-01 00:00:00.0000000</td><td>817.000</td></tr>",
                            "<tr><td>46</td><td>2021-10-01 00:00:00.0000000</td><td>827.000</td></tr>",
                            "<tr><td>47</td><td>2021-11-01 00:00:00.0000000</td><td>797.000</td></tr>",
                            "<tr><td>48</td><td>2021-12-01 00:00:00.0000000</td><td>843.000</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Securely store the API Key\r\n",
                "\r\n",
                "Azure Cogntives services uses a key to authenticate requests. The key must be passed in the `Ocp-Apim-Subscription-Key` request header, as described in the documentation: [Anomaly Detector API Documentation](https://westus2.dev.cognitive.microsoft.com/docs/services/AnomalyDetector/operations/post-timeseries-entire-detect).\r\n",
                "\r\n",
                "Since the authorization key is a sensitive value, it is recommended to store its value in a `DATABASE SCOPED CREDENTIAL` (more info here: [Credentials (Database Engine)](https://learn.microsoft.com/sql/relational-databases/security/authentication-access/credentials-database-engine))"
            ],
            "metadata": {
                "azdata_cell_guid": "941d1c14-daad-4ab6-8591-2efdfeb46f44"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "-- make sure a database master key exists\r\n",
                "if not exists(select * from sys.symmetric_keys where [name] = '##MS_DatabaseMasterKey##') begin\r\n",
                "    create master key encryption by password = 'LONg_Pa$$_w0rd!'\r\n",
                "end\r\n",
                "\r\n",
                "-- create database scoped credential\r\n",
                "create database scoped credential [https://azure-sql-anomaly-detector.cognitiveservices.azure.com/]\r\n",
                "with identity = 'HTTPEndpointHeaders', secret = '{\"Ocp-Apim-Subscription-Key\":\"<your-function-key-here>\"}';\r\n",
                "go"
            ],
            "metadata": {
                "azdata_cell_guid": "69ae5373-ffd7-4a44-8fb6-334a43506c59",
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Prepare a temp table to store request and response\r\n",
                "\r\n",
                "To make it easier to try out this sample, a temporary table will be used to store the request and the response payload. This is not technically needed as everything could be done using variables, but it will make it harder to split the sample in smaller steps, that are easier to follow."
            ],
            "metadata": {
                "azdata_cell_guid": "28f90aeb-ef46-4274-a9bc-3b3751510fd0"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "drop table if exists #temp;\r\n",
                "create table #temp (id int not null primary key, request nvarchar(max), response nvarchar(max));"
            ],
            "metadata": {
                "azdata_cell_guid": "dfd4b89f-f03f-4d92-902a-91b39588b73e",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.021"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Use `OPENJSON` to shape the JSON document as requested by Cognitive Services\n",
                "\n",
                "As Cognitive Services expect the receving JSON with a specific schema, as documented here: [Anomaly Detector API Documentation](https://westus2.dev.cognitive.microsoft.com/docs/services/AnomalyDetector/operations/post-timeseries-entire-detect), the data stored in the sample `datapoints` table must be converted into a JSON using the `FOR JSON` operator and then put the generated JSON into the `#temp` table for later use."
            ],
            "metadata": {
                "azdata_cell_guid": "61155428-6203-4a3d-a13f-03fafb71fc98"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @payload nvarchar(max);\r\n",
                "\r\n",
                "set @payload = (\r\n",
                "\tselect\r\n",
                "\t\tseries = json_query((\t\r\n",
                "\t\t\tselect \r\n",
                "\t\t\t\tsample_date as [timestamp],\r\n",
                "\t\t\t\tsample_value as [value]\r\n",
                "\t\t\tfrom \r\n",
                "\t\t\t\tdbo.[datapoints] as series for json path\r\n",
                "\t\t)),\r\n",
                "\t\t[T].[maxAnomalyRatio],\r\n",
                "\t\t[T].[sensitivity],\r\n",
                "\t\t[T].[granularity]\r\n",
                "\tfrom\r\n",
                "\t\t(values (0.25, 95, 'monthly')) T ([maxAnomalyRatio], [sensitivity], [granularity])\r\n",
                "\tfor\r\n",
                "\t\tjson path, without_array_wrapper\r\n",
                ");\r\n",
                "\r\n",
                "delete from #temp;\r\n",
                "insert into #temp (id, request) values (1, @payload);\r\n",
                "select request from #temp where id = 1;"
            ],
            "metadata": {
                "azdata_cell_guid": "6d978928-a94b-46ce-a692-fea6b2ab0d79",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.024"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 7,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "request"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "{\"series\":[{\"timestamp\":\"2018-01-01T00:00:00\",\"value\":826.000},{\"timestamp\":\"2018-02-01T00:00:00\",\"value\":799.000},{\"timestamp\":\"2018-03-01T00:00:00\",\"value\":890.000},{\"timestamp\":\"2018-04-01T00:00:00\",\"value\":900.000},{\"timestamp\":\"2018-05-01T00:00:00\",\"value\":961.000},{\"timestamp\":\"2018-06-01T00:00:00\",\"value\":935.000},{\"timestamp\":\"2018-07-01T00:00:00\",\"value\":894.000},{\"timestamp\":\"2018-08-01T00:00:00\",\"value\":855.000},{\"timestamp\":\"2018-09-01T00:00:00\",\"value\":809.000},{\"timestamp\":\"2018-10-01T00:00:00\",\"value\":810.000},{\"timestamp\":\"2018-11-01T00:00:00\",\"value\":766.000},{\"timestamp\":\"2018-12-01T00:00:00\",\"value\":805.000},{\"timestamp\":\"2019-01-01T00:00:00\",\"value\":821.000},{\"timestamp\":\"2019-02-01T00:00:00\",\"value\":773.000},{\"timestamp\":\"2019-03-01T00:00:00\",\"value\":883.000},{\"timestamp\":\"2019-04-01T00:00:00\",\"value\":898.000},{\"timestamp\":\"2019-05-01T00:00:00\",\"value\":957.000},{\"timestamp\":\"2019-06-01T00:00:00\",\"value\":924.000},{\"timestamp\":\"2019-07-01T00:00:00\",\"value\":881.000},{\"timestamp\":\"2019-08-01T00:00:00\",\"value\":837.000},{\"timestamp\":\"2019-09-01T00:00:00\",\"value\":784.000},{\"timestamp\":\"2019-10-01T00:00:00\",\"value\":791.000},{\"timestamp\":\"2019-11-01T00:00:00\",\"value\":760.000},{\"timestamp\":\"2019-12-01T00:00:00\",\"value\":802.000},{\"timestamp\":\"2020-01-01T00:00:00\",\"value\":828.000},{\"timestamp\":\"2020-02-01T00:00:00\",\"value\":1030.000},{\"timestamp\":\"2020-03-01T00:00:00\",\"value\":889.000},{\"timestamp\":\"2020-04-01T00:00:00\",\"value\":902.000},{\"timestamp\":\"2020-05-01T00:00:00\",\"value\":969.000},{\"timestamp\":\"2020-06-01T00:00:00\",\"value\":947.000},{\"timestamp\":\"2020-07-01T00:00:00\",\"value\":908.000},{\"timestamp\":\"2020-08-01T00:00:00\",\"value\":867.000},{\"timestamp\":\"2020-09-01T00:00:00\",\"value\":815.000},{\"timestamp\":\"2020-10-01T00:00:00\",\"value\":812.000},{\"timestamp\":\"2020-11-01T00:00:00\",\"value\":773.000},{\"timestamp\":\"2020-12-01T00:00:00\",\"value\":813.000},{\"timestamp\":\"2021-01-01T00:00:00\",\"value\":834.000},{\"timestamp\":\"2021-02-01T00:00:00\",\"value\":782.000},{\"timestamp\":\"2021-03-01T00:00:00\",\"value\":892.000},{\"timestamp\":\"2021-04-01T00:00:00\",\"value\":903.000},{\"timestamp\":\"2021-05-01T00:00:00\",\"value\":966.000},{\"timestamp\":\"2021-06-01T00:00:00\",\"value\":937.000},{\"timestamp\":\"2021-07-01T00:00:00\",\"value\":896.000},{\"timestamp\":\"2021-08-01T00:00:00\",\"value\":858.000},{\"timestamp\":\"2021-09-01T00:00:00\",\"value\":817.000},{\"timestamp\":\"2021-10-01T00:00:00\",\"value\":827.000},{\"timestamp\":\"2021-11-01T00:00:00\",\"value\":797.000},{\"timestamp\":\"2021-12-01T00:00:00\",\"value\":843.000}],\"maxAnomalyRatio\":0.25,\"sensitivity\":95,\"granularity\":\"monthly\"}"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>request</th></tr>",
                            "<tr><td>{&quot;series&quot;:[{&quot;timestamp&quot;:&quot;2018-01-01T00:00:00&quot;,&quot;value&quot;:826.000},{&quot;timestamp&quot;:&quot;2018-02-01T00:00:00&quot;,&quot;value&quot;:799.000},{&quot;timestamp&quot;:&quot;2018-03-01T00:00:00&quot;,&quot;value&quot;:890.000},{&quot;timestamp&quot;:&quot;2018-04-01T00:00:00&quot;,&quot;value&quot;:900.000},{&quot;timestamp&quot;:&quot;2018-05-01T00:00:00&quot;,&quot;value&quot;:961.000},{&quot;timestamp&quot;:&quot;2018-06-01T00:00:00&quot;,&quot;value&quot;:935.000},{&quot;timestamp&quot;:&quot;2018-07-01T00:00:00&quot;,&quot;value&quot;:894.000},{&quot;timestamp&quot;:&quot;2018-08-01T00:00:00&quot;,&quot;value&quot;:855.000},{&quot;timestamp&quot;:&quot;2018-09-01T00:00:00&quot;,&quot;value&quot;:809.000},{&quot;timestamp&quot;:&quot;2018-10-01T00:00:00&quot;,&quot;value&quot;:810.000},{&quot;timestamp&quot;:&quot;2018-11-01T00:00:00&quot;,&quot;value&quot;:766.000},{&quot;timestamp&quot;:&quot;2018-12-01T00:00:00&quot;,&quot;value&quot;:805.000},{&quot;timestamp&quot;:&quot;2019-01-01T00:00:00&quot;,&quot;value&quot;:821.000},{&quot;timestamp&quot;:&quot;2019-02-01T00:00:00&quot;,&quot;value&quot;:773.000},{&quot;timestamp&quot;:&quot;2019-03-01T00:00:00&quot;,&quot;value&quot;:883.000},{&quot;timestamp&quot;:&quot;2019-04-01T00:00:00&quot;,&quot;value&quot;:898.000},{&quot;timestamp&quot;:&quot;2019-05-01T00:00:00&quot;,&quot;value&quot;:957.000},{&quot;timestamp&quot;:&quot;2019-06-01T00:00:00&quot;,&quot;value&quot;:924.000},{&quot;timestamp&quot;:&quot;2019-07-01T00:00:00&quot;,&quot;value&quot;:881.000},{&quot;timestamp&quot;:&quot;2019-08-01T00:00:00&quot;,&quot;value&quot;:837.000},{&quot;timestamp&quot;:&quot;2019-09-01T00:00:00&quot;,&quot;value&quot;:784.000},{&quot;timestamp&quot;:&quot;2019-10-01T00:00:00&quot;,&quot;value&quot;:791.000},{&quot;timestamp&quot;:&quot;2019-11-01T00:00:00&quot;,&quot;value&quot;:760.000},{&quot;timestamp&quot;:&quot;2019-12-01T00:00:00&quot;,&quot;value&quot;:802.000},{&quot;timestamp&quot;:&quot;2020-01-01T00:00:00&quot;,&quot;value&quot;:828.000},{&quot;timestamp&quot;:&quot;2020-02-01T00:00:00&quot;,&quot;value&quot;:1030.000},{&quot;timestamp&quot;:&quot;2020-03-01T00:00:00&quot;,&quot;value&quot;:889.000},{&quot;timestamp&quot;:&quot;2020-04-01T00:00:00&quot;,&quot;value&quot;:902.000},{&quot;timestamp&quot;:&quot;2020-05-01T00:00:00&quot;,&quot;value&quot;:969.000},{&quot;timestamp&quot;:&quot;2020-06-01T00:00:00&quot;,&quot;value&quot;:947.000},{&quot;timestamp&quot;:&quot;2020-07-01T00:00:00&quot;,&quot;value&quot;:908.000},{&quot;timestamp&quot;:&quot;2020-08-01T00:00:00&quot;,&quot;value&quot;:867.000},{&quot;timestamp&quot;:&quot;2020-09-01T00:00:00&quot;,&quot;value&quot;:815.000},{&quot;timestamp&quot;:&quot;2020-10-01T00:00:00&quot;,&quot;value&quot;:812.000},{&quot;timestamp&quot;:&quot;2020-11-01T00:00:00&quot;,&quot;value&quot;:773.000},{&quot;timestamp&quot;:&quot;2020-12-01T00:00:00&quot;,&quot;value&quot;:813.000},{&quot;timestamp&quot;:&quot;2021-01-01T00:00:00&quot;,&quot;value&quot;:834.000},{&quot;timestamp&quot;:&quot;2021-02-01T00:00:00&quot;,&quot;value&quot;:782.000},{&quot;timestamp&quot;:&quot;2021-03-01T00:00:00&quot;,&quot;value&quot;:892.000},{&quot;timestamp&quot;:&quot;2021-04-01T00:00:00&quot;,&quot;value&quot;:903.000},{&quot;timestamp&quot;:&quot;2021-05-01T00:00:00&quot;,&quot;value&quot;:966.000},{&quot;timestamp&quot;:&quot;2021-06-01T00:00:00&quot;,&quot;value&quot;:937.000},{&quot;timestamp&quot;:&quot;2021-07-01T00:00:00&quot;,&quot;value&quot;:896.000},{&quot;timestamp&quot;:&quot;2021-08-01T00:00:00&quot;,&quot;value&quot;:858.000},{&quot;timestamp&quot;:&quot;2021-09-01T00:00:00&quot;,&quot;value&quot;:817.000},{&quot;timestamp&quot;:&quot;2021-10-01T00:00:00&quot;,&quot;value&quot;:827.000},{&quot;timestamp&quot;:&quot;2021-11-01T00:00:00&quot;,&quot;value&quot;:797.000},{&quot;timestamp&quot;:&quot;2021-12-01T00:00:00&quot;,&quot;value&quot;:843.000}],&quot;maxAnomalyRatio&quot;:0.25,&quot;sensitivity&quot;:95,&quot;granularity&quot;:&quot;monthly&quot;}</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Invoke Anomaly Detector's API from Azure SQL\r\n",
                "\r\n",
                "It is now possible to invoke the Cognitive Service API to perform anomaly detection using `sp_invoke_external_rest_endpoint`. The result is stored back into the `#temp` table created before."
            ],
            "metadata": {
                "azdata_cell_guid": "8b15e26b-4f9e-449f-9ed3-3e6af6396176"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "declare @payload nvarchar(max) = (select request from #temp where id = 1);\r\n",
                "declare @ret int, @response nvarchar(max);\r\n",
                "\r\n",
                "exec @ret = sp_invoke_external_rest_endpoint \r\n",
                "\t@url = 'https://azure-sql-anomaly-detector.cognitiveservices.azure.com/anomalydetector/v1.0/timeseries/entire/detect',\r\n",
                "\t@credential = [https://azure-sql-anomaly-detector.cognitiveservices.azure.com/],\r\n",
                "\t@payload = @payload,\r\n",
                "\t@response = @response output;\r\n",
                "\t\r\n",
                "update #temp set response = @response where id = 1;\r\n",
                "select * from #temp where id = 1;"
            ],
            "metadata": {
                "azdata_cell_guid": "4f670f18-028d-425b-8bf8-b590f200dc7d",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.215"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 8,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "request"
                                    },
                                    {
                                        "name": "response"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "{\"series\":[{\"timestamp\":\"2018-01-01T00:00:00\",\"value\":826.000},{\"timestamp\":\"2018-02-01T00:00:00\",\"value\":799.000},{\"timestamp\":\"2018-03-01T00:00:00\",\"value\":890.000},{\"timestamp\":\"2018-04-01T00:00:00\",\"value\":900.000},{\"timestamp\":\"2018-05-01T00:00:00\",\"value\":961.000},{\"timestamp\":\"2018-06-01T00:00:00\",\"value\":935.000},{\"timestamp\":\"2018-07-01T00:00:00\",\"value\":894.000},{\"timestamp\":\"2018-08-01T00:00:00\",\"value\":855.000},{\"timestamp\":\"2018-09-01T00:00:00\",\"value\":809.000},{\"timestamp\":\"2018-10-01T00:00:00\",\"value\":810.000},{\"timestamp\":\"2018-11-01T00:00:00\",\"value\":766.000},{\"timestamp\":\"2018-12-01T00:00:00\",\"value\":805.000},{\"timestamp\":\"2019-01-01T00:00:00\",\"value\":821.000},{\"timestamp\":\"2019-02-01T00:00:00\",\"value\":773.000},{\"timestamp\":\"2019-03-01T00:00:00\",\"value\":883.000},{\"timestamp\":\"2019-04-01T00:00:00\",\"value\":898.000},{\"timestamp\":\"2019-05-01T00:00:00\",\"value\":957.000},{\"timestamp\":\"2019-06-01T00:00:00\",\"value\":924.000},{\"timestamp\":\"2019-07-01T00:00:00\",\"value\":881.000},{\"timestamp\":\"2019-08-01T00:00:00\",\"value\":837.000},{\"timestamp\":\"2019-09-01T00:00:00\",\"value\":784.000},{\"timestamp\":\"2019-10-01T00:00:00\",\"value\":791.000},{\"timestamp\":\"2019-11-01T00:00:00\",\"value\":760.000},{\"timestamp\":\"2019-12-01T00:00:00\",\"value\":802.000},{\"timestamp\":\"2020-01-01T00:00:00\",\"value\":828.000},{\"timestamp\":\"2020-02-01T00:00:00\",\"value\":1030.000},{\"timestamp\":\"2020-03-01T00:00:00\",\"value\":889.000},{\"timestamp\":\"2020-04-01T00:00:00\",\"value\":902.000},{\"timestamp\":\"2020-05-01T00:00:00\",\"value\":969.000},{\"timestamp\":\"2020-06-01T00:00:00\",\"value\":947.000},{\"timestamp\":\"2020-07-01T00:00:00\",\"value\":908.000},{\"timestamp\":\"2020-08-01T00:00:00\",\"value\":867.000},{\"timestamp\":\"2020-09-01T00:00:00\",\"value\":815.000},{\"timestamp\":\"2020-10-01T00:00:00\",\"value\":812.000},{\"timestamp\":\"2020-11-01T00:00:00\",\"value\":773.000},{\"timestamp\":\"2020-12-01T00:00:00\",\"value\":813.000},{\"timestamp\":\"2021-01-01T00:00:00\",\"value\":834.000},{\"timestamp\":\"2021-02-01T00:00:00\",\"value\":782.000},{\"timestamp\":\"2021-03-01T00:00:00\",\"value\":892.000},{\"timestamp\":\"2021-04-01T00:00:00\",\"value\":903.000},{\"timestamp\":\"2021-05-01T00:00:00\",\"value\":966.000},{\"timestamp\":\"2021-06-01T00:00:00\",\"value\":937.000},{\"timestamp\":\"2021-07-01T00:00:00\",\"value\":896.000},{\"timestamp\":\"2021-08-01T00:00:00\",\"value\":858.000},{\"timestamp\":\"2021-09-01T00:00:00\",\"value\":817.000},{\"timestamp\":\"2021-10-01T00:00:00\",\"value\":827.000},{\"timestamp\":\"2021-11-01T00:00:00\",\"value\":797.000},{\"timestamp\":\"2021-12-01T00:00:00\",\"value\":843.000}],\"maxAnomalyRatio\":0.25,\"sensitivity\":95,\"granularity\":\"monthly\"}",
                                    "2": "{\"response\":{\"status\":{\"http\":{\"code\":200,\"description\":\"\"}},\"headers\":{\"Date\":\"Fri, 03 Feb 2023 19:11:07 GMT\",\"Content-Length\":\"3582\",\"Content-Type\":\"application\\/json\",\"csp-billing-usage\":\"CognitiveServices.AnomalyDetector.DataPoints=1\",\"model-id\":\"10\",\"x-envoy-upstream-service-time\":\"39\",\"apim-request-id\":\"68498343-e890-401a-a7ee-09cd360e28f2\",\"strict-transport-security\":\"max-age=31536000; includeSubDomains; preload\",\"x-content-type-options\":\"nosniff\",\"x-ms-region\":\"East US\"}},\"result\":{\"expectedValues\":[827.7940908243968,798.9133774671927,888.6058431807189,900.5606407986661,962.8389426378304,933.2591606306954,891.0784104799666,856.1781601363697,809.8987227908941,807.375129007505,764.3196682448518,803.933498594564,823.5900620883058,794.0905641334288,883.164245249282,894.8419000690953,956.8430591101258,927.6285055190114,885.812983784303,851.7622285698933,806.3322863536049,804.8024303608446,762.74070738882,804.0251702513732,825.3523662579559,798.0404188724976,889.3016505577698,902.4226124345937,965.867078532635,937.2113627931791,895.9546789101294,862.0087368413656,816.4662342097423,814.4297745524709,771.8614479159354,811.859271346729,831.8998279215521,802.947544797165,892.5684407435083,904.5488214533809,966.8527063844707,937.3168391003043,895.3975195019448,860.7889417178712,814.801176931919,812.7134983694949,770.0939528278067,810.3738137939964],\"isAnomaly\":[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],\"isNegativeAnomaly\":[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],\"isPositiveAnomaly\":[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],\"lowerMargins\":[41.389704541219885,38.91337746719273,44.43029215903596,45.02803203993335,48.14194713189147,46.6629580315348,44.553920523998386,42.808908006818456,40.494936139544734,40.36875645037526,4.319668244851755,40.19667492972815,41.17950310441529,34.09056413342876,44.158212262464076,44.74209500345478,47.84215295550632,46.38142527595062,44.29064918921517,42.5881114284947,40.316614317680205,40.2401215180422,2.7407073888200557,40.20125851256864,41.26761831289775,38.04041887249764,44.46508252788851,45.12113062172966,48.29335392663177,46.860568139658994,44.79773394550648,43.100436842068234,40.82331171048713,40.72148872762352,11.8614479159354,40.59296356733648,41.5949913960776,40.1473772398582,44.62842203717537,45.22744107266908,48.34263531922352,46.865841955015185,44.76987597509719,43.039447085893585,40.74005884659596,40.635674918474706,10.093952827806675,40.51869068969984],\"period\":12,\"upperMargins\":[41.389704541219885,39.94566887335964,44.43029215903596,45.02803203993335,48.14194713189147,46.6629580315348,44.553920523998386,42.808908006818456,40.494936139544734,40.36875645037526,38.21598341224262,40.19667492972815,41.17950310441529,39.7045282066714,44.158212262464076,44.74209500345478,47.84215295550632,46.38142527595062,44.29064918921517,42.5881114284947,40.316614317680205,40.2401215180422,38.13703536944104,40.20125851256864,41.26761831289775,39.90202094362485,44.46508252788851,45.12113062172966,48.29335392663177,46.860568139658994,44.79773394550648,43.100436842068234,40.82331171048713,40.72148872762352,38.59307239579675,40.59296356733648,41.5949913960776,40.1473772398582,44.62842203717537,45.22744107266908,48.34263531922352,46.865841955015185,44.76987597509719,43.039447085893585,40.74005884659596,40.635674918474706,38.504697641390294,40.51869068969984]}}"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>id</th><th>request</th><th>response</th></tr>",
                            "<tr><td>1</td><td>{&quot;series&quot;:[{&quot;timestamp&quot;:&quot;2018-01-01T00:00:00&quot;,&quot;value&quot;:826.000},{&quot;timestamp&quot;:&quot;2018-02-01T00:00:00&quot;,&quot;value&quot;:799.000},{&quot;timestamp&quot;:&quot;2018-03-01T00:00:00&quot;,&quot;value&quot;:890.000},{&quot;timestamp&quot;:&quot;2018-04-01T00:00:00&quot;,&quot;value&quot;:900.000},{&quot;timestamp&quot;:&quot;2018-05-01T00:00:00&quot;,&quot;value&quot;:961.000},{&quot;timestamp&quot;:&quot;2018-06-01T00:00:00&quot;,&quot;value&quot;:935.000},{&quot;timestamp&quot;:&quot;2018-07-01T00:00:00&quot;,&quot;value&quot;:894.000},{&quot;timestamp&quot;:&quot;2018-08-01T00:00:00&quot;,&quot;value&quot;:855.000},{&quot;timestamp&quot;:&quot;2018-09-01T00:00:00&quot;,&quot;value&quot;:809.000},{&quot;timestamp&quot;:&quot;2018-10-01T00:00:00&quot;,&quot;value&quot;:810.000},{&quot;timestamp&quot;:&quot;2018-11-01T00:00:00&quot;,&quot;value&quot;:766.000},{&quot;timestamp&quot;:&quot;2018-12-01T00:00:00&quot;,&quot;value&quot;:805.000},{&quot;timestamp&quot;:&quot;2019-01-01T00:00:00&quot;,&quot;value&quot;:821.000},{&quot;timestamp&quot;:&quot;2019-02-01T00:00:00&quot;,&quot;value&quot;:773.000},{&quot;timestamp&quot;:&quot;2019-03-01T00:00:00&quot;,&quot;value&quot;:883.000},{&quot;timestamp&quot;:&quot;2019-04-01T00:00:00&quot;,&quot;value&quot;:898.000},{&quot;timestamp&quot;:&quot;2019-05-01T00:00:00&quot;,&quot;value&quot;:957.000},{&quot;timestamp&quot;:&quot;2019-06-01T00:00:00&quot;,&quot;value&quot;:924.000},{&quot;timestamp&quot;:&quot;2019-07-01T00:00:00&quot;,&quot;value&quot;:881.000},{&quot;timestamp&quot;:&quot;2019-08-01T00:00:00&quot;,&quot;value&quot;:837.000},{&quot;timestamp&quot;:&quot;2019-09-01T00:00:00&quot;,&quot;value&quot;:784.000},{&quot;timestamp&quot;:&quot;2019-10-01T00:00:00&quot;,&quot;value&quot;:791.000},{&quot;timestamp&quot;:&quot;2019-11-01T00:00:00&quot;,&quot;value&quot;:760.000},{&quot;timestamp&quot;:&quot;2019-12-01T00:00:00&quot;,&quot;value&quot;:802.000},{&quot;timestamp&quot;:&quot;2020-01-01T00:00:00&quot;,&quot;value&quot;:828.000},{&quot;timestamp&quot;:&quot;2020-02-01T00:00:00&quot;,&quot;value&quot;:1030.000},{&quot;timestamp&quot;:&quot;2020-03-01T00:00:00&quot;,&quot;value&quot;:889.000},{&quot;timestamp&quot;:&quot;2020-04-01T00:00:00&quot;,&quot;value&quot;:902.000},{&quot;timestamp&quot;:&quot;2020-05-01T00:00:00&quot;,&quot;value&quot;:969.000},{&quot;timestamp&quot;:&quot;2020-06-01T00:00:00&quot;,&quot;value&quot;:947.000},{&quot;timestamp&quot;:&quot;2020-07-01T00:00:00&quot;,&quot;value&quot;:908.000},{&quot;timestamp&quot;:&quot;2020-08-01T00:00:00&quot;,&quot;value&quot;:867.000},{&quot;timestamp&quot;:&quot;2020-09-01T00:00:00&quot;,&quot;value&quot;:815.000},{&quot;timestamp&quot;:&quot;2020-10-01T00:00:00&quot;,&quot;value&quot;:812.000},{&quot;timestamp&quot;:&quot;2020-11-01T00:00:00&quot;,&quot;value&quot;:773.000},{&quot;timestamp&quot;:&quot;2020-12-01T00:00:00&quot;,&quot;value&quot;:813.000},{&quot;timestamp&quot;:&quot;2021-01-01T00:00:00&quot;,&quot;value&quot;:834.000},{&quot;timestamp&quot;:&quot;2021-02-01T00:00:00&quot;,&quot;value&quot;:782.000},{&quot;timestamp&quot;:&quot;2021-03-01T00:00:00&quot;,&quot;value&quot;:892.000},{&quot;timestamp&quot;:&quot;2021-04-01T00:00:00&quot;,&quot;value&quot;:903.000},{&quot;timestamp&quot;:&quot;2021-05-01T00:00:00&quot;,&quot;value&quot;:966.000},{&quot;timestamp&quot;:&quot;2021-06-01T00:00:00&quot;,&quot;value&quot;:937.000},{&quot;timestamp&quot;:&quot;2021-07-01T00:00:00&quot;,&quot;value&quot;:896.000},{&quot;timestamp&quot;:&quot;2021-08-01T00:00:00&quot;,&quot;value&quot;:858.000},{&quot;timestamp&quot;:&quot;2021-09-01T00:00:00&quot;,&quot;value&quot;:817.000},{&quot;timestamp&quot;:&quot;2021-10-01T00:00:00&quot;,&quot;value&quot;:827.000},{&quot;timestamp&quot;:&quot;2021-11-01T00:00:00&quot;,&quot;value&quot;:797.000},{&quot;timestamp&quot;:&quot;2021-12-01T00:00:00&quot;,&quot;value&quot;:843.000}],&quot;maxAnomalyRatio&quot;:0.25,&quot;sensitivity&quot;:95,&quot;granularity&quot;:&quot;monthly&quot;}</td><td>{&quot;response&quot;:{&quot;status&quot;:{&quot;http&quot;:{&quot;code&quot;:200,&quot;description&quot;:&quot;&quot;}},&quot;headers&quot;:{&quot;Date&quot;:&quot;Fri, 03 Feb 2023 19:11:07 GMT&quot;,&quot;Content-Length&quot;:&quot;3582&quot;,&quot;Content-Type&quot;:&quot;application\\/json&quot;,&quot;csp-billing-usage&quot;:&quot;CognitiveServices.AnomalyDetector.DataPoints=1&quot;,&quot;model-id&quot;:&quot;10&quot;,&quot;x-envoy-upstream-service-time&quot;:&quot;39&quot;,&quot;apim-request-id&quot;:&quot;68498343-e890-401a-a7ee-09cd360e28f2&quot;,&quot;strict-transport-security&quot;:&quot;max-age=31536000; includeSubDomains; preload&quot;,&quot;x-content-type-options&quot;:&quot;nosniff&quot;,&quot;x-ms-region&quot;:&quot;East US&quot;}},&quot;result&quot;:{&quot;expectedValues&quot;:[827.7940908243968,798.9133774671927,888.6058431807189,900.5606407986661,962.8389426378304,933.2591606306954,891.0784104799666,856.1781601363697,809.8987227908941,807.375129007505,764.3196682448518,803.933498594564,823.5900620883058,794.0905641334288,883.164245249282,894.8419000690953,956.8430591101258,927.6285055190114,885.812983784303,851.7622285698933,806.3322863536049,804.8024303608446,762.74070738882,804.0251702513732,825.3523662579559,798.0404188724976,889.3016505577698,902.4226124345937,965.867078532635,937.2113627931791,895.9546789101294,862.0087368413656,816.4662342097423,814.4297745524709,771.8614479159354,811.859271346729,831.8998279215521,802.947544797165,892.5684407435083,904.5488214533809,966.8527063844707,937.3168391003043,895.3975195019448,860.7889417178712,814.801176931919,812.7134983694949,770.0939528278067,810.3738137939964],&quot;isAnomaly&quot;:[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],&quot;isNegativeAnomaly&quot;:[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],&quot;isPositiveAnomaly&quot;:[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false],&quot;lowerMargins&quot;:[41.389704541219885,38.91337746719273,44.43029215903596,45.02803203993335,48.14194713189147,46.6629580315348,44.553920523998386,42.808908006818456,40.494936139544734,40.36875645037526,4.319668244851755,40.19667492972815,41.17950310441529,34.09056413342876,44.158212262464076,44.74209500345478,47.84215295550632,46.38142527595062,44.29064918921517,42.5881114284947,40.316614317680205,40.2401215180422,2.7407073888200557,40.20125851256864,41.26761831289775,38.04041887249764,44.46508252788851,45.12113062172966,48.29335392663177,46.860568139658994,44.79773394550648,43.100436842068234,40.82331171048713,40.72148872762352,11.8614479159354,40.59296356733648,41.5949913960776,40.1473772398582,44.62842203717537,45.22744107266908,48.34263531922352,46.865841955015185,44.76987597509719,43.039447085893585,40.74005884659596,40.635674918474706,10.093952827806675,40.51869068969984],&quot;period&quot;:12,&quot;upperMargins&quot;:[41.389704541219885,39.94566887335964,44.43029215903596,45.02803203993335,48.14194713189147,46.6629580315348,44.553920523998386,42.808908006818456,40.494936139544734,40.36875645037526,38.21598341224262,40.19667492972815,41.17950310441529,39.7045282066714,44.158212262464076,44.74209500345478,47.84215295550632,46.38142527595062,44.29064918921517,42.5881114284947,40.316614317680205,40.2401215180422,38.13703536944104,40.20125851256864,41.26761831289775,39.90202094362485,44.46508252788851,45.12113062172966,48.29335392663177,46.860568139658994,44.79773394550648,43.100436842068234,40.82331171048713,40.72148872762352,38.59307239579675,40.59296356733648,41.5949913960776,40.1473772398582,44.62842203717537,45.22744107266908,48.34263531922352,46.865841955015185,44.76987597509719,43.039447085893585,40.74005884659596,40.635674918474706,38.504697641390294,40.51869068969984]}}</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Join result with stored data to identify anomalies\r\n",
                "\r\n",
                "Now that the result is available, it is possible to extract the received results and join them with the original `datapoints` table to see what values are considered anomalies."
            ],
            "metadata": {
                "azdata_cell_guid": "d48993a2-9cc2-4aa2-b7b4-692773b6aecb"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "with \r\n",
                "\tev as (select [key], [value] from #temp cross apply openjson(response, '$.result.expectedValues') where id = 1)\r\n",
                ", \ta as (select [key], [value] from #temp cross apply openjson(response, '$.result.isAnomaly') where id = 1)\r\n",
                "select\r\n",
                "\td.id\r\n",
                ",\td.sample_date\r\n",
                ",\td.sample_value\r\n",
                ",\tcast(ev.[value] as numeric(18,6)) [expectedValues]\r\n",
                ",\tcast(a.[value] as bit) as isAnomaly\r\n",
                "from\r\n",
                "\tdbo.[datapoints] as d\r\n",
                "inner join\r\n",
                "\tev on [ev].[key] = [d].[id] - 1\r\n",
                "inner join \r\n",
                "\ta on [a].[key] = [ev].[key]\r\n",
                "where   \r\n",
                "    a.[value] = 'true'\r\n",
                "order by\r\n",
                "\t[d].[id]\r\n",
                "\t"
            ],
            "metadata": {
                "azdata_cell_guid": "fe17c608-eff3-43ee-9691-af5606543a5f",
                "language": "sql"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.041"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 16,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "id"
                                    },
                                    {
                                        "name": "sample_date"
                                    },
                                    {
                                        "name": "sample_value"
                                    },
                                    {
                                        "name": "expectedValues"
                                    },
                                    {
                                        "name": "isAnomaly"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "26",
                                    "1": "2020-02-01 00:00:00.0000000",
                                    "2": "1030.000",
                                    "3": "798.040419",
                                    "4": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>id</th><th>sample_date</th><th>sample_value</th><th>expectedValues</th><th>isAnomaly</th></tr>",
                            "<tr><td>26</td><td>2020-02-01 00:00:00.0000000</td><td>1030.000</td><td>798.040419</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 16
        }
    ]
}