Using a SQL Document Store and Airflow to automate indexing

[Ashish-Soni08]

Hey guys, Is there an example of how data is stored in a SQL document store, and how I can trigger a pipeline using an airflow SQL sensor if data is updated or added to the document store? Also, the data source is also a SQL database. Can I do this using the created and updated fields in the sql document store?

[bogdankostic]

Hi @Ashish-Soni08! Not sure what you mean by how data is stored, you can simply use the write_documents method to add Documents to your Document Store (see also our Documentation).
The SQLDocumentStore is usually not used on its own but in combination with a vector database, for example FAISS, to store the document embeddings into a FAISS instance and the document's content and metadata into a SQL database.
Also, I'm not very familiar with airflow so I'd be happy if you could provide a bit more information in order to help you.

[Ashish-Soni08]

All right! so from what I understand so far. other Document Stores store embeddings, content and metadata in one place but not SQLDocumentStore and FAISS. When we talk about just using the SQLDocumentStore, it can only be used for a sparse retrieval pipeline, we cannot store embeddings in SQLDocumentStore?

[bogdankostic]

Yes exactly, SQLDocumentStore doesn't support retrieval with embeddings.

[Ashish-Soni08]

Is there a resource or documentation to dig into to find/point out why its not possible.. ?

[bogdankostic]

We don't have a resource for this but the short answer would be that SQL databases don't allow you to efficiently query your database by a vector and get back the most similar Documents. That's the strength of specialized vector databases like FAISS, Weaviate, Pinecone, or Milvus. Also OpenSearch provides good support for storing vectors and querying by them.

[Ashish-Soni08]

All right! Thank you very much :)

[Ashish-Soni08]

@bogdankostic I had another query.. How can I get the storage space occupied by the indexed data in respective document stores?

[bogdankostic]

This highly depends on the document store that you use and isn't an information that Haystack provides. For Elasticsearch, for example, you can get this information via the allocation endpoint. The only information that Haystack can give your is the number of indexed Documents through document_store.get_document_count().

[Ashish-Soni08]

Thank you again

[Ashish-Soni08]

Hi @bogdankostic I hope you are well

I had some questions regarding the SQLDocumentStore

I generated some data and converted it to haystack format and called sql_document_store.write_documents(docs)

when I check the database I see

The embeddings column is not found in the database and the vector_id column is full of NULLs. But I can access the other data. Am I doing something wrong?

I did map the "embeddings" column to embedding in the Document class

Is this why the SQLDocumentStore cannot be used alone to store data and embeddings?

If I explicitly create a table with a field embedding that accepts an array of float values in the database and then try to store the data ? it will work, right? Can I store Haystack documents that way?

Why are there methods like SQLDocumentStore.get_documents_by_vector_ids if we can't use the DocumentStore on its own to store embeddings?

[bogdankostic]

Hey @Ashish-Soni08! Let me try to answer your questions:

If I explicitly create a table with a field embedding that accepts an array of float values in the database and then try to store the data ? it will work, right? Can I store Haystack documents that way?

Yes, this would work in theory, but is not feasible right now with Haystack. We don't store embeddings in SQL databases because we can't use them to query by a query embedding in an efficient way.

Why are there methods like SQLDocumentStore.get_documents_by_vector_ids if we can't use the DocumentStore on its own to store embeddings?

These methods are used for DocumentStores that combine a vector database that only stores the vectors (like FAISS) with a SQL database for the Document's content and metadata. If you try to query these types of DocumentStores, we query the vector db using the query embedding and get back the IDs of the vectors that are most similar to the query vectors. These vector IDs are then used to get back the Document's content and metadata from the SQL database using the get_documents_by_vector_ids method.

[Ashish-Soni08]

Thank you so much @bogdankostic