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

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.

Let’s cook!#

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.

  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.

flowchart LR subgraph Aiven for PostgreSQL id1[(sensors_dim database)] end subgraph Aiven for ClickHouse id2[(iot_measurements database)] id3[(service_postgres-gcp-us_sensor_dims_public database)] end id1 ==> |Service integration| id3

Configure common files#

Expand to check out the relevant common files needed for this recipe.

Navigate to a new folder and add the following files:

  1. provider.tf file

terraform {
  required_providers {
    aiven = {
      source  = "aiven/aiven"
      version = "~> 3.10.0"
    }
  }
}

provider "aiven" {
  api_token = var.aiven_api_token
}

Tip

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.

  1. 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
}

  1. *.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>"

Configure the 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 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 Terraform files#

Expand to check out how to execute the Terraform files.

  1. 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.

  1. 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.

  1. 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.

Check out the results#

  • 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.