getting_data_in_a_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "517455a9-a817-495f-b760-f68472a93922",
   "metadata": {},
   "source": [
    "# Getting data into a database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b0d6cd25-3b13-4307-8029-5b5081d53991",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "import ohio.ext.pandas\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d26eccd-6ca9-41e3-857d-82396561167a",
   "metadata": {},
   "source": [
    "## Let's start with the raw data\n",
    "\n",
    "We'll use the data from donorschoose projects that comes in a csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "df73910c-14ed-44b1-ab28-341e76ac5f2d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "datafile='/mnt/data/projects/food_inspections/data/projects.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f28f7a6-c435-4cb3-9812-85191b182709",
   "metadata": {},
   "source": [
    "## Let's try putting it in our postgres database in a few different ways and figure out pros and cons"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7937f639-eb94-4976-bde5-39b4a8701bfd",
   "metadata": {},
   "source": [
    "### Option 1: Use pandas to_sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ad67737-9964-458a-9848-44546b063427",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1. Create schema in the postgres database\n",
    "\n",
    "You can use psql or dbeaver\n",
    "\n",
    "```create schema donorschoose```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "089a8ed4-148d-4ba7-a854-497051e4815d",
   "metadata": {},
   "source": [
    "#### 2. Load it in a paindas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5f278722-05ea-458c-881d-31d3e1225b06",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv(datafile)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c1a5b2b-b326-49db-b003-35872e54ed2c",
   "metadata": {},
   "source": [
    "#### 3. Get postgres credentials and connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e1175be2-ccc8-4910-afbf-f81da91fc995",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "engine=create_engine(\"postgresql:///food-inspections\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef549a48-8e2b-42ad-909f-016b740a45ca",
   "metadata": {},
   "source": [
    "#### 4. Copy the dataframe to postgres (to the right schema and tablename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "06f2c09e-b82e-42c3-ba28-e83a2f7ee47c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_sql(schema='donorschoose',name='projects',con=engine,if_exists='replace',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3699cf47-792d-4cae-a28e-541c90356cdf",
   "metadata": {},
   "source": [
    "#### Let's talk about what happened and why it was so slow"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1427f96b-9c5f-4ae4-a8be-f9e248d6aa73",
   "metadata": {},
   "source": [
    "...\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9104e595-df71-417b-bd40-4a294ae6c178",
   "metadata": {},
   "source": [
    "### Option 2: Use the native COPY command from postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e3a785-fbe2-4574-88ae-9823ab0858d4",
   "metadata": {},
   "source": [
    "#### 1. Create schema in the postgres database (if you already did it earlier, you don't need to do it again)\n",
    "\n",
    "You can use psql or dbeaver\n",
    "\n",
    "```create schema donorschoose```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c21981bc-c8f5-4c82-879e-abaf49c15078",
   "metadata": {},
   "source": [
    "#### 2. Create table to copy data into\n",
    "\n",
    "Here you can choose to \n",
    "1. put all the columns as varchars/strings\n",
    "or \n",
    "2. get the right column types\n",
    "\n",
    "\n",
    "You can also choose to:\n",
    "1. Write the create table statement by hand\n",
    "or \n",
    "2. Use a tool like csvkit to infer types and generate the create statement. \n",
    "```head -n 10000 | csvsql -i postgresql```\n",
    "\n",
    "\n",
    "You can use psql or dbeaver to run it\n",
    "\n",
    "```\n",
    "create table donorschoose.projects\n",
    "(projectid varchar(10000),\n",
    "teacher_acctid varchar(10000),\n",
    " schoolid varchar(10000),\n",
    " school_ncesid varchar(10000),\n",
    " school_latitude varchar(10000),\n",
    " school_longitude varchar(10000),\n",
    " school_city varchar(10000),\n",
    " school_state varchar(10000),\n",
    " school_zip varchar(10000),\n",
    " school_metro varchar(10000),\n",
    " school_district varchar(10000),\n",
    " school_county varchar(10000),\n",
    " school_charter varchar(10000),\n",
    " school_magnet varchar(10000),\n",
    " school_year_round varchar(10000),\n",
    " school_nlns varchar(10000),\n",
    " school_kipp varchar(10000),\n",
    " school_charter_ready_promise varchar(10000),\n",
    " teacher_prefix varchar(10000),\n",
    " teacher_teach_for_america varchar(10000),\n",
    " teacher_ny_teaching_fellow varchar(10000),\n",
    " primary_focus_subject varchar(10000),\n",
    " primary_focus_area varchar(10000),\n",
    " secondary_focus_subject varchar(10000),\n",
    " secondary_focus_area varchar(10000),\n",
    " resource_type varchar(10000),\n",
    " poverty_level varchar(10000),\n",
    " grade_level varchar(10000),\n",
    " fulfillment_labor_materials varchar(10000),\n",
    " total_price_excluding_optional_support varchar(10000),\n",
    " total_price_including_optional_support varchar(10000),\n",
    " students_reached varchar(10000),\n",
    " eligible_double_your_impact_match varchar(10000),\n",
    " eligible_almost_home_match varchar(10000),\n",
    " date_posted varchar(10000)\n",
    " );\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db591542-29d5-4731-b8a2-3241ca33afa7",
   "metadata": {},
   "source": [
    "#### 3. Use the postgres COPY command to copy thew csv into the table you just created\n",
    "\n",
    "You can use psql for this\n",
    "```\n",
    "food-inspections> \\copy donorschoose.project from '/mnt/data/projects/food_inspections/data/projects.csv' header csv;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb3fcbe3-4e97-4391-b34e-0c3c42a5628a",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Option 3: Use ohio, a python package we (thanks Jesse London) developed that combines the best of the two worlds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a93c9b1-576a-4cc1-9dc7-4a6b33f1f3ac",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 1. Create schema in the postgres database (you don't need to do it again if you already did this)\n",
    "\n",
    "You can use psql or dbeaver\n",
    "\n",
    "```create schema donorschoose```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9a0b5c9-7e92-4739-9ebe-fb6293d376b4",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 2. Load it in a paindas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2cf4824b-33d4-4b40-8def-c5cfae2590d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv(datafile)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "046a1d6d-fbc2-4ba3-af1d-684c49235c3a",
   "metadata": {},
   "source": [
    "#### 3. Copy it to the postgres databse using ohio pandas extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c4e16061-7943-40d9-9a6a-fd69b4fdc794",
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    with conn.begin():\n",
    "        df.pg_copy_to(schema='donorschoose',name='projects',con=conn, index=False, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fec20a3c-7f1a-4a99-8128-b6b8edba0e6e",
   "metadata": {},
   "source": [
    "### If you want to time how long each option takes, you can use time()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b0c810e8-bac8-4c10-a581-685f5498c718",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COPY duration: 86.23058223724365 seconds\n"
     ]
    }
   ],
   "source": [
    "start_time = time.time()\n",
    "df.to_sql(schema='donorschoose',name='projects',con=engine,if_exists='replace',index=False)\n",
    "print(\"COPY duration: {} seconds\".format(time.time() - start_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e8f959bf-5696-455b-ba07-25a1d3bd72df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COPY duration: 10.623504400253296 seconds\n"
     ]
    }
   ],
   "source": [
    "start_time = time.time()\n",
    "with engine.connect() as conn:\n",
    "    with conn.begin():\n",
    "        df.pg_copy_to(schema='donorschoose',name='projects',con=conn, index=False, if_exists='replace')\n",
    "print(\"COPY duration: {} seconds\".format(time.time() - start_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "578d116f-47b2-4d54-9efe-c9b8b7474e5f",
   "metadata": {},
   "source": [
    "### Using smaller chunks instead of loading it in one go if you data file is too large for pandas and the RAM you have"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fe3f3027-851d-4a21-8db5-806d9a996a3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    with conn.begin():\n",
    "        for df in pd.read_csv(datafile,  chunksize=1000000):\n",
    "            df.pg_copy_to(schema='donorschoose', name='projects',con=conn,index=False, if_exists='append')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8249b179-66c8-4826-b39b-105a49ccab43",
   "metadata": {},
   "source": [
    "## Check to see if the data actually got in the database\n",
    "\n",
    "\n",
    "You can do this is psql or dbeaver\n",
    "```\n",
    "select count(*) from donorschoose.projects;\n",
    "\n",
    "select * from donorschoose.projects limit 100;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aeb79d1e-4bf7-4e45-81b9-c710d7dede31",
   "metadata": {},
   "source": [
    "## Workflow tips for loading project data (if it comes in as csvs or even excel)\n",
    "\n",
    "### Getting raw data into postgres\n",
    "1. create a ```raw``` schema to store the data you get without much processing\n",
    "2. Try ohio to load the files directly to postgres. If you get an error, debug :)\n",
    "2a. If the data file is too large for the RAM you have, process it in chunks\n",
    "3. Check to make sure the column types make sense and the counts are consistent\n",
    "\n",
    "\n",
    "### Processing the raw data before analysis\n",
    "The column types may not be useful for analysis (they may be varchars/strings instead of int or datetime) or you may have one table for each file and you want to merge them into one table.\n",
    "\n",
    "1. create ```processed``` schema to store the processed data you create (from raw)\n",
    "2. use sql to change column types, change column names, merge tables, etc.  (create table statements)\n",
    "3. do not drop rows or columns (ever! but especially not now)\n",
    "4. check to make sure the counts of rows and columns are still the same\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8b465f4-0b07-4579-a863-8998bf850545",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}