HTTP::ClickHouse - Perl driver for ClickHouse
Version 0.061
HTTP::ClickHouse - Perl interface to ClickHouse Database via HTTP.
use HTTP::ClickHouse; my $chdb = HTTP::ClickHouse->new( host => '127.0.0.1', user => 'Harry', password => 'Alohomora', ); $chdb->do("create table test (id UInt8, f1 String, f2 String) engine = Memory"); $chdb->do("INSERT INTO my_table (id, field_1, field_2) VALUES", [1, "Gryffindor", "a546825467 1861834657416875469"], [2, "Hufflepuff", "a18202568975170758 46717657846"], [3, "Ravenclaw", "a678 2527258545746575410210547"], [4, "Slytherin", "a1068267496717456 878134788953"] ); my $rows = $chdb->selectall_array("SELECT count(*) FROM my_table"); unless (@$rows) { $rows->[0]->[0] = 0; } # the query returns an empty string instead of 0 print $rows->[0]->[0]."\n"; if ($chdb->select_array("SELECT id, field_1, field_2 FROM my_table")) { my $rows = $chdb->fetch_array(); foreach my $row (@$rows) { # Do something with your row foreach my $col (@$row) { # Do something print $col."\t"; } print "\n"; } } $rows = $chdb->selectall_hash("SELECT count(*) as count FROM my_table"); foreach my $row (@$rows) { foreach my $key (keys %{$row}){ # Do something print $key." = ".$row->{$key}."\t"; } print "\n"; } ... disconnect $chdb;
This module implements HTTP driver for Clickhouse OLAP database
Create a new connection object with auto reconnect socket if disconnected.
my $chdb = HTTP::ClickHouse->new( host => '127.0.0.1', user => 'Harry', password => 'Alohomora' );
options:
host => 'hogwards.mag', # optional, default value '127.0.0.1' port => 8123, # optional, default value 8123 user => 'Harry', # optional, default value 'default' password => 'Alohomora', # optional database => 'database_name', # optional, default name "default" nb_timeout => 10 # optional, default value 25 second keep_alive => 1 # optional, default 1 (1 or 0) debug => 1 # optional, default 0
First step - select data from the table (readonly). It returns 1 if query completed without errors or 0. Don't set FORMAT in query. TabSeparated is used by default in the HTTP interface.
Second step - fetch data. It returns a reference to an array containing a reference to an array for each row of data fetched.
if ($chdb->select_array("SELECT id, field_1, field_2 FROM my_table")) { my $rows = $chdb->fetch_array(); foreach my $row (@$rows) { # Do something with your row foreach my $col (@$row) { # Do something print $col."\t"; } print "\n"; } }
Fetch data from the table (readonly). It returns a reference to an array containing a reference to an array for each row of data fetched.
my $rows = $chdb->selectall_array("SELECT count(*) FROM my_table"); unless (@$rows) { $rows->[0]->[0] = 0; } # the query returns an empty string instead of 0 print $rows->[0]->[0]."\n";
First step - select data from the table (readonly). It returns 1 if query completed without errors or 0. Don't set FORMAT in query.
if ($chdb->select_hash("SELECT id, field_1, field_2 FROM my_table")) { my $rows = $chdb->fetch_hash(); foreach my $row (@$rows) { # Do something with your row foreach my $key (sort(keys %{$row})){ # Do something print $key." = ".$row->{$key}."\t"; } print "\n"; } }
Fetch data from the table (readonly). It returns a reference to an array containing a reference to an hash for each row of data fetched.
my $rows = $chdb->selectall_hash("SELECT id, field_1, field_2 FROM my_table"); foreach my $row (@$rows) { # Do something with your row foreach my $key (sort(keys %{$row})){ # Do something print $key." = ".$row->{$key}."\t"; } print "\n"; }
Universal method for any queries inside the database, which modify data (insert data, create, alter, detach or drop table or partition). It returns 1 if query completed without errors or 0.
# drop $chdb->do("drop table test if exist"); # create $chdb->do("create table test (id UInt8, f1 String, f2 String) engine = Memory"); # insert $chdb->do("INSERT INTO my_table (id, field_1, field_2) VALUES", [1, "Gryffindor", "a546825467 1861834657416875469"], [2, "Hufflepuff", "a18202568975170758 46717657846"], [3, "Ravenclaw", "a678 2527258545746575410210547"], [4, "Slytherin", "a1068267496717456 878134788953"] );
Disconnects http socket from the socket handle. Disconnect typically occures only used before exiting the program.
disconnect $chdb; # or $chdb->disconnect;
ClickHouse official documentation
https://clickhouse.yandex/reference_en.html
Maxim Motylkov
The closest plans are
Add json data format.
See the Changes file.
Copyright 2016 Maxim Motylkov This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
To install HTTP::ClickHouse, copy and paste the appropriate command in to your terminal.
cpanm
cpanm HTTP::ClickHouse
CPAN shell
perl -MCPAN -e shell install HTTP::ClickHouse
For more information on module installation, please visit the detailed CPAN module installation guide.