README

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"));

ierusalim / php-clickhouse

Maintainers

Details