samples/mssql_spark_connector_user_creation.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": [
                "# User Creation for MSQL Spark Connector\r\n",
                "MSSQL Spark connector provides an efficient write SQLServer master instance and SQL Server data pool in Big Data Clusters. To transfer data to SQL Server the MSSQL Spark Connector requires the right credentials. This notebook explains creation of the user account the permissions for use in MSSQL Spark Connector.\r\n",
                "\r\n",
                "## What permissions does MSSQL Spark connector requires.\r\n",
                "The answer depends on the capability that's used. MSSQL Spark connector can read, write, create tables in SQL Server Master instance and Data Pools in SQL Server 2019 Big Data Clusters. For using the full set of functionality the user account needs to have permissions to read, write, create tables and external tables in data pools.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4d6539fc-aa99-4cd8-a9de-357e2d6424e2"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## For non-AD cluster, run the following cells to prepare user and table:"
            ],
            "metadata": {
                "azdata_cell_guid": "9642f940-770f-4bfd-85ee-e0159ccb5689"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- Create a database named \"connector_test_db\"\r\n",
                "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'connector_test_db')\r\n",
                "BEGIN\r\n",
                "    Create DATABASE connector_test_db\r\n",
                "END\r\n",
                "\r\n",
                "-- Create a login\r\n",
                "IF NOT EXISTS (select name from sys.server_principals where name='connector_user')\r\n",
                "BEGIN\r\n",
                "    CREATE LOGIN connector_user  WITH PASSWORD ='password123!#' \r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "84932473-88cd-433e-baf7-eeee178021aa"
            },
            "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.335"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "code",
            "source": [
                "Use connector_test_db;\r\n",
                "\r\n",
                "-- Create a database user\r\n",
                "CREATE USER connector_user FROM LOGIN connector_user\r\n",
                "\r\n",
                "-- To view data pool node configuration\r\n",
                "grant VIEW DATABASE STATE to connector_user;\r\n",
                "\r\n",
                "-- To create external tables in data pools\r\n",
                "grant alter any external data source to connector_user;\r\n",
                "\r\n",
                "-- To create external table\r\n",
                "grant create table to connector_user;\r\n",
                "grant alter any schema to connector_user;\r\n",
                "\r\n",
                "ALTER ROLE [db_datareader] ADD MEMBER connector_user;\r\n",
                "ALTER ROLE [db_datawriter] ADD MEMBER connector_user;\r\n",
                "\r\n",
                "-- Create external table in data pool\r\n",
                "CREATE EXTERNAL DATA SOURCE connector_ds  WITH (LOCATION = 'sqldatapool://controller-svc/default');\r\n",
                "EXECUTE('USE connector_test_db; CREATE EXTERNAL TABLE [dummy3] ([number] int, [word] nvarchar(2048)) WITH (DATA_SOURCE = connector_ds, DISTRIBUTION = ROUND_ROBIN)')\r\n",
                "\r\n",
                "-- Create a login in data pools and Provide right permissions to this user\r\n",
                "EXECUTE( ' Use connector_test_db; CREATE LOGIN connector_user  WITH PASSWORD = ''password123!#'' ; ' )  AT  DATA_SOURCE connector_ds;\r\n",
                "EXECUTE( ' Use connector_test_db; CREATE USER  connector_user ; ALTER ROLE [db_datareader] ADD MEMBER connector_user;  ALTER ROLE [db_datawriter] ADD MEMBER connector_user ;')  AT  DATA_SOURCE connector_ds;"
            ],
            "metadata": {
                "azdata_cell_guid": "8fdf2f56-f89d-4bf9-81be-1dfb92631251"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:10.334"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "Connector also supports write when the destination table contains multiple computed columns. We will write the same data to a table that has one additional column which is computed by other columns. The table is constructed using this T-SQL. Run below cells to create the table."
            ],
            "metadata": {
                "azdata_cell_guid": "19d6797c-6b16-42d8-af98-3ad54753e6cc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- Create a table with computed column net_capotal_gain\r\n",
                "USE connector_test_db;\r\n",
                "CREATE TABLE AdultCenses_computed_col\r\n",
                "(\r\n",
                "    age int,\r\n",
                "    workclass nvarchar(max),\r\n",
                "    fnlwgt int,\r\n",
                "    education nvarchar(max),\r\n",
                "    education_num int,\r\n",
                "    marital_status nvarchar(max),\r\n",
                "    occupation nvarchar(max),\r\n",
                "    relationship nvarchar(max),\r\n",
                "    race nvarchar(max),\r\n",
                "    sex\tnvarchar(max),\r\n",
                "    capital_gain int,\r\n",
                "    capital_loss int,\r\n",
                "    hours_per_week int,\r\n",
                "    native_country nvarchar(max),\r\n",
                "    income nvarchar(max),\r\n",
                "    net_capital_gain AS (capital_gain - capital_loss)\r\n",
                ")"
            ],
            "metadata": {
                "azdata_cell_guid": "5044f72c-bac4-44ed-b97b-77272fc65152",
                "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.157"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "-- Create a table without age column\r\n",
                "USE connector_test_db;\r\n",
                "CREATE TABLE AdultCenses_schema_check\r\n",
                "(\r\n",
                "    workclass nvarchar(max),\r\n",
                "    fnlwgt int,\r\n",
                "    education nvarchar(max),\r\n",
                "    education_num int,\r\n",
                "    marital_status nvarchar(max),\r\n",
                "    occupation nvarchar(max),\r\n",
                "    relationship nvarchar(max),\r\n",
                "    race nvarchar(max),\r\n",
                "    sex\tnvarchar(max),\r\n",
                "    capital_gain int,\r\n",
                "    capital_loss int,\r\n",
                "    hours_per_week int,\r\n",
                "    native_country nvarchar(max),\r\n",
                "    income nvarchar(max),\r\n",
                "    net_capital_gain AS (capital_gain - capital_loss)\r\n",
                ")"
            ],
            "metadata": {
                "azdata_cell_guid": "c120889a-6995-4432-aae7-0519794ff8cf"
            },
            "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.156"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "## For AD cluster, run the following cells to prepare user and table:"
            ],
            "metadata": {
                "azdata_cell_guid": "11ea89a5-5f60-495d-864e-a80ef7a10510"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- Create a database named \"spark_mssql_db\"\r\n",
                "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'spark_mssql_db')\r\n",
                "BEGIN\r\n",
                "    CREATE DATABASE spark_mssql_db\r\n",
                "END\r\n",
                "\r\n",
                "-- Create a login from a domain user\r\n",
                "IF NOT EXISTS (select name from sys.server_principals where name='AZDATA\\testusera1')\r\n",
                "BEGIN\r\n",
                "    CREATE LOGIN [AZDATA\\testusera1] FROM WINDOWS\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "819012ba-db8f-4c25-9606-1801f46d60e4"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:10.921"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 18
        },
        {
            "cell_type": "code",
            "source": [
                "Use spark_mssql_db;\r\n",
                "\r\n",
                "-- Create a database user\r\n",
                "CREATE USER [AZDATA\\testusera1];\r\n",
                "\r\n",
                "-- To view data pool node configuration\r\n",
                "GRANT VIEW DATABASE STATE to [AZDATA\\testusera1];\r\n",
                "\r\n",
                "-- To create external tables in data pools\r\n",
                "grant alter any external data source to [AZDATA\\testusera1];\r\n",
                "\r\n",
                "-- To create external table\r\n",
                "grant create table to [AZDATA\\testusera1];\r\n",
                "grant alter any schema to [AZDATA\\testusera1];\r\n",
                "\r\n",
                "ALTER ROLE [db_datareader] ADD MEMBER [AZDATA\\testusera1];\r\n",
                "ALTER ROLE [db_datawriter] ADD MEMBER [AZDATA\\testusera1];\r\n",
                "\r\n",
                "-- Create external table in data pool\r\n",
                "CREATE EXTERNAL DATA SOURCE connector_ds  WITH (LOCATION = 'sqldatapool://controller-svc/default');\r\n",
                "EXECUTE('USE spark_mssql_db; CREATE EXTERNAL TABLE [dummy3] ([number] int, [word] nvarchar(2048)) WITH (DATA_SOURCE = connector_ds, DISTRIBUTION = ROUND_ROBIN)')\r\n",
                "\r\n",
                "-- Create a login in data pools and Provide right permissions to this user\r\n",
                "EXECUTE( ' USE spark_mssql_db; CREATE LOGIN [AZDATA\\testusera1]  FROM WINDOWS ' )  AT  DATA_SOURCE connector_ds;\r\n",
                "\r\n",
                "EXECUTE( ' USE spark_mssql_db; CREATE USER  [AZDATA\\testusera1] ; ALTER ROLE [db_datareader] ADD MEMBER [AZDATA\\testusera1];  ALTER ROLE [db_datawriter] ADD MEMBER [AZDATA\\testusera1] ;')  AT  DATA_SOURCE connector_ds;"
            ],
            "metadata": {
                "azdata_cell_guid": "2c853db3-e081-4229-a66f-5c248fb8eb0d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:10.628"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 19
        }
    ]
}