### MongoDB Setup for Example Documents

Source: https://clickhouse.com/docs/sql-reference/table-functions/mongodb

These commands set up a MongoDB user, create a collection, and insert sample documents for demonstration purposes.

```javascript
db.createUser({user:"test_user",pwd:"password",roles:[{role:"readWrite",db:"test"}]})
```

```javascript
db.createCollection("my_collection")
```

```javascript
db.my_collection.insertOne(
    { log_type: "event", host: "120.5.33.9", command: "check-cpu-usage -w 75 -c 90" }
)
```

```javascript
db.my_collection.insertOne(
    { log_type: "event", host: "120.5.33.4", command: "system-check"}
)
```

--------------------------------

### Setup for Array and Map Column Examples

Source: https://clickhouse.com/docs/sql-reference/functions/string-search-functions

Defines a table `log` with array and map columns, including text indexes for tags and map keys/values, to be used in subsequent examples.

```sql
CREATE TABLE log (
    id UInt32,
    tags Array(String),
    attributes Map(String, String),
    INDEX idx_tags (tags) TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_attributes_keys mapKeys(attributes) TYPE text(tokenizer = array),
    INDEX idx_attributes_vals mapValues(attributes) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO log VALUES
    (1, ['clickhouse', 'clickhouse cloud'], {'address': '192.0.0.1', 'log_level': 'INFO'}),
    (2, ['chdb'], {'embedded': 'true', 'log_level': 'DEBUG'});

```

--------------------------------

### Install marimo with SQL support

Source: https://clickhouse.com/docs/integrations/marimo

Install marimo with SQL support and the clickhouse_connect library. Then, start the marimo editor.

```bash
pip install "marimo[sql]" clickhouse_connect
marimo edit clickhouse_demo.py
```

--------------------------------

### Quick Start: Execute a Query with C#

Source: https://clickhouse.com/docs/integrations/csharp

Demonstrates how to create a ClickHouse client and execute a simple query to get the server version. The client should typically be managed as a singleton.

```csharp
using ClickHouse.Driver;

// Create a client (typically as a singleton)
using var client = new ClickHouseClient("Host=my.clickhouse;Protocol=https;Port=8443;Username=user");

// Execute a query
var version = await client.ExecuteScalarAsync("SELECT version()");
Console.WriteLine(version);

```

--------------------------------

### Install and Run ClickHouse CLI

Source: https://clickhouse.com/docs/deployment-modes

Installs the ClickHouse CLI, sets the latest ClickHouse version as default, starts a local server, and opens the client. Ensure you have curl installed.

```bash
# Install the CLI
curl https://clickhouse.com/cli | sh

# Install the latest ClickHouse, set it as your default, and symlink the clickhouse binary onto your PATH
clickhousectl local use latest
clickhousectl local server start
clickhousectl local client

```

--------------------------------

### Initial Table Setup and Data Insertion

Source: https://clickhouse.com/docs/sql-reference/statements/create/table

This snippet sets up a database and a table with initial structure and inserts a single row. It serves as a starting point for demonstrating REPLACE operations.

```sql
CREATE DATABASE base 
ENGINE = Atomic;

CREATE OR REPLACE TABLE base.t1
(
    n UInt64,
    s String
)
ENGINE = MergeTree
ORDER BY n;

INSERT INTO base.t1 VALUES (1, 'test');

SELECT * FROM base.t1;

```

--------------------------------

### Example: Creating sample files on cluster nodes

Source: https://clickhouse.com/docs/sql-reference/table-functions/fileCluster

Illustrates how to create sample CSV files on each node of a cluster using the file table function. This setup is a prerequisite for using fileCluster effectively.

```sql
INSERT INTO TABLE FUNCTION file('file1.csv', 'CSV', 'i UInt32, s String') VALUES (1,'file1'), (11,'file11');
```

```sql
INSERT INTO TABLE FUNCTION file('file2.csv', 'CSV', 'i UInt32, s String') VALUES (2,'file2'), (22,'file22');
```

--------------------------------

### Quick Start Profiling Example

Source: https://clickhouse.com/docs/chdb/debugging/profiling

Enable profiling, run operations, and then print the profiling report. Ensure the 'chdb' library is installed and data is available.

```python
from chdb import datastore as pd
from chdb.datastore.config import config, get_profiler

# Enable profiling
config.enable_profiling()

# Run your operations
ds = pd.read_csv("large_data.csv")
result = (ds
    .filter(ds['amount'] > 100)
    .groupby('category')
    .agg({'amount': 'sum'})
    .sort('sum', ascending=False)
    .head(10)
    .to_df()
)

# View report
profiler = get_profiler()
print(profiler.report())
```

--------------------------------

### Create Sample Products Table

Source: https://clickhouse.com/docs/guides/developer/stored-procedures-and-prepared-statements

Sets up a sample 'products' table with product details for use in subsequent examples.

```sql
-- Create the products table
CREATE TABLE products (
    product_id UInt32,
    product_name String,
    price Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY product_id;

-- Insert sample data
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 899.99),
(2, 'Wireless Mouse', 24.99),
(3, 'USB-C Cable', 12.50),
(4, 'Monitor', 299.00),
(5, 'Keyboard', 79.99),
(6, 'Webcam', 54.95),
(7, 'Desk Lamp', 34.99),
(8, 'External Hard Drive', 119.99),
(9, 'Headphones', 149.00),
(10, 'Phone Stand', 15.99);
```

--------------------------------

### Start the SlackBot Agent

Source: https://clickhouse.com/docs/use-cases/AI/MCP/ai-agent-libraries/slackbot

Run this command to start the SlackBot agent. Ensure you have uv installed.

```bash
uv run main.py
```

--------------------------------

### Install IPython

Source: https://clickhouse.com/docs/chdb/guides/clickhouse-local

Installs IPython, an enhanced interactive Python shell, for running commands in this guide.

```bash
pip install ipython
```

--------------------------------

### Create Sample Database

Source: https://clickhouse.com/docs/knowledgebase/projection_example

Initializes a new database for testing projection usage.

```sql
CREATE database db1;
```

--------------------------------

### Create and Backup Database

Source: https://clickhouse.com/docs/engines/database-engines/backup

Example demonstrating the creation of a database, tables, insertion of data, and then creating a backup of the database to a specified disk.

```sql
CREATE DATABASE test_database;

CREATE TABLE test_database.test_table_1 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_1 VALUES (0, 'test_database.test_table_1');

CREATE TABLE test_database.test_table_2 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_2 VALUES (0, 'test_database.test_table_2');

CREATE TABLE test_database.test_table_3 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_3 VALUES (0, 'test_database.test_table_3');

BACKUP DATABASE test_database TO Disk('backups', 'test_database_backup');
```

--------------------------------

### Show Create Table Example

Source: https://clickhouse.com/docs/sql-reference/statements/insert-into

Demonstrates how to view the schema of a table, which is useful before inserting data.

```sql
SHOW CREATE insert_select_testtable;
```

--------------------------------

### Install otelgen with Homebrew

Source: https://clickhouse.com/docs/use-cases/observability/clickstack/getting-started/otelgen

Install the `otelgen` CLI tool using Homebrew. This is a convenient way to get the latest version.

```bash
brew install krzko/tap/otelgen
```

--------------------------------

### Watch Command for Keeper Client

Source: https://clickhouse.com/docs/whats-new/changelog

Interact with ClickHouse Keeper using the 'watch' command for get, exists, and ls operations.

```bash
clickhouse-keeper-client --host <host> --port <port> watch /path/to/key
```

--------------------------------

### Create Sample Database

Source: https://clickhouse.com/docs/knowledgebase/exchangeStatementToSwitchTables

Creates a new database to host the example tables. Ensure the database does not already exist.

```sql
create database db1;
```

--------------------------------

### Monitor S3 Read Operations

Source: https://clickhouse.com/docs/whats-new/changelog

Observe S3 GET request duration and bytes consumed using histogram metrics.

```sql
SELECT * FROM system.histogram_metrics WHERE name LIKE 's3_read_request_%';
```

--------------------------------

### Install OpenTelemetry and HyperDX Go Packages

Source: https://clickhouse.com/docs/use-cases/observability/clickstack/sdks/golang

Install the OpenTelemetry and HyperDX Go packages using go get. Ensure you install the necessary packages for trace information to be attached correctly.

```bash
go get -u go.opentelemetry.io/otel
go get -u github.com/hyperdxio/otel-config-go
go get -u github.com/hyperdxio/opentelemetry-go
go get -u github.com/hyperdxio/opentelemetry-logs-go

```

--------------------------------

### Create Sample Database

Source: https://clickhouse.com/docs/operations/access-rights

Creates a new database to be used for permission examples.

```sql
CREATE DATABASE my_db;
```

--------------------------------

### Connect to ClickHouse SQL Playground

Source: https://clickhouse.com/docs/use-cases/AI/ai-powered-sql-generation

Connect to the ClickHouse SQL playground using the clickhouse-client command. Ensure ClickHouse is installed or refer to the installation guide.

```bash
clickhouse client -mn \
--host sql-clickhouse.clickhouse.com \
--secure \
--user demo --password ''

```

--------------------------------

### Clone and Install Node.js App Dependencies

Source: https://clickhouse.com/docs/use-cases/observability/clickstack/example-datasets/instrument-app

Clone the HackerNews Analyzer repository, navigate into the directory, install npm dependencies, and copy the example environment file.

```bash
git clone https://github.com/ClickHouse/hn-news-analyzer.git
cd hn-news-analyzer
npm install
cp .env.example .env

```

--------------------------------

### Example: Create, Drop, and Undrop a Table

Source: https://clickhouse.com/docs/sql-reference/statements/undrop

This example demonstrates the process of creating a table, dropping it, verifying its absence in `system.dropped_tables`, and then undropping it. Finally, it verifies the table's existence.

```sql
CREATE TABLE tab
(
    `id` UInt8
)
ENGINE = MergeTree
ORDER BY id;

DROP TABLE tab;

SELECT *
FROM system.dropped_tables
FORMAT Vertical;
```

```sql
UNDROP TABLE tab;

SELECT * 
FROM system.dropped_tables
FORMAT Vertical;
```

```sql
DESCRIBE TABLE tab
FORMAT Vertical;
```

--------------------------------

### Create and Insert Data into Example Table

Source: https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniq

Sets up a sample table and populates it with data for demonstrating the uniq function.

```sql
CREATE TABLE example_table (
    id UInt32,
    category String,
    value Float64
) ENGINE = Memory;

INSERT INTO example_table VALUES
(1, 'A', 10.5),
(2, 'B', 20.3),
(3, 'A', 15.7),
(4, 'C', 8.9),
(5, 'B', 12.1),
(6, 'A', 18.4);
```

--------------------------------

### Connect to Local ClickHouse Instance

Source: https://clickhouse.com/docs/interfaces/odbc

Example connection string for a ClickHouse server installed locally on a WSL instance. Ensure the driver name matches your installation.

```sql
Driver={ClickHouse ODBC Driver (Unicode)};Url=http://localhost:8123/;Username=default

```

--------------------------------

### Clone the examples repository

Source: https://clickhouse.com/docs/use-cases/AI/MCP/ai-agent-libraries/copilotkit

Clone the project locally to access the example code. Navigate to the ai/mcp/copilotkit directory.

```bash
git clone https://github.com/ClickHouse/examples
cd ai/mcp/copilotkit
```

--------------------------------

### Launch IPython

Source: https://clickhouse.com/docs/chdb/guides/apache-arrow

Start an IPython interactive session to run the commands in this guide.

```bash
ipython
```

--------------------------------

### Extract and Install ClickHouse Server

Source: https://clickhouse.com/docs/install/linux_other

Extracts the clickhouse-server package, runs its installation script with configuration, and then starts the ClickHouse server. This is the core component for data storage and querying.

```bash
# Extract and install server package with configuration
tar -xzvf "clickhouse-server-$LATEST_VERSION-${ARCH}.tgz"
  || tar -xzvf "clickhouse-server-$LATEST_VERSION.tgz"
sudo "clickhouse-server-$LATEST_VERSION/install/doinst.sh" configure
sudo /etc/init.d/clickhouse-server start  # Start the server

```

--------------------------------

### Create Sample Sales Table

Source: https://clickhouse.com/docs/guides/developer/stored-procedures-and-prepared-statements

Sets up a sample 'sales' table with relevant columns for demonstrating parameterized views. This table is used in subsequent examples.

```sql
-- Create the sales table
CREATE TABLE sales (
  date Date,
  product_id UInt32,
  product_name String,
  category String,
  quantity UInt32,
  revenue Decimal(10, 2),
  sales_amount Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (date, product_id);

-- Insert sample data
INSERT INTO sales VALUES
('2024-01-05', 12345, 'Laptop Pro', 'Electronics', 2, 1799.98, 1799.98),
('2024-01-06', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-01-10', 12346, 'Wireless Mouse', 'Electronics', 5, 124.95, 124.95),
('2024-01-15', 12347, 'USB-C Cable', 'Accessories', 10, 125.00, 125.00),
('2024-01-20', 12345, 'Laptop Pro', 'Electronics', 3, 2699.97, 2699.97),
('2024-01-25', 12348, 'Monitor 4K', 'Electronics', 2, 598.00, 598.00),
('2024-02-01', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-02-05', 12349, 'Keyboard Mechanical', 'Accessories', 4, 319.96, 319.96),
('2024-02-10', 12346, 'Wireless Mouse', 'Electronics', 8, 199.92, 199.92),
('2024-02-15', 12350, 'Webcam HD', 'Electronics', 3, 164.85, 164.85);
```

--------------------------------

### Configuration File Example: Local File Source with Settings

Source: https://clickhouse.com/docs/sql-reference/statements/create/dictionary/sources

Example of configuring a dictionary from a local file using the configuration file, including specific settings.

```xml
<source>
  <file>
    <path>/opt/dictionaries/os.tsv</path>
    <format>TabSeparated</format>
  </file>
  <settings>
#highlight-next-line
      <format_csv_allow_single_quotes>0</format_csv_allow_single_quotes>
  </settings>
</source>
```

--------------------------------

### Get Initial Query Start Time

Source: https://clickhouse.com/docs/sql-reference/functions/other-functions

Retrieves the start time of the initial current query. This function is non-deterministic and provides consistent results across different shards.

```sql
SELECT count(DISTINCT t) FROM (SELECT initialQueryStartTime() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID())
```

--------------------------------

### Get Tumbling Window Start Bound

Source: https://clickhouse.com/docs/sql-reference/functions/time-window-functions

Returns the inclusive lower bound of a tumbling time window. Use to identify the start of a fixed, non-overlapping time interval.

```sql
SELECT tumbleStart(now(), toIntervalDay('1'))
```

--------------------------------

### Create and Insert Data into wf_partition Table

Source: https://clickhouse.com/docs/sql-reference/window-functions

Sets up a temporary table 'wf_partition' and populates it with sample data for demonstrating partitioning in window functions.

```sql
CREATE TABLE wf_partition
(
    `part_key` UInt64,
    `value` UInt64,
    `order` UInt64    
)
ENGINE = Memory;

INSERT INTO wf_partition FORMAT Values
   (1,1,1), (1,2,2), (1,3,3), (2,0,0), (3,0,0);

```

--------------------------------

### Initialize Project Structure

Source: https://clickhouse.com/docs/interfaces/cli

Bootstraps a ClickHouse and Postgres project directory with a standard folder structure for definitions and data.

```bash
clickhousectl local init

```

--------------------------------

### Get Hopping Window Start Bound

Source: https://clickhouse.com/docs/sql-reference/functions/time-window-functions

Returns the inclusive lower bound of a hopping time window. Use to identify the start of a period for hopping window calculations.

```sql
SELECT hopStart(now(), INTERVAL '1' DAY, INTERVAL '2' DAY)
```

--------------------------------

### Create Table and Insert Data for Compression Estimate Example

Source: https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/estimateCompressionRatio

Sets up a table and inserts sample data for demonstrating the estimateCompressionRatio function. Ensure the server's default compression codec is considered for accurate results.

```sql
CREATE TABLE compression_estimate_example
(
    `number` UInt64
)
ENGINE = MergeTree()
ORDER BY number
SETTINGS min_bytes_for_wide_part = 0;

INSERT INTO compression_estimate_example
SELECT number FROM system.numbers LIMIT 100_000;
```

--------------------------------

### fileCluster Example Usage

Source: https://clickhouse.com/docs/sql-reference/table-functions/fileCluster

Provides a practical example of using the fileCluster table function to read data from multiple CSV files across a cluster, including setup and expected output.

```APIDOC
## Example

Given a cluster named `my_cluster` and given the following value of setting `user_files_path`:
```
$ grep user_files_path /etc/clickhouse-server/config.xml
    <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
```

Also, given there are files `test1.csv` and `test2.csv` inside `user_files_path` of each cluster node, and their content is identical across different nodes:
```
$ cat /var/lib/clickhouse/user_files/test1.csv
    1,"file1"
    11,"file11"

$ cat /var/lib/clickhouse/user_files/test2.csv
    2,"file2"
    22,"file22"
```

For example, one can create these files by executing these two queries on every cluster node:
```
INSERT INTO TABLE FUNCTION file('file1.csv', 'CSV', 'i UInt32, s String') VALUES (1,'file1'), (11,'file11');
INSERT INTO TABLE FUNCTION file('file2.csv', 'CSV', 'i UInt32, s String') VALUES (2,'file2'), (22,'file22');
```

Now, read data contents of `test1.csv` and `test2.csv` via `fileCluster` table function:
```sql
SELECT * FROM fileCluster('my_cluster', 'file{1,2}.csv', 'CSV', 'i UInt32, s String') ORDER BY i, s
```

```
┌──i─┬─s──────┐
│  1 │ file1  │
│ 11 │ file11 │
└────┴────────┘
┌──i─┬─s──────┐
│  2 │ file2  │
│ 22 │ file22 │
└────┴────────┘
```
```

--------------------------------

### Get Current Shard Index

Source: https://clickhouse.com/docs/sql-reference/functions/other-functions

Use shardNum() to get the index of the shard processing a part of a distributed query. Indices start from 1; returns 0 if the query is not distributed. This function is non-deterministic.

```sql
CREATE TABLE shard_num_example (
    dummy UInt8
)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT dummy, shardNum(), shardCount() FROM shard_num_example;
```

```text
┌─dummy─┬─shardNum()─┬─shardCount()─┐
│     0 │          1 │            2 │
│     0 │          2 │            2 │
└───────┴────────────┴──────────────┘
```

--------------------------------

### Create Table Example (ClickHouse Cloud)

Source: https://clickhouse.com/docs/integrations/javascript

Demonstrates creating a table in ClickHouse Cloud using the command method. Includes recommended settings for cluster usage.

```javascript
await client.command({
  query: `
    CREATE TABLE IF NOT EXISTS my_cloud_table
    (id UInt64, name String)
    ORDER BY (id)
  `,
  // Recommended for cluster usage to avoid situations where a query processing error occurred after the response code,
  // and HTTP headers were already sent to the client.
  // See https://clickhouse.com/docs/interfaces/http/#response-buffering
  clickhouse_settings: {
    wait_end_of_query: 1,
  },
})
```

--------------------------------

### Example Configuration File A

Source: https://clickhouse.com/docs/operations/configuration-files

The first configuration file used in the merging example.

```xml
<clickhouse>
    <config_a>
        <setting_1>1</setting_1>
    </config_a>
    <config_b>
        <setting_2>2</setting_2>
    </config_b>
    <config_c>
        <setting_3>3</setting_3>
    </config_c>
</clickhouse>

```

--------------------------------

### Successful Response for Get List of Keys

Source: https://clickhouse.com/docs/cloud/manage/api/swagger

Example of a successful response when retrieving a list of API keys.

```json
{
  "status": 200,
  "requestId": "d385ab22-0f51-4b97-9ecd-b8ff3fd4fcb6",
  "result": [
    {
      "id": "497f6eca-6276-4993-bfeb-53cbbbba6f08",
      "name": "string",
      "state": "enabled",
      "roles": [
        "admin"
      ],
      "assignedRoles": [
        {
          "roleId": "7382d58e-652a-4905-b7c9-bcca1e0e5391",
          "roleName": "string",
          "roleType": "system"
        }
      ],
      "keySuffix": "string",
      "createdAt": "2019-08-24T14:15:22Z",
      "expireAt": "2019-08-24T14:15:22Z",
      "usedAt": "2019-08-24T14:15:22Z",
      "ipAccessList": [
        {
          "source": "string",
          "description": "string"
        }
      ]
    }
  ]
}
```

--------------------------------

### Get Server UUID

Source: https://clickhouse.com/docs/sql-reference/functions/other-functions

Returns the unique UUID (v4) generated when the server was first started. This function is non-deterministic.

```sql
SELECT serverUUID();
```

```text
┌─serverUUID()─────────────────────────────┐
│ 7ccc9260-000d-4d5c-a843-5459abaabb5f     │
└──────────────────────────────────────────┘
```

--------------------------------

### Query Log Configuration Example

Source: https://clickhouse.com/docs/operations/server-configuration-parameters/settings

An example demonstrating how to configure the query log settings, including database, table name, engine definition, and various buffer and flush parameters.

```xml
<query_log>
    <database>system</database>
    <table>query_log</table>
    <engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 30 day</engine>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <flush_on_crash>false</flush_on_crash>
</query_log>
```

--------------------------------

### Simple aiGenerate Example

Source: https://clickhouse.com/docs/sql-reference/functions/ai-functions

Demonstrates a basic usage of the aiGenerate function to get a numerical answer to a simple question.

```sql
SELECT aiGenerate('ai_credentials', 'What is 2 + 2? Reply with just the number.')
```

```text
4
```

--------------------------------

### Create and Insert Data for uniqHLL12 Example

Source: https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqhll12

Sets up a temporary Memory table and inserts sample data to demonstrate the uniqHLL12 function.

```sql
CREATE TABLE example_hll
(
    id UInt32,
    category String
)
ENGINE = Memory;

INSERT INTO example_hll VALUES
(1, 'A'), (2, 'B'), (3, 'A'), (4, 'C'), (5, 'B'), (6, 'A');
```

--------------------------------

### Get Day of Week with toDayOfWeek

Source: https://clickhouse.com/docs/sql-reference/functions/date-time-functions

Use toDayOfWeek to get the number of the day within the week for a given date or datetime value. The mode parameter customizes the week's start day and the return value range.

```sql
SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)
```

```sql
┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│                                     5 │                                        4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘
```

--------------------------------

### Create Table and Insert Data

Source: https://clickhouse.com/docs/interfaces/formats/LineAsStringWithNames

Sets up a sample table and inserts data for demonstration purposes.

```sql
CREATE TABLE example (
    name String,
    value Int32
)
ENGINE = Memory;

INSERT INTO example VALUES ('John', 30), ('Jane', 25), ('Peter', 35);
```

--------------------------------

### Clone and Run Airbyte Platform

Source: https://clickhouse.com/docs/integrations/airbyte

Clone the Airbyte repository and start the platform using docker-compose. Ensure Docker is installed.

```bash
git clone https://github.com/airbytehq/airbyte.git --depth=1
cd airbyte
./run-ab-platform.sh
```

--------------------------------

### Start Kafka Server

Source: https://clickhouse.com/docs/knowledgebase/kafka-to-clickhouse-setup

Launch the Kafka server. This command assumes you are in the Kafka installation directory and uses the default server properties.

```bash
ssh -i ~/training.pem ubuntu@ec2.compute.amazonaws.com
cd kafka/kafka_2.13-3.7.0/
bin/kafka-server-start.sh config/server.properties

```

--------------------------------

### Creating and Populating a Table for argMinIf Example

Source: https://clickhouse.com/docs/examples/aggregate-function-combinators/argMinIf

This snippet demonstrates the SQL commands to create a 'product_prices' table and insert sample data, which will be used to illustrate the argMinIf function.

```sql
CREATE TABLE product_prices(
    product_id UInt32,
    price Decimal(10,2),
    timestamp DateTime,
    in_stock UInt8
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO product_prices VALUES
    (1, 10.99, '2024-01-01 10:00:00', 1),
    (1, 9.99, '2024-01-01 10:05:00', 1),
    (1, 11.99, '2024-01-01 10:10:00', 0),
    (2, 20.99, '2024-01-01 11:00:00', 1),
    (2, 19.99, '2024-01-01 11:05:00', 1),
    (2, 21.99, '2024-01-01 11:10:00', 1);
```

--------------------------------

### Create and Insert into QBit Table

Source: https://clickhouse.com/docs/sql-reference/data-types/qbit

Demonstrates creating a table with a QBit column and inserting sample vector data.

```sql
CREATE TABLE test (id UInt32, vec QBit(Float32, 8)) ENGINE = Memory;
INSERT INTO test VALUES (1, [1, 2, 3, 4, 5, 6, 7, 8]), (2, [9, 10, 11, 12, 13, 14, 15, 16]);
SELECT vec FROM test ORDER BY id;
```

--------------------------------

### Query Views Log Configuration Example

Source: https://clickhouse.com/docs/operations/server-configuration-parameters/settings

An example demonstrating how to configure the query_views_log setting, including database, table, partitioning, and various buffer/flush parameters.

```xml
<query_views_log>
    <database>system</database>
    <table>query_views_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <max_size_rows>1048576</max_size_rows>
    <reserved_size_rows>8192</reserved_size_rows>
    <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
    <flush_on_crash>false</flush_on_crash>
</query_views_log>

```

--------------------------------

### Creating a Regexp Tree Dictionary

Source: https://clickhouse.com/docs/sql-reference/statements/create/dictionary/layouts/regexp-tree

Example of creating a dictionary with the `YAMLRegExpTree` source and `regexp_tree` layout. This setup is used for pattern-match lookups.

```sql
CREATE DICTIONARY regexp_dict
(
    regexp String,
    name String,
    version String
)
PRIMARY KEY(regexp)
SOURCE(YAMLRegExpTree(PATH '/var/lib/clickhouse/user_files/regexp_tree.yaml'))
LAYOUT(regexp_tree)
...
```

--------------------------------

### Manage ClickPipes

Source: https://clickhouse.com/docs/interfaces/cli

Commands for listing, getting, starting, stopping, resyncing, deleting, and updating ClickPipes. Resync is only applicable to CDC pipes.

```bash
# List ClickPipes for a service
clickhousectl cloud clickpipe list <service-id>

# Get ClickPipe details
clickhousectl cloud clickpipe get <service-id> <clickpipe-id>

# Start/stop/resync a ClickPipe
clickhousectl cloud clickpipe start <service-id> <clickpipe-id>
clickhousectl cloud clickpipe stop <service-id> <clickpipe-id>
clickhousectl cloud clickpipe resync <service-id> <clickpipe-id>   # CDC pipes only

# Delete a ClickPipe
clickhousectl cloud clickpipe delete <service-id> <clickpipe-id>

# Update scaling
clickhousectl cloud clickpipe scale <service-id> <clickpipe-id> \
  --replicas 2 --cpu-millicores 250 --memory-gb 1

# Get/update settings
clickhousectl cloud clickpipe settings get <service-id> <clickpipe-id>
clickhousectl cloud clickpipe settings update <service-id> <clickpipe-id> \
  --streaming-max-insert-wait-ms 10000
```

--------------------------------

### Start ClickStack All-in-One

Source: https://clickhouse.com/docs/use-cases/observability/clickstack/integrations/jvm-metrics

Run the ClickStack all-in-one Docker image to set up the observability platform. This command exposes the necessary ports for metrics ingestion and UI access.

```bash
docker run -d --name clickstack \
  -p 8080:8080 -p 4317:4317 -p 4318:4318 \
  clickhouse/clickstack-all-in-one:latest
```

--------------------------------

### Configure Query ID, Parameters, and Progress Callback

Source: https://clickhouse.com/docs/integrations/language-clients/go/config-reference

Shows how to combine multiple context options for a query, including setting a custom query ID, passing parameters for parameterized SQL, and registering a callback function to receive query progress updates. This example also demonstrates executing a query with these options.

```go
ctx := clickhouse.Context(ctx,
    clickhouse.WithQueryID("query-123"),
    clickhouse.WithParameters(clickhouse.Parameters{
        "user_id": "12345",
    }),
    clickhouse.WithProgress(func(p *clickhouse.Progress) {
        log.Printf("Progress: %d rows, %d bytes", p.Rows, p.Bytes)
    }),
)
rows, err := conn.Query(ctx, "SELECT * FROM users WHERE id = {user_id:String}")

```

--------------------------------

### ClickHouse Client Interactive Prompt Example

Source: https://clickhouse.com/docs/interfaces/client

This shows the typical prompt and connection information displayed when the ClickHouse client starts in interactive mode.

```bash
ClickHouse client version 25.x.x.x
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.x.x.x

hostname :)

```

--------------------------------

### Schema Configuration Example

Source: https://clickhouse.com/docs/integrations/dbt/materializations

Example demonstrating how to configure schema, contract enforcement, and column-level properties like codec and ttl.

```yaml
models:
  - name: table_column_configs
    description: 'Testing column-level configurations'
    config:
      contract:
        enforced: true
    columns:
      - name: ts
        data_type: timestamp
        codec: ZSTD
      - name: x
        data_type: UInt8
        ttl: ts + INTERVAL 1 DAY

```

--------------------------------

### Example of $__fromTime_ms Macro Output

Source: https://clickhouse.com/docs/integrations/grafana/query-builder

Illustrates the output of the $__fromTime_ms macro, which represents the starting time of the Grafana panel range cast to a DateTime64.

```sql
fromUnixTimestamp64Milli(1415792726123)
```

--------------------------------

### Example of $__fromTime Macro Output

Source: https://clickhouse.com/docs/integrations/grafana/query-builder

Illustrates the output of the $__fromTime macro, which represents the starting time of the Grafana panel range cast to a DateTime.

```sql
toDateTime(1415792726)
```

--------------------------------

### Install ClickHouse using Old Distributions Method

Source: https://clickhouse.com/docs/install/debian_ubuntu

Installs prerequisite packages, adds the GPG key, sets up the repository, updates package lists, and installs ClickHouse server and client. This method is for older distributions.

```bash
# Install prerequisite packages
sudo apt-get install apt-transport-https ca-certificates dirmngr

# Add the ClickHouse GPG key to authenticate packages
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754

# Add the ClickHouse repository to apt sources
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
    
# Update apt package lists
sudo apt-get update

# Install ClickHouse server and client packages
sudo apt-get install -y clickhouse-server clickhouse-client

# Start the ClickHouse server service
sudo service clickhouse-server start

# Launch the ClickHouse command line client
clickhouse-client # or "clickhouse-client --password" if you set up a password.

```

--------------------------------

### Copy .env.example to .env

Source: https://clickhouse.com/docs/use-cases/AI/MCP/librechat

Copy the example environment file to create your own .env configuration file. This file will store your API keys and other settings.

```bash
cp .env.example .env  
```