php-clickhouse

ClickHouse simple access library (http, https)

Class ClickHouseAPI

Class ClickHouseAPI contains simple http/https connector for ClickHouse server and have not dependencies (may be used independently, file src/ClichHouseAPI.php).

API simple requests functions:

• query($sql [,$post_data]) - object-oriented style SQL-query (return $this, throw exceptions)
• getQuery($h_query [, $sess]) - send GET request
• postQuery($h_query, $post_data [, $sess]) - send POST request

Async (parallel) requests:

• Set toSlot(name) before any request, and the request will be launched asynchronously.
• toSlot("name")->query($sql) - start async-$sql-query, results will be written to slot "name"
• Get results from this slot may at any time later:
• slotResults("name") - get results from slot "name".

Server-state functions:

• setServerUrl($url) - set ClickHouse server parameters by url (host, port, etc.)
• getVersion() - return version of ClickHouse server (side effect - detect server features)
• isSupported(feature-name) - true or false depending on the server support features.

Sessions:

• getSession() - get current session_id from options
• setSession([$sess]) - set session_id or generate new session_id and set it

Options:

• setOption($key, $value) - set http-option for all next requests
• getOption($key) - get current http-option value
• delOption($key) - delete http-option (same ->setOption($key, null)

Class ClickHouseQuery

Class ClickHouseQuery contains wrapper for ClickHouseAPI and allow to easily send queries to ClickHouse server and parsing answering data.

Main query-functions for use:

• queryFalse($sql, [post])- for queries that should not return anything. False if ok, or error string.
• queryTrue($sql, [post]) - return false only if error, otherwise return true or response data.
• queryValue($sql, [post]) - send any query and receive all data in one string (false if error)
• queryArray($sql) - for queries returning structured data (usually one or more table rows)
• queryKeyValues(see descr.) - for queries returning 2 columns, first means as key, second as value
• queryInsertArray($table, $fields_names, $fields_set) - insert data into table from array
• queryInsertFile($table, $file, $structure) - insert data from file into table
• queryInsertGzip($table, $file, $format [, $fields]) - insert data from file, using gzip when sending

Class ClickHouseFunctions

Class ClickHouseFunctions based on ClickHouseQuery and ClickHouseAPI and contains functions for simple operations with ClickHouse.

Functions:

• createTableQuick($table, $fields_arr) - create table with specified fields
• sendFileInsert($file, $table) - send TabSeparated-file into table (structure autodetect)
• clearTable($table [, $sess]) - clear table (DROP and re-create)
• dropTable($table [, $sess]) - drop specified table
• renameTable($from_name_or_arr [, $to_name] [, $sess]) - rename tables
• getTableFields($table, ...) - returns [field_name=>field_type] array
• getTableInfo($table [, $extended]) - returns array with info about table
• getTablesList([$db] [,$pattern]) - returns tables list by SHOW TABLES request
• createDatabase($db) - create new database with specified name
• dropDatabase($db) - drop specified database and remove all tables inside
• getDatabasesList() - returns array contained names of existing Databases
• setCurrentDatabase($db [, $sess]) - set current database by 'USE db' request or by option
• getCurrentDatabase([$sess]) - return results of 'SELECT currentDatabase()' or from option
• getUptime() - return server uptime in seconds
• getSystemSettings() - get information from system.settings as array [name=>value]

Example:

<?php
    namespace ierusalim\ClickHouse;

    require "vendor/autoload.php";

    $ch = new ClickHouseFunctions("http://127.0.0.1:8123/");

    echo "ClickHouse version: " . $ch->getVersion();
    if (!$ch->isSupported('query')) {
        die(" Server not ready");
    }
    echo " Server uptime: " . $ch->getUptime();
    
    echo "\n\nDatabases: ";
    print_r($ch->getDatabasesList());

    $ch->setCurrentDatabase("system");
    echo "Tables in '" . $ch->getCurrentDatabase() ."' database:\n";
    print_r($ch->getTablesList());

    $ch->setCurrentDatabase("default");

    $ch->createTableQuick("temptab", [
        'id'   => 'integer',
        'dt' => 'date now()',
        'name' => "char(32) 'example'",
        'email'  => 'string'
    ]);
    
    $ch->queryInsertArray("temptab", null, [
        'id' => 1,
        'email' => 'noreply@github.com'
    ]);
    
    $ch->queryInsertArray("temptab", ['id', 'email', 'name'], [
        [2, 'reply@github.com', 'Andy'],
        [3, null , 'Donald'],
    ]);

    $ch->queryInsertArray("temptab", null, [
        ['id'=>4, 'name'=>'Ronald', 'email'=>'no'],
        ['id'=>5, 'name'=>'', 'email'=>'yes'],
    ]);
    
    $rows = $ch->queryArray("SELECT * FROM temptab");
    print_r($rows);
    
    $name_emails_arr = $ch->queryKeyValues('temptab', 'name, email');
    print_r($name_emails_arr);
    
    print_r($ch->getTableInfo("temptab"));