### Start DBConnection Pool with Default Settings

Source: https://github.com/plausible/ch/blob/master/README.md

Starts a ClickHouse connection pool using `Ch.start_link` with predefined default settings for scheme, hostname, port, database, and pool configuration.

```elixir
defaults = [
  scheme: "http",
  hostname: "localhost",
  port: 8123,
  database: "default",
  settings: [],
  pool_size: 1,
  timeout: :timer.seconds(15)
]

# note that starting in ClickHouse 25.1.3.23 `default` user doesn't have
# network access by default in the official Docker images
# see https://github.com/ClickHouse/ClickHouse/pull/75259
{:ok, pid} = Ch.start_link(defaults)
```

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

### Install Ch Elixir Package

Source: https://github.com/plausible/ch/blob/master/README.md

Defines the necessary dependency in the Elixir project's `deps` function for version 0.6.0 or compatible.

```elixir
defp deps do
  [
    {:ch, "~> 0.6.0"}
  ]
end
```

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

### Select Rows from ClickHouse

Source: https://github.com/plausible/ch/blob/master/README.md

Demonstrates querying ClickHouse for rows using `Ch.query`. Includes examples of selecting all columns with a limit, using positional parameters, and named parameters.

```elixir
{:ok, pid} = Ch.start_link()

{:ok, %Ch.Result{rows: [[0], [1], [2]]}} =
  Ch.query(pid, "SELECT * FROM system.numbers LIMIT 3")

{:ok, %Ch.Result{rows: [[0], [1], [2]]}} =
  Ch.query(pid, "SELECT * FROM system.numbers LIMIT {$0:UInt8}", [3])

{:ok, %Ch.Result{rows: [[0], [1], [2]]}} =
  Ch.query(pid, "SELECT * FROM system.numbers LIMIT {limit:UInt8}", %{"limit" => 3})
```

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

### Insert Rows into ClickHouse (Default Format)

Source: https://github.com/plausible/ch/blob/master/README.md

Shows how to insert rows into a ClickHouse table using `Ch.query!`. Examples include creating a table, inserting static values using positional and named parameters, and inserting from a subquery.

```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, "CREATE TABLE IF NOT EXISTS ch_demo(id UInt64) ENGINE Null")

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) VALUES (0), (1)")

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) VALUES ({$0:UInt8}), ({$1:UInt32})", [0, 1])

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) VALUES ({a:UInt16}), ({b:UInt64})", %{"a" => 0, "b" => 1})

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) SELECT number FROM system.numbers LIMIT {limit:UInt8}", %{"limit" => 2})
```

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

### Insert Rows using RowBinary Format

Source: https://github.com/plausible/ch/blob/master/README.md

Demonstrates efficient row insertion into ClickHouse using the `RowBinary` format with the `:types` option. Includes examples of specifying types as strings, `Ch.Types` atoms, or module atoms.

```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, "CREATE TABLE IF NOT EXISTS ch_demo(id UInt64) ENGINE Null")

types = ["UInt64"]
# or
types = [Ch.Types.u64()]
# or
types = [:u64]

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) FORMAT RowBinary", [[0], [1]], types: types)
```

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

### Connection Pool Management

Source: https://context7.com/plausible/ch/llms.txt

Learn how to start and manage DBConnection pools for ClickHouse HTTP connections, including configuration options and supervision.

```APIDOC
## Connection Pool Management

### Description
This section details how to initiate and manage a DBConnection pool for ClickHouse HTTP interactions. It covers basic startup procedures and integration with Elixir's supervision trees.

### Method
`Ch.start_link/1`

### Endpoint
N/A (Client-side connection management)

### Parameters
#### Keyword List Options
- **scheme** (atom | string) - Required - The protocol scheme (e.g., `:http`, `:https`).
- **hostname** (atom | string) - Required - The ClickHouse server hostname.
- **port** (integer) - Required - The ClickHouse HTTP port.
- **database** (atom | string) - Optional - The default database to use.
- **username** (atom | string) - Optional - The username for authentication.
- **password** (atom | string) - Optional - The password for authentication.
- **pool_size** (integer) - Optional - The number of connections in the pool (defaults to 10).
- **timeout** (timeout | pos_integer) - Optional - The connection timeout in milliseconds.
- **settings** (list) - Optional - A list of per-query settings to apply to all queries run through this pool.

### Request Example
```elixir
# Start a basic connection pool
{:ok, pid} = Ch.start_link(
  scheme: "http",
  hostname: "localhost",
  port: 8123,
  database: "default",
  username: "default",
  password: "password",
  pool_size: 10,
  timeout: :timer.seconds(15),
  settings: [max_execution_time: 60]
)

# Use as a supervised child in your application
children = [
  {Ch, [
    hostname: "clickhouse.example.com",
    port: 8123,
    database: "analytics",
    pool_size: 5
  }]}
]

Supervisor.start_link(children, strategy: :one_for_one)
```

### Response
#### Success Response
- **{:ok, pid}** - Returns the PID of the started connection pool process.
- **Supervisor integration** - The pool is managed by the supervisor.
```

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

### Start ClickHouse Connection Pool with Ch in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Initiates a DBConnection pool for ClickHouse HTTP connections. Configuration includes scheme, hostname, port, database credentials, pool size, timeout, and per-query settings. This can be used as a standalone process or as a supervised child in an Elixir application.

```elixir
iex> {:ok, pid} = Ch.start_link(
...>   scheme: "http",
...>   hostname: "localhost",
...>   port: 8123,
...>   database: "default",
...>   username: "default",
...>   password: "password",
...>   pool_size: 10,
...>   timeout: :timer.seconds(15),
...>   settings: [max_execution_time: 60]
...> )

children = [
  {Ch, [
    hostname: "clickhouse.example.com",
    port: 8123,
    database: "analytics",
    pool_size: 5
  }]}
]

Supervisor.start_link(children, strategy: :one_for_one)
```

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

### Handle Nullable Types in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Correctly handle NULL values when working with Nullable types in ClickHouse via the RowBinary format. This example demonstrates inserting rows with `nil` values for nullable columns and querying them to verify that `nil` is returned for NULLs.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE IF NOT EXISTS orders (
 id UInt64,
 customer_id Nullable(UInt64),
 notes Nullable(String),
 amount Decimal(10, 2)
 ) ENGINE MergeTree()
 ORDER BY id
""")

 types = ["UInt64", "Nullable(UInt64)", "Nullable(String)", "Decimal(10, 2)"]
 rows = [
 [1, 100, "Rush order", Decimal.new("99.99")],
 [2, nil, nil, Decimal.new("49.99")],  # NULL values
 [3, 102, "Gift wrap", Decimal.new("149.99")]
 ]

 %Ch.Result{num_rows: 3} = Ch.query!(pid,
 "INSERT INTO orders FORMAT RowBinary",
 rows,
 types: types
)

 # Query returns nil for NULL values
 {:ok, %Ch.Result{rows: result_rows}} = Ch.query(
 pid,
 "SELECT * FROM orders WHERE id = 2"
 )
 # result_rows = [[2, nil, nil, Decimal.new("49.99")]]
```

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

### Encode and Decode Data with RowBinary Format

Source: https://context7.com/plausible/ch/llms.txt

Provides examples of manually encoding and decoding data using ClickHouse's RowBinary format. It covers encoding single rows, multiple rows, and decoding binary data into rows, including handling data with column names and types.

```elixir
# Encode a single row
row = [42, "hello", 3.14]
types = ["UInt32", "String", "Float64"]
encoded = Ch.RowBinary.encode_row(row, types)
# Returns iodata: [42, [5 | "hello"], <<195, 245, 72, 64, 9, 33, 201, 64>>]

# Encode multiple rows
rows = [
  [1, "first", 1.0],
  [2, "second", 2.0],
  [3, "third", 3.0]
]
encoded = Ch.RowBinary.encode_rows(rows, types)

# Decode RowBinary data
binary_data = <<1, 5, "first", 0, 0, 128, 63, 2, 6, "second", 0, 0, 0, 64>>
decoded_rows = Ch.RowBinary.decode_rows(binary_data, types)
# [[1, "first", 1.0], [2, "second", 2.0]]

# Decode RowBinaryWithNamesAndTypes (includes header)
row_binary_with_names_and_types = <<
  1,                    # column count
  3, "num",            # column name
  5, "UInt8",          # column type
  1, 2, 3              # three rows of data
>>  
decoded = Ch.RowBinary.decode_rows(row_binary_with_names_and_types)
# [[1], [2], [3]]

# Get names and rows separately
[names | rows] = Ch.RowBinary.decode_names_and_rows(row_binary_with_names_and_types)
# names = ["num"]
# rows = [[1], [2], [3]]
```

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

### Insert Rows using Custom CSV Format

Source: https://github.com/plausible/ch/blob/master/README.md

Inserts data into ClickHouse using a custom `CSV` format. The example shows converting a list of numbers to a newline-separated string before insertion.

```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, "CREATE TABLE IF NOT EXISTS ch_demo(id UInt64) ENGINE Null")

csv = [0, 1] |> Enum.map(&to_string/1) |> Enum.intersperse(?\n)

%Ch.Result{num_rows: 2} =
  Ch.query!(pid, "INSERT INTO ch_demo(id) FORMAT CSV", csv, encode: false)
```

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

### Insert Data with VALUES Syntax using Ch in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Inserts data into ClickHouse tables using the SQL VALUES syntax with parameter binding. This function supports both positional and named parameters for inserting multiple rows or single rows efficiently. It also includes an example of creating a table if it does not exist.

```elixir
iex> {:ok, pid} = Ch.start_link()

iex> Ch.query!(pid, """
...>   CREATE TABLE IF NOT EXISTS events (
...>     id UInt64,
...>     name String,
...>     timestamp DateTime,
...>     value Float64
...>   ) ENGINE MergeTree()
...>   ORDER BY (timestamp, id)
...> """)

iex> %Ch.Result{num_rows: 3} = Ch.query!(
...>   pid,
...>   "INSERT INTO events VALUES ({$0:UInt64}, {$1:String}, {$2:DateTime}, {$3:Float64}), ({$4:UInt64}, {$5:String}, {$6:DateTime}, {$7:Float64}), ({$8:UInt64}, {$9:String}, {$10:DateTime}, {$11:Float64})",
...>   [1, "click", DateTime.utc_now(), 42.5, 2, "view", DateTime.utc_now(), 10.0, 3, "purchase", DateTime.utc_now(), 99.99]
...> )

iex> %Ch.Result{num_rows: 1} = Ch.query!(
...>   pid,
...>   "INSERT INTO events VALUES ({id:UInt64}, {name:String}, {timestamp:DateTime}, {value:Float64})",
...>   %{"id" => 4, "name" => "signup", "timestamp" => DateTime.utc_now(), "value" => 0.0}
...> )
```

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

### Handle Complex Types (Arrays, Tuples, Maps) in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Work with complex ClickHouse data types such as Arrays, Tuples, and Maps during data insertion and retrieval. This example shows how to define tables with these types and insert/query data, including specifying types for accurate decoding.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE IF NOT EXISTS analytics (
 user_id UInt64,
 tags Array(String),
 coordinates Tuple(Float64, Float64),
 metadata Map(String, String)
 ) ENGINE MergeTree()
 ORDER BY user_id
""")

 types = [
 "UInt64",
 "Array(String)",
 "Tuple(Float64, Float64)",
 "Map(String, String)"
 ]

 rows = [
 [1, ["premium", "active"], {37.7749, -122.4194}, %{"city" => "SF", "country" => "US"}],
 [2, ["trial"], {40.7128, -74.0060}, %{"city" => "NY", "country" => "US"}],
 [3, [], {51.5074, -0.1278}, %{}]
 ]

 %Ch.Result{num_rows: 3} = Ch.query!(pid,
 "INSERT INTO analytics FORMAT RowBinary",
 rows,
 types: types
)

 # Query with type specification for correct decoding
 {:ok, %Ch.Result{rows: [[tags, coords, meta]]}} = Ch.query(
 pid,
 "SELECT tags, coordinates, metadata FROM analytics WHERE user_id = 1",
 [],
 types: [
 Ch.Types.array(Ch.Types.string()),
 Ch.Types.tuple([Ch.Types.f64(), Ch.Types.f64()]),
 Ch.Types.map(Ch.Types.string(), Ch.Types.string())
 ]
 )
```

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

### Handle NULLs in RowBinary with Elixir

Source: https://github.com/plausible/ch/blob/master/README.md

Demonstrates inserting and selecting rows with NULL values in ClickHouse using the RowBinary format and the `ch` Elixir client. It shows how to create tables with nullable columns and how `nil` values are treated for both normal and nullable columns. It also illustrates a workaround using the `input()` table function for default values.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE ch_nulls (
   a UInt8 NULL,
   b UInt8 DEFAULT 10,
   c UInt8 NOT NULL
 ) ENGINE Memory
 ")

 types = ["Nullable(UInt8)", "UInt8", "UInt8"]
 inserted_rows = [[nil, nil, nil]]
 selected_rows = [[nil, 0, 0]]

 %Ch.Result{num_rows: 1} =
   Ch.query!(pid, "INSERT INTO ch_nulls(a, b, c) FORMAT RowBinary", inserted_rows, types: types)

 %Ch.Result{rows: ^selected_rows} =
   Ch.query!(pid, "SELECT * FROM ch_nulls")
```

```elixir
sql = """
INSERT INTO ch_nulls
  SELECT * FROM input('a Nullable(UInt8), b Nullable(UInt8), c UInt8')
  FORMAT RowBinary\n"

Ch.query!(pid, sql, inserted_rows, types: ["Nullable(UInt8)", "Nullable(UInt8)", "UInt8"])

%Ch.Result{rows: [[0], [10]]} =
  Ch.query!(pid, "SELECT b FROM ch_nulls ORDER BY b")
```

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

### Query with Custom Settings

Source: https://github.com/plausible/ch/blob/master/README.md

Demonstrates how to apply custom ClickHouse query settings, such as `async_insert`, to modify query behavior. It shows querying the current setting and then updating it.

```elixir
{:ok, pid} = Ch.start_link()

settings = [async_insert: 1]

%Ch.Result{rows: [["async_insert", "Bool", "0"]]} =
  Ch.query!(pid, "SHOW SETTINGS LIKE 'async_insert'")

%Ch.Result{rows: [["async_insert", "Bool", "1"]]} =
  Ch.query!(pid, "SHOW SETTINGS LIKE 'async_insert'", [], settings: settings)
```

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

### Inserting Data with RowBinary Format

Source: https://context7.com/plausible/ch/llms.txt

Utilize the highly efficient RowBinary format for bulk data insertion, optimizing performance for large datasets.

```APIDOC
## Inserting Data with RowBinary Format

### Description
This endpoint enables high-performance data insertion into ClickHouse tables by leveraging the native `RowBinary` format. This method is significantly faster for large volumes of data compared to the `VALUES` clause.

### Method
`Ch.query/3` or `Ch.query!/3`

### Endpoint
N/A (Function call on a connection pool PID)

### Parameters
#### Function Arguments
- **pid** (pid) - Required - The PID of the running Ch connection pool.
- **query** (string) - Required - The `INSERT INTO ... FORMAT RowBinary` SQL query string.
- **rows** (list) - Required - A list of lists, where each inner list represents a row of data to be inserted.
- **opts** (keyword list) - Required - Options for formatting the data.
  - **types** (list) - Required - A list specifying the data types for each column. Can be strings (e.g., `"DateTime"`), atoms (e.g., `:datetime`), or type structs (e.g., `Ch.Types.datetime()`).

### Request Example
```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, """
  CREATE TABLE IF NOT EXISTS metrics (
    timestamp DateTime,
    metric_name String,
    value Float64
  ) ENGINE MergeTree()
  ORDER BY timestamp
")

# Define types as strings
types = ["DateTime", "String", "Float64"]
rows = [
  [DateTime.utc_now(), "cpu_usage", 45.2],
  [DateTime.utc_now(), "memory_usage", 78.5],
  [DateTime.utc_now(), "disk_io", 120.0]
]

%Ch.Result{num_rows: 3} = Ch.query!(
  pid,
  "INSERT INTO metrics FORMAT RowBinary",
  rows,
  types: types
)

# Use type atoms for convenience
types = [:datetime, :string, :f64]
%Ch.Result{num_rows: 3} = Ch.query!(
  pid,
  "INSERT INTO metrics FORMAT RowBinary",
  rows,
  types: types
)

# Use Ch.Types helpers
types = [Ch.Types.datetime(), Ch.Types.string(), Ch.Types.f64()]
%Ch.Result{num_rows: 3} = Ch.query!(
  pid,
  "INSERT INTO metrics FORMAT RowBinary",
  rows,
  types: types
)
```

### Response
#### Success Response (200)
- **%Ch.Result{num_rows: integer}** - A struct indicating the number of rows inserted.
  - **num_rows**: The total number of rows successfully inserted.

#### Response Example
```elixir
%Ch.Result{num_rows: 3}
```
```

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

### Executing SELECT Queries

Source: https://context7.com/plausible/ch/llms.txt

Execute SELECT queries with support for native parameter binding (positional and named) and automatic result decoding.

```APIDOC
## Executing SELECT Queries

### Description
This endpoint allows you to execute `SELECT` statements against your ClickHouse database. It supports native parameter binding using both positional (`{$N}`) and named (`{name}`) placeholders, simplifying query construction and preventing SQL injection. Results are automatically decoded into Elixir terms.

### Method
`Ch.query/3` or `Ch.query!/3`

### Endpoint
N/A (Function call on a connection pool PID)

### Parameters
#### Function Arguments
- **pid** (pid) - Required - The PID of the running Ch connection pool.
- **query** (string) - Required - The SQL query string to execute.
- **params** (list | map) - Optional - A list of positional parameters or a map of named parameters.
- **opts** (keyword list) - Optional - Additional options, such as `settings` for query-specific configurations.
  - **settings** (list) - A list of ClickHouse settings (e.g., `[max_execution_time: 30]`).

### Request Example
```elixir
{:ok, pid} = Ch.start_link()

# Basic query
{:ok, %Ch.Result{rows: rows}} =
  Ch.query(pid, "SELECT number FROM system.numbers LIMIT 5")
# rows = [[0], [1], [2], [3], [4]]

# Query with positional parameters
{:ok, %Ch.Result{rows: rows}} =
  Ch.query(
    pid,
    "SELECT number FROM system.numbers WHERE number < {$0:UInt32} LIMIT {$1:UInt8}",
    [100, 5]
  )

# Query with named parameters
{:ok, %Ch.Result{rows: [[count]]}} =
  Ch.query(
    pid,
    "SELECT count() FROM events WHERE date >= {start:Date} AND date <= {end:Date}",
    %{"start" => ~D[2024-01-01], "end" => ~D[2024-12-31]}
  )

# Query with custom settings
{:ok, result} = Ch.query(
  pid,
  "SELECT * FROM large_table",
  [],
  settings: [max_execution_time: 30, max_memory_usage: 10_000_000_000]
)
```

### Response
#### Success Response (200)
- **%Ch.Result{rows: list, column_names: list, column_types: list, num_rows: integer, ...}** - A struct containing the query results.
  - **rows**: A list of lists, where each inner list represents a row.
  - **column_names**: A list of strings representing the column names.
  - **column_types**: A list of strings representing the column data types.
  - **num_rows**: The total number of rows returned.

#### Response Example
```elixir
{:ok, %Ch.Result{rows: [[0], [1], [2], [3], [4]], column_names: ["number"], column_types: ["UInt64"], num_rows: 5}}
```
```

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

### Handle Timezones in RowBinary with Elixir

Source: https://github.com/plausible/ch/blob/master/README.md

Demonstrates how to handle timezone-aware DateTime values when using the RowBinary format with the `ch` Elixir client. It shows how to configure a timezone database and how to cast and insert DateTime values with different timezones, including UTC and 'Asia/Taipei'.

```elixir
Mix.install([:ch, :tz])

:ok = Calendar.put_time_zone_database(Tz.TimeZoneDatabase)

{:ok, pid} = Ch.start_link()

%Ch.Result{rows: [[~N[2023-04-25 17:45:09]]]} =
  Ch.query!(pid, "SELECT CAST(now() as DateTime)")

%Ch.Result{rows: [[~U[2023-04-25 17:45:11Z]]]} =
  Ch.query!(pid, "SELECT CAST(now() as DateTime('UTC'))")

%Ch.Result{rows: [[%DateTime{time_zone: "Asia/Taipei"} = taipei]]} =
  Ch.query!(pid, "SELECT CAST(now() as DateTime('Asia/Taipei'))")

"2023-04-26 01:45:12+08:00 CST Asia/Taipei" = to_string(taipei)
```

```elixir
Mix.install([:ch, :tz])

:ok = Calendar.put_time_zone_database(Tz.TimeZoneDatabase)

{:ok, pid} = Ch.start_link()

Ch.query!(pid, "CREATE TABLE ch_datetimes(name String, datetime DateTime) ENGINE Memory")

naive = NaiveDateTime.utc_now()
utc = DateTime.utc_now()
taipei = DateTime.shift_zone!(utc, "Asia/Taipei")

rows = [["naive", naive], ["utc", utc], ["taipei", taipei]]

Ch.query!(pid, "INSERT INTO ch_datetimes(name, datetime) FORMAT RowBinary", rows, types: ["String", "DateTime"])

%Ch.Result{
  rows: [
    ["naive", ~U[2024-12-21 05:24:40Z]],
    ["utc", ~U[2024-12-21 05:24:40Z]],
    ["taipei", ~U[2024-12-21 05:24:40Z]]
  ]
} =
  Ch.query!(pid, "SELECT name, CAST(datetime as DateTime('UTC')) FROM ch_datetimes")
```

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

### Execute SELECT Queries with Ch in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Executes SELECT queries against ClickHouse using the Ch client. Supports basic queries, positional parameters, named parameters, and custom query settings like `max_execution_time` and `max_memory_usage`. Results are automatically decoded.

```elixir
iex> {:ok, pid} = Ch.start_link()

iex> {:ok, %Ch.Result{rows: rows}} =
...>   Ch.query(pid, "SELECT number FROM system.numbers LIMIT 5")
# rows = [[0], [1], [2], [3], [4]]

iex> {:ok, %Ch.Result{rows: rows}} =
...>   Ch.query(
...>     pid,
...>     "SELECT number FROM system.numbers WHERE number < {$0:UInt32} LIMIT {$1:UInt8}",
...>     [100, 5]
...>   )

iex> {:ok, %Ch.Result{rows: [[count]]}} =
...>   Ch.query(
...>     pid,
...>     "SELECT count() FROM events WHERE date >= {start:Date} AND date <= {end:Date}",
...>     %{"start" => ~D[2024-01-01], "end" => ~D[2024-12-31]}
...>   )

iex> {:ok, result} = Ch.query(
...>   pid,
...>   "SELECT * FROM large_table",
...>   [],
...>   settings: [max_execution_time: 30, max_memory_usage: 10_000_000_000]
...> )
```

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

### Inserting Data with VALUES

Source: https://context7.com/plausible/ch/llms.txt

Insert rows into ClickHouse tables using the SQL VALUES syntax, supporting parameter binding for dynamic data.

```APIDOC
## Inserting Data with VALUES

### Description
This endpoint facilitates data insertion into ClickHouse tables using the standard SQL `VALUES` clause. It leverages parameter binding (positional and named) to safely and efficiently insert multiple rows of data.

### Method
`Ch.query/3` or `Ch.query!/3`

### Endpoint
N/A (Function call on a connection pool PID)

### Parameters
#### Function Arguments
- **pid** (pid) - Required - The PID of the running Ch connection pool.
- **query** (string) - Required - The `INSERT INTO ... VALUES ...` SQL query string.
- **params** (list | map) - Required - A list of positional parameters or a map of named parameters corresponding to the placeholders in the query.

### Request Example
```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, """
  CREATE TABLE IF NOT EXISTS events (
    id UInt64,
    name String,
    timestamp DateTime,
    value Float64
  ) ENGINE MergeTree()
  ORDER BY (timestamp, id)
")

# Insert with positional parameters
%Ch.Result{num_rows: 3} = Ch.query!(
  pid,
  "INSERT INTO events VALUES ({$0:UInt64}, {$1:String}, {$2:DateTime}, {$3:Float64}), ({$4:UInt64}, {$5:String}, {$6:DateTime}, {$7:Float64}), ({$8:UInt64}, {$9:String}, {$10:DateTime}, {$11:Float64})",
  [1, "click", DateTime.utc_now(), 42.5, 2, "view", DateTime.utc_now(), 10.0, 3, "purchase", DateTime.utc_now(), 99.99]
)

# Insert with named parameters
%Ch.Result{num_rows: 1} = Ch.query!(
  pid,
  "INSERT INTO events VALUES ({id:UInt64}, {name:String}, {timestamp:DateTime}, {value:Float64})",
  %{"id" => 4, "name" => "signup", "timestamp" => DateTime.utc_now(), "value" => 0.0}
)
```

### Response
#### Success Response (200)
- **%Ch.Result{num_rows: integer}** - A struct indicating the number of rows inserted.
  - **num_rows**: The total number of rows successfully inserted.

#### Response Example
```elixir
%Ch.Result{num_rows: 3}
```
```

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

### Handle ClickHouse Errors in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Demonstrates effective error handling strategies for ClickHouse operations in Elixir. It covers using pattern matching with `Ch.query/4` for graceful error recovery and employing `try/rescue` with `Ch.query!/4` to catch exceptions, along with handling connection errors.

```elixir
{:ok, pid} = Ch.start_link()

# Using query/4 with pattern matching
case Ch.query(pid, "SELECT * FROM nonexistent_table") do
  {:ok, result} ->
    IO.inspect(result.rows)

  {:error, %Ch.Error{code: code, message: message}} ->
    IO.puts("ClickHouse error #{code}: #{message}")
    # ClickHouse error 60: Table default.nonexistent_table does not exist
end

# Using query!/4 with exception handling
try do
  Ch.query!(pid, "INVALID SQL SYNTAX")
rescue
  e in Ch.Error ->
    IO.puts("Query failed: #{e.message}")
    IO.inspect(e.code)
end

# Connection errors
case Ch.start_link(hostname: "nonexistent.host", timeout: 1000) do
  {:ok, _pid} -> :ok
  {:error, %Ch.Error{message: msg}} -> IO.puts("Connection failed: #{msg}")
end
```

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

### Insert Chunked RowBinary Stream

Source: https://github.com/plausible/ch/blob/master/README.md

Performs insertion of a large number of rows efficiently by streaming chunked `RowBinary` encoded data. This method uses chunked transfer encoding for lower memory usage.

```elixir
{:ok, pid} = Ch.start_link()

Ch.query!(pid, "CREATE TABLE IF NOT EXISTS ch_demo(id UInt64) ENGINE Null")

stream = Stream.repeatedly(fn -> [:rand.uniform(100)] end)
chunked = Stream.chunk_every(stream, 100)
encoded = Stream.map(chunked, fn chunk -> Ch.RowBinary.encode_rows(chunk, _types = ["UInt64"]) end)
ten_encoded_chunks = Stream.take(encoded, 10)

%Ch.Result{num_rows: 1000} =
  Ch.query(pid, "INSERT INTO ch_demo(id) FORMAT RowBinary", ten_encoded_chunks, encode: false)
```

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

### Insert Rows using RowBinaryWithNamesAndTypes Format

Source: https://github.com/plausible/ch/blob/master/README.md

Inserts rows into ClickHouse using the `RowBinaryWithNamesAndTypes` format, which includes column names and types for verification, enhancing data integrity.

```elixir
sql = "INSERT INTO ch_demo FORMAT RowBinaryWithNamesAndTypes"
opts = [names: ["id"], types: ["UInt64"]]
rows = [[0], [1]]

%Ch.Result{num_rows: 2} = Ch.query!(pid, sql, rows, opts)
```

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

### Handle UTF-8 in RowBinary with Elixir

Source: https://github.com/plausible/ch/blob/master/README.md

Illustrates how the `ch` Elixir client handles UTF-8 encoded strings in RowBinary format. It shows the default behavior of replacing non-UTF-8 characters with '' and how to retrieve raw binary data using the `:binary` type to bypass UTF-8 checks.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, "CREATE TABLE ch_utf8(str String) ENGINE Memory")

 bin = "\x61\xF0\x80\x80\x80b"
 utf8 = "ab"

 %Ch.Result{num_rows: 1} =
   Ch.query!(pid, "INSERT INTO ch_utf8(str) FORMAT RowBinary", [[bin]], types: ["String"])

 %Ch.Result{rows: [[^utf8]]} =
   Ch.query!(pid, "SELECT * FROM ch_utf8")

 %Ch.Result{rows: %{"data" => [[^utf8]]}} =
   pid |> Ch.query!("SELECT * FROM ch_utf8 FORMAT JSONCompact") |> Map.update!(:rows, &Jason.decode!/1)
```

```elixir
%Ch.Result{rows: [[^bin]]} =
  Ch.query!(pid, "SELECT * FROM ch_utf8", [], types: [:binary])
```

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

### Decode and Encode ClickHouse Type Strings

Source: https://context7.com/plausible/ch/llms.txt

Demonstrates decoding ClickHouse type strings into Elixir representations and encoding Elixir type representations back into ClickHouse strings. It also shows how to use helper functions for common and complex types like unsigned integers, decimals, and nullable arrays.

```elixir
type = Ch.Types.decode("Array(Nullable(DateTime64(3, 'UTC')))")
# {:array, {:nullable, {:datetime64, 3, "UTC"}}}

clickhouse_type = Ch.Types.encode(type) |> IO.iodata_to_binary()
# "Array(Nullable(DateTime64(3, 'UTC')))"

types = [
  Ch.Types.u64(),
  Ch.Types.string(),
  Ch.Types.datetime("UTC"),
  Ch.Types.nullable(Ch.Types.decimal(18, 4)),
  Ch.Types.array(Ch.Types.i32())
]

uint_types = [
  Ch.Types.u8(), Ch.Types.u16(), Ch.Types.u32(),
  Ch.Types.u64(), Ch.Types.u128(), Ch.Types.u256()
]

int_types = [
  Ch.Types.i8(), Ch.Types.i16(), Ch.Types.i32(),
  Ch.Types.i64(), Ch.Types.i128(), Ch.Types.i256()
]

float_types = [Ch.Types.f32(), Ch.Types.f64()]
```

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

### Handle Datetime and Timezones with ClickHouse

Source: https://context7.com/plausible/ch/llms.txt

Illustrates how to work with datetime values in ClickHouse using Elixir, including proper timezone support. It shows creating tables with different datetime types, inserting values with various timezones, and querying data while respecting timezone information.

```elixir
Mix.install([:ch, :tz])
:ok = Calendar.put_time_zone_database(Tz.TimeZoneDatabase)

{:ok, pid} = Ch.start_link()

Ch.query!(pid, """
  CREATE TABLE IF NOT EXISTS events (
    id UInt32,
    naive_dt DateTime,
    utc_dt DateTime('UTC'),
    tokyo_dt DateTime('Asia/Tokyo'),
    precise_dt DateTime64(3)
  ) ENGINE MergeTree()
  ORDER BY id
")

naive = ~N[2024-01-15 10:30:00]
utc = DateTime.utc_now()
tokyo = DateTime.shift_zone!(utc, "Asia/Tokyo")

types = [
  "UInt32",
  "DateTime",
  "DateTime('UTC')",
  "DateTime('Asia/Tokyo')",
  "DateTime64(3)"
]

rows = [[1, naive, utc, tokyo, utc]]

%Ch.Result{num_rows: 1} = Ch.query!(
  pid,
  "INSERT INTO events FORMAT RowBinary",
  rows,
  types: types
)

# Query with automatic timezone decoding
{:ok, %Ch.Result{rows: [[_id, n, u, t, p]]}} = Ch.query(
  pid,
  "SELECT * FROM events WHERE id = 1"
)
# n is %NaiveDateTime{}
# u is %DateTime{time_zone: "Etc/UTC"}
# t is %DateTime{time_zone: "Asia/Tokyo"}
# p is %DateTime{} with microsecond precision
```

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

### Insert Data with RowBinary Format using Ch in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Performs high-performance bulk inserts into ClickHouse using the RowBinary format. This method is significantly more efficient for large datasets than text-based inserts. It supports defining column types as strings, atoms, or using helper functions from `Ch.Types`.

```elixir
iex> {:ok, pid} = Ch.start_link()

iex> Ch.query!(pid, """
...>   CREATE TABLE IF NOT EXISTS metrics (
...>     timestamp DateTime,
...>     metric_name String,
...>     value Float64
...>   ) ENGINE MergeTree()
...>   ORDER BY timestamp
...> """)

iex> types = ["DateTime", "String", "Float64"]
iex> rows = [
...>   [DateTime.utc_now(), "cpu_usage", 45.2],
...>   [DateTime.utc_now(), "memory_usage", 78.5],
...>   [DateTime.utc_now(), "disk_io", 120.0]
...> ]

iex> %Ch.Result{num_rows: 3} = Ch.query!(
...>   pid,
...>   "INSERT INTO metrics FORMAT RowBinary",
...>   rows,
...>   types: types
...> )

iex> # Use type atoms for convenience
iex> types = [:datetime, :string, :f64]
iex> %Ch.Result{num_rows: 3} = Ch.query!(
...>   pid,
...>   "INSERT INTO metrics FORMAT RowBinary",
...>   rows,
...>   types: types
...> )

iex> # Use Ch.Types helpers
iex> types = [Ch.Types.datetime(), Ch.Types.string(), Ch.Types.f64()]
iex> %Ch.Result{num_rows: 3} = Ch.query!(
...>   pid,
...>   "INSERT INTO metrics FORMAT RowBinary",
...>   rows,
...>   types: types
...> )
```

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

### Stream Inserts with Chunked Data in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Efficiently insert large datasets into ClickHouse by using chunked streaming to minimize memory usage. This method involves creating a stream of data, chunking it, encoding each chunk, and then inserting the encoded chunks.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE IF NOT EXISTS sensor_data (
 sensor_id UInt32,
 reading Float64
 ) ENGINE MergeTree()
 ORDER BY sensor_id
""")

 # Create a stream of data
 data_stream = Stream.repeatedly(fn ->
 [:rand.uniform(1000), :rand.uniform() * 100.0]
 end)

 # Chunk and encode the stream
 chunked = Stream.chunk_every(data_stream, 1000)
 encoded = Stream.map(chunked, fn chunk ->
 Ch.RowBinary.encode_rows(chunk, ["UInt32", "Float64"])
 end)

 # Take 100 chunks (100,000 rows total)
 limited = Stream.take(encoded, 100)

 # Insert using chunked transfer encoding
 %Ch.Result{num_rows: 100_000} = Ch.query(
 pid,
 "INSERT INTO sensor_data FORMAT RowBinary",
 limited,
 encode: false
 )
```

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

### Use Ch.Types for Type Definitions in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Utilize helper functions from Ch.Types to define and work with ClickHouse type definitions in Elixir. This allows for programmatic construction of type specifications, which is particularly useful when dealing with complex or nested data structures.

```elixir
# Example usage for Ch.Types helpers would go here.
# This snippet is a placeholder as the provided text only mentions the utility.
```

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

### Insert Data with Custom Formats (CSV, TSV) in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Insert data into ClickHouse tables using custom formats such as CSV and TSV. This method is useful for integrating data from various sources that can be represented in these common delimited formats. Ensure the data conforms to the specified format.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE IF NOT EXISTS products (
 id UInt32,
 name String,
 price Decimal(10, 2)
 ) ENGINE MergeTree()
 ORDER BY id
""")

 # Insert CSV data
 csv_data = """
1,Widget,19.99
2,Gadget,29.99
3,Doohickey,39.99
"""

 %Ch.Result{num_rows: 3} = Ch.query!(pid,
 "INSERT INTO products FORMAT CSV",
 csv_data,
 encode: false
)

 # Insert TSV data
 tsv_data = [
 ["4", "Thingamajig", "49.99"],
 ["5", "Whatsit", "59.99"]
 ]
 |> Enum.map(&Enum.join(&1, "\t"))
 |> Enum.join("\n")

 %Ch.Result{num_rows: 2} = Ch.query!(pid,
 "INSERT INTO products FORMAT TSV",
 tsv_data,
 encode: false
)
```

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

### Insert Data with RowBinaryWithNamesAndTypes in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Insert data into ClickHouse tables using the RowBinaryWithNamesAndTypes format for explicit type validation. This method ensures that the data types provided match the table schema during insertion. It requires specifying column names and their corresponding types.

```elixir
 {:ok, pid} = Ch.start_link()

 Ch.query!(pid, """
 CREATE TABLE IF NOT EXISTS users (
 id UInt64,
 email String,
 created_at DateTime
 ) ENGINE MergeTree()
 ORDER BY id
""")

 sql = "INSERT INTO users FORMAT RowBinaryWithNamesAndTypes"
 opts = [
 names: ["id", "email", "created_at"],
 types: ["UInt64", "String", "DateTime"]
 ]
 rows = [
 [1, "user1@example.com", DateTime.utc_now()],
 [2, "user2@example.com", DateTime.utc_now()]
 ]

 %Ch.Result{num_rows: 2} = Ch.query!(pid, sql, rows, opts)
```

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

### Stream Large Result Sets in Elixir

Source: https://context7.com/plausible/ch/llms.txt

Handle large query result sets that exceed available memory by streaming them. This approach processes data in chunks, making it suitable for very large datasets. It utilizes DBConnection.stream for efficient chunk processing.

```elixir
 {:ok, pid} = Ch.start_link()

 stream = Ch.stream(
 pid,
 "SELECT number, number * 2 FROM system.numbers LIMIT 1000000"
 )

 # Process results in chunks using DBConnection.stream
 DBConnection.stream(pid, fn conn ->
 stream
 |> Enum.reduce(0, fn %Ch.Result{rows: rows}, acc ->
 # Process each chunk of rows
 count = length(rows)
 IO.puts("Processing chunk of #{count} rows")
 acc + count
 end)
 end)
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.