Delete data

[sagarsumit]

I had gone through phpClickhouse Client but I could not find how to delete data using PHP. Could you help me out in this?

[isublimity]

CH not like delete data.

I use drop partitions on my production CH. See my example code in #130

[isublimity]

If need delete use:

$client->write("Delete....");

If you use cluster, need delete per node

[sagarsumit]

If need delete use:

$client->write("Delete....");

If you use cluster, need delete per node

It is not working.

[sagarsumit]

CH not like delete data.

I use drop partitions on my production CH. See my example code in #130

I want to delete some records without dropping like we delete in MySQL.

[isublimity]

Check you version CH support Delete command. Ch not recommended use delete, use drop partition

[sagarsumit]

Check you version CH support Delete command. Ch not recommended use delete, use drop partition

CH use delete command using ALTER TABLE <table_name> DELETE WHERE filters

[isublimity]

I recomend use for delete in Cli, on server. Delete command can work many time, more 10...20 MINS!

[sagarsumit]

I recomend use for delete in Cli, on server. Delete command can work many time, more 10...20 MINS!

But my requirement is in the PHP script. Any solution with ALTER query or any other method?

[bun4uk]

@mitzsu

$query = 'ALTER TABLE db.ebobo ON CLUSTER my_cluster DELETE WHERE id=123456';
$client->write($query);

[13DaGGeR]

@bun4uk, did you check your solution?

For me, ALTER TABLE ... DELETE works fine if executed from console, but does not work with this library. Here is testcase, based on queries from README:

$db = new ClickHouseDB\Client($config);
$db->database('default');
$db->write('
    DROP TABLE IF EXISTS summing_url_views;
');

$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)
');
$db->insert('summing_url_views',
	[
		[time(), 'HASH1', 2345, 22, 20, 2],
		[time(), 'HASH2', 2345, 12, 9,  3],
		[time(), 'HASH3', 5345, 33, 33, 0],
	],
	['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

$db->write('
    ALTER TABLE summing_url_views DELETE WHERE site_id = 2345
');

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

echo $statement->count() === 1 ? 'OK' : 'FAIL';

[arukompas]

@13DaGGeR - The ALTER TABLE summing_url_views DELETE ... query in ClickhouseDB is asynchronous.

In your script, you're expecting the row count to be updated immediately, but the asynchronous deletion has not yet been processed.

Read more here - https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations