syncliteio/synclite-consolidator repository overview

⁠SyncLite - Build Anything Sync Anywhere

SyncLite (https://www.synclite.io⁠) is an open-source, low-code, comprehensive relational data consolidation platform empowering developers to rapidly build data intensive applications for edge, desktop and mobile environments. SyncLite enables performing real-time, transactional data replication and consolidation from a myriad of sources including edge/desktop applications using popular embedded databases (SQLite, DuckDB, Apache Derby, H2, HyperSQL), data streaming applications, IoT message brokers, traditional database systems(ETL) and more into a diverse array of databases, data warehouses, and data lakes, enabling AI and ML use-cases at all three levels: Edge, Fog and Cloud.

{Edge/Desktop Apps} + {SyncLite Logger} ---> {Staging Storage} ---> {SyncLite Consolidator} ---> {Destination DB/DW/DataLakes}

SyncLite enables following scenarios for industry leading databases, data warehouse and data lakes.

⁠Build Sync-Ready Applications with Zero-Coding:

SyncLite's novel CDC replication framework for embedded databases, is designed to empower general purpose data-intensive applications, Gen AI Search/RAG applications for edge, desktop, and mobile environments. It seamlessly integrates with embedded databases like SQLite, DuckDB, Apache Derby, H2, HyperSQL(HSQLDB), enabling Change Data Capture + transactional, real-time data replication and consolidation from them into a diverse range of industry leading databases, data warehouses, and data lakes, enabling global analytics, AI Search and RAG applications.

{Edge/Desktop Apps} + {SyncLite Logger + Embedded Databases} ---> {Staging Storage} ---> {SyncLite Consolidator} ---> {Destination DB/DW/DataLakes}

⁠Build Streaming Applications For Last Mile Data Integration:

SyncLite facilitates development of large-scale data streaming applications through SyncLite Logger, which offers both a Kafka Producer API and SQL API. This allows for the ingestion of massive amounts of data and provides the capability to query the ingested data using the SQL API within applications. Together, SyncLite Logger and SyncLite Consolidator enable seamless last-mile data integration from thousands of streaming application instances into a diverse array of final data destinations.

{Data Streaming Apps} + {SyncLite Logger} ---> {Staging Storage} ---> {SyncLite Consolidator} ---> {Destination DB/DW/DataLakes}

⁠Deploy Smart Database ETL/Replication/Migration Pipelines:

Set up many-to-many, scalable database replication/migration/incremental ETL pipelines from a diverse range of source databases and raw data files into a diverse range of destinations.

{Source Databases} ---> {SyncLite DBReader} ---> {Staging Storage} ---> {SyncLite Consolidator} ---> {Destination DB/DW/DataLakes}

⁠Setup Rapid IoT Data Connectors:

Effortlessly connect numerous MQTT brokers (IoT gateways) to one or more final data destinations.

{IoT Message Brokers} ---> {SyncLite QReader} ---> {Staging Storage} ---> {SyncLite Consolidator} ---> {Destination DB/DW/DataLakes}

⁠SyncLite Components

• SyncLite Logger — JDBC driver, CDC + streaming for embedded DBs.
• SyncLite DB — Standalone sync server that wraps embedded DBs.
• SyncLite Client — CLI for device operations.
• SyncLite DBReader — Configurable incremental DB ETL/replication.
• SyncLite QReader — MQTT/message-broker ingestion.
• SyncLite Consolidator — Central real-time consolidator (this image).
• SyncLite JobMonitor / Validator — Job management and E2E testing utilities.

⁠Using SyncLite Logger

Refer https://github.com/syncliteio/synclite-logger-java/tree/main⁠ to learn about SyncLite logger's usage.

⁠Deploying SyncLite Consolidator

You can get started with this docker image or download the release from GitHub Repo: https://github.com/syncliteio/SyncLite/releases⁠

1. Pull the docker image

docker pull syncliteio/synclite-consolidator:latest

1. Create work directory and stage directory in home directory and start docker container

mkdir -p "$HOME"/synclite/job1/workDir
mkdir -p "$HOME"/synclite/job1/stageDir

docker run \
    -p 8080:8080 \
    -v "$HOME"/synclite:/home/root/synclite \
    --net=host \
    -d \
    --name synclite-consolidator \
    -e "JAVA_TOOL_OPTIONS=-Duser.home=/home/root" \
    syncliteio/synclite-consolidator:latest sh -c "./start.sh && tail -f /dev/null"

If you are doing this deployment on a cloud VM, then add inbound rule for port number 8080 for this VM. Create a local port forwarding from your host to this cloud VM with a fresh command prompt using ssh as below, so that you can launch the SyncLite consolidator at the cloud VM just by accessing localhost:8080

ssh -i <your_pem_file.pem> -L 8080:localhost:8080 user@<CloudVM_PublicIP>

1. Open http://localhost:8080/synclite-consolidator⁠ to launch SyncLite Consolidator dashboard. (Default username/password for Tomcat manager app http://localhost:8080/manager⁠ is synclite/synclite)

2. Open http://localhost:8080/synclite-sample-app⁠ to launch SyncLite sample application.

3. This release has a free license which allows unlimited data consolidation across popular open source/free database systems: PostgreSQL/MySQL/MongoDB/Apache Iceberg Tables/DuckDB/SQLite. Reach out to us at [email protected]⁠ to get your copy of license based on your requirements.

4. Configure and start SyncLite consolidator job in the SyncLite Consolidator application. You can follow through the "Configure Job" wizard reviewing all the default configuration values. Create SyncLite databases/devices from the deployed SyncLite sample application and run SQL workloads. Observe data consolidator in the SyncLite Consolidator dashboard. You can check device specific data consolidation progress on individual device pages (from "List Devices" page), query destination database on the "Analyze Data" page. Refer "SyncLite Consolidator Configurations" section at https://github.com/syncliteio/SyncLite/blob/main/DOCUMENTATION.md⁠ for more details about configuration options for SyncLite consolidator.

5. You can deploy your edge applications using SyncLite logger on different hosts/devices and share the local-stage-directory of your respective SyncLite applications with SyncLite Consolidator host via one of the following staging mechanisms. Refer documentation for setting up following staging storage(s).

• SFTP
• Amazon S3
• MinIO Object Storage Server
• Apache Kafka
• Microsoft OneDrive
• Google Drive
• NFS Sharing
• Local Network Sharing etc.

1. To facilitate staging via SFTP, we have published a docker image synclite-stage-sftp-server which you can pull and start

docker pull syncliteio/synclite-stage-sftp-server:latest

PORT=55555
UPLOAD_USER=synclite
UPLOAD_USER_PASSWORD=synclite
STAGE_DIR_NAME=stageDir

docker run \
        -d \
        -p ${PORT}:22 \
        -v $HOME/synclite/stageDir:/var/uploads/${STAGE_DIR_NAME} \
        --name synclite-stage-sftp-server \
        syncliteio/synclite-stage-sftp-server:latest

(Note: Change default user/password)

If you are doing this deployment on a cloud VM, then add inbound rule for port number 55555 for this VM.

1. Verify that you are able to connect to this SFTP server from one of your client hosts where you plan to run your client applications.

sftp -P 55555 synclite@<SFTP_Server_HostIP>

1. Use the following configuration in synclite_logger.conf file for SyncLite applications to enable device staging on the above started SFTP server

destination-type=SFTP
local-data-stage-directory=<UserHome/synclite/stageDir>
sftp:host=<HostName/IP of the host/VM running the staging SFTP server>
sftp:port=55555
sftp:user-name=synclite
sftp:password=synclite
sftp:remote-data-stage-directory=/stageDir

1. You can perform unlimited volumes of data consolidation into PostgreSQL, MySQL, MongoDB, Apache Iceberg, SQLite or DuckDB destinations, with the free license shipped with this docker image. You can use the docker image synclite-dst-postgresql to bring up a PostgreSQL instance to serve as a destination database. Pull and start docker container:

docker pull syncliteio/synclite-dst-postgresql:latest

DST_USER=synclite
DST_USER_PASSWORD=synclite
DST_DB_NAME=synclitedb

mkdir -p $HOME/synclite/dstDir/postgresql/data

docker run \
        -d \
        -p 5432:5432 \
        --name synclite-dst-postgresql \
        -v $HOME/synclite/demo/workDir/dst/postgresql/data:/var/lib/postgresql/data \
        syncliteio/synclite-dst-postgresql:latest

(Note: Change default user/password)

1. After a successful trial, if you need to perform another fresh trial, stop docker containers and delete contents under /home/synclite/ directory to start a fresh trial of a different scenario etc.

2. If you want to use SyncLite platform as a database ETL/migration/replication solution, launch the synclite-dbreader app by opening: http://localhost:8080/synclite-dbreader⁠, click on "Configure DB Reader" -> "Configure and Start" page, fill the source database connection details and set various configuration options as needed, followed by selection of tables/columns, specification of unique keys (if a table does not have PK defined explicitly, it is highly recommended to specify a logical unique key even though it is not defined in the source database table) and incremental keys for tables for which you need incremental replication to be setup (an incremental key column is a column with monotonically increasing value such as last_update_time, a timestamp column which is updated on each UPDATE/INSERT of a record in that table. SyncLite strongly recommends to also create an index on such a column if it is to be used as incremental key for replication of this table, to speed up replication), on the "Configure DB Tables" page and start the job. Configure and start the SyncLite consolidator job to start the database migration/replication pipeline and explore many more features.

3. If you want to use SyncLite platform as a rapid IOT data connector to read data from an MQTT broker and deliver it to one or more destination databases, launch the synclite-qreader app by opening : http://localhost:8080/synclite-qreader⁠, click on "Configure Reader" -> "Configure and Start "page, fill the MQTT broker details and set various configuration options as needed, followed by schema details for each topic : number of fields, create table statement (in SQLite syntax) on the "Configure Topic Schemas" page and start the job. Configure and start the SyncLite consolidator job to have all your IOT data published to your MQTT broker get consolidated into the destination databases in real-time.

4. You can use the SyncLite JobMonitor utility by launching http://localhost:8080/synclite-jobmonitor⁠, to track all the jobs you have created along with their types, status' and process IDs. You can directly load the jobs from this portal as well.

5. Refer our documentation for more details about the platform usage, tools, configuration options etc. at https://github.com/syncliteio/SyncLite/blob/main/DOCUMENTATION.md⁠

⁠Support

Contact [email protected]⁠ for support and feedback.