Skip to main content
Version: 0.18.3

Databases

Upload Data from a Database

Connect to a Database

Setting up a database connection is a better option for larger datasets or datasets that change over time. To connect to a database, you can create a connection file by clicking the + New button on the homepage, then select Connection.

connection button

DataChat supports the following database systems:

  • BigQuery
  • Databricks
  • Microsoft SQL Server
  • MySQL
  • PostgreSQL
  • Presto
  • Snowflake
  • ConnectionString
note

If you working from DataChat's cloud platform (apps.datachat.ai) and are connecting to a read-only database that's behind a firewall, refer to Add DataChat IP for Read-Only Databases for more information on allowing access to read-only databases.

To add a connection to your BigQuery database:

  1. Give the connection a name.
  2. In the Project field, enter the name of your BigQuery project.
  3. In the Database field, enter the name of your BigQuery database.
  4. Upload your credentials file. Refer to Google's documentation for more information on creating and downloading this file.

If you're not going to use a staging project for your intermediate tables, click Save. Otherwise, continue to the next section.

Add a Staging Project

When working in DataChat, you will create intermediate tables that need to be written to the database. If you want to limit write access to your primary project, you can create a staging project to which those intermediate tables can be written. First, create a new project. Then:

  1. In the Staging Project field, enter the name of the BigQuery project you want to use to stage your data.
  2. In the Staging Database Name field, enter the name of the BigQuery database you want to use to stage your data.
  3. Upload the credentials file used for that database in the Staging Credential File field.
  4. Optionally, set the connection to be read-only.
  5. Test your connection, then click Save.

big query connection window

Edit a Database Connection

To edit an existing database connection:

  1. From the home screen, navigate to the Connections page.
  2. Select the connection to edit.
  3. Click the Edit Connection button: edit connection button
  4. Make your edits and click Save.

Rename a Database Connection

To rename an existing database connection:

  1. From the home screen, navigate to the Connections page.
  2. Select the connection to rename.
  3. Click the Rename Connection button: rename connection button
  4. Rename your connection and click Save.

Delete a Database Connection

To delete an existing database connection:

  1. From the home screen, navigate to the Connections page.
  2. Select the connection to delete.
  3. Click the Delete Connection button: delete connection button

Use the Load Button

tip

If there are a large number of tables in your database, DataChat recommends specifying which tables you want to use when connecting to the database. Refer to the reference page for Load for more information.

To load data from a database:

  1. Click the Load button to open the Load Data form.
  2. Select a database from the recommendations list.
  3. Click Submit.

Or, if your database isn't listed:

  1. Click the Load button to open the Load Data form.
  2. Click Load Data From My Databases.
  3. Enter the database from which you'd like to load data.
  4. Optionally, enter how you'd like to load the data.
  5. Click Submit.

the database form

You can also enter the following utterance in the chat box: Load data from the database <connection name> where <connection name> is the name you specified in step 3.

note

For databases with write access, all tables are loaded into DataChat, which might be a time-intensive operation. Once the tables are loaded, you can click a link to list all the loaded datasets.

Browse a Read-Only Database

For read-only databases, when you enter: Load data from the database <connection name>

DataChat displays a table containing the datasets available within the database. Click on a dataset to load it, or specify which tables within the dataset to use in your session, as detailed in the reference page for Load.

Load More Tables from a Database

After connecting to a database and loading some tables, you can load more data from the database by specifying the table or loading a group of tables with the same schema. Refer to the reference page for Load.

Share a Connection

You can Share your existing database connection with another user in your organization. The recipient can then use the Connect skill to directly connect to the database.

Read-Only Databases

For read-only databases, when you enter Load data from the database <connection name>, DataChat displays a table containing the datasets available within the database. Click on a dataset to load it, or specify which tables within the dataset to use in your session, as detailed in the reference page for Load.

Best Practices

To expedite future load time for datasets containing less than 10M rows, you can load the data and save it as a snapshot. Enter Load data from <read-only database name> and force transfer, followed by Save the current snapshot as <snapshot name>.

Optionally, you can schedule this workflow to refresh on a given time basis to ensure that the snapshot remains updated. Refer to the Schedule and Refresh skills references pages for more information.