Show HN: Dataherald AI – Natural Language to SQL Engine (github.com)
GPT-4 class LLMs have gotten remarkably good at writing SQL. However, out-of-the-box LLMs and existing frameworks would not work with our own structured data at a necessary quality level. For example, given the question “what was the average rent in Los Angeles in May 2023?” a reasonable human would either assume the question is about Los Angeles, CA or would confirm the state with the question asker in a follow up. However, an LLM translates this to:
select price from rent_prices where city=”Los Angeles” AND month=”05” AND year=”2023”
This pulls data for Los Angeles, CA and Los Angeles, TX without getting columns to differentiate between the two. You can read more about the challenges of enterprise-level text-to-SQL in this blog post I wrote on the topic: https://medium.com/dataherald/why-enterprise-natural-languag...
Dataherald comes with “batteries-included.” It has best-in-class implementations of core components, including, but not limited to: a state of the art NL-to-SQL agent, an LLM-based SQL-accuracy evaluator. The architecture is modular, allowing these components to be easily replaced. It’s easy to set up and use with major data warehouses.
There is a “Context Store” where information (NL2SQL examples, schemas and table descriptions) is used for the LLM prompts to make the engine get better with usage. And we even made it fast!
This version allows you to easily connect to PG, Databricks, BigQuery or Snowflake and set up an API for semantic interactions with your structured data. You can then add business and data context that are used for few-shot prompting by the engine.
The NL-to-SQL agent in this open source release was developed by our own Mohammadreza Pourreza, whose DIN-SQL algorithm is currently top of the Spider (https://yale-lily.github.io/spider) and Bird (https://bird-bench.github.io/) NL 2 SQL benchmarks. This agent has outperformed the Langchain SQLAgent anywhere from 12%-250%.5x (depending on the provided context) in our own internal benchmarking while being only ~15s slower on average.
Needless to say, this is an early release and the codebase is under swift development. We would love for you to try it out and give us your feedback! And if you are interested in contributing, we’d love to hear from you!
107 comments
[ 4.9 ms ] story [ 181 ms ] threadYour product is very interesting. Maybe we can collaborate here, as Discourse is a standard Rails database schema on PostgreSQL and could be a good way to show your tool working in a real database.
I’ve no doubt the AIs will reach human parity at some point (and therefore still make mistakes!) but I’d be terrified deploying any sort of one-shot text-to-results pipeline today.
Additional color: Once you configure a connection to a DB, you can trigger a 'scan' of the DB through the API which detects various tables/columns, identifies low cardinality (categorical) columns and their values and also loads in VIEWS stored on tables. In our default NL2SQL agent, these are then used together with 'verified' NL2SQL pairs and with other metadata you can manually add to generate the SQL.
We are looking to automatically add context from Stored procedures and other sources like dbt and various data dictionaries as well.
In 10 years, programming languages like Java, SQL, etc will be dead.
I predict everyone will write in their native language (English, Chinese, etc) and a AI-based compiler will write the actual code for them.
Without the ability to fine tune Codex, we had to employ a bunch of techniques to pick the right set of examples to provide in the context to get good SQL. We have come very far since then and it has been great to see projects projects like sqlcoder and this.
Pieces of the pipeline powering makerdojo eventually became LLMStack (https://github.com/trypromptly/LLMStack). We are looking to integrate one of these SQL generators as processors in LLMStack so we can build higher level applications.
Want to put your attention to https://www.veezoo.com as well. A conversational self-service analytics solution that's been around since 2016 and productively deployed in fortune 500 companies and used by thousands of users daily :)
Congrats on the launch - and also reaching the top of the Spider dataset. We're very familiar with that dataset and its difficulties :)!
Happy to have a chat as well!
- https://hasura.io/docs/latest/api-reference/graphql-api/quer...
- https://postgrest.org/en/stable/references/api/tables_views....
^ The advantage would be, these tools handle authorization.
Do you have a specific use-case in mind? Would love to collaborate on adding it to the open-source library if you are open to it.
2014, so it (and previous similar projects) are not going to be able to compete with contemporary LLMs.
Like instead of shoehorning LLMs into old paradigms, why not conceive data storage systems inherently designed for LLM interfacing?
There's a latent inefficiency in using SQL as a bridge. It feels like trying to fit a new narrative into an old framework
And yes, rethinking database architectures from the ground up is daunting and demands more background in storage systems but given where machine learning and data interaction are heading, maybe we should consider it.
Surely it makes sense for a MVP, but I'm curious about how much we're potentially forfeiting in terms of innovation by anchoring so deeply to it with all the possibilities afforded by generative AI. In holding onto SQL, might we be forestalling the development or adoption of a more evolved language? With Dataherald's proposition, how do you envision maintaining that cutting edge, ensuring you're not just prolonging the lifespan of a fading tech, but genuinely pushing the envelope forward?
I suppose one intriguing possibility in my mind is that Dataherald could serve as a bridge or runtime. As you continue to develop and refine the tool, do you see a future where it might support more abstract querying languages or even emerging data interaction paradigms? This could potentially mitigate some transition pains when the tech ecosystem eventually shifts.
1) A middle layer, such as SQL, offers a vital introspection point to understand and fix LLM mistakes. Without it, we will end up with a "black box" that could lead to errors in data queries and manipulations, making debugging and correction much more challenging.
2) Mathematical properties and data integrity: Databases are built around principles like 3NF and BCNF that are pivotal for efficiency and data integrity. It is highly doubtful that these essential principles could be as effectively replicated in a pure LLM-based system without losing critical aspects of data management and efficient query planning & execution.
3) You mentioned "translational losses" without clarity on what these might be. SQL is a specialized declarative language with clear structure and syntax, while human language often leaves room for ambiguity. Relying solely on an LLM could lead to interpretations that are likely but not necessarily correct. This ambiguity might actually increase "translational losses" rather than reduce them.
I understand there's a lot of excitement around generative AI at the moment, but DBMS are an extremely complex topic and this feels like another case of "when you have a hammer everything looks like a nail".
1. Agree on the introspection point, but it's worth noting that the future of LLMs might involve self-awareness capabilities, which could provide an introspective mechanism similar to SQL's transparency. the aim would be to build upon this middle layer, not blindly supplant it. 2. While 3NF, BCNF, and other normalization forms have served us well, they are essentially tools to manage imperfections in our storage and retrieval systems. LLMs can be trained to understand these nuances intrinsically. additionally, database theories have evolved, with advancements like distributed databases, graph-based models, and nosql. so, it's not entirely outside the realm of possibility that we can pivot and adapt to new paradigms. 3. The "translational losses" referred to the semantic disconnect between natural language queries and their SQL representations. while SQL is unambiguous, the leap from a user's intent to the SQL formulation often introduces errors or inefficiencies. LLMs can be trained to improve over time, constantly refining based on feedback loops.
not arguing that SQL or databases as we know them are obsolete today, just advocating for a more imaginative exploration about where the tech is headed.
The thing is, an SQL database is already a sort of AI - an old school algorithmic AI:
“The code generator, and especially the logic in where*.c and in select.c, is sometimes called the query planner. For any particular SQL statement, there might be hundreds, thousands, or millions of different algorithms to compute the answer. The query planner is an AI that strives to select the best algorithm from these millions of choices.”
https://www.sqlite.org/arch.html
Translating natural language into SQL is ironic, because SQL was intended to be a natural language that would be interpreted by an AI.
In the future maybe an AI could be trained to bypass high-level languages. Perhaps it could generate intermediate representation (IR) code - skipping the tokenising and parsing. Perhaps it could generate byte code directly. Or it could even skip the VM and generate machine code to work with the back-end API.
SQL is easy. Knowledge management is hard. Does the LLM know that there was a bug in June that changed the data? Does it know that this one column is improperly named and confusing? Does it know that you recently released a mobile app the data from which is in a different table?
No, of course not, those things are never explicitly documented and so are invisible to an LLM.
This only works if you have perfect data engineering. But if you have perfect data engineering, point and click tools like Looker will work too.
I did an NL-to-SQL startup, but now I think is a much better time to do this.
With a human in the loop, even providing a "mostly" correct SQL that takes a swing at the correct joins between relevant tables reduces the data practitioner's work significantly. Of course, as more questions are asked, the tool gets better at writing the SQL better. Almost like a human in a Database Management and SQL class...
If it was that easy you wouldn't have a tradition of devs trying for any alternative.
There's obviously a use case for this sort of product, objections appealing to the ease of use of any technical language or toolset are unlikely to be convincing to the majority who are not comfortable with it.
When your only tool is a FOR loop hammer, every set based operation frustratingly looks less like a nail than a screw.
No. Does "the SQL"? Also no.
That requirement falls on the person using the tool, doesn't it?
It takes a lot of training to get a team member up to speed - with the same amount of training, do you think an LLM can compete?
That being said, I am pretty sure the self hosted models will get there very soon.
Are you looking to deploy a solution with a self-hosted model?
i should note that our (dataherald's) product only sends schema to the LLM, which is important to note.
Here a few thoughts, feedback, and questions:
* you do a good job in this post of describing why you need more than just ChatGPT to get acceptable quality, but much less of that is in the readme. I wouldn't be afraid to sell the project a bit harder, even in early days
* likewise, I think a small visual of the architecture would be helpful, just to make clear what the relationship is to chatGPT, and the additional features of a context store, etc
* Between the notes here and your product pages, it seems a strategy for commercialization with this repo being a lower level tool, with your commercial service having UI, simplified integration, and a hosted version to make this easier for semi-techical teams? If that is the case, I wouldn't be afraid to make that more explicit. GitHub is more and more a place for discovery, even to semi-techical people, but to do that well, I think focusing on a readme that makes it clear who the open source is for is important
* How are you planning on solving the data access problem? Is this a full SaaS service that will need access to the customer data directly somehow? Do you deploy this in the customer's environment? In thinking beyond this open source release and to the commercial side, that would be what I would want to know
* This point has been hit by others in this thread, but I would be curious to know what your plans are to help protect against valid but incorrect queries? I am not as convinced that this problem is insurmountable, as it does seem like you could build features to remove ambiguity by asking questions, show alternatives, etc that most semi-techical people could reason through, but ultimately it seems like thinking about how to involve the teams that own the data might be an important part of the problem.
Anyways, congrats again! This is an area I am really excited to see how it evolves (and will be exploring more!). My email is in my profile if you are interested in chatting more :)
You can connect the hosted version to your data warehouse using the SQLAlchemy connectors which are in the open source engine, and the connection info will be stored after being encrypted. An on-prem version of the enterprise product is also on the roadmap.
For protecting against errors the approach is to allow for a human to be in the loop as you alluded to. One of the core pieces of the engine is the Evaluation module which combines white box and black box techniques. You can block the response from going back to the question asker if the confidence level is below a certain threshold until a human verifies the response manually (or always put the human from the data team in the loop).
To minimize the token count used by Large Language Models (LLMs), we've designed our tools to handle most of the computational work internally. Consequently, only a small number of tokens are transmitted to the agent. The agent focuses solely on decision-making, while the tools handle the heavy lifting and similarity assessments.
Will definitely look at using Dataherald for adding proper SQL support!
DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.
I've found that spelling out exactly what I don't want an LLM to do dramatically increases the probability that it will do exactly that.
For example:
SELECT clicks.session_id, clicks.page_url, sessions.user_id FROM clicks, sessions;
(This context is guaranteed to generate lots of cartesian products.)
Regarding the context store you mentioned, it plays a vital role in our system. We use the context store to retrieve previously verified questions from earlier interactions. This functionality assists the agent in locating the correct tables and columns based on the knowledge gained from past interactions with our system.
I have to say the best implementations out there are already far ahead. Naturally there are some context limitations with large database schema but those can be handled:
- having an easy way to include/exclude tables ad hoc
- importing database schema in multiple different data sources that you can switch between depending on usage
And of course if AI isn't using the correct tables or columns, simple prime the prompt using autosuggest feature containing the tables and columns.
Generating JOINS isn't really a hurdle if you have a good prompt template:
- https://aihelperbot.com/snippets/cllovmlat000kmj0fedssj8c5 (notice it even uses the correct "film" tables even-thought it was instructed to find "movies" and naturaly category and category bridges table)
- https://aihelperbot.com/snippets/cllnztzky0002mj0fa78hw0o2 (it can also find lat/lng for Boston for AWS Athena, was demonstrating this for a Boston company)
- https://aihelperbot.com/snippets/cllo1vwl6000wlb0fj5669zt4 (It can generate in Mandarin as well, but less capable than English. Same goes for German, French, Spanish and so)
You can also have AI generate advanced SWOT analysis based on your database schema: https://i.imgur.com/JTv4QmX.png
There's just one thing I worry about. It's losing expertise in your data model and gaining organizational false confidence in bad data. Let's consider Bob. Bob is a Product Manager.
Bob always used to bother his software engineers to write SQL queries, but now he just uses this tool. Bob didn't write the tables or the data structures, so Bob doesn't know the nuances of the data model. Bob just types English and gets result sets back. Bob doesn't know that field order_status can also be in "pending_legal", and neither does the "sql compiler" know when it's appropriate to add or elide that field. Bob then presents his data to leadership to make changes to the Pending Order Logic, based on bad data.
Other than that, I'm sceptical for using LLMs for lots of things, but this I find cool. In my consulting work (and even before as a CTO), I usually train PMs, CEOs, really anyone who didn't make it up a tree by the count of 3 some basic code/tech literacy.
Imagine if Bob used this tool to learn about SQL and the data scheme. Now he got a glimpse of the inherent complexities, knows better when and how to ask for help, can have more trust in and better conversations with those engineers. Sounds naive probably, but I've usually seen these results from my (manual) efforts.
Of course that only works if the tool exposes the generated SQL in some fashion, it looks like it can but I might be wrong. If it's like you say and it's just natural language in, data out, I'd say that's more likely to do harm than good :)
Edit: Took a minute to look at the code, and it looks like there's no API to generate SQL from a question, just the "question" API that generates SQL on the fly and returns the data. I really think there'd be a lot of potential in at least returning the actual SQL query along with the data. Hope I didn´t miss something!
I fix small stuff around my house. Does that make me a builder, or threaten builder jobs? Don't think so. I know when to call one and I know to trust them based on understanding the nature and struggles of their work a little bit better.
You can even see the entire chain of LLM calls in the intermediate_steps field to help debug the agent.
And perhaps even more importantly, my experience has been that people ultimately wish to understand trends. In other words, an ad-hoc question that is posed and answered in natural language might ultimately be made into a dashboard that lives on the BI tool (so that trends become clearer).
I was looking at the project to spin it up with a BigQuery connection, but I couldn't find an example in the docs (expected it on this page: https://dataherald.readthedocs.io/en/latest/api.database.htm...), nor can I find it in the repo. Any hints on this? Otherwise I'm fine going source code diving. :)
I think it's useful to add as a note how to add credentials when running inside Docker since that mode is included right there in the readme (ie. adding a volume with the credentials).
As an idea, for the "Adding string descriptions" section, having stuff like dbt docs (or even just the manifest that generates) could supercharge the context.
Very interesting in general, definitely trying it out for our database!