Aiven for PostgreSQL® as a source for Aiven for ClickHouse®

Deploy an Aiven for PostgreSQL® service and integrate it as a data source with Aiven for ClickHouse® using Terraform. A part of Aiven's Terraform Cookbook.

Subscribe to RSS

You can use a PostgreSQL® database as a data source and Aiven for ClickHouse® to read, transform, and execute jobs using data from the PostgreSQL server. For this purpose, you need to integrate Aiven for PostgreSQL® with Aiven for ClickHouse. Continue reading to learn how to connect these services using Aiven Terraform Provider.

Imagine that you've been collecting IoT measurements from thousands of sensors and storing them in ClickHouse database iot_measurements. Now, you'd like to enrich your metrics by adding the sensor's location to the measurements so that you can filter the metrics by city name. The sensor's location data is available in the sensors_dim database in PostgreSQL.

This recipe calls for the following:

  1. Set up an Aiven for ClickHouse database. Sign up for an Aiven account to do so.
  2. Insert your measurements data into the Aiven for ClickHouse database.
  3. Combine your measurements data in the Aiven for ClickHouse database with the related PostgreSQL dimension database.

Common files

Navigate to a new folder and add the following files:

provider.tf file

terraform {
   required_providers {
      aiven = {
         source  = "aiven/aiven"
         version = ">=4.0.0, < 5.0.0"
      }
   }
}

provider "aiven" {
api_token = var.aiven_api_token
}

You can set environment variable TF_VAR_aiven_api_token for the api_token property so that you don't need to pass the -var-file flag when executing Terraform commands.

variables.tf file

Use it for defining the variables to avoid including sensitive information in source control. The variables.tf file defines the API token, the project name, and the prefix for the service name.

variable "aiven_api_token" {
   description = "Aiven console API token"
   type        = string
}

variable "project_name" {
   description = "Aiven console project name"
   type        = string
}

*.tfvars file

Use it to indicate the actual values of the variables so that they can be passed (with the -var-file= flag) to Terraform during runtime and excluded later on. Configure the var-values.tfvars file as follows:

aiven_api_token     = "<YOUR-AIVEN-AUTHENTICATION-TOKEN-GOES-HERE>"
project_name        = "<YOUR-AIVEN-CONSOLE-PROJECT-NAME-GOES-HERE>"

Services.tf file

The following Terraform script initializes both Aiven for PostgreSQL and Aiven for ClickHouse services, creates the service integration, the source PostgreSQL database, and the Aiven for ClickHouse database.

// Postgres service based in GCP US East
resource "aiven_pg" "postgres" {
   project                 = var.project_name
   service_name            = "postgres-gcp-us"
   cloud_name              = "google-us-east4"
   plan                    = "business-8" // Primary + read-only replica
   maintenance_window_dow  = "monday"
   maintenance_window_time = "10:00:00"
}

// Postgres sensor dimensions database
resource "aiven_pg_database" "sensor_dims" {
   project       = var.project_name
   service_name  = aiven_pg.postgres.service_name
   database_name = "sensor_dims"
}

// ClickHouse service based in the same region
resource "aiven_clickhouse" "clickhouse" {
   project                 = var.project_name
   service_name            = "clickhouse-gcp-us"
   cloud_name              = "google-us-east4"
   plan                    = "startup-16"
   maintenance_window_dow  = "monday"
   maintenance_window_time = "10:00:00"
}

// Sample ClickHouse database that can be used to write and process raw data
resource "aiven_clickhouse_database" "iot_measurements" {
   project      = var.project_name
   service_name = aiven_clickhouse.clickhouse.service_name
   name         = "iot_measurements"
}

// ClickHouse service integration for the PostgreSQL service as a source
resource "aiven_service_integration" "clickhouse_postgres_source" {
   project                  = var.project_name
   integration_type         = "clickhouse_postgresql"
   source_service_name      = aiven_pg.postgres.service_name
   destination_service_name = aiven_clickhouse.clickhouse.service_name
   clickhouse_postgresql_user_config {
   databases {
      database = aiven_pg_database.sensor_dims.database_name
      schema = "public"
   }
   }
}

Execute the files

Run the following command:

terraform init

The init command performs initialization operations to prepare the working directory for use with Terraform. For this recipe, init automatically finds, downloads, and installs the necessary Aiven Terraform Provider plugins.

Run the following command:

terraform plan -var-file=var-values.tfvars

The plan command creates an execution plan and shows the resources to be created (or modified). This command doesn't actually create any resources but gives you a heads-up on what's going to happen next.

If the output of terraform plan looks as expected, run the following command:

terraform apply -var-file=var-values.tfvars

The terraform apply command creates (or modifies) your infrastructure resources.

  • aiven_clickhouse resource creates an Aiven for ClickHouse service with the parameters specified in the services.tf file (project name, cloud name, service plan and service name)
  • aiven_clickhouse_database resource creates a database that can be used to store high-throughput measurement data as well as create new tables and views to process this data.
  • aiven_pg resource creates a highly-available Aiven for PostgreSQL service.
  • aiven_pg_database resource creates the sensor_dims database.
  • aiven_service_integration resource creates the integration between the Aiven for PostgreSQL and Aiven for ClickHouse services.

This results in the creation of the service_postgres-gcp-us_sensor_dims_public database in Aiven for ClickHouse, allowing you to access the sensor_dims database for the postgres-gcp-us service.

Learn more

When you use this recipe, parameters and configurations will vary from those used in this article. For Aiven for PostgreSQL and Aiven for ClickHouse advanced parameters, a related blog, and instructions on how to get started with Aiven Terraform Provider, see Set up your first Aiven Terraform project.

More resources