Livesync from S3 to Timescale Cloud

You use livesync to synchronize CSV and Parquet files from an S3 bucket to your Timescale Cloud service in real time. Livesync runs continuously, enabling you to leverage Timescale Cloud as your analytics database with data constantly synced from S3. This lets you take full advantage of Timescale Cloud's real-time analytics capabilities without having to develop or manage custom ETL solutions between S3 and Timescale Cloud.

Expand image

You can use livesync to synchronize your existing and new data. Here's what livesync can do:

• Sync data from an S3 bucket instance to a Timescale Cloud service:

  • Use glob patterns to identify the objects to sync.
  • Livesync uses the objects returned for subsequent queries. This efficient approach means files are synced in lexicographical order.
  • Livesync watches an S3 bucket for new files and imports them automatically. It runs on a configurable schedule and tracks processed files.
  • For large backlogs, livesync checks every minute until caught up.

• Sync data from multiple file formats:

  • CSV: files are checked for compression in .gz and .zip format, then processed using timescaledb-parallel-copy.
  • Parquet: files are converted to CSV, then processed using timescaledb-parallel-copy.

• Livesync offers an option to enable a hypertable during the file-to-table schema mapping setup. You can enable columnstore and continuous aggregates through the SQL editor once livesync has started.

• Livesync offers a default 1-minute polling interval. This means that Timescale Cloud checks the S3 source every minute for new data. You can customize this interval by setting up a cron expression.

Livesync for S3 continuously imports data from an Amazon S3 bucket into your database. It monitors your S3 bucket for new files matching a specified pattern and automatically imports them into your designated database table.

Note: livesync for S3 currently only syncs existing and new files—it does not support updating or deleting records based on updates and deletes from S3 to tables in a Timescale Cloud service.

#livesync in Timescale Community

To follow the steps on this page:

• Create a target Timescale Cloud service with time-series and analytics enabled.

  You need your connection details.

• Ensure access to a standard Amazon S3 bucket containing your data files.

  Directory buckets are not supported.

• Configure access credentials for the S3 bucket.

  • The following credentials are supported:

    • IAM Role.

      • Configure the trust policy. Set the:

        • Principal: arn:aws:iam::142548018081:role/timescale-s3-connections.

        • ExternalID: set to the Timescale Cloud project and Timescale Cloud service ID of the service you are syncing to in the format <projectId>/<serviceId>.

          This is to avoid the confused deputy problem.

      • Give the following access permissions:

        • s3:GetObject.
        • s3:ListBucket.

    • Public anonymous user.

• CSV:

  • Maximum file size: 1 GB

    To increase this limit, contact sales@timescale.com

  • Maximum row size: 2 MB

  • Supported compressed formats:

    • .gz
    • .zip

  • Advanced settings:

    • Delimiter: the default character is ,, you can choose a different delimiter
    • Skip header: skip the first row if your file has headers

• Parquet:

  • Maximum file size: 1 GB
  • Maximum row group uncompressed size: 200 MB
  • Maximum row size: 2 MB

• Sync iteration:

  To prevent system overload, livesync tracks up to 100 files for each sync iteration. Additional checks only fill empty queue slots.

To sync data from your S3 bucket to your Timescale Cloud service using Timescale Console:

And that is it, you are using livesync to synchronize all the data, or specific files, from an S3 bucket to your Timescale Cloud service in real time.