From_SQLServer_To_AzureBlobStore.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae578c84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install feather\n",
    "# !pip install pandas\n",
    "# !pip install numpy\n",
    "# !pip install pyarrow\n",
    "# !pip install fastavro \n",
    "# !pip install fastparquet\n",
    "# !pip install azure.storage.blob "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52b6727d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import timeit\n",
    "#import time\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import feather\n",
    "import pickle\n",
    "import pyarrow as pa\n",
    "import pyarrow.orc as orc \n",
    "from fastavro import writer, reader, parse_schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1300c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "from azure.storage.blob import PublicAccess\n",
    "from azure.storage.blob import BlobServiceClient\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ee2d93d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os \n",
    "cwd = os.getcwd()\n",
    "print(cwd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be65416d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import pypyodbc as pyodbc\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# connection parameters\n",
    "nHost = '(localdb)\\MSSQLlocalDB'\n",
    "nBase = 'AdventureWorks2019'\n",
    "\n",
    "\n",
    "def sqlconnect(nHost,nBase):\n",
    "    try:\n",
    "        return pyodbc.connect('DRIVER={SQL Server};SERVER='+nHost+';DATABASE='+nBase+';Trusted_connection=yes')\n",
    "    except:\n",
    "        print (\"connection failed check authorization parameters\")  \n",
    "\n",
    "con = sqlconnect(nHost,nBase)\n",
    "cursor = con.cursor()\n",
    "\n",
    "sql = ('''select * from [Sales].[vIndividualCustomer]''')\n",
    "\n",
    "query = cursor.execute(sql)\n",
    "row = query.fetchall()\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ac0ce56",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data=row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5bace5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns =['BusinessEntityID','Title','FirstName','MiddleName','LastName','Suffix','PhoneNumber','PhoneNumberType','EmailAddress','EmailPromotion','AddressType','AddressLine1','AddressLine2','City','StateProvinceName','PostalCode','CountryRegionName','Demographics']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32ccfb23",
   "metadata": {},
   "outputs": [],
   "source": [
    "#to csv\n",
    "df.to_csv('AWorks2019.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "251260dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#to parquet\n",
    "df.to_parquet('AWorks2019.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50794366",
   "metadata": {},
   "outputs": [],
   "source": [
    "from azure.storage.blob import BlobServiceClient, BlobBlock\n",
    "\n",
    "# Get Connection string from Container SAS. starts with: BlobEndpoint=https:// ...\n",
    "connection_string = \"\"\n",
    "\n",
    "blob_service_client = BlobServiceClient.from_connection_string(connection_string)\n",
    "\n",
    "container_client = blob_service_client.get_container_client(\"blobcontainerpytest\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d4bff00",
   "metadata": {},
   "outputs": [],
   "source": [
    "#get name of the file for blob Store\n",
    "blob_client = container_client.get_blob_client(\"AWorks2019_Uploaded.csv\")\n",
    "\n",
    "# get the local file name\n",
    "with open(\"AWorks2019.csv\", \"rb\") as data:\n",
    "    blob_client.upload_blob(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1c5301a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#get name of the file for blob Store\n",
    "blob_client = container_client.get_blob_client(\"AWorks2019_upload.parquet\")\n",
    "\n",
    "# get the local file name\n",
    "with open(\"AWorks2019.parquet\", \"rb\") as data:\n",
    "    blob_client.upload_blob(data)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.2"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}