Dataherald AI

## Sections
• [Introduction](https://app.theneo.io/dataherald/dataherald-ai/introduction.md): You can interact with the Dataherald API through HTTP requests from any language or through our official Python SDK package. To install the official Python SDK package, run the following command: Select... pip install dataherald You can join our Discord community with hundreds of Dataherald AI users for updates, answers to your questions, and other support!
• [Authentication](https://app.theneo.io/dataherald/dataherald-ai/authentication.md): The Dataherald API uses API keys for authentication. These API keys are at an organization level. You can visit the API Keys page in the Admin Console and retrieve a key that you can use to make API calls. You should store you API keys securely and do not expose it in client side code. If you feel a key has been exposed please revoke it immediately from the Admin Console and report it to us. All API requests should include your API key in an X-API-Key HTTP header as follows Select... 'X-API-Key: dh-2d3b40……………………………………………………………………60af' For example to make a call to get a list of database connections for your organization the call will be as follows: Select... curl -X 'GET' \ 'https://stage.api.dataherald.ai/api/database-connections' \ -H 'accept: application/json' \ -H 'X-API-Key: dh-2d3b40……………………………………………………………………60af' If you are using the Python SDK, you can set your key as an environment variable Select... export DATAHERALD_API_KEY= dh-2d3b40……………………………………………………………………60af While you can pass your API key to the Dataherald Client object as a keyword argument, this is not recommended since your API key will be stored with your source control Select... dh_client = Dataherald(base_url = 'https://api.dataherald.ai', api_key='dh-2d3b40……………………………………………………………………60af' , timeout=360)
• [Getting Started](https://app.theneo.io/dataherald/dataherald-ai/getting-started.md): Dataherald is a hosted NL-to-SQL API that enables any developer to embed natural language querying to their relational database. You can access Dataherald directly through the API or through the Python SDK . In this guide, we’ll walk through the steps for getting started with the Dataherald Python SDK. You can also follow this quick 2-minute video guide to get set up. We highly recommend joining our Discord community for support. Install and Instantiate Dataherald First, you have to sign up for an account . Once that is done, you can log into the Dataherald admin console and generate an API key. Save this key, as it won’t be recorded anywhere unencrypted. Then you can start building the app. First, install the Dataherald SDK. Select... !pip install dataherald You can then instantiate the Dataherald client. Note that you should never save your API key with your code. The code below assumes the API key is saved as an environment variable. Select... import os from dataherald import Dataherald API_KEY = os.environ['API_KEY'] dh_client = Dataherald(api_key=API_KEY) Create a Database Connection The next step is to give the tool access to your relational data warehouse. Dataherald uses a standard ODBC protocol to connect to the relational data warehouse of your choice. It currently supports: PostGres Databricks Snowflake BigQuery AWS Athena MotherDuck In order to create a database connection, all you need is a connection URI for your database. Once you have that, you can create the database connection from the admin console’s Databases tab or through the SDK. For additional instructions on generating the connection URI (such as using an SSH connection or if your warehouse requires additional credentials like BigQuery) please refer to this page . To create a database connection using the SDK use the following command: Select... alias = "<Insert a human-readable name for the connection>" use_ssh = False # Dataherald provides support for SSH connections connection_uri = "<Insert connection URI here for your data warehouse>" db_connection = dh_client.database_connections.create( alias=alias, use_ssh=use_ssh, connection_uri=connection_uri ) Scan Tables The next step is to scan the tables in the database which allows Dataherald to build context about the schema and dataset. During the scan Dataherald will: Read the schema to collect the table and column schema including data types Identify categorical columns and their values Determine relationships between tables and more You can specify tables to be scanned or just have the tool scan all available tables. Please note this is an asynchronous process and can take some time to complete. Select... table_names = [ <List of tables to scan> ] dh_client.table_descriptions.sync_schemas( db_connection_id=db_connection.id, table_names=table_names ) Ask a Question The engine is finally set up to query the database in natural language. There are three main resources involved in querying the database in natural language: Prompts: the natural language text or question SQL-Generations: the AI generated SQL query to answer the natural language prompt NL-Generations: a natural language answer to the prompt While these resources are linked and build on each other (ie you need to create a Prompt before a SQL-Generation before an NL-Generation), the API and SDK provide methods where you can create these in a single call. The following example creates a SQL query for a given question. Select... from dataherald.types.sql_generation_create_params import Prompt prompt = Prompt(text="<Insert Question here>", db_connection_id="<Insert Database Connection ID here>") response = dh_client.sql_generations.create(prompt=prompt) To access the generated SQL: Select... print(response.sql)
• [Support](https://app.theneo.io/dataherald/dataherald-ai/support.md): For technical support, join our Discord community ! With hundreds of active users and Dataherald support active, the Discord community is the best place to get quick answers to questions about using DHAI. For billing support, contact support@dataherald.com .
• [Metadata](https://app.theneo.io/dataherald/dataherald-ai/metadata.md): All Dataherald resources, including database-connections, sql-generation and nl-generation have a metadata parameter. You can use this parameter to attach key-value pairs to Dataherald objects. You can use the metada to store additional application specific information on an object. For example, you could store user's name that initiated the query or store the corresponding unique identifier from your system for the Dataherald object. The Dataherald backend does not do anything with the metadata other than storing and returning it. The following API call stores the key value pair along with sql-generation object Select... curl -X 'POST' \ 'https://api.dataherald.ai/api/prompts/sql-generations' \ -H 'accept: application/json' \ -H 'X-API-Key: dh-2d3…………………………………………………………………………………………………………..af' \ -H 'Content-Type: application/json' \ -d '{ "finetuning_id": "6597665e4641238c6e9cb", "metadata": {"user": "Jane Doe"}, "prompt": { "text": "Los Angeles Home price trends", "db_connection_id": "6596fd97c97ab8dasdfrs5" } }' To do the same through the Python SDK Select... import dataherald dh_client = Dataherald(timeout=360) prompt = Prompt(text='Los Angeles Home price trends', db_connection_id='6596fd97c97ab8dasdfrs5') dh_client.sql_generations.create(prompt=prompt, metadata={'user':'Jane Doe'})
• [Database Connection](https://app.theneo.io/dataherald/dataherald-ai/database-connection.md): The Database Connection object defines connections between Dataherald and your relational database. Through the APIs and SDK methods that interact with this resource you can create connections to new databases providing necessary authentication information, including SSH information if a tunnel needs to set up to access the database. We currently provide support for connections to Postegres , Databricks , BigQuery , Snowflake and MotherDuck . Since the Dataherald engine uses SQLAlchemy and an ODBC driver to connect to your database, other databases might work as well, though they are not officially supported.
  • [Add database connection](https://app.theneo.io/dataherald/dataherald-ai/database-connection/add-database-connection.md): Creates a new database connection. All configuration information (including authentication information) to establish the connection to the database. If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. You'll need an ODBC connection URI to establish the database connection. The tool currently supports connections to: Postgres, Snowflake, Databricks, Redshift, BigQuery, AWS Athena, and MotherDuck. The connection URI formats for each are as follows: Postgres: postgresql+psycopg2://<user>:<password>@<host>:<port>/<db-name> Snowflake: snowflake://<user>:<password>@<organization>-<account-name>/<database>/<schema> Databricks: databricks://token:<token>@<host>?http_path=<http-path>&catalog=<catalog>&schema=<schema-name> BigQuery: bigquery://<project>/<database> AWS Athena: awsathena+rest://<aws_access_key_id>:<aws_secret_access_key>@athena.<region_name>.amazonaws.com:443/<schema_name>?s3_staging_dir=<s3_staging_dir>&work_group=primary MotherDuck: duckdb:///md:<database_name>?motherduck_token=<your_token> MySQL/MariaDB: mysql+pymysql://<user>:<password>@<host>:<port>/<db-name> MS SQL Server: mssql+pymssql://<user>:<password>@<host>:<port>/<db-name> If you want to use a different schema than the dbo default one, execute the next command: Select... ALTER USER your_username WITH DEFAULT_SCHEMA = your_schema_name; ClickHouse: clickhouse+http://<user>:<password>@<host>:<port>/<db_name>?protocol=https Redshift: redshift+psycopg2://<user>:<password>@<host>:<port>/<database> SSH connection To establish an SSH connection, please follow these steps: Download our public SSH key . Add the downloaded SSH key to the autorized_keys file located on your server. In the body param set use_ssh as true Set the connection_uri param following this structure: <db-driver>://<user>:<password>@<host>:<port>/<db-name> In the body param set ssh_setting By completing these actions, you will grant our server the necessary access for a successful connection.
  • [Retrieve database connection](https://app.theneo.io/dataherald/dataherald-ai/database-connection/retrieve-database-connection.md): Retrieve a single database connection object from its id.
  • [List all database connections](https://app.theneo.io/dataherald/dataherald-ai/database-connection/list-all-database-connections.md): Retrieve a list of all your organization's database connections to the Dataherald engine.
  • [Update database connection](https://app.theneo.io/dataherald/dataherald-ai/database-connection/update-database-connection.md): Update configuration information for a specific database connection object
• [Golden SQL](https://app.theneo.io/dataherald/dataherald-ai/golden-sql.md): Golden SQL are sample natural language to SQL pairs for a given database. These can be used as training data to finetune a model, or as samples to be passed to the LLM in few-shot prompting.
  • [Add golden SQL](https://app.theneo.io/dataherald/dataherald-ai/golden-sql/add-golden-sql.md): Add Natural Language <> SQL samples for a relational database.
  • [Retrieve golden SQL](https://app.theneo.io/dataherald/dataherald-ai/golden-sql/retrieve-golden-sql.md): Get a specific Golden SQL given its id . T
  • [List all golden SQLs](https://app.theneo.io/dataherald/dataherald-ai/golden-sql/list-all-golden-sqls.md): Get a list of all Golden SQL that have been added. Includes pagination and ordering arguments.
  • [Delete golden SQL](https://app.theneo.io/dataherald/dataherald-ai/golden-sql/delete-golden-sql.md): Delete a specific Golden SQL given its id .
• [Instruction](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1.md): SQL Generation is the main object that represents a translation of a natural language question to SQL for a given relational database given the context that was added to the Dataherald engine. Other than metadata, these objects are immutable and cannot be modified. SQL Generation objects are created from a Prompt object and there can be multiple SQL Generation objects for a single Prompt object. While SQL Generations can only be created for a Prompt object, you can create the SQL Generation and Prompt in a single call to the Dataherald Engine. For best results a SQL Generation should be created with a fine-tuned model. However, you can still create a SQL Generation without a fine-tuned model, in which case the agent will use relevant Golden SQL for few shot prompting.
  • [Add instruction](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1/add-instruction.md): Add a database level instruction to be used in the SQL generation.
  • [Retrieve instruction](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1/retrieve-instruction.md): Retrieve a specific Instruction given its id .
  • [List all instructions](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1/list-all-instructions.md): List all Instructions in the engine for a given Database Connection.
  • [Update instruction](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1/update-instruction.md): Update a specific Instruction given its id .
  • [Delete instruction](https://app.theneo.io/dataherald/dataherald-ai/sql-generation-1/delete-instruction.md): Remove an Instruction given its id . This will no longer be passed to the LLM during SQL generation.
• [Table Description](https://app.theneo.io/dataherald/dataherald-ai/table-description.md): After successfully creating a Database Connection, you need to upload the schema into Dataherald you can start generating SQL from natural language. The Dataherald engine provides a scanner which can automatically scan the database and retrieve the following: Table and column schema Identify low cardinality columns and their values Mine SQL query history for synthetic Golden SQL generation The information in the table descriptions are used in during fine-tuning and SQL generation Schema Linking step (where the agent identifies the relevant tables and columns to use in the query) and in the SQL generation pipeline. In addition to information collected automatically in the scan, you can manually add descriptions to tables and columns.
  • [Scan tables](https://app.theneo.io/dataherald/dataherald-ai/table-description/scan-tables.md): You can use this endpoint to generate Table Descriptions. Since this process can be time-consuming, it runs in the background. We highly recommend that you specify only the tables you want to scan within the table_names parameter. Otherwise, it will scan all the tables in your database. During the scan, Dataherald: stores the schema, including column names, column data types, and the DDL for each table identifies low cardinality columns and unique values in these columns retrieves sample rows: SELECT * FROM table_name LIMIT 3;
  • [Retrieve table description](https://app.theneo.io/dataherald/dataherald-ai/table-description/retrieve-table-description.md): Retrieve a specific table description given its id .
  • [List all table descriptions](https://app.theneo.io/dataherald/dataherald-ai/table-description/list-all-table-descriptions.md): List all Table Descriptions associated with a given Database Connection. You can filter based using db_connection_id or table_name . If these are not selected, all Table Descriptions will be returned.
  • [Update table description](https://app.theneo.io/dataherald/dataherald-ai/table-description/update-table-description.md): Update a table description given its id .
• [Finetuning](https://app.theneo.io/dataherald/dataherald-ai/finetuning.md): In order to achieve best NL-to-SQL performance, you need to fine-tune a model specific to your own relational data. The Finetuning object represents a model that has been fine-tuned from a set of Golden SQL objects. Once a Finetuning object is succesfully created, you can use it in creating SQL Generation and NL Generation objects. While you can create a Finetuning object from as little as 10 Golden SQL, for the finetuned model to yield accurate NL-to-SQL the training set will need at least hundreds of samples with covering various tables in the relational database.
  • [Create a finetuning job](https://app.theneo.io/dataherald/dataherald-ai/finetuning/create-a-finetuning-job.md): Add a new Finetuning model from Golden SQLs you have uploaded. If you do not specify a list of golden records, the model will use all available golden records in the associated Database Connection. Finetunings are specific to a given Database Connection. The default model_provider and model_name for finetuning are “openai” and "gpt-3.5-turbo-1106", respectively. Finetunings take a few hours before they can be used to generate SQL. These calls are asynchronous.
  • [Retrieve a finetuning job](https://app.theneo.io/dataherald/dataherald-ai/finetuning/retrieve-a-finetuning-job.md): Retrieve a specific Finetuning object. If the status is SUCCESS , the model is ready to use.
  • [List all finetuning jobs](https://app.theneo.io/dataherald/dataherald-ai/finetuning/list-all-finetuning-jobs.md): List all Finetuning objects for your Database Connection.
  • [Cancel a finetuning job](https://app.theneo.io/dataherald/dataherald-ai/finetuning/cancel-a-finetuning-job.md): Cancel a specific Finetuning job. If the job has already succeeded, this call will not affect the object.
• [Prompt](https://app.theneo.io/dataherald/dataherald-ai/prompt.md): A Prompt object represents a natural language question from a given relational database. Prompt objects are immutable and cannot be modified once created. SQL Generations and NL Generations are created from Prompt objects.
  • [Create prompt](https://app.theneo.io/dataherald/dataherald-ai/prompt/create-prompt.md): Prompt objects store questions asked in natural language to the database.
  • [Retrieve prompt](https://app.theneo.io/dataherald/dataherald-ai/prompt/retrieve-prompt.md): Retrieve a specific Prompt given its id .
  • [List all prompts](https://app.theneo.io/dataherald/dataherald-ai/prompt/list-all-prompts.md): List all Prompts in the engine. Includes pagination and ordering arguments.
• [SQL Generation](https://app.theneo.io/dataherald/dataherald-ai/sql-generation.md): SQL Generation is the main object that represents a translation of a natural language question to SQL for a given relational database given the context that was added to the Dataherald engine. Other than metadata, these objects are immutable and cannot be modified. SQL Generation objects are created from a Prompt object and there can be multiple SQL Generation objects for a single Prompt object. While SQL Generations can only be created for a Prompt object, you can create the SQL Generation and Prompt in a single call to the Dataherald Engine. For best results a SQL Generation should be created with a fine-tuned model. However, you can still create a SQL Generation without a fine-tuned model, in which case the agent will use relevant Golden SQL for few shot prompting.
  • [Generate SQL](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/generate-sql.md): Use the DHAI agent to generate a SQL query to answer the question posed in the Prompt. This endpoint will also create a Prompt object.
  • [Generate SQL for a Prompt ID](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/generate-sql-for-a-prompt-id.md): Use the DHAI agent to generate a SQL query to answer the question posed in the Prompt.
  • [Retrieve SQL generation](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/retrieve-sql-generation.md): Retrieve a specific SQL generation given its id .
  • [List all SQL generations](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/list-all-sql-generations.md): List all SQL generations in the engine. Includes pagination and ordering arguments.
  • [List all SQL generations for a Prompt ID](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/list-all-sql-generations-for-a-prompt-id.md): List all SQL generations in the engine for a given Prompt ID.
  • [Execute SQL](https://app.theneo.io/dataherald/dataherald-ai/sql-generation/execute-sql.md): Execute a SQL query from a given SQL generation ID against the database connection. Includes a max_rows argument to limit the size of the returned data.
• [NL Generation](https://app.theneo.io/dataherald/dataherald-ai/nl-generation.md): A NL Generation object is a natural language answer to a given Prompt object from the associated relational database. An NL Generation is created from an SQL Generation object from the query results after executing the query. There can be multiple NL Generations for each SQL Generation (and these values can be different) as the underlying data can change. While a Prompt object and SQL Generation object must exist in order to create a NL Generation object, all three can be created with a single call to the Dataherald Engine.
  • [Generate NL Response](https://app.theneo.io/dataherald/dataherald-ai/nl-generation/generate-nl-response.md): For a given SQL Generation ID, use the LLM to generate a natural language response to the Prompt question. This endpoint will use the data pulled by the SQL query to inform its response.
  • [Generate SQL+NL Response](https://app.theneo.io/dataherald/dataherald-ai/nl-generation/generate-sql-nl-response.md): For a given Prompt ID, use the LLM to generate a the SQL query and natural language response to the Prompt question. This endpoint create a SQL Generation object and an NL Generation object. For a given SQL Generation ID, use the LLM to generate a natural language response to the Prompt question. This endpoint will use the data pulled by the SQL query to inform its response.
  • [Generate Prompt+SQL+NL Response](https://app.theneo.io/dataherald/dataherald-ai/nl-generation/generate-prompt-sql-nl-response.md): Use the LLM to generate a the SQL query and natural language response to the Prompt question. This endpoint create a Prompt, SQL Generation object, and an NL Generation object.
  • [Retrieve NL generation](https://app.theneo.io/dataherald/dataherald-ai/nl-generation/retrieve-nl-generation.md): Retrieve a specific NL generation given its id .
  • [List all NL generations](https://app.theneo.io/dataherald/dataherald-ai/nl-generation/list-all-nl-generations.md): List all SQL generations in the engine. Includes pagination and ordering arguments.
• [Guides](https://app.theneo.io/dataherald/dataherald-ai/guides.md): This section contains guides on how to set up and get the best NL-to-SQL performance for your database.
  • [How to Connect to your Database](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database.md): Upon making your account, the first step will be to create a connection to your RDBMS. Currently, Dataherald supports out-of-the-box connections to: PostgreSQL, BigQuery, Snowflake, Databricks, AWS Athena, and MotherDuck. However, you can connect to any database that supports an ODBC connection. If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Navigate to the guide for your data warehouse provider to get started.
    • [AWS Athena](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/aws-athena.md): Requirements You'll need the following pieces of information to establish a connection to your AWS Athena database. aws_access_key_id and aws_secret_access_key for authentication region_name and schema_name the s3_staging_directory for the data You can then construct the connection URI accordingly: awsathena+rest://<aws_access_key_id>:<aws_secret_access_key>@athena.<region_name>.amazonaws.com:443/<schema_name>?s3_staging_dir=<s3_staging_directory>&work_group=primary . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a Athena database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = "<Insert human-readable name for the DB connection>", use_ssh = False, connection_uri = "<Insert connection URI created above>" ) To create a Athena database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = "<Insert human-readable name for the DB connection>", use_ssh = True, connection_uri = "<Insert connection URI created above>", ssh_settings = { 'host': "<Insert SSH hostname here>", 'port': "<Insert SSH port here>", 'username': "<Insert SSH username here>", 'password': "<Insert SSH password here>" } ) Through the Admin Console Follow these steps to create a AWS Athena database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select AWS Athena in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community . Connection problems You may encounter this error if: You incorrectly input your URI You need a SSH tunnel
    • [BigQuery](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/bigquery.md): Requirements You'll need the following pieces of information to establish a connection to your BigQuery database. project and database from BigQuery A credentials file, created by following these steps Navigate to your project in the BigQuery Cloud Console . On the top right, click on the Ellipses —> Project Settings . Click on Service Accounts in the left pane, then the “+ CREATE SERVICE ACCOUNT” button. Provide a service account name, and optionally, a service account description. Click on “CREATE AND CONTINUE”. Add the “BigQuery User” and “BigQuery Data Viewer” roles, then click “CONTINUE” and then “DONE”. You'll be redirected to the Service Accounts landing page. Click into the service account you just created, and switch to the Keys tab. Click on ADD KEY —> Create new key . Select JSON for the key type, then click on “CREATE”. The JSON credentials file will be downloaded to your computer. You can then construct the connection URI accordingly: bigquery://<project>/<database> . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a BigQuery database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = False, connection_uri = "<Insert connection URI created above>", bigquery_credential_file_content = { # Insert the contents of the credentials file created above } ) To create a BigQuery database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = True, connection_uri = "<Insert connection URI created above>", bigquery_credential_file_content = { # Insert the contents of the credentials file created above }, ssh_settings = { 'host': “<Insert SSH hostname here>”, 'port': <Insert SSH port here>, 'username': “<Insert SSH username here>”, 'password': “<Insert SSH password here>” } ) Through the Admin Console Follow these steps to create a BigQuery database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select BigQuery in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. Upload the credentials file under “Service Account Key File”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community . Connection problems You may encounter this error if: You incorrectly input your URI
    • [Databricks](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/databricks.md): Requirements You'll need the following pieces of information to establish a connection to your Databricks database. token created by following the following steps, and your Databricks instance's hostname Navigate to the Databricks console . Click on your username on the top right —> User Settings . Under Settings, click on Developer and then on “Manage” in the Access Tokens section. Click on “Generate new token”. Specify a comment explaining its purpose, and specify no length of time for the Lifetime. Copy the generated token and save it somewhere, as it will not be persisted or shown elsewhere. http_path of one of your clusters, from the Advanced Options —> JDBC/ODBC tab the catalog and schema you want to connect to in Databricks You can then construct the connection URI accordingly: databricks://token:<token>@<hostname>?http_path=<http-path>&catalog=<catalog>&schema=<schema-name> . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a Databricks database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = False, connection_uri = "<Insert connection URI created above>" ) To create a Databricks database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = True, connection_uri = "<Insert connection URI created above>", ssh_settings = { 'host': “<Insert SSH hostname here>”, 'port': <Insert SSH port here>, 'username': “<Insert SSH username here>”, 'password': “<Insert SSH password here>” } ) Through the Admin Console Follow these steps to create a Databricks database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select Databricks in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community . Connection problems You may encounter this error if: You incorrectly input your URI
    • [MotherDuck](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/motherduck.md): Requirements You'll need the following pieces of information to establish a connection to your MotherDuck database. token for authentication database_name You can then construct the connection URI accordingly: duckdb:///md:<database_name>?motherduck_token=<your_token> . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a MotherDuck database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = False, connection_uri = "<Insert connection URI created above>" ) To create a MotherDuck database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = True, connection_uri = "<Insert connection URI created above>", ssh_settings = { 'host': “<Insert SSH hostname here>”, 'port': <Insert SSH port here>, 'username': “<Insert SSH username here>”, 'password': “<Insert SSH password here>” } ) Through the Admin Console Follow these steps to create a MotherDuck database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select MotherDuck in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community .
    • [Postgres](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/postgres.md): Requirements You'll need the following pieces of information to establish a connection to your PostgreSQL database. username and password for authentication hostname and port database_name You can then construct the connection URI accordingly: postgresql+psycopg2://<username>:<password>@<hostname>:<port>/<database_name> . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a PostgreSQL database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = False, connection_uri = "<Insert connection URI created above>" ) To create a PostgreSQL database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = True, connection_uri = "<Insert connection URI created above>", ssh_settings = { 'host': “<Insert SSH hostname here>”, 'port': <Insert SSH port here>, 'username': “<Insert SSH username here>”, 'password': “<Insert SSH password here>” } ) Through the Admin Console Follow these steps to create a PostgreSQL database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select PostgreSQL in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community . Connection problems You may encounter this error if: You incorrectly input your URI You need a SSH tunnel
    • [Snowflake](https://app.theneo.io/dataherald/dataherald-ai/guides/how-to-connect-to-your-database/snowflake.md): Requirements You'll need the following pieces of information to establish a connection to your Snowflake database. username and password for authentication organization_id and account_name from Snowflake database and schema You can then construct the connection URI accordingly: snowflake://<username>:<password>@<organization>-<account-name>/<database>/<schema> . If your data source is behind a firewall, you need to allow access from the IP address 52.7.14.213 in your database firewall/Security Groups. Through the SDK Follow the sample code to create a Snowflake database connection. Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = False, connection_uri = "<Insert connection URI created above>" ) To create a Snowflake database connection with an SSH connection: Select... #Create Dataherald client API_KEY = "<Insert your API key here>" dh_client = Dataherald(api_key = API_KEY) #Create database connection dh_client.database_connections.create( alias = “<Insert human-readable name for the DB connection>”, use_ssh = True, connection_uri = "<Insert connection URI created above>", ssh_settings = { 'host': “<Insert SSH hostname here>”, 'port': <Insert SSH port here>, 'username': “<Insert SSH username here>”, 'password': “<Insert SSH password here>” } ) Through the Admin Console Follow these steps to create a Snowflake database connection through the UI. In the Databases tab, click on the “+ Add Database” button. In the pop-up, select Snowflake in the dropdown menu. Enter a human-readable alias for the DB connection under “Alias”. Enter the connection URI generated above under “Connection URI”. [Optional] Click on the “Connect through SSH tunnel” if required for your use case. Enter the SSH details. Click on “Add Database”. The popup should display the following message, confirming that your database connection was created: “Connection successful! To begin using this database for queries, select the tables you wish to scan and synchronize with the platform.” You can now move on to scanning your tables! If you see an “Oops! Something went wrong” pop-up, please check the above details. If the problem persists, you can reach out to the #support channel under “Hosted API” in Dataherald’s Discord community . Connection problems You may encounter this error if: You incorrectly input your URI You need a SSH tunnel
  • [Improving NL-to-SQL Performance](https://app.theneo.io/dataherald/dataherald-ai/guides/improving-nl-to-sql-performance.md): With a basic setup, the engine now can write SQL, but the performance in terms of accuracy or latency is lackluster. This is because the engine still has limited business context and has not been trained on the specific underlying dataset. Dataherald allows you to address these by: Context Tools — Providing table/column descriptions and database instructions to guide SQL generation. Adding Golden SQLs — Providing sample Natural Language to SQL pairs. These can be used in few-shot prompting or to fine-tune your own LLM. Fine-tuning a model — Training and deploying an LLM fine-tuned to your own dataset. This unlocks the best performance in terms of accuracy and speed.
    • [Context Tools](https://app.theneo.io/dataherald/dataherald-ai/guides/improving-nl-to-sql-performance/context-tools.md): Simply put, a stand-alone NL-to-SQL LLM cannot function effectively at enterprise scale without a structure of business context supporting it. Dataherald AI provides a variety of contextual solutions to their agent to ensure strong enterprise performance, improving steadily as the tool learns the requisite context to serve effectively. Table and Column Descriptions The first and most basic context tool available to our agent is table and column descriptions. Human-readable text explaining the purpose of the data, format expectations, intended use, etc for each table or column helps the agent better understand how and when to use them. The following examples of descriptions can help the agent better determine when to query certain tables and how to use various columns. median_sale_price table: “Contains the median sale price aggregated at the monthly level of residential properties by property type and geo” property_type column: “The property type associated with this record; categorical values (apartment, condo, townhouse, etc) and an “All Residential” category aggregating across all property types” To add table descriptions: Select... table_id = <Insert table ID here> description = <Insert text description of table here> dh_client.table_descriptions.update( id=table_id, description=description ) To add column descriptions: Select... table_id = table_description.id column_descriptions = [ {'name': <Column name>, 'description': <Column description>}, {'name': <Column name>, 'description': <Column description>}, … ] dh_client.table_descriptions.update( id=table_id, columns=column_descriptions ) Database-Level Instructions This context tool is particularly useful for setting rules and guidelines for any SQL query written against that particular database. A good candidate for a database-level instruction is: A rule that must always be applied in particular situations to queries written against a database, such as: “The value column holds aggregated or averaged data already. Never run aggregate functions (sum, count, avg) on the value column.” A guidance particular to the database that may not be clear to the agent through golden SQLs, such as: “If the geo_type is ‘city’ or ‘county’, always filter on the relevant dh_state_name as well.” A standard SQL snippet that the tool should never deviate from, such as: “When filtering for apartments, filter for both “apartment” and “condo”: (property_type = ‘apartment’ OR property_type = ‘condo’). Unique intricacies in the data that are not immediately relevant from a schema scan, such as: “Data is available for the last complete month. When searching for ‘current’ or ‘latest’ data, or ‘now’, search for period_start values associated with the start of last month.” To add a description: Select... db_connection_id = <Insert DB connection ID here> instruction = <Insert instruction here> dh_client.instructions.create( db_connection_id=db_connection_id, instruction=instruction )
    • [Golden Records](https://app.theneo.io/dataherald/dataherald-ai/guides/improving-nl-to-sql-performance/golden-records.md): Golden records are verified natural language <> SQL query pairings. When a SQL query is generated in response to a question, technical users have the option of “verifying” the correctness of the query, which adds it to the list of golden records. As more golden records are added, the accuracy of the tool improves. Users can also manually upload pairings to kickstart the performance of the NL-to-SQL model. When a question is asked, Dataherald peruses the complete list of golden records and identifies a small subset that are highly correlated with the asked question. Through this process, Dataherald learns how to better write future queries. For our use case, if we verify multiple questions about “active” listings with the correct WHERE clauses, we can expect Dataherald to capture that expectation for future questions about active listings. A key value of the collection of golden records is that Dataherald can then use these pairings to further fine-tune an open-source LLM for your particular use case, reducing latency and improving accuracy as the training dataset gets larger. The Golden SQL need to be in the following format: Select... [ { 'prompt_text': "", 'sql': "", 'db_connection_id': "" }, ...] If you have this dataset available, you can upload it to Dataherald using the following code snippet (assuming it is in a file called `training_data.json`): Select... with open('training_data.json', 'r') as jsonfile: samples = json.loads(jsonfile.read()) created_golden_sqls = dh_client.golden_sqls.upload(body=samples) If you do not have such a training set, you can use the Admin Console UI and the SQL generated by the agent as a starting point to verify and edit the SQL and add it to the Golden SQLs through the UI by marking it as Verified.
    • [Fine-tuning](https://app.theneo.io/dataherald/dataherald-ai/guides/improving-nl-to-sql-performance/fine-tuning.md): In order to get state of the art NL-to-SQL performance, you need to fine-tune your own NL-to-SQL model. Although Dataherald works without a fine-tuned model, the accuracy and latency will be considerably worse than when you deploy a fine-tuned model. Dataherald works with various LLMs, but for the best performance you should fine-tune GPT-4. Dataherald is one of a limited number of companies in the world with access to GPT-4 fine-tuning. Dataherald uses Golden SQLs you have uploaded for the fine-tuning. While you can fine-tune a model with as little as 10 Golden SQL, in order for the fine-tuned model to provide value, you need a diverse training set with at least 20–30 samples per table and also samples that show how to join across tables. You can create a fine-tuned model with the following command. Please note fine-tuning is an asynchronous task that will take a few hours to complete. Select... base_llm = { "model_provider": "openai", "model_name": "gpt-4",} alias = "<Insert a human-readable name for the fine-tuned model>" dh_client.finetunings.create( db_connection_id=db_connection.id, base_llm=base_llm, alias=alias ) Further model parameters for fine-tuning can be found in the docs . Once the model is fine-tuned its STATUS will be SUCCESS and it can be used in SQL generation. Select... from dataherald.types.sql_generation_create_params import Prompt prompt = Prompt( text="<Insert Question here>", db_connection_id="<Insert Database Connection ID here>", ) result = dh_client.sql_generations.create( prompt=prompt, finetuning_id='<Insert fine-tuned model ID here>' )