component_setup_kingfisher.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EEE8NyL9UXi7"
      },
      "source": [
        "## Setup Kingfisher Process"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hVRJEe8Ry04F"
      },
      "source": [
        "### Connect to the database"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import getpass\n",
        "\n",
        "from ocdskingfishercolab import (\n",
        "    list_collections,\n",
        "    list_source_ids,\n",
        "    set_search_path,\n",
        ")"
      ],
      "metadata": {
        "id": "sX1Mk084L8gN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F8fwq_F6XMLs"
      },
      "source": [
        "Enter your PostgreSQL credentials and connect to the Kingfisher Process database:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "27yRx-oaXPtd"
      },
      "source": [
        "user = input(\"Username:\")\n",
        "password = getpass.getpass(\"Password:\")\n",
        "\n",
        "# Don't show connection string after execute.\n",
        "%config SqlMagic.displaycon = False\n",
        "\n",
        "connection_string = (\n",
        "    \"postgresql://\"\n",
        "    + user\n",
        "    + \":\"\n",
        "    + password\n",
        "    + \"@postgres.kingfisher.open-contracting.org/kingfisher_process?sslmode=require\"\n",
        ")\n",
        "%sql $connection_string"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0h0nJSx6Mzm9"
      },
      "source": [
        "### Choose collections and schema\n",
        "\n",
        "*Use this section to choose the collections and schema that you want to query.*"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IKDe1itVq7dQ"
      },
      "source": [
        "#### Set the collection(s)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-EVkYkVUsP9f"
      },
      "source": [
        "Update `collection_ids` with the `id`(s) of the [Kingfisher Process collection(s)](https://kingfisher-process.readthedocs.io/en/latest/data-model.html#collections):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IKn0TqQIsIGY"
      },
      "source": [
        "collection_ids = (2358, 2359)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ewGMrRM9nNCl"
      },
      "source": [
        "If you don't know which collections you need, run the next cell and use the **Filter** button to filter the [collection table](https://kingfisher-process.readthedocs.io/en/latest/database-structure.html#collection-table) to find the collection(s). You can use the `source_id` column to filter on the `name` of the [Kingfisher Collect spider](https://kingfisher-collect.readthedocs.io/en/latest/spiders.html) used to collect the data. Use the value(s) from the `id` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ITTvCttGsWj1"
      },
      "source": [
        "list_collections()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aupr2bJKm4wr"
      },
      "source": [
        "#### Set the schema"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y3mo29DCm4wu"
      },
      "source": [
        "Update `schema_name` with the name of the [Kingfisher Summarize schema](https://kingfisher-summarize.readthedocs.io/en/latest/index.html#how-it-works)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RtNddeQWm4wx"
      },
      "source": [
        "schema_name = \"view_data_collection_2358_2359\"\n",
        "set_search_path(schema_name)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Ns5ujbNWbID"
      },
      "source": [
        "If you don't know which schema you need, run the next cell and use the **Filter** button to filter the [selected collections table](https://kingfisher-summarize.readthedocs.io/en/latest/database.html#summaries-selected-collections) to find the schema. You can use the `collection_id` column to filter on the `id` of the collections that you identified in the previous step. Alternatively, you can filter on the `source_id` column. Use the value from the `schema` column to update the previous cell."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aVg-KWfvzcfa"
      },
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    summaries.selected_collections.*,\n",
        "    source_id\n",
        "FROM\n",
        "    summaries.selected_collections\n",
        "INNER JOIN\n",
        "    collection\n",
        "    ON summaries.selected_collections.collection_id = collection.id\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LKjwIzCZoJYw"
      },
      "source": [
        "If you can't find a schema containing the collections that you want to query, you can create a schema using [Kingfisher Summarize](https://ocdsdeploy.readthedocs.io/en/latest/use/kingfisher-summarize.html)."
      ]
    }
  ]
}