Clickhouse Query Builder

The Clickhouse Builder can be integrated into Laravel or Lumen applications.

1. Register Provider:
   • For Laravel, add \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class to config/app.php providers.
   • For Lumen, register it in bootstrap/app.php using $app->register(...).
2. Configure Database: Define connections in config/database.php. You can configure single servers, multiple servers (for load balancing), or clusters.
3. Usage: Access the builder via DB::connection('clickhouse')->query(). You can also use using(), usingRandomServer(), onCluster(), or usingServerWithTag() to control which server or cluster handles the query.

// Laravel connection example
'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'servers' => [
            ['host' => 'ch-00.domain.com', 'port' => '8123', 'database' => 'db', 'username' => 'user', 'password' => 'pass'],
            ['host' => 'ch-01.domain.com', 'port' => '8123', 'database' => 'db', 'username' => 'user', 'password' => 'pass'],
        ]
    ]
]

// Querying
DB::connection('clickhouse')->onCluster('test')->select(...);
DB::connection('clickhouse')->usingServerWithTag('tag')->select(...);

You can use local files as temporary tables to handle large datasets (like huge lists of IDs) that are too large for standard WHERE IN clauses.

1. Attach a file: Use addFile() with a TempTable instance before calling your query. This allows the builder to automatically detect and attach the file for use in whereIn, prewhereIn, havingIn, or join statements.
2. Insert files: Use insertFile() for a single file or insertFiles() to insert multiple files asynchronously.
3. Memory tables: Use the values() and format() methods combined with the into_memory_table() helper to create a temporary Memory engine table from a local file.

// Using a local file in a query via TempTable
$builder->addFile(new TempTable('numbersTable', 'numbers.tsv', ['number' => 'UInt64'], Format::TSV));
$builder->table(raw('numbers(0,1000)')->whereIn('number', 'numbersTable')->get());

// Inserting a file
$builder->table('test')->insertFile(['date', 'userId'], 'test.tsv', Format::TSV);

// Asynchronous batch insertion
$builder->table('test')->insertFiles(['date', 'userId'], [
    'test-1.tsv', 
    'test-2.tsv'
], Format::TSV);

// Using the into_memory_table helper
$builder->table('test')->values('test.tsv')->format(Format::TSV);
into_memory_table($builder, [
    'date' => 'Date', 
    'userId' => 'UInt64'
]);

The join() method allows you to join tables. It supports various join types (e.g., left, any, all, inner) and can use USING syntax by passing an array of columns. You can also join against a subquery by passing a Builder or a closure.

Additionally, there are specialized helper methods for common joins like anyLeftJoin, allLeftJoin, allInnerJoin, and anyInnerJoin. For ClickHouse array joins, use arrayJoin() or leftArrayJoin().

// Standard join with USING
$builder->from('table')->join('another_table', 'any', 'left', ['column1', 'column2'], true, 'alias');

// Join with a subquery closure
$builder->from('table')->join(function ($join) {
    $join->query()->select('column1', 'column2')->from('table2');
}, 'any', 'left', ['column1', 'column2']);

// Helper methods
$builder->from('table')->anyLeftJoin('table', ['column']);
$builder->from('table')->allInnerJoin('table', ['column']);

// Array joins
$builder->from('test')->arrayJoin('someArr');
$builder->from('test')->leftArrayJoin('someArr');

The select() method allows you to specify columns for your query. You can pass multiple arguments, an array of column names, or an associative array where keys are columns and values are aliases.

For complex expressions, you can pass a closure that receives a Column instance. This allows for advanced functions, subqueries, and custom logic. Note that column functions are currently under development and may be unstable.

// Basic selection
$builder->select('column', 'column2', 'column3 as alias');
$builder->select(['column', 'column2', 'column3 as alias']);
$builder->select(['column', 'column2', 'column3' => 'alias']);

// Using a closure for complex expressions
$builder->select(function ($column) {
    $column->name('time')->sumIf('time', '>', 10);
});

// Using a closure for subqueries
$builder->select(function ($column) {
    $column->as('alias')
           ->query()
           ->select('column')
           ->from('table');
});

The builder provides where(), prewhere(), and having() methods for filtering.

• Basic usage: Pass column, operator, and value. If the operator is omitted, = is used. If the value is an array, IN is used.
• Closures: Passing a closure to where() wraps the internal statements in parentheses. If the closure defines a from(), it becomes a subquery.
• Subqueries: Passing a Builder or a closure as the value parameter converts it into a subquery.
• Dictionary looks: Use whereDict() to perform lookups in ClickHouse dictionaries. You can pass an Identifier instance for complex keys to ensure proper tuple conversion.

// Basic where
$builder->from('table')->where('column', '=', 'value');
$builder->from('table')->where('column', ['val1', 'val2']); // Uses IN

// Nested where with parentheses
$builder->from('table')->where(function ($query) {
    $query->where('column1', 'value')->where('column2', 'value');
});

// Subquery in where
$builder->from('table')->where('column', 'IN', function ($query) {
    $query->select('column')->from('table');
});

// Dictionary lookup
$builder->whereDict('dict', 'attribute', 'key', '=', 'value');

// Complex dictionary key with Identifier
$builder->whereDict('dict', 'attribute', [new Identifier('column'), 'string value'], '=', 'value');

The Tinderbox\ClickhouseBuilder\Integrations\Laravel\Connection class requires a configuration array to initialize the Clickhouse client. You can define individual servers or named clusters.

Each server entry should include:

• host: The hostname of the Clickhouse server.
• port: The port number.
• database: The default database.
• username: The database user.
• password: The user's password.
• options: (Optional) An array containing protocol (e.g., 'http') and tags (an array of strings used for server selection).

Additionally, you can provide transportOptions to configure the underlying transport (e.g., HttpTransport).

$config = [
    'servers' => [
        [
            'host' => '127.0.0.1',
            'port' => 8123,
            'database' => 'default',
            'username' => 'default',
            'password' => '',
            'options' => [
                'protocol' => 'http',
                'tags' => ['replica-1']
            ]
        ]
    ],
    'transportOptions' => [
        // options for HttpTransport
    ]
];