02-DeployAndConfigure/loaddata/LoadData.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": [
                "# Load data - Azure SQL Database\r\n",
                "\r\n",
                "In this activity, you'll get to see how you can bulk load data into Azure SQL Database.  \r\n",
                "\r\n",
                "\r\n",
                "**Set up - Attach the notebook to Azure SQL Database**   \r\n",
                "\r\n",
                "0. You should have opened this file using Azure Data Studio. If you didn't, please refer to Module 2 Activity 3 in the main Module 2 file to get set up.  \r\n",
                "1. In the bar at the top of this screen, confirm or change the \"Kernel\" to **SQL**. This determines what language the code blocks in the file are. In this case, that language is SQL.  \r\n",
                "2. For \"Attach to\", use the drop-down to select **Change Connection**. From the Recent Connections pane, you should be able to select your Azure SQL Database logical server and be sure to **select your AdventureWorks database as the database to connect to**.  \r\n",
                "\r\n",
                "Now that you're set up, you should read the text cells and \"Run\" the code cells by selecting the play button that appears in the left of a code cell when you hover over it.  \r\n",
                "> Some of the cells have been run before, this is just to show you the expected result from the testing of the labs. If you choose not to complete the labs/prerequisites, do not run any cells, just review the results.      \r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "2c06b521-aaf9-41a8-9824-f06a3fb12e2c"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "When you're bulk loading data, it has to come from somewhere. In Azure, it's very common to store or dump data into an [Azure Blob Storage](https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction) because Blob storage is optimized for storing massive amounts of unstructured data at a relatively low cost.   \r\n",
                "\r\n",
                "In this scenario, AdventureWorks is receiving store return data based on store identification number (e.g. 1, 2, etc.) This return data is being stored in `.dat` files which are then pushed into Azure Blob storage.  \r\n",
                "\r\n",
                "Within blob storage, there exists three types of resources:  \r\n",
                "* Storage account: this provides a unique namespace for a storage account, and a way to connect or access it  \r\n",
                "* Containers: these are used to organize a set of blobs. A storage account can have an unlimited number of containers  \r\n",
                "* Blobs: there are several types of blobs but we will use Block blobs that can store text and binary data that can be managed individually.  \r\n",
                "\r\n",
                "Now, once the data is in blob storage, Azure SQL needs a way to access it. You can do that by [creating an external data source](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azuresqldb-current) that has access to the Azure Storage account.  \r\n",
                "\r\n",
                "You can [control access to Azure Storage accounts](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview?toc=%2fazure%2fstorage%2fblobs%2ftoc.json#control-access-to-account-data) through Azure Active Directory, Shared Key authorization, or with a Shared access signature (SAS). The link points to more details, but we will use SAS for this exercise.  \r\n",
                "\r\n",
                "If you want to read more about how SAS works with regards to Azure Storage, please [read here](https://docs.microsoft.com/en-us/azure/storage/common/storage-sas-overview) before continuing.  \r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "8cdf43ef-a2ce-42b1-af4c-4cb2669440f3"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Step 1 - Create a table and schema**  \r\n",
                "\r\n",
                "First, we need to create a table and schema for our data to be loaded into. This is pretty straightforward, good old-fashioned T-SQL."
            ],
            "metadata": {
                "azdata_cell_guid": "234764b7-3174-401f-b105-66f22bee5ab3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF SCHEMA_ID('DataLoad') IS NULL \r\n",
                "EXEC ('CREATE SCHEMA DataLoad')\r\n",
                "\r\n",
                "CREATE TABLE DataLoad.store_returns\r\n",
                "(\r\n",
                "    sr_returned_date_sk             bigint,\r\n",
                "    sr_return_time_sk               bigint,\r\n",
                "    sr_item_sk                      bigint           ,\r\n",
                "    sr_customer_sk                  bigint,\r\n",
                "    sr_cdemo_sk                     bigint,\r\n",
                "    sr_hdemo_sk                     bigint,\r\n",
                "    sr_addr_sk                      bigint,\r\n",
                "    sr_store_sk                     bigint,\r\n",
                "    sr_reason_sk                    bigint,\r\n",
                "    sr_ticket_number                bigint           ,\r\n",
                "    sr_return_quantity              integer,\r\n",
                "    sr_return_amt                   float,\r\n",
                "    sr_return_tax                   float,\r\n",
                "    sr_return_amt_inc_tax           float,\r\n",
                "    sr_fee                          float,\r\n",
                "    sr_return_ship_cost             float,\r\n",
                "    sr_refunded_cash                float,\r\n",
                "    sr_reversed_charge              float,\r\n",
                "    sr_store_credit                 float,\r\n",
                "    sr_net_loss                     float\r\n",
                "\r\n",
                ") "
            ],
            "metadata": {
                "azdata_cell_guid": "46df44da-7cb9-48c1-b071-fee30b67d4d1"
            },
            "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.102"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Step 2 - Create a `MASTER KEY`**  \r\n",
                "\r\n",
                "Leveraging [an example in the docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15#f-importing-data-from-a-file-in-azure-blob-storage) you learn that a `MASTER KEY` is required to create a `DATABASE SCOPED CREDENTIAL` since the blob storage is not configured to allow public (anonymous) access.  \r\n",
                "\r\n",
                "So, let's first create a `MASTER KEY`"
            ],
            "metadata": {
                "azdata_cell_guid": "c66497ba-8fc8-4296-829e-562ccc6a942b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE MASTER KEY \r\n",
                "ENCRYPTION BY PASSWORD='MyComplexPassword00!';"
            ],
            "metadata": {
                "azdata_cell_guid": "65b4c01a-9bec-417b-a471-4b99c4e28cea"
            },
            "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.093"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Step 3 - Create a `DATABASE SCOPED CREDENTIAL`**\n",
                "\n",
                "A `MASTER KEY` is required to create a `DATABASE SCOPED CREDENTIAL`, which we can now create. The credential refers to the Azure blob storage account and the `data/` portion specifies the container where the store return data is located.\n",
                "\n",
                "We use `SHARED ACCESS SIGNATURE` as the identity which SQL knows how to interpret, and the secret provided is the SAS token that you can generate from the Azure blob storage account.\n",
                "\n",
                "> Note: the `?` at the beginning of the SAS token should be removed"
            ],
            "metadata": {
                "azdata_cell_guid": "68c55330-e433-4526-a62f-904660fb8adb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/]\r\n",
                "WITH IDENTITY = 'SHARED ACCESS SIGNATURE',\r\n",
                "SECRET = 'st=2020-09-28T22%3A05%3A27Z&se=2030-09-29T22%3A05%3A00Z&sp=rl&sv=2018-03-28&sr=c&sig=52WbuSIJCWyjS6IW6W0ILfIpqh4wLMXmOlifPyOetZI%3D';"
            ],
            "metadata": {
                "azdata_cell_guid": "26c0a508-595d-4ead-a680-b8ea422a8d68",
                "tags": []
            },
            "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.089"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Step 4 - Create an external data source to the container**  \r\n",
                "\r\n",
                "> Note: `LOCATION` doesn't have a trailing `/`, even through the `CREDENTIAL` does."
            ],
            "metadata": {
                "azdata_cell_guid": "3e65516c-6c05-4cc4-b8f4-310d9fe41da2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE EXTERNAL DATA SOURCE dataset\r\n",
                "WITH \r\n",
                "(\r\n",
                "    TYPE = BLOB_STORAGE,\r\n",
                "    LOCATION = 'https://azuresqlworkshopsa.blob.core.windows.net/data',\r\n",
                "    CREDENTIAL = [https://azuresqlworkshopsa.blob.core.windows.net/data/]\r\n",
                ");"
            ],
            "metadata": {
                "azdata_cell_guid": "e8e3ad86-2f58-41ef-a568-18ffc9128438"
            },
            "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.086"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Step 5 - `BULK INSERT` a single file**   \r\n",
                "\r\n",
                "You're finally ready to `BULK INSERT` one of the store return files. \r\n",
                "\r\n",
                "Run the following cell, and while it completes, review the comments."
            ],
            "metadata": {
                "azdata_cell_guid": "7d0ffa7d-660a-48c1-aa6b-2a295aff2e30"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SET NOCOUNT ON -- Reduce network traffic by stopping the message that shows the number of rows affected\r\n",
                " BULK INSERT DataLoad.store_returns -- Table you created in Step 1\r\n",
                " FROM 'dataset/store_returns/store_returns_1.dat' -- Within the container, the location of the file\r\n",
                "     WITH (\r\n",
                "\t\t\tDATA_SOURCE = 'dataset' -- Using the External data source from Step 4\r\n",
                "\t\t\t,DATAFILETYPE = 'char' \r\n",
                "\t        ,FIELDTERMINATOR = '\\|' \r\n",
                "\t        ,ROWTERMINATOR = '\\|\\n' \r\n",
                "            ,BATCHSIZE=100000 -- Reduce network traffic by inserting in batches\r\n",
                "            , TABLOCK -- Minimize number of log records for the insert operation\r\n",
                "           )"
            ],
            "metadata": {
                "azdata_cell_guid": "bcc16f9e-1fc5-4f51-8cd2-11d47da4b24d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:01:28.408"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "For now, let's check how many rows were inserted into our table:"
            ],
            "metadata": {
                "azdata_cell_guid": "96f0dcbb-7bf6-456e-b5d8-53763eae3630"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select count(*) from DataLoad.store_returns"
            ],
            "metadata": {
                "azdata_cell_guid": "36c0c58f-b0a1-4854-88d1-e576e31b37d0",
                "tags": []
            },
            "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.928"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 6,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "2807797"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>(No column name)</th></tr>",
                            "<tr><td>2807797</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you want to run throught the exercise again, run the following code to reset what you've done."
            ],
            "metadata": {
                "azdata_cell_guid": "3ab4cac4-9c02-4ee1-818d-c6cbaa54ca6e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "DROP EXTERNAL DATA SOURCE dataset\r\n",
                "DROP DATABASE SCOPED CREDENTIAL [https://azuresqlworkshopsa.blob.core.windows.net/data/]\r\n",
                "DROP TABLE DataLoad.store_returns\r\n",
                "DROP MASTER KEY"
            ],
            "metadata": {
                "azdata_cell_guid": "297d59bb-08be-4680-94e4-028161ac0b4e"
            },
            "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.093"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "You've seen how you can optimize bulk inserts in this activity. We are really just skimming the surface here, and in the Performance module you'll learn about other ways you can optimize performance. You can also check the [documentation (under **How to** > **Load and move data**)](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-load-from-csv-with-bcp) for more information.  "
            ],
            "metadata": {
                "azdata_cell_guid": "ece2733b-4152-4e47-90ea-cda0da0882cb"
            }
        }
    ]
}