phpClickHouse

Introduction to phpClickHouse

phpClickHouse is a PHP wrapper designed to facilitate interactions with ClickHouse, a modern and fast open-source column-oriented database management system developed for online analytical processing (OLAP) and is widely used for its high performance and efficient storage capabilities. The phpClickHouse library enables developers to communicate easily with ClickHouse through a simplified interface, supporting PHP versions 7.1 and above. It integrates seamlessly, requiring only the cURL extension, and offers various features to streamline database operations.

Features of phpClickHouse

Core Functionality

• No Dependency Except cURL: phpClickHouse operates independently of any extra library dependencies, relying only on cURL to send HTTP requests to ClickHouse servers.

• Parallel and Asynchronous Queries: It supports parallel query execution, allowing users to run multiple queries simultaneously, thus improving performance by efficiently utilizing network and CPU resources.

• Asynchronous Bulk Inserts: The library can handle asynchronous bulk inserts directly from CSV files, making it ideal for working with large datasets.

Compression and Performance Optimization

• HTTP Compression: phpClickHouse enables HTTP compression (using Gzip), which is particularly beneficial for bulk inserts as it reduces data transfer size, saving bandwidth and time.

• Load Balancing with Active Host Discovery: The library can dynamically find active hosts within a ClickHouse cluster, allowing for load balancing and reducing the likelihood of downtime.

Data Operations and Analytics

• Specialized SELECT Clauses: Supports 'WHERE IN' using local CSV files, SQL conditions, and template queries, facilitating complex filtering operations.

• Size and Partition Management: It includes functions to check the size of tables and databases, manage partitions, and perform operations like truncateTable within a cluster.

Advanced Features

• Insert Arrays as Columns: Supports inserting array data as a column type, allowing for the versatile storage of multiple values in a single field.

• Table Size and Node Management: Users can retrieve the size of a table across all nodes and identify the master node replica in a cluster for better data organization and replication management.

Enhancements for Development

• Stream Read/Write & Closure Functions: These functions allow developers to manage data streaming operations more efficiently with direct input and output streams.

• Session Management: Supports session ID management for maintaining stateful connections, which can be crucial for applications that need persistent connections for transaction management.

Installation

To install phpClickHouse, you can simply use Composer, a dependency manager for PHP. The command is as follows:

composer require smi2/phpclickhouse

Getting Started

Here's a quick guide to begin using phpClickHouse.

Connecting to ClickHouse

First, you need to establish a connection to your ClickHouse server with the following configuration:

$config = [
    'host' => '192.168.1.1',
    'port' => '8123',
    'username' => 'default',
    'password' => '',
    'https' => true
];
$db = new ClickHouseDB\Client($config);
$db->database('default');

Basic Operations

Showing Tables:

print_r($db->showTables());

Creating a Table:

$db->write('
    CREATE TABLE IF NOT EXISTS summing_url_views (
        event_date Date DEFAULT toDate(event_time),
        event_time DateTime,
        site_id Int32,
        site_key String,
        views Int32,
        v_00 Int32,
        v_55 Int32
    )
    ENGINE = SummingMergeTree(event_date, (site_id, site_key, event_time, event_date), 8192)
');

Inserting Data:

$stat = $db->insert('summing_url_views',
    [
        [time(), 'HASH1', 2345, 22, 20, 2],
        [time(), 'HASH2', 2345, 12, 9,  3],
    ],
    ['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

Selecting Data:

$statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');

Future Potential

phpClickHouse also supports more advanced functionalities such as executing queries asynchronously, managing clusters, handling errors effectively, and implementing gzip compression for efficient data transfers. This versatility makes it an indispensable tool for developers working with large datasets and requiring robust performance from their database environments.

To explore further, the library provides extensive documentation and examples, enabling users to leverage its full capability in their PHP projects connected with ClickHouse.