9 ms·
Show HN: We open sourced our entire text-to-SQL product
Long story short: We (Dataherald) just open-sourced our entire codebase, including the core engine, the clients that interact with it and the backend application layer for authentication and RBAC. You can now use the full solution to build text-to-SQL into your product.
The Problem: modern LLMs write syntactically correct SQL, but they struggle with real-world relational data. This is because real world data and schema is messy, natural language can often be ambiguous and LLMs are not trained on your specific dataset.
Solution: The core NL-to-SQL engine in Dataherald is an LLM based agent which uses Chain of Thought (CoT) reasoning and a number of different tools to generate high accuracy SQL from a given user prompt. The engine achieves this by:
- Collecting context at configuration from the database and sources such as data dictionaries and unstructured documents which are stored in a data store or a vector DB and injected if relevant
- Allowing users to upload sample NL <> SQL pairs (golden SQL) which can be used in few shot prompting or to fine-tune an NL-to-SQL LLM for that specific dataset
- Executing the SQL against the DB to get a few sample rows and recover from errors
- Using an evaluator to assign a confidence score to the generated SQL
The repo includes four services https://github.com/Dataherald/dataherald/tree/main/services https://github.com/Dataherald/dataherald/tree/main/services:
1- Engine: The core service which includes the LLM agent, vector stores and DB connectors.
2- Admin Console: a NextJS front-end for configuring the engine and observability.
3- Enterprise Backend: Wraps the core engine, adding authentication, caching, and APIs for the frontend.
4- Slackbot: Integrate Dataherald directly into your Slack workflow for on-the-fly data exploration.
Would love to hear from the community on building natural language interfaces to relational data. Anyone live in production without a human in the loop? Thoughts on how to improve performance without spending weeks on model training?
- instabart 2y agoInteresting! I am assuming it can do complex joins. Are there any examples of text -> sql it produces? I looked on the website but only saw "coming soon"
- coder543 2y agoHave you considered enforcing a grammar on the LLM when it is generating SQL? This could ensure that it only generates syntactically valid SQL, including awareness of the valid set of field names and their types, and such. It would not be easy, by any means, but I believe it is theoretically possible.
- kasmura 2y agoThat cannot be done when using OpenAI API calls as far as I know
- coder543 2y agoNobody in the original post or this entire discussion said anything about OpenAI until your comment. I thought it was fairly obvious that we were talking about a local LLM agent... if DataHerald is a wrapper around only OpenAI, and no other options, then that seems unfortunate.
- aazo11 2y agoThe agent is LLM agnostic and you can use it with OpenAI or self-hosted LLMs. For self hosted LLM we have benchmarked performance with Mixtral for tool selection and CodeLlama for code generation.
- aazo11 2y agoThe agent currently executed the generated SQL (limited to 10 rows) and recovers from errors.
- Kiro 2y agoThat sounds overkill. It's usually enough to just tell the LLM to output valid SQL and it will adhere to the schema.
- lmeyerov 2y agoIn our experience building louie.ai for a continuous learning variant of text2query (and for popular DBs beyond SQL), getting syntax right via a symbolic lint phase is a nice speedup, but not the a correctness issue. For syntax, bigger LLMs are generally right on the first shot, and an agent loop autocorrects quickly when the DB gives a syntax error. Much more time for us goes to things like: * Getting the right table, column name spelling * Disambiguating typos when users define names, and deciding whether they mean a specific name or are using a shorthand * Disambiguating selection when there are multiple for the same thing: hint - this needs to be learned from usage, not by static schema analysis * Guard rails, such as on perf * Translation from non-technical user concepts to analyst concepts * Enterprise DB schemas are generally large and often blow out the LLM context window, or make things slow, expensive, and lossy if you rely on giant context windows * Learning and team modes so the model improves over time. User teaching interfaces are especially tricky once you expose them - learning fuzzy vs explicit modes, avoid data leakage, ... . * A lot of power comes from being part of an agentic loop with other tools like Python and charting, which creates a 'composition' problem that requires AI optimization across any sub-AIs We have been considering OSS this layer of louie.ai, but it hasn't been a priority for our customers, who are the analyst orgs using our UIs on top (Splunk, OpenSearch, Neo4j, Databricks, ...), and occasionally building their own internal tools in top of our API. Our focus has been building a sustainable and high quality project, and these OSS projects seem to be very different to sustain without also solving that, which is hard enough as-is..
- vlovich123 2y agoAre there strategic parts of the stack you haven’t open-sourced?
- aazo11 2y agoThe entirety of the codebase is now open source.
- vlovich123 2y agoIt’s sometimes hard to understand how you keep a business running when you’ve open-sourced your entire stack, both consumers self-hosting but even worse would be a competitor just taking what you spend R&D budget on & rehosting it with a cheaper price since they don’t need to pay for that R&D. From a business perspective, do you see the operational challenge of running your stack at scale as the differentiator?
- fhd2 2y agoNot affiliated with OP and therefore unable to answer your question, but there's a lot of products that built traction that way: WordPress, GitLab, Discourse, Docker, Ubuntu, ... I think it solves the problem of gaining traction today, at the expense of future market power. Then they face a choice of pulling a HashiCorp or being OK with being a commodity provider rather than a fancy unicorn. I can see the appeal, a humble business is better than no business, isn't it?
- bobismyuncle 2y agoCurious why you decided to open source your entire product. Are you moving to an open core model? I’d expect in that case that much of 2, 3 & 4 would have stayed closed. Would be grateful if you can share your reasoning
- robertlagrant 2y agoThis is often the move when the team's spent the money developing something and now the end's in sight, so they want the chance to leave and take the code with them. Don't know if this is that at all, but it's always worth considering.
- e1g 2y agoThat is almost certainly what’s happening here. They raised $3M three years ago, at the peak of evaluations, and don’t have the metrics to raise a Series A in the current climate. Running out of money and want to leave some artifact behind. A very difficult and emotional transition.
- hackernewds 2y agoI don't understand the "leave the code with them" part
- learnedbytes 2y agoI think they mean by open sourcing, they can take the code to a new startup without having IP legality issues.
- tomhallett 2y agoWould open sourcing the core IP of a company “typically” require board approval? If a company goes under, the investors will want to sell off the IP, open sourcing everything would make that IP less valueable. There must be some blanket clause in the term sheet to cover that, right? Ie: founders won’t do anything which will materially hurt the company without board approval (or something, I am no where close to a lawyer, this is all conjecture)
- akch 2y agoNot finding the license anywhere. Which one have you chosen?
- aazo11 2y agoHi -- the license is Apache 2.0
- numlocked 2y agoIs that documented somewhere? The "contributing" link in the readme also 404s. I would definitely need to understand the licensing and how contributing works before I could consider integrating this (and I would definitely consider it!). Cool stuff.
- threesevenths 2y agoGuess it's public domain since there is no license
- munk-a 2y agoJust for future reference - if no license is given it's unlicensed. Licensing defaults closed for extremely good reasons - that's one of the reasons why github had a strong push for users to assign appropriate licensing documents to repositories a while back and declare those licenses in machine readable forms (if applicable).
- alchemist1e9 2y agoCoT, OPA, CoALA … these techniques can deliver massive performance improvements. Are there any other methods for agent frameworks? any way to follow these developments vs pure LLM research?
- fpater 2y agoSuper cool to see this!! I've been prototyping with NL-to-SQL recently, one problem I've stumble into is how to prevent mistakes from impacting your database, be it a hallucination or even a malicious actor who was able to send a prompt to the LLM agent. I don't have much input about the questions you asked here, but feel free to contact me (info on my profile) if you'd like to talk about those other aspects!!
- aazo11 2y agoSure will reach you out. Currently Dataherald blocks DML or DDL commands from being generated/executed.
- arrosenberg 2y agoI still wonder who the audience is for tools like this. The website posits you can answer data questions without going through an analyst, but the role of the analyst is not to be a SQL whisperer for PMs and Executives - it is to be an expert in the model and the data. A data warehouse of any real scale is going to have some amount of issues - anomalous data, different interpretations of the same numbers - how does the LLM deal with that consistently across a business?
- saigal 2y agothe target audience is developers who wish to embed text to SQL functionality into their own products. the target audience is less the 'internal use case' (i.e. a data analyst) and more about letting external users do things they couldn't do before. a good example is payroll software where this type of technology can allow users to pull reports.
- arrosenberg 2y agoI agree that is a more reasonable use-case. The readme for this tool seems geared toward the business of answering business questions.
- saigal 2y agoTbh the original intention was to be the "data analyst" but we found over time (and with literally 100s of user conversations at small cos and enterprises) the embedded use case was more interesting and made for a better business, which was not at all what we expected.
- _hzw 2y agox
- thedynamicduo 2y agoThis looks really cool, can't wait to check it out. The problem I've seen with other tools I've tinkered with is that they do well with simple stuff like: "what are my latest orders" -> select * from orders where user_id=x order by created_date But really struggle when you have a complex schema that requires joins, and basically has no support when you are describing something that needs outer joins or the like. Would be great to hear if DataHerald has cracked that nut or if it's still a challenge for you as well (no judgement if it is, it seems like a hard problem).
- saigal 2y agogreat question, and the one that we get the most :-) this is precisely why we created Dataherald. Off the shelf LLMs can handle a single table and simple questions. Dataherald's quest is to ultimately provide enterprise-grade text to SQL, where complex schema and joins are present. it does take some training, but we've found that it can handle situations such as the one you mention above.
- BossingAround 2y agoPerhaps orthogonal problem - imagine you join a new company that has an enterprise product with hundreds of tables. Is there a way to connect Dataherald to my DB, and ask basic questions about the DB? E.g. "where are stored records related to X".
- aazo11 2y agoYes when you connect Dataherald to a DB it scans it and you can do exploratory queries.
- momothereal 2y agoWhat happens when the tables and columns have cryptic names/acronyms? Do you need to inject documentation?
- roughly 2y ago
- mholubowski 2y agoHey! Why did you open source it? Genuinely curious.
- tootie 2y agoIs the only LLM support OpenAI?
- saigal 2y ago"The agent is LLM agnostic and you can use it with OpenAI or self-hosted LLMs."
- totalhack 2y agoIs this more like text-to-semantic layer or does it throw the schema in the prompt and generate SQL with the llm?
- aazo11 2y agoThis is not a text to semantic layer but it does far more than just inject schema into the prompt: - the engine keeps an updated catalog of the data (low cardinality columns, their values etc) - taps into query history and finetunes the model to the schema - allows uploading context from unstructured sources like docs and data dictionaries - has an agent which collects all relevant info, generate the SQL, tries to retrieve a few rows to recover from errors and provides an confidence score to the generated SQL
- winphone1974 2y agoSQL is really close to a natural language that's unambiguous, there's a few rough edges but it's not bad. Anything more natural requires a lot of context and needs to solve ambiguity.
- saigal 2y agowhile i agree, there is clear demand for people to use natural language to SQL. we have tremendous conviction around the desire for natural language tools, but of course the technology and product need to deliver desired results.
- saigal 2y ago"Anything more natural requires a lot of context and needs to solve ambiguity. this is precisely why we created Dataherald-- to make it much easier to add that business context so that NL to SQL could actually be good enough to get into production
- kwerk 2y agoWill it work with GraphQL?
- throwaway115 2y agoWhat guarantees do you offer with query security if I turn this over to an end user? How do I keep them only accessing their own data?
- freeone3000 2y agoAny number of database namespacing techniques already present in postgresql can prevent this. Link the user sign-on to a DB user and you’re gold.
- throwaway115 2y agoWhat? How does that ensure user 123 only generates LLM queries that constrain on rows where user=123?
- aazo11 2y agoAs I wrote on the original thread, we recommend using the RDBMS row-level security features. This blog discusses how to do that on Postgres https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/ https://www.2ndquadrant.com/en/blog/application-users-vs-row...
- altdataseller 2y agoWay way too complicated. I thought this tool was suppsed to make my life easier
- saigal 2y agois there an easier way?
- altdataseller 2y agoYes write SQL
- RyanHamilton 2y ago>Would love to hear from the community on building natural language interfaces to relational data. I produce a free sql editor that allows users to plugin openai to perform text to sql: https://www.timestored.com/qstudio/help/ai-text2sql https://www.timestored.com/qstudio/help/ai-text2sql so far uptake is slow and the only good benefit is to spit out a few queries as a starting point. The accuracy went up significantly by sending schema and sample data but it sounds like you've done a good job at going beyond that. I wouldn't say my users or I am convinced it's the future but I'll certainly look at your product tomorrow. Good work and congratulations.
- saigal 2y agoYes please do. We’d love your feedback and or to hear whether you see material improvement over what you have now
- fkm0r0ns 2y agoIt's a great idea. Of course, there will be some ambiguities, but maybe over time you can somehow constrain the input language a bit, adding some structure to it, such that you can query a database in English-like syntax without any ambiguities. That would be nice!
- iandanforth 2y agoAm I misreading this code? It looks like you don't have precomputed table representations and search, instead you scan, embed, and compare on each run? https://github.com/Dataherald/dataherald/blob/main/services/engine/dataherald/sql_generator/dataherald_sqlagent.py#L217 https://github.com/Dataherald/dataherald/blob/main/services/...
- aazo11 2y agoTables, columns and views are scanned at configuration time (or based on an API trigger) and stored in the data store and a vector store, not on every run. They are then retrieved and injected based on relevance to the query.
- chenster 2y agoThank you! This is exactly something we are look for at querro.io
- threeseed 2y agoNever understood why I would want to use this over an NLP+ORM system. At least with that you get 100% accuracy at the expense of having to use a fixed syntax.
- aazo11 2y agoORMs generally map around entities and dimensions. Users generally ask about metrics and measures, which can be expressed in aggregations and group bys. How ould the NLP+ORM system do this?
- DeathArrow 2y agoI understand that this does better than the average LLM because you can train it using the database structure. But since database structures can change a lot, it might require retraining often. Is retraining being done automatically after each PR that modifies the DB? Is there a way to inject the DB structure in the context?
- zurfer 2y agoThat's one of the more feature rich AI analytics assistants. (1) Kudos for open sourcing. I think it's really difficult to build a business around that, but there are some successful examples in the space: metabase, airbyte, dbt, (maybe databricks?) (1) https://github.com/Snowboard-Software/awesome-ai-analytics https://github.com/Snowboard-Software/awesome-ai-analytics
- evan_ry 2y agoThis is a historical contribution. Thank you for doing it! Basically all the enterprises with a lot of data need to "chat with their data" right now. I can't imagine how many teams are doing similar stuff right now.
- iloveitaly 2y agoWe open-sourced our text-to-sql product last year too (way more simple than this): https://github.com/ryanstout/question_to_sql https://github.com/ryanstout/question_to_sql These sorts of businesses are really hard to build: incumbents have such an advantage. Makes so much more sense for this to be (a) open source (b) tied to snowflake / powerbi that have free distribution and a good security story.
- saigal 2y agoWe also encounter a lot of build vs buy conversations with businesses.
- devd00d 2y agoI struggle to see why chatgpt can't do this already?
- badgersnake 2y agoThen you haven’t used it very much.
- saigal 2y agoYes totally agree. You can easily sniff out products that are simple wrap of GPT
- yard2010 2y agoWouldn't a full featured OS GUI be a simple wrap of the command line? Would this make it less valuable to have?
- badgersnake 2y agoI think it would make it unusably slow.
- jaynpatel 2y agoPerhaps orthogonal problem - imagine you join a new company that has an enterprise product with hundreds of tables. Is there a way to connect Dataherald to my DB, and ask basic questions about the DB? E.g. "where are stored records related to X".
- PartiallyTyped 2y agoDump the schema, create a document for each table, use LLM with rag?
- zainhoda 2y agoWould you be interested in merging with Vanna in some way? You’re ahead of us in terms of interface but we’re ahead of you in terms of adoption (because of specific choices we’ve made and partnerships we’ve done).
- pamelafox 2y agoIt looks like the supported vector DBs are Pinecone and Astra. Have you looked into Postgres with pgvector? I’ve started experimenting with building RAG flows for pgvector, works fairly well.
- aazo11 2y agoRight now the supported Vector stores are Chroma (which you can self-host), Pinecone and Astra. Adding a new vector store is quite easy: you just need to extend the VectorStore class (https://github.com/Dataherald/dataherald/tree/main/services/engine/dataherald/vector_store https://github.com/Dataherald/dataherald/tree/main/services/...) and set it as the Vector store module to be used in the environment variable https://github.com/Dataherald/dataherald/blob/main/services/engine/.env.example https://github.com/Dataherald/dataherald/blob/main/services/...
- emmender2 2y agodid data-herald not find usecases or user problems to solve using its tech ? are any startups applying LLMs profitable at all ? or is it just a mirage - ie, in the real world, startups are not able to solve users problems well using LLMs.
- npsimons 2y agoThis is awesome! While I'm nowhere near being able to leverage this right now, I am currently going through the painful process of "databasing" raw documents into SQL, and I can tell you that perhaps the hardest part is getting the schema correct; as you put it "natural language can often be ambiguous". Even worse, is just the squishiness of things never originally intended to be specified for software. Communication always has been, and continues to be, the hardest part of software development.
- dhanushreddy29 2y agoI built a similar thing with a streamlit interface for an hackathon recently. https://devpost.com/software/personal-sql-assistant https://devpost.com/software/personal-sql-assistant
- westurner 2y ago/? awesome "sql" llm site:github.com https://www.google.com/search?q=awesome+%22sql%22+llm+site%3Agithub.com https://www.google.com/search?q=awesome+%22sql%22+llm+site%3... : - awesome-Text2SQL: https://github.com/eosphoros-ai/Awesome-Text2SQL https://github.com/eosphoros-ai/Awesome-Text2SQL : > Curated tutorials and resources for Large Language Models, Text2SQL, Text2DSL、Text2API、Text2Vis and more. - Awesome-code-llm > Benchmarks > Text to SQL: https://github.com/codefuse-ai/Awesome-Code-LLM#text-to-sql https://github.com/codefuse-ai/Awesome-Code-LLM#text-to-sql - underlines/awesome-ml//llm-tools.md > RAG > OpenAI > dataherald,: https://github.com/underlines/awesome-ml/blob/master/llm-tools.md#openai-3 https://github.com/underlines/awesome-ml/blob/master/llm-too... - underlines/awesome-ml//llm-tools.md > Benchmarking > Benchmark Suites, Leaderboards: https://github.com/underlines/awesome-ml/blob/master/llm-tools.md#benchmark-suites https://github.com/underlines/awesome-ml/blob/master/llm-too... : - sql-eval: https://github.com/defog-ai/sql-eval https://github.com/defog-ai/sql-eval : > This repository contains the code that Defog uses for the evaluation of generated SQL. It's based off the schema from the Spider, but with a new set of hand-selected questions and queries grouped by query category. For an in-depth look into our process of creating this evaluation approach, see this. > Our testing procedure comprises the following steps. For each question/query pair: 1. We generate a SQL query (possibly from an LLM). 2. We run both the "gold" query and the generated query on their respective database to obtain 2 dataframes with the results. 3. We compare the 2 dataframes using an "exact" and a "subset" match. TODO add link to blogpost. 4. We log these alongside other metrics of interest (e.g. tokens used, latency) and aggregate the results for reporting - dataherald/services/engine/dataherald/tests/sql_generator/test_generator.py: https://github.com/Dataherald/dataherald/blob/main/services/engine/dataherald/tests/sql_generator/test_generator.py https://github.com/Dataherald/dataherald/blob/main/services/...
- mariarmestre 2y agoAre you going to keep maintaining the package?
- saigal 2y agowe intend to