Query OpenAI Embeddings with MyScale Vector Database
OpenAI cookbook for using MyScale, a ClickHouse-based SQL+vector database, for nearest-neighbor search on OpenAI embeddings.
Why it matters
Leverage MyScale as a high-performance vector database for your OpenAI embeddings. This asset streamlines the process of storing, indexing, and searching vector data alongside structured information using SQL.
Outcomes
What it gets done
Store precomputed OpenAI embeddings in MyScale.
Index embeddings for efficient nearest neighbor search.
Generate embeddings for text queries using OpenAI API.
Perform vector searches within MyScale using SQL.
Install
Add it to your toolbox
Run in your project directory:
curl -fsSL https://spark.entire.vc/get/oai-gettingstartedwithmyscaleandopenai | bash Steps
Steps in the chain
Overview
Using MyScale as a vector database for OpenAI embeddings
OpenAI cookbook guide to using MyScale, a ClickHouse-based database combining SQL analytics with vector search, as a store for OpenAI embeddings, covering table/index creation and cosine-distance nearest-neighbor search. Use when you need a single database for both vector similarity search and SQL analytics over the same structured and unstructured data.
What it does
This OpenAI cookbook notebook is a step-by-step guide to using MyScale - a database built on ClickHouse that combines vector search with full SQL analytics - as a vector store for OpenAI embeddings. It walks through an end-to-end process: using precomputed OpenAI embeddings, storing them in a cloud MyScale instance, converting a raw text query into an embedding via the OpenAI API, and running a nearest-neighbor search over the stored collection. MyScale is positioned as a high-performance, fully managed option that supports joint queries and analyses across both structured and vector data with comprehensive SQL support, deployable and queryable via the MyScale Console within minutes. Setup requires a MyScale cluster (via the MyScale quickstart guide), the clickhouse-connect Python library, and an OpenAI API key for query vectorization, plus installing the openai and clickhouse-connect packages. After connecting to the cluster using host, username, and password details from the MyScale console, the guide downloads OpenAI's precomputed Wikipedia article embeddings dataset (via wget), extracts it, and loads it into a Pandas DataFrame. It then creates an articles SQL table in MyScale with a vector index using a cosine distance metric and a length constraint on the embedding vectors, inserts the loaded data, and checks the vector index's build status (since it builds automatically in the background) before the index is ready for querying. Finally, it generates an embedding for a search query via the OpenAI API and runs a MyScale vector search to retrieve semantically similar articles.
When to use - and when NOT to
Use this guide when you want a single system that handles both vector similarity search and standard SQL analytics on the same structured and unstructured data - for example when you need to join vector search results with relational filters or aggregations. It is not a guide to building a full RAG or QA application on top of the search results - it covers storage and nearest-neighbor retrieval of precomputed embeddings only, stopping at the search step.
Inputs and outputs
Input is precomputed OpenAI Wikipedia article embeddings plus a raw text search query. Output is a ranked list of nearest-neighbor articles retrieved from the MyScale articles table via cosine-distance vector search, once the background-built vector index is ready.
Integrations
Built on MyScale (ClickHouse-based vector database with SQL support), the clickhouse-connect Python client library, and the OpenAI embeddings API for query and document vectorization.
Who it's for
Developers who want a fully managed database combining vector similarity search with SQL analytics over the same data, and need a minimal working example of storing OpenAI embeddings and running nearest-neighbor search on MyScale.
Source README
Using MyScale as a vector database for OpenAI embeddings
This notebook provides a step-by-step guide on using MyScale as a vector database for OpenAI embeddings. The process includes:
- Utilizing precomputed embeddings generated by OpenAI API.
- Storing these embeddings in a cloud instance of MyScale.
- Converting raw text query to an embedding using OpenAI API.
- Leveraging MyScale to perform nearest neighbor search within the created collection.
What is MyScale
MyScale is a database built on Clickhouse that combines vector search and SQL analytics to offer a high-performance, streamlined, and fully managed experience. It's designed to facilitate joint queries and analyses on both structured and vector data, with comprehensive SQL support for all data processing.
Deployment options
- Deploy and execute vector search with SQL on your cluster within two minutes by using MyScale Console.
Prerequisites
To follow this guide, you will need to have the following:
- A MyScale cluster deployed by following the quickstart guide.
- The 'clickhouse-connect' library to interact with MyScale.
- An OpenAI API key for vectorization of queries.
Install requirements
This notebook requires the openai, clickhouse-connect, as well as some other dependencies. Use the following command to install them:
Prepare your OpenAI API key
To use the OpenAI API, you'll need to set up an API key. If you don't have one already, you can obtain it from OpenAI.
Connect to MyScale
Follow the connections details section to retrieve the cluster host, username, and password information from the MyScale console, and use it to create a connection to your cluster as shown below:
Load data
We need to load the dataset of precomputed vector embeddings for Wikipedia articles provided by OpenAI. Use the wget package to download the dataset.
After the download is complete, extract the file using the zipfile package:
Now, we can load the data from vector_database_wikipedia_articles_embedded.csv into a Pandas DataFrame:
Index data
We will create an SQL table called articles in MyScale to store the embeddings data. The table will include a vector index with a cosine distance metric and a constraint for the length of the embeddings. Use the following code to create and insert data into the articles table:
We need to check the build status of the vector index before proceeding with the search, as it is automatically built in the background.
Search data
Once indexed in MyScale, we can perform vector search to find similar content. First, we will use the OpenAI API to generate embeddings for our query. Then, we will perform the vector search using MyScale.
FAQ
Common questions
Discussion
Questions & comments · 0
Sign In Sign in to leave a comment.