Clickhouse Commands

cd /tmp
wget 'https://builds.clickhouse.com/master/macos/clickhouse'
chmod a+x ./clickhouse


./clickhouse server


http://127.0.0.1:8123/play




SHOW databases

CREATE DATABASE IF NOT EXISTS helloworld

CREATE TABLE helloworld.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)


INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )


SELECT * FROM helloworld.my_first_table


./clickhouse client


SELECT * 
FROM helloworld.my_first_table 
ORDER BY timestamp


SELECT * 
FROM helloworld.my_first_table 
ORDER BY timestamp 
FORMAT TabSeparated

exit

https://clickhouse.com/#quick-start

https://clickhouse.com/docs/en/quick-start

how to dump csv

./clickhouse client --query='INSERT INTO helloworld.my_first_table FORMAT CSV' < /Users/str-kwml0020/datasets/data.csv

SELECT * 
FROM helloworld.my_first_table 

Retailer Dump

CREATE DATABASE IF NOT EXISTS retailer

walmart_20220501_1.csv


cd ~/ && ./clickhouse client 

CREATE TABLE retailer.walmart
(
	entryid Int32,
    product_name String,
    siteid Float32,
    retailer_site String
)
ENGINE = MergeTree()
PRIMARY KEY (entryid);

DROP TABLE retailer.walmart;

SELECT * FROM retailer.walmart;

SELECT COUNT(*) FROM retailer.walmart;

SELECT 
product_name,
retailer_site
FROM retailer.walmart
where product_name like '%White%'
;


cd ~/ && ./clickhouse client --query='INSERT INTO retailer.walmart FORMAT CSV' < /Users/str-kwml0020/datasets/walmart_20220501_1.csv

~/.clickhouse


SELECT * 
FROM retailer.walmart 


https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/

CRUD on ClickHouse DB with Python

https://altinity.com/blog/clickhouse-and-python-getting-to-know-the-clickhouse-driver-client

Column-oriented DB:
https://clickhouse.com/docs/en/intro#why-column-oriented-databases-work-better-in-the-olap-scenario

Clickhouse Docker:

https://hub.docker.com/r/yandex/clickhouse-server/

Top C Database:
HBase
https://data-flair.training/blogs/apache-hbase-tutorial/