Set up Aiven for ClickHouse® data source integrations

Connect your Aiven for ClickHouse® service to another Aiven service or an external data source to make data available in ClickHouse.

Prerequisites

• You are familiar with the limitations listed in About Aiven for ClickHouse® data service integration.
• You have an organization, a project, and an Aiven for ClickHouse service.
• You have access to the Aiven Console.

Create an Apache Kafka integration

tip

Learn about managed databases integrations.

Make Apache Kafka data available in Aiven for ClickHouse using the Kafka engine:

1. Log in to the Aiven Console, and go to an organization and a project.

2. From Services, select an Aiven for ClickHouse service to integrate with a data source.

3. On the service's Overview page, click Integrations in the sidebar.

4. On the Integrations page, go to the Data sources section and click Apache Kafka.

   The Apache Kafka data source integration wizard opens and displays available data sources. If no data sources are listed, click Create service (for Aiven-managed sources) or Add external endpoint (for external sources) to create one.

5. In the Apache Kafka data source integration wizard:

   1. Select a data source to integrate with, and click Continue.

      note

      If the data source is not in the list, click one of the following:

      • Create service: Creates an Aiven-managed data service for integration
      • Create external endpoint: Makes your external data source available for integration

   2. Create tables where your Apache Kafka data will be available in Aiven for ClickHouse. Enter Table name, Consumer group name, Topics, Data format, and Table columns. Click Save table details.

      note

      • You can have up to 400 such tables for receiving and sending messages from multiple topics.

      • To query the tables, use the following statement:

        SELECT *
        FROM APACHE_KAFKA_RESOURCE_NAME.APACHE_KAFKA_TABLE_NAME

      • To set up Data format, see Formats for Aiven for ClickHouse® - Aiven for Apache Kafka® data exchange.

      • For more integration configuration options, see Update Apache Kafka integration settings.

   3. Click Enable integration > Close.

Create a PostgreSQL integration

tip

Learn about managed databases integrations.

Make PostgreSQL data available in Aiven for ClickHouse using the PostgreSQL engine:

1. Log in to the Aiven Console, and go to an organization and a project.

2. From Services, select the Aiven for ClickHouse service to integrate with a data source.

3. On the service's Overview page, click Integrations in the sidebar.

4. On the Integrations page, go to the Data sources section and click PostgreSQL.

   The PostgreSQL data source integration wizard opens and displays a list of external data sources or Aiven-managed data services available for integration. If no data sources are listed, click Create service (for Aiven-managed sources) or Add external endpoint (for external sources) to create one.

5. In the PostgreSQL data source integration wizard:

   1. Select a data source to integrate with, and click Continue.

      note

      If the data source is not in the list, click one of the following:

      • Create service: Creates an Aiven-managed data service for integration
      • Create external endpoint: Makes your external data source available for integration

   2. Optionally, create databases where your PostgreSQL data will be available in Aiven for ClickHouse. Enter Database name and Database schema.

      tip

      You can query the created databases using the following statement:

      SELECT *
      FROM POSTGRESQL_RESOURCE_NAME.POSTGRESQL_TABLE_NAME

      note

      You can create integration databases later. For example, fine your integration on the Integrations page and click Actions > Edit database.

   3. Click Enable integration > Close.

Use managed-credentials integrations

tip

Learn about managed credentials integrations.

Set up a managed-credentials integration and create tables to make data available through the integration. Access your stored credentials.

Create managed-credentials integrations

1. Log in to the Aiven Console, and go to an organization and a project.

2. From Services, select an Aiven for ClickHouse service to integrate with a data source.

3. On the service's Overview page, click Integrations in the sidebar.

4. On the Integrations page, go to the Data sources section and click ClickHouse Credentials.

   The ClickHouse credentials integration wizard opens and displays a list of external data sources or Aiven-managed data services available for integration. If no data sources are listed, click Create service (for Aiven-managed sources) or Add external endpoint (for external sources) to create one.

5. In the ClickHouse credentials integration wizard:

   1. Select a data source to integrate with.

      note

      If the data source is not in the list, click one of the following:

      • Create service: Creates an Aiven-managed data service for integration
      • Create external endpoint: Makes your external data source available for integration

   2. Click Enable integration.

   3. Optionally, click Test connection > Open in query editor > Execute.

      Alternative

      You can test the connection later from your Aiven for ClickHouse service's Integrations page. Find the credentials integration and click Actions > Test connection.

   4. Click Close.

Create tables

Create tables using table engines, for example, the PostgreSQL engine:

CREATE TABLE default.POSTGRESQL_TABLE_NAME
(
   `float_nullable` Nullable(Float32),
   `str` String,
   `int_id` Int32
)
ENGINE = PostgreSQL(postgres_credentials);

tip

For details on how to use different table engines for integrations with external systems, see the upstream ClickHouse documentation.

Access credentials storage

Depending on your data source type, you can access your credentials storage by passing your data source name in the following query:

PostgreSQL data source

SELECT *
FROM postgresql(
   `service_POSTGRESQL_SOURCE_NAME`,
   database='defaultdb',
   table='tables',
   schema='information_schema'
)

MySQL data source

SELECT *
FROM mysql(
   `service_MYSQL_SOURCE_NAME`,
   database='mysql',
   table='slow_log'
)

Amazon S3 data source

SELECT * FROM s3(
   `endpoint_S3_SOURCE_NAME`,
   filename='*.csv',
   format='CSVWithNames')

warning

When you run a managed-credentials query with a typo, the query fails with an error message related to grants.

View data source integrations

1. Log in to the Aiven Console, and go to an organization and a project.

2. From Services, select an Aiven for ClickHouse service.

3. On the service's page, go to one of the following:

   • Overview in the sidebar > Integrations
   • Integrations in the sidebar

Stop data source integrations

warning

When you terminate a data source integration, you disconnect from the data source. Aiven for ClickHouse removes all related databases and configuration.

1. Log in to the Aiven Console, and go to an organization and a project.

2. From Services, select the Aiven for ClickHouse service where you want to stop the integration.

3. On the service's page, do one of the following:

   • Click Overview > Integrations, find the integration to stop, and click Actions > Disconnect.
   • Click Integrations, find the integration to stop, and click Actions > Disconnect.

This terminates the integration and deletes all corresponding databases and configuration.

Related pages

• Aiven for ClickHouse® data service integration
  • Managed credentials integration
  • Managed databases integration
• Manage Aiven for ClickHouse® integration databases
• Integrate your Aiven for ClickHouse® service