ClickHouse Integration

Relevant source files

The following files were used as context for generating this wiki page:

• pkg/clickhouse/source.go
• pkg/clickhouse/config.go
• cmd/pmm-dump/main.go
• pkg/util/util.go
• internal/test/e2e/qan_test.go

Introduction

The ClickHouse integration in pmm-dump is responsible for exporting and importing Query Analytics (QAN) data from Percona Monitoring and Management (PMM) servers. It provides the logic to connect to ClickHouse databases, discover relevant metrics tables, and process data in manageable chunks.

This module ensures that QAN data, stored in ClickHouse's MergeTree engines, can be transferred between PMM instances while maintaining data integrity. It supports filtering via custom SQL WHERE clauses and organizes data into Tab-Separated Values (TSV) format for storage within the dump archive.
Sources: pkg/clickhouse/source.go:37-47, README.md:154-158

Architecture and Components

The integration is built around the Source struct, which implements the dump.Source interface. This allows the ClickHouse module to be treated as a standard data provider or consumer within the transfer process.

Core Structure

The Source manages the database connection, active transactions for writing, and metadata about the table schema.

Connection and Discovery

When a new ClickHouse source is initialized, it performs automatic engine detection to identify the correct metrics table (typically metrics or metrics_local). It also fetches column types to ensure data consistency during TSV processing.

Data Flow: Export and Import

Export Flow (Read)

Data is read in chunks defined by a row limit. The process involves generating a SQL query with appropriate filters and streaming results into TSV format.

1. Chunk Splitting: The time range is evaluated to determine the total row count.
2. Query Generation: A SELECT * query is constructed with WHERE clauses for time and custom filters.
3. TSV Writing: Rows are scanned and written to a buffer using a TSV writer.

Import Flow (Write)

During import, the source uses prepared statements and transactions to ensure high-performance inserts.

1. Transaction Start: A transaction is opened during NewSource.
2. TSV Reading: The TSV content from the dump is parsed.
3. Batch Exec: Data is executed against a prepared INSERT statement.
4. Finalization: The transaction is committed in FinalizeWrites.

Sources: pkg/clickhouse/source.go:175-194, pkg/clickhouse/source.go:206-212

Configuration and Parameters

The ClickHouse integration is configured primarily through environment variables or CLI flags passed via the Config struct.

Parameter	Source	Description
ConnectionURL	PMM_CLICKHOUSE_URL	Full connection string (e.g., clickhouse://user:pass@host:9000/db).
Where	CLI Flag --where	Custom SQL filter applied to the export.
chunk-rows	CLI Flag --chunk-rows	The number of rows to include in a single data chunk (Default: 100,000).

Sources: pkg/util/util.go:76-104, cmd/pmm-dump/main.go:79-84, pkg/clickhouse/config.go:17-20

Internal Logic Details

WHERE Clause Construction

The prepareWhereClause function merges three types of filters:

• Custom filters provided via the --where flag.
• The period_start > [start] timestamp.
• The period_start < [end] timestamp.

func prepareWhereClause(whereCondition string, start, end *time.Time) string {
	var where []string
	if whereCondition != "" {
		where = append(where, fmt.Sprintf("(%s)", whereCondition))
	}
	if start != nil {
		where = append(where, fmt.Sprintf("period_start > %d", start.Unix()))
	}
    // ... logic for 'AND' joining ...
}

Sources: pkg/clickhouse/source.go:211-233

Chunk Creation

The SplitIntoChunks method determines how many files will be created in the dump. It queries the database for the total count of matching records and divides them by the requested chunkRowsLen.
Sources: pkg/clickhouse/source.go:244-275

Conclusion

The ClickHouse integration provides a robust mechanism for handling QAN data. By utilizing TSV for intermediate storage and standard SQL transactions for imports, it ensures that even large datasets can be exported and imported with reliability. Its ability to automatically detect table engines and schemas makes it compatible with various PMM versions and ClickHouse configurations.