OC4IDS_Database_Data_Import.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wlYslPD1uRp2"
      },
      "source": [
        "# OC4IDS Database - Download, check and import\n",
        "\n",
        "Use this notebook to:\n",
        "\n",
        "* Download data from OC4IDS publishers\n",
        "* Check data using the OC4IDS Data Review Tool\n",
        "* Import data and check results into the OC4IDS database\n",
        "\n",
        "**How to run the notebook**\n",
        "\n",
        "1. [Enter your database credentials](#scrollTo=z4-iWuZRhoEe)\n",
        "1. Run all cells in the [Setup](#scrollTo=3PU3KAsPuYP7) section\n",
        "1. For each load (set of data sources that you wish to analyze as a group):\n",
        "  1. [Set a new `load_id`](#scrollTo=XJSBAcLbWJHu)\n",
        "  1. For each data source, [choose a data source](#scrollTo=HFTcVz0Q0tMr) and run all cells under [Download, check and import data](#scrollTo=veSGp6SIwYRt)\n",
        "\n",
        "**How to add a new publisher**\n",
        "\n",
        "1. Add a `source_id` for the publisher in the [Choose a data source](#scrollTo=HFTcVz0Q0tMr) section.\n",
        "2. In the [Download data](#scrollTo=veSGp6SIwYRt) section:\n",
        "  1. Add the `source_id` and download URL to `sources`.\n",
        "  1. If the data is not accessible as a single project package via a simple `GET` request, add an [`elif`](https://docs.python.org/3/reference/compound_stmts.html#elif) part to the `if` statement with code that returns a single project package named `project_package.json`.\n",
        "\n",
        "**Having problems?**\n",
        "\n",
        "[Open an issue in the `notebooks-oc4ids` repository](https://github.com/open-contracting/notebooks-oc4ids/issues/new)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z4-iWuZRhoEe"
      },
      "source": [
        "## Enter your database credentials.\n",
        "\n",
        "> **Open Data Services users:** Refer to the password database."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IVy70_JfeV9C",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "\n",
        "user = 'postgres'\n",
        "password = getpass.getpass('Password:')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3PU3KAsPuYP7"
      },
      "source": [
        "## Setup"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N7eihXsOqxiZ"
      },
      "source": [
        "Install `psql` client:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TnDPv6EXquxF",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "sudo apt-get update\n",
        "sudo apt-get install -y postgresql-client"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UjTbla1Xq7jJ"
      },
      "source": [
        "Create a `.pgpass` file with database credentials:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zhYmhNnwqxHB",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "!touch ~/.pgpass\n",
        "!chmod 0600 ~/.pgpass\n",
        "!echo oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com:5432:postgres:{user}:{password} > ~/.pgpass"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xycifs7is8Dt"
      },
      "source": [
        "Install `.jq`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VcCYPKJbtALB",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "sudo apt-get install jq"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "femBJjzTsQtO"
      },
      "source": [
        "Connect notebook to database:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bIYcnkQ7rl6B",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "connection_string = 'postgresql://' + user + ':' + password + '@oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com/postgres'\n",
        "\n",
        "%load_ext sql\n",
        "%sql $connection_string"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-q3kWP4a7bIW"
      },
      "source": [
        "Install packages:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ic5au-Fq7dMn",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "pip install libcoveoc4ids\n",
        "pip install oc4idskit\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Set a new `load_id`"
      ],
      "metadata": {
        "id": "XJSBAcLbWJHu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from datetime import datetime\n",
        "load_id = datetime.now()"
      ],
      "metadata": {
        "id": "AIGD4qmoWMOD"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HFTcVz0Q0tMr"
      },
      "source": [
        "## Choose a data source"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zMP_MPYzycvD",
        "outputId": "573982fc-6ddd-415c-99a3-d189d47ab266",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# @title { run: \"auto\" }\n",
        "\n",
        "# @markdown After running this cell manually, it will auto-run when you change the source_id.\n",
        "\n",
        "source_id = 'ukraine_cost_ukraine' #@param [ 'ghana_cost_sekondi_takoradi', 'indonesia_cost_ntb', 'indonesia_cost_west_lombok', 'malawi_cost_malawi', 'mexico_cost_jalisco', 'mexico_nuevo_leon', 'uganda_gpp', 'ukraine_cost_ukraine']\n",
        "\n",
        "print('Source selected:', source_id)"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Source selected: ukraine_cost_ukraine\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "veSGp6SIwYRt"
      },
      "source": [
        "# Download, check and import data"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import io\n",
        "import json\n",
        "import zipfile\n",
        "from datetime import datetime\n",
        "\n",
        "import requests\n",
        "\n",
        "from oc4idskit.combine import combine_project_packages"
      ],
      "metadata": {
        "id": "zuf3uKqhFFPG"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SHbJftL1zkMK"
      },
      "source": [
        "sources = {\n",
        "  'mexico_cost_jalisco': 'http://www.costjalisco.org.mx/jsonprojects',\n",
        "  'ghana_cost_sekondi_takoradi': 'https://costsekondi-takoradigh.org/uploads/projectJson.json',\n",
        "  'mexico_nuevo_leon': 'http://si.nl.gob.mx/siasi_ws/api/edcapi/DescargarProjectPackage',\n",
        "  'indonesia_cost_west_lombok': 'https://intras.lombokbaratkab.go.id/oc4ids',\n",
        "  'ukraine_cost_ukraine': 'https://portal.costukraine.org/data.json',\n",
        "  'uganda_gpp': 'https://gpp.ppda.go.ug/adminapi/public/api/open-data/v1/infrastructure/projects/download?yr=',\n",
        "  'malawi_cost_malawi': 'https://ippi.mw/api/projects/query',\n",
        "  'indonesia_cost_ntb':'https://intras.ntbprov.go.id/storage/docs/oc4ids.json'\n",
        "}\n",
        "\n",
        "if source_id == 'malawi_cost_malawi':\n",
        "  # The IPPI API accepts a POST request with JSON-encoded start_date and end_date paramters\n",
        "  payload = {\n",
        "      \"start_date\": \"2010-01-01\",\n",
        "      \"end_date\": datetime.today().strftime('%Y-%m-%d')\n",
        "    }\n",
        "  response = requests.post(sources[source_id], json=payload)\n",
        "\n",
        "  with open('project_package.json', 'wb') as f:\n",
        "    f.write(response.content)\n",
        "\n",
        "elif source_id == 'uganda_gpp':\n",
        "    packages = []\n",
        "    start_year = 2022\n",
        "    current_year = datetime.now().year\n",
        "    years = list(range(start_year, current_year + 1))\n",
        "\n",
        "    for year in years:\n",
        "      url = f\"{sources[source_id]}{year}\" # Construct the url\n",
        "      response = requests.get(url, verify=False, stream=True) # stream=True for large files\n",
        "      zip_data = io.BytesIO(response.content)\n",
        "\n",
        "      try:\n",
        "          with zipfile.ZipFile(zip_data, 'r') as zip_ref:\n",
        "              for filename in zip_ref.namelist(): #namelist returns all the filepaths in the zip file\n",
        "                  if filename.endswith('.json'):\n",
        "                      with zip_ref.open(filename) as f:\n",
        "                          package = json.load(f)\n",
        "                          packages.append(package)\n",
        "      except zipfile.BadZipFile:\n",
        "          print(url, \"Error: The downloaded file is not a valid zip archive.\")\n",
        "\n",
        "    # Get unique versions\n",
        "    versions = {package[\"version\"] for package in packages}\n",
        "    if len(versions) > 1:\n",
        "      raise ValueError(f\"Packages declare more than one version: {versions}\")\n",
        "    extracted_version, = versions\n",
        "\n",
        "    # Get unique publishedDates\n",
        "    published_dates = {package[\"publishedDate\"] for package in packages}\n",
        "\n",
        "    # Combine the packages\n",
        "    combined_package = combine_project_packages(\n",
        "      packages,\n",
        "      uri=\"https://gpp.ppda.go.ug/public/open-data/oc4ids/project-datasets\",\n",
        "      publisher={\n",
        "        \"name\": \"The Public Procurement and Disposal of Public Assets Authority\",\n",
        "        \"scheme\": \"PPDA\",\n",
        "        \"uid\": \"475\",\n",
        "        \"uri\": \"https://www.ppda.go.ug/\"\n",
        "      },\n",
        "      published_date = max(published_dates),#Latest date\n",
        "      version = extracted_version\n",
        "    )\n",
        "\n",
        "    #Write to file\n",
        "    with open('project_package.json', 'w') as f:\n",
        "      json.dump(combined_package, f, indent=2)  # Use json.dump() to write the dictionary as JSON\n",
        "\n",
        "else:\n",
        "  response = requests.get(sources[source_id], verify=False)\n",
        "\n",
        "  with open('project_package.json', 'wb') as f:\n",
        "    f.write(response.content)\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s7w1j3t9wTPM"
      },
      "source": [
        "## Check data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fz9ZqgSk7q6V"
      },
      "source": [
        "Check data using `libcoveoc4ids`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kBxOdYUg7CiD",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "libcoveoc4ids project_package.json > results.json"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PpKqvkB_8BWd"
      },
      "source": [
        "## Import data and check results"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KP5uMmSuMapZ"
      },
      "source": [
        "Use `jq` to generate a new-line delimited JSON file from the project package:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SEwoDVukMapa",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%shell\n",
        "\n",
        "cat project_package.json | jq -crM .projects[] > projects.json"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aZCclPabw2Cv"
      },
      "source": [
        "Import data to `temp_data` table:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qOouDNlD8tVF",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "delete from temp_data;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dA8Z8ZLbtElG",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "!cat projects.json | psql -h \"oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com\" -U {user} -d \"postgres\" -c \"COPY temp_data (data) FROM STDIN WITH escape '\\' quote e'\\x01' delimiter e'\\x02' CSV\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AZhfpWPO8zri"
      },
      "source": [
        "Import check results to `temp_checks`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7iutDHJQ84mH",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "delete from temp_checks;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vAi0SC7684mn",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "!cat results.json | jq -crM . | psql -h \"oc4ids-database-2.cuujgua4wses.us-east-1.rds.amazonaws.com\" -U {user} -d \"postgres\" -c \"COPY temp_checks (cove_output) FROM STDIN WITH escape '\\' quote e'\\x01' delimiter e'\\x02' CSV\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bBj3VI2Ew5jb"
      },
      "source": [
        "Create collection, copy data to `projects` table, copy check results to `collection_check` table, populate `field_counts` and `project_fields` tables:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rmTiK5xft12d",
        "vscode": {
          "languageId": "python"
        }
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "\n",
        "INSERT INTO collection (source_id, data_version, load_id)\n",
        "    VALUES (:source_id, CURRENT_TIMESTAMP, :load_id);\n",
        "\n",
        "INSERT INTO projects (collection_id, project_id, data)\n",
        "SELECT\n",
        "    (\n",
        "        SELECT\n",
        "            id\n",
        "        FROM\n",
        "            collection\n",
        "        ORDER BY\n",
        "            id DESC\n",
        "        LIMIT 1) AS collection_id,\n",
        "    trim(BOTH '\"' FROM (data -> 'id')::text) AS project_id,\n",
        "    data AS data\n",
        "FROM\n",
        "    temp_data;\n",
        "\n",
        "DELETE FROM temp_data;\n",
        "\n",
        "INSERT INTO collection_check (collection_id, cove_output)\n",
        "SELECT\n",
        "    (\n",
        "        SELECT\n",
        "            id\n",
        "        FROM\n",
        "            collection\n",
        "        ORDER BY\n",
        "            id DESC\n",
        "        LIMIT 1) AS collection_id,\n",
        "    cove_output AS cove_output\n",
        "FROM\n",
        "    temp_checks;\n",
        "\n",
        "DELETE FROM temp_checks;\n",
        "\n",
        "INSERT INTO field_counts\n",
        "SELECT\n",
        "    (\n",
        "        SELECT\n",
        "            id\n",
        "        FROM\n",
        "            collection\n",
        "        ORDER BY\n",
        "            id DESC\n",
        "        LIMIT 1) AS collection_id,\n",
        "    path,\n",
        "    regexp_split_to_array(path, '/') AS path_array,\n",
        "    sum(object_property) object_property,\n",
        "    sum(array_item) array_count,\n",
        "    count(DISTINCT id) distinct_projects\n",
        "FROM\n",
        "    projects\n",
        "    CROSS JOIN flatten (data)\n",
        "WHERE\n",
        "    collection_id = (\n",
        "        SELECT\n",
        "            id\n",
        "        FROM\n",
        "            collection\n",
        "        ORDER BY\n",
        "            id DESC\n",
        "        LIMIT 1)\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    path;\n",
        "\n",
        "WITH RECURSIVE paths (\n",
        "    project_id,\n",
        "    path,\n",
        "    \"value\"\n",
        ") AS (\n",
        "    SELECT\n",
        "        project_id,\n",
        "        (key_value).KEY \"path\",\n",
        "        (key_value).value \"value\",\n",
        "        'true'::boolean \"use_path\"\n",
        "    FROM (\n",
        "        SELECT\n",
        "            project_id,\n",
        "            jsonb_each(data) key_value\n",
        "        FROM\n",
        "            projects\n",
        "        WHERE\n",
        "            collection_id = (\n",
        "                SELECT\n",
        "                    id\n",
        "                FROM\n",
        "                    collection\n",
        "                ORDER BY\n",
        "                    id DESC\n",
        "                LIMIT 1)) a\n",
        "    UNION ALL (\n",
        "        SELECT\n",
        "            project_id,\n",
        "            CASE WHEN key_value IS NOT NULL THEN\n",
        "                path || '/'::text || (key_value).KEY::text\n",
        "            ELSE\n",
        "                path\n",
        "            END \"path\",\n",
        "            CASE WHEN key_value IS NOT NULL THEN\n",
        "            (key_value).value\n",
        "        ELSE\n",
        "            array_value\n",
        "            END \"value\",\n",
        "            key_value IS NOT NULL \"use_path\"\n",
        "        FROM (\n",
        "            SELECT\n",
        "                project_id,\n",
        "                path,\n",
        "                jsonb_each(\n",
        "                    CASE WHEN jsonb_typeof(value) = 'object' THEN\n",
        "                        value\n",
        "                    ELSE\n",
        "                        '{}'::jsonb\n",
        "                    END) key_value,\n",
        "                jsonb_array_elements(\n",
        "                    CASE WHEN jsonb_typeof(value) = 'array'\n",
        "                        AND jsonb_typeof(value -> 0) = 'object' THEN\n",
        "                        value\n",
        "                    ELSE\n",
        "                        '[]'::jsonb\n",
        "                    END) \"array_value\"\n",
        "            FROM\n",
        "                paths) a))\n",
        "    INSERT INTO project_fields\n",
        "    SELECT\n",
        "        (\n",
        "            SELECT\n",
        "                id\n",
        "            FROM\n",
        "                collection\n",
        "            ORDER BY\n",
        "                id DESC\n",
        "            LIMIT 1) AS collection_id,\n",
        "        project_id,\n",
        "        array_agg(path) AS paths\n",
        "FROM\n",
        "    paths\n",
        "WHERE\n",
        "    use_path\n",
        "GROUP BY\n",
        "    project_id;\n"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": " OC4IDS Database - Data Import",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}