
In an era defined by information overload, the ability to extract knowledge effectively from huge data sources is becoming increasingly important. With the breakthroughs in artificial intelligence, OpenAI's GPT-4 has emerged as a game-changer, promising to revolutionize how we process information. In this blog post, we look into the world of YouTube, a goldmine of content, and explore how GPT-4 can be harnessed to ask any question based on the source data provided by YouTube videos.

YouTube has become a hub for sharing knowledge, with an abundance of content ranging from tutorials and lectures to interviews and panel discussions. Its massive user base and extensive video library make it an ideal platform for exploring various topics. However, skimming a video is historically difficult - meaning it's even more difficult to find the right information from the huge pool of thousands and thousands of videos.
Integrating GPT-4's advanced language processing capabilities with this treasure trove of YouTube data would provide tremendous productivity gains.

Exactly this is the main objective of this blog post: to demonstrate how GPT-4 can leverage the vast YouTube data to ask intricate questions, ultimately leading to enriched knowledge acquisition and discovery.

## The potential of using GPT-4 to chat with YouTube data

YouTube has transformed into an expansive library of a wide variety of content. With billions of videos hosted on the platform, it offers an unparalleled wealth of information waiting to be tapped. Whether you're seeking tutorials on coding, in-depth lectures on quantum physics, or expert interviews on entrepreneurship, chances are you'll find it all on YouTube.

One of YouTube's distinguishing features is its incredible diversity of content types. From step-by-step tutorials that guide you through knitting patterns to university lectures unraveling complex philosophical concepts, YouTube caters to a broad range of topics and learning styles. Additionally, interviews with industry experts, panel discussions, and educational documentaries provide valuable insights and perspectives on various subjects.

Furthermore, YouTube's content creators span individuals, educational institutions, researchers, artists, and more. Each contributor brings their unique expertise, experiences, and perspectives, enabling users to access a vibrant tapestry of knowledge.

Moreover, YouTube fosters a global community, transcending geographical and cultural boundaries. Its accessibility promotes the sharing of knowledge from individuals worldwide, resulting in a rich blend of diverse perspectives. This collective knowledge, deeply rooted in personal experiences, adds depth and authenticity to the information available on YouTube.

And here lies the problem with using YouTube as a knowledge source: It's hard to extract specific information from a video compared to text. With text sources, we can skim the whole text and rather quickly extract the information we need. With videos, that's not possible. To answer a simple question we are often required to watch minutes of unrelated parts of the video.

If we use an LLM like GPT-4 on top of YouTube videos, we could provide a simple chat interface allowing us to query the video for specific questions. This question would be sent to our LLM which then uses the YouTube video information and provides a summarized answer.

![Connect GPT to YouTube videos](/blog/pictures/chatWIthYouTube/LangChain_YouTube_concept.png)

## Using LangChain to connect YouTube videos to our Large Language Model

As already demonstrated [in previous](./post-chat-with-bigquery) [posts](./post-multiple-pdfs-with-gpt) [about](./post-analyze-csv-with-gpt) how to connect LLMs to all sorts of real-world tools and information, we are going to use LangChain for this task.

The process is as follows:

**Preparation:**

1. Create a transcript of our YouTube video
2. Split the transcript into chunks of text (as the full video transcript will not fit the LLMs context window)
3. Create text embeddings from the text chunks (what embeddings are and why they are useful is introduced [in this introductory post](./post-what-is-pgvector))
4. Store these embeddings in a vector store - for convenience we are using [Chroma DB](https://www.trychroma.com/) as our local vector store.

**Query:**

1. User asks specific question which could be answered by the YouTube video.
2. Create embedding from this question and use similarity search to find the relevant text-chunks of the transcript
3. Send these chunks as well as the original prompt to GPT-3.5 or GPT-4 (or any LLM)
4. The LLM then uses these chunks to find a potential answer for the query

**Memory:**

1. And finally, we also want to add chat-memory to our application - so we not only want to ask "one-off" questions like "What is xyz?" - but we want to engage in a conversation, asking follow-up questions and providing feedback to the LLM.

While we could develop all that ourselves, we'll make our lives easier by using [LangChain](https://github.com/hwchase17/langchain). LangChain is a Python library that aims to assist in the development of applications using large language models. It allows to connect LLMs with other sources of computation or knowledge. LangChain helps with various tasks such as question answering, chatbots, agents, and data augmented generation. It provides a standard interface for working with LLMs and chains, integration with other tools, memory management, and evaluation support. The library offers comprehensive documentation and examples to guide users through the process of building applications with LLMs. Summarized, it makes integrating LLMs in real-world tools, data and applications quite easy.

![LangChain Architecture to connect LLMs to video sources](/blog/pictures/chatWIthYouTube/LangChain_YouTube_Architecture.png)

### Transcribing the video

As the above sketch outlines, transcribing the video is at the heart of how this system is going to work. We have multiple options here - we could use the [youtube transcript api](https://pypi.org/project/youtube-transcript-api/) or basically any transcription service out there.

However, as we'll use OpenAIs GPT-3.5 as our LLM model, we're also using their [Whisper AI](https://openai.com/research/whisper) for transcribing. I found it to be one of the best transcription services currently available.

## Step-by-step guide to connect YouTube video sources to LLMs

1. Install the dependencies

    ```bash
    sudo apt-get install youtube-dl ffmpeg
    ```

    ```bash
    pip install --upgrade langchain openai yt_dlp pydub chroma
    ```

2. Get the YouTube video URL of one or many videos you want to ask questions for. For the sake of this guide we are using two videos explaining LangChain (because GPT itself knows nothing about LangChain - so we can verify whether our data are actually used as source for GPT's answers):

    - https://youtu.be/_v_fgW2SkkQ
    - https://youtu.be/2xxziIWmaSA

3. Load required Python modules and set up the video loading infrastructure

    ```python
    from langchain.document_loaders.generic import GenericLoader
    from langchain.document_loaders.parsers import OpenAIWhisperParser
    from langchain.document_loaders.blob_loaders.youtube_audio import YoutubeAudioLoader

    from langchain.vectorstores import Chroma
    from langchain.chat_models import ChatOpenAI
    from langchain.embeddings.openai import OpenAIEmbeddings
    from langchain.text_splitter import RecursiveCharacterTextSplitter
    from langchain.chains import ConversationalRetrievalChain
    import os

    os.environ['OPENAI_API_KEY'] = "sk-secret"

    # Urls of the videos to use as data source
    video_urls = ["https://youtu.be/_v_fgW2SkkQ", "https://youtu.be/2xxziIWmaSA"]

    # Directory of where to save the video audio files
    download_folder = "/home/andreas/Downloads/temp/YT/"
    ```

4. Transcribe the videos to chunks of texts. This step is hilariously easy. This really shows where LangChain shines - very smart abstraction of complexity. Under the hood, these two lines of code download the videos, transform them to mp4, splits the audio into chunks to fit the WhisperAI context window and use the [OpenAI Whisper AI](https://platform.openai.com/docs/guides/speech-to-text/quickstart) to transcribe the video audio.

    ```python
    # Transcribe the videos to text
    loader = GenericLoader(YoutubeAudioLoader(video_urls, download_folder), OpenAIWhisperParser())
    docs = loader.load()
    ```

    ``docs`` contains the transcribed video documents.

5. Combine the split text chunks again use the ``RecursiveCharacterTextSplitter`` to split them again into chunk which we can use for our GPT inference. Why combine them first and split again? The WhisperAI needs different context length than our LLM, so we combine the transcribed text chunks and then split them again - to control the chunk sizes we send to the LLM.

    ```python
    # Combine docs as they were split during transcription process to fit the whisper ai context window
    combined_docs = [doc.page_content for doc in docs]
    full_transcript = " ".join(combined_docs)


    # Splitting up the text into smaller chunks for indexing
    text_splitter = RecursiveCharacterTextSplitter(chunk_size=1500, chunk_overlap=200)
    pages = text_splitter.split_text(full_transcript)
    ```

6. Create a ``Chroma db`` vector store with local persistence and create a LangChain retriever object. The ``search_kwargs`` parameter defines how many document chunks we want to add to the prompt.

    ```python
    # Create an index with local persistence
    directory = 'index_store'
    vector_index = Chroma.from_texts(pages, OpenAIEmbeddings(), persist_directory=directory)
    vector_index.persist()

    # set up ChromaDB as a in-memory vector store
    retriever = vector_index.as_retriever(search_type="similarity", search_kwargs={"k":6})
    ```

7. Initialize the chat history array (which we'll use later to store our chat history or memory) an the conversation chain - which is the LangChain interface we'll use to ask GPT-3.5-turbo to answer questions about our videos.

    ```python
    # Initialize chat history
    chat_history = []
    conv_interface = ConversationalRetrievalChain.from_llm(ChatOpenAI(model_name="gpt-3.5-turbo-16k", temperature=0), retriever=retriever)
    ```

That's all the preparation work we need. From now, it's query time. The ``ConversationalRetrievalChain`` abstracts the complexities of:

- creating embeddings from the query/prompt
- execute similarity search on our vector index to find the chunks of texts which are most relevant to answer our question
- send a refined prompt to GPT-3.5 and return the answer

```python
query = "What is langchain?"
result = conv_interface({"question": query, "chat_history": chat_history})
result["answer"]
```

> **Note:** Why do we need these embeddings, vector stores and retrievers? Why not send the full text to GPT? Well, GPT-3.5 currently has a context window of 16.000 tokens - meaning we can only send texts up to 16.000 tokens to the model (keeping in mind, that the token limit counts to both the question and the answer, so we can send even less). Splitting the source-text in reasonable chunks and using embeddings + similarity search allows us to send only the most relevant chunks of text to the model and therefore kind of circumventing the context window.

**The output:**

```markdown
'Langchain is a framework for developing applications powered by language models. It helps make the complicated parts of working and building with AI models easier by providing integration with external data and allowing language models to interact with their environment through decision-making. It offers components and tools to work with language models and customize chains, and it has a fast speed and a supportive community.'
```

As we can see, GPT-3.5 suddenly knows a lot about LangChain - despite LangChain not being part of it's training data.

Let's add this conversation to the chat history and engage in a conversation:

```python
chat_history = [(query, result["answer"])]
query = "Can you provide a longer answer to the previous question?"
result = conv_interface({"question": query, "chat_history": chat_history},include_run_info=True)
result["answer"]
```

**The output (shortened):**

```markdown
"Langchain is a framework for developing applications powered by language models. It aims to make the complicated parts of working and building with AI models easier. It does this through integration, allowing you to bring in external data such as files, other applications, and API data to your language models. [...] in their applications."
```

And again, a perfect answer. This time we make use of the chat history to not type out our question again but simply ask GPT-3.5 to refine it's answer.

Last but not least, let's query another, little more complex topic:

```python
chat_history.append((query, result["answer"]))
query = "What are chains in LangChain?"
result = conv_interface({"question": query, "chat_history": chat_history})
result["answer"]
```

**The output:**

```markdown
'Chains in LangChain refer to a sequence of steps or actions that are executed in a specific order. These chains can be predefined, where the steps are predetermined, or they can be dynamic, where the steps depend on user input or other factors. Chains allow for the customization and flexibility of language models in LangChain, enabling developers to create complex and interactive applications.'
```

Perfect answer for a quite complex topic!

### What happened under the hood?

The ``ConversationalRetrievalChain`` provides a very powerful interface, as it allows to seamlessly add historical context or memory to our chain. Now, under the hood, LangChain executes two prompts and a vector store retrieval:

- The first prompt is used to summarize the whole memory as well as the new query into a single question.
- Then, this new question is used to find the most relevant piece of information in the vector store
- Finally, the relevant chunks of information as well as the refined questions are sent to the LLM for the final answer

This not only allows to answer questions on documents - but even more refine these questions and keep a full conversation based on them - as well as previous answers.

### Addendum: Loading the vector store from disk

In the section before we stored our embeddings in a Chroma db vector store and persisted it on disk. To load the vector store for a later session, we can execute the following code snippet:

```python
vector_index = Chroma(persist_directory=directory, embedding_function=OpenAIEmbeddings())
retriever = vector_index.as_retriever(search_type="similarity", search_kwargs={"k":6})
```

## Summary

This blog post explores the potential of using OpenAI's GPT to extract knowledge from YouTube videos effectively. With YouTube being a vast source of diverse content, the ability to extract specific information from videos is challenging. By integrating GPT with YouTube data, users can ask questions and receive summarized answers, leading to optimized knowledge acquisition and discovery.

The post highlights YouTube's extensive video library, catering to various topics and learning styles, ranging from tutorials and lectures to interviews and panel discussions. YouTube's global community fosters knowledge sharing, providing a wide range of perspectives and authentic information.

To connect YouTube videos with GPT, the blog post introduces the use of LangChain, a Python library that simplifies the integration of large language models with real-world tools. LangChain allows the creation of a chat interface to query specific questions from YouTube videos. The process includes transcribing the videos, splitting the transcript into text chunks, creating embeddings from the chunks, and storing them in a vector store.

Using LangChain, the blog post presents a step-by-step guide to connect YouTube videos to GPT. It covers the installation of dependencies, obtaining video URLs, setting up the video loading infrastructure, transcribing the videos using the Whisper AI for speech-to-text, creating text chunks from the transcripts, and configuring the system to interact with GPT. The guide demonstrates querying the videos with questions, refining prompts, and engaging in a conversation with GPT.

The full code example is found below:

```python
from langchain.document_loaders.generic import GenericLoader
from langchain.document_loaders.parsers import OpenAIWhisperParser
from langchain.document_loaders.blob_loaders.youtube_audio import YoutubeAudioLoader

from langchain.vectorstores import Chroma
from langchain.chat_models import ChatOpenAI
from langchain.embeddings.openai import OpenAIEmbeddings
from langchain.text_splitter import RecursiveCharacterTextSplitter
from langchain.chains import ConversationalRetrievalChain
import os

os.environ['OPENAI_API_KEY'] = "sk-secret"

# Urls of the videos to use as data source
video_urls = ["https://youtu.be/_v_fgW2SkkQ", "https://youtu.be/2xxziIWmaSA"]

# Directory of where to save the video audio files
download_folder = "/home/andreas/Downloads/temp/YT/"

# Transcribe the videos to text
loader = GenericLoader(YoutubeAudioLoader(video_urls, download_folder), OpenAIWhisperParser())
docs = loader.load()

# Combine docs as they were split during transcription process to fit the whisper ai context window
combined_docs = [doc.page_content for doc in docs]
full_transcript = " ".join(combined_docs)

# Splitting up the text into smaller chunks for indexing
text_splitter = RecursiveCharacterTextSplitter(chunk_size=1500, chunk_overlap=200)
pages = text_splitter.split_text(full_transcript)

# Create an index with local persistence
directory = 'index_store'
vector_index = Chroma.from_texts(pages, OpenAIEmbeddings(), persist_directory=directory)
vector_index.persist()

# set up ChromaDB as a in-memory vector store
retriever = vector_index.as_retriever(search_type="similarity", search_kwargs={"k":6})

# Initialize chat history
chat_history = []
conv_interface = ConversationalRetrievalChain.from_llm(ChatOpenAI(model_name="gpt-3.5-turbo-16k", temperature=0), retriever=retriever)

# Query time
query = "What is langchain?"
result = conv_interface({"question": query, "chat_history": chat_history})
print(result["answer"])

chat_history = [(query, result["answer"])]
query = "Can you provide a longer answer to the previous question?"
result = conv_interface({"question": query, "chat_history": chat_history},include_run_info=True)
print(result["answer"])
```

### What's next?

If you want to use Language Models to chat with your BigQuery data, have a look [at this next post](./post-chat-with-bigquery).

If you are more interested in how to connect your very own pdf files to GPT and ask questions about them, have a look [at my LangChain PDF tutorial](./post-multiple-pdfs-with-gpt)

And if you are interested in how to utilize GPT-3.5/4 to automate your data analytics, have a look at this [csv analytics guide](./post-analyze-csv-with-gpt)


How to use GPT-4 to chat with YouTube videos?


Did you know that working with new and powerful Large Language Models (LLMs) - [if you want to run and train your own](../train-your-own-llm) - often requires powerful GPUs boasting up to 80GB of memory? Why is this so, one might wonder. Well, each LLM consists of Billions of parameters - basically numbers which determine the behavior of the model. And these billions of parameters need to be stored somewhere. Somewhere fast. Enter your GPU memory.

Sometimes, a single GPU, no matter how powerful, simply isn't enough to shoulder the extraordinary demands of our models. Thankfully, Large language models training can be distributed across multiple GPUs to house larger models and also shrink the training times substantially. Streamlining this process is key to staying ahead in a field characterized by perpetual urgency and innovation.

Understanding this, you may now ask, "But where do I get these high-performance GPUs from?". Well - easy. Go to Amazon and buy 2 NVIDIA A100 GPUs for $7.000 each... But wait... there's actually a less budget-blowing method: GPU on-demand.

That, dear readers, is where [RunPod](https://runpod.io?ref=o273qldc) comes into play.

[RunPod](https://runpod.io?ref=o273qldc) is a brilliant service that bridges the gap between users and high-tech resources. It offers cost-effective, easy-to-setup virtual machines furnished with single or multiple state-of-the-art GPUs. From higher-end consumer GPUs like NVIDIA RTX 3080s up to multiple NVIDIA A100s with 80GB of RAM each.

Imagine having access to an array of powerful GPUs to meet all your LLM training needs, without the substantial investment in hardware or the worries about maintenance or upgrades.

In this blog post, we will guide you through the process of acquiring these GPUs through [RunPod](https://runpod.io?ref=o273qldc) and we'll also have a look at their real-world costs and how to actively manage your budget. Let's dive right in.

## Why do we need GPUs and large amounts of VRAM for LLM training?

When it comes to training deep learning models, performance and speed are paramount. Not because you desperately need to be finished extremely fast, but because these models got so complex - with billions of parameters - that you need as much performance as possible during training to finish in a lifetime.

GPUs excel at parallel processing, allowing them to perform vast numbers of computations simultaneously. Modern deep learning frameworks leverage this parallelism to distribute the workload across GPUs, dramatically accelerating model training times. By utilizing the vast computational power of GPUs, training deep learning and large language models becomes significantly faster and more efficient.

Modern GPUs additionally provide specific cores just for AI operations - basically tuned for being extremely fast for the mathematical operations using for AI applications.

And then there is requirement for VRAM - the GPUs memory. Large language models consist of millions, and nowadays billions, of learnable parameters. These parameters store the "knowledge" of the model and are adjusted during training to optimize performance. To accommodate these vast parameter sets, ample VRAM is necessary. Each parameter requires memory to store and manipulate, and insufficient GPU VRAM can get a huge bottleneck.

## Where to get CPUs for LLM model training?

There are actually several options where to get your GPUs: 

1. **Google Colab**: Provided by Google, [Colab](https://colab.research.google.com/?utm_source=scs-index) is a platform to run Jupyter Notebooks. The platform provides quite beefy hardware - for FREE or for 10$/month if we need even better HW and RM. The free plan offers a NVIDIA T4 GPU with 12 GB of RAM which already suffices to train some of the 7 Billion parameter models. For larger models you need the paid plan for $10/month. This already offers GPUs up to 80GB of RAM. The drawbacks are quite low availability for these larger GPUs and that you can't interact with the underlying virtual machine - you just have a jupyter notebook interface.

2. **Cloud Providers**: It goes without mention that all the big cloud providers Azure, GCP and AWS provide GPU machines. Refer to their virtual machine pages for more details. For model training, they are a little behind solutions like Colab and RunPod as the setup process is more complex and you potentially don't require the machine to run for longer than some days - at least for model training.

3. **On-Premise**: Well, the most classical solution is to purchase the GPU and run it in your home and company. While this might require significant investments at first (a NVIDIA RTX 3080 which is considered the bare minimum for real LLM operations, still is around $1000 and an A100 is currently at around $4000), this might be worthwhile, if you plan to run many training operations. Considering you really need 24/7 model training, a service like RunPod (coming to that in a second) will cost up to $1500 for a single GPU! So in less than 4 months, your GPU purchase of an A100 would be amortized.
But still keep in mind that for most operations, the training process will only require a day or two of a powered up GPU - this makes on-premise economically less attractive than on-demand services

4. **RunPod**: [RunPod](https://runpod.io?ref=o273qldc) is an on-demand GPU provider, offering many NVIDIA GPUs for very attractive, hourly prices. Starting a GPU enabled virtual machine is as easy as pressing the Run button. You'll get SSH access, a quite versatile web-console and the jupyter server pre-installed. While nothing beats the Google Colab FREE pricing, the quite low hourly prices for GPUs and the ease of use make RunPod ideal for training, testing and experimenting.

While all 4 of the above options are worth considering - and the right choice under some circumstances - we at DSE found [RunPod](https://runpod.io?ref=o273qldc) to be the best option for most use cases.

## What is RunPod?

[RunPod](https://runpod.io?ref=o273qldc) is a cloud-based service that provides scalable GPU infrastructure for production. It allows users to rent cloud GPUs starting from $0.2 per hour. The platform offers container-based GPU instances that can be deployed in seconds using both public and private repositories. It also provides serverless GPU computing on a pay-per-second basis, which brings autoscaling to production, low cold-start, and security using their secure cloud.

![RunPod GPU selection](/blog/pictures/rent-a-gpu/runpodOverview.png)

[RunPod](https://runpod.io?ref=o273qldc) also offers fully managed and scaled AI endpoints for various workloads. These endpoints are used for services like Dreambooth, Stable Diffusion, Whisper, and more. The platform provides free bandwidth and a range of GPU options with different capacities and pricing.

The service is divided into two types of clouds: the **Community Cloud**, which offers vetted hosts, many compute options, and rock-bottom pricing; and the **Secure Cloud**, which operates from Tier 3/4 data-centers, provides dedicated bandwidth, failover/redundancy, strict privacy & security, and uses enterprise-grade hardware.

As for what you get when you rent a GPU: RunPod will spawn a docker container with on-demand access to the GPUs you selected.

## How to rent a GPU using RunPod?

Ok, theory done, let's dive into action.

1. Navigate to [RunPod.io](https://runpod.io?ref=o273qldc) and create an account.
2. After being logged in, you need to first buy some credits. So RunPod does not provision their GPUs if you don't have some money transferred first. It works kind of like a mobile phone prepaid card. Navigate to "[Billing](https://www.runpod.io/console/user/billing)", enter the desired amount (I suggest to start with as low as $10 for testing the waters) and hit "Pay with Card".
  ![RunPod billing](/blog/pictures/rent-a-gpu/runpodBilling.png). After entering your credit card details, you are ready to go.
3. Navigate to "Community Cloud" for the cheaper options and "Secure cloud" for the more expensive options.
   - **Secure Cloud** runs in T4 data centers by RunPod trusted partners. They come with high-reliability, redundancy, security, and fast response times to mitigate any downtimes. For any sensitive and enterprise workloads, Secure Cloud is recommended.
   - **Community Cloud** brings power in numbers and diversity spanning the whole world. Through RunPods decentralized platform, they can offer peer-to-peer GPU computing that connects individual compute providers to compute consumers. The Community Cloud hosts are invite-only and vetted by RunPod. Suggested for model training, testing and experimenting.
4. In the next screen, you can select the GPU you want to rent.
    ![RunPod GPU selection](/blog/pictures/rent-a-gpu/runpodSecureCloud.png)

    Click on the arrow on the left side of the GPU names to not only rent on GPU, but a multi-GPU cluster with up to 8 GPUs.
    Click on "Deploy".
5. In the next screen, select your pod template. Templates determine which docker image will be used for your container. So basically it determines, which software and libraries are already preinstalled. For most ML and AI applications nowadays you are fine by selecting the pytorch 2.0.1 template.

    ![Select template screen](/blog/pictures/rent-a-gpu/runpodTemplate.png)

    >**Note:** You get root access to the container spawned for you - so you might install additional software packages afterwards. You might even create your [very own template](https://docs.runpod.io/docs/templates).

    Make sure to tick "Start Jupyter Notebook" to get a Jupyter environment set up for you. Very convenient.
6. **Optional**: By clicking "Customize deployment" you can change the disk sizes, exposed ports as well as docker command to run when starting your container.

    ![Customize Deployment](/blog/pictures/rent-a-gpu/runpodCustomizeDeployment.png)
7. Click on ``Continue`` and then ``Deploy``. Your pod will be provisioned.
    ![RunPod provisioning screen](/blog/pictures/rent-a-gpu/runpodProvisioning.png)

8. Next you are automatically redirected to your new pod (VM). Click on the arrow at the top right and then "Connect" to find your connection options.

    ![RunPod pods overview](/blog/pictures/rent-a-gpu/runpodPods.png)
  
    ![RunPod connection options](/blog/pictures/rent-a-gpu/runpodConnectionOptions.png)

    Here you can access the very versatile web terminal or Jupyter lab. Simply click on "Connect to Jupyter Lab" and your preinstalled jupyter environment is presented.

    ![RunPod Jupyter Lab](/blog/pictures/rent-a-gpu/runpodJupyter.png)

That's it - you successfully provisioned a pod with one or multiple powerful GPUs - ready to tackle huge AI workloads. With the connection options presented above, your pod acts similar to a virtual machine. Just keep in mind, everything which is not in the ``/workspace`` folder will be deleted when you stop your pod - so keep all your persistent files in ``/workspace``.

## How to manage costs with RunPod?

The general cost structure on RunPod is as follows:

- You pay an hourly rate per GPU, starting from $0.2. See [this pricing chart](https://www.runpod.io/gpu-instance/pricing) for the current rates. You only pay, if your pod is running. Stopped pods are not charged.
- You pay $0.10/GB/Month for storage for running pods and $0.20/GB/Month for powered down pods.
- No bandwidth costs (this is incredible - compared to eg. cloud providers)
- RunPod provides [Savings Plans](https://docs.runpod.io/docs/savings-plans). Savings plans are a way for you to pay up-front and get a discount for it. This is great for when you know you will need prolonged access to compute. You can learn more on the about [Savings Plans here](https://docs.runpod.io/docs/savings-plans). You can save up to 50% if you prepay for 3 months. Yes - that's right. Compared to big cloud providers where you need to reserver for years and years to come, RunPod saves you big bucks if you only commit for either 1 or 3 months!

Additionally to these on-demand features, there is a [Serverless option](https://www.runpod.io/serverless-gpu) - meaning you pay per second and get autoscaling out of the box. This is a highly valuable service for model inference scenarios - especially when starting out. Imagine having 3 to 10 requests per hour and you need to buy or rent a full GPU for that? With this serverless option you get a really affordable way of running your operations.

Taking all of that into considerations, we can manage our costs as follows:

1. Don't over-provision your storage. Think about how much storage you need and choose this amount during pod creation
2. Stop pods when you don't need them. Consider using their API [found here](https://docs.runpod.io/docs/start-pod) to create automatic pod start and stop schedules
3. Use a cost savings plan. This is almost a no-brainer as you only need to commit to 1 month and already get significant savings.
4. Use the serverless option after model training for model inference.

## Summary

In conclusion, training Large Language Models (LLMs) requires significant computational resources, particularly GPUs, due to the vast amount of data they need to process. GPUs are designed to handle parallel operations efficiently, making them ideal for the matrix operations and high-speed computations required in machine learning and deep learning tasks. However, acquiring and maintaining a fleet of high-performance GPUs can be prohibitively expensive and complex, especially for small teams or individual researchers.

This is where RunPod comes into play. RunPod is a cloud-based service that provides scalable GPU infrastructure specifically designed for high-performance computing tasks like training LLMs. With RunPod, you can rent cloud GPUs starting from as low as $0.2 per hour, making it a cost-effective solution for those who need high-performance computing resources but don't have the capacity to maintain their own hardware.

RunPod offers a range of GPU options with different capacities and pricing, allowing you to choose the one that best fits your needs. It also provides serverless GPU computing on a pay-per-second basis, bringing autoscaling to production, low cold-start, and security using their secure cloud.

As we've seen in this guide, setting up and operating even a cluster of GPUs is easy and straight forward. Utilizing either Autoscaling or actively managing the GPU fleet makes sure to keep budget under control.

> **Note:** Some of the links in this blog are RunPod affiliate links, earning me a small commission if you sign up. If you don't want this, simply directly navigate to [runpod.io](https://runpod.io) without clicking my links.

### What's next?

If you want to use Language Models to chat with your BigQuery data, have a look [at this next post](./post-chat-with-bigquery).

If you are more interested in how to connect your very own pdf files to GPT and ask questions about them, have a look [at my LangChain PDF tutorial](./post-multiple-pdfs-with-gpt)

And if you are interested in how to utilize GPT-3.5/4 to automate your data analytics, have a look at this [csv analytics guide](./post-analyze-csv-with-gpt)


How to rent a 80 GB memory GPU without blowing your wallet?


We all know the power of GPT-3.5 and GPT-4 by now - it's incredible how versatile we can use these LLMs to be much more productive in our workspace. Nevertheless, under the hood, the GPT-models are just Language Models - meaning they are quite good with language tasks as well as by answering questions about their training data.

Wouldn't it be nice to also use these models to ask questions about other data sources like pdf files? Well, that's where [LangChain](https://github.com/hwchase17/langchain) enters the ring.

LangChain is a technology used to connect LLMs to real-world tools like Python, document archives, [csv files](./post-analyze-csv-with-gpt) or [databases](./post-chat-with-bigquery).

So, why not use LangChain to connect GPT to my pdf archives? This is exactly what we are going to do in this guide.

## Using LangChain and GPT-3.5 or GPT-4 to ask questions about your pdf files

As discussed [in this introductory post](./post-analyze-csv-with-gpt), LangChain is a set of tools to connect Large Language Models (LLMs) like GPT-3.5 and GPT-4 to various tools. Think python code, calculators, documents, the internet and many more.

They first of all provide easy-to-use connectors to all of these tools and even more importantly they have a concept called [Chains] - which basically is the interconnection of one or multiple actions and prompts involving an LLM.

Secondly, they provide easy-to-maintain "memory" or chat history to build powerful chat-bots. LLMs by default don't remember what you discussed just seconds ago. LangChain steps in and helps to manage this memory state.

Thirdly, they offer easy-to-use interfaces to many [Vector Stores](post-what-is-pgvector). For a quick introduction on what these stores are, have a look at this [introduction](post-what-is-pgvector). Why do we need them? Well, we can't ship the full pdf file to the LLM, as any LLM is limited by it's context window. For GPT-3.5 for example we can only have a conversion of 4000 Tokens (approx. 3000 words) - meaning any reasonably sized pdf file hit this limit. By storing so-called Embeddings - which are just high-dimensional, numeric representations of text - in a Vector Store, we can compare different texts and find their similarities. It turns out, embeddings, or vector representations of texts which are similar have a shorter vectorial distance than texts which are not similar. And vector stores are great in finding the distance of vectors.

How does this help? We can simply divide our pdf in chunks of smaller text-blocks, create embeddings of these chunks and then compare them with the question we want to ask the LLM about our pdf. For example, if we ask the question "What is photosynthesis?" we can compare the vector representation of this question with all the vectors of the text chunks of our pdf file. If there are some chunks which are similar to this question, they are highly likely to answer it. As a next step, we only send the similar chunks of text plus the question to LLM - drastically reducing the required context.

All these LangChain-tools allow us to build the following process:

1. We load our pdf files and create embeddings - the vectors described above - and store them in a local file-based vector database
2. We use vector similarity search to find the chunks needed to answer our question
3. We send these chunks and the question to GPT-3.5 and GPT-4
4. We add additional Chat capabilities to not only ask simple questions, but engage in a conversation - including conversation history

![LangChain to connect PDFs to GPT-4](/blog/chat-with-documents/LangChainWithPdf.png)

## Step by step guide for using LangChain and GPT to ask questions about your pdf files

With our basic architecture established let's get our hands dirty.

1. Install the dependency modules

    ```bash
    pip install langchain openai pypdf chroma
    ```

2. Download the sample pdf files from [ResearchGate](https://www.researchgate.net/publication/309466837_Photosynthesis) and [USGS](https://pubs.usgs.gov/pp/1802/j/pp1802j.pdf)

3. Import the required modules

    ```python
    from langchain.document_loaders import PyPDFLoader
    from langchain.vectorstores import Chroma
    from langchain.chat_models import ChatOpenAI
    from langchain.embeddings.openai import OpenAIEmbeddings
    from langchain.text_splitter import RecursiveCharacterTextSplitter
    from langchain.chains import RetrievalQA, ConversationalRetrievalChain
    import os
    ```

4. Get an OpenAI key from [the OpenAI platform](https://platform.openai.com/account/api-keys)

    ```python
    os.environ["OPENAI_API_KEY"] = "sk-secretxxxxx"
    ```

5. Load a pdf file, using the LangChain pypdf loader

    ```python
    loader = PyPDFLoader("sample_data/Photosynthesis.pdf")
    ```

6. Split the text in chunks, using one of the [LangChain Text Splitters](https://python.langchain.com/en/latest/modules/indexes/text_splitters.html). We'll use the ``RecursiveCharacterTextSplitter`` as this splitters tries to not split paragraphs - meaning it varies the length of the chunks, when it can keep paragraphs together.

    ```python
    text_splitter = RecursiveCharacterTextSplitter(chunk_size=1000, chunk_overlap=200)
    pages = loader.load_and_split(text_splitter)
    ```

7. Create a persistent, file-based vector store. We create a [Chroma](https://github.com/chroma-core/chroma) vector store. It provides good performance and is very easy to manage. It persists the vectors to the file system. Please note to set the ``persist_directory``parameter - otherwise your store will only be in-memory.

    ```python
    directory = 'index_store'
    vector_index = Chroma.from_documents(pages, OpenAIEmbeddings(), persist_directory=directory)
    vector_index.persist() # actually the Chroma client automatically persists the indexes when it is disposed - however better save then sorry :-)
    ```

8. Create the retriever and the query-interface. The retriever is used to get similar vectors from the vector store and the query-interface to then ask our questions. The parameter ``k``in the retriever sets the amount of text-chunks we want to retrieve from the vector store.

    ```python
    retriever = vector_index.as_retriever(search_type="similarity", search_kwargs={"k":6})
    # create the chain for allowing us to chat with the document
    qa_interface = RetrievalQA.from_chain_type(llm=ChatOpenAI(), chain_type="stuff", retriever=retriever, return_source_documents=True)
    ````

9. Now we are ready to query our pdf files! First, let's ask about ``aerobic respiration`` - a term described in our Photosynthesis pdf file

    ```python
    qa_interface("What is aerobic respiration? Return 3 paragraphs and a headline as markdown.")
    ```

    **Result (shortened):**

    ```python
    {'query': 'What is aerobic respiration? Return 3 paragraphs and a headline as markdown.',
    'result': '# Aerobic Respiration\n\nUnlike photosynthesis, aerobic respiration is an exergonic process (negative /Delta1G◦) with the energy released being used by the organism to power biosynthetic processes that allow growth and renewal, mechanical work (such as muscle contraction or flagella rotation) and facilitating changes in chemical concentrations within the cell (e.g. accumulation of nutrients and expulsion of waste). The use of exergonic reactions to power endergonic ones associated with biosynthesis and housekeeping in biological organisms such that the overall free energy change is negative is known as ‘coupling’.\n\nPhotosynthesis and respiration are thus seemingly the reverse of one another, with the important caveat that both oxygen formation during photosynthesis and its utilization during respiration result in its liberation or incorporation respectively into water rather than CO2. In addition, glucose is one of several possible products of photosynthesis with amino acids and lipids also being synthesized rapidly from the primary photosynthetic products.\n\nThe inefficiencies of the Rubisco enzyme mean that plants must produce it in very large amounts ( ∼30–50% of total soluble protein in a spinach leaf) to achieve the maximal photosynthetic rate.',
    'source_documents': [Document(page_content='photosynthesis acts as the ... is negative is', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 1}),
    Document(page_content='Essays in Biochemistry (2016) 60255–273\nDOI: 10.1042/EBC20160016\nFigure 1. The glob...an', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 2}),
    Document(page_content='a st h eb a s i sf o r...', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 0}),
    Document(page_content='during respiration, decay....', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 1}),
    Document(page_content='with biosynthesis ....', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 1}),
    Document(page_content='reaction, ...4.0 (CC BY).271', metadata={'source': 'sample_data/Photosynthesis.pdf', 'page': 16})]}
    ```

Look at that! The question is perfectly answered! Not only do we get the answer but also which documents were used as source files. Especially for more serious endeavours, this is very convenient. If you don't want the source documents, set the ``return_source_documents`` parameter in the ``qa_interface`` to ``False``.

### Adding additional documents to our vector store

So, now that we know how to ask questions about one specific document, let's add additional documents. These can be totally different documents about different topics. The vector similarity search will yield the correct documents when asking our questions. This is an awesome way for building a knowledge base. Just index any document you want to have as source data available and ask questions about any one of them.

```python
loader = PyPDFLoader("sample_data/graphite.pdf")

pages_new = loader.load_and_split(text_splitter)

_ = vector_index.add_documents(pages_new)
vector_index.persist()
```

As you might notice, the process is more or less the same as for the first document:

1. Load the document
2. Split the document, using the same text splitter as before
3. Add the document to our existing index

That's all the magic - now we can ask questions to either one of the documents.

### Adding memory to our conversations

Up until now we used a [RetrievalQA chain](https://python.langchain.com/en/latest/modules/chains/index_examples/question_answering.html) - which is a LangChain chain type specifically tailored for loading chunks of documents from a vector store and asking a single question about them.

However, sometimes it would be convenient to really have a full conversation about a document and being able to refer to already discussed things.

For such a thing to work, our system needs memory or a conversation history. Conveniently, LangChain got as covered also here. Instead of the ``RetrievalQA`` - chain we'll use the ``ConversationalRetrievalChain``.

```python
conv_interface = ConversationalRetrievalChain.from_llm(ChatOpenAI(temperature=0), retriever=retriever)
```

Next, we initialize our chat history - this might be a simple list.

```python
chat_history = []
query = "What is photosyntheses?"
```

And that's all the preparation we need. Let's start our chat conversation.

```python
result = conv_interface({"question": query, "chat_history": chat_history})
result["answer"]
```

**Result:** 'Photosynthesis is the process of converting water and CO2 into complex organic molecules such as carbohydrates and oxygen. It involves two reactions, the light reactions and the dark reactions, which occur in separate parts of the chloroplast. The light reactions involve the splitting of water into oxygen, protons and electrons, while the dark reactions involve the reduction of CO2 to carbohydrate. Photosynthesis is the primary energy input into the global food chain and is essential for the formation of the ozone layer, the evolution of aerobic respiration and complex multicellular life.'

Ok, this is nice - but a little too long, right? As we have a chat interface - let's simply ask to shorten this sentence. Before that however, let's not forget to add the previous conversation to our chat history.

```python
# Add previous conversation to chat history
chat_history.append((query, result["answer"]))

# Query to shorten the last sentence
query = "Can you shorten this sentence please?"
result = conv_interface({"question": query, "chat_history": chat_history})
result["answer"]
```

**Result:** 'Photosynthesis is the process by which plants use light energy to convert water and carbon dioxide into oxygen and energy-rich organic compounds. This process sustains virtually all life on Earth, providing oxygen and food, and forms the basis of global food chains.

Voila, here you have it. Without specifying which sentence to shorten, LangChain was able to automatically extract this information from the chat history. From here, we could simply continue our conversation, until we hit the context limit of the LLM.

#### What happens under the hood`

The ``ConversationalRetrievalChain`` provides a very powerful interface, as it allows to seamlessly add historical context or memory to our chain. Now, under the hood, LangChain executes two prompts and a vector store retrieval:

- The first prompt is used to summarize the whole memory as well as the new query into a single question.
- Then, this new question is used to find the most relevant piece of information in the vector store
- Finally, the relevant chunks of information as well as the refined questions are sent to the LLM for the final answer

This is great because it not only smartly combines history and new questions - but it also drastically shortens the required context, as you don't always need to send the full history to the LLM.

### Loading persisted embeddings

The complete this guide, let's also have a look at how to load previously persisted vector data/embeddings from disk.

```python
vector_index = Chroma(persist_directory=directory, embedding_function=OpenAIEmbeddings())
```

That's all we need - now you can use this index in the retriever as before.

## Summary

In this blog post, we demonstrated how to use LangChain to connect multiple PDF files to GPT-3.5 and GPT-4 and engage in a conversation about these files. Through LangChain, users can leverage GPT models to ask questions about other data sources like PDFs.

The post provided a step-by-step guide on using LangChain and GPT to ask questions about PDF files. It began by installing the necessary dependencies, then moved on to loading a sample PDF file and splitting its text into smaller chunks for processing. We then created a local file-based vector store to save the PDF's embeddings and built a retriever and a query interface to find relevant text blocks and ask questions to GPT models.

By following the guide, users can query their PDF files, add additional documents to their vector stores, and engage in a conversation with their data by keeping track of chat history. The post also covered loading previously persisted vector data from disk.

The result is a powerful and flexible way to interact with data from multiple sources, perfect for building a knowledge base or working with diverse documents.

For reference, find here the full code example:

```python
from langchain.document_loaders import PyPDFLoader
from langchain.vectorstores import Chroma
from langchain.chat_models import ChatOpenAI
from langchain.embeddings.openai import OpenAIEmbeddings
from langchain.text_splitter import RecursiveCharacterTextSplitter
from langchain.chains import RetrievalQA, ConversationalRetrievalChain
import os

# OpenAI platform key
os.environ["OPENAI_API_KEY"] = "sk-secretxxxxx"

# Load pdf file and split into chunks
loader = PyPDFLoader("sample_data/Photosynthesis.pdf")
text_splitter = RecursiveCharacterTextSplitter(chunk_size=1000, chunk_overlap=200)
pages = loader.load_and_split(text_splitter)

# Prepare vector store
directory = 'index_store'
vector_index = Chroma.from_documents(pages, OpenAIEmbeddings(), persist_directory=directory)
vector_index.persist() # actually the Chroma client automatically persists the indexes when it is disposed - however better save then sorry :-)

# Prepare the retriever chain
retriever = vector_index.as_retriever(search_type="similarity", search_kwargs={"k":6})
qa_interface = RetrievalQA.from_chain_type(llm=ChatOpenAI(), chain_type="stuff", retriever=retriever, return_source_documents=True)

# First query
print(qa_interface("What is aerobic respiration? Return 3 paragraphs and a headline as markdown."))

#Adding additional docs
loader = PyPDFLoader("sample_data/graphite.pdf")

pages_new = loader.load_and_split(text_splitter)

_ = vector_index.add_documents(pages_new)
vector_index.persist()

#Adding memory
conv_interface = ConversationalRetrievalChain.from_llm(ChatOpenAI(temperature=0), retriever=retriever)

chat_history = []
query = "What is photosyntheses?"

# First chat query
result = conv_interface({"question": query, "chat_history": chat_history})
print(result["answer"])

# Second query, using the previous queries as memory
# Add previous conversation to chat history
chat_history.append((query, result["answer"]))

# Shorten the last sentence
query = "Can you shorten this sentence please?"
result = conv_interface({"question": query, "chat_history": chat_history})
print(result["answer"])
```

### What's next?

If you want to analyze csv files and even create plots from your csvs, have a look [at this next post](./post-analyze-csv-with-gpt) in this LangChain blogging series.

If you want to "chat" with your BigQuery data, have a look at [this tutorial](./post-chat-with-bigquery), detailing how to connect GPT to BigQuery.

How to use LangChain and GPT to ask question on multiple pdf documents


In [this post](./post-analyze-csv-with-gpt) we introduced the concept of how to use [LangChain](https://github.com/hwchase17/langchain) to use GPT to analyze csv data. This helped us to simply ask ordinary human-like questions and the LLM would create code to analyze the CSV file.

LangChain was used to implement an agent-concept, which basically took our questions, reformulated them, sent them to GPT and asked GPT to write code to analyze the file.

Now, what if we don't want to export csv files anymore, but want to ask questions directly based on our database? Eg. in [our modern data warehouse](./post-what-and-why-datawarehouse).

Why, do you ask? Well, the main use case is quite big: If you have business data in your central warehouse - or in any database - your business users most likely are very interested in these data. Nowadays one needs to know SQL to being able to interact with these data - making it necessary for the business users to contact the data team even for very rudimentary questions. Having a tool which would allow business-users to interact with databases by simple, human-like language would drastically reduce data-team workload and increase business productivity.

Well, LangChain conveniently provides an agent for this kind of task - allowing us to implement such a function with quite little python code.

## Using LangChain and GPT to query BigQuery data

As [previously discussed](./post-analyze-csv-with-gpt), LangChain is a set of tools to connect Large Language Models (LLMs) like GPT to real-world tools. This might be python code, calculators, documents or the internet.

Additionally, they provide a powerful [Agent](https://python.langchain.com/en/latest/modules/agents.html) concept: Agents take our prompts and questions and asks the LLM for what tools the agent might use. So we don't need to create our own, fixed chains of questions to the LLM, but the agent itself - sparring with the LLM - decides which tools to use and which prompts to send to the LLM.

![Simplified LangChain agent concept](/blog/bigquery-with-llms/LangChainAgent.png)

The agent therefore executes all the back and forth between itself and the LLM - until our question is answered. (Note that there is a certain iteration limit included to prevent endless loops. If after 10 iterations the answer is not found, the execution will stop).

LangChain offers basic, general agents but also several different optimized agents for various use-cases. Conveniently for us, they provide an [SQL Database Agent](https://python.langchain.com/en/latest/modules/agents/toolkits/examples/sql_database.html), which is optimized for creating database access chats. This database agent is equipped with the following tools out of the box:

- **QuerySQLDataBaseTool**: Tool for querying the database
- **InfoSQLDatabaseTool**: Tool for getting metadata about a SQL database
- **QueryCheckerTool**: Tool to check if a query is correct. This actually is quite a nice tool - it will ask the LLM to check the query for validity and potentially correct itself
- **ListSQLDatabaseTool**: Tool for getting tables names

It is worth noting, that these tools use [SQLAlchemy](https://www.sqlalchemy.org/) as database abstraction layer - so any database supported by SQLAlchemy is theoretically able to be queried with the SQL Database Agent.

> **One note before we start**: The context window of LLMs and especially GPT-3.5 are quite limited. While the agents seldomly send data themselves to the LLM, they quite often need to inspect the dataset and table schemas - meaning if you have a lot of tables in the dataset or you have a very wide table which you want to query, it might bee too much for the context window. I therefore suggest to create a dedicated dataset as well as a dedicated, very well-groomed and narrow table which you use for interaction with LLMs.
>
>(By the way this limitation also shows that Data Engineers have quite a bright future ahead. While analyzing data using LLMs and agents will get stronger and stronger - you still need very well-prepared data in first place. Good for our Data Engineers.)

## Step by step guide for using LangChain and GPT to chat with BigQuery

As we established the basic architecture of what we want to build, this section contains detailed instructions on how to build this Chat with BigQuery solution.

### Prerequisites

This article and guide is mainly about how to create a ChatInterface for Google BigQuery rather Google BigQuery and Google Cloud Management itself. Therefore, we assumes you have [set up a google cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects) and [activated billing for this project](https://cloud.google.com/billing/docs/how-to/modify-project).

Furthermore, you should have enabled the following Google Cloud Project APIs:

1. [IAM API](https://console.cloud.google.com/flows/enableapi?apiid=iam.googleapis.com&redirect=https://console.cloud.google.com)
2. [BigQuery API](https://console.cloud.google.com/apis/library/bigquery.googleapis.com)

For authentication:

1. Create [a service account](https://cloud.google.com/iam/docs/service-accounts-create#creating), with the following Bigquery Roles:

   - BigQuery User
   - BigQuery Data Viewer
   - BigQuery Job User

2. Download [a service account key json](https://cloud.google.com/iam/docs/keys-create-delete#creating)

If you want to follow this guide step-by-step:

1. Download the dataset from [my website](https://datascienceengineer.com/churn-dataset.csv). The dataset contains information about churned users of a telecom provider.
2. Create a new BigQuery dataset and [import the csv file](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#loading_csv_data_into_a_table) as BigQuery table.

> **Note:** These last two steps are highly optional, as you may simply use your own data to follow this guide.

### Implementation

1. Install the required dependencies:

    ```bash
    pip install langchain openai chromadb tiktoken tabulate sqlalchemy sqlalchemy-bigquery google-cloud-bigquery
    ```

2. Let's import the modules, define the table you want to read and create an [sqlalchemy connection string](https://docs.sqlalchemy.org/en/20/core/engines.html)
    ```python
    from google.cloud import bigquery
    from sqlalchemy import *
    from sqlalchemy.engine import create_engine
    from sqlalchemy.schema import *
    import os

    from langchain.agents import create_sql_agent
    from langchain.agents.agent_toolkits import SQLDatabaseToolkit
    from langchain.sql_database import SQLDatabase
    from langchain.llms.openai import OpenAI
    from langchain.agents import AgentExecutor

    service_account_file = "/path/to/your/service-account-key.json" # Change to where your service account key file is located
    project = "devopsandmore"
    dataset = "langchain_test"
    table = "churn_table"
    sqlalchemy_url = f'bigquery://{project}/{dataset}?credentials_path={service_account_file}'
    ````

3. Get an OpenAI key from [the OpenAI platform](https://platform.openai.com/account/api-keys)

    ```python
    os.environ["OPENAI_API_KEY"] = "sk-secretxxxxx"
    ```

4. Set up the langchain agent

    ```python
    db = SQLDatabase.from_uri(sqlalchemy_url)
    llm = OpenAI(temperature=0, model="text-davinci-003")

    toolkit = SQLDatabaseToolkit(db=db, llm=llm)

    agent_executor = create_sql_agent(
        llm=llm,
        toolkit=toolkit,
        verbose=True,
        top_k=1000,
    )
    ```

    Setting ``verbose=True`` will provide us with the chain of thoughts of the agent - so we see all the intermediate steps taken by the agent. This is quite interesting to learn what's going on. If you only want to have the result, set this parameter to ``False``.

    Setting ``top_k`` to 1000 will limit the SQL results to 1000 rows.

5. Run your query

    ```python
    agent_executor.run("How many male users churned? ")
    ```

**Result:**

```bash
> Entering new AgentExecutor chain...
Action: list_tables_sql_db
Action Input: ""
Observation: churn_table
Thought: I should check the schema of the churn_table to see what columns I can query.
Action: schema_sql_db
Action Input: "churn_table"
Observation: 
CREATE TABLE `churn_table` (
	`customerID` STRING, 
	`gender` STRING, 
	`SeniorCitizen` INT64, 
	`Partner` BOOL, 
	`Dependents` BOOL, 
	`tenure` INT64, 
	`PhoneService` BOOL, 
	`MultipleLines` STRING, 
	`InternetService` STRING, 
	`OnlineSecurity` STRING, 
	`OnlineBackup` STRING, 
	`DeviceProtection` STRING, 
	`TechSupport` STRING, 
	`StreamingTV` STRING, 
	`StreamingMovies` STRING, 
	`Contract` STRING, 
	`PaperlessBilling` BOOL, 
	`PaymentMethod` STRING, 
	`MonthlyCharges` FLOAT64, 
	`TotalCharges` STRING, 
	`Churn` BOOL
)

/*
3 rows from churn_table table:
customerID	gender	SeniorCitizen	Partner	Dependents	tenure	PhoneService	MultipleLines	InternetService	OnlineSecurity	OnlineBackup	DeviceProtection	TechSupport	StreamingTV	StreamingMovies	Contract	PaperlessBilling	PaymentMethod	MonthlyCharges	TotalCharges	Churn
9732-OUYRN	Female	0	True	False	49	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Credit card (automatic)	19.0	918.7	False
0661-KQHNK	Female	0	True	True	6	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Credit card (automatic)	19.0	105.5	False
4709-LKHYG	Female	0	True	True	29	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Electronic check	20.0	540.05	False
*/
Thought: I should query the churn_table for the number of male users who churned.
Action: query_sql_db
Action Input: SELECT COUNT(*) FROM churn_table WHERE gender = 'Male' AND Churn = True
Observation: [(930,)]
Thought: I now know the final answer
Final Answer: 930 male users churned.

> Finished chain.
```

Isn't that something? We can see that the agent did some quite logical things. Keep in mind - we only asked the agent how many male users churned. No table-name, no columns, etc. It was able to figure it out on it's own. The output also shows us, what it did:

1. It checked the available tables
2. Then it checked the columns as well as the first 3 rows of the table to use (as there is only one, not much to figure - but I did some tests with multiple tables and for as long as the table names are quite descriptive, the agent will figure out which table to use!)
3. Finally, it figured which query to use and provided the correct answer!

Let's try a little more advanced query, trying to query for more information at once:

```python
agent_executor.run("""How many users churned which had internet service? 
How many churned which had no internet service? 
And for each of the groups, how many did not churn?""")
```

**Result:**
```bash

> Entering new AgentExecutor chain...
Action: list_tables_sql_db
Action Input: ""
Observation: churn_table
Thought: I should check the schema of the churn_table to see what columns I can use.
Action: schema_sql_db
Action Input: "churn_table"
Observation: 
CREATE TABLE `churn_table` (
	`customerID` STRING, 
	`gender` STRING, 
	`SeniorCitizen` INT64, 
	`Partner` BOOL, 
	`Dependents` BOOL, 
	`tenure` INT64, 
	`PhoneService` BOOL, 
	`MultipleLines` STRING, 
	`InternetService` STRING, 
	`OnlineSecurity` STRING, 
	`OnlineBackup` STRING, 
	`DeviceProtection` STRING, 
	`TechSupport` STRING, 
	`StreamingTV` STRING, 
	`StreamingMovies` STRING, 
	`Contract` STRING, 
	`PaperlessBilling` BOOL, 
	`PaymentMethod` STRING, 
	`MonthlyCharges` FLOAT64, 
	`TotalCharges` STRING, 
	`Churn` BOOL
)

/*
3 rows from churn_table table:
customerID	gender	SeniorCitizen	Partner	Dependents	tenure	PhoneService	MultipleLines	InternetService	OnlineSecurity	OnlineBackup	DeviceProtection	TechSupport	StreamingTV	StreamingMovies	Contract	PaperlessBilling	PaymentMethod	MonthlyCharges	TotalCharges	Churn
9732-OUYRN	Female	0	True	False	49	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Credit card (automatic)	19.0	918.7	False
0661-KQHNK	Female	0	True	True	6	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Credit card (automatic)	19.0	105.5	False
4709-LKHYG	Female	0	True	True	29	True	No	No	No internet service	No internet service	No internet service	No internet service	No internet service	No internet service	One year	False	Electronic check	20.0	540.05	False
*/
Thought: I can use the Churn and InternetService columns to answer the question.
Action: query_checker_sql_db
Action Input: SELECT COUNT(*) AS num_users, InternetService, Churn FROM churn_table GROUP BY InternetService, Churn LIMIT 1000
Observation: 

SELECT COUNT(*) AS num_users, InternetService, Churn 
FROM churn_table 
WHERE InternetService IS NOT NULL 
AND Churn IS NOT NULL 
GROUP BY InternetService, Churn 
LIMIT 1000
Thought: This query looks correct, I can now execute it.
Action: query_sql_db
Action Input: SELECT COUNT(*) AS num_users, InternetService, Churn FROM churn_table WHERE InternetService IS NOT NULL AND Churn IS NOT NULL GROUP BY InternetService, Churn LIMIT 1000
Observation: [(1413, 'No', False), (1962, 'DSL', False), (1799, 'Fiber optic', False), (459, 'DSL', True), (1297, 'Fiber optic', True), (113, 'No', True)]
Thought: I now know the final answer
Final Answer: There were 1413 users with no internet service who did not churn, 1962 users with DSL who did not churn, 1799 users with fiber optic who did not churn, 459 users with DSL who churned, 1297 users with fiber optic who churned, and 113 users with no internet service who churned.

> Finished chain.
```

I mean holy cow, look at that! First try we get this quite elaborate result. And we can also see the query which was used for the result:

```SQL
SELECT COUNT(*) AS num_users, InternetService, Churn 
FROM churn_table 
WHERE InternetService IS NOT NULL 
AND Churn IS NOT NULL 
GROUP BY InternetService, Churn 
LIMIT 1000
```

Considering, that we simply used "words" to describe the output we want, I'm very much impressed by how far LangChain and GPT were able to get!

From here, I'd suggest you to experiment with different prompts and find what works and does not.

## Summary

This guide demonstrates how to use LangChain and GPT to interact with your BigQuery data. LangChain agents can create queries by analyzing questions in natural language and executing back and forth interactions with GPT until the answer is found. The article includes a step-by-step guide to set up the required modules, import data, and execute queries through the LangChain agent. By following this guide, users can use GPT to query data and find insights without having to resort to SQL queries. This is especially useful to enable business users to interact with (well-prepared) databases.

It is very exciting to experience how far the GPT and LLM ecosystem has come and how easily we can use tools like LangChain to connect LLMs to real-world applications.

For reference, this is all the code we needed to create our chat with BigQuery application:

```python
from google.cloud import bigquery
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
import os

from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.llms.openai import OpenAI
from langchain.agents import AgentExecutor

service_account_file = "/path/to/your/service-account-key.json" # Change to where your service account key file is located
project = "devopsandmore"
dataset = "langchain_test"
table = "churn_table"
sqlalchemy_url = f'bigquery://{project}/{dataset}?credentials_path={service_account_file}'

# OpenAI API key
os.environ["OPENAI_API_KEY"] = "sk-secretxxxxx"

# Set up langchain
db = SQLDatabase.from_uri(sqlalchemy_url)
llm = OpenAI(temperature=0, model="text-davinci-003")
toolkit = SQLDatabaseToolkit(db=db, llm=llm)
agent_executor = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    verbose=True,
    top_k=1000,
)

# First query
agent_executor.run("How many male users churned? ")

# Second query
agent_executor.run("""How many users churned which had internet service? 
How many churned which had no internet service? 
And for each of the groups, how many did not churn?""")
```

### What's next?

If you don't want to only "chat" with your BigQuery data, but also analyze csv files and even create plots from your csvs, have a look [at this next post](./post-analyze-csv-with-gpt) in this LangChain blogging series.

If you are more interested in how to connect your very own pdf files to GPT and ask questions about them, have a look [at my LangChain PDF tutorial](./post-multiple-pdfs-with-gpt)

And if you want to know, how to rent a beefy GPU for your own LLM experiments, [check out this guide](./post-rent-gpu-without-blowing-your-wallet)

Using LangChain and GPT to chat with your BigQuery data


Large Language Models (LLMs) and especially GPT-4 conquered the world by storm. This technology is the first of it's kind which allows humans to interact with an advanced artificial intelligence system by simply using human language. Breathtaking results were achieved across many industries.

Also the tooling around these LLMs got quite intriguing. LLMs are by definition very good in interpreting language and creating answers which are most probable to fit to the prompt or question - so they are, as the name suggests, language models. This is especially true for the english language, but also many other "human" languages and - interestingly - also for programming languages. OpenAI and many others have done a tremendous job in training these models and code and therefore made them quite well versed for answering questions around coding.
However, in it's core they are just language model - this makes them not very good for mathematical problems and data analytics.

You know what's good in math and data analytics? Python. So... we have a technology which is great in understanding human input and language. And it can write code. And we have a programming language which is good for data analytics. What if... we could ask our LLM to use code to do data analytics?

## How to enable GPT-4 to use python?

As mentioned above, we'd like to use LLMs - GPT-4 in this example - to simply ask questions in human language (like "How many users did churn last month"?) based on data in a csv file. ChatGPT nowadays supports uploading CSV files but - privacy concerns aside - it just simply analyzes the text in the file and provides an answer based on it's training as a language model. Which is not what we need in terms of data analytics (LLMs are bad at data analytics!). However, we know that GPT-4 is actually quite good in using python.

So what we want to achieve is a system where we interact with GPT-4 and GPT-4 creates and executes some python code which analyzes the csv file. GPT-4 in the end returns the result to us. So we never actually need to write or interact with any Python. Furthermore, we also don't want to send the csv file to GPT because - first - it would definitely hit the context limit of GPT and - second - for privacy reasons.

![Asking GPT-4 to analyze a csv file](/blog/analyze-csv-with-llms/gpt_lc.png)

While we could theoretically develop this system on our own, there is an amazing tool out there called [LangChain](https://github.com/hwchase17/langchain) which does almost all the required "glueing". In short, LangChain is a library which assists in connecting LLMs with data sources and tools. It basically enables GPT-4 to use tools like web search, calculators or - yes - Python.

As you'll see in the next chapter, LangChain is one hell of a tool - abstracting away almost any complexity involved in connecting LLMs with real-world tools.

## Step-by-step guide to using LangChain to analyze CSV data with GPT-4

Now that the basics are done, let's see how we can "teach" GPT-4 to analyze a real-world dataset. We are going to use a well-known dataset about churned users of a telecom provider. It has quite a good amount of data and contains interesting content.

1. Let's install the required dependencies

    ```bash
    pip install langchain openai chromadb tiktoken tabulate matplotlib
    ```

    Execute the following steps in a jupyter notebook or python script.

2. Import the required modules

    ```python
    import os
    import pandas as pd

    from langchain.agents import create_csv_agent
    from langchain.llms import OpenAI
    ```

3. Get an OpenAI key from [the OpenAI platform](https://platform.openai.com/account/api-keys)

4. Download the dataset from [my website](https://datascienceengineer.com/churn-dataset.csv). The dataset contains information about churned users of a telecom provider.

5. Create a LangChain agent. Agents with LangChain are responsible to decide which tool (in our case python) to use and how to use these tools. Agents simply reformulate the user input (our prompt) and send a prompt to an LLM asking the LLM what tools would best be used for the user input. The agent then uses the selected tool - python in our case - and yet again asks an LLM how to use the tool. The LLM then will provide the agent with instructions - python code - on how to use the tool. This is all highly fascinating and requires almost no code. All the business logic is actually provided by the LLM.

    ```python
    agent = create_csv_agent(OpenAI(temperature=0), 'churn-dataset.csv', verbose=True)
    ```

    > Note: Setting verbose to ``True`` outputs the "thought chain" of the agent. So we can read what the agent was deciding.

6. That was actually all the heavy lifting required. The agent knows which dataset it should use and it knows which LLM to use (the ``OpenAI`` llm which by default is the ``text-davinci-003`` model which is the most capable language model provided by OpenAI as of this writing). We can start asking questions. Let's start with an easy one:

    ```python
    agent.run("How many rows and columns are there in the dataframe?")
    ```

    **Result/Output**:

    ```bash
    > Entering new AgentExecutor chain...
    Thought: I need to find the shape of the dataframe
    Action: python_repl_ast
    Action Input: df.shape
    Observation: (7043, 21)
    Thought: I now know the final answer
    Final Answer: The dataframe has 7043 rows and 21 columns.

    > Finished chain.
    'The dataframe has 7043 rows and 21 columns.'
    ```

Isn't that something? By simply calling ``agent.run`` with our question we instructed our agent to ask the LLM for how to find the rows and columns of our dataframe.

What's amazing here is, that it's not the LLM itself reading the file - this would exceed the context limit of the LLM as we have way more than 8k tokens. But the LLM just provided the python code which is then run on our machine which determines the correct shape of the dataframe.

The output of the agent also shows the steps it needed to execute. First, it asked the LLM to interpret our input - the LLM told the agent, it needs to find the shape and use the python - tool. Then the agent sent the result to the LLM and the LLM concluded, that that's the answer. So the final answer was ``The dataframe has 7043 rows and 21 columns``.

Let's try something more advanced.

```python
agent.run("How many users in the dataset did churn? Provide the answer as percentage.")
```

**Result/Output:**
```bash
> Entering new AgentExecutor chain...
Thought: I need to count the number of users who churned and divide it by the total number of users.
Action: python_repl_ast
Action Input: df['Churn'].value_counts(normalize=True)
Observation: Churn
No     0.73463
Yes    0.26537
Name: proportion, dtype: float64
Thought: I now know the final answer
Final Answer: 26.54%

> Finished chain.
'26.54%'
```

And again, a perfect answer - factually correct and beautifully explained.

Ok - now it would be great to know, how many of our users had an Internet Service and which of these churned.
Why not create a bar chart from that?

```python
agent.run("Please use matplotlib to create a bar chart showing how many of the customers had Internet Service and which portion of these churned. The bar chart should be stacked bar chart, where the top portion of the bar shows the churned users, the bottom portion shows the non-churned ones.")
```

As you can see above, I was quite specific about what I want to have. And guess what - the result was perfect.

![Users with or without Internet Service](/blog/analyze-csv-with-llms/churned_internet.png)

With just 3 lines of text we were able to start an exploratory data analyses which would be quite some lines of python code. From here you could simply continue with further queries.

## Summary

This small guide demonstrated the tremendous capabilities of LangChain in helping LLMs to connect to real-world tools like python. With the following few lines of code we were able to analyze data in a csv file and even generate plots - by simply asking our agent for it.

It is exciting to see how far the AI LLM ecosystem has come and how easily they can be extended by providing them the power of tools.

While I must admit that knowing python helps to "guide" the agents to the desired outcome it's of incredible help to not needing to know every in and out of the python programming language and still being able to create basic analytics. And let's be honest: With the rate of development in this field it's to be expected that this whole process will only get more and more powerful.

For reference, this is all the code we needed to create our analyses:

```python
import os
import pandas as pd

from langchain.agents import create_csv_agent
from langchain.llms import OpenAI

# set your openai key here
os.environ["OPENAI_API_KEY"] = "sk-Wcxxxxxxx"

agent = create_csv_agent(OpenAI(temperature=0), 'churn-dataset.csv', verbose=True)

# Start our "interrogation"
agent.run("How many rows and columns are there in the dataframe?")

agent.run("How many users in the dataset did churn? Provide the answer as percentage.")

agent.run("Please use matplotlib to create a bar chart showing how many of the customers had Internet Service and which portion of these churned. The bar chart should be stacked bar chart, where the top portion of the bar shows the churned users, the bottom portion shows the non-churned ones")
```

### What's next?

If you don't want to export your data as csv to analyze them, but want to "chat" with your BigQuery data, have a look [at this next post](./post-chat-with-bigquery) in this LangChain blogging series.

If you are more interested in how to connect your very own pdf files to GPT and ask questions about them, have a look [at my LangChain PDF tutorial](./post-multiple-pdfs-with-gpt)

And if you want to know, how to rent a beefy GPU for your own LLM experiments, [check out this guide](./post-rent-gpu-without-blowing-your-wallet)

How to analyze csv data using GPT-4


Vector databases are currently all the hype. With the rise of large language AI models (LLMs), efficiently managing and searching large-scale, high-dimensional data has become a tremendously important use case. The solution to this challenge lies in vector databases – a powerful and increasingly popular data storage technology that enables faster and more accurate searches. In this blog post, we will demystify vector databases, discuss the reasons behind their growing popularity, and guide you through using pgvector – a versatile vector extension for PostgreSQL – to enhance your data management and search capabilities.

## Table of contents

- [What are vector databases?](#what-are-vector-databases)
- [Use-Cases for vector databases](#use-cases-for-vector-databases)
- [Vector databases and Large Language Models](#vector-databases-and-large-language-models)
- [pgvector - a PostgreSQL vector extension](#pgvector---a-postgresql-vector-extension)
  - [How to use pgvector](#how-to-use-pgvector)
  - [pgvector query operators](#pgvector-query-operators)
  - [pgvector indexing](#pgvector-indexing)
- [Bonus: pgvector on Timescale](#bonus-pgvector-on-timescale)
- [Summary](#summary)

## What are vector databases?

Vector databases have emerged as highly valuable in the world of data storage and search, proving to be especially beneficial for high-dimensional data. At their core, these databases store, manage, and index data in the form of vectors which are mathematical representations of abstract entities such as images, audio files, text, and other complex data types. For example, each word in a text can be represented as a single number - making it easy to store and even more so it's easy to work with by algorithms.

By exploiting the inherent properties of vectors, these databases facilitate efficient and precise searching and analysis of massive datasets. The main advantage of a vector database is it's capability to find similar entries. As an easy-to-understand example, if you have two sentences which both have similar meanings, they both will result in vectors with a short distance to each other. The vector database then helps you to find all the vectors which are close to each other.

A very easy example is shown below. While the two sentences at the top are quite similar in terms of their meaning, the one on the bottom is very different. Vector databases are able to encode such sentences into vectors and than find the ones which are close togehter - meaning they are similar.

![vector similarity search](/blog/pictures/vectorDatabases/similarity.png "vector similarity search")

Keep in mind that in real-world application we have way more than 2 dimensions - OpenAI embeddings currently use around 1500 dimensions for meaningful vectorization of language.

Now let's break down some of the key features of vector databases:

**Vector representation**: Converting complex data types into vectors allows higher-dimensional data to be represented as points in a multi-dimensional space. This representation is not only computationally efficient but also simplifies the process of comparing and relating data points.

**Similarity-based search**: Vector databases excel in searching for items that are similar to a given query, based on their vector representations. They use similarity metrics like Euclidean distance, cosine similarity, or Manhattan distance to determine the closeness between data points in the multi-dimensional space, which translates to finding the most relevant and similar results.

**Scalability**: Designed to handle large volumes of data, vector databases maintain high search accuracy and response times even as the dataset size grows. Additionally, they typically offer mechanisms for parallel processing and distributed computing, ensuring that the demands of ever-growing data can be met effectively.

**Machine learning and AI compatibility**: The adoption of vector databases has surged in recent years owing to the massive growth of artificial intelligence (AI) and machine learning (ML) applications. Converting complex data into vector representations enables seamless integration with these algorithms, leading to valuable insights and predictions at an unprecedented scale.

## Use-Cases for vector databases

The versatility and efficiency of vector databases make them valuable for a wide range of applications across various industries. Some of the prominent ones are:

**Natural language processing (NLP)**: Vector databases facilitate semantic search and text analysis through efficient handling of word embeddings or document vectors. They enable tasks like document classification, sentiment analysis, and keyword extraction, helping organizations make sense of massive volumes of textual data from sources like social media, forums, customer interactions - and currently most importantly - LLM prompt inputs.

**Image search and recognition**: Image databases benefit immensely from the vector representation and similarity-based search capabilities of vector databases. They can effectively handle tasks such as reverse image search, object detection, and facial recognition, due to the inherent ability to identify similarities and patterns across high-dimensional data.

**Recommendation systems**: One of the critical components of modern e-commerce and content platforms is a robust recommendation engine. By using vector databases, these systems can efficiently analyze user preferences and content features to generate personalized and highly relevant recommendations to users in real-time.

**Anomaly detection and fraud prevention**: Sector-specific data points, such as financial transactions or user behavior, can be converted to vector representations for real-time analysis. The similarity-based search capabilities of vector databases enable rapid identification of unusual patterns or potential fraud, aiding organizations in mitigating risks.

**Genomic research**: In the field of genomics, researchers deal with complex biological data and must often identify similar genetic sequences or structures. Vector databases, with their powerful search capabilities, can accelerate this process with greater precision, paving the way for critical discoveries and advancements.

**Multimedia content management**: Vector databases can handle diverse content, including audio and video, by transforming them into vector representations. This ability democratizes content search and analysis, enabling seamless retrieval of media based on similarity, pattern recognition, or automatic content tagging.

These use-cases are just the tip of the iceberg, as vector databases have far-reaching applications across a plethora of industries. As more organizations recognize the potential of vector databases, their adoption and exploration will continue to push the boundaries of data management and analysis.

## Vector databases and Large Language Models

While we already covered use-cases, the currently most prominent use-case might be usage of vector databases as part of LLM AI chains.
[Pinecone](https://pinecone.io) created a highly valuable blog post about exactly this topic - explaining vector database usage for LLMs. Have a look [at their great article](https://www.pinecone.io/learn/vector-database) for more details into this specific topic.

## pgvector - a PostgreSQL vector extension

[pgvector](https://github.com/pgvector/pgvector) is an open-source vector database extension for PostgreSQL. It supports:

- exact and approximate nearest neighbor search
- L2 distance, inner product, and cosine distance
- any language with a Postgres client

Some of the highlights are:

**Straightforward integration**: pgvector is easy to set up within an existing PostgreSQL environment, as it can be added as an extension to an existing database. This feature enables both new and long-time users of PostgreSQL to reap the benefits of vector databases without the need for major system overhauls.

**Support for various distance metrics**: pgvector comes with built-in support for multiple distance metrics, including Euclidean, cosine, and Manhattan distances. This versatility allows for a highly customizable similarity-based search and analysis tailored to specific application requirements.

**Indexing support**: The pgvector extension offers efficient indexing options for vector data, such as k-nearest neighbors (k-NN) search. As a result, users can achieve rapid query execution while maintaining high search accuracy, even with considerable growth in dataset size.

**Accessible query language**: As a PostgreSQL extension, pgvector leverages familiar SQL query syntax for vector operations. This approach simplifies the process of adopting vector databases for users with SQL knowledge and experience, and prevents them from having to learn a new language or system.

**Active development and support**: pgvector is frequently updated to ensure compatibility with the latest PostgreSQL versions and features, while the developer community continually works on enhancing its functionality. Consequently, users can expect a well-supported solution for their vector data needs.

**Robustness and security**: By integrating with PostgreSQL, one of the most reliable and secure RDBMSs available, pgvector inherits the same level of robustness and security features, enabling users to store and manage their vector data with confidence.

### How to use pgvector

1. Install pgvector on your database server

    ```bash
    cd /tmp
    git clone --branch v0.4.2 https://github.com/pgvector/pgvector.git
    cd pgvector
    make
    make install # may need sudo
    ```

2. In your database, run this command to enable the extension

    ```sql
    CREATE EXTENSION IF NOT EXISTS vector;
    ````

3. Create a table storing the vectors

    ```sql
    CREATE TABLE items (id bigserial PRIMARY KEY, name, features vector(3));
    ````

4. Adding data works as follows:

    ```sql
    INSERT INTO items (features) VALUES ('[1,2,3]'), ('[4,5,6]');
    ```

5. As pgvector builds on top of postgres, a lot of the PG DML is available. Eg. to upsert, you might run:

    ```sql
    INSERT INTO items (id, features) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
    ON CONFLICT (id) DO UPDATE SET features = EXCLUDED.features;
    ```

### pgvector query operators

In pgvector, various query operators are available to perform different operations on vector data. These operators typically focus on calculating the similarity or distance between vectors, with some operators using different distance metrics. Here, we will discuss the commonly used operators in pgvector:

1. `<->`: This operator calculates the Euclidean distance between two vectors. The Euclidean distance is the straight-line distance between the points represented by the vectors in the multi-dimensional space. Smaller Euclidean distances indicate greater similarity between vectors, making this operator useful in finding and ranking similar items.

```sql
SELECT id, name, features, features <-> '[0.45, 0.4, 0.85]' as distance
FROM items
ORDER BY features <-> '[0.45, 0.4, 0.85]';
```

2. `<=>`: The `<=>` operator computes the cosine similarity between two vectors. Cosine similarity compares the orientation of two vectors rather than their magnitude. It ranges from -1 to 1, with 1 indicating that the vectors are identical, 0 implying no correlation, and -1 meaning that the vectors point in opposite directions.

```sql
SELECT id, name, features, features <=> '[0.45, 0.4, 0.85]' as similarity
FROM items
ORDER BY features <=> '[0.45, 0.4, 0.85]' DESC;
```

3. `<#>`: The (negative) inner product between two vectors can be computed using the `<#>` operator. The inner product denotes the magnitude of the an orthogonal projection of one vector onto another one. The sign determines whethere the vectors point in the same or opposite directions. Orthogonal vectors have an inner product of 0.
Please take notice that pgvector - due to Postgres technical details - returns the negative inner product!

```sql
SELECT id, name, features, features <#> '[0.45, 0.4, 0.85]' as distance
FROM items
ORDER BY features <#> '[0.45, 0.4, 0.85]';
```

You can select an appropriate operator based on your application requirements or the nature of your data. Some factors to consider while choosing an operator include preserving relative distances, emphasizing magnitude or orientation, and prioritizing specific dimensions over others. Note that, depending on your data and use case, the choice of operator may have a significant impact on the quality of search results and ultimately the effectiveness of your application.

> **NOTE:** Additionally to query parameters, pgvectors offers functions to calculate l1 & l2 distances, inner product and cosine similarity, as described [on the pgvector github page](https://github.com/pgvector/pgvector#vector-functions).

### pgvector indexing

By default, pgvector performs exact nearest neighbor search, which provides perfect mathematical matches.
While this is the most accurate way, it might be quite resource intense and lead to slow query response time. pgvector allows to trade some of the accuracy with performance by adding an index to use approximate nearest neighbor search.

To still have a good accuracy but with greatly enhanced performance:

1. Create the index after the table has some data
1. Choose an appropriate number of lists - a good place to start is rows / 1000 for up to 1M rows and sqrt(rows) for over 1M rows
1. When querying, specify an appropriate number of probes (higher is better for recall, lower is better for speed) - a good place to start is lists / 10 for up to 1M rows and sqrt(lists) for over 1M rows.

    ```sql
    BEGIN;
    SET LOCAL ivfflat.probes = 10;
    SELECT ...
    COMMIT;
    ```

1. Add an index for each distance function you want to use.

    - L2 distance

        ```sql
        CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
        ```

    - Inner product

        ```sql
        CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);
        ```

    - Cosine distance

        ```sql
        CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
        ```

## Bonus: pgvector on Timescale

[Timescale](https://timescale.com) just announced that their managed cloud service now also supports pgvector.

[TimescaleDB is an open-source, time-series database](https://www.timescale.com/blog/what-is-a-time-series-database) designed for managing large-scale time-series data with the power and convenience of SQL. Built as an extension of PostgreSQL, it combines the proven reliability and robustness of PostgreSQL with optimizations specifically tailored to handle time-series data.

It is widely used in the Time-Series space and offers a superb cloud database experience. Many users therefore will already have a Timescale instance - now having access to pgvector allows them to create LLM AI applications for their time series use-cases. All without having to set up and manage yet another database "just" for the vectors.

The following guide assumes that you already have a running Timescale service [set up](https://docs.timescale.com/getting-started/latest/).

1. If not already done, install psql as described [in the official Timescale Blog](https://www.timescale.com/blog/how-to-install-psql-on-mac-ubuntu-debian-windows/)
2. Connect to your database by using the connection command provided in the Timescale console

    ```bash
    psql "postgres://tsdbadmin@secret.tsdb.cloud.timescale.com:36055/tsdb?sslmode=require"
    ```

Now you are connected to the Timescale postgres database.
Run the following command to enable the pgvector extension:

```sql
CREATE EXTENSION IF NOT EXISTS vector;
```

That's it - now you have a fully managed pgvector-based vector database at your hands, in the same database where also your time series data reside.

## Summary

In this blog post, we explored the importance of vector databases for managing high-dimensional data and their applications across various industries. We introduced pgvector, a versatile PostgreSQL extension that inherently supports vector data storage and search, presenting an accessible vector database solution. Using a practical guide, we demonstrated the process of creating tables using pgvector, inserting data, and querying for similar items. Furthermore, we discussed different query operators available in pgvector for calculating similarity measures, such as Euclidean distance, cosine similarity, and Manhattan distance.

Finally, we demonstrated how to use pgvector in your fully managed Timescale cloud database.


Uncovering the Power of Vector Databases with pgvector: An Introduction

Snowplow Analytics: 7 Web-Analytics Use-Cases directly related to your business growth


Most of the world nowadays agrees, that having some [sort of data warehouse](./post-what-and-why-datawarehouse) is key for a successful data operation. Each and every company - no matter how big or small - has quite a huge number of tools which all produce data. Combining the data of these tools is essential to make use of them. That's - in short - the sales-pitch for a data warehouse.
Compared to the very expensive and huge "enterprise warehouses" from 20 years ago, there is a new breed of warehouses available. For marginal cost and with incredible scalability. The ["modern data warehouse"](./post-what-and-why-datawarehouse) - a warehouse which is feasible for any company - no matter of budget or size.

Now, let's assume that we succeeded in establishing the warehouse technology and integrating all our data sources into this warehouse. What do we have? A bunch of raw data, all organized in a central place. Which is already good - but now comes the work of making use of them.

And here is where dbt comes into play. This post will cover to basic topics:

- [What is dbt?](#what-is-dbt) and
- [How to set up dbt?](#how-to-set-up-dbt)

## What is dbt?

It's rare these days that one can simply navigate to a website of a tool and get a perfect description of what it does. dbt actually is one of these tools. So, I've the pleasure to simply quote dbt's website:

dbt transforms data in your data warehouse and helps to data engineers to work like software engineers - to ship trusted data, incrementally and fast.

dbt does this by providing tools to:

- use supercharged SQL (more on that later) to transform your raw data in usable, well-defined models
- provide easy-to maintain documentation tools
- add a thing data testing layer to make sure your data are what you expect

This marketing image summarizes this quite well.

![dbt marketing slide](/blog/pictures/dbtPost/analytics-engineering-dbt.png "dbt Marketing Slide")

Summarized, dbt provides us an easy to use, easy to maintain way of modelling our raw data into multiple ready-to-use datasets which can be utilized by more downstream operations like machine learning, BI and data analytics. dbt does this, by providing a powerful SQL interface and tools for documentation and testing.

If you want to see dbt in action, skip to section [How to model data with dbt?](#how-to-model-data-with-dbt).

## How to set up dbt?

In this section we are installing dbt and create a nice and tidy vscode development setup.

dbt core - the open source and well... core offering of dbt is actually just a python package one needs to install.

Besides the dbt main application, you can install one or several database adapters.

Run these commands to install dbt.

```bash
# Optional but highly recommended, create and activate a virtual environment
# Instead of venv, you might also use anaconda for environment management
python3 -m vevn dbt-venv
source dbt-venv/bin/activate

# Install the core application
pip install dbt-core

# Install one or several of the following adapters

pip install dbt-bigquery # If you want to model data in your bigquery data warehouse
pip install dbt-athena-adapter # If you want to model data in your athena data warehouse
pip install dbt-postgres # If you want to model data in your postgres data warehouse
pip install dbt-synapse # If you want to model data in your synapse data warehouse
pip install dbt-databricks # If you want to model data in your databricks platform
pip install dbt-redshift # If you want to model data in your redshift data warehouse
pip install dbt-snowflake # If you want to model data in your snowflake data warehouse
pip install dbt-spark # If you want to model data in your spark platform
```

These are all the dependencies one needs, to start developing with dbt!

For developing our data models we are going to use VSCode - as it provides awesome extensions which make working with dbt fun and exciting.

1. If not already done, [install vscode](https://code.visualstudio.com/download)
2. Set up the folder structure, as follows
    1. Add a .gitignore file with the following content

        ```gitignore
        target/
        dbt_modules/
        dbt_packages/
        logs/
        venv/
        env/
        test.env
        __pycache__
        .vscode
        export.json
        ```

    2. Run ``dbt init`` and follow the screen. The init application might ask different questions, depending on which database adapter you are going to use. In this example, I'm using BigQuery as an example (make sure you have installed the appropriate adapter - see the ``pip install`` steps above)

        ```bash
        dbt init

        # This is the result
        17:06:52  Running with dbt=1.4.5
        Enter a name for your project (letters, digits, underscore): waffle_shop
        Which database would you like to use?
        [1] bigquery

        (Dont see the one you want? https://docs.getdbt.com/docs/available-adapters)

        Enter a number: 1
        [1] oauth
        [2] service_account
        Desired authentication method option (enter a number): 2
        keyfile (/path/to/bigquery/keyfile.json): /tmp/google_key.json
        project (GCP project id): devopsandmore
        dataset (the name of your dbt dataset): dbt_devops
        threads (1 or more): 8
        job_execution_timeout_seconds [300]: 300
        [1] US
        [2] EU
        Desired location option (enter a number): 2
        17:07:25  Profile waffle_shop written to /home/andreas/.dbt/profiles.yml using targets profile_template.yml and your supplied values. Run 'dbt debug' to validate the connection.
        17:07:25
        Your new dbt project "waffle_shop" was created!

        For more information on how to configure the profiles.yml file,
        please consult the dbt documentation here:

        https://docs.getdbt.com/docs/configure-your-profile

        One more thing:

        Need help? Don't hesitate to reach out to us via GitHub issues or on Slack:

        https://community.getdbt.com/

        Happy modeling!
        ```

        Finishing this installer will create the following folder structure

        ```bash
        .
        ├── logs
        │   └── dbt.log
        └── waffle_shop
            ├── README.md
            ├── analyses
            ├── dbt_project.yml
            ├── macros
            ├── models
            │   └── example
            │       ├── my_first_dbt_model.sql
            │       ├── my_second_dbt_model.sql
            │       └── schema.yml
            ├── seeds
            ├── snapshots
            └── tests
        ```

        - **logs**: This folder contains the logs of the dbt init operation. You can delete it.
        - **waffle_shop**: This is the main dbt project folder. All your model sql files, description files and settings are located here. The name of the folder is the name of the dbt project you entered during ``dbt init``.
        - **README.md**: Readme file. Feel free to change.
        - **analyses**: Folder which might contain [dbt analyses](https://docs.getdbt.com/docs/build/analyses). Despite it's name, analyses are not the main thing in dbt - "models" are
        - **dbt_project.yaml**: Project settings file
        - **macros**: Folder to store [dbt macros](https://docs.getdbt.com/docs/build/jinja-macros)
        - **models**: This is the folder you will spend most of your time in. It contains all [dbt models](https://docs.getdbt.com/docs/build/sql-models) - as a reminder, dbt models are "just" sql files, enhanced with jinja template code.
        - **example**: Delete this folder
        - **seeds**: Folder which might contain [dbt seeds](https://docs.getdbt.com/docs/build/seeds)
        - **snapshots**: Folder which might contain [dbt snapshots](https://docs.getdbt.com/docs/build/snapshots)
        - **tests**: Folder containing [custom dbt tests](https://docs.getdbt.com/docs/build/tests)

    3. Install [vscode-dbt](https://marketplace.visualstudio.com/items?itemName=bastienboutonnet.vscode-dbt). This adds support for the dbt syntax (dbt-sql, jinja, yaml, markdown)

    4. Install sqlfluff

        ```bash
        pip install sqlfluff
        ```

    5. Install the [sqlfluff vscode extension](https://marketplace.visualstudio.com/items?itemName=dorzey.vscode-sqlfluff)

    6. Add a file ``.sqlfluff`` to the toplevel folder, with the following content:

        ```nginx
        [sqlfluff]
        # Choose the sql dialect to use
        dialect = bigquery
        # Use jinja templater instead of dbt, as the dbt templater is slow
        # and does not work with bigquery dialect
        templater = jinja
        # Optional: Exclude some rules
        # Find all rules here: https://docs.sqlfluff.com/en/stable/rules.html
        exclude_rules = L034, L032, L024, L015, L008, L001
        ignore_templated_areas = True
        large_file_skip_byte_limit = 200000

        [sqlfluff:templater:jinja]
        # Make dbt default macros available like `ref`
        apply_dbt_builtins = true
        # Load custom macros from the macro directory
        load_macros_from_path = ./macros/
        # Allow mocking dbt_utils and other packages through
        # python files placed in this dir
        library_path = ./sqlfluff_libs/

        # Mock variables that are used in dbt
        [sqlfluff:templater:jinja:context]
        target = prod

        [sqlfluff:rules]
        tab_space_size = 4
        max_line_length = 100
        ```

    7. Add a file ``.sqlfluffignore``, with following lines:

        ```conf
        target/
        dbt_modules/
        dbt_packages/
        macros/
        tests/
        ```

    8. Add a folder ``.vscode`` at the same folder level as ``logs`` and ``waffle_shop``.

    9. Add a file ``settings.json`` inside the ``.vscode`` folder, having the following content:

        ```json
        {
            // change this to where your python dbt virtual environment python binary is located
            "python.python.defaultInterpreterPath": "./.dbtenv/bin/python",
            
            "files.associations": {
                // associate all sql files in the folder with jinja sql
                "*.sql": "jinja-sql",
            
                // optional: don't format models in `target/` dir.
                // This disables syntax highlighting in the target folder
                // This helps to very quickly identify, that you are not in a model file, 
                // but a compiled target file.
                "**/target/**": ""
            },
            "editor.quickSuggestions": {
                "strings": true
            },
            
            // run "whereis sqlfluff" to find the executable path of your sqlfluff installation
            "sqlfluff.executablePath": "/path/to/dbt-venv/bin/sqlfluff",
            "sqlfluff.dialect": "bigquery",
            "sqlfluff.linter.run": "onSave",
            "sqlfluff.experimental.format.executeInTerminal": true,
            "editor.formatOnSave": false,
            "dbt.queryLimit": 1000,
            "editor.rulers": [100]
        }
        ```

    10. Install the [dbt power user vscode extension](https://marketplace.visualstudio.com/items?itemName=innoverio.vscode-dbt-power-user)

> If you follow the steps above you'll get a basic dbt setup and vscode as sort of dbt IDE. The developer experience is quite nice and you have a development system at hand to start working with dbt.

In the next post, you'll see how to create your first dbt data model, based on some raw-data in your data warehouse.


What is dbt - and how to set up your dbt developer environment?

Highly available Snowplow pipeline on kubernetes and GCP


- [Why not using Google Analytics?](#why-not-using-google-analytics)
- [What is Snowplow?](#what-is-snowplow)
- [What's the architecture of Snowplow](#whats-the-architecture-of-snowplow)
  - [The snowplow tracker](#the-snowplow-tracker)
- [The Snowplow pipeline](#the-snowplow-pipeline)
  - [Iglu - the snowplow schema registry](#iglu---the-snowplow-schema-registry)
  - [Failed and invalid messages](#failed-and-invalid-messages)
  - [Horizontal scaling and architecture summary](#horizontal-scaling-and-architecture-summary)
- [Data modelling](#data-modelling)
- [Feature summary](#feature-summary)

Knowing how our websites perform is critical for the success of virtually any business in this internet-focussed time. We design and create websites and web apps to satisfy our users, engage them and - at the end - make money. For these reasons it's from elementary importance to know, how our users are behaving on our sites, where they experience struggles and bottlenecks and where and why they churn. To answer all of these - and many more questions, there are numerous web-trackers on the market, from Google Analytics, to Adobe Analytics - and to Snowplow.

Snowplow is the third-most used web tracking solution on the planet - directly after Google Analytics and the Facebook Web-Tracker.

## Why not using Google Analytics?

As Google Analytics is the most used tracker - and it's free after all - why not using it?

Unfortunately, Google did a lot of bad things when it comes to Google Analytics:

Google Universal Analytics (UA) - the predecessor of Google Analytics 4 (GA4) - was many times a hit for Google and equally for many website owners. Universal Analytics was easy to integrate, easy to use, and delivered decent results out of the box for data novices. And beginners were exactly the target-group of UA. The product was not intended for experts - and experts did not enjoy it. They didn't have to. Beginners and hobbyists were rightly happy about a free, huge, easy to use and feature-rich web-tracking solution. While UA was a huge success - and also a product which was really recommended to use when looking on what it promised - there was a huge problem: UA was and never will be GDPR compliant. It stores data in America and it's quite easy to identify users based on browser fingerprint, etc.

Several courts determined, that UA is actually illegal to use in Europe. So Google came around with Google Analytics 4, promising to be more GDPR compliant and also taking the chance to introduce a set of new features and concepts. At the same time, the beloved (but remember - still illegal) Universal Analytics was discontinued (and will be shut off in 2023).

After this rather long introduction - what's wrong with Google Analytics 4 then. If it's a GDPR compliant Universal Analytics replacement - which was beloved - why not using GA4?

Well - because Google changed almost any aspect of the software. Getting started is much more complex, the session-based tracking was replaced with event-based tracking (which is good for experts, but bad for beginners) and overall, beginners were obviously not the target group anymore. So does Google Analytics 4 work for experts? Unfortunately, it doesn't:

- Too much "magic" is still happening in the data model. Some numbers are just not comprehensible
- There is too little control over the captured events - when are they stored where, and how are they processed?
- Google Analytics 4 still tracks less events than eg. Snowplow. Reasons for that are unknown - but when directly comparing snowplow tracking and GA4 tracking - with Snowplow there are more users logged. A deep investigation of a team I led found that indeed the Snowplow-Tracking was much closer to the real user number than GA4 - by almost 10%

So - all of the above issues would not matter if GA4 was still a product for beginners - but it isn't anymore. And for experts - these issues are actually a dealbreaker. So no matter whether you are a beginner or expert - you will encounter huge flaws.

(Google tried to server both groups here - and let's be honest - when was there ever a product which served beginners and experts alike?)

While above issues are already big enough - there is one additional elephant in the room: Google Analytics 4 is still not GDPR compliant. It allows for more options to be closer to compliance than UA - however it still sends plain-text IP-Addresses to America and it is still unknown which user identifiable information is stored in which locations. Again - we have a problem here. 

For me personally, it's not only the product issue or the fact that basically running GA4 is illegal - it's mainly the fact that I as a website owner want to respect my users privacy (by at the same time knowing what my site does good or bad). Using GA4 currently simply tells the user, that one does not respect them. As easy as that.

## What is Snowplow?

[Snowplow](https://snowplow.io) markets itself as "Behavioral Data Platform" - a platform for collecting and modelling user behavior. They see themselves as puzzle in a broader data strategy.

![Snowplow Marketing Slide](/blog/pictures/whatIsSnowplowBlogPost/snowplow.svg "Snowplow Marketing Slide")

While I like their approach and their overall marketing strategy is sound - in real-world terms, Snowplow is "simply" a web, mobile and app tracking solution - very good one, should be added. It generates data on user interaction (eg. when a user visits a page, clicks a button, etc.) and sends these data to a backend pipeline - hosted by you. This pipeline provides means for enriching your data - eg. to anonymize the data or adding more details from other data sources. And finally, the data is inserted in a data store of your choosing. Currently supported data targets are:

- Google BigQuery
- Snowflake
- Postgres
- AWS Redshift
- Databricks

**To summarize**: Snowplow collects data and provides tools to manage and enrich these data and store them in a database or data warehouse.

## What's the architecture of Snowplow

Snowplow consists of two main components:

- The snowplow tracker
- The snowplow pipeline

### The snowplow tracker

To create the behavioral data, snowplow utilizes a whole armada of tracking solutions, for all sorts of programming languages and use cases. Among others, there are trackers for:

- Javascript
- Native mobile
- .NET
- Java
- Go
- Python
- C++ 

and even Unity (the game engine)

For a full list, see [the official docu](https://docs.snowplow.io/docs/collecting-data/collecting-from-own-applications/).

Adding trackers is as easy as it gets, simply follow the instructions in the above linked documentation and you should be able to collect data within some minutes.

## The Snowplow pipeline

The pipeline itself is actually the "heart" of snowplow. The pipeline is the place where most of the advantages of snowplow over any other tracking solution emerges.

The architecture of the snowplow pipeline is not independent of the cloud you are running. While it is possible to run snowplow on premise - it is a much more complex. For the sake of this blog, let's assume we run on the Google Cloud Platform (GCP). The situation is very similar for AWS.

When running the pipeline on GCP, it is comprised of the following components:

- **Collector server**: The entry point of the pipeline. It provides an HTTP server, where the trackers send their data to.
- **Enrich server**: All collected events are redirected to the enrich server. This component checks the validity of the data (more on that later), anonymizes them, adds additional enrichment (eg. it can look up information about the browser which sent the data and add these information to the data stream) and redirects the data to the database loader
- **Database loader**: The database loader component is responsible for inserting the collected events into the selected database. Depending on the database, this component differs a bit. For example, running on Google BigQuery, this component consists of three sub-components:
  - **Streamloader**: The streamloader inserts the events in the BigQuery data warehouse
  - **Mutator**: The mutator is an ingenious small tool, simply checking the incoming datastream for unknown data points. If unknown data arrives, it automatically checks for validity. If the data are valid, the mutator adds the corresponding column to BigQuery. Therefore, if you add additional tracking data you are not obliged to manually add them to the BigQuery schema.
  - **Repeater**: The repeater simply retries failed inserts - mainly because the mutator might take some time to create new database columns - therefore the repeater waits a some time (15 minutes by default) and then attempts to re-insert the events
- **Pub/Sub**: The "glue" between all the above mentioned components is the Google Pub/Sub service. Pub/Sub is a managed message queue. The collector server sends it's data to a pub/sub topic, the enrich server then subscribes to this topic. The enrich server sends his enriched data to a different topic, which again is subscribed to by the database loader.
    Using pub/sub here provides some huge advantages. Mainly, it fully decouples all the components. Let's say the enrich server is overloaded - if the collector server would directly send the data to the enrich server we would most probably lose data. However, as the collector server simply publishes it's messages to pub/sub, the messages will be queued until the enrich server is able to handle them again. 

    Furthermore, we can also easily do pipeline maintenance tasks - even shutting down the whole pipeline - as long as at least one of our collector servers is running we will not lose a single event - as pub/sub will happily buffer our data.

### Iglu - the snowplow schema registry

Snowplow does not simply accept all messages - you need to create a schema first. This ensures highest data quality - and after some getting used to, this really is a huge daily-life-improvement. In contrast to many other tracking tools, with Snowplow you have by definition a much higher data quality due to this restrictive event schemas.

Snowplow provides a tool called "Iglu" which acts as schema registry. It's a simply http server which reads the created schema either from static html file or from an Postgres server.

The enrich server then requests the schemas from the iglu server and then validates all incoming messages against this schema.

On a GCP snowplow installation, the Iglu-Server again is a simple compute instance (virtual machine) and a Postgres database running somewhere in reach of the server.

### Failed and invalid messages

In addition to the already described pub/sub-topics, there are two more of them. They are used by all components: Failed inserts and bad rows. So, if the pipeline determines, that your messages are invalid, because they are ill-formed or do not conform to a schema, they will not be discarded - they will be sent to the bad-rows pub/sub topic. You can then use this topic to insert the data automatically into a GCS cloud blob storage - for further analyses - and even more important to maybe adjust your schema, transform the failed data and attempt to re-insert them. So you again decrease the likelihood of message loss.

### Horizontal scaling and architecture summary

One thing to mention is the perfect execution of the Snowplow development team when it comes to horizontal scaling. All the above components can be run multiple times and linearly scale with the number of instances running. If your enrich server is too slow, simply add a second one with the same configuration - and you will have twice the throughput. The same goes for any of the other components.

The pipeline for GCP in summary looks as follows:

![Snowplow architecture on GCP](/blog/pictures/whatIsSnowplowBlogPost/Snowplow_Pipeline_architecture.png)

As you might see there are quite a few components involved when running Snowplow. However, this very loosely coupled infrastructure setup makes maintaining the pipeline rather easy.

(For more details on how the pipeline looks an AWS, have a look at their [AWS quickstart](https://docs.snowplow.io/docs/getting-started-on-snowplow-open-source/quick-start-aws/summary-of-what-you-have-deployed/))

## Data modelling

The collected data with Snowplow are raw event data - meaning per event you get one entry in your data warehouse, containing all the collected parameters.

To make use of these data, it is suggested to model these raw data into the data model you need. As Snowplow is most prominently used for web-tracking, they provide out of the box data models for web and mobile tracking, to be found on [their excellent docu page](https://docs.snowplow.io/docs/modeling-your-data/modeling-your-data-with-dbt/). The available models for web-tracking for example create models for:

- pageviews
- users
- and sessions

from the raw data - covering huge amounts of use-cases out of the box.

## Feature summary

Now that we know what Snowplow is and what exactly this means infrastructure-wise, what are some of the most prominent features of Snowplow?

- The snowplow tracking can be done in accordance to GDPR and with user privacy in mind
- A lot of trackers for various programming languages available
- Very low latency - below 1 second from event-tracking to streamloader
- Event schemas lead to high data quality
- Decoupled architecture allows for easy scalability and pipeline operation
- Very good enrichment engine. Allows to add all sorts of context to your data
- Huge and very good data models - this helps with analyzing the data
- Full control over any step in the data pipeline

All in all, Snowplow is currently my favorite user behavior tracking solution. First of all, it is one of the only tools I can guarantee that it's GDPR compliant. The "no-magic" approach to pipelining and data modelling helps to understand your user in much more details compared to similar solutions. And having high-quality raw data sometimes is the only dream a data expert has - Snowplow delivers perfectly with this aspect.


What is Snowplow - the behavioral data platform?

What is a modern data warehouse and why do you need one?

I often encounter situations, where contacts need to share a password or a secret - and then simply send it via potentially unencrpyted E-Mail. Or use WhatsApp, Microsoft Teams or Slack or similar apps.

Let's first start with the obvious: In general one should avoid to share passwords and secrets with other people. Most of the times it's the best and only reasonable solution to have dedicated accounts, tokens and secrets per user!

That being said - security needs to be practical, and while I'm all about highest security standards, I can see some use-cases, where sharing passwords is fine:

- First-time login password - where you request your user to change it afterwards
- API secrets - when a tool only allows an admin to create the secret, but a developer needs to work with it
- User accounts which are only used for API purposes (while I think, tools which don't allow personalized API keys are bad - there are thousands out there. And we somehow need to work with them...)
- Emergency access to an account or a system in case of disaster
- And I guess there are many more reasons why sometimes it's ok to share passwords

## The No-Go - Sharing via messenger app

However, where I really draw the line is the method of sharing the password. As you can guess, I'm quite apologetic when it comes to sharing passwords, when it is necessary - but I'm very much a hardliner when it comes to HOW the secrets are shared.

The most common thing I guess is E-Mail. An admin creates a user account for a new employee and sends the credentials via E-Mail. The colleague needs emergency access to one of your accounts - and sends the password via E-Mail. Or Microsoft Teams. Or Slack. What's the problem with that? First of all, E-Mail mostly is not encrypted. Anybody could watch your password being sent around. Also Slack and Teams have huge security flaws when it comes who could potentially read your messages.

And almost as important - or even more important: The passwords and secrets stay in the Inbox or Chat forever. Let's be honest - most people do not delete these secrets after they received them. And most people also do not change the secret, after the purpose of the sharing is not valid anymore. And we all have seen chat messages of our colleagues without us or them wanting us to see them, right?
(And again - don't mistake my words for approval. I'd like to live in a world, where nobody shares his secrets. But that's just not realistic - making all our lives a little less secure).

> Conclusion: Please do not and never share your secrets via E-Mail or messenger app!

## The hard - creating encrypted archive with password protection

Then there are the people among us, who see the need for sharing a secret but also see the danger of simply transferring them via Mail. So they create a text-file containing the secret, encrypt the file with password protection, send the encrypted file via E-Mail and most probably the password of the encrypted file via different messenger app. While this is much more secure than the E-Mail approach, it still has flaws:

- The password for decrypting the file is still around forever
- The encrypted file will most probably also be around forever
- The receiving user might extract/decrypt the file and keep the decrypted secret on his computer (which again is quite bad)
- This whole process takes forever and is very inconvenient - and inconvenient security mechanisms are rarely successful

> Conclusion: While this approach is inherently more secure, then sending secrets via Mail, it still lacks the convenience and security we need

## The suggested way - sharing using a password manager

Ok, the clear and obvious best solution is to use a paasword-manager tool to share the secrets. There are quite some good once out there - both managed solutions and open-source self-hosting ones:

- [1Password](https://1password.com/)
- [Bitwarden](https://bitwarden.com/)
- [Keepass](https://keepass.info/)
- [LastPass](https://www.lastpass.com/)
- and many more

(Note that LastPass had some serious security incidents lately, so I'd not recommend them at the moment!)

And there is actually nothing inherently wrong with using a password manager to share your secrets. It is in fact the best and most secure solution. However - and this is not to be underestimated - a lot of people and companies still don't have passsword management solutions set up. And setting ups such a solution can get complex rather quickly if you have a rather huge IT landscape. 

This solution also breaks, if sharing is to be done beyond your company limits. And this is where actually I - as a freelance consultant - are at the moment. I have a password manager - but my clients often don't. My solution is able to securely share secrets with clients - but not the other way around. So I end up getting passwords in plain text - even though I have a password manager.

> Conclusion: If you need to share secrets - best is to use a password manager. However, there are some hurdles which prevent companies and inter-company-relationships from utilizing them. Still - use them. Excuses for not using password managers are actually very rare!!

## The attempt of a compromise - secret.datascienceengineer.com

While it is clear that using a password manager for sharing secrets is the way to go there are still too many situations where I - as an avid user and preacher of security solutions - get handed over secrets and passwords via Mail, Chat or SMS. I really don't like this situation - that's why I created a small tool which I provide for free to anybody interested: [secret.datascienceengineer.com](https://secret.datascienceengineer.com).

The tool is very simple:

- Upload your secret
- The app will create a sharing link which you can distribute to your colleagues
- Once the secret gets accessed by using the link, it gets deleted. As easy as that.

While this solution is worse than using password managers - as mentioned, please use a password manager whenever possible - it is better than any alternative:

- It's easy to use - so very little friction to use. Which is good for security
- It deletes your secrets after they are accessed once
- This brings two additional benefits:
  - First, the secret is not lingering around your desktop for years
  - And second, if for some reasons your colleague gets the link but can't access the secret, you know, that someone already read this secret. Making a good case for changing it asap. With any other method, it's hard to find compromise that soon in the process
- It's free to use - which should also limit friction

### Behind the scenes

The app creates a new encryption key for each secret upload. The secret then is encrypted, using this key. The key and the encrypted secret are then stored in two different databases in two different physical locations.

When a user wants to access the secret again by using the share-link, the key as well as the encrypted secret are retrieved from their databases. The secret gets deleted, then decrypted and finally is presented to the user. So if the secret wants to be accessed a second time, it's gone...

### Quick intro

1. Navigate to [secret.datascienceengineer.com](https://secret.datascienceengineer.com)
2. Enter your secret in the input field and press ``Generate one-time link``

    ![Upload secrets](/blog/pictures/shareSecretOncePost/Upload.png "Upload secrets")

3. A popup appears, providing you the sharing link. Click ``Copy Link to Clipboard`` to copy this link

    ![Share Link](/blog/pictures/shareSecretOncePost/ShareLink.png "Share Link")

4. Distribute this link to your colleague
5. Your colleague may now enter the sharing link into their browser. They can click on ``Copy this secret``

    ![Get Secret](/blog/pictures/shareSecretOncePost/GetLink.png "Get Secret")

    The secret is already deleted, when the user is presented with it's value.

Feel free to use this tool for free - it will be free forever. If you have thoughts about whether this tool helps with security or whether it's rather worsening security aspects - let me know!


Securely share passwords - only once

Harden PostgreSQL on Kubernetes and CloudNativePG


In this blog post we are going to discover how we can use Airbyte to synchronize Senseforce datasets to our own PostgreSQL database.

For a general introduction to what Senseforce and Airbyte are, please have a look [at my previous post](https://www.datascienceengineer.com/blog/post-sync-senseforce-airbyte).

- [Prerequisites](#prerequisites)
- [Prepare a Dataset in Senseforce](#prepare-a-dataset-in-senseforce)
- [Configuring the Airbyte Connector](#configuring-the-airbyte-connector)
- [Starting the Sync](#starting-the-sync)
- [Advantages of Airbyte for Senseforce data syncs](#advantages-of-airbyte-for-senseforce-data-syncs)
- [Summary](#summary)

## Prerequisites

To follow this guide, you need:

- git and docker installed
- a Senseforce user account which can create datasets

## Prepare a Dataset in Senseforce

For a general and extensive introduction on how to use the Senseforce connector for Airbyte, see [the official airbyte docs](https://docs.airbyte.com/integrations/sources/senseforce/). Nevertheless, let's start from scratch here.

To use Airbyte to sync some data from Senseforce, we first need to define what data we want to extract.

1. Create a new Dataset
    ![Create new Dataset](/blog/pictures/airbyteSenseforceBlogPost/SenseforceNewDs.png "Create new Dataset")
2. Add the columns you want to sync by clicking on the data attributes in the "Add Data" section
    ![Senseforce Add Data Dialog](/blog/pictures/airbyteSenseforceBlogPost/SenseforceAddData.png "Senseforce Add Data Dialog")

    **Important: You definitely need to add the "Timestamp", "Thing and "Id" column of the "Metadata" section. This is needed so that Airbyte can provide the Stream Slicing and Incremental Sync features.**

    In our example we are interested in the "Uncompressed size" and the "Inserted Events" metrics - see the above Screenshot for reference. But feel free to add any columns you like - as long as you keep Thing, Timestamp and Id in the dataset.
3. Give the Dataset a nice name and save.
    ![Senseforce save the dataset](/blog/pictures/airbyteSenseforceBlogPost/SenseforceSaveDataset.png "Senseforce save the dataset")
4. Navigate to your user profile and create an API token
    ![Senseforce Create an API Token](/blog/pictures/airbyteSenseforceBlogPost/SenseforceApiToken.png "Senseforce Create an API Token")
    Make sure to not down this token as you will need it later in the Airbyte configuration.

> **NOTE:** That's it. Your Senseforce installation is ready to export the defined dataset.

## Configuring the Airbyte Connector

Conveniently for us, Airbyte already provides a Senseforce Source Connector - meaning we have an easy time and can use the user interface of Airbyte to configure our data extraction.

1. Clone the [Airbyte Github Repository](https://github.com/airbytehq/airbyte) to your local machine by running:

   ```bash
   git clone https://github.com/airbytehq/airbyte.git
   ```

    This will download the Airbyte source code and provides a convenient way for us to spin up an on-demand airbyte instance on our local machine.

    >**NOTE:** Alternatively, you can also host airbyte on a remote server - but that's a story for another time.
2. Run the following commands to run Airbyte on your local machine.

   ```bash
   cd airbyte
   docker compose up
   ```

   Wait until the following output is shown in your terminal:

   ```bash
         ___    _      __          __
        /   |  (_)____/ /_  __  __/ /____
       / /| | / / ___/ __ \/ / / / __/ _ \
      / ___ |/ / /  / /_/ / /_/ / /_/  __/
     /_/  |_/_/_/  /_.___/\__, /\__/\___/
                         /____/
     --------------------------------------
      Now ready at http://localhost:8000/
     --------------------------------------
     Version: 0.40.23
    
   ```

3. With your browser, navigate to ``http://localhost:8000``. The default username is ``airbyte`` and password is ``password``. Complete the sign-up steps and will arrive at a page similar to the one below.
    ![Airbyte Start-Page](/blog/pictures/airbyteSenseforceBlogPost/AirbyteStarter.png "Airbyte Start-Page")

4. Click on "Create your first Connection" and select "Senseforce" from the Airbyte Dropdown menu
    ![Airbyte source selection](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSelect.png "Airbyte source selection")
5. You will arrive at the following screen:
    ![Airbyte connector config screen](/blog/pictures/airbyteSenseforceBlogPost/SenseforceAirbyteConfigScreen.png "Airbyte connector config screen")

    Add the information as follows:
    - **Source name**: Display name of this source in your airbyte instance. Can be any name.
    - **Dataset ID**: Id of your Senseforce dataset. This is the last part of your dataset-url in Senseforce
        ![Senseforce Dataset Id](/blog/pictures/airbyteSenseforceBlogPost/SenseforceDSId.png "Senseforce Dataset Id")

    - **The first day (in UTC) when to read data from**: As the name implies.
    - **Senseforce backend URL**: The URL of your Senseforce backend. Easiest way to find your backend URL is to log out from your Senseforce profile. In the login Screen, you see the backend-url. It's simply the domain of the login screen.
        ![Senseforce backend url](/blog/pictures/airbyteSenseforceBlogPost/SenseforceBackendUrl.png "Senseforce backend url")

    - **API Access Token**: Enter the access token you created in the previous step.

    Click ``Set up source`` afterwards.
6. The next screen will ask you to select a Destination:
        ![Airbyte Destination selection screen](/blog/pictures/airbyteSenseforceBlogPost/AirbyteDestinationSelection.png "Airbyte Destination selection screen")

   There you may select where to send your data to. In our example, we want to select ``Postgres`` to store the exports in our very own database. In the next screen, enter:
   - **Destination name**: Name of this Destination in your Airbyte instance. This can be any name.
   - **Host**: Hostname of the database.
   - **Port**: Port of the database.
   - **DB Name**: Name of the database.
   - **Default Schema**: The default schema tables are written to if the source does not specify a namespace. The usual value for this field is "public".
   - **User**: Username to use to access the database.
   - **Password**: Password associated with the database user.
   - **SSL Connection**: Encrypt data using SSL. When activating SSL, select one of the connection modes in the next setting.
   - **SSL modes**: Only relevant, if you select SSL Connection. This setting defines the SSL connection modes to connect to your Postgres database. Make sure your Postgres database is configured to connect with the selected mode.
   - **JDBC URL Params**: Additional properties to pass to the JDBC URL string when connecting to the database formatted as 'key=value' pairs separated by the symbol '&'. (example: key1=value1&key2=value2&key3=value3).
   - **SSH Tunnel Method**: Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use. (This is actually a great feature, as it allows to securely SSH - tunnel to your database host. So you don't need to expose the database host).

7. Click "Set up Destination".

8. The next screen is the "Connection" screen, allowing you to configure, how to sync data between Senseforce and your PostgreSQL.
You might hover over all the information-symbols to get an easy-to-understand description of what exactly we are setting.
To finish the configuration, adjust the next screen as follows and click on "Set up connection".
        ![Airbyte Senseforce to Postgres](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSenseforceConnectionPG.png "Airbyte Senseforce to Postgres connection configuration")

   - **Connection Name**: Name of the connection in your Airbyte instance. Can be any name.
   - **Replication frequency**: Airbyte provides a powerful scheduler. If you want, you can set this to eg. 30 minutes to schedule a data sync every 30 minutes. We are interested in one-time-sync only, therefore we set it to "Manual".
   - **Namespace:** This defines the name of our resulting file. It will be called ``export`` in our example.
   - **Sync mode**: This defines how Airbyte syncs data from source to destination. Options are either "Incremental" or "Full Refresh". A "Full Refresh" always syncs all data. For Incremental Syncs, Airbyte stores a state variable - which in case of the Senseforce connector is the Timestamp of your Dataset. When you attempt to sync this source the next time, Airbyte will look up this state variable and continue syncing from the last successfully synced timestamp.
   - **Normalization & Transformation**: For PostgreSQL destination, Airbyte provides a nice feature called Normalization. When running airbyte, the raw-data are simply stored as JSON blobs - so you will end up with having one single column called ``_airbyte_raw_data``. If you enable ``Normalization``, Airbyte attempts to unpack and flatten the JSON and provide separate columns for each JSON key. subsequently this means, that you will end up with one column per Senseforce dataset column - which is kind of what we want.
    > **NOTE:** If you do not enable Normalization, your data will still be synced - but you need to unpack the JSON in your downstream systems.

## Starting the Sync

You are now read for your first data extraction. In the connection screen where you ended up after configuring your connector, click ``Sync now`` to start your first source synchronization. You might click on the "Sync Running" button to open the Logs and see whats happening. In the below example we see, that the connector is currently working and reading thousands of records.
        ![Airbyte sync logs](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSyncLogsPG.png "Airbyte sync logs")

Wait until the sync is finished. You can find your exported data in the specified PostgreSQL database.

## Advantages of Airbyte for Senseforce data syncs

Ok, we did all this connector configuration work - but what did we gain - compared to directly using the [Senseforce API](https://manual.senseforce.io/manual/sf-platform/public-api/endpoints)? Actually quite a lot:

1. We can use the configured connector to sync Senseforce data to any other supported Destination. Like local CSVs, Google BigQuery, Snowflake or even MQTT.
2. The Senseforce API is quite powerful, but also quite complex. It allows filtering and pagination. Airbyte handles all of that for us.
   1. It paginates data to never fetch more than the Senseforce-supported API limits.
   2. It implements backoff and retries to handle API rate limits.
   3. And most importantly: Airbyte uses [Stream Slices](https://airbytehq.github.io/connector-development/cdk-python/stream-slices/) to fetch small chunks of data. So if you want to fetch data for let's say 5 years - Airbyte intelligently filters the Dataset to only include one day worth of data. This ensures to prevent any timeouts and API overloads.
3. We can start scheduling this connection - to automatically get the latest amount of data. We can combine this with "Incremental Syncs" to extract the most important information from Senseforce in a cost- and time-efficient manner.

## Summary

We have seen, that Airbyte makes it easy to extract data even from very powerful APIs like the Senseforce APIs. It helps with features like Incremental Syncs, Retries, Backoff strategies, pagination and Stream Slices.

We also saw the easy-to-use Senseforce query builder and how convenient we can create our Datasets which we subsequently use in one of our downstream systems. By utilizing a PostgreSQL export, we enable ourselves to do advanced data analyses or custom BI dashboarding.

Senseforce, with it's very targeted machine industry low-code-tool seems perfect to collect, manage and prepare our data with very little to now IT/Data Science know how required. In combination with Airbyte, one can enable their companies Data Scientists to perform further Analyses, Machine Learning and more.


How to sync data from Senseforce (Paze) to PostgreSQL with Airbyte


In this blog post we are going to discover what Airbyte is, what Senseforce is and how we can utilize Airbyte to sync any dataset from Senseforce to a local csv file.

- [What is Airbyte](#what-is-airbyte)
- [What is Senseforce](#what-is-senseforce)
- [Prerequisites](#prerequisites)
- [Prepare a Dataset in Senseforce](#prepare-a-dataset-in-senseforce)
- [Configuring the Airbyte Connector](#configuring-the-airbyte-connector)
- [Starting the Sync](#starting-the-sync)
- [Advantages of Airbyte for Senseforce data syncs](#advantages-of-airbyte-for-senseforce-data-syncs)
- [Summary](#summary)

## What is Airbyte

In simple terms, [Airbyte](https://airbyte.com/) is a system made for integrating data. More specifically, it is tailored to excel at ELT - use cases. Meaning, they are extremely good in extracting and loading data and then storing them in another database or system. Why are they so good?

- They have a ton of free and ready-to-use connectors. So there is a good chance that the system you want to integrate already is covered by Airbyte.
- They have by far THE best connector building SKD. If the system of your choice is not available as a connector - it's really easy to create new connectors (which is also the reason they have so many of them)
- Awesome, helpful and active community. You know who built a lot of the Airbyte-Connectors? Well, the community. Airbyte is very much focused on it's community and they invest heavily in building and maintaining an active community. They are even paying active community members.

And what are they offering?

- As mentioned, a lot of **connectors out-of-the-box** and a great connector builder SKD
- **Scheduling** to automatically trigger syncs
- **Logging and Monitoring** of connectors
- **Incremental updates** (no need to sync ALL data with every sync. Airbyte will maintain a state)
- **Pagination**
- **Authentication**
- **Stream Slicing** (If you have a lot of data to sync, Airbyte automatically divides the API requests into chunks of data - not overloading the source APIs)

All in all, Airbyte is one of the best, if not the best solution to integrate modern systems into a modern data stack.

![Airbyte Marketing Overview](/blog/pictures/airbyteSenseforceBlogPost/airbyteHeader.png "How Airbyte markets themselves (source: https://airbyte.com)")

## What is Senseforce

> **NOTE:** As per time of this writing Senseforce was re-branded to Paze.Industries. As many still know them with their previous name, I'll call them Senseforce, but update this post once the new brand is established. Congrats to the re-branding by the way.

[Senseforce](https://senseforce.io/)/[Paze.industries](https://paze.industries/) - in it's core - is a low-code IoT solution, targeted for the machine industry. Senseforce excels in out-of-the-box feature-completeness. They provide a wide array of features needed for big-tech IoT installations - they make it relatively easy to connect a machine, gather data and create feature-rich data applications in their cloud platform and cover the full lifecycle of a machine when it comes to the machine's data.

Among others, they offer the following key features:

- **Low-Code** query builder. It's one of the feature-richest low-code SQL query builders out there
- **Script integrations**: Besides low-code queries, one can also extend their data analytics with scripts
- **Dashboards**
- **Edge Device Remote Management**: They provide secure remote accessibility to any connected remote device
- **Virtual Events**: Sort of data Transformations
- **Scheduling and Triggers**: Create Events and Actions based on your data
- **User/Groups**: Senseforce was made to accommodate tenants and clients of tenants, meaning they provide very detailed user and group management capabilities

And thankfully they also offer a REST-API, which we are going to use to sync their data from.

![Senseforce low-code query builder](/blog/pictures/airbyteSenseforceBlogPost/senseforceFunctions.png "Senseforce low-code query builder")

## Prerequisites

To follow this guide, you need:

- git and docker installed
- a Senseforce user account which can create datasets

## Prepare a Dataset in Senseforce

As all Airbyte - Connectors, also the Senseforce Airbyte connector provides a great starting point for how to get started. See [the airbyte docs](https://docs.airbyte.com/integrations/sources/senseforce/) for the introduction. Nevertheless, let's start from scratch here.

To use Airbyte to sync some data from Senseforce, we first need to define what data we want to extract.

1. Create a new Dataset

    ![Create new Dataset](/blog/pictures/airbyteSenseforceBlogPost/SenseforceNewDs.png "Create new Dataset")

2. Add the columns you want to sync by clicking on the data attributes in the "Add Data" section

    ![Senseforce Add Data Dialog](/blog/pictures/airbyteSenseforceBlogPost/SenseforceAddData.png "Senseforce Add Data Dialog")

    **Important: You definitely need to add the "Timestamp", "Thing" and "Id" column of the "Metadata" section. This is needed so that Airbyte can provide the Stream Slicing and Incremental Sync features.**

    In our example we are interested in the "Uncompressed size" and the "Inserted Events" metrics - see the above Screenshot for reference. But feel free to add any columns you like - as long as you keep Thing, Timestamp and Id in the dataset.
3. Give the Dataset a nice name and save.

    ![Senseforce save the dataset](/blog/pictures/airbyteSenseforceBlogPost/SenseforceSaveDataset.png "Senseforce save the dataset")

4. Navigate to your user profile and create an API token

    ![Senseforce Create an API Token](/blog/pictures/airbyteSenseforceBlogPost/SenseforceApiToken.png "Senseforce Create an API Token")

    Make sure to not down this token as you will need it later in the Airbyte configuration.

> **NOTE:** That's it. Your Senseforce installation is ready to export the defined dataset.

## Configuring the Airbyte Connector

Conveniently for us, Airbyte already provides a Senseforce Source Connector - meaning we have an easy time and can use the user interface of Airbyte to configure our data extraction.

1. Clone the [Airbyte Github Repository](https://github.com/airbytehq/airbyte) to your local machine by running:

        ```shell
        git clone https://github.com/airbytehq/airbyte.git
        ```

    This will download the Airbyte source code and provides a convenient way for us to spin up an on-demand airbyte instance on our local machine.

    >**NOTE:** Alternatively, you can also host airbyte on a remote server - but that's a story for another time.
2. Run the following commands to run Airbyte on your local machine.

        ```shell
        cd airbyte
        docker compose up
        ```

   Wait until the following output is shown in your terminal:

        ```shell
             ___    _      __          __
            /   |  (_)____/ /_  __  __/ /____
           / /| | / / ___/ __ \/ / / / __/ _ \
          / ___ |/ / /  / /_/ / /_/ / /_/  __/
         /_/  |_/_/_/  /_.___/\__, /\__/\___/
                             /____/
         --------------------------------------
          Now ready at http://localhost:8000/
         --------------------------------------
         Version: 0.40.23
    
        ```

3. With your browser, navigate to ``http://localhost:8000``. The default username is ``airbyte`` and password is ``password``. Complete the sign-up steps and will arrive at a page similar to the one below.

    ![Airbyte Start-Page](/blog/pictures/airbyteSenseforceBlogPost/AirbyteStarter.png "Airbyte Start-Page")

4. Click on "Create your first Connection" and select "Senseforce" from the Airbyte Dropdown menu

    ![Airbyte source selection](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSelect.png "Airbyte source selection")

5. You will arrive at the following screen:

    ![Airbyte connector config screen](/blog/pictures/airbyteSenseforceBlogPost/SenseforceAirbyteConfigScreen.png "Airbyte connect to config screen")

    Add the information as follows:

    - **Source name**: Display name of this source in your airbyte instance. Can be any name.
    - **Dataset ID**: Id of your Senseforce dataset. This is the last part of your dataset-url in Senseforce

        ![Senseforce Dataset Id](/blog/pictures/airbyteSenseforceBlogPost/SenseforceDSId.png "Senseforce Dataset Id")

    - **The first day (in UTC) when to read data from**: As the name implies.
    - **Senseforce backend URL**: The URL of your Senseforce backend. Easiest way to find your backend URL is to log out from your Senseforce profile. In the login Screen, you see the backend-url. It's simply the domain of the login screen.

        ![Senseforce backend url](/blog/pictures/airbyteSenseforceBlogPost/SenseforceBackendUrl.png "Senseforce backend url")

    - **API Access Token**: Enter the access token you created in the previous step.

    Click ``Set up source`` afterwards.
6. The next screen will ask you to select a Destination:

    ![Airbyte Destination selection screen](/blog/pictures/airbyteSenseforceBlogPost/AirbyteDestinationSelection.png "Airbyte Destination selection screen")

   There you may select where to send your data to. In our example, we want to select ``Local CSV`` to store the exports to a local CSV file. In the next screen, enter:
   - **Destination name**: Name of this Destination in your Airbyte instance. This can be any name.
   - **destination_path**: Airbyte adds all files to a local folder mount which can - by default - be found in your locals host folder ``/tmp/airbyte_local``. The setting ``destination_path`` therefore needs to be relative to ``/local``. A possible example is ``/local/export``. **Note:** This setting defines the directory where your files are placed - not the filename itself.

7. Click "Set up Destination".
8. The next screen is the "Connection" screen, allowing you to configure, how to sync data between Senseforce and your local csv.
You might hover over all the information-symbols to get an easy-to-understand description of what exactly we are setting.
To finish the configuration, adjust the next screen as follows and click on "Set up connection".

   ![Airbyte Senseforce to Local CSV connection configuration](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSenseforceConnection.png "Airbyte Senseforce tLocal CSV connection configuration")

   - **Connection Name**: Name of the connection in your Airbyte instance. Can be any name.
   - **Replication frequency**: Airbyte provides a powerful scheduler. If you want, you can set this to eg. 30 minutes to schedule a data sync every 30 minutes. We are interested in one-time-sync only, therefore we set it to "Manual".
   - **Namespace:** This defines the name of our resulting file. It will be called ``export`` in our example.
   - **Sync mode**: This defines how Airbyte syncs data from source to destination. Options are either "Incremental" or "Full Refresh". A "Full Refresh" always syncs all data. For Incremental Syncs, Airbyte stores a state variable - which in case of the Senseforce connector is the Timestamp of your Dataset. When you attempt to sync this source the next time, Airbyte will look up this state variable and continue syncing from the last successfully synced timestamp.

## Starting the Sync

You are now read for your first data extraction. In the connection screen where you ended up after configuring your connector, click ``Sync now`` to start your first source synchronization. You might click on the "Sync Running" button to open the Logs and see whats happening. In the below example we see, that the connector is currently working and reading thousands of records.

   ![Airbyte sync logs](/blog/pictures/airbyteSenseforceBlogPost/AirbyteSyncLogs.png "Airbyte sync logs")

Wait until the sync is finished. You can find your exported CSV file in the the ``/tmp/airbyte-local`` directory on your host machine.

## Advantages of Airbyte for Senseforce data syncs

Ok, we did all this connector configuration work - but what did we gain - compared to directly using the [Senseforce API](https://manual.senseforce.io/manual/sf-platform/public-api/endpoints)? Actually quite a lot:

1. We can use the configured connector to sync Senseforce data to any other supported Destination. Like PostgreSQL, Snowflake or even MQTT.
2. The Senseforce API is quite powerful, but also quite complex. It allows filtering and pagination. Airbyte handles all of that for us.
   1. It paginates data to never fetch more than the Senseforce-supported API limits.
   2. It implements backoff and retries to handle API rate limits.
   3. And most importantly: Airbyte uses [Stream Slices](https://airbytehq.github.io/connector-development/cdk-python/stream-slices/) to fetch small chunks of data. So if you want to fetch data for let's say 5 years - Airbyte intelligently filters the Dataset to only include one day worth of data. This ensures to prevent any timeouts and API overloads.
3. We can start scheduling this connection - to automatically get the latest amount of data. We can combine this with "Incremental Syncs" to extract the most important information from Senseforce in a cost- and time-efficient manner.

## Summary

We have seen, that Airbyte makes it easy to extract data even from very powerful APIs like the Senseforce APIs. It helps with features like Incremental Syncs, Retries, Backoff strategies, pagination and Stream Slices.

We also saw the easy-to-use Senseforce query builder and how convenient we can create our Datasets which we subsequently use in one of our downstream systems. By utilizing a simple CSV export, we enable ourselves to do some Data Analytics on our local machine.

Senseforce, with it's very targeted machine industry low-code-tool seems perfect to collect, manage and prepare our data with very little to now IT/Data Science know how required. In combination with Airbyte, one can enable their companies Data Scientists to perform further Analyses, Machine Learning and more.


How to sync data from Senseforce (Paze) to local CSV with Airbyte


[CloudNativePG](https://cloudnative-pg.io/) makes it rather easy to set up and operate a highly available PostgreSQL cluster on Kubernetes. See [this post](https://www.datascienceengineer.com/blog/post-ha-postgresql-on-kubernetes/) for more details.

Now while setting up is already good - operating PostgreSQL obviously requires one additional component: **Backups**.

- [pgBarman - Overview](#pgbarman---overview)
- [Prerequisites](#prerequisites)
- [Setting up Barman backups with CloudNativePG](#setting-up-barman-backups-with-cloudnativepg)
  - [The settings explained](#the-settings-explained)
- [Executing backups](#executing-backups)
  - [On-demand backups](#on-demand-backups)
  - [Scheduled backups](#scheduled-backups)
- [A note on how often to schedule backups](#a-note-on-how-often-to-schedule-backups)
- [Recovery](#recovery)
  - [Recover from an existing Backup](#recover-from-an-existing-backup)
  - [Recover from backup object storage](#recover-from-backup-object-storage)
  - [Point in time recovery](#point-in-time-recovery)
  - [Important to note for recovery](#important-to-note-for-recovery)
- [Summary](#summary)

**The TLDR is found in the [Summary](#summary)**

There are a ton of backup solutions for PostgreSQL.

- [pgBarman](https://pgbarman.org/)
- [pgBackRest](https://github.com/pgbackrest/pgbackrest)
- [pg_probackup](https://github.com/postgrespro/pg_probackup)
- [pg_basebackup](https://www.postgresql.org/docs/10/app-pgbasebackup.html)

to just mention 4 of the more popular options.

Now - if we look at how we deployed our cluster - using CloudNativePG - it's clear that we could use any of the methods. Nevertheless, the CloudNativePG operator comes with first class pgBarman support. Which is no surprise as both pgBarman and CloudNativePG are from the awesome people at [Enterprise DB](https://www.enterprisedb.com/).

## pgBarman - Overview

pgBarman or short Barman was developed to implement backup and disaster recovery for PostgreSQL databases. One of the main design aspects was to optimize for maximum business continuity in case of a disaster.

**Features:**
Some of the more exciting features are:

- Point in time recovery
- Remote backup
- WAL archiving and streaming
- Synchronous WAL streaming (means exactly zero data loss in case of failures)
- Incremental and parallel backups
- Backup catalog

This guide will show us some of these features in action.

## Prerequisites

To follow this guide, you need:

1. A [running CloudNativePG cluster](https://www.datascienceengineer.com/blog/post-ha-postgresql-on-kubernetes/#summary)
2. An [Azure Blob Storage Container](https://learn.microsoft.com/en-us/azure/storage/blobs/blob-containers-portal#create-a-container)

## Setting up Barman backups with CloudNativePG

First, we will add Barman to our clust objects - and configure it to store it's backup in an [Azure Blob Storage Container](https://azure.microsoft.com/en-us/products/storage/blobs/).

Setting up Barman with CloudNativePG is as simple as adding an additional ``backup`` configuration block to the PostgreSQL cluster object and creating a k8s secret for accessign the backup backend.

CloudNativePG currently supports, Azure Blob Storage, Google Cloud Storage and AWS S3 for backup backends. In this guide we'll use Azure Blob Storage - but the steps are very similar for the other backends. See the following documentation section for more details: [Link to CloudNativePG docs](https://cloudnative-pg.io/documentation/1.15/backup_recovery/#cloud-provider-support).

First, let's create our backup backend secret - the secret to access the cloud backend (Azure Blob Storage in our case). Please replace ``<base64-encoded-connection-string>`` with your Azure Blob Storage container connection string. You can find the connection string in the Azure Console at your storage account page -> Access keys --> Connection string

![Azure Blob Storage Access Keys](/blog/pictures/postgresBackupsPost/AzureAccessKey.png "Azure Blob Storage Access Keys")

1. Create the following secret and apply it to your cluster.

    ```yaml
    apiVersion: v1
    kind: Secret
    metadata:
    name: backup-creds
    namespace: postgres-namespace
    data:
    AZURE_CONNECTION_STRING: <base64-encoded-connection-string>

    ```

2. Next, update the Cluster k8s object. If we look at the [Summary of how we set up our cluster](https://www.datascienceengineer.com/blog/post-ha-postgresql-on-kubernetes/#summary), there we had our cluster manifest. To now add backups to the cluster, simply add the following lines to the manifest (see highlighted lines 3 to 16)

    ```yaml{3,4,5,6,7,8,9,10,11,12,13,14,15,16}
    superuserSecret:
        name: example-superuser
    backup:
        barmanObjectStore:
            destinationPath: https://devopsandmorebackups.blob.core.windows.net/postgres-backups/generalpurpose # This is an azure blob storage path
            azureCredentials:
                connectionString:
                    name: backup-creds
                    key: AZURE_CONNECTION_STRING
            wal:
                compression: gzip
                maxParallel: 8
                encryption: AES256
            data:
                compression: gzip
                encryption: AES256
                immediateCheckpoint: false
                jobs: 2
        retentionPolicy: "30d"
    storage:
        pvcTemplate:
    ```

    Afterward, apply the manifest to your cluster.

### The settings explained

- **destinationPath**: Destination path of the Microsoft Azure Blob Storage Container. Format: ``<http|https>://<account-name>.<service-name>.core.windows.net/<container>/<blob>``. Note, that ``<container>`` refers to the name of your Blob Storage container and ``<blob>`` to the name of the Blob inside the container. The "blob" will automatically be created - with the name you set here.
- **connectionString**: Reference to the secret which stores the connection string
- **wal**: Define the WAL archiving/recovery behavior:
  - **maxParallel**: Number of WAL files to be either archived in parallel.
  - **compression**: Whether to compress the backups. Options are: gzip, bzip2, snappy. Off by default
  - **encryption**: Whether to encrypt the WAL files. Options are: AES256 or aws:kms. Leave empty to use the backup backend storage policy.
- **data**: Defines the data backup behavior:
  - **immediateCheckpoint**: If set to true, an immediate checkpoint will be used, meaning PostgreSQL will complete the checkpoint as soon as possible.
  - **compression:**: Whether to compress the backups. Options are: gzip, bzip2, snappy. Off by default.
  - **encryption**: Whether to encrypt the WAL files. Options are: AES256 or aws:kms. Leave empty to use the backup backend storage policy.
  - **jobs**: The number of parallel jobs to be used to upload the backup.
- **retentionPolicy**: Defines when old backups should be deleted.

> **NOTE:** That's actually all we need. Apply the cluster manifest and your CloudNativePG operated PostgreSQL cluster is ready to make it's first backup. WAL archiving by the way is already started directly after applying these changes.

## Executing backups

There are two ways to execute a backup.

1. On-demand and
2. Scheduled

While On-demand backups are fine if you need a backup immediately - eg. because you attempt complex maintenance - scheduled backups are used during everydays operation.

### On-demand backups

For on-demand backups simply create a ``Backup`` object by creating the following yaml manifest and applying it:

```yaml
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: general-purpose-backup
  namespace: postgres-namespace
spec:
  cluster:
    name: example-cluster
```

Directly after applying the manifest, the operator will attempt to initiate the backup. You can check progress (and potential errors) by running ``kubectl describe backups -n postgres-namespace general-purpose-backup``.

### Scheduled backups

Scheduled backups are not more complex to set up. Create a yaml manifest and apply it:

```yaml
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: general-purpose-scheduled-backup
  namespace: postgres-namespace
spec:
  # Note that this cron dialect has 6 places - an additional one for seconds
  schedule: "1 0 0 * * 0"
  # Set this to true, if you want to suspend the backup for now
  suspend: false
  # Determines if the first backup should be done immediately
  immediate: true
  #Indicates which ownerReference should be put inside the created backup resources.
  # - none: no owner reference for created backup objects (same behavior as before the field was introduced)
  # - self: sets the Scheduled backup object as owner of the backup
  # - cluster: set the cluster as owner of the backup
  backupOwnerReference: self
  cluster:
    name: example-cluster
```

After applying the manifest, the backup will be scheduled as defined. If ``immediate`` is set to true, the backup will execute immediately.

Check the backup state by running ``kubectl describe backups -n postgres-namespace general-purpose-scheduled-backup``.

## A note on how often to schedule backups

The interval of how often to schedule backups is determined mainly by how fast one needs to recover after a desaster. Between two backups, Barman needs to recover from the WAL-Archive which takes longer than recovery from backup. That being said, backup intervals of faster than once per week are rarely needed and simply lead to unnecessary load and costs.

## Recovery

While taking backups is nice - we need to be able to recover a cluster from backup in case the unthinkable happens.

To recover a cluster from backup, we can bootstrap a new cluster by referencing the backup data. This means **we can't recover our backup into an existing Cluster**.

### Recover from an existing Backup

If there is a ``Backup`` object inside the same namespace as you want your cluster to recover to, simply add the following snippets to your cluster yaml manifest (in the ``spec`` - section):

```yaml
  bootstrap:
    recovery:
      backup:
        name: general-purpose-scheduled-backup
```

Applying the manifest will create a cluster and recover from the data referenced in the backup.

### Recover from backup object storage

If there is NO ``Backup`` object inside the same namespace as you want your cluster to recover to, add the following ``externalCluster`` configuration to the ``spec`` section of your cluster manifest.
Replace ``<your-previous-cluster-name>`` with the name of what your cluster was previously called.
If you do not recall the name of your cluster, you can the Azure blob storage path (which is ``https://devopsandmorebackups.blob.core.windows.net/postgres-backups/generalpurpose``) with the Azure Blob Storage console. The first subdirectory found in this folder is the name of the server.

```yaml
  bootstrap:
    recovery:
      source: clusterBackup

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        serverName: "<your-previous-cluster-name>"
        destinationPath: https://devopsandmorebackups.blob.core.windows.net/postgres-backups/generalpurpose # This is an azure blob storage path
        azureCredentials:
          connectionString:
            name: backup-creds
            key: AZURE_CONNECTION_STRING
        wal:
          maxParallel: 8
```

### Point in time recovery

Point in time recovery is the process of not recovering all the WALs up to the latest one, but until a certain point in time. This comes handy, if you messed up your database and want to restore the database state of eg. yesterday.

Compare with [Chapter Setting up Barman](#setting-up-barman-backups-with-cloudnativepg) - as the ``backup`` configuration section needs to match the ``externalCluster`` configuration during recovery.

While this process is rather complex in the background, CloudNativePG as well as Barman help us tremendously. We again simply need to define, how we want to bootstrap a new cluster. We - again as for normal recovery - can choose to either recover from ``Backup`` or from an backup object store. As the process is similar to the chapters above, we are only going to demonstrate point in time recovery for object store.

Add the following snippets to your cluster ``spec`` yaml section:

```yaml
  bootstrap:
    recovery:
      source: clusterBackup
      recoveryTarget:
        targetTime: "2020-11-26 15:22:00.00000+00"

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        destinationPath: https://devopsandmorebackups.blob.core.windows.net/postgres-backups/generalpurpose # This is an azure blob storage path
        azureCredentials:
          connectionString:
            name: backup-creds
            key: AZURE_CONNECTION_STRING
        wal:
          maxParallel: 8
```

As you can see it's literally the same configuration as for normal backup recovery, but with the additional ``targetTime`` setting.

### Important to note for recovery

1. You need to bootstrap a fresh cluster
2. Use a different blob configuration for your recovery object and your backup of the new cluster. Eg if your "old" cluster you want to recover from had a ``<blob>`` name of ``postgres-backup`` - use a different blob name in the ``backup`` section of your new cluster. You can reuse the same container though - just use a different blob name.
3. The operator does NOT attempt to back up (and recover) the underlying secrets. Make sure to back them up with your regular k8s backups.

## Summary

Adding backups to a CloudNativePG operated, highly available PostgreSQL cluster is rather easy.

1. Add the following sections to your Cluster yaml manifest and apply

    ```yaml{3,4,5,6,7,8,9,10,11,12,13,14,15,16}
    superuserSecret:
        name: example-superuser
    backup:
        barmanObjectStore:
            destinationPath: https://devopsandmorebackups.blob.core.windows.net/postgres-backups/generalpurpose # This is an azure blob storage path
            azureCredentials:
                connectionString:
                    name: backup-creds
                    key: AZURE_CONNECTION_STRING
            wal:
                compression: gzip
                maxParallel: 8
                encryption: AES256
            data:
                compression: gzip
                encryption: AES256
                immediateCheckpoint: false
                jobs: 2
        retentionPolicy: "30d"
    storage:
        pvcTemplate:
    ```

2. Add a scheduled backup by creating a yaml manifest and applying it:

    ```yaml
    apiVersion: postgresql.cnpg.io/v1
    kind: ScheduledBackup
    metadata:
        name: general-purpose-scheduled-backup
        namespace: postgres-namespace
    spec:
        # Note that this cron dialect has 6 places - an additional one for seconds
        schedule: "1 0 0 * * 0"
        # Set this to true, if you want to suspend the backup for now
        suspend: false
        # Determines if the first backup should be done immediately
        immediate: true
        #Indicates which ownerReference should be put inside the created backup resources.
        # - none: no owner reference for created backup objects (same behavior as before the field was introduced)
        # - self: sets the Scheduled backup object as owner of the backup
        # - cluster: set the cluster as owner of the backup
    backupOwnerReference: self
    cluster:
        name: example-cluster

3. (Optional) Run an on-demand backup by creating a yaml manifest and applying it:

    ```yaml
    apiVersion: postgresql.cnpg.io/v1
    kind: Backup
    metadata:
        name: general-purpose-backup
        namespace: postgres-namespace
    spec:
        cluster:
        name: example-cluster
    ```

To check the status of your backups, simply run: ``kubectl describe backups -n postgres-namespace <name-of-your-backup>``.


Automated Backups with CloudNativePG for PostgreSQL on Kubernetes

High available PostgreSQL on kubernetes


As a single-person company, I need to be as efficient as possible when it comes to IT operations. And therefore, I feel very strong about high availability. While a lot of small businesses neglect this aspect of system deployment, I try to invest here very early. The reason for that is very simple: I want to concentrate on growing my business rather than working on (and worrying about) downtime.

> **The TLDR can be found in the [Summary](#summary)**

- [About k3sup](#about-k3sup)
- [Prerequisites](#prerequisites)
- [Installing k3sup](#installing-k3sup)
- [Setting up a high available, multimaster k3s installation](#setting-up-a-high-available-multimaster-k3s-installation)
  - [Initialize the first server on the first node in "cluster" mode](#initialize-the-first-server-on-the-first-node-in-cluster-mode)
  - [Explanation of k3sup command line parameters](#explanation-of-k3sup-command-line-parameters)
- [Using a different network interface for inter-node communication](#using-a-different-network-interface-for-inter-node-communication)
  - [Encrypting flannel traffic](#encrypting-flannel-traffic)
- [Summary](#summary)

In a previous article, we set up k3s on a remote server in a cost-efficient single-node environment. For more details, see [one of my previous posts](https://www.datascienceengineer.com/blog/post-k3s-kubernetes-in-10-minutes).
This time, we extend this installation with two more nodes and achieve a highly available k3s cluster. We will see that due to the very straight-forward tooling around k3s, setting up a 3-node HA cluster is equally easy as installing a single-node one.
(That being said, what makes HA a little more complex than single-node deployments is actually having at least 3 virtual/physical server nodes to deploy on. However - this is a one time effort and with modern cloud or VPS providers also one you can afford quite easily).

```markdown
Setting up a system in high available manners is not much more complex than setting it up in a single-node setup
```

To proof this point, in this article I'll demonstrate how to set up kubernetes - more specifically k3s - in a high available installation.

Between this post and the one linked above, I found out about the superb tool [k3sup](https://github.com/alexellis/k3sup) - developed by Alex Ellis. This tool will help us today, setting up k3s on 3 remote servers.

## About k3sup

k3sup (pronounced ketchup) is a quite intuitive tool which bootstraps k3s on a local or remote virtual or physical machine. All one needs is local or ssh access to the server to install and the k3sup binary. k3sup also manages your KUBECONFIG, merges it with potentially existing KUBECONFIGs and provides immediate kubectl access to your remote, potentially highly available, k3s cluster. Especially the feature of merging KUBECONFIGs is quite nice - as we have seen [last time](https://www.datascienceengineer.com/blog/post-k3s-kubernetes-in-10-minutes), this aspect is quite complex - compared to other k3s setup steps.

For more details about k3sup, see their official [github page](https://github.com/alexellis/k3sup).

## Prerequisites

For this guide, we assume you have 3 physical or virtual servers which can communicate between each other. As we'll see below, we run k3s with embedded etcd, which requires a quorum for leader election - therefore we need an odd amount of nodes.

Furthermore, you have passwordless SSH access to each one of them. For more information about how to access your remote systems via SSH, see this excellent [Redhat guide](https://www.redhat.com/sysadmin/access-remote-systems-ssh).

## Installing k3sup

As a first step, we need to install k3sup on our local host machine. The binaries are conveniently provided in their [github release page](https://github.com/alexellis/k3sup/releases). For even more convenience, we can use the k3sup setup script - provided [by k3sup themselves](https://get.k3sup.dev).
As I like convenience, we use this script.

> **NOTE:** As always, please check scripts you download from the internet, whether they are to be trusted. For the time of this writing, the script was checked and found to do no harm.

1. If your local machine is a Linux machine, run the following commands:

```shell
curl -sLS https://get.k3sup.dev | sh
sudo install k3sup /usr/local/bin/
rm k3sup # (we can remove the downloaded binary after installation)
```

>**Windows:** For Windows, visit the binary [github release page](https://github.com/alexellis/k3sup/releases) and download the ``k3sup.exe`` file. No additional installation necessary.

## Setting up a high available, multimaster k3s installation

As established in our [last k3s blog entry](https://www.datascienceengineer.com/blog/post-k3s-kubernetes-in-10-minutes), k3s comes with two high availability modes:

1. Having an external database (eg. postgres) to store the metadata
2. Having etcd embedded in the k3s master nodes

Number 1 has the advantage, that you can have HA with 2 nodes, but you need to manage an external database system. Which - in my opinion - somehow defeats the purpose. So we (and k3sup by default) choose option 2. We set up a k3s cluster, having 3 nodes with the storage (etcd) embedded in the servers.

![k3s high available multi-server deployment with embedded storage](/blog/pictures/k3sIntroBlogPost/multiple_servers_embedded_db.png "k3s high available multi-server deployment with embedded storage")

The general workflow for setting up is:

1. Initialize the first server on the first node in "cluster" mode
2. Join the other two nodes as servers

### Initialize the first server on the first node in "cluster" mode

For setting up our first server, simply run, the following command - changing the placeholders as follows:

- ``your-servers-ip``: The IP-Address where you can reach your server via SSH
- ``your-servers-user``: The (SSH)-user of the remote server where you want to install k3s to

> **Important:** The server-user needs to either be root or have passwordless root via ssh enabled. Otherwise k3sup will fail.

You can check if both the IP and user are correct by trying to connect via SSH (``ssh <your-servers-user>@<your-servers-ip>``). If you can't connect via passwordless SSH to the server, please follow this [Redhat guide](https://www.redhat.com/sysadmin/access-remote-systems-ssh).

```shell
k3sup install --ip <your-servers-ip> --user <your-servers-user> --context k3scontext --local-path ~/.kube/config --cluster --merge
```

This command now bootstrapped a single k3s server instance with embedded etcd. Joining our two additional nodes is as easy as:

```shell
k3sup join --ip <ip-of-node-2> --user <user-of-node-2> --server-ip <your-servers-ip> --server-user <your-servers-user> --server

k3sup join --ip <ip-of-node-3> --user <user-of-node-3> --server-ip <your-servers-ip> --server-user <your-servers-user> --server
```

As with the step above, make sure to replace the placeholders as follows:

- ``your-servers-ip``: The IP-Address where you can reach your first server via SSH (the one we already initialized in the step before)
- ``your-servers-user``: The (SSH)-user of the remote server where you already bootstrapped k3s (the one we already initialized in the step before)
- ``ip-of-node-2``: The IP-Address where you can reach your second node via SSH
- ``user-of-node-2``: The (SSH)-user of the second remote node
- ``ip-of-node-3``: The IP-Address where you can reach your third node via SSH
- ``user-of-node-3``: The (SSH)-user of the third remote node

> That's already all the magic. You now have three k3s nodes running in high availability mode. Now you might start scheduling your workloads, using kubectl.

As k3sup did all the heavy lifting for your kubeconfig-setup, you can already use ``kubectl`` to manage workloads on your cluster.
Start by checking your nodes:

```shell
kubectl get node -o wide
```

This should give you your 3 nodes.

### Explanation of k3sup command line parameters

Besides the obvious ``user`` and ``ip`` flags, we used three additional flags while running our commands. These are:

- ``context``: Set the name of the kubeconfig context. The default is "default" - which I personally don't like so much, as there are potentially quite a view contexts in your kubeconfig.
- ``cluster``: starts this server in cluster-mode. It uses an embedded etcd to potentially be HA.
- ``server``: Flag required to tell k3sup to join the nodes to an existing cluster - as servers rather than agents.
- ``merge``: Merge the config with existing kubeconfig if it already exists. This is important, as by default, k3sup overwrites existing kubeconfigs. You might omit this flag, if you don't have an existing kubeconfig anyhow.

Besides these basic flags, there are a ton of other customization options. For the sake of completeness, I'll illustrate them here - shamelessly copying them from the command lines help screen:

- ``datastore``: If you run k3s in "with external datastore" mode, this sets the connection-string for the k3s datastore to enable HA - i.e. "mysql://username:password@tcp(hostname:3306)/database-name"
- ``host``:  Public hostname of node on which to install agent
- ``ip ip``:  Public IP of node (default 127.0.0.1)
- ``ipsec``:  Enforces and/or activates optional extra argument for k3s: flannel-backend option: ipsec
- ``k3s-channel``: Release channel: stable, latest, or pinned v1.19 (default "stable")
- ``k3s-extra-args``:  Additional arguments to pass to k3s installer, wrapped in quotes (e.g. --k3s-extra-args '--no-deploy servicelb')
- ``k3s-version``: Set a version to install, overrides k3s-channel
- ``local``:  Perform a local install without using ssh
- ``local-path``:  Local path to save the kubeconfig file (default "kubeconfig")
- ``no-extras``:  Disable "servicelb" and "traefik"
- ``print-command``:  Print a command that you can use with SSH to manually recover from an error
- ``print-config``: Print the kubeconfig obtained from the server after installation
- ``skip-install``: Skip the k3s installer
- ``ssh-key``: The ssh key to use for remote login (default "~/.ssh/id_rsa")
- ``ssh-port``: The port on which to connect for ssh (default 22)
- ``sudo``: Use sudo for installation. e.g. set to false when using the root user and no sudo is available. (default true)
- ``tls-san``: Use an additional IP or hostname for the API server
- ``token``: the token used to encrypt the datastore, must be the same token for all nodes

## Using a different network interface for inter-node communication

The following section is optional and only applies, if you have more than one network interface on your nodes.

By default, k3s uses [flannel](https://github.com/flannel-io/flannel) as network fabric with [VXLAN](https://www.juniper.net/us/en/research-topics/what-is-vxlan.html) as network backend. In short this means the k3s nodes will communicate via VXLAN between each other. It's important to keep in mind that the traffic through the VXLAN is not encrypted. However, this per se is not an issue in itself - see the following quite funny blog post about what this means for the general security context: [https://blog.ipspace.net/2015/04/omg-vxlan-encapsulation-has-no-security.html](https://blog.ipspace.net/2015/04/omg-vxlan-encapsulation-has-no-security.html).

By default, k3s uses the first non-loopback network interface to establish the flannel network. This might be good or might be bad, depending on your network configuration. If you have a private network where your nodes participate, it might be wise to use this network instead of a public one. To check your network configuration, run ``ip a`` or ``ifconfig``. The output might be similar to

```shell
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether xxxxxxxxxxxxxxxxxxxxxx
    altname enp0s18
    inet xxxxxxxxxxxx/22 brd xxxxxxxxx scope global eth0
       valid_lft forever preferred_lft forever
    inet6 xxxxxxxxxxxxxxxxxxxx/64 scope global
       valid_lft forever preferred_lft forever
    inet6 xxxxxxxxxxxxxxxxxxxxxx/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether xxxxxxxxxxxxxxxxxxxx
    altname enp0s19
    inet 10.0.0.1/22 brd 10.0.3.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet6 fe80::bc06:19ff:fe3d:1597/64 scope link
       valid_lft forever preferred_lft forever
```

In the above output, I have 3 interfaces:

- **lo**: The loopback interface
- **eth0**: A public network, accessing the internet
- **eth1**: A private network

If you'd like to not use the first network interface, but a different one, add the following flag to the k3sup commands:

```shell
--k3s-extra-args '--flannel-iface=eth1' # k3s will now use eth1 for flannel to create the network
```

Creating the k3s cluster therefore might look like

```shell
k3sup install --ip <your-servers-ip> --user <your-servers-user> --context k3scontext --local-path ~/.kube/config --cluster --merge --k3s-extra-args '--flannel-iface=eth1'
```

Make sure to also add this flag to your ``k3sup join`` commands!

### Encrypting flannel traffic

As mentioned above, flannel uses VXLAN as network backend. This is not bad per se, however the traffic through VXLAN is not encrypted. If a bad actor gains access to the VXLAN network, the traffic is exposed. To increase resiliency against such attacks, flannel provides different backends which can encrypt traffic. Popular ones are ipsec or wireguard. As wireguard is the [more modern and more secury option (with the default configs at least)](https://tailscale.com/compare/ipsec/#:~:text=IPsec%20and%20WireGuard%20VPNs%20are,on%20a%20powerful%20Linux%20computer.), we'll use wireguard in this guide.

1. Install wireguard. Detailed instructions are provided here: [https://www.wireguard.com/install/](https://www.wireguard.com/install/) . For Ubuntu/Debian, simply run ``sudo apt install wireguard``.
2. Add the following flag to your ``k3sup`` commands (all of them): ``--k3s-extra-args '--flannel-backend=wireguard-native'``. If you already have a k3s-extra-args flag, make sure to merge them. Eg. ``--k3s-extra-args '--flannel-iface=eth1 --flannel-backend=wireguard-native'``

## Summary

We've seen, that k3sup makes it very easy to set up a highly available kubernetes cluster on remote computers. Even with advanced features like different network interface and traffic encryption.
3 simple commands is all it needs to spawn the k3s cluster and also merge your cluster config with already existing kubeconfigs.
While I think k3s in itself is already great technology and very easy to use, k3sup makes it really a piece of cake to set up single-node or multi-node k3s clusters.

1. Prerequisites for HA k3s cluster
   - 3 nodes where to install k3s servers
   - SSH access to all the 3 nodes

2. Run the following commands to bootstrap your cluster and have your kubectl ready to go

   ```shell
   k3sup install --ip <your-servers-ip> --user <your-servers-user> --context k3scontext --cluster --merge

   k3sup join --ip <ip-of-node-2> --user <user-of-node-2> --server-ip <your-servers-ip> --server-user <your-servers-user> --context k3scontext --server --merge

   k3sup join --ip <ip-of-node-3> --user <user-of-node-3> --server-ip <your-servers-ip> --server-user <your-servers-user> --context k3scontext --server --merge

   kubectl get node -o wide
   ```

   - ``your-servers-ip``: The IP-Address where you can reach your first server via SSH
   - ``your-servers-user``: The (SSH)-user of the first remote server
   - ``ip-of-node-2``: The IP-Address where you can reach your second node via SSH
   - ``user-of-node-2``: The (SSH)-user of the second remote node
   - ``ip-of-node-3``: The IP-Address where you can reach your third node via SSH
   - ``user-of-node-3``: The (SSH)-user of the third remote node

```markdown
That's it - happy k3s-ing.
```


High availability Kubernetes with k3s


In this guide I'll show how to deploy a little API and expose it with the traefik reverse proxy. Traefik is already packaged with k3s, so no need for additional installation.

As described in my instruction manual about how to install k3s, K3s is one of the more lightweight kubernetes distributions out there, created by the well-known kubernetes experts at Rancher. Running kubernetes by using k3s is a real blast - and on top of the little complexity, k3s is CNCF-certified and marked as production-ready.

- [The application - a resume parsing backend](#the-application---a-resume-parsing-backend)
- [The goal](#the-goal)
- [Traefik Ingress](#traefik-ingress)
- [Cert-Manager](#cert-manager)
- [k8s Resource overview](#k8s-resource-overview)
- [How-To](#how-to)
- [Change issuer to Let's Encrypt Production](#change-issuer-to-lets-encrypt-production)
- [The Traefik Dashboard](#the-traefik-dashboard)
- [Summary - TLDR](#summary---tldr)

We will also enable automatic SSL certificate handling, using Let's encrypt and [kubernetes Cert Manager](https://cert-manager.io/).

## The application - a resume parsing backend

> Note: The application is not part of this guide - however I outline some of the details to set the context for the following steps

For a different project, I created a small project which takes pdf resumes (CVs), parses them and returns a machine readable JSON document with the summarized candidate information.
The application provides two REST endpoints:

- **POST: /parseresume**: Takes a pdf file as payload and parses it as resume
- **GET: /parseresume**: Simply returns ``{"answer": "test"}``

The application is served using [uvicorn](https://www.uvicorn.org/) - a python low-level webserver. It listens on port 8000.

## The goal

In the end, we want to serve the aforementioned application on ``https://myresume.datascienceengineer.com/parseresume``.
The communication should be SSL encrypted with Let's-Encrypt certificates and the certificate renewal should be automated.

## Traefik Ingress

The Traefik Ingress provider is a Kubernetes Ingress controller based on the popular Traefik reverse proxy. Traefik describes itself as "an open-source Edge Router that makes publishing your services a fun and easy experience. It receives requests on behalf of your system and finds out which components are responsible for handling them."

![Traefik Overview](/blog/pictures/k3straefikBlogPost/Traefik_Overview.png "Traefik Overview")

And indeed, using traefic is very easy and fun as they say - especially as it comes already prepacked with k3s.
One word though: While traefic is able to automatically work with Let's Encrypt certificates, it lacks a lot of features which for example the kubernetes-native cert-manager provides. Most noteworthy, as of time of this writing, the certificates are stored in the file system - meaning they are gone when the pod is restartet. It's also hard to impossible to share the certs across pods and nodes - making high availability setups nearly impossible.

## Cert-Manager

Enter cert-manager, the well-known certificate handling solution for kubernetes. It will obtain certificates from a variety of Issuers, both popular public Issuers as well as private Issuers, and ensure the certificates are valid and up-to-date, and will attempt to renew certificates at a configured time before expiry.

The certificates are stored as kubernetes secrets - making them easily shareable and a k8s-native resource.

And on top of that, cert-manager is known to be very reliable: I run cert-manager on several installations for years know - I had not a single incident with certificate since.

## k8s Resource overview

So, additionally to k3s, what do we need to set all of this up?

1. The cert-manager
2. A ClusterIssuer resource which is used by the cert-manager to issue certificates
3. A traefik-based Ingress resource which handles the incoming traffic, TLS termination and redirects to the right services
4. A certificate - but which is automatically created by the ClusterIssuer - so nothing todo here

    ![Kubernetes Resources](/blog/pictures/k3straefikBlogPost/k3straefik.png "Kubernetes Resources")

## How-To

> Note: We are installing all our own resources into namespace ``resume``

1. Install cert-manager by running ``kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.9.1/cert-manager.yaml``
2. Check, that cert-manager was installed: Run ``kubectl get pods -n cert-manager``. You should see something like:

        ```shell
        NAME                                       READY   STATUS    RESTARTS   AGE
        cert-manager-5dd59d9d9b-4q9vp              1/1     Running   0          26s
        cert-manager-cainjector-8696fc9f89-4z5k6   1/1     Running   0          26s
        cert-manager-webhook-7d4b5b8c56-qn2tt      1/1     Running   0          26s
        ```

3. Create a new file ``staging-issuer.yaml``

        ```yaml
        apiVersion: cert-manager.io/v1
        kind: ClusterIssuer
        metadata:
            name: letsencrypt-staging
            nmamespace: resume
        spec:
            acme:
            email: your-email@example.com
            server: https://acme-staging-v02.api.letsencrypt.org/directory
            privateKeySecretRef:
                name: letsencrypt-staging
            solvers:
                - http01:
                    ingress:
                    class: traefik
        ```

4. Apply the staging-issuer.yaml: ``kubectl apply -f staging-issuer.yaml``
5. Optional: Add a https-redirect middleware. This comes handy to automatically redirect http request to https. But, this step is optional. Add a file ``traefik-https-redirect-middleware.yaml`` and apply it

        ```yaml
        apiVersion: traefik.containo.us/v1alpha1
        kind: Middleware
        metadata:
        name: redirect-https
        namespace: resume
        spec:
        redirectScheme:
            scheme: https
            permanent: true
        ```

6. Create the ingress resource. Add file called ``resume-parser-ingress.yaml`` and apply it

        ```yaml
        apiVersion: networking.k8s.io/v1
        kind: Ingress
        metadata:
        name: resume-parser-tls-ingress
        namespace: resume
        annotations:
            kubernetes.io/ingress.class: traefik
            cert-manager.io/cluster-issuer: letsencrypt-staging
            traefik.ingress.kubernetes.io/router.middlewares: resume-redirect-https@kubernetescrd
        spec:
        rules:
            - host: myresume.datascienceengineer.com
            http:
                paths:
                - path: /
                    pathType: Prefix
                    backend:
                    service:
                        # this is the service which interfaces the application
                        # it is a simple ClusterIP service, redirecting port 
                        # 8000 to 8000
                        name: resume-parser-cluster-ip-service
                        port:
                        number: 8000
        tls:
            - secretName: resume-parser-tls
            hosts:
                - myresume.datascienceengineer.com
        ```

        > **Note:** The first part of the ``traefik.ingress.kubernetes.io/router.middlewares`` is the namespace. Our namespace is ``resume``.
7. In your DNS configuration console, make sure to have ``myresume.datascienceengineer.com`` pointed to the external IP-Address of the ingress. You can check the external IP by running ``kubectl get ingress -n resume``

        ```shell
        NAME                        CLASS    HOSTS                                  ADDRESS         PORTS     AGE
        resume-parser-tls-ingress   <none>   myresume.datascienceengineer.com   164.68.x.x   80, 443   2m7s
        ```

That's actually all the magic. Your ingress is now deployed and your application is served on ``myresume.datascienceengineer.com``. Keep in mind, that a modern browser will still tell you, that your route is insecure, because we used a staging issuer - instead of a production one.

## Change issuer to Let's Encrypt Production

Please note, that changing the issuer to the Let's Encrypt production issuer will make browser rate your route as secure. However you only have 5 certificate renewals per week - so make sure to test everything with the staging issuer and only afterwards change to production.

1. Add a file ``production-issuer.yaml`` and apply it

        ```yaml
        apiVersion: cert-manager.io/v1
        kind: ClusterIssuer
        metadata:
        name: letsencrypt-prod
        namespace: resume
        spec:
        acme:
            email: andreas.nigg@devopsandmore.com
            server: https://acme-v02.api.letsencrypt.org/directory
            privateKeySecretRef:
            name: letsencrypt-prod
            solvers:
            - http01:
                ingress:
                    class: traefik
        ```

2. Delete the automatically generated staging certificate: ``kubectl delete secrets resume-parser-tls -n resume``
3. In your Ingress Definition file, change the cluster-issuer configuration to ``letsencrypt-prod`` and apply the file

        ```yaml{9}
        apiVersion: networking.k8s.io/v1
        kind: Ingress
        metadata:
        name: resume-parser-tls-ingress
        namespace: resume
        annotations:
            kubernetes.io/ingress.class: traefik
            cert-manager.io/cluster-issuer: letsencrypt-prod
            traefik.ingress.kubernetes.io/router.middlewares: resume-redirect-https@kubernetescrd
        spec:
        ```

## The Traefik Dashboard

Traefik comes with a quite handy dashboard. To reach the dashboard, we forward the remote port to our local port by running:

    ```shell
    kubectl port-forward -n kube-system "$(kubectl get pods -n kube-system | grep '^traefik-' | awk '{print $1}')" 9000:9000
    ```

Now you can access the dashboard with your browser: ``http://localhost:9000/dashboard/``
> **IMPORTANT:** Note the trailing slash in the URL. This is required, otherwise you'll get an error 404!

The dashboard than provides some overview about your Traefik Resources.

![Traefik Dashboard](/blog/pictures/k3straefikBlogPost/traefik_dashboard.png "Traefik Dashboard")

## Summary - TLDR

In this guide we saw how one can serve workloads running in their k3s kubernetes cluster by using the pre-installed Traefik controller and cert-manager. The following steps are necessary:

1. Install cert-manager by running ``kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.9.1/cert-manager.yaml``
2. Create a new file ``production-issuer.yaml``

        ```yaml
        apiVersion: cert-manager.io/v1
        kind: ClusterIssuer
        metadata:
        name: letsencrypt-prod
        namespace: resume
        spec:
        acme:
            email: andreas.nigg@devopsandmore.com
            server: https://acme-v02.api.letsencrypt.org/directory
            privateKeySecretRef:
            name: letsencrypt-prod
            solvers:
            - http01:
                ingress:
                    class: traefik
        ```

3. Add a http to https redirect middleware:

        ```yaml
        apiVersion: traefik.containo.us/v1alpha1
        kind: Middleware
        metadata:
        name: redirect-https
        namespace: resume
        spec:
        redirectScheme:
            scheme: https
            permanent: true
        ```

4. Create the ingress resource:

        ```yaml
        apiVersion: networking.k8s.io/v1
        kind: Ingress
        metadata:
        name: resume-parser-tls-ingress
        namespace: resume
        annotations:
            kubernetes.io/ingress.class: traefik
            cert-manager.io/cluster-issuer: letsencrypt-prod
            traefik.ingress.kubernetes.io/router.middlewares: resume-redirect-https@kubernetescrd
        spec:
        rules:
            - host: myresume.datascienceengineer.com
            http:
                paths:
                - path: /
                    pathType: Prefix
                    backend:
                    service:
                        # this is the service which interfaces the application
                        # it is a simple ClusterIP service, redirecting port 
                        # 8000 to 8000
                        name: resume-parser-cluster-ip-service
                        port:
                        number: 8000
        tls:
            - secretName: resume-parser-tls
            hosts:
                - myresume.datascienceengineer.com
        ```


How to expose services in k3s - with automatic Let's Encrypt certs and certificate renewal

Were you ever in a situation when you needed a quickly accessible kubernetes cluster? Because, maybe, you are a Data Engineer wanting to quickly deploy an application to test the latest in dashboarding? Or you want to quickly run a machine learning pipeline?

While Kubernetes is an extremely powerful platform, it can be quite complex and challenging to get started with.
This is where k3s comes in. k3s is a light-weight Kubernetes distribution that is designed to be easy to install and operate. It is perfect for running Kubernetes on edge devices and small clusters. In this blog post, we will give you a quick introduction to k3s and show you how to get started with it.

- [Why k3s](#why-k3s)
- [General Architecture](#general-architecture)
- [Deployment Options](#deployment-options)
- [Installation of Single Node Deployment](#installation-of-single-node-deployment)
  - [Minimum requirements](#minimum-requirements)
  - [Installation options](#installation-options)
- [Access your cluster remotely](#access-your-cluster-remotely)
- [Conclusion / TLDR](#conclusion--tldr)

## Why k3s

K3s is one of the more lightweight kubernetes distributions out there, created by the well-known kubernetes experts at Rancer. As this guide will show, running kubernetes by using k3s is a real blast - and on top of the little complexity, k3s is CNCF-certified and marked as production-ready!
To summarize some of the more prominent arguments:

- k3s is CNCF-certified
- k3s is a CNCF Sandbox project (the first k8s distribution to get this state - by the way)
- Several million downloads
- 21k github stars
- Designed for production workloads (according to rancher)
- No external dependencies - it can run on any Linux box
- You might use storage backends (like SQLITE) other than etcd

## General Architecture

k3s comprises two architectural parts - the server and the agent. The server process runs the kubernetes API, controller, scheduler and the storage backend (sqlite by default).

The agent runs the kubelet, flannel, containerd, load balancers, network policy controllers and the kube-proxy.

The server furthermore uses a handy reverse tunnel proxy - allowing for one-way communication between k3s server and k3s agent. This helps to reduce network complexity as you do not need to open firewall ports on the agent-side!

![k3s agent and server overview](/blog/pictures/k3sIntroBlogPost/k3sagent_server.png "k3s agent and server overview (source: https://www.suse.com/c/rancher_blog/introduction-to-k3s)")

## Deployment Options

k3s comes with 4 different deployment options:

1. **Single node:** With this method, k3s get's installed a single server with the agent process and the default storage - SQLITE - embedded. They highly optimized this method for edge devices as well as quick installations of development clusters, short-lived Data Science clusters or CI integration clusters.

    ![k3s single node deployment](/blog/pictures/k3sIntroBlogPost/singlenode.png "k3s single node deployment")

2. **Single server, multiple agents:** Here we still only have one server, but one might add multiple agents - which are nothing less than workers - to a cluster. The agents connect to the server using a token issued by the server during startup. This method is still very lightweight and suitable for edge setups, a little more advanced Data Science workloads and CI integrations.

    ![k3s single server multiple agents deployment](/blog/pictures/k3sIntroBlogPost/singleserver_multiagents1.png "k3s single server multiple agents deployment")

3. **High availability setup with external storage backend:** This setup now is a truly high-availability set-up - having multiple servers and multiple agents - any one or multiple of them can fail and the cluster is still operating. This setup uses an external database like PostgreSQL for storage. Why not SQLITE anymore? Because of the multiple servers, we need a server to manage concurrent access.

    The big advantage of this method compared to the next one is, that the leader election/HA setup is not based on a quorum-based algorithm. So we can have any number of nodes!

    ![k3s high available multi-server deployment with external database](/blog/pictures/k3sIntroBlogPost/multiple_servers_external_db.png "k3s high available multi-server deployment with external database")

4. **High availability setup with embedded storage:** This installation again utilizes multiple servers in a HA setup - however having etcd as embedded storage as part of each server. The enormous advantage: This is really easy to set up and provides high availability. The big disadvantage? The leader election is quorum-based, so we need 3 servers to maintain quorum.

    Because of the simple installation process and the very reduced complexity, this setup is best suited for more elaborate edge installations with multiple server nodes.

    ![k3s high available multi-server deployment with embedded storage](/blog/pictures/k3sIntroBlogPost/multiple_servers_embedded_db.png "k3s high available multi-server deployment with embedded storage")

## Installation of Single Node Deployment

As the premise of this guide is to set up a cluster for quick Data Analytics tasks or development workflows, let's evaluate, how to quickly install k3s in the single-node configuration on one of our servers.

### Minimum requirements

The requirements for installing k3s are rather low:

- If you are using Raspbian Buster, follow [these](https://rancher.com/docs/k3s/latest/en/advanced/#enabling-legacy-iptables-on-raspbian-buster) steps to switch to legacy iptables.
- If you are using Alpine Linux, follow [these](https://rancher.com/docs/k3s/latest/en/advanced/#additional-preparation-for-alpine-linux-setup) steps for additional setup.
- If you are using (Red Hat/CentOS) Enterprise Linux, follow [these](https://rancher.com/docs/k3s/latest/en/advanced/#additional-preparation-for-red-hat-centos-enterprise-linux) steps for additional setup.
- **Hardware:**
  - RAM: 512MB
  - CPU: Pentium 4, 1.5GHz...

### Installation options

The easiest method to bootstrap a single-node k3s installation is to use the RANCHER-maintained install-script - found [at the k3s weg page](https://get.k3s.io). This script can be used to install k3s on systemd or openrc based systems. For other options, please visit: [https://rancher.com/docs/k3s/latest/en/installation/](https://rancher.com/docs/k3s/latest/en/installation/)

> **IMPORTANT:** As always when running scripts from the internet - make sure to vet the script before running.

On your server where you want to run k3s, run

```shell
curl -sfL https://get.k3s.io | sh -
```

Surprisingly - that's it. k3s is already installed and

- configured to automatically restart after node reboots
- with kubectl, crictl, ctr, k3s-killall.sh and k3s-uninstall.sh utilities installed
- with a kubeconfig file written to ``/etc/rancher/k3s/k3s.yaml``

To connect to the cluster, we need to copy the k3s.yaml kubernetes context information to our kubeconfig file.

If you don't have a file ``$HOME/.kube/config``, simply copy the rancher-file, set permissions right and rename it accordingly:

```shell
mkdir ~/.kube
sudo cp /etc/rancher/k3s/k3s.yaml $HOME/.kube/config
sudo chown $USER $HOME/.kube/config
sudo chmod 600 $HOME/.kube/config
export KUBECONFIG=$HOME/.kube/config # This tells kubectl which config-file to use
```

Also add the line ``export KUBECONFIG=$HOME/.kube/config`` to your ``~/.bashrc`` or ``~/.zshrc`` file to have it persisted across session restarts.

**Alternative:** If you already have a kubectl context configuration file (``$HOME/.kube/config``), you need to merge the two config files. This can be done by some bash magic:

First, create a copy of the ``k3s.yaml`` - file in your KUBECONFIG-folder

```shell
sudo cp /etc/rancher/k3s/k3s.yaml $HOME/.kube/config_k3s
sudo chown $USER $HOME/.kube/config_k3s
```

Then, open the file and change the highlighted lines below:

```yaml{6,9,10,11,12,16}
apiVersion: v1
clusters:
- cluster:
    certificate-authority-data: <secret>
    server: https://127.0.0.1:6443
  name: k3s_cluster
contexts:
- context:
    cluster: k3s_cluster
    user: k3s_user
  name: k3s_context
current-context: k3s_context
kind: Config
preferences: {}
users:
- name: k3s_user
  user:
    client-certificate-data:  <secret>
    client-key-data:  <secret>
```

Finally, merge the two files into the final ``$HOME/.kube/config``

```shell
# Make a copy of your existing config
cp $HOME/.kube/config $HOME/.kube/config.bak

# Merge the two config files together into a new config file
KUBECONFIG=$HOME/.kube/config:$HOME/.kube/config_k3s kubectl config view --flatten > /tmp/config

# Replace your old config with the new merged config
mv /tmp/config $HOME/.kube/config

# (optional) Delete the backup once you confirm everything worked ok
rm $HOME/.kube/config.bak 

# Set the new k3s_context as your current context
kubectl config use-context k3s_context

# Verify that the context is installed
kubectl config get-contexts
```

The output should look like:

```shell
CURRENT   NAME          CLUSTER       AUTHINFO   NAMESPACE
*         default       default       default
          k3s_context   k3s_cluster   k3s_user
```

> **Congratulations:** You are ready to access the cluster

To access the cluster, use kubectl:

```shell
kubectl get pods --all-namespaces

# result
NAMESPACE     NAME                                      READY   STATUS      RESTARTS   AGE
kube-system   local-path-provisioner-7b7dc8d6f5-jzkdx   1/1     Running     0          81m
kube-system   coredns-b96499967-9rwwm                   1/1     Running     0          81m
kube-system   helm-install-traefik-crd-hczz4            0/1     Completed   0          81m
kube-system   metrics-server-668d979685-dx6c6           1/1     Running     0          81m
kube-system   helm-install-traefik-46tgr                0/1     Completed   2          81m
kube-system   svclb-traefik-aa7136cb-td8kt              2/2     Running     0          80m
kube-system   traefik-7cd4fcff68-w8hdr                  1/1     Running     0          80m
```

## Access your cluster remotely

Up until know we only accessed our cluster from the server where it was installed on. To connect the cluster remotely, simply create a kube/config - file on your local machine - similar as you did on the server.

1. Create your local ``$HOME/.kube/config`` file - with the same strategy as outlined above and as you did on the server. Simply copy the ``/etc/rancher/k3s/k3s.yaml`` file from your server to your local machine.
2. Open the ``$HOME/.kube/config`` file and change the server address from 127.0.0.1 to your remote cluster IP-Address (or DNS).

    ```yaml{5}
    apiVersion: v1
    clusters:
    - cluster:
        certificate-authority-data: <secret>
        server: https://<your-server-ip-or-dns>:6443
    name: k3s_cluster
    ...
    ```

3. Run

   ```shell
   kubectl config use k3s_context
   ```

4. Now you can use kubectl on your local machine to remotely connect to your cluster

## Conclusion / TLDR

We have seen that we can install k3s with some simple steps. This allows us to bootstrap a simple kubernetes cluster for various workloads:

- Running adhoc machine learning pipelines
- Testing out this new shiny tool which comes easily bundled as kubernetes application
- Deploying kubernetes on a small edge network
- Run kubernetes on our local developer machines for testing purposes - without having to worry about virtual machines

The steps to install and run k3s are.

```shell
# installs and runs k3s
curl -sfL https://get.k3s.io | sh -

# Creates a kubec-config context to connect to the cluster
# Use the alternative-method described above, if you already have such a context-file
mkdir ~/.kube
sudo cp /etc/rancher/k3s/k3s.yaml $HOME/.kube/config
sudo chown $USER $HOME/.kube/config
sudo chmod 600 $HOME/.kube/config
export KUBECONFIG=$HOME/.kube/config # This tells kubectl which config-file to use
kubectl get pods --all-namespaces
```


k3s - Kubernetes in 10 minutes - An Introduction

Collecting and analyzing data was promised to be one of the most important and most profitable areas of business in the last decade. However, it’s a fact that most businesses - especially small and medium companies - fail to either properly access their data or cannot provide the right data and analytics to the right people in time.

While this situation is frustrating, it’s a risk for the entire business. Properly using business data and basing decision on these data can be a tremendous advantage in the marketplace for any company who successfully incorporates data in their daily business.

So, why do most companies struggle with profiting from data?

- [1. They don't know how to use their data and they've failed to identify the value of their data](#1-they-dont-know-how-to-use-their-data-and-theyve-failed-to-identify-the-value-of-their-data)
  - [So, how do you derive value from your data?](#so-how-do-you-derive-value-from-your-data)
    - [Summary](#summary)
- [2. They've invested enormous amounts of money in data infrastructure, IoT systems and modern databases just to end up with tons of unused data scattered across data silos](#2-theyve-invested-enormous-amounts-of-money-in-data-infrastructure-iot-systems-and-modern-databases-just-to-end-up-with-tons-of-unused-data-scattered-across-data-silos)
- [3. They don't base their decision on data, due to unusable or not available data end-products](#3-they-dont-base-their-decision-on-data-due-to-unusable-or-not-available-data-end-products)
- [You can and will profit from data](#you-can-and-will-profit-from-data)

## 1. They don't know how to use their data and they've failed to identify the value of their data

As a business, you continually strive for increasing revenue and customer satisfaction, decrease churn and find ways to overall increase your businesses health. In a best-case scenario, you would already know in advance what actions you can take to generate the desired outcome. Or at least to know quickly, whether your actions are driving you in the right direction. Using your business-data is the best known way of exactly having this - a rather objective way of measuring your success or lack thereof.

However, because of either some unpleasant experiences with failed investments in data or by not having access to the right data, a lot of companies conclude, that data are not important for them. What a tremendous pity, as using data as an integral part of your business strategy has shown time and time again to be highly successful, if done right.

### So, how do you derive value from your data?

Let’s get the elephant in the room out of our ways: Data in itself don’t provide any value - only using them to enhance your business does. First, you want to decide on a limited and clearly described set of business factors or metrics you want to improve. The more specific you can describe your metric, the better.
Examples can be, you want to "increase number of sales started via your homepage", "decrease churn of your subscription contracts" or "decrease the number of problems on my product". These are all rather specific metrics or questions which would all result in better health for your business. Try to avoid rather generic and very open-ended questions like "increase revenue" - this is just too wide of a question to be answered. You can however transform these generic questions into good ones by adding more details to them - like "increase revenue by increasing number of weekly subscribers to your product" or "find ways which were historically successful in driving revenue".

Then you want to establish a baseline - the current situation - for these questions. To measure increased sales started via your homepage, you first need to know the current sales started via your homepage.

This step is often overlooked - resulting in having no objective way of knowing whether your actions are successful.

For creating this highly valuable baseline, look for data sources within your company which might provide the foundation for it. Work closely with your departments or industry experts to know where data are available and how you can integrate them. Don’t get too complicated here: Most of the time the most basic data sources are all you need - website tracking data, email sign-ups or basic IoT data like alarms and operations data.

As a nice side-effect of creating the baseline: The data sources needed to create the baseline are often required to solve your key question - two birds with one stone.

Assuming you integrated the data (more on that in the next chapter) and you created the baseline, use your data to look for a limited set of factors which influence (positively or negatively) your question or metric. This requires some Data-Science background and manually working with data. Don’t expect an automatic solution to do its wonders here - this step involves _some_ amount of labour. However, also here, don’t overcomplicate things. More often than not, the answer is rather easy to find. A set of 3 to 5 factors/parameters is enough to sufficiently describe your problem. This step most of the time also involves some experimentation. Create a hypothesis, implement it and test how this influences your factors and key metric.  Finally, after finding what influences your key metrics or questions, work closely with your departments or industry experts to derive actions which influence these influencing parameters. These actions are obviously based on the experiments already conducted in step 3.

As an example, I was working with a client lately who already knew rather well, what he wanted: Generating more inbound sales via his homepage and blog rather than only via his sales force. He already instrumented his homepage, meaning he had all the tracking data we would need. To now increase inbound sales, we followed the above formula:

1. Define the question to answer (already done)

1. Create a baseline of how many sales are already done via the homepage: For creating the baseline, we first established a specific definition of "inbound sale" - when does a sale count as inbound sale? We defined it as a sale counts as inbound sale, if a potential client presses the "Contact Sales" button on the homepage and this client finally get’s signs a contract. Clients which signed up for a Newsletter and then were converted did not count - we wanted to specifically go for the shortest potential sale.

As the client already perfectly tracked his homepage, all we had to do was to check the historic data for how many clients clicked on the "Contact Sales" button and converted. Easy enough.

1. Derive influencing factors to improve the baseline: For completing this step, we again used the website tracking data and did some old-school data exploration. Manually and semi-automatically looking for patterns in the datasets related to users who clicked or did not click the "Contact Sales" button. We also analyzed, which of these users finally converted. Interestingly, we found, that users who contacted sales are already very engaged on the website. They read through a very high number of product information pages before clicking "Contact Sales" - however the overall conversion rate still was very low. After talking with the sales department, we could quickly plan a hypothesis: When people were contacting sales, they most of the time only wanted to have more information, rather than already buying the product. Combined with our data-based knowledge, that people are reading a lot of information on our website, we concluded, that the homepage simply does not provide good enough informational material. We designed an experiment, where we served some users more specific information about the product on fewer pages. To our delight, already this first experiment proved successful - not only were we able to convert more people, we also could increase the number of users contacting the sales team as compared to the control group. My guess is, that the clients need to navigate fewer pages to get more information. This leads to fewer people leaving the homepage without contacting sales at all - but this hypothesis is its own experiment ;-)

1. Create specific actions to improve the influencing factors: After knowing, that one of the major problems of the client’s webpage was too little information, scattered across to many sub-pages, the action was rather easy to define. First, we created some smaller experiments, tracking which specific information we need to convey - and then reworked the homepage accordingly.

Many companies struggle to find value in data because they don’t use a straight-forward and easy enough route to data analytics. As the above process describes, using data for increasing your metrics does not require complicated processes and systems. Just a well-executed, educated plan and some curiosity for data you already have.

#### Summary

> 1. Define the question to answer
> 2. Create a baseline
> 3. Collect influencing factors to improve the baseline
> 4. Create specific actions to improve the influencing factors

## 2. They've invested enormous amounts of money in data infrastructure, IoT systems and modern databases just to end up with tons of unused data scattered across data silos

It’s important to not only collect data but also use them. One of the biggest hurdles in using data is the overabundance at which data is available. Any modern system provides data, any product provides interfaces to collect data. IoT systems promise us Golden Eras by using IoT data. Modern databases allow to implement any data use case in no time - according to their marketing. Literature and media is overwhelming us with positive examples of how data changed some companies life.

Taking all this praise into account it’s no wonder, that many companies were excited and invested heavily in systems providing data, in data infrastructure, IoT, etc. etc. However, the excitement often ended rather quickly. When one realized, that all these data systems need someone to work with. These systems have their unique data formats and APIs, most of the time you need multiple systems to answer your questions and finally you also have to think about which data you want to store for how long.

To not suffer from these problems, you want to build upon a solid data strategy. It’s fine if you already used some of your data sources, it’s easy enough to integrate your ongoing efforts with a bigger strategic effort.

A data strategy incorporates foremost a system for documenting your data sources. This not only is relevant for GDPR compliance but also to allow your data scientists to easily find and work with any data.

It’s advisable to integrate your most valuable data into a centralized system which is the main entry point for your data analytics endeavors - think data warehouse or data lake. Please don’t be repulsed by the "big" words data warehouse or data lake. Using state-of-the-art tooling and good architectures, it’s easy enough to set up infrastructure which requires very little man-power to run and operate.

Using modern workflow orchestration tools like prefect.io you can and want to fully automate integrating your data sources and creating models based on your data analytics needs. Using cloud-based data-warehouse tools like Google BigQuery or Snowflake provide very cheap data storage for huge amounts of data with at the same time unbelievable scalability if you need to analyze lots of data.

You also want to have possibilities to test your data - tools like "good expectations" allow to define how you expect your data. Having this system in place will increase your data quality tremendously.

One of my recent clients had invested in a modern IoT system for analyzing his products. This went well for the initial months, as he could find some potential for improvements on his products. However, after some time the business leaders got accustomed to using data and their line of questioning got more elaborate. Soon enough, the IoT data itself were not enough anymore. Questions arose like "how may we increase the upsell volume for a specific segment of clients". One can’t answer this question by using an IoT system alone, as client segments were done in a CRM system. You also want to have additional customer-related data only available in the CRM systems. And finally, we wanted to have specific product information which in this clients case were part of their ERP system.

The straightforward way now might seem to simply integrate the 3 data sources (IoT, ERP, CRM system) with your analytics solution. This is exactly what the client first intended, however he come across several hurdles:

- Integrating the APIs in itself is rather complex
- The integration itself is not scalable - you need to integrate starting from zero for your next analytics
- Performance of the analytics was slow
- Let's not talk about data security…

Based on the very good analytics question from Management, we went for a small data warehouse. We could see that this not only helps to answer this question, but many future ones as well. So we set up the excellent workflow orchestration tool prefect.io, we used airbyte.io for integrating the data sources and Snowflake on Microsoft Azure to store the data. For modelling our data we used another excellent tool: dbt.

As the bespoke systems provide excellent documentation and awesome developer experiences, the whole warehouse was running within less than a month - being a scalable and modern foundation for any present and future data analytics.
By using the infrastructure we now had, it was easy to combine the data from these various data sources. From a data science perspective, we only had to deal with one system - our warehouse database.
On top of this system, we early on invested in data quality, by using the tool "Good expectations" to define how our data should look like. This investment proofed highly valuable as we caught several problems with our data like inconsistent sampling rates of the IoT system or missing data entries in the CRM system.

Having this centralized repository of data allows you to work with data in a totally new way. It provides ONE interface which your data scientists can. It ensures, the quality of your data is high. It allows to combine multiple data sources without any additional integration hassle - analyzing data from a single system is oftentimes not enough to answer your complex business needs.

> Having this centralized repository of data allows you to work with data in a totally new way

## 3. They don't base their decision on data, due to unusable or not available data end-products

Basing decisions on data rather than gut-feeling is from utmost importance - however many decision makers cannot do so. One of the main reasons - if not the most common one - is, that the data they receive is simply not useful for decision making.

Data Scientists are amazingly talented in creating detailed answers from data - but they MUST NOT stop there. The final and most important step is to make data usable by the end customer. This requires product-thinking and identification of stakeholders. The result of a data analytics project needs to be easy to understand, easy to use and must follow common UX practices. Do not overload your target group. Know exactly how accustomed your customer is with data and only present the data which are really relevant.

Also, the look and feel of the data analytics result needs to be from high quality. Using nice color schemes, modern data visualizing and reporting is not optional. Make your end users love your analytics, not only your numbers!

I had a client already some time ago who was well ahead of the curve with data usage. They had a great data team, nice, centralized infrastructure and innovative top-level management who quickly knew, how to ask questions to be answered by data.

However, they continually struggled to spread the fire of excitement to middle management and individual departments. Quickly analyzing the situation we came with the obvious reason: The data products delivered to these departments were simply boring. Nothing more and nothing less. The necessary (and by the way amazing) information was there. But they delivered too much information with too little entertainment. Often as pdf files on Monday morning (you like getting a 10 page pdf on Monday morning via E-Mail, right?). The visualizations were plain non-styled 3d.js charts - visually not appealing.

We changed the delivery of data as follows:

- We did a product-like survey to identify the information which was REALLY needed
- We introduced a modern dashboarding tool (which then was loved not only by the end customers, but also data scientists)
- We had dashboards with as little as 3 metrics on them. Including a description about what the metrics were about
- We did 2-weekly check-ins with the departments to identify pain points with data and changed the representation accordingly. For some departments, we established "data champions" which were in constant conversation with the data team.
- We did not actively deliver metrics anymore. With having this clear and visually appealing dashboards, people started to proactively use dashboards. (Nice side-effect: Less infrastructure for automating reports!)

Only 8 weeks after introducing this new data representation we could see a 450% (!!) increase in dashboard usage, compared to the open rate of the e-mail based reports. [Note that measuring open-rates is fuzzy. Despite that, as the number of increased engagement is so huge, we can neglect the inaccuracy in mail open rate measurements].

Treat your data - and especially the outcome of your data analytics - similar to your products. You have internal (and maybe external) clients with their needs and knowledge. Targeting these data products to the needs of your data-clients will tremendously increase acceptance - and therefore efficiency - of the data throughout your entire organization.

> Only 8 weeks after introducing this new data representation we could see a 450% (!!) increase in dashboard usage

## You can and will profit from data

   Data are one of your most valuable assets - this should be the norm, but is not - for many. While you can still be successful without using data to your advantage, you will have a harder and harder time on the markets, as your competition will use objective, data-based decisions to appeal to your clients and market share.

It’s time to change how you work with data, how you deliver data and how you make decisions. As an expert data concierge, I can help. My data science engineer product line is tailored to small and medium-sized businesses, looking for finally profiting from data. Early on I will help you improve your business health, revenue, etc. by focusing on a data-based, objective approach of data engineering and analytics.

Data are no longer a liability - but the foundation for your profit generation.

> Leave me a message how you are using data and what obstacles you encounter in your daily data business. How do you integrate your various data sources? How do you deliver your data products to your end customers - like top management? How do you handle data quality issues? When was the last time your data or data products annoyed you?


Knowledge Base

Articles and Projects

Contact Me