How to setup clcikhouseDB in proper way?

Level 58

Certainly! Here’s a step-by-step guide to properly set up ClickHouseDB with Laravel, using the recommended cybercog/laravel-clickhouse package, and ensuring you connect via the correct protocol and port.

1. Install the Package

Run:

composer require cybercog/laravel-clickhouse

2. Publish the Config

php artisan vendor:publish --provider="Cog\Laravel\Clickhouse\ClickhouseServiceProvider"

This will create a config/clickhouse.php file.

3. Configure Connection

By default, ClickHouse’s HTTP interface runs on port 8123 and the native TCP interface on port 9000 (not 9004).
Most PHP ClickHouse clients (including cybercog/laravel-clickhouse) use the HTTP interface.

If you must use TCP (9004), you need a client that supports the native protocol, but most Laravel packages use HTTP.

Edit your `.env` file:

CLICKHOUSE_HOST=your-clickhouse-host
CLICKHOUSE_PORT=8123
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USERNAME=default
CLICKHOUSE_PASSWORD=

Edit `config/clickhouse.php`:

Make sure the host, port, and credentials match your server.

'connections' => [
    'main' => [
        'host'     => env('CLICKHOUSE_HOST', '127.0.0.1'),
        'port'     => env('CLICKHOUSE_PORT', 8123),
        'database' => env('CLICKHOUSE_DATABASE', 'default'),
        'username' => env('CLICKHOUSE_USERNAME', 'default'),
        'password' => env('CLICKHOUSE_PASSWORD', ''),
        'https'    => false,
        'timeout'  => 10,
        'settings' => [],
    ],
],

4. Testing the Connection

Try running a simple query:

use Cog\Laravel\Clickhouse\Facades\ClickhouseDB;

$tables = ClickhouseDB::select('SHOW TABLES');
dd($tables);

5. Common Issues

"Received HTTP/0.9 when not allowed"
This error usually means you are trying to connect to the TCP port (9000/9004) using an HTTP client.
Solution: Set the port to 8123 (the HTTP port) in your config.
"Can't ping server"
This can be caused by incorrect host, port, firewall, or credentials.

6. If You Need Native TCP (9004) Support

Most Laravel packages do not support the native TCP protocol out of the box.
You would need to use a lower-level PHP client like smi2/phpClickHouse, but this is not tightly integrated with Laravel.

Example usage:

$client = new ClickHouseDB\Client([
    'host' => 'your-clickhouse-host',
    'port' => 9004, // Native TCP port
    'username' => 'default',
    'password' => '',
]);
$client->database('default');
$tables = $client->showTables();
dd($tables);

Note: You may need to install the ext-sockets PHP extension for native TCP.

Summary

Use cybercog/laravel-clickhouse with port 8123 (HTTP).
If you must use TCP (9004), use a native PHP client, but Laravel integration will be manual.
Double-check your host, port, and credentials.

If you still have issues, please provide your config and error logs for more targeted help!