qwiklabs/colab-enterprise/gen-ai-demo/Weather-Generate-Insight-GenAI.ipynb (814 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "k6eIqerFOzyj"
},
"source": [
"## <img src=\"https://lh3.googleusercontent.com/mUTbNK32c_DTSNrhqETT5aQJYFKok2HB1G2nk2MZHvG5bSs0v_lmDm_ArW7rgd6SDGHXo0Ak2uFFU96X6Xd0GQ=w160-h128\" width=\"45\" valign=\"top\" alt=\"BigQuery\"> Using GenAI to postion our trucks based upon the weather"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### License"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"##################################################################################\n",
"# Copyright 2024 Google LLC\n",
"#\n",
"# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
"# you may not use this file except in compliance with the License.\n",
"# You may obtain a copy of the License at\n",
"# \n",
"# https://www.apache.org/licenses/LICENSE-2.0\n",
"# \n",
"# Unless required by applicable law or agreed to in writing, software\n",
"# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
"# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
"# See the License for the specific language governing permissions and\n",
"# limitations under the License.\n",
"###################################################################################"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Notebook Overview"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- This notebook will use GenAI to determine the best places to locate our trucks based upon current weather events. \n",
"\n",
"- Notebook Logic:\n",
" 1. Create a row, per city, in the weather_gen_ai_insight table. \n",
" - For each city:\n",
" - Query BigQuery for the current hourly weather forecast.\n",
" - BigQuery will return the results as JSON.\n",
" - Our LLM prompt will be created:\n",
" - The prompt will ask for 5 waypoints to be created based upon the weather.\n",
" - The prompt will ask for each truck to be tagged with a truck number so we can identify each truck in the results.\n",
" - The prompt will ask for the time and address of where to move each truck. \n",
" - The prompt will ask the LLM to compute the latitude and longitude of each address.\n",
" - The prompt will ask the LLM to provide an explaination of its thought process.\n",
" - The prompt will provide example JSON results so the LLM knows the output format to generate the results.\n",
" - The prompt will include the wather from BigQuery as the context.\n",
"\n",
" - A row will be inserted into the weather_gen_ai_insight table.\n",
"\n",
" 2. An update statement will be run on the weather_gen_ai_insight table which will call GENERATE_TEXT (gemini-pro) to execute the LLM prompt. \n",
" - We save our prompt in the table so we can later inspect it or reprocess the prompt.\n",
"\n",
" 3. The results of the GENERATE_TEXT are JSON so the inner JSON is then parsed into the generated_insight_text and generated_insight_json fields.\n",
"\n",
" 4. The data is then displayed."
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "2FHvD3ffG52M"
},
"source": [
"## Initialize Python"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "h-3IL5aFP6Dl"
},
"outputs": [],
"source": [
"# No need to set these\n",
"city_names=[\"New York City\", \"London\", \"Tokyo\", \"San Francisco\"]\n",
"city_ids=[1,2,3,4]\n",
"city_temperature=[\"fahrenheit\", \"celsius\", \"celsius\", \"fahrenheit\"]\n",
"number_of_coffee_trucks = \"4\""
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "HTNQKUNaRsyO"
},
"outputs": [],
"source": [
"from google.cloud import bigquery\n",
"client = bigquery.Client()"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "KOXpog83RlPG"
},
"source": [
"## Supporting Functions"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "GZ18slUFRpjG"
},
"outputs": [],
"source": [
"def RunQuery(sql):\n",
" import time\n",
"\n",
" if (sql.startswith(\"SELECT\") or sql.startswith(\"WITH\")):\n",
" df_result = client.query(sql).to_dataframe()\n",
" return df_result\n",
" else:\n",
" job_config = bigquery.QueryJobConfig(priority=bigquery.QueryPriority.INTERACTIVE)\n",
" query_job = client.query(sql, job_config=job_config)\n",
"\n",
" # Check on the progress by getting the job's updated state.\n",
" query_job = client.get_job(\n",
" query_job.job_id, location=query_job.location\n",
" )\n",
" print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
"\n",
" while query_job.state != \"DONE\":\n",
" time.sleep(2)\n",
" query_job = client.get_job(\n",
" query_job.job_id, location=query_job.location\n",
" )\n",
" print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
"\n",
" if query_job.error_result == None:\n",
" return True\n",
" else:\n",
" return False"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "qNUW0MFiUOFy"
},
"outputs": [],
"source": [
"def GetNextPrimaryKey(fully_qualified_table_name, field_name):\n",
" sql = f\"\"\"\n",
" SELECT IFNULL(MAX({field_name}),0) AS result\n",
" FROM `{fully_qualified_table_name}`\n",
" \"\"\"\n",
" # print(sql)\n",
" df_result = client.query(sql).to_dataframe()\n",
" # display(df_result)\n",
" return df_result['result'].iloc[0] + 1"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Create the GenAI Insights table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "jrWuWfUCSZUv"
},
"outputs": [],
"source": [
"%%bigquery\n",
"CREATE TABLE IF NOT EXISTS `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
"--CREATE OR REPLACE TABLE `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight` -- only use to replace the entire table\n",
"(\n",
" weather_gen_ai_insight_id INTEGER NOT NULL OPTIONS(description=\"Primary key.\"),\n",
" weather_gen_ai_insight_type STRING NOT NULL OPTIONS(description=\"The type of insight: WEATHER, EVENT, CAMPAIGN, SOCIAL, WAIT-TIME, SALES, VIDEO-AI\"),\n",
" insight_datetime TIMESTAMP NOT NULL OPTIONS(description=\"The datetime of the GenAI insight.\"),\n",
" applies_to_entity_type STRING NOT NULL OPTIONS(description=\"The type of entity to which the insight applies: Company, City, Truck, Customer\"),\n",
" applies_to_entity_id INTEGER NOT NULL OPTIONS(description=\"The id (primary key) of entity to which the insight applies.\"),\n",
" applies_to_entity_name STRING NOT NULL OPTIONS(description=\"The name to which the insight applies.\"),\n",
" llm_prompt STRING NOT NULL OPTIONS(description=\"The LLM prompt.\"),\n",
" ml_generate_json_result JSON OPTIONS(description=\"The raw JSON output of the LLM.\"),\n",
" generated_insight_text STRING OPTIONS(description=\"The generated insight in text\"),\n",
" generated_insight_json JSON OPTIONS(description=\"The generated insight in JSON\")\n",
")\n",
"CLUSTER BY weather_gen_ai_insight_id;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "O2ESzvG8uuUq"
},
"outputs": [],
"source": [
"%%bigquery\n",
"\n",
"-- Remove the current days items (so you can re-run)\n",
"DELETE\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
" WHERE CAST(insight_datetime AS DATE) = CURRENT_DATE();"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "ZrQ_gOebR2vX"
},
"source": [
"## Create the LLM prompt for each city and insert into or GenAI Insights table."
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "rUsnDL0ts9pu"
},
"source": [
"- You will need to run the Data-Bean-GenAI-Weather-Table notebook to download the current weather (for today)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "zlsrcCAmAGqY"
},
"outputs": [],
"source": [
"# Get the next primary key\n",
"weather_gen_ai_insight_id = GetNextPrimaryKey(\"${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight\",\"weather_gen_ai_insight_id\")\n",
"\n",
"# Loop for each city\n",
"for city_index in range(0, 4):\n",
" print(f\"Processing city: {city_ids[city_index]}\")\n",
"\n",
" ##############################################################################\n",
" # Get the weather data for the city and current date\n",
" ##############################################################################\n",
" sql=f\"\"\"WITH hourly_data AS\n",
" (\n",
" SELECT city_id, periods\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather`\n",
" CROSS JOIN UNNEST(JSON_QUERY_ARRAY(weather_json,'$.properties.periods')) AS periods\n",
" WHERE city_id = {city_ids[city_index]}\n",
" AND weather_date = CURRENT_DATE()\n",
" )\n",
" , hourly_fields AS\n",
" (\n",
" SELECT city_id,\n",
" CAST(SUBSTR(JSON_VALUE(periods, '$.startTime'),1,18)AS datetime) AS startTime,\n",
" CAST(SUBSTR(JSON_VALUE(periods, '$.endTime'),1,18)AS datetime) AS endTime,\n",
" ROUND(CAST(JSON_VALUE(periods, '$.temperature') AS FLOAT64),1) AS temperatureFahrenheit,\n",
" ROUND((32 - CAST(JSON_VALUE(periods, '$.temperature') AS FLOAT64)) * (5/9),1) AS temperatureCelsius, -- (32°F − 32) × 5/9 = 0°C\n",
" CAST(JSON_VALUE(periods, '$.probabilityOfPrecipitation.value') AS FLOAT64) AS probabilityOfPrecipitation,\n",
" CAST(JSON_VALUE(periods, '$.shortForecast') AS STRING) AS shortForecast\n",
" FROM hourly_data\n",
" )\n",
" , data AS\n",
" (\n",
" SELECT startTime,\n",
" endTime,\n",
" CASE WHEN city_id IN (1,4) -- US Fahrenheit\n",
" THEN temperatureFahrenheit\n",
" ELSE temperatureCelsius\n",
" END AS temperature,\n",
" probabilityOfPrecipitation,\n",
" shortForecast\n",
" FROM hourly_fields\n",
" )\n",
"\n",
" SELECT TO_JSON(data) AS weather_json\n",
" FROM data\n",
" ORDER BY startTime;\n",
" \"\"\"\n",
"\n",
" # Run the SQL to get the weather for this city\n",
" print(f\"Gathering weather for: {city_names[city_index]}\")\n",
" weather_df = RunQuery(sql)\n",
"\n",
" # Loop through the dataframe (we want a JSON string for our prompt)\n",
" weather_json = \"\"\n",
" for index, row in weather_df.iterrows():\n",
" weather_json = weather_json + row['weather_json'] + \"\\n\"\n",
"\n",
" ##############################################################################\n",
" # Get the weather id so we can link back to this record.\n",
" ##############################################################################\n",
" sql = f\"\"\"SELECT weather_id\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather`\n",
" WHERE city_id = {city_ids[city_index]}\n",
" AND weather_date = CURRENT_DATE()\"\"\"\n",
"\n",
" print(f\"Gathering weather_id for: {city_names[city_index]}\")\n",
" weather_id_df = RunQuery(sql)\n",
"\n",
" weather_id = \"\"\n",
" for index, row in weather_id_df.iterrows():\n",
" weather_id = weather_id + str(row['weather_id'])\n",
"\n",
" print(f\"weather_id: {weather_id}\")\n",
"\n",
" ##############################################################################\n",
" # Create the LLM prompt\n",
" ##############################################################################\n",
"\n",
" # Example return JSON (without the python f\"\" formatting)\n",
" json_example_data='[ ' + \\\n",
" '{ \"weather_id\": 1, \"truck_number\": 1, \"waypoint_number\": 1, \"location_address\": \"Central Park\", ' + \\\n",
" '\"location_time\": \"2023-12-03 08:00:00\", \"latitude\" : 40.736874, \"longitude\": -73.985394, ' + \\\n",
" '\"explanation\": \"Due to rain you should position this under the central park coverings.\" }, ' + \\\n",
" '{ \"weather_id\": 1, \"truck_number\" : 1, \"waypoint_number\": 2, \"location_address\": \"Hyde Park\", ' + \\\n",
" '\"location_time\": \"2023-12-03 13:00:00\", \"latitude\" : 40.736874, \"longitude\": -73.985394, ' + \\\n",
" '\"explanation\": \"The sunny weather will have lots of people in the park.\" }' + \\\n",
" ']'\n",
"\n",
" llm_prompt=f\"\"\"You need to return JSON in the below \"JSON format\".\n",
"You run a fleet of {number_of_coffee_trucks} coffee trucks in {city_names[city_index]}.\n",
"You want to maximize profits by picking the best locations, based upon the weather, in order to maximize profits.\n",
"Create a route with up to 5 waypoints for each truck based upon the below weather forecast.\n",
"The weather forecast temperature field is in {city_temperature[city_index]}.\n",
"Return the results in JSON with no special characters or formatting.\n",
"Number the trucks from 1 through {number_of_coffee_trucks} and place the result in the field \"truck_number\".\n",
"Compute the address of the location and place the result in the field \"location_address\".\n",
"Compute the time the truck should be moved to the location and place the result in the field \"location_time\".\n",
"Compute the latitude of the field \"location_address\" and place the result in the field \"latitude\".\n",
"Compute the longitude of the field \"location_address\" and place the result in the field \"longitude\".\n",
"Use the constant value of {weather_id} for the \"weather_id\" field.\n",
"Explain your logic and place the result in the field \"explanation\".\n",
"\n",
"JSON format:\n",
"{json_example_data}\n",
"\n",
"Context:\n",
"- When the probabilityOfPrecipitation is high the it is likely to be raining or snowing.\n",
"- If the weather is the same or close the same for several hours, we do not need to move the truck. Only move the truck if it has been more than 4 hours or if the weather is changing.\n",
"- Consider locations that do not have a nearby coffee shop. Take advanage of the ablity for a coffee truck to move locations.\n",
"\n",
"Weather Forecast:\n",
"{weather_json}\n",
"\"\"\"\n",
"\n",
" print(f\"Created LLM Prompt for: {city_names[city_index]}\")\n",
"\n",
" # Insert new row\n",
" sql=f\"\"\"INSERT INTO `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
" (weather_gen_ai_insight_id, weather_gen_ai_insight_type, insight_datetime, applies_to_entity_type, applies_to_entity_id, applies_to_entity_name, llm_prompt)\n",
" VALUES({weather_gen_ai_insight_id}, 'WEATHER', CURRENT_TIMESTAMP(), 'CITY', {city_ids[city_index]}, '{city_names[city_index]}',\\\"\\\"\\\"{llm_prompt}\\\"\\\"\\\")\n",
" \"\"\".replace(\" \",\"\")\n",
"\n",
" RunQuery(sql)\n",
"\n",
" # Get the next primary key\n",
" weather_gen_ai_insight_id = weather_gen_ai_insight_id + 1\n",
"\n",
" print(f\"Inserted prompt into weather_gen_ai_insight with weather_gen_ai_insight_id = {weather_gen_ai_insight_id}\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Sample Prompt with Weather JSON\n",
"```\n",
"You run a fleet of 4 coffee trucks in New York City.\n",
"You want to maximize profits by picking the best locations, based upon the weather, in order to maximize profits.\n",
"Create a route with up to 5 waypoints for each truck based upon the below weather forecast.\n",
"The weather forecast temperature field is in fahrenheit.\n",
"Return the results in JSON with no special characters or formatting.\n",
"Number the trucks from 1 through 4 and place the result in the field \"truck_number\".\n",
"Compute the address of the location and place the result in the field \"location_address\".\n",
"Compute the time the truck should be moved to the location and place the result in the field \"location_time\".\n",
"Compute the latitude of the field \"location_address\" and place the result in the field \"latitude\".\n",
"Compute the longitude of the field \"location_address\" and place the result in the field \"longitude\".\n",
"Use the constant value of 5 for the \"weather_id\" field.\n",
"Explain your logic and place the result in the field \"explanation\".\n",
"\n",
"Example Return Data:\n",
"[ { \"weather_id\": 1, \"truck_number\": 1, \"waypoint_number\": 1, \"location_address\": \"Central Park\", \"location_time\": \"2023-12-03 08:00:00\", \"latitude\" : 40.736874, \"longitude\": -73.985394, \"explanation\": \"Due to rain you should position this under the central park coverings.\" }, { \"weather_id\": 1, \"truck_number\" : 1, \"waypoint_number\": 2, \"location_address\": \"Hyde Park\", \"location_time\": \"2023-12-03 13:00:00\", \"latitude\" : 40.736874, \"longitude\": -73.985394, \"explanation\": \"The sunny weather will have lots of people in the park.\" }]\n",
"\n",
"Context:\n",
"- When the probabilityOfPrecipitation is high the it is likely to be raining or snowing.\n",
"- If the weather is the same or close the same for several hours, we do not need to move the truck.Only move the truck if it has been more than 4 hours or if the weather is changing.\n",
"- Consider locations that do not have a nearby coffee shop.Take advanage of the ablity for a coffee truck to move locations.\n",
"\n",
"Weather Forecast:\n",
"{\"endTime\":\"2024-03-14T10:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T09:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-14T11:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T10:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-14T12:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T11:00:00\",\"temperature\":59.0}\n",
"{\"endTime\":\"2024-03-14T13:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T12:00:00\",\"temperature\":63.0}\n",
"{\"endTime\":\"2024-03-14T14:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T13:00:00\",\"temperature\":65.0}\n",
"{\"endTime\":\"2024-03-14T15:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T14:00:00\",\"temperature\":68.0}\n",
"{\"endTime\":\"2024-03-14T16:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T15:00:00\",\"temperature\":69.0}\n",
"{\"endTime\":\"2024-03-14T17:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T16:00:00\",\"temperature\":68.0}\n",
"{\"endTime\":\"2024-03-14T18:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Sunny\",\"startTime\":\"2024-03-14T17:00:00\",\"temperature\":67.0}\n",
"{\"endTime\":\"2024-03-14T19:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Mostly Clear\",\"startTime\":\"2024-03-14T18:00:00\",\"temperature\":64.0}\n",
"{\"endTime\":\"2024-03-14T20:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Mostly Clear\",\"startTime\":\"2024-03-14T19:00:00\",\"temperature\":63.0}\n",
"{\"endTime\":\"2024-03-14T21:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Clear\",\"startTime\":\"2024-03-14T20:00:00\",\"temperature\":61.0}\n",
"{\"endTime\":\"2024-03-14T22:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-14T21:00:00\",\"temperature\":59.0}\n",
"{\"endTime\":\"2024-03-14T23:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-14T22:00:00\",\"temperature\":58.0}\n",
"{\"endTime\":\"2024-03-15T00:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-14T23:00:00\",\"temperature\":57.0}\n",
"{\"endTime\":\"2024-03-15T01:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-15T00:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-15T02:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-15T01:00:00\",\"temperature\":55.0}\n",
"{\"endTime\":\"2024-03-15T03:00:00\",\"probabilityOfPrecipitation\":55.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T02:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-15T04:00:00\",\"probabilityOfPrecipitation\":55.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T03:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-15T05:00:00\",\"probabilityOfPrecipitation\":55.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T04:00:00\",\"temperature\":53.0}\n",
"{\"endTime\":\"2024-03-15T06:00:00\",\"probabilityOfPrecipitation\":55.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T05:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-15T07:00:00\",\"probabilityOfPrecipitation\":44.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T06:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-15T08:00:00\",\"probabilityOfPrecipitation\":44.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T07:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-15T09:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T08:00:00\",\"temperature\":53.0}\n",
"{\"endTime\":\"2024-03-15T10:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T09:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-15T11:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T10:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-15T12:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T11:00:00\",\"temperature\":58.0}\n",
"{\"endTime\":\"2024-03-15T13:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T12:00:00\",\"temperature\":60.0}\n",
"{\"endTime\":\"2024-03-15T14:00:00\",\"probabilityOfPrecipitation\":37.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T13:00:00\",\"temperature\":62.0}\n",
"{\"endTime\":\"2024-03-15T15:00:00\",\"probabilityOfPrecipitation\":58.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T14:00:00\",\"temperature\":63.0}\n",
"{\"endTime\":\"2024-03-15T16:00:00\",\"probabilityOfPrecipitation\":58.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T15:00:00\",\"temperature\":64.0}\n",
"{\"endTime\":\"2024-03-15T17:00:00\",\"probabilityOfPrecipitation\":58.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T16:00:00\",\"temperature\":63.0}\n",
"{\"endTime\":\"2024-03-15T18:00:00\",\"probabilityOfPrecipitation\":58.0,\"shortForecast\":\"Rain Showers Likely\",\"startTime\":\"2024-03-15T17:00:00\",\"temperature\":62.0}\n",
"{\"endTime\":\"2024-03-15T19:00:00\",\"probabilityOfPrecipitation\":47.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T18:00:00\",\"temperature\":60.0}\n",
"{\"endTime\":\"2024-03-15T20:00:00\",\"probabilityOfPrecipitation\":47.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T19:00:00\",\"temperature\":58.0}\n",
"{\"endTime\":\"2024-03-15T21:00:00\",\"probabilityOfPrecipitation\":36.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T20:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-15T22:00:00\",\"probabilityOfPrecipitation\":36.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T21:00:00\",\"temperature\":55.0}\n",
"{\"endTime\":\"2024-03-15T23:00:00\",\"probabilityOfPrecipitation\":36.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T22:00:00\",\"temperature\":53.0}\n",
"{\"endTime\":\"2024-03-16T00:00:00\",\"probabilityOfPrecipitation\":36.0,\"shortForecast\":\"Chance Rain Showers\",\"startTime\":\"2024-03-15T23:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-16T01:00:00\",\"probabilityOfPrecipitation\":18.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-16T00:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-16T02:00:00\",\"probabilityOfPrecipitation\":18.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-16T01:00:00\",\"temperature\":49.0}\n",
"{\"endTime\":\"2024-03-16T03:00:00\",\"probabilityOfPrecipitation\":12.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-16T02:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-16T04:00:00\",\"probabilityOfPrecipitation\":12.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-16T03:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-16T05:00:00\",\"probabilityOfPrecipitation\":12.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-16T04:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-16T06:00:00\",\"probabilityOfPrecipitation\":12.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-16T05:00:00\",\"temperature\":46.0}\n",
"{\"endTime\":\"2024-03-16T07:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-16T06:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-16T08:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T07:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-16T09:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T08:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-16T10:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T09:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-16T11:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T10:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-16T12:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T11:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-16T13:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T12:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-16T14:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T13:00:00\",\"temperature\":57.0}\n",
"{\"endTime\":\"2024-03-16T15:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T14:00:00\",\"temperature\":59.0}\n",
"{\"endTime\":\"2024-03-16T16:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T15:00:00\",\"temperature\":58.0}\n",
"{\"endTime\":\"2024-03-16T17:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T16:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-16T18:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-16T17:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-16T19:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T18:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-16T20:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T19:00:00\",\"temperature\":51.0}\n",
"{\"endTime\":\"2024-03-16T21:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T20:00:00\",\"temperature\":51.0}\n",
"{\"endTime\":\"2024-03-16T22:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T21:00:00\",\"temperature\":51.0}\n",
"{\"endTime\":\"2024-03-16T23:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T22:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-17T00:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-16T23:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-17T01:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T00:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-17T02:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T01:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-17T03:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-17T02:00:00\",\"temperature\":49.0}\n",
"{\"endTime\":\"2024-03-17T04:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-17T03:00:00\",\"temperature\":49.0}\n",
"{\"endTime\":\"2024-03-17T05:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-17T04:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-17T06:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-17T05:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-17T07:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-17T06:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-17T08:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-17T07:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-17T09:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T08:00:00\",\"temperature\":49.0}\n",
"{\"endTime\":\"2024-03-17T10:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T09:00:00\",\"temperature\":50.0}\n",
"{\"endTime\":\"2024-03-17T11:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T10:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-17T12:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T11:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-17T13:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T12:00:00\",\"temperature\":55.0}\n",
"{\"endTime\":\"2024-03-17T14:00:00\",\"probabilityOfPrecipitation\":19.0,\"shortForecast\":\"Slight Chance Rain Showers\",\"startTime\":\"2024-03-17T13:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-17T15:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-17T14:00:00\",\"temperature\":57.0}\n",
"{\"endTime\":\"2024-03-17T16:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-17T15:00:00\",\"temperature\":57.0}\n",
"{\"endTime\":\"2024-03-17T17:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-17T16:00:00\",\"temperature\":57.0}\n",
"{\"endTime\":\"2024-03-17T18:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-17T17:00:00\",\"temperature\":56.0}\n",
"{\"endTime\":\"2024-03-17T19:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T18:00:00\",\"temperature\":55.0}\n",
"{\"endTime\":\"2024-03-17T20:00:00\",\"probabilityOfPrecipitation\":5.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T19:00:00\",\"temperature\":54.0}\n",
"{\"endTime\":\"2024-03-17T21:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T20:00:00\",\"temperature\":53.0}\n",
"{\"endTime\":\"2024-03-17T22:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T21:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-17T23:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T22:00:00\",\"temperature\":52.0}\n",
"{\"endTime\":\"2024-03-18T00:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-17T23:00:00\",\"temperature\":51.0}\n",
"{\"endTime\":\"2024-03-18T01:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T00:00:00\",\"temperature\":49.0}\n",
"{\"endTime\":\"2024-03-18T02:00:00\",\"probabilityOfPrecipitation\":0.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T01:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-18T03:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T02:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-18T04:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T03:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-18T05:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T04:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-18T06:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T05:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-18T07:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-18T06:00:00\",\"temperature\":43.0}\n",
"{\"endTime\":\"2024-03-18T08:00:00\",\"probabilityOfPrecipitation\":2.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-18T07:00:00\",\"temperature\":42.0}\n",
"{\"endTime\":\"2024-03-18T09:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T08:00:00\",\"temperature\":41.0}\n",
"{\"endTime\":\"2024-03-18T10:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T09:00:00\",\"temperature\":41.0}\n",
"{\"endTime\":\"2024-03-18T11:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T10:00:00\",\"temperature\":42.0}\n",
"{\"endTime\":\"2024-03-18T12:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T11:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-18T13:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T12:00:00\",\"temperature\":46.0}\n",
"{\"endTime\":\"2024-03-18T14:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T13:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-18T15:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T14:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-18T16:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T15:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-18T17:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T16:00:00\",\"temperature\":48.0}\n",
"{\"endTime\":\"2024-03-18T18:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Partly Sunny\",\"startTime\":\"2024-03-18T17:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-18T19:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-18T18:00:00\",\"temperature\":46.0}\n",
"{\"endTime\":\"2024-03-18T20:00:00\",\"probabilityOfPrecipitation\":11.0,\"shortForecast\":\"Mostly Cloudy\",\"startTime\":\"2024-03-18T19:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-18T21:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T20:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-18T22:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T21:00:00\",\"temperature\":43.0}\n",
"{\"endTime\":\"2024-03-18T23:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T22:00:00\",\"temperature\":43.0}\n",
"{\"endTime\":\"2024-03-19T00:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-18T23:00:00\",\"temperature\":42.0}\n",
"{\"endTime\":\"2024-03-19T01:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T00:00:00\",\"temperature\":41.0}\n",
"{\"endTime\":\"2024-03-19T02:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T01:00:00\",\"temperature\":40.0}\n",
"{\"endTime\":\"2024-03-19T03:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T02:00:00\",\"temperature\":38.0}\n",
"{\"endTime\":\"2024-03-19T04:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T03:00:00\",\"temperature\":37.0}\n",
"{\"endTime\":\"2024-03-19T05:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T04:00:00\",\"temperature\":37.0}\n",
"{\"endTime\":\"2024-03-19T06:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T05:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-19T07:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T06:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-19T08:00:00\",\"probabilityOfPrecipitation\":8.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T07:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-19T09:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T08:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-19T10:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T09:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-19T11:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T10:00:00\",\"temperature\":37.0}\n",
"{\"endTime\":\"2024-03-19T12:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T11:00:00\",\"temperature\":39.0}\n",
"{\"endTime\":\"2024-03-19T13:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T12:00:00\",\"temperature\":41.0}\n",
"{\"endTime\":\"2024-03-19T14:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T13:00:00\",\"temperature\":43.0}\n",
"{\"endTime\":\"2024-03-19T15:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T14:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-19T16:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T15:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-19T17:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T16:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-19T18:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-19T17:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-19T19:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T18:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-19T20:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T19:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-19T21:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T20:00:00\",\"temperature\":44.0}\n",
"{\"endTime\":\"2024-03-19T22:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T21:00:00\",\"temperature\":43.0}\n",
"{\"endTime\":\"2024-03-19T23:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T22:00:00\",\"temperature\":41.0}\n",
"{\"endTime\":\"2024-03-20T00:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-19T23:00:00\",\"temperature\":40.0}\n",
"{\"endTime\":\"2024-03-20T01:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T00:00:00\",\"temperature\":39.0}\n",
"{\"endTime\":\"2024-03-20T02:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T01:00:00\",\"temperature\":38.0}\n",
"{\"endTime\":\"2024-03-20T03:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T02:00:00\",\"temperature\":38.0}\n",
"{\"endTime\":\"2024-03-20T04:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T03:00:00\",\"temperature\":37.0}\n",
"{\"endTime\":\"2024-03-20T05:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T04:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-20T06:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T05:00:00\",\"temperature\":35.0}\n",
"{\"endTime\":\"2024-03-20T07:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T06:00:00\",\"temperature\":35.0}\n",
"{\"endTime\":\"2024-03-20T08:00:00\",\"probabilityOfPrecipitation\":7.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T07:00:00\",\"temperature\":35.0}\n",
"{\"endTime\":\"2024-03-20T09:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T08:00:00\",\"temperature\":35.0}\n",
"{\"endTime\":\"2024-03-20T10:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T09:00:00\",\"temperature\":36.0}\n",
"{\"endTime\":\"2024-03-20T11:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T10:00:00\",\"temperature\":37.0}\n",
"{\"endTime\":\"2024-03-20T12:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T11:00:00\",\"temperature\":39.0}\n",
"{\"endTime\":\"2024-03-20T13:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T12:00:00\",\"temperature\":42.0}\n",
"{\"endTime\":\"2024-03-20T14:00:00\",\"probabilityOfPrecipitation\":10.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T13:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-20T15:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T14:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-20T16:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T15:00:00\",\"temperature\":47.0}\n",
"{\"endTime\":\"2024-03-20T17:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T16:00:00\",\"temperature\":46.0}\n",
"{\"endTime\":\"2024-03-20T18:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Mostly Sunny\",\"startTime\":\"2024-03-20T17:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-20T19:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T18:00:00\",\"temperature\":45.0}\n",
"{\"endTime\":\"2024-03-20T20:00:00\",\"probabilityOfPrecipitation\":9.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T19:00:00\",\"temperature\":46.0}\n",
"{\"endTime\":\"2024-03-20T21:00:00\",\"probabilityOfPrecipitation\":6.0,\"shortForecast\":\"Partly Cloudy\",\"startTime\":\"2024-03-20T20:00:00\",\"temperature\":45.0}\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "PampTfgdRfqe"
},
"source": [
"## Process the LLM prompt with BigQuery"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "tbVKnddVSPKH"
},
"outputs": [],
"source": [
"# Run the LLM\n",
"temperature=.75\n",
"max_output_tokens=8192\n",
"top_p=.8\n",
"top_k=30\n",
"\n",
"sql=f\"\"\"UPDATE `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight` AS weather_gen_ai_insight\n",
" SET insight_datetime = CURRENT_TIMESTAMP(),\n",
" ml_generate_json_result = llm_query.ml_generate_text_result\n",
" FROM (SELECT *\n",
" FROM ML.GENERATE_TEXT(MODEL`${project_id}.${bigquery_data_beans_curated_dataset}.gemini_model`,\n",
" (SELECT weather_gen_ai_insight_id,\n",
" llm_prompt AS prompt\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
" WHERE (ml_generate_json_result IS NULL\n",
" OR\n",
" JSON_VALUE(ml_generate_json_result, '$.candidates[0].content') IS NULL\n",
" )\n",
" ),\n",
" STRUCT(\n",
" {temperature} AS temperature,\n",
" {max_output_tokens} AS max_output_tokens,\n",
" {top_p} AS top_p,\n",
" {top_k} AS top_k\n",
" ))\n",
" ) AS llm_query\n",
" WHERE weather_gen_ai_insight.weather_gen_ai_insight_id = llm_query.weather_gen_ai_insight_id;\n",
"\"\"\"\n",
"RunQuery(sql)"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "qAaHGZKye7LV"
},
"source": [
"## Parse and view the results"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "sKYAOE6XDMHY"
},
"outputs": [],
"source": [
"%%bigquery\n",
"\n",
"# Parse the LLM results into json fields\n",
"UPDATE `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
" SET generated_insight_text = `${project_id}.${bigquery_data_beans_curated_dataset}.gemini_model_result_as_string`(ml_generate_json_result),\n",
" generated_insight_json = `${project_id}.${bigquery_data_beans_curated_dataset}.gemini_model_result_as_json`(ml_generate_json_result)\n",
" WHERE generated_insight_text IS NULL\n",
" --AND weather_gen_ai_insight_id = 2"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "RCCogPjFSPHO"
},
"outputs": [],
"source": [
"%%bigquery\n",
"\n",
"# Display the results\n",
"SELECT weather_gen_ai_insight_id, generated_insight_text, generated_insight_json\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight`\n",
" WHERE CAST(insight_datetime AS DATE) = CURRENT_DATE()\n",
" ORDER BY weather_gen_ai_insight_id"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Sample GenAI Result\n",
"\n",
"```\n",
"[\n",
" {\n",
" \"explanation\": \"Due to rain you should position this under the central park coverings.\",\n",
" \"latitude\": 40.736874,\n",
" \"location_address\": \"Central Park\",\n",
" \"location_time\": \"2023-12-03 08:00:00\",\n",
" \"longitude\": -73.985394,\n",
" \"truck_number\": 1,\n",
" \"waypoint_number\": 1,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"The sunny weather will have lots of people in the park.\",\n",
" \"latitude\": 40.736874,\n",
" \"location_address\": \"Hyde Park\",\n",
" \"location_time\": \"2023-12-03 13:00:00\",\n",
" \"longitude\": -73.985394,\n",
" \"truck_number\": 1,\n",
" \"waypoint_number\": 2,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"Due to rain you should position this under the Washington Square Park arch.\",\n",
" \"latitude\": 40.73061,\n",
" \"location_address\": \"Washington Square Park\",\n",
" \"location_time\": \"2023-12-03 08:00:00\",\n",
" \"longitude\": -73.996226,\n",
" \"truck_number\": 2,\n",
" \"waypoint_number\": 1,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"The sunny weather will have lots of people in the park.\",\n",
" \"latitude\": 40.734711,\n",
" \"location_address\": \"Union Square Park\",\n",
" \"location_time\": \"2023-12-03 13:00:00\",\n",
" \"longitude\": -73.989566,\n",
" \"truck_number\": 2,\n",
" \"waypoint_number\": 2,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"Due to rain you should position this under the Madison Square Park arch.\",\n",
" \"latitude\": 40.74391,\n",
" \"location_address\": \"Madison Square Park\",\n",
" \"location_time\": \"2023-12-03 08:00:00\",\n",
" \"longitude\": -73.988196,\n",
" \"truck_number\": 3,\n",
" \"waypoint_number\": 1,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"The sunny weather will have lots of people in the park.\",\n",
" \"latitude\": 40.753866,\n",
" \"location_address\": \"Bryant Park\",\n",
" \"location_time\": \"2023-12-03 13:00:00\",\n",
" \"longitude\": -73.983711,\n",
" \"truck_number\": 3,\n",
" \"waypoint_number\": 2,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"Due to rain you should position this under the Columbus Circle coverings.\",\n",
" \"latitude\": 40.767943,\n",
" \"location_address\": \"Columbus Circle\",\n",
" \"location_time\": \"2023-12-03 08:00:00\",\n",
" \"longitude\": -73.981323,\n",
" \"truck_number\": 4,\n",
" \"waypoint_number\": 1,\n",
" \"weather_id\": 5\n",
" },\n",
" {\n",
" \"explanation\": \"The sunny weather will have lots of people in the area.\",\n",
" \"latitude\": 40.758895,\n",
" \"location_address\": \"Times Square\",\n",
" \"location_time\": \"2023-12-03 13:00:00\",\n",
" \"longitude\": -73.98513,\n",
" \"truck_number\": 4,\n",
" \"waypoint_number\": 2,\n",
" \"weather_id\": 5\n",
" }\n",
"]\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "Qad7nxKrcqgE"
},
"outputs": [],
"source": [
"%%bigquery\n",
"\n",
"-- Display the results\n",
"WITH location_data AS\n",
"(\n",
" SELECT city_id,\n",
" location_id,\n",
" ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY location_id) AS truck_number,\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.location`\n",
" ORDER BY location_id\n",
")\n",
"SELECT weather.weather_id,\n",
" weather.weather_date,\n",
" location_data.location_id,\n",
" weather_gen_ai_insight.applies_to_entity_name,\n",
" CAST(JSON_VALUE(json_data.truck_number) AS INT) AS truck_number,\n",
" CAST(JSON_VALUE(json_data.waypoint_number) AS INT) AS waypoint_number,\n",
" CAST(JSON_VALUE(json_data.location_address) AS STRING) AS location_address,\n",
" CAST(JSON_VALUE(json_data.location_time) AS DATETIME) AS location_time,\n",
" CAST(JSON_VALUE(json_data.latitude) AS FLOAT64) AS latitude,\n",
" CAST(JSON_VALUE(json_data.longitude) AS FLOAT64) AS longitude,\n",
" CAST(JSON_VALUE(json_data.explanation) AS STRING) AS explanation,\n",
" FROM `${project_id}.${bigquery_data_beans_curated_dataset}.weather_gen_ai_insight` AS weather_gen_ai_insight\n",
" CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(weather_gen_ai_insight.generated_insight_json)) AS json_data\n",
" INNER JOIN `${project_id}.${bigquery_data_beans_curated_dataset}.weather` AS weather\n",
" ON CAST(JSON_VALUE(json_data,'$.weather_id') AS INT64) = weather.weather_id\n",
" AND weather.weather_date = CURRENT_DATE()\n",
" AND CAST(weather_gen_ai_insight.insight_datetime AS DATE) = CURRENT_DATE()\n",
" INNER JOIN location_data\n",
" ON CAST(JSON_VALUE(json_data.truck_number) AS INT) = location_data.truck_number\n",
" AND location_data.city_id = weather_gen_ai_insight.applies_to_entity_id\n",
" ORDER BY applies_to_entity_name, weather_id, truck_number"
]
}
],
"metadata": {
"colab": {
"name": "BigQuery table",
"private_outputs": true,
"provenance": []
},
"kernelspec": {
"display_name": "Python 3",
"name": "python3"
},
"language_info": {
"name": "python"
}
},
"nbformat": 4,
"nbformat_minor": 0
}