Ingest Public JSON/Ingest Public JSON.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "id": "a90dbf7a-f7e9-4b6d-b056-0d1711b7e54c",
      "metadata": {
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "name": "cell1"
      },
      "source": [
        "# How to Ingest JSON Data from Public Endpoint\n",
        "\n",
        "This example demonstrates how you can download data from a public endpoint and transform it into a Snowpark Dataframe and save the results into a table in Snowflake.\n",
        "\n",
        "**Note:** Running this notebook require that you have ACCOUNTADMIN or SECURITYADMIN roles to create new network rules."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "0e28f122-e4b0-4d7e-87f6-33348f4b8d39",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "language": "sql",
        "name": "cell2"
      },
      "outputs": [],
      "source": [
        "USE ROLE ACCOUNTADMIN"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f719f3fe-5979-4409-829c-0a8389d8c10f",
      "metadata": {
        "codeCollapsed": false,
        "jupyter": {
          "source_hidden": true
        },
        "language": "sql",
        "name": "cell3"
      },
      "outputs": [],
      "source": [
        "CREATE or replace TABLE bike_riders (\n",
        "  timestamp STRING,\n",
        "  northbound NUMBER,\n",
        "  southbound NUMBER\n",
        ");"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "6d5ac106-138f-41c0-98f6-819a7bd5ee51",
      "metadata": {
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "name": "cell4"
      },
      "source": [
        "By default, Snowflake restricts network traffic from requests from public IP addresses. In order to access external data, we first need to create an [external access integration](https://docs.snowflake.com/en/developer-guide/external-network-access/creating-using-external-network-access#label-creating-using-external-access-integration-access-integration) to add `data.seattle.gov` as an allowed endpoint."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e726bdb1-6d15-43e5-b148-76a77d053de0",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "language": "sql",
        "name": "cell5"
      },
      "outputs": [],
      "source": [
        "CREATE OR REPLACE NETWORK RULE SEATTLE_OPEN_DATA_RULE\n",
        "MODE = EGRESS\n",
        "TYPE = HOST_PORT\n",
        "VALUE_LIST = ('DATA.SEATTLE.GOV');"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "17477268-b8de-4f7d-93f5-38991ffc505c",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "language": "sql",
        "name": "cell6"
      },
      "outputs": [],
      "source": [
        "CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION SEATTLE_OPEN_DATA_INTEGRATION\n",
        "ALLOWED_NETWORK_RULES = (SEATTLE_OPEN_DATA_RULE)\n",
        "ENABLED=TRUE;"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "dfd1e275-2eb2-4600-9cde-ffccd0dc7571",
      "metadata": {
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "name": "cell7"
      },
      "source": [
        "Next, we create a user-defined function (UDF) that allows users to connect outside of Snowflake and fetch the data from the remote endpoint. We attach the external access object that we created earlier to the UDF so that it has permission to access the allowed network. Read more about using external access integration in a UDF or procedure [here](https://docs.snowflake.com/en/developer-guide/external-network-access/creating-using-external-network-access#using-the-external-access-integration-in-a-function-or-procedure).\n",
        "\n",
        "\n",
        "\n",
        "The external function uses the `requests` library in Python to get the JSON response from the URL."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "676448b8-e8d8-4976-8291-7a04f7f6f96e",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "language": "sql",
        "name": "cell8"
      },
      "outputs": [],
      "source": [
        "CREATE OR REPLACE FUNCTION FETCH_ENDPOINT(URL STRING)\n",
        "returns string\n",
        "language python\n",
        "runtime_version=3.8\n",
        "handler = 'fetch_and_transform_data'\n",
        "external_access_integrations=(SEATTLE_OPEN_DATA_INTEGRATION)\n",
        "packages = ('requests')\n",
        "as\n",
        "$$\n",
        "import requests\n",
        "import _snowflake\n",
        "session = requests.Session()\n",
        "def fetch_and_transform_data(url):\n",
        "    response = requests.get(url)\n",
        "    data = response.json()\n",
        "    # Perform data transformation here\n",
        "    return data\n",
        "$$;"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c1ef6835-5950-4496-bb50-53aa12728ccb",
      "metadata": {
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "name": "cell9"
      },
      "source": [
        "Now we can call the external function on [this URL](https://data.seattle.gov/resource/65db-xm6k.json), we see the JSON string returned as output:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e7021122-f635-490c-aac9-5364e80228f1",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "language": "sql",
        "name": "cell10"
      },
      "outputs": [],
      "source": [
        "SELECT FETCH_ENDPOINT('https://data.seattle.gov/resource/65db-xm6k.json')"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8f54b6b8-2256-4f14-830a-aeb87bef9122",
      "metadata": {
        "collapsed": false,
        "name": "cell11"
      },
      "source": [
        "Next, we want to insert the JSON into the `bike_riders` table. We use Snowflake's [`PARSE_JSON`](https://docs.snowflake.com/en/sql-reference/functions/parse_json) function to process the data. \n",
        "\n",
        "Furthermore, we use the `::` operator to extract the value of the JSON field to the desired data type (STRING, NUMBER). Read more about how to work with semi-structured data in Snowflake [here](https://docs.snowflake.com/en/sql-reference/data-types-semistructured#using-values-in-a-variant)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "16dab11c-9230-407e-83af-f4dbaa77ad00",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "language": "sql",
        "name": "cell12"
      },
      "outputs": [],
      "source": [
        "insert into bike_riders\n",
        "with json_blob as \n",
        "(select parse_json(fetch_endpoint('https://data.seattle.gov/resource/65db-xm6k.json')) AS json_arr)\n",
        "select \n",
        "   value:date::STRING AS date,\n",
        "   value:fremont_bridge_nb::NUMBER AS northbound,\n",
        "   value:fremont_bridge_sb::NUMBER AS southbound\n",
        "from json_blob, TABLE(FLATTEN(input => json_arr))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4f4c17eb-2a9a-4ec8-8ffa-8a4582848743",
      "metadata": {
        "collapsed": false,
        "name": "cell13"
      },
      "source": [
        "Now that the table is loaded, we can use SQL to preview the data: "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1fcfc6ae-666a-4547-8eee-9ef02a62d097",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "jupyter": {
          "outputs_hidden": false
        },
        "language": "sql",
        "name": "cell14"
      },
      "outputs": [],
      "source": [
        "select * from bike_riders"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "0972d7a0-fd70-494c-87a6-040a6058d41d",
      "metadata": {
        "collapsed": false,
        "name": "cell15"
      },
      "source": [
        "Alternatively, we can also load this table into a Snowpark Dataframe to work with your data in Python."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7ccb7d06-94d0-4afa-8cd3-12dcdc97f83e",
      "metadata": {
        "codeCollapsed": false,
        "collapsed": false,
        "language": "python",
        "name": "cell16"
      },
      "outputs": [],
      "source": [
        "from snowflake.snowpark.context import get_active_session\n",
        "session = get_active_session()\n",
        "# Add a query tag to the session. This helps with troubleshooting and performance monitoring.\n",
        "session.query_tag = {\"origin\":\"sf_sit-is\", \n",
        "                     \"name\":\"notebook_demo_pack\", \n",
        "                     \"version\":{\"major\":1, \"minor\":0},\n",
        "                     \"attributes\":{\"is_quickstart\":1, \"source\":\"notebook\", \"vignette\":\"public_json\"}}\n",
        "df = session.table(\"bike_riders\")\n",
        "df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b7f5d1a5-40c7-46d7-9ef4-18939c639cf5",
      "metadata": {
        "codeCollapsed": false,
        "language": "python",
        "name": "cell17"
      },
      "outputs": [],
      "source": [
        "# Compute descriptive statistics for overview\n",
        "df.describe()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "26c51388-c129-4bbe-9698-c55487b94638",
      "metadata": {
        "collapsed": false,
        "name": "cell18"
      },
      "source": [
        "We can also convert our Snowpark DataFrame to pandas and operate on it with pandas."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4faec2b3-184e-49bf-8e6c-dbd303efd09a",
      "metadata": {
        "codeCollapsed": false,
        "language": "python",
        "name": "cell19"
      },
      "outputs": [],
      "source": [
        "pandas_df = df.to_pandas()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7191da17-f271-4dab-ad09-92b59a6aeefc",
      "metadata": {
        "codeCollapsed": false,
        "language": "python",
        "name": "cell20"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "pandas_df[\"TIMESTAMP\"] = pd.to_datetime(pandas_df[\"TIMESTAMP\"])"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "744f51c2-90fa-43b9-ad3f-cfc72730df53",
      "metadata": {
        "collapsed": false,
        "name": "cell21"
      },
      "source": [
        "Now, we can visualize the `TIMESTAMP` column by plot a histogram distribution of hours."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "2c6ab857-c0a3-41d0-b25d-183297afccf2",
      "metadata": {
        "codeCollapsed": false,
        "language": "python",
        "name": "cell22"
      },
      "outputs": [],
      "source": [
        "import altair as alt \n",
        "hours = pd.DataFrame(pandas_df[\"TIMESTAMP\"].dt.hour)\n",
        "alt.Chart(hours).mark_bar().encode(\n",
        "    alt.X(\"TIMESTAMP:Q\",bin = True),\n",
        "    y = 'count()',\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "92525d7f-fe5c-45bb-8500-0821d0152cb4",
      "metadata": {
        "collapsed": false,
        "name": "cell23"
      },
      "source": [
        "### Conclusion\n",
        "\n",
        "In this example, we demonstrated how you can create an external access integration and attach it to a UDF that loads data from a public endpoint. We also showed how you can load semi-structured JSON data into a Snowflake table and work with it using SQL or Python. To learn more about external network access to Snowflake, refer to the documentation [here](https://docs.snowflake.com/en/developer-guide/external-network-access/external-network-access-overview)."
      ]
    }
  ],
  "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.11.5"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}