Like Jitsu? Give us a star on ⭐ GitHub!

πŸ“œ Configuration

Configuration UI

πŸ‘©β€πŸ”¬ Extending Jitsu

Overview
Destination Extensions
Source Extensions
API Specs

Jitsu Internals

Data Warehouses

Jitsu supports following Data Warehouses (DWH) as destinations:

This article explains which features are available for each DWH and implementation details.

Postgres SQL

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedIf destination table has primary key:
INSERT INTO destination_table(...) VALUES ... ON CONFLICT ON CONSTRAINT primary_key DO UPDATE SET ...
If destination table has no primary key:
INSERT INTO destination_table(...) VALUES ...
Push events (API keys)
batch mode
SupportedIf destination table has primary key:
In SQL transaction:
1. create tmp_table
2. insert multiple rows into tmp_table
3. insert from tmp_table: INSERT INTO destination_table(...) SELECT ... FROM tmp_table ON CONFLICT ON CONSTRAINT primary_key DO UPDATE SET ...
4. delete tmp_table

If destination table has no primary key:
In SQL transaction:
1. insert multiple rows into destination table
Pull events (Sources)Supported**JavaScript Transformation currently not applied to pulled data

In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. See Push events (API keys) batch mode
User Recognition
stream mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?
User Recognition
batch mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
Same as Push events (API keys) batch mode

Clickhouse

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedINSERT INTO destination_table (...) VALUES ...
Push events (API keys)
batch mode
SupportedINSERT INTO destination_table (...) VALUES ...
Pull events (Sources)Supported**JavaScript Transformation currently not applied to pulled data

1. Delete previous data for current sync interval: ALTER TABLE destination_table DELETE WHERE _time_interval=?
2. See Push events (API keys) batch mode
User Recognition
stream mode
Supported*INSERT INTO destination_table (...) VALUES ...
*Read more about Clickhouse specifics
User Recognition
batch mode
Supported*INSERT INTO destination_table (...) VALUES ...
*Read more about Clickhouse specifics

Redshift

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedINSERT INTO destination_table(...) VALUES ...
Push events (API keys)
batch mode
Supported1. Upload file with data to S3
2. Copy data from S3 to destination table: copy destination_table from 's3://...'
3. Delete data from S3
Pull events (Sources)Supported**JavaScript Transformation currently not applied to pulled data

If destination table has primary key:
In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. create tmp_table
3. insert multiple rows into tmp_table
4. delete from destination table duplicated rows DELETE FROM destination_table using tmp_table where primary_key_columns=? .
5. insert from tmp_table: INSERT INTO destination_table (...) select ... from tmp_table
6. delete tmp_table

If destination table has no primary key:
In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. insert multiple rows into destination table
User Recognition
stream mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?
User Recognition
batch mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
In SQL transaction:
1. create tmp_table
2. insert multiple rows into tmp_table
3. delete from destination table duplicated rows DELETE FROM destination_table using tmp_table where eventn_ctx_event_id=? .
4. insert from tmp_table: INSERT INTO destination_table (...) select ... from tmp_table
5. delete tmp_table

Snowflake

Please carefully read Snowflake docs regarding System Usage & Billing, especially the Virtual Warehouse Credit Usage part.
Remember that using Snowflake destination in stream mode is highly likely to be charged as full-time warehouse usage.
For batch mode, we recommend enabling Automating Warehouse Suspension using ALTER WAREHOUSE query. E.g.:
ALTER WAREHOUSE WAREHOUSE_NAME set AUTO_SUSPEND = 1

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedINSERT INTO destination_table(...) VALUES ...
Push events (API keys)
batch mode
SupportedS3:
1. Upload file with data to S3
2. Copy data from S3 to destination table: copy into destination_table (...) from 's3://...'
3. Delete data from S3

Google Cloud Storage:
1. Upload file with data to GCS
2. Copy data from GCS to destination table: copy into destination_table (...) from @...
3. Delete data from GCS
Pull events (Sources)Supported**JavaScript Transformation currently not applied to pulled data

If destination table has primary key:
In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. create tmp_table
3. insert multiple rows into tmp_table
4. merge into destination table: MERGE INTO destination_table USING (SELECT ... FROM tmp_table) ON primary_key_columns=? WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
5. delete tmp_table

If destination table has no primary key:
In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. insert multiple rows into destination table
User Recognition
stream mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?
User Recognition
batch mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. create tmp_table
3. insert multiple rows into tmp_table
4. merge into destination table: MERGE INTO destination_table USING (SELECT ... FROM tmp_table) ON eventn_ctx_event_id=? WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
5. delete tmp_table

BigQuery

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedInsert using bigquery API
Push events (API keys)
batch mode
Supported1. Upload file with data to GCS
2. Copy data from GCS to destination table using bigquery API
3. Delete data from GCS
Pull events (Sources)Not supported
User Recognition
stream mode
Not supported
User Recognition
batch mode
Not supported

Mysql

FeatureStatusDetails
Push events (API keys)
stream mode
SupportedIf destination table has primary key:
INSERT INTO destination_table(...) VALUES ... ON DUPLICATE KEY UPDATE ...
If destination table has no primary key:
INSERT INTO destination_table(...) VALUES ...
Push events (API keys)
batch mode
SupportedIf destination table has primary key:
In SQL transaction:
1. create tmp_table
2. insert multiple rows into tmp_table
3. insert from tmp_table: INSERT INTO destination_table(...) SELECT * FROM (SELECT ... FROM tmp_table) AS tmp ON DUPLICATE KEY UPDATE ...
4. delete tmp_table

If destination table has no primary key:
In SQL transaction:
1. insert multiple rows into destination table
Pull events (Sources)Supported**JavaScript Transformation currently not applied to pulled data

In SQL transaction:
1. Delete previous data for current sync interval: DELETE FROM destination_table WHERE _time_interval=?
2. See Push events (API keys) batch mode
User Recognition
stream mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?
User Recognition
batch mode
SupportedPrimary key on eventn_ctx_event_id field is required for destination table.
Same as Push events (API keys) batch mode