artifacts/02/Exercise 2 - Bonus Notebook with CSharp.ipynb

{
  "metadata": {
    "saveOutput": true,
    "language_info": {
      "name": "csharp"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2,
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {},
      "source": [
        "// Configure the connection to the data lake\n",
        "var adlsPath = \"abfss://wwi@<primary_storage>.dfs.core.windows.net\";"
      ],
      "attachments": {}
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {},
      "source": [
        "// Import the sales data from the data lake\n",
        "var sales = spark.Read().Option(\"header\", \"true\").Option(\"inferSchema\", \"true\").Option(\"sep\", \"|\").Csv($\"{adlsPath}/factsale-csv/2012/Q4\");\n",
        "sales.Show(5);\n",
        "sales.PrintSchema();"
      ],
      "attachments": {}
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "metadata": {},
      "source": [
        "using System;\n",
        "using System.Collections.Generic;\n",
        "\n",
        "// Set up a JDBC connection to Azure Synapse Analytics\n",
        "var jdbcUsername = \"asa.sql.staging@<workspace>\";\n",
        "var jdbcPassword = \"<sql_staging_password>\";\n",
        "var jdbcConnectionString = $\"jdbc:sqlserver://<workspace>.sql.azuresynapse.net:1433;database=<sql_pool>;\";\n",
        "\n",
        "// Set target table name\n",
        "var tableName = $\"wwi_staging.Sale_CSharp\";\n",
        "\n",
        "// Write the retrieved sales data into a staging table in Azure Synapse Analytics.\n",
        "sales\n",
        "    .Limit(1500)\n",
        "    .Write()\n",
        "    .Format(\"jdbc\")\n",
        "    .Options(\n",
        "        new Dictionary<string, string>\n",
        "        {\n",
        "            {\"url\", jdbcConnectionString},\n",
        "            {\"dbtable\", tableName},\n",
        "            {\"user\", jdbcUsername},\n",
        "            {\"password\", jdbcPassword},\n",
        "            {\"encrypt\", \"false\"},\n",
        "            {\"trustServerCertificate\", \"true\"},\n",
        "            {\"createTableColumnTypes\", \"DeliveryDateKey VARCHAR(50), Description VARCHAR(100), Package VARCHAR(50)\"} // Override the column types for string values. They are assigned nvarchar(max) by default, and that is not compatible with columnstore index.\n",
        "        }\n",
        "    )\n",
        "    .Mode(\"append\")\n",
        "    .Save();"
      ],
      "attachments": {}
    }
  ]
}