{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 1: Setup\n",
    "\n",
    "Run imports and set up secrets. We also drop all related tables to start fresh."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2\n",
    "from psycopg2.extras import execute_values\n",
    "from timescale_vector import pgvectorizer\n",
    "\n",
    "from langchain.docstore.document import Document\n",
    "from langchain.text_splitter import CharacterTextSplitter\n",
    "from timescale_vector import client\n",
    "from langchain.embeddings.openai import OpenAIEmbeddings\n",
    "from langchain.vectorstores.timescalevector import TimescaleVector\n",
    "from datetime import timedelta, datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv, find_dotenv\n",
    "_ = load_dotenv(find_dotenv(), override=True)\n",
    "\n",
    "TIMESCALE_SERVICE_URL = os.environ[\"TIMESCALE_SERVICE_URL\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "with psycopg2.connect(TIMESCALE_SERVICE_URL) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute('''DROP TABLE IF EXISTS blog CASCADE''')\n",
    "        cursor.execute('''DROP TABLE IF EXISTS blog_embedding CASCADE''')\n",
    "        cursor.execute('''DROP TABLE IF EXISTS blog_embedding_work_queue CASCADE''')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 2: Insert data\n",
    "\n",
    "Create a blog table and insert some data into it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "with psycopg2.connect(TIMESCALE_SERVICE_URL) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute('''\n",
    "        CREATE TABLE IF NOT EXISTS blog (\n",
    "            id              SERIAL PRIMARY KEY NOT NULL,\n",
    "            title           TEXT NOT NULL,\n",
    "            content         TEXT NOT NULL,\n",
    "            url             TEXT NOT NULL\n",
    "        );\n",
    "        ''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>content</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>How to Build a Weather Station With Elixir, Ne...</td>\n",
       "      <td>This is an installment of our “Community Membe...</td>\n",
       "      <td>https://www.timescale.com/blog/how-to-build-a-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CloudQuery on Using PostgreSQL for Cloud Asset...</td>\n",
       "      <td>This is an installment of our “Community Membe...</td>\n",
       "      <td>https://www.timescale.com/blog/cloudquery-on-u...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>How a Data Scientist Is Building a Time-Series...</td>\n",
       "      <td>This is an installment of our “Community Membe...</td>\n",
       "      <td>https://www.timescale.com/blog/how-a-data-scie...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>How Conserv Safeguards History: Building an En...</td>\n",
       "      <td>This is an installment of our “Community Membe...</td>\n",
       "      <td>https://www.timescale.com/blog/how-conserv-saf...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>How Messari Uses Data to Open the Cryptoeconom...</td>\n",
       "      <td>This is an installment of our “Community Membe...</td>\n",
       "      <td>https://www.timescale.com/blog/how-messari-use...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  \\\n",
       "0  How to Build a Weather Station With Elixir, Ne...   \n",
       "1  CloudQuery on Using PostgreSQL for Cloud Asset...   \n",
       "2  How a Data Scientist Is Building a Time-Series...   \n",
       "3  How Conserv Safeguards History: Building an En...   \n",
       "4  How Messari Uses Data to Open the Cryptoeconom...   \n",
       "\n",
       "                                             content  \\\n",
       "0  This is an installment of our “Community Membe...   \n",
       "1  This is an installment of our “Community Membe...   \n",
       "2  This is an installment of our “Community Membe...   \n",
       "3  This is an installment of our “Community Membe...   \n",
       "4  This is an installment of our “Community Membe...   \n",
       "\n",
       "                                                 url  \n",
       "0  https://www.timescale.com/blog/how-to-build-a-...  \n",
       "1  https://www.timescale.com/blog/cloudquery-on-u...  \n",
       "2  https://www.timescale.com/blog/how-a-data-scie...  \n",
       "3  https://www.timescale.com/blog/how-conserv-saf...  \n",
       "4  https://www.timescale.com/blog/how-messari-use...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load your CSV file into a pandas DataFrame\n",
    "df = pd.read_csv('../openai_pgvector_helloworld/blog_posts_data.csv')\n",
    "\n",
    "# Insert it into the db\n",
    "with psycopg2.connect(TIMESCALE_SERVICE_URL) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        values = df.values.tolist()\n",
    "        insert_statement = f\"INSERT INTO blog (title, content, url) VALUES %s\"\n",
    "        execute_values(cursor, insert_statement, values) \n",
    "\n",
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 3: Create the embed_and_write function for PGVectorizer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_document(blog):\n",
    "    text_splitter = CharacterTextSplitter(\n",
    "        separator=\"\\n\",\n",
    "        chunk_size=1000,\n",
    "        chunk_overlap=200,\n",
    "    )\n",
    "    docs = []\n",
    "    for chunk in text_splitter.split_text(blog['content']):\n",
    "        content = f\"Title: {blog['title']}, contents:{chunk}\"\n",
    "        metadata = {\n",
    "            \"id\": str(client.uuid_from_time(datetime.now())),\n",
    "            \"blog_id\": blog['id'], \n",
    "            \"title\": blog['title'], \n",
    "            \"url\": blog['url'],\n",
    "        }\n",
    "        docs.append(Document(page_content=content, metadata=metadata))\n",
    "    return docs\n",
    "\n",
    "def embed_and_write(blog_instances, vectorizer):\n",
    "    TABLE_NAME = \"blog_embedding\"\n",
    "    embedding = OpenAIEmbeddings()\n",
    "    vector_store = TimescaleVector(\n",
    "        collection_name=TABLE_NAME,\n",
    "        service_url=TIMESCALE_SERVICE_URL,\n",
    "        embedding=embedding,\n",
    "        time_partition_interval=timedelta(days=30),\n",
    "    )\n",
    "\n",
    "    # delete old embeddings for all ids in the work queue\n",
    "    metadata_for_delete = [{\"blog_id\": blog['locked_id']} for blog in blog_instances]\n",
    "    vector_store.delete_by_metadata(metadata_for_delete)\n",
    "\n",
    "    documents = []\n",
    "    for blog in blog_instances:\n",
    "        # skip blogs that are deleted (title will be None because of left join)\n",
    "        if blog['title'] != None:\n",
    "            documents.extend(get_document(blog))\n",
    "\n",
    "    if len(documents) == 0:\n",
    "        return\n",
    "\n",
    "    texts = [d.page_content for d in documents]\n",
    "    metadatas = [d.metadata for d in documents]\n",
    "    ids = [d.metadata[\"id\"] for d in documents]\n",
    "    vector_store.add_texts(texts, metadatas, ids)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 4: Run PgVectorizer\n",
    "\n",
    "This has to be done any time you need to sync the database data with embeddings. We suggest running it in a scheduled job. e.g:\n",
    "- A scheduled AWS Lambda function\n",
    "- A scheduled Cloudflare worker\n",
    "- A Modal function\n",
    "- A cron job on an ECec2 instance or even on your local machine\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Created a chunk of size 6009, which is longer than the specified 1000\n",
      "Created a chunk of size 8793, which is longer than the specified 1000\n",
      "Created a chunk of size 9840, which is longer than the specified 1000\n",
      "Created a chunk of size 8469, which is longer than the specified 1000\n",
      "Created a chunk of size 6232, which is longer than the specified 1000\n",
      "Created a chunk of size 9329, which is longer than the specified 1000\n",
      "Created a chunk of size 8311, which is longer than the specified 1000\n",
      "Created a chunk of size 7167, which is longer than the specified 1000\n",
      "Created a chunk of size 8469, which is longer than the specified 1000\n",
      "Created a chunk of size 10884, which is longer than the specified 1000\n",
      "Created a chunk of size 3818, which is longer than the specified 1000\n",
      "Created a chunk of size 9799, which is longer than the specified 1000\n",
      "Created a chunk of size 7374, which is longer than the specified 1000\n",
      "Created a chunk of size 10123, which is longer than the specified 1000\n",
      "Created a chunk of size 9446, which is longer than the specified 1000\n"
     ]
    }
   ],
   "source": [
    "vectorizer = pgvectorizer.Vectorize(TIMESCALE_SERVICE_URL, 'blog')\n",
    "while vectorizer.process(embed_and_write) > 0:\n",
    "    pass"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 5: Query your embedding\n",
    "\n",
    "You can now query embeddings like you normally would"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Document(page_content='Title: How to Build a Weather Station With Elixir, Nerves, and TimescaleDB, contents:This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition,Alexander Koutmos, author of the Build a Weather Station with Elixir and Nerves book, joins us to share how he uses Grafana and TimescaleDB to store and visualize weather data collected from IoT sensors.About the teamThe bookBuild a Weather Station with Elixir and Nerveswas a joint effort between Bruce Tate, Frank Hunleth, and me.I have been writing software professionally for almost a decade and have been working primarily with Elixir since 2016. I currently maintain a few Elixir libraries onHexand also runStagira, a software consultancy company.Bruce Tateis a kayaker, programmer, and father of two from Chattanooga, Tennessee. He is the author of more than ten books and has been around Elixir from the beginning. He is the founder ofGroxio, a company that trains Elixir developers.Frank Hunlethis an embedded systems programmer, OSS maintainer, and Nerves core team member. When not in front of a computer, he loves running and spending time with his family.About the projectIn the Pragmatic Bookshelf book,Build a Weather Station with Elixir and Nerves, we take a project-based approach and guide the reader to create a Nerves-powered IoT weather station.For those unfamiliar with the Elixir ecosystem,Nervesis an IoT framework that allows you to build and deploy IoT applications on a wide array of embedded devices. At a high level, Nerves allows you to focus on building your project and takes care of a lot of the boilerplate associated with running Elixir on embedded devices.The goal of the book is to guide the reader through the process of building an end-to-end IoT solution for capturing, persisting, and visualizing weather data.Assembled weather station hooked up to development machine.One of the motivating factors for this book was to create a real-world project where readers could get hands-on experience with hardware without worrying too much about the nitty-gritty of soldering components together. Experimenting with hardware can often feel intimidating and confusing, but with Elixir and Nerves, we feel confident that even beginners get comfortable and productive quickly. As a result, in the book, we leverage a Raspberry Pi Zero W along with a few I2C enabled sensors to capture weather and environmental data. In all, the reader will capture and persist into TimescaleDB the current: altitude, atmospheric pressure, temperature, CO2 levels,TVOClevels, and the ambient light.Once the environmental data is captured on the Nerves device, it is published to a backend REST API and stored in TimescaleDB for later analytics/visualization. Luckily, TimescaleDB is an extension on top of PostgreSQL, allowing Elixir developers to use existing database tooling likeEctoto interface with time-series enabled tables.After the time-series weather data is stored in TimescaleDB, we walk the reader through how to visualize this data using the popular open-source visualization toolGrafana.Using Grafana for visualizing the weather was an easy choice given that Grafana natively supports TimescaleDB and is able to easily plot time-series data stored in TimescaleDB hypertables.✨Editor’s Note:Check outGrafana 101 video seriesandGrafana tutorialsto learn everything from building awesome, interactive visualizations to setting up custom alerts, sharing dashboards with teammates, and solving common issues.The diagram shows all of the various components of the weather station system and how they interact with one another.By the end of the book, readers have a fully-featured IoT application and API backend that can power a live Grafana dashboard in order to plot their TimescaleDB data published from their Nerves weather station.Screenshot of Grafana dashboard, showing various graphs for various weather data📖If you are interested in learning about how to build an end-to-end IoT weather monitoring solution, be sure tocheck out the book and the accompanying code. If you are interested in learning more about Nerves and Elixir,check out the Nerves documentation.Choosing (and using!) TimescaleDBFrom the onset of the book, we knew that we wanted to use a purpose-built time-series database to persist the weather station data. We wanted the project to be as realistic as possible and something that could possibly be expanded for use in the real world.With that goal in mind, TimescaleDB was an obvious choice given that PostgreSQL has become a ubiquitous database and it has great support in the Elixir community. In addition,leveraging TimescaleDB on top of PostgreSQL does not add a tremendous amount of overhead or complexity and allows new users to easily leverage the benefits of a time-series database without having to learn any new query languages or databases. Specifically, all it took for readers to start leveraging TimescaleDB was to run a single SQL command in their database migration:SELECT create_hypertable(\\'weather_conditions\\', \\'timestamp\\').\"It’s this kind of pragmatism and ease of use that makes TimescaleDB a great time-series database for projects both small and large. \"-Alexander KoutmousAll in all, leveraging TimescaleDB as the time-series database for the project worked out great and allowed us to show readers how they can set up a production-ready IoT project in a relatively short amount of time.✨Editor’s Note:To start with TimescaleDB today,sign up for a free 30-day trialorinstall TimescaleDB on your own server.Getting started advice & resourcesAny time we had questions about the inner workings of TimescaleDB, how to set it up, or what the various configuration options are, we turned to the official TimescaleDB docs. Some of the articles that helped us get started included:•Using TimescaleDB via Docker• Understanding some ofthe fundamental TimescaleDB concepts• Getting an overview of some of theTimescaleDB best practicesWe’d like to thank Alex, Bruce, and Frank for sharing their story, as well as for writing a book that makes building full-stack IoT solutions accessible for complete beginners. We congratulate them and the entire Nerves community on their success, and we cannot wait to read the final version of their book that will be released in January 2022 🎊We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Lucie Šimečková), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescaleHeroes program.The open-source relational database for time-series and analytics.Try Timescale for free', metadata={'id': 'dfb8c84e-7e5f-11ee-adf7-7f8fd74eb16d', 'url': 'https://www.timescale.com/blog/how-to-build-a-weather-station-with-elixir-nerves-and-timescaledb/', 'title': 'How to Build a Weather Station With Elixir, Nerves, and TimescaleDB', 'blog_id': 1}),\n",
       " 0.20596089434157538)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TABLE_NAME = \"blog_embedding\"\n",
    "embedding = OpenAIEmbeddings()\n",
    "vector_store = TimescaleVector(\n",
    "    collection_name=TABLE_NAME,\n",
    "    service_url=TIMESCALE_SERVICE_URL,\n",
    "    embedding=embedding,\n",
    "    time_partition_interval=timedelta(days=30),\n",
    ")\n",
    "\n",
    "res = vector_store.similarity_search_with_score(\"Weather Station\", 1)\n",
    "res[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note the document returned has the chunk of the blog that the embedding matched. To get back the full blog, you can look at the blog id in the metadata."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res[0][0].metadata['blog_id']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Addendum: Keeping your embeddings up-to-date with your PostgreSQL tables\n",
    "\n",
    "Keeping your data up to date is super-simple. Simply re-run `vectorizer.process()` (that's why we suggest running it on a schedle). It will automatically sync all the insert, updates, and deletes that were performed.\n",
    "\n",
    "For example, if you delete a blog post like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "with psycopg2.connect(TIMESCALE_SERVICE_URL) as conn:\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute('''\n",
    "          DELETE FROM blog WHERE title LIKE '%Weather Station%';\n",
    "        ''')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And re-run the vectorizer via:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "vectorizer = pgvectorizer.Vectorize(TIMESCALE_SERVICE_URL, 'blog')\n",
    "while vectorizer.process(embed_and_write) > 0:\n",
    "    pass"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then the embedding will no-longer find the deleted blog post."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Document(page_content=\"Title: How WsprDaemon Combines TimescaleDB and Grafana to Measure and Analyze Radio Transmissions, contents:This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.In this edition, Rob Robinett and Gwyn Griffiths, the creators of WsprDaemon, join us to share the work they’re doing to allow amateur radio enthusiasts to analyze transmission data and understand trends, be it their own personal noise levels or much larger space weather patterns.Amateur radio is a hobby for some three million people worldwide (see “What is Amateur Radio?” to learn more) and its technical scope is vast, examples include: designing and building satellites, devising bandwidth-efficient data communications protocols, and creating novel, low noise antennas for use in urban locations.Our project,WsprDaemon, focuses on amateurs who use the (amateur-developed!) open-sourceWeak Signal Propagation Reporter(WSPR): a protocol that uses low-power radio transmissions that probe the earth’s ionosphere to identify radio propagation paths and provide insights onspace weather. On a typical day, 2,500 amateurs may report 2.7 million WSPR “spots” to thewsprnetdatabase, whose webpage interface allows simple queries on data collected in the last two weeks.Image of radio antennae, with mountains in the background. Photo of theNorthern Utah WebSDRantenna (photo courtesy of Clint Turner)Radio signals that end up in the WsprDaemon TimescaleDB database may be received on a wide variety of antennas, from the 94-foot tower-supported multiband array in Northern Utah pictured above, to more modest 3-foot installations that you may see in many suburban or urban locations.About the TeamWe have a small, two-member volunteer core team, and we’re supported by a dozen or so beta testers and radio specialists (we typically have people from six countries on a weekly Zoom meeting). Rob Robinett, based in Berkeley California, is CEO of TV equipment manufacturerMystic Videoand he’s founded a series of Silicon Valley startups. He recently “rediscovered” amateur radio - after an absence of more than 40 years - and he's applying his software expertise to developing systems that measure short wave radio transmission conditions.Gwyn (left) & Rob (right), WsprDaemon's core volunteer team membersGwyn Griffiths, based in Southampton, UK, returned to amateur radio after retiring from a career as anocean technologist, where he worked with sensors and data from ships, undersea moorings, and robotics underwater vehicles. Gwyn focuses on the TimescaleDB components, devises Grafana dashboards to help inspire WsprDaemon users to create their own, and writes our step-by-step guides (check them outhere).About the projectWsprDaemon ingests data from the wsprnet database into TimescaleDB, allowing users to access historical data (remember, the wsprnet database shows online data from the last two weeks, and our project allows users to use older data for more robust analysis) and enabling a rich range of SQL queries.Additionally, TimescaleDB facilitates our Grafana dashboards; seeing a month of “spots” gives users a far deeper understanding about their own data, enables comparisons with other datasets, and provides a platform for further experimentation and creative graphics.Our TimescaleDB application caters to a wide spectrum of radio amateurs, from casual hobbyists to third-party developers:Some hobbyists simply want to see lists of who’s heard their transmissions in the last hour, or whotheyheard, at what strength, and where the transmissions originated.Other users want to display transmission metrics as time-series graphs, while there’s another class of users for whom ability to use aggregate functions, apply defined time buckets, derive statistics, and create heat maps and other visualizations is essential (such as the internationalHam Radio Science Citizen Investigation community).Last, third-party app developers, like theVK7JJlisting,WSPR watch, and other mapping and graphing apps, also access our WSPR data, appreciating the fast query response.The key measurement for a WSPR receiver is the signal-to-noise ratio (SNR): how strong an incoming signal is compared with the background noise. But, there is alsovital metadata, including the location of the receiver and transmitter, the operating radio frequency, and most critically - time. On average, our database takes in about 4,000 “sets” of this data from a given transmitter, typically 400kB, every two minutes.This below shows an example of SNR from three transmitters, in New York State, Italy, and Virginia.Signal-to-noise (SNR) Grafana dashboard exampleThe seven-day time-series data shown in this dashboard example provides rich information for its receiver, station KD2OM in New York State:They consistently hear N2AJX, just 16km distant, whose radio waves will have travelled over the ground, at much the same SNR throughout the day.They hear WA4KFZ in Virginia throughout most days – but with a dramatic variation in SNR. It’s at a minimum in the hours before sunrise (all times are UTC), and with peaks above the local station. This is the ionosphere at work, providing a path with less loss over 1000km than 16km for over-the-ground waves.The time-series view also allows us to see day-to-day variations, such as the shorter period of high SNR on 23rd June compared to prior days.They hear IU0ICC from Italy via the ionosphere from early evening to about 0300 local time each day, with a consistent shape to the rise and fall of SNR.While SNR is the main measurement, our users are also interested in aggregate metadata functions, which provide an overview of daily changes in the ionosphere.Our project allows them to run these more complex queries, and we bring in complementary public domain data, such as the case below where we pull in data from theUS Space Weather Prediction Center.WsprDaemon also runs complex transmission data queries and visualizationsIn this example, the top panel of the Grafana dashboard uses the WsprDaemon dataset to display a simple count of the “spots” in each 10 minute time bucket with, on the second y-axis, \\xa0a measure of the planetary geomagnetic disturbance index (kp) from the US Space Weather Prediction Center. In 2020, we’re at the minimum of the11-year sunspot cycle, so our current space weather is generally very quiet, but we’re anticipating disturbances - as well as more WSPR spots - as the sun becomes more active over the next four years.The second panel is a heat map that shows the variation in distance between the receiver in Belgium and the transmitters it’s heard over time.The third panel shows the direction of arrival at the receiver, while the bottom panel helps the user interpret all of this data, showing the local noise level and instances of overload.Editor’s note: see ourGrafana Series Overrideblog post to learn how (and why) to use two y-axes to more accurately plot your data and understand trends.Using TimescaleDBAs evidenced above, a big advantage for our users is our ability to bring disparate datasets together into one database, with one graphical visualisation tool.Our initial need was for a database and visualisation tool for radio noise measurements from a handful of stations. A colleague suggestedInfluxandGrafana, and kindly set up a prototype for us. We were hooked.We sought to expand to cover a larger range of data sets from several thousand sources. The Influx documentation was great, and we had an extended application running quickly. Initially,our query time performance was excellent, but, as we accumulated weeks of data we hit thecardinalityissue.Query times became unacceptably long, and we looked for an alternative time-series database solution.We quickly came across an objectivearticleon how to solve the cardinality problem that led us to adopt TimescaleDB.The biggest factor in choosing TimescaleDB was that it solved our cardinality problem, but there were also “nice to have” features, such as the easy-to-usetool to migrate our data from Influxand the underlying use of PostgreSQL. But, we did miss Influx’s comprehensive single-source documentation.Editor’s Note: Because we think it’s important to remain balanced and let our community members’ voice shine through, we don’t edit mentions of alternative technologies (favorable or unfavorable🙂).Current deployment & future plansOur initial TimescaleDB implementation is on a DigitalOcean Droplet (2 cores, 4GB memory 100GB SSD disk), but we are moving to our own 16 core, 192GB memory Dell server and a back-up (we’re evaluating query performance as our user base grows).As noted above,the way TimescaleDB has solved the issue of cardinality was a big selling point for us, and it’s what makes the WsprDaemon site performant for our users.When we wereusing Influx, a typical query that returned 1,000 results from a table of 12 million records and a cardinality of about 400,000 took 15-25 seconds.Now,running TimescaleDB on the same Digital Ocean Droplet(albeit with 4GB rather than the previous 2GB of memory),those same queries overwhelmingly return results in under 2s*.*as long as the data requested is within the chunk that is in memory. That’s why we’ve recently increased our Dell server memory from 24 to 192GB, to handle one-month chunks, and why it will become our primary machine.We use bash Linux shell scripts with Python to gather the data that populates our database tables. We find that batch upload usingpsycopg2.extras.execute_batchworks well for us, and our users use a variety of methods to access WsprDaemon, including Node.js andpsqlvia its command line interface.Simplified WsprDaemon architecture, showing data routes fromwsprnetand 3rd party interfacesWe already make extensive use of Grafana dashboards, and we expect to expand our capabilities - adding global map panels is just one example. But, even after extensive searching, it’s not always straightforward or obvious how to obtain the best, streamlined end result.For example, creating an animation that shows the global geographic distribution of receivers and transmitters by hour requires us to export data to CSV using psql, import the file intoOctave, generate maps on anazimuthal equidistantprojection, save these map files as PNG, and then import intoImageJto generate an AVI file.Editor’s Note: To learn more about building Grafana visualizations with TimescaleDB, check out ourstep-by-step tutorials,blogs, and “Guide to Grafana” webinar series.Our future path includes collaboration, both with others in the global amateur radio community and more data sources. We continually learn about people who have neat solutions for handling and visualising data, and, by sharing knowledge and experience, we can collectively grow and improve the tools we offer this great community. We’re keen to expand our connections to other third party data sources, such as space weather, to help our users better interpret their results.Getting started advice & resourcesWe’re non-professional database users, so we only feel qualified to speak to others with a similar minimal level of prior familiarity.As you evaluate time-series database options, of course, readindependent comparisonsand the links they provide, but also look carefully at insightful, fair-minded comparisons from TimescaleDB, e.g., onSQL vs Flux. Try to assess the advantages of different approaches foryourapplication, current and future skill sets, and requirements.Parting thoughtsWe believe that data analytics for radio amateurs is in its infancy. We’re detailing our approach and experience with TimescaleDB and Grafana in a paper at the 39th gathering of theDigital Communications Conferencein September 2020 (for the first time, the Conference will be completely virtual, which is likely to enable a larger-than-usual participation from around the world). We’ll feature some nice examples of how self inner joins help pull out features and trends from comparisons, as well as many other capabilities of interest to our users.We’d like to thank Rob & Gwyn for sharing their story, as well as for their work to create open-source, widely distributed queries, graphs, and tools. Their dedication to making transmission data accessible and consumable for the global amateur radio community is yet another testament to how technology and creativity combine to breed amazing solutions.We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@lacey butler), and we’ll go from there.Additionally, if you’re looking for more ways to get involved and show your expertise, check out theTimescale Heroesprogram.The open-source relational database for time-series and analytics.Try Timescale for free\", metadata={'id': 'dfb8f8be-7e5f-11ee-a98e-c3fdb723fcaa', 'url': 'https://www.timescale.com/blog/wsprdaemon-combines-timescaledb-grafana-analyze-radio/', 'title': 'How WsprDaemon Combines TimescaleDB and Grafana to Measure and Analyze Radio Transmissions', 'blog_id': 6}),\n",
       " 0.23517647208887005)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TABLE_NAME = \"blog_embedding\"\n",
    "embedding = OpenAIEmbeddings()\n",
    "vector_store = TimescaleVector(\n",
    "    collection_name=TABLE_NAME,\n",
    "    service_url=TIMESCALE_SERVICE_URL,\n",
    "    embedding=embedding,\n",
    "    time_partition_interval=timedelta(days=30),\n",
    ")\n",
    "\n",
    "res = vector_store.similarity_search_with_score(\"Weather Station\", 1)\n",
    "res[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nbdev_env",
   "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.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
