C3 AI Documentation Home

Write to an External Database

The C3 Agentic AI Platform connects to an external database through Java Database Connectivity (JDBC). Databases such as PostgreSQL, Microsoft SQL Server, and Snowflake use their own JDBC drivers. Sources such as Databricks and Google BigQuery use CData JDBC drivers. To set up a connection, see Configure SQL Sources.

An External Type on that connection writes back to the external table. create and createBatch insert rows, merge and mergeBatch update rows, and remove deletes a row. For a worked example on Google BigQuery, see Connect Application to Google BigQuery. To run your own SQL statements against the database instead, see Grant an Application Direct Access to a JDBC Datastore.

Take care with write operations to virtualized tables. Follow these recommendations:

  • Avoid writing to virtualized tables: Do not perform write operations directly on virtualized tables, because direct writes can lead to complications and unpredictable behavior in data management.

  • Implement primary keys: Always define a primary key on tables in external databases that you are writing to. A primary key is essential for merging or updating records correctly. Without a primary key, you may encounter problems such as incomplete actions or failed writes.

  • Create a set of tests: Define a set of test queries based on the expected write and update patterns of the application. These tests confirm that the application behaves as expected in use.

Implement primary keys in external databases

Use primary keys when you write to external databases, for the following reasons:

  • Data integrity: Primary keys uniquely identify each record in a database table. Primary keys prevent duplicate entries and keep datasets accurate and reliable.

  • Concurrency control: In environments with multiple processes updating data simultaneously, a primary key helps manage concurrency effectively. The primary key enables the system to distinguish between different records, which ensures updates are applied correctly.

  • Predictable behavior: Enforce primary keys to get predictable outcomes in data operations. Without a primary key, you may get unpredictable results, especially when multiple updates occur concurrently.

  • Database design: Primary keys are standard database design. Primary keys make the data in external tables easier to manage.

Consider alternative data ingestion methods

Consider alternative data ingestion methods, such as file-based data integration, which uses file storage integrations to load data into external databases. File-based data integration can involve creating storage integrations from blob storage to external databases. Systems like Snowflake or BigQuery then look for files at specified locations for ingestion.

After defining a file-based integration, you can create an Export job to export Type data to file. For examples of Export jobs, see Import and Export Data.

See also

Was this page helpful?