https://github.com/Dataherald/dataherald Skip to content Toggle navigation Sign up * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions For + Enterprise + Teams + Startups + Education By Solution + CI/CD & Automation + DevOps + DevSecOps Resources + Customer Stories + White papers, Ebooks, Webinars + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. {{ message }} Dataherald / dataherald Public * Notifications * Fork 14 * Star 369 License Apache-2.0 license 369 stars 14 forks Activity Star Notifications * Code * Issues 1 * Pull requests 1 * Actions * Projects 0 * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Security * Insights Dataherald/dataherald This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags Name already in use A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch? Cancel Create 3 branches 0 tags Code * Local * Codespaces * Clone HTTPS GitHub CLI [https://github.com/D] Use Git or checkout with SVN using the web URL. [gh repo clone Datahe] Work fast with our official CLI. Learn more about the CLI. * Open with GitHub Desktop * Download ZIP Sign In Required Please sign in to use Codespaces. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Launching Xcode If nothing happens, download Xcode and try again. Launching Visual Studio Code Your codespace will open once ready. There was a problem preparing your codespace, please try again. Latest commit @jcjc712 @aazo11 jcjc712 and aazo11 DH-4514 Add in readme how to create DB_URI connections and add scanne... ( ... 5a57184 Aug 24, 2023 DH-4514 Add in readme how to create DB_URI connections and add scanne... ( #124) * DH-4514 Add in readme how to create DB_URI connections and add scanned db endpoint example * DH-4514 edit README * DH-4514 edit docs for connections to data warehouses --------- Co-authored-by: Amir A. Zohrenejad 5a57184 Git stats * 34 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time .github/workflows Initial commit August 15, 2023 14:11 dataherald handle_parsing_errors worng type (#122) August 24, 2023 15:38 docs DH-4514 Add in readme how to create DB_URI connections and add scanne... ( August 24, 2023 13:47 initdb.d Initial commit August 15, 2023 14:11 .DS_Store DH-4434/removing the hardcoded gpt-4 from eval (#90) August 15, 2023 16:11 .dockerignore Initial commit August 15, 2023 14:11 .env.example DH-4446 Improve .env.example (#106) August 16, 2023 15:09 .gitignore DH-4453/creating the collection if doesn't exist when removing from v... August 16, 2023 12:59 .readthedocs.yaml Fix .readthedocs.yaml (#100) August 16, 2023 11:44 .test.env DH-4446 Remove first db connection (#104) August 16, 2023 14:52 CONTRIBUTING.md Initial commit August 15, 2023 14:11 Dockerfile Initial commit August 15, 2023 14:11 LICENSE Initial commit August 15, 2023 14:11 README.md DH-4514 Add in readme how to create DB_URI connections and add scanne... ( August 24, 2023 13:47 docker-compose.yml DH-4446 Remove first db connection (#104) August 16, 2023 14:52 log_config.yml Initial commit August 15, 2023 14:11 pyproject.toml Initial commit August 15, 2023 14:11 requirements.txt DH-4480 Fix databricks connection (#110) August 17, 2023 17:13 setup.py Initial commit August 15, 2023 14:11 View code [ ] dataherald Overview Background Goals Get Started How to Run Dataherald (with local Mongo) using Docker See Docker App container logs Connect to Docker MongoDB container Connecting to and Querying your SQL Databases Connecting to your data warehouses Connecting through the API Connecting to supported Data warehouses Adding Context Scanning the Database Get a scanned db Adding verified SQL Adding string descriptions Querying the Database in Natural Language Replacing core modules Contributing Mongo errors README.md dataherald Dataherald logo Query your structured data in natural language. Discord | License | Docs | Homepage Dataherald is a natural language-to-SQL engine built for enterprise-level question answering over structured data. It allows you to set up an API from your database that can answer questions in plain English. You can use Dataherald to: * Allow business users to get insights from the data warehouse without going through a data analyst * Enable Q+A from your production DBs inside your SaaS application * Create a ChatGPT plug-in from your proprietary data This project is undergoing swift development, and as such, the API may be subject to change at any time. Overview Background The latest LLMs have gotten remarkably good at writing SQL. However we could not get existing frameworks to work with our structured data at a level which we could incorporate into our application. That is why we built and released this engine. Goals Dataherald is built to: * Be modular, allowing different implementations of core components to be plugged-in * Come batteries included: Have best-in-class implementations for components like text to SQL, evaluation * Be easy to set-up and use with major data warehouses * Get better with usage * Be fast Get Started The simplest way to set up Dataherald is to use the hosted version. We are rolling this service to select customers. Sign up for the waitlist. You can also self-host the engine locally using Docker. By default the engine uses Mongo to store application data. How to Run Dataherald (with local Mongo) using Docker 1. Create .env file, you can use the .env.example file as a guide. You must set these fields for the engine to start. cp .env.example .env Specifically the following 5 fields must be manually set before the engine is started. #OpenAI credentials and model OPENAI_API_KEY = LLM_MODEL = ORG_ID = #Encryption key for storing DB connection data in Mongo ENCRYPT_KEY = While not strictly required, we also strongly suggest you change the MONGO username and password fields as well. Follow the next commands to generate an ENCRYPT_KEY and paste it in the .env file like this ENCRYPT_KEY = 4Mbe2GYx0Hk94o_f-irVHk1fKkCGAt1R7LLw5wHVghI= # Install the package cryptography in the terminal pip3 install cryptography # Run python in terminal python3 # Import Fernet from cryptography.fernet import Fernet # Generate the key Fernet.generate_key() 2. Install and run Docker 3. Create a Docker network for communication between services. We need to set it up externally to enable external clients running on docker to communicate with this app. Run the following command: docker network create backendnetwork 4. Build docker images, create containers and raise them. This will raise the app and mongo container docker-compose up --build You can skip the --build if you don't have to rebuild the image due to updates to the dependencies 5. Check that the containers are running, you should see 2 containers docker ps It should look like this: CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 72aa8df0d589 dataherald-app "uvicorn dataherald...." 7 seconds ago Up 6 seconds 0.0.0.0:80->80/tcp dataherald-app-1 6595d145b0d7 mongo:latest "docker-entrypoint.s..." 19 hours ago Up 6 seconds 0.0.0.0:27017->27017/tcp dataherald-mongodb-1 6. In your browser visit http://localhost/docs See Docker App container logs Once app container is running just execute the next command docker-compose exec app cat dataherald.log Connect to Docker MongoDB container Once your mongo container is running you can use any tool (Such as NoSQLBooster) to connect it. The default values are: HOST: localhost # inside the docker containers use the host "mongodb" and outside use "localhost" PORT: 27017 DB_NAME: dataherald DB_USERNAME = admin DB_PASSWORD = admin Connecting to and Querying your SQL Databases Once the engine is running, you will want to use it by: 1. Connecting to you data warehouses 2. Adding context about the data to the engine 3. Querying the data in natural language Connecting to your data warehouses We currently support connections to Postgres, BigQuery, Databricks and Snowflake. You can create connections to these warehouses through the API or at application start-up using the envars. Connecting through the API You can define a DB connection through a call to the following API endpoint /api/v1/database. For example Example 1. Without a SSH connection curl -X 'POST' \ '/api/v1/database' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '{ "db_alias": "my_db_alias_identifier", "use_ssh": false, "connection_uri": "sqlite:///mydb.db" }' Example 2. With a SSH connection curl -X 'POST' \ 'http://localhost/api/v1/database' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '{ "db_alias": "my_db_alias_identifier", "use_ssh": true, "ssh_settings": { "db_name": "db_name", "host": "string", "username": "string", "password": "string", "remote_host": "string", "remote_db_name": "string", "remote_db_password": "string", "private_key_path": "string", "private_key_password": "string", "db_driver": "string" } }' If you need to set up an SSH connection to connect to your DB you need to fill out all the ssh_settings fields By default, DB credentials are stored in database_connection collection in MongoDB. Connection URI information is encrypted using the ENCRYPT_KEY you provided as an environment variable Connecting to supported Data warehouses You can generate the connection_uri parameter in the API call for each of the supported warehouses by using the steps outlined below. Postgres Uri structure: "connection_uri": postgresql+psycopg2://:@:/ Example: "connection_uri": postgresql+psycopg2://admin:123456@foo.rds.amazonaws.com:5432/my-database Databricks Uri structure: "connection_uri": databricks://token:@?http_path=&catalog=&schema= Example: "connection_uri": databricks://token:abcd1234abcd1234abcd1234abcd1234@foo-bar.cloud.databricks.com?http_path=sql/protocolv1/o/123456/123-1234-abcdabcd&catalog=foobar&schema=default Snowflake Uri structure: "connection_uri": snowflake://:@-// Example: "connection_uri": snowflake://jon:123456@foo-bar/my-database/public BigQuery To connect to BigQuery you should create a credential file, this is a json file, you can follow this tutorial to generate it. Once you have your credential json file you can store it inside this project for example I created the folder private_credentials and inside I stored my credential file my-db-123456acbd.json Uri structure: "connection_uri": bigquery:///?credentials_path= Example: "connection_uri": bigquery://v2-real-estate/K2?credentials_path=./private_credentials/my-db-123456acbd.json Adding Context Once you have connected to the data warehouse, you should add context to the engine to help improve the accuracy of the generated SQL. While this step is optional, it is necessary for the tool to generate accurate SQL. Context can currently be added in one of three ways: 1. Scanning the Database tables and columns 2. Adding verified SQL (golden SQL) 3. Adding string descriptions of the tables and columns Scanning the Database The database scan is used to gather information about the database including table and column names and identifying low cardinality columns and their values to be stored in the context store and used in the prompts to the LLM. You can trigger a scan of a database from the POST /api/v1/scanner endpoint. Example below curl -X 'POST' \ '/api/v1/scanner' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '{ "db_alias": "db_name", "table_name": "table_name" }' Get a scanned db Once a database was scanned you can use this endpoint to retrieve the tables names and columns curl -X 'GET' \ '/api/v1/scanned-databases?db_alias=databricks' \ -H 'accept: application/json' Adding verified SQL Sample NL<>SQL pairs (golden SQL) can be stored in the context store and used for few-shot in context learning. In the default context store and NL 2 SQL engine, these samples are stored in a vector store and the closest samples are retrieved for few shot learning. You can add golden SQL to the context store from the POST /api/v1/ golden-record endpoint curl -X 'POST' \ '/api/v1/golden-record' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '[ { "nl_question":"what was the most expensive zip code to rent in Los Angeles county in May 2022?", "sql": "SELECT location_name, metric_value FROM table_name WHERE dh_county_name = '\''Los Angeles'\'' AND dh_state_name = '\''California'\'' AND period_start='\''2022-05-01'\'' AND geo_type='\''zip'\'' ORDER BY metric_value DESC LIMIT 1;", "db":"db_name" } ]' Adding string descriptions In addition to database table_info and golden_sql, you can add strings describing tables and/or columns to the context store manually from the PATCH /api/v1/scanned-db/{db_name}/{table_name} endpoint curl -X 'PATCH' \ '/api/v1/scanned-db/db_name/table_name' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '{ "description": "Tabla description", "columns": [ { "name": "column1", "description": "Column1 description" }, { "name": "column2", "description": "Column2 description" } ] }' Querying the Database in Natural Language Once you have connected the engine to your data warehouse (and preferably added some context to the store), you can query your data warehouse using the POST /api/v1/question endpoint. curl -X 'POST' \ '/api/v1/question' \ -H 'accept: application/json' \ -H 'Content-Type: application/json' \ -d '{ "question": "what was the most expensive zip code to rent in Los Angeles county in May 2022?"", "db_alias": "db_name" }' Replacing core modules The Dataherald engine is made up of replaceable modules. Each of these can be replaced with a different implementation that extends the base class. Some of the main modules are: 1. SQL Generator -- The module that generates SQL from a given natural language question. 2. Vector Store -- The Vector DB used to store context data such as sample SQL queries 3. DB -- The DB that persists application logic. By default this is Mongo. 4. Evaluator -- A module which evaluates accuracy of the generated SQL and assigns a score. In some instances we have already included multiple implementations for testing and benchmarking. Contributing As an open-source project in a rapidly developing field, we are open to contributions, whether it be in the form of a new feature, improved infrastructure, or better documentation. For detailed information on how to contribute, see here. Mongo errors The Mongo installation is configured to store application data in the /dbdata folder. In case you want to wipe the local DB, try completely deleting /dbdata before rebuilding the databases. About No description, website, or topics provided. Resources Readme License Apache-2.0 license Activity Stars 369 stars Watchers 4 watching Forks 14 forks Report repository Releases No releases published Packages 0 No packages published Contributors 5 * @jcjc712 * @aazo11 * @MohammadrezaPourreza * @DishenWang2023 * @jvalacco-dataherald Languages * Python 99.6% * Other 0.4% Footer (c) 2023 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time.