Java Database Connectivity (JDBC) is an application programming interface which defines how a client may access a database. It provides methods to query and update data in a database, and is oriented toward relational databases.

The integration between JDBC and Togai requires only a one-time setup. Follow the steps below:

​
Prerequisites

​
Create a read-only user

  • Create a read-only user in your database
  • Grant the user access to the tables that you want to sync with Togai
  • Make sure that the user has access to the tables that you want to sync with Togai

​
Connection URI

​
Maria DB

The connection URI for Maria DB is as follows:

jdbc:mariadb://<host>:<port>/<database_name>?user=<username>&password=<password>

​
MS SQL Server

The connection URI for MS SQL Server is as follows:

jdbc:sqlserver://<host>:<port>;databaseName=<database_name>;user=<username>;password=<password>

​
MySQL DB

The connection URI for MySQL DB is as follows:

jdbc:mysql://<host>:<port>/<database_name>?user=<username>&password=<password>

​
Oracle DB

The connection URI for Oracle DB is as follows:

jdbc:oracle:thin:<username>/<password>@<host>:<port>:<database_name>

​
SQL Query

All the flows in this integration require a SQL query to fetch the data from the database. The query should be written in such a way that it returns the data in the format that is expected by the Togai API. The query should also be written in such a way that it returns only the data that you want to sync with Togai.

​
Flows

​
1. Sync customer from Database to Togai

  • Provide the SQL query to fetch the customers from Database: 
    SELECT 
    <customer_id> AS "id", 
    <customer_name> AS "name", 
    <email> AS "primaryEmail", 
    <address_line_1> AS "addressLine1",
    <address_line_2> AS "addressLine2",
    <city> AS "address_city",
    <state> AS "address_state",
    <country> AS "address_country",
    <currency> AS "currency",
    <phone> AS "phone"
FROM <table_name> 
WHERE created_at > '${startTime}' 
AND created_at < '${endTime}'
  • The flow will be available to be triggered on demand from the Togai dashboard
  • It will execute a SQL query to fetch all the customers from Database
  • It will then create the customers in Togai using the Customers API

​
2. Sync events from Database to Togai

  • Provide the SQL query to fetch the events from Database: 
    SELECT 
    <event_id> AS "id", 
    <account_id> AS "accountId", 
    <eventName> AS "schemaName",
    <attributes> AS "attributes",
    <dimensions> AS "dimensions",
    <created_at> AS "timestamp" 
FROM <table_name> 
WHERE created_at > '${startTime}' 
AND created_at < '${endTime}'
  • Make sure that the attributes are JSON array and dimensions columns are of type JSON
  • The flow will be available to be triggered on demand from the Togai dashboard
  • It will execute a SQL query to fetch all the events from Database
  • It will then create the events in Togai using the Events Ingestion API

​
3. Sync usages from Database to Togai

  • Provide the SQL query to fetch the usages from Database: 
    SELECT 
    <event_id> AS "id", 
    <account_id> AS "accountId", 
    <usage_count> AS "usage",
    <created_at> AS "timestamp" 
FROM <table_name> 
WHERE created_at > '${startTime}' 
AND created_at < '${endTime}'
  • The flow will be available to be triggered on schedule from the Togai dashboard
  • It will execute a SQL query to fetch all the usages from Database
  • It will then create the usages in Togai using the Events Ingestion API
SnowflakePostgres