samples/PySpark Connector with Big Data Clusters.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "pysparkkernel",
            "display_name": "PySpark"
        },
        "language_info": {
            "name": "pyspark",
            "mimetype": "text/x-python",
            "codemirror_mode": {
                "name": "python",
                "version": 3
            },
            "pygments_lexer": "python3"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Read and Write from Spark to SQL using the Apache Spark Connector for SQL Server and Azure SQL\r\n",
                "A typical big data scenario a key usage pattern is high volume, velocity and variety data processing in Spark followed with batch or streaming writes to SQL for access to LOB applications. These usage patterns greatly benefit from a connector that utilizes key SQL optimizations and provides an efficient write to SQLServer master instance and SQL Server data pool in Big Data Clusters.\r\n",
                "\r\n",
                "The Apache Spark Connector for SQL Server and Azure SQL provides an efficient write SQLServer master instance and SQL Server data pool in Big Data Clusters.\r\n",
                "\r\n",
                "Usage\r\n",
                "----\r\n",
                "- Familiar Spark DataSource V1 interface\r\n",
                "- Referenced by fully qualified name \"com.microsoft.sqlserver.jdbc.spark\"\r\n",
                "- Use from supported Spark language bindings ( Python, Scala, Java, R)\r\n",
                "- Optionally pass Bulk Copy parameters \r\n",
                "\r\n",
                "** Note : The image here may not be visible dues to markdown bug. Please change path here to full path to view the image.\r\n",
                "<img src =\r\n",
                "\"../data-virtualization/MSSQL_Spark_Connector2.jpg\" style=\"float: center;\" alt=\"drawing\" width=\"900\">\r\n",
                "\r\n",
                "More details\r\n",
                "-----------\r\n",
                "\r\n",
                "The Apache Spark Connector for SQL Server and Azure SQL, uses [SQL Server Bulk copy APIS](https://docs.microsoft.com/en-us/sql/connect/jdbc/using-bulk-copy-with-the-jdbc-driver?view=sql-server-2017#sqlserverbulkcopyoptions) to implement an efficient write to SQL Server. The connector is based on Spark Data source APIs and provides a familiar JDBC interface for access\r\n",
                "\r\n",
                "The Sample\r\n",
                "---------\r\n",
                "\r\n",
                "The following sample uses the Apache Spark Connector for SQL Server and Azure SQL for read/write SQLServer master instance and SQL Server data pool in Big Data Clusters. The sample is divided into 2 parts. \r\n",
                "- Part 1 shows read/write to SQL Master instance and \r\n",
                "- Part 2 shows read/write to Data Pools in Big Data Cluster. \r\n",
                "\r\n",
                "In the sample we' ll \r\n",
                "- Read a file from HDFS and do some basic processing \r\n",
                "- In Part 1, we'll write the dataframe to SQL server table and then read the table to a dataframe .\r\n",
                "- In Part 2, we'll write the dataframe to SQL Server data pool external table and then read it back to a spark data frame. \r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "    "
            ],
            "metadata": {
                "azdata_cell_guid": "2757df21-8174-4bb1-a52c-66eaf94f6b96"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## PreReq\r\n",
                "-------\r\n",
                "- Download [AdultCensusIncome.csv]( https://amldockerdatasets.azureedge.net/AdultCensusIncome.csv ) to your local machine.  Upload this file to hdfs folder named *spark_data*. \r\n",
                "-  The sample uses a SQL database  *connector_test_db*,  user  *connector_user* with password *password123!#* and datasource  *connector_ds*. The database, user/password and datasource need to be created before running the full sample. Refer **mssql_spark_connector_user_creation.ipynb** on steps to create this user."
            ],
            "metadata": {
                "azdata_cell_guid": "48874729-541f-4b01-888f-3fdd2aeb59da"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Read CSV into a data frame\r\n",
                "In this step we read the CSV into a data frame and do some basic cleanup steps. \r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "ed8b58e0-3607-4a71-8dc8-034bc0180ee4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "#spark = SparkSession.builder.getOrCreate()\r\n",
                "sc.setLogLevel(\"INFO\")\r\n",
                "\r\n",
                "#Read a file and then write it to the SQL table\r\n",
                "datafile = \"/spark_data/AdultCensusIncome.csv\"\r\n",
                "df = spark.read.format('csv').options(header='true', inferSchema='true', ignoreLeadingWhiteSpace='true', ignoreTrailingWhiteSpace='true').load(datafile)\r\n",
                "df.show(5)\r\n",
                "\r\n",
                "\r\n",
                "#Process this data. Very simple data cleanup steps. Replacing \"-\" with \"_\" in column names\r\n",
                "columns_new = [col.replace(\"-\", \"_\") for col in df.columns]\r\n",
                "df = df.toDF(*columns_new)\r\n",
                "df.show(5)\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "813bbfa3-2613-45dd-9556-94faba602977"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "1621c3c9135146128525a5c74dce3372"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n|age|       workclass|fnlwgt|education|education-num|    marital-status|       occupation| relationship| race|   sex|capital-gain|capital-loss|hours-per-week|native-country|income|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n| 39|       State-gov| 77516|Bachelors|           13|     Never-married|     Adm-clerical|Not-in-family|White|  Male|        2174|           0|            40| United-States| <=50K|\n| 50|Self-emp-not-inc| 83311|Bachelors|           13|Married-civ-spouse|  Exec-managerial|      Husband|White|  Male|           0|           0|            13| United-States| <=50K|\n| 38|         Private|215646|  HS-grad|            9|          Divorced|Handlers-cleaners|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|\n| 53|         Private|234721|     11th|            7|Married-civ-spouse|Handlers-cleaners|      Husband|Black|  Male|           0|           0|            40| United-States| <=50K|\n| 28|         Private|338409|Bachelors|           13|Married-civ-spouse|   Prof-specialty|         Wife|Black|Female|           0|           0|            40|          Cuba| <=50K|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\nonly showing top 5 rows\n\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n|age|       workclass|fnlwgt|education|education_num|    marital_status|       occupation| relationship| race|   sex|capital_gain|capital_loss|hours_per_week|native_country|income|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n| 39|       State-gov| 77516|Bachelors|           13|     Never-married|     Adm-clerical|Not-in-family|White|  Male|        2174|           0|            40| United-States| <=50K|\n| 50|Self-emp-not-inc| 83311|Bachelors|           13|Married-civ-spouse|  Exec-managerial|      Husband|White|  Male|           0|           0|            13| United-States| <=50K|\n| 38|         Private|215646|  HS-grad|            9|          Divorced|Handlers-cleaners|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|\n| 53|         Private|234721|     11th|            7|Married-civ-spouse|Handlers-cleaners|      Husband|Black|  Male|           0|           0|            40| United-States| <=50K|\n| 28|         Private|338409|Bachelors|           13|Married-civ-spouse|   Prof-specialty|         Wife|Black|Female|           0|           0|            40|          Cuba| <=50K|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\nonly showing top 5 rows"
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "# (PART 1) Write and READ to SQL Table\r\n",
                "- Write dataframe to SQL table to SQL Master\r\n",
                "- Read SQL Table to Spark dataframe"
            ],
            "metadata": {
                "azdata_cell_guid": "a6afceb2-6fbc-435b-af88-e9f5cc784f5d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "#Write from Spark to SQL table using the Apache Spark Connector for SQL Server and Azure SQL\r\n",
                "print(\"Use Apache Spark Connector for SQL Server and Azure SQL to write to master SQL instance \")\r\n",
                "\r\n",
                "servername = \"jdbc:sqlserver://master-0.master-svc\"\r\n",
                "dbname = \"connector_test_db\"\r\n",
                "url = servername + \";\" + \"databaseName=\" + dbname + \";\"\r\n",
                "\r\n",
                "dbtable = \"AdultCensus_test\"\r\n",
                "user = \"connector_user\"\r\n",
                "password = \"password123!#\" # Please specify password here\r\n",
                "\r\n",
                "#com.microsoft.sqlserver.jdbc.spark\r\n",
                "\r\n",
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"overwrite\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", dbtable) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"Connector write(overwrite) succeeded  \")\r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b851fe61-6e85-4e46-a20f-4063fc6586e0"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "1aa452e858b049a6a30315c30d14857a"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Use Apache Spark Connector for SQL Server and Azure SQL to write to master SQL instance \nConnector write(overwrite) succeeded"
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "code",
            "source": [
                "#Use mode as append\r\n",
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"append\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", dbtable) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"Connector write(append) succeeded  \")"
            ],
            "metadata": {
                "azdata_cell_guid": "fbfa1938-59a3-4424-acf7-844c3ef0984a"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "MSSQL Connector write(append) succeeded"
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "code",
            "source": [
                "#Read from SQL table using the Apache Spark Connector for SQL Server and Azure SQL\r\n",
                "print(\"read data from SQL server table  \")\r\n",
                "jdbcDF = spark.read \\\r\n",
                "        .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "        .option(\"url\", url) \\\r\n",
                "        .option(\"dbtable\", dbtable) \\\r\n",
                "        .option(\"user\", user) \\\r\n",
                "        .option(\"password\", password).load()\r\n",
                "\r\n",
                "jdbcDF.show(5)"
            ],
            "metadata": {
                "azdata_cell_guid": "e3e19e1f-1325-47ea-87d1-1170f316d2d8"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "read data from SQL server table  \n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n|age|       workclass|fnlwgt|education|education_num|    marital_status|       occupation| relationship| race|   sex|capital_gain|capital_loss|hours_per_week|native_country|income|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n| 39|       State-gov| 77516|Bachelors|           13|     Never-married|     Adm-clerical|Not-in-family|White|  Male|        2174|           0|            40| United-States| <=50K|\n| 50|Self-emp-not-inc| 83311|Bachelors|           13|Married-civ-spouse|  Exec-managerial|      Husband|White|  Male|           0|           0|            13| United-States| <=50K|\n| 38|         Private|215646|  HS-grad|            9|          Divorced|Handlers-cleaners|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|\n| 53|         Private|234721|     11th|            7|Married-civ-spouse|Handlers-cleaners|      Husband|Black|  Male|           0|           0|            40| United-States| <=50K|\n| 28|         Private|338409|Bachelors|           13|Married-civ-spouse|   Prof-specialty|         Wife|Black|Female|           0|           0|            40|          Cuba| <=50K|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+\nonly showing top 5 rows"
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "Connector also supports write when the destination table contains multiple computed columns. We will now write the same data to a table that has one additional column which is computed by other columns.\r\n",
                "- Write dataframe to SQL table with computed column\r\n",
                "- Read SQL Table to Spark dataframe"
            ],
            "metadata": {
                "azdata_cell_guid": "04f92430-f059-4050-9993-9cbf59015c0c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Append to table with computed column: net_capital_gain = capital_gain - capital_loss\r\n",
                "\r\n",
                "dbtable = \"AdultCenses_computed_col\"\r\n",
                "\r\n",
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"append\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", dbtable) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"MSSQL Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"MSSQL Connector write(append) succeeded  \")"
            ],
            "metadata": {
                "azdata_cell_guid": "8e5ffe11-29d1-47b5-a6f0-6eb7b88f1999"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "4e5509ad713f44759e9acdf6d88a41f9"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "MSSQL Connector write(append) succeeded"
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "code",
            "source": [
                "#Read from SQL table using MSSQ Connector\r\n",
                "print(\"read data from SQL server table  \")\r\n",
                "jdbcDF = spark.read \\\r\n",
                "        .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "        .option(\"url\", url) \\\r\n",
                "        .option(\"dbtable\", dbtable) \\\r\n",
                "        .option(\"user\", user) \\\r\n",
                "        .option(\"password\", password).load()\r\n",
                "\r\n",
                "jdbcDF.show(5)"
            ],
            "metadata": {
                "azdata_cell_guid": "c73a7751-a59e-40d5-91cb-dcdec0bcc24c"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "bc56aa03aafe44f5af3639f942c66efa"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "read data from SQL server table  \n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\n|age|       workclass|fnlwgt|education|education_num|    marital_status|       occupation| relationship| race|   sex|capital_gain|capital_loss|hours_per_week|native_country|income|net_capital_gain|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\n| 39|       State-gov| 77516|Bachelors|           13|     Never-married|     Adm-clerical|Not-in-family|White|  Male|        2174|           0|            40| United-States| <=50K|            2174|\n| 50|Self-emp-not-inc| 83311|Bachelors|           13|Married-civ-spouse|  Exec-managerial|      Husband|White|  Male|           0|           0|            13| United-States| <=50K|               0|\n| 38|         Private|215646|  HS-grad|            9|          Divorced|Handlers-cleaners|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|               0|\n| 53|         Private|234721|     11th|            7|Married-civ-spouse|Handlers-cleaners|      Husband|Black|  Male|           0|           0|            40| United-States| <=50K|               0|\n| 28|         Private|338409|Bachelors|           13|Married-civ-spouse|   Prof-specialty|         Wife|Black|Female|           0|           0|            40|          Cuba| <=50K|               0|\n+---+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\nonly showing top 5 rows"
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "When schemaCheckEnabled is false, we can write to the destination table which has less column than dataframe.\r\n",
                "- Write dataframe to SQL table when strict dataframe and sql table schema check set to false\r\n",
                "- Read SQL Table to Spark dataframe"
            ],
            "metadata": {
                "azdata_cell_guid": "cb26e74c-127b-4031-b642-d266219aab7a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "# Append to table when schemaCheckEnabled set to false\r\n",
                "# age column not exist in sql table\r\n",
                "\r\n",
                "dbtable = \"AdultCenses_schema_check\"\r\n",
                "\r\n",
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"append\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", dbtable) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .option(\"schemaCheckEnabled\", \"false\") \\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"MSSQL Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"MSSQL Connector write(append) succeeded  \")"
            ],
            "metadata": {
                "azdata_cell_guid": "fcefd066-ab24-47c6-80fd-067bf20fc00f"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "3bf86a2df8e3435592ffd79802cfce4e"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "MSSQL Connector write(append) succeeded"
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "code",
            "source": [
                "#Read from SQL table using MSSQ Connector\r\n",
                "print(\"read data from SQL server table  \")\r\n",
                "jdbcDF = spark.read \\\r\n",
                "        .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "        .option(\"url\", url) \\\r\n",
                "        .option(\"dbtable\", dbtable) \\\r\n",
                "        .option(\"user\", user) \\\r\n",
                "        .option(\"password\", password).load()\r\n",
                "\r\n",
                "jdbcDF.show(5)"
            ],
            "metadata": {
                "azdata_cell_guid": "13fa5a5c-dca9-40cc-b605-fff7efc30f1d"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/plain": "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…",
                        "application/vnd.jupyter.widget-view+json": {
                            "version_major": 2,
                            "version_minor": 0,
                            "model_id": "708a5a7bebeb4770a4ce4dbba13a901a"
                        }
                    },
                    "metadata": {}
                },
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "read data from SQL server table  \n+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\n|       workclass|fnlwgt|education|education_num|    marital_status|       occupation| relationship| race|   sex|capital_gain|capital_loss|hours_per_week|native_country|income|net_capital_gain|\n+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\n|       State-gov| 77516|Bachelors|           13|     Never-married|     Adm-clerical|Not-in-family|White|  Male|        2174|           0|            40| United-States| <=50K|            2174|\n|Self-emp-not-inc| 83311|Bachelors|           13|Married-civ-spouse|  Exec-managerial|      Husband|White|  Male|           0|           0|            13| United-States| <=50K|               0|\n|         Private|215646|  HS-grad|            9|          Divorced|Handlers-cleaners|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|               0|\n|         Private|234721|     11th|            7|Married-civ-spouse|Handlers-cleaners|      Husband|Black|  Male|           0|           0|            40| United-States| <=50K|               0|\n|         Private|338409|Bachelors|           13|Married-civ-spouse|   Prof-specialty|         Wife|Black|Female|           0|           0|            40|          Cuba| <=50K|               0|\n+----------------+------+---------+-------------+------------------+-----------------+-------------+-----+------+------------+------------+--------------+--------------+------+----------------+\nonly showing top 5 rows"
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "# (PART 2) Write and READ to Data Pool external Tables in Big Data Cluster\r\n",
                "- Write dataframe to SQL external table in Data Pools in Big Data Cluste\r\n",
                "- Read SQL external Table to Spark dataframe"
            ],
            "metadata": {
                "azdata_cell_guid": "375fa271-fd2a-4a89-a782-0e34ba9baf3c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "#Write from Spark to SQL table using the Apache Spark Connector for SQL Server and Azure SQL\r\n",
                "print(\"Use MSSQL connector to write to master SQL instance \")\r\n",
                "\r\n",
                "datapool_table = \"AdultCensus_DataPoolTable\"\r\n",
                "datasource_name = \"connector_ds\"\r\n",
                "\r\n",
                "\r\n",
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"overwrite\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", datapool_table) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .option(\"dataPoolDataSource\",datasource_name)\\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"Connector write(overwrite) to data pool external table succeeded\")\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f12a886e-6423-40aa-83ab-e8578b56a10c"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "Use MSSQL connector to write to master SQL instance \nMSSQL Connector write(overwrite) to data pool external table succeeded"
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "code",
            "source": [
                "try:\r\n",
                "  df.write \\\r\n",
                "    .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "    .mode(\"append\") \\\r\n",
                "    .option(\"url\", url) \\\r\n",
                "    .option(\"dbtable\", datapool_table) \\\r\n",
                "    .option(\"user\", user) \\\r\n",
                "    .option(\"password\", password) \\\r\n",
                "    .option(\"dataPoolDataSource\",datasource_name)\\\r\n",
                "    .save()\r\n",
                "except ValueError as error :\r\n",
                "    print(\"Connector write failed\", error)\r\n",
                "\r\n",
                "print(\"Connector write(append) to data pool external table succeeded\")"
            ],
            "metadata": {
                "azdata_cell_guid": "a9340485-9bfe-414a-a938-09d71976a5b3"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "MSSQL Connector write(append) to data pool external table succeeded"
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "code",
            "source": [
                "#Read from SQL table using the Apache Spark Connector for SQL Server and Azure SQL\r\n",
                "print(\"read data from SQL server table  \")\r\n",
                "jdbcDF = spark.read \\\r\n",
                "        .format(\"com.microsoft.sqlserver.jdbc.spark\") \\\r\n",
                "        .option(\"url\", url) \\\r\n",
                "        .option(\"dbtable\", datapool_table) \\\r\n",
                "        .option(\"user\", user) \\\r\n",
                "        .option(\"password\", password)\\\r\n",
                "        .load()\r\n",
                "\r\n",
                "jdbcDF.show(5)\r\n",
                "\r\n",
                "print(\"Connector read from data pool external table succeeded\")"
            ],
            "metadata": {
                "azdata_cell_guid": "e120a1ab-72e5-4ae7-8bd4-b8d45c1e2267"
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "read data from SQL server table  \n+---+----------------+------+------------+-------------+------------------+-------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n|age|       workclass|fnlwgt|   education|education_num|    marital_status|   occupation| relationship| race|   sex|capital_gain|capital_loss|hours_per_week|native_country|income|\n+---+----------------+------+------------+-------------+------------------+-------------+-------------+-----+------+------------+------------+--------------+--------------+------+\n| 36|         Private| 99374|Some-college|           10|          Divorced| Craft-repair|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|\n| 27|         Private|248402|   Bachelors|           13|     Never-married| Tech-support|    Unmarried|Black|Female|           0|           0|            40| United-States| <=50K|\n| 46|Self-emp-not-inc|277946|  Assoc-acdm|           12|         Separated| Craft-repair|Not-in-family|White|  Male|           0|           0|            40| United-States| <=50K|\n| 38|         Private| 91039|   Bachelors|           13|Married-civ-spouse|        Sales|      Husband|White|  Male|       15024|           0|            60| United-States|  >50K|\n| 18|         Private|156764|        11th|            7|     Never-married|Other-service|    Own-child|White|  Male|           0|           0|            40| United-States| <=50K|\n+---+----------------+------+------------+-------------+------------------+-------------+-------------+-----+------+------------+------------+--------------+--------------+------+\nonly showing top 5 rows\n\nMSSQL Connector read from data pool external table succeeded"
                }
            ],
            "execution_count": 10
        }
    ]
}