Fivetran and Pinecone Assistant make starting with RAG easy

The simplest way to build a chatbot combining Pinecone Assistant with data from your data lake.

Many enterprises are using RAG architectures as an entry point into generative AI. To leverage generative AI with your proprietary data, you must securely combine your data with foundation models trained on public datasets. Foundation models access this data for additional context through vector databases — secure repositories designed to store and manage data in a format that's ready for LLMs. 

However, integrating data upstream of vector databases presents a significant engineering challenge. As generative AI use cases become more complex, data integration solutions need to handle the ingestion of unstructured, semi-structured and structured data at scale.

Fivetran and Pinecone provide a seamless solution to this challenge. With Fivetran’s ability to sync data effortlessly and Pinecone’s Assistant for storing and interacting with that data, you can unlock powerful insights without extensive setup. This approach minimizes the technical overhead usually required for AI-driven data insights.

In a previous post, we discussed how RAG architecture works. Here, we’ll demonstrate how to build an end-to-end RAG architecture using Fivetran's Managed Data Lake Service and Pinecone Assistant, which uses a vector database in its back end.

The flow in a nutshell

Many organizations leverage RAG to glean insights from unstructured data that previously would have been very challenging to access. Organisations who want to better serve their customers may consult text-rich sources like Zendesk and Salesforce to answer questions such as "What was the top feature request for my product in the last month?"

Here's how Fivetran + Pinecone make it possible to interact with your proprietary data sources using natural language. The process looks like the following:

  1. Set up and leverage Fivetran connectors to sync data to your data lake.
  2. Transform the data in a RAG-ready format.
  3. Create the RAG application using Pinecone Assistant.
  4. Load the files into Pinecone Assistant, which automatically splits, embeds and stores the contents into a vector database.

Now, you’re ready to engage with your data in an entirely new way.

1. Set up Fivetran connectors to data lake; extract and load data

First, identify the relevant tables and fields in your CRMs (like Salesforce), ticketing systems (like Zendesk), communication platforms (like Slack) or project management tools (like GitHub).

From the Fivetran dashboard, setting up connectors involves just a few menu selections:

  1. Go to the "Connectors" tab and click "Add connector" in the upper right corner.
  2. Select your destination.
  3. Choose the connector you need.

Then, set up a schedule and begin syncing. Consult our demo or our documentation for additional guidance.

Now data is landing reliably in your data lake, creating the data foundation for your analytics and AI use cases. 

2. Transform the data into a RAG-ready formats

The next step is to extract the text-rich fields from your sources into a raw text file, either PDF or TXT, that can be uploaded into the Pinecone Assistant application. 

A detailed tutorial for extracting text-rich data from tables is beyond the scope of this discussion, but in broad outlines, the process consists of two steps:

  1. Using SQL, concatenate text-rich fields together in a new table.
  2. Export the table as a file. You may use a scripting language like Python; libraries such as PySpark and Pandas include functions to export open table formats to text).

For a detailed example, check out this blog or our webinars. There are also tutorials using PySpark and Pandas on sites like Stack Overflow.

[CTA_MODULE]

3. Create the RAG application using the Pinecone Assistant

To set up your first Assistant, go to the Pinecone console at https://app.pinecone.io/. Once logged in, click “Assistants” on the left side navigation menu. 

Click the “Create an assistant” button to begin. Once you’ve named your Assistant it is ready to accept files. 

4. Load the files into Pinecone Assistant

There are two ways to add files to your Assistant: through the GUI in the Pinecone console, or via an API. Both work equally well, though if you are setting up a regular import job we recommend going with the API. 

Add files via the GUI

The Pinecone console has a convenient drag-and-drop interface for adding up to 10 files at a time to your Assistant. Simply drag them from a folder on your local machine or laptop, drop them in the field and Assistant will take care of the rest. 

Add files via API

The API is available via the Pinecone Python SDK, or via a REST API using ‘curl’ or another command line utility. Make sure you have the latest version of the SDK in place by running pip -U pinecone-client. Using the SDK is as simple as instantiating a connection to your Assistant and providing the path to the file to be uploaded. 


# To use the Python SDK, install the plugin:
# pip install duckdb pyiceberg pinecone pinecone-plugin-assistant

import os
import duckdb
import pandas as pd
from pinecone import Pinecone

# Step 1: Use DuckDB with Iceberg integration to fetch data.
conn = duckdb.connect(':memory:')
conn.execute('INSTALL iceberg;')
conn.execute('INSTALL httpfs')
conn.execute('LOAD iceberg')
conn.execute('LOAD httpfs')

query = "SELECT id, content FROM FROM iceberg_scan('s3://path/to/metadata/12345.metadata.json', allow_moved_paths = true)"
file_paths = []
for id, content in conn.execute(query).fetchall():
	file_path = f"{id}.txt"
	with open(file_path, 'w') as file:
		file.write(str(content))
		file_paths.append(file_name)

# Step 2: Upload each of these files to the Pinecone assistant.
pc = Pinecone(api_key="")

# Get a Knowledge Assistant.
assistant = pc.assistant.Assistant(assistant_name="example-assistant")

# Upload each file extracted from .
for file_path in file_paths:
	response = assistant.upload_file(
		file_path=file_path,
		timeout=None
)

print(f"Uploaded {file_path}: {response}")
Keep in mind that to access this testing window you need to be a member of the Pinecone Organization and Project that hosts the Assistant. If stakeholders aren’t also members of your Project you will use the chat_completion_assistant endpoint in your application. 

To automate the process of uploading files to the Pinecone Assistant, you can use GitHub actions or Buildkite to trigger the upload script on schedule. For best results, schedule these jobs to match Fivetran connector sync schedules.

With the Python script above as load_assistant.py, you could use a script like the following. Note that the schedule is written in POSIX cron syntax. Here, it is set to execute every sixth hour of the day:


name: Run load_assistant.py

on:
  schedule:
    - cron: '0 */6 * * *'

jobs:
  run_script:
    runs-on: ubuntu-latest

    steps:
    - name: Check out repository
      uses: actions/checkout@v2

    - name: Set up Python
      uses: actions/setup-python@v2
      with:
        python-version: '3.12'  # Using Python 3.12

    - name: Load data from datalake to pinecone assistant
      run: python load_assistant.py

Under Assistants, select the one you just created. This will open a chat interface where you can ask questions of the Assistant. Verify that your new chatbot is able to answer questions only your own data can address.

Your Assistant is now able to answer critical questions about your data. If you integrate Zendesk data, you’ll be able to understand which features raise the most support issues. If it was Salesforce, you can determine which features are in greatest demand. You should now have access to a wealth of insights that would otherwise be inaccessible or require painstaking manual effort to assemble.

This is the fastest way to set up RAG

Pinecone Assistant provides an intuitive way to augment AI with your data, supporting easy document uploads through either a drag-and-drop interface or an API. This approach is ideal for smaller datasets and quick deployments, where simplicity is key. It also enables you to quickly and cheaply prototype new AI initiatives before making further investments, i.e. productionizing a project. For larger or frequently changing datasets, leveraging the inference API or external models to generate embeddings and managing them in a Pinecone index might be more efficient. This approach allows for more flexibility, though it’s suited for users with more technical expertise.

To get started with your first AI use case using RAG, try Fivetran’s 14-day free trial and Pinecone’s starter plan.

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Data insights
Data insights

Fivetran and Pinecone Assistant make starting with RAG easy

Fivetran and Pinecone Assistant make starting with RAG easy

October 23, 2024
October 23, 2024
Fivetran and Pinecone Assistant make starting with RAG easy
The simplest way to build a chatbot combining Pinecone Assistant with data from your data lake.

Many enterprises are using RAG architectures as an entry point into generative AI. To leverage generative AI with your proprietary data, you must securely combine your data with foundation models trained on public datasets. Foundation models access this data for additional context through vector databases — secure repositories designed to store and manage data in a format that's ready for LLMs. 

However, integrating data upstream of vector databases presents a significant engineering challenge. As generative AI use cases become more complex, data integration solutions need to handle the ingestion of unstructured, semi-structured and structured data at scale.

Fivetran and Pinecone provide a seamless solution to this challenge. With Fivetran’s ability to sync data effortlessly and Pinecone’s Assistant for storing and interacting with that data, you can unlock powerful insights without extensive setup. This approach minimizes the technical overhead usually required for AI-driven data insights.

In a previous post, we discussed how RAG architecture works. Here, we’ll demonstrate how to build an end-to-end RAG architecture using Fivetran's Managed Data Lake Service and Pinecone Assistant, which uses a vector database in its back end.

The flow in a nutshell

Many organizations leverage RAG to glean insights from unstructured data that previously would have been very challenging to access. Organisations who want to better serve their customers may consult text-rich sources like Zendesk and Salesforce to answer questions such as "What was the top feature request for my product in the last month?"

Here's how Fivetran + Pinecone make it possible to interact with your proprietary data sources using natural language. The process looks like the following:

  1. Set up and leverage Fivetran connectors to sync data to your data lake.
  2. Transform the data in a RAG-ready format.
  3. Create the RAG application using Pinecone Assistant.
  4. Load the files into Pinecone Assistant, which automatically splits, embeds and stores the contents into a vector database.

Now, you’re ready to engage with your data in an entirely new way.

1. Set up Fivetran connectors to data lake; extract and load data

First, identify the relevant tables and fields in your CRMs (like Salesforce), ticketing systems (like Zendesk), communication platforms (like Slack) or project management tools (like GitHub).

From the Fivetran dashboard, setting up connectors involves just a few menu selections:

  1. Go to the "Connectors" tab and click "Add connector" in the upper right corner.
  2. Select your destination.
  3. Choose the connector you need.

Then, set up a schedule and begin syncing. Consult our demo or our documentation for additional guidance.

Now data is landing reliably in your data lake, creating the data foundation for your analytics and AI use cases. 

2. Transform the data into a RAG-ready formats

The next step is to extract the text-rich fields from your sources into a raw text file, either PDF or TXT, that can be uploaded into the Pinecone Assistant application. 

A detailed tutorial for extracting text-rich data from tables is beyond the scope of this discussion, but in broad outlines, the process consists of two steps:

  1. Using SQL, concatenate text-rich fields together in a new table.
  2. Export the table as a file. You may use a scripting language like Python; libraries such as PySpark and Pandas include functions to export open table formats to text).

For a detailed example, check out this blog or our webinars. There are also tutorials using PySpark and Pandas on sites like Stack Overflow.

[CTA_MODULE]

3. Create the RAG application using the Pinecone Assistant

To set up your first Assistant, go to the Pinecone console at https://app.pinecone.io/. Once logged in, click “Assistants” on the left side navigation menu. 

Click the “Create an assistant” button to begin. Once you’ve named your Assistant it is ready to accept files. 

4. Load the files into Pinecone Assistant

There are two ways to add files to your Assistant: through the GUI in the Pinecone console, or via an API. Both work equally well, though if you are setting up a regular import job we recommend going with the API. 

Add files via the GUI

The Pinecone console has a convenient drag-and-drop interface for adding up to 10 files at a time to your Assistant. Simply drag them from a folder on your local machine or laptop, drop them in the field and Assistant will take care of the rest. 

Add files via API

The API is available via the Pinecone Python SDK, or via a REST API using ‘curl’ or another command line utility. Make sure you have the latest version of the SDK in place by running pip -U pinecone-client. Using the SDK is as simple as instantiating a connection to your Assistant and providing the path to the file to be uploaded. 


# To use the Python SDK, install the plugin:
# pip install duckdb pyiceberg pinecone pinecone-plugin-assistant

import os
import duckdb
import pandas as pd
from pinecone import Pinecone

# Step 1: Use DuckDB with Iceberg integration to fetch data.
conn = duckdb.connect(':memory:')
conn.execute('INSTALL iceberg;')
conn.execute('INSTALL httpfs')
conn.execute('LOAD iceberg')
conn.execute('LOAD httpfs')

query = "SELECT id, content FROM FROM iceberg_scan('s3://path/to/metadata/12345.metadata.json', allow_moved_paths = true)"
file_paths = []
for id, content in conn.execute(query).fetchall():
	file_path = f"{id}.txt"
	with open(file_path, 'w') as file:
		file.write(str(content))
		file_paths.append(file_name)

# Step 2: Upload each of these files to the Pinecone assistant.
pc = Pinecone(api_key="")

# Get a Knowledge Assistant.
assistant = pc.assistant.Assistant(assistant_name="example-assistant")

# Upload each file extracted from .
for file_path in file_paths:
	response = assistant.upload_file(
		file_path=file_path,
		timeout=None
)

print(f"Uploaded {file_path}: {response}")
Keep in mind that to access this testing window you need to be a member of the Pinecone Organization and Project that hosts the Assistant. If stakeholders aren’t also members of your Project you will use the chat_completion_assistant endpoint in your application. 

To automate the process of uploading files to the Pinecone Assistant, you can use GitHub actions or Buildkite to trigger the upload script on schedule. For best results, schedule these jobs to match Fivetran connector sync schedules.

With the Python script above as load_assistant.py, you could use a script like the following. Note that the schedule is written in POSIX cron syntax. Here, it is set to execute every sixth hour of the day:


name: Run load_assistant.py

on:
  schedule:
    - cron: '0 */6 * * *'

jobs:
  run_script:
    runs-on: ubuntu-latest

    steps:
    - name: Check out repository
      uses: actions/checkout@v2

    - name: Set up Python
      uses: actions/setup-python@v2
      with:
        python-version: '3.12'  # Using Python 3.12

    - name: Load data from datalake to pinecone assistant
      run: python load_assistant.py

Under Assistants, select the one you just created. This will open a chat interface where you can ask questions of the Assistant. Verify that your new chatbot is able to answer questions only your own data can address.

Your Assistant is now able to answer critical questions about your data. If you integrate Zendesk data, you’ll be able to understand which features raise the most support issues. If it was Salesforce, you can determine which features are in greatest demand. You should now have access to a wealth of insights that would otherwise be inaccessible or require painstaking manual effort to assemble.

This is the fastest way to set up RAG

Pinecone Assistant provides an intuitive way to augment AI with your data, supporting easy document uploads through either a drag-and-drop interface or an API. This approach is ideal for smaller datasets and quick deployments, where simplicity is key. It also enables you to quickly and cheaply prototype new AI initiatives before making further investments, i.e. productionizing a project. For larger or frequently changing datasets, leveraging the inference API or external models to generate embeddings and managing them in a Pinecone index might be more efficient. This approach allows for more flexibility, though it’s suited for users with more technical expertise.

To get started with your first AI use case using RAG, try Fivetran’s 14-day free trial and Pinecone’s starter plan.

Build your own RAG-based application in 30 minutes
Start now
Topics
No items found.
Share

Articles associés

Assemblage d’une architecture RAG à l’aide de Fivetran
Product

Assemblage d’une architecture RAG à l’aide de Fivetran

Lire l’article
Build your own RAG-based GenAI application in 30 minutes
Product

Build your own RAG-based GenAI application in 30 minutes

Lire l’article
Building a chatbot with Fivetran and LangChain
Data insights

Building a chatbot with Fivetran and LangChain

Lire l’article
No items found.
No items found.

Commencer gratuitement

Rejoignez les milliers d’entreprises qui utilisent Fivetran pour centraliser et transformer leur data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.