### ClickHouse SQL: Example Table Setup and Data Insertion

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

Provides SQL statements to create a sample table named `salaries` with columns for team, player, salary, and position. It then populates this table with sample data using `INSERT INTO` with the `FORMAT Values` clause, setting up the data for the `dense_rank()` function example.

```sql
CREATE TABLE salaries
(
    `team` String,
    `player` String,
    `salary` UInt32,
    `position` String
)
Engine = Memory;

INSERT INTO salaries FORMAT Values
    ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'),
    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),
    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),
    ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'),
    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'),
    ('South Hampton Seagulls', 'Douglas Benson', 150000, 'M'),
    ('South Hampton Seagulls', 'James Henderson', 140000, 'M');
```

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

### Setup Bitmap Table and Data for groupBitmapAnd Examples (SQL)

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

Creates a table with a bitmap column and inserts sample data using bitmapBuild for demonstrating the groupBitmapAnd and groupBitmapAndState functions.

```sql
DROP TABLE IF EXISTS bitmap_column_expr_test2;
CREATE TABLE bitmap_column_expr_test2
(
    tag_id String,
    z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] AS Array(UInt32))));
```

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

### SQL: UNDROP TABLE syntax and example

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

Demonstrates the syntax for the UNDROP TABLE statement and provides a complete SQL example. The example includes creating a table, dropping it, querying the system.dropped_tables table to show the dropped table's details, and then using UNDROP TABLE to recover it. Finally, it verifies the table's existence with DESCRIBE TABLE.

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

DROP TABLE tab;

SELECT *  
FROM system.dropped_tables  
FORMAT Vertical;

UNDROP TABLE tab;

SELECT * 
FROM system.dropped_tables 
FORMAT Vertical;

DESCRIBE TABLE tab
FORMAT Vertical;
```

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

### Examples of granted queries with database prefix wildcard

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

Demonstrates queries that are granted when using a wildcard for the database name prefix. This shows the effect of `GRANT SELECT ON db*.* TO john` on tables across different databases starting with 'db'.

```sql
SELECT * FROM db.my_tables -- granted
SELECT * FROM db.my_tables_0 -- granted
SELECT * FROM db.my_tables_1 -- granted
SELECT * FROM db.other_table -- granted
SELECT * FROM db2.my_tables -- granted
```

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

### Show Columns Example - ClickHouse SQL

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

An example of using the SHOW COLUMNS statement to retrieve information about columns in the 'orders' table that start with 'delivery_'. This demonstrates pattern matching for column retrieval.

```sql
SHOW COLUMNS FROM 'orders' LIKE 'delivery_%'
```

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

### Create and Populate Employees Table for Sum() Example in SQL

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

This SQL code demonstrates the creation of an 'employees' table with 'id', 'name', and 'salary' columns, followed by the insertion of sample employee data. This setup is used to illustrate the application of the `sum()` function.

```sql
CREATE TABLE employees
(
    `id` UInt32,
    `name` String,
    `salary` UInt32
)
ENGINE = Log
```

```sql
INSERT INTO employees VALUES
    (87432, 'John Smith', 45680),
    (59018, 'Jane Smith', 72350),
    (20376, 'Ivan Ivanovich', 58900),
    (71245, 'Anastasia Ivanovna', 89210);
```

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

### Install PostgreSQL ODBC Driver on Ubuntu

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Installs the necessary packages on Ubuntu for connecting to PostgreSQL via ODBC. Requires `unixodbc` and the PostgreSQL ODBC driver.

```bash
$ sudo apt-get install -y unixodbc odbcinst odbc-postgresql

```

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

### Define and Populate Table for first_value() Examples (SQL)

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

This SQL snippet defines a temporary table 'test_data' with an integer column 'a' and a nullable integer column 'b', then populates it with sample data including NULL values. This setup is used for demonstrating the behavior of the first_value() function.

```sql
CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) VALUES (1,null), (2,3), (4, 5), (6,null);

```

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

### Install MS SQL ODBC Driver on Ubuntu

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Installs the FreeTDS package on Ubuntu, which provides ODBC drivers for connecting to MS SQL Server. Includes tools for testing the connection.

```bash
$ sudo apt-get install tdsodbc freetds-bin sqsh

```

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

### Create a Window View for Aggregating Data (ClickHouse SQL)

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

This example demonstrates creating a window view to count logs within 10-second intervals from a 'data' table. It uses the tumble function for time windowing and groups the results by the window start time.

```sql
CREATE TABLE data ( `id` UInt64, `timestamp` DateTime) ENGINE = Memory;
CREATE WINDOW VIEW wv as select count(id), tumbleStart(w_id) as window_start from data group by tumble(timestamp, INTERVAL '10' SECOND) as w_id
```

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

### Show Dictionaries Example - ClickHouse SQL

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

An example demonstrating how to list the first two dictionaries from the 'db' database whose names contain 'reg'. This showcases the use of FROM, LIKE, and LIMIT clauses.

```sql
SHOW DICTIONARIES FROM db LIKE '%reg%' LIMIT 2
```

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

### groupBitmapXor Example: Calculating Cardinality

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

Demonstrates how to use the groupBitmapXor function to calculate the XOR of bitmap states and retrieve the resulting cardinality. This example sets up a table, inserts data, and then queries the cardinality.

```sql
DROP TABLE IF EXISTS bitmap_column_expr_test2;
CREATE TABLE bitmap_column_expr_test2
(
    tag_id String,
    z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] AS Array(UInt32))));

SELECT groupBitmapXor(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

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

### Create and Insert Data for sumMapFilteredWithOverflow Example

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

This snippet demonstrates how to create a table named 'sum_map' and insert sample data, which will be used in subsequent examples for sumMapFilteredWithOverflow and sumMapFiltered functions.

```sql
CREATE TABLE sum_map
(
    `date` Date,
    `timeslot` DateTime,
    `statusMap` Nested(status UInt8, requests UInt8)
)
ENGINE = Log

INSERT INTO sum_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10]),
    ('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10]);

```

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

### Example: Restoring a Replica in ClickHouse

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

Illustrates restoring a replica for a ReplicatedMergeTree table after its ZooKeeper metadata is lost. The example shows table creation, data insertion, simulated metadata loss, and the replica restore command.

```sql
CREATE TABLE test(n UInt32)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/test/', '{replica}')
ORDER BY n PARTITION BY n % 10;

INSERT INTO test SELECT * FROM numbers(1000);

-- zookeeper_delete_path("/clickhouse/tables/test", recursive=True) <- root loss.

SYSTEM RESTART REPLICA test;
SYSTEM RESTORE REPLICA test;
```

```sql
SYSTEM RESTORE REPLICA test ON CLUSTER cluster;
```

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

### Example: Creating a table using generateSeries

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

Shows how to create a persistent table named 'series' using the `generateSeries` table function. This table will contain the generated sequence of numbers.

```sql
CREATE TABLE series AS generateSeries(1, 5);
SELECT * FROM series;

```

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

### Get First 5 String Implementations using groupArraySorted (ClickHouse SQL)

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

Example showing the use of groupArraySorted to get the first 5 string representations of numbers from a subquery in ClickHouse SQL.

```sql
SELECT groupArraySorted(5)(str) FROM (SELECT toString(number) AS str FROM numbers(5))
```

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

### Show Clusters Examples

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

Demonstrates how to list all clusters, filter clusters using LIKE patterns with a limit, and display detailed information for a specific cluster using the FORMAT Vertical option.

```sql
SHOW CLUSTERS;

┌─cluster──────────────────────────────────────┐
│ test_cluster_two_shards                      │
│ test_cluster_two_shards_internal_replication │
│ test_cluster_two_shards_localhost            │
│ test_shard_localhost                         │
│ test_shard_localhost_secure                  │
│ test_unavailable_shard                       │
└──────────────────────────────────────────────┘

SHOW CLUSTERS LIKE 'test%' LIMIT 1;

┌─cluster─────────────────┐
│ test_cluster_two_shards │
└─────────────────────────┘

SHOW CLUSTER 'test_shard_localhost' FORMAT Vertical;

Row 1:
──────
cluster:                 test_shard_localhost
shard_num:               1
replica_num:             1
host_name:               localhost
host_address:            127.0.0.1
port:                    9000
```

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

### maxIntersections Function Syntax and Usage - ClickHouse SQL

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

Defines the syntax and provides an example for the `maxIntersections` aggregate function in ClickHouse SQL. This function calculates the maximum number of overlapping intervals based on specified start and end columns. Intervals are skipped if their start or end values are NULL or 0. The example includes table creation, data insertion, and a SELECT query to demonstrate the function's output.

```sql
CREATE TABLE my_events (
    start UInt32,
    end UInt32
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO my_events VALUES
   (1, 3),
   (1, 6),
   (2, 5),
   (3, 7);

SELECT maxIntersections(start, end) FROM my_events;
```

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

### Replicated Setup INSERT Considerations

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

Details on inserting data into a replicated ClickHouse setup. Explains that data becomes visible on other replicas after replication, which starts immediately after an INSERT. Notes potential latency due to ClickHouse Keeper commits and S3 storage.

```APIDOC
## Inserting into a Replicated Setup

### Description
In a replicated ClickHouse setup, data inserted into a table becomes visible on other replicas once it has been successfully replicated. Replication begins immediately after an `INSERT` operation completes. This process differs from ClickHouse Cloud where data is written to shared storage first.

### Method
`INSERT INTO ...`

### Endpoint
Not applicable (applies to the replicated ClickHouse cluster)

### Parameters
None specific to this documentation section.

### Considerations
- `INSERT` operations in replicated setups require committing to ClickHouse Keeper for distributed consensus, which can introduce latency (potentially up to one second).
- Using S3 for storage can add further latency to the replication process.

### Response
Data is replicated across all nodes in the setup, eventually becoming visible on all replicas.
```

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

### Example: Inserting data into a file using the file table function

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

Demonstrates how to insert data from an existing table ('series') into a file ('numbers.csv') using the `file` table function. This allows exporting query results to files.

```sql
INSERT INTO FUNCTION file('numbers.csv', 'CSV') SELECT * FROM series;

```

```bash
cat numbers.csv

```

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

### Insecure ODBC Connection String Example

Source: https://clickhouse.com/docs/sql-reference/dictionaries

This example illustrates an insecure way to connect via ODBC where sensitive credentials might be exposed. It shows a typical unixODBC configuration file (`odbc.ini`) and a subsequent SQL query that could potentially send stored credentials to an unintended server. Always review and secure connection parameters.

```text
[gregtest]
Driver = /usr/lib/psqlodbca.so
Servername = localhost
PORT = 5432
DATABASE = test_db
#OPTION = 3
USERNAME = test
PASSWORD = test
```

```sql
SELECT * FROM odbc('DSN=gregtest;Servername=some-server.com', 'test_db');
```

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

### Show Settings Examples

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

Illustrates using the SHOW SETTINGS statement with LIKE, ILIKE for pattern matching on setting names, and the CHANGED clause to display only modified settings.

```sql
SHOW SETTINGS LIKE 'send_timeout';

┌─name─────────┬─type────┬─value─┐
│ send_timeout │ Seconds │ 300   │
└──────────────┴─────────┴───────┘

SHOW SETTINGS ILIKE '%CONNECT_timeout%'

┌─name────────────────────────────────────┬─type─────────┬─value─┐
│ connect_timeout                         │ Seconds      │ 10    │
│ connect_timeout_with_failover_ms        │ Milliseconds │ 50    │
│ connect_timeout_with_failover_secure_ms │ Milliseconds │ 100   │
└─────────────────────────────────────────┴──────────────┴───────┘

SHOW CHANGED SETTINGS ILIKE '%MEMORY%'

┌─name─────────────┬─type───┬─value───────┐
│ max_memory_usage │ UInt64 │ 10000000000 │
└──────────────────┴────────┴─────────────┘
```

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

### ClickHouse SQL: LIMIT BY with Offset

Source: https://clickhouse.com/docs/sql-reference/statements/select/limit-by

Illustrates using LIMIT BY with an offset to skip rows within each group. This example selects rows starting from the second row for each distinct 'id'.

```sql
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
```

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

### EXPLAIN AST for SELECT Query

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

Demonstrates the 'EXPLAIN AST' command to retrieve the Abstract Syntax Tree (AST) of a simple SELECT query. This output helps in understanding the query's structure before execution.

```sql
EXPLAIN AST SELECT 1;
```

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

### Create and Replace Table Example (ClickHouse SQL - Local)

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

This SQL example shows the creation of a table and its subsequent replacement with new data and a modified structure in a local ClickHouse environment. It demonstrates the use of CREATE OR REPLACE TABLE for both data clearing and schema changes.

```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;

┌─n─┬─s────┐
│ 1 │ test │
└───┴──────┘

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

INSERT INTO base.t1 VALUES (2, null);

SELECT * FROM base.t1;

┌─n─┬─s──┐
│ 2 │ \N │
└───┴────┘

REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

┌─n─┐
│ 3 │
└───┘
```

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

### Get First 10 Numbers using groupArraySorted (ClickHouse SQL)

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

Example demonstrating how to use the groupArraySorted function to retrieve the first 10 numbers from the 'numbers(100)' table in ClickHouse SQL.

```sql
SELECT groupArraySorted(10)(number) FROM numbers(100)
```

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

### EXPLAIN Statement Syntax and Example

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

This snippet shows the general syntax for the EXPLAIN statement in ClickHouse, along with a practical example demonstrating its use with a UNION ALL query. It illustrates how to examine the execution plan of complex queries.

```sql
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]  
    [  
      SELECT ... |  
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]  
    ]  
    [FORMAT ...]  

EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;
```

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

### Example: Create and Query a Temporary View (ClickHouse SQL)

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

This example creates a temporary table 't_src', inserts data, and then defines a temporary view 'tview' to process the data. It demonstrates querying the temporary view and displaying its DDL.

```sql
CREATE TEMPORARY TABLE t_src (id UInt32, val String);
INSERT INTO t_src VALUES (1, 'a'), (2, 'b');

CREATE TEMPORARY VIEW tview AS
SELECT id, upper(val) AS u
FROM t_src
WHERE id <= 2;

SELECT * FROM tview ORDER BY id;

SHOW CREATE TEMPORARY VIEW tview;

DROP TEMPORARY VIEW IF EXISTS tview;
```

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

### Get Region Hierarchy (SQL)

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

Returns an array of region IDs, starting with the given region ID and including all its parent regions up the hierarchy. An optional 'geobase' parameter can be specified. The result is an array of UInt32.

```SQL
SELECT regionHierarchy(number::UInt32) AS arr, arrayMap(id -> regionToName(id, 'en'), arr) FROM numbers(5);
```

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

### ClickHouse SQL: last_value Example - Table Setup and Data Insertion

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

Sets up a temporary 'salaries' table and inserts sample data for demonstrating the last_value function in ClickHouse SQL. The table includes player information and their salaries.

```sql
DROP TABLE IF EXISTS salaries;
CREATE TABLE salaries
(
    `team` String,
    `player` String,
    `salary` UInt32,
    `position` String
)
Engine = Memory;

INSERT INTO salaries FORMAT VALUES
    ('Port Elizabeth Barbarians', 'Gary Chen', 196000, 'F'),
    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),
    ('Port Elizabeth Barbarians', 'Michael Stanley', 100000, 'D'),
    ('New Coreystad Archdukes', 'Scott Harrison', 180000, 'D'),
    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'),
    ('South Hampton Seagulls', 'Douglas Benson', 150000, 'M'),
    ('South Hampton Seagulls', 'James Henderson', 140000, 'M');
```

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

### EXECUTE AS Example with currentUser() and authenticatedUser()

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

Illustrates how to use EXECUTE AS with a subquery and shows the output of currentUser() and authenticatedUser() before and after impersonation.

```sql
SELECT currentUser(), authenticatedUser(); -- outputs "default    default"
CREATE USER james;
EXECUTE AS james SELECT currentUser(), authenticatedUser(); -- outputs "james    default"
```

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

### ClickHouse SQL: Create and Insert Test Data

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

Defines a temporary table 'test_data' with integer and nullable integer columns and populates it with sample data. This setup is used to demonstrate the behavior of the `last_value` function in subsequent examples.

```sql
CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) VALUES (1,null), (2,3), (4, 5), (6,null)
```

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

### EXPLAIN SYNTAX with query tree passes enabled

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

Demonstrates 'EXPLAIN SYNTAX' with the 'run_query_tree_passes' setting enabled. This output reflects query transformations after running specific optimization passes, providing a more optimized query representation.

```sql
EXPLAIN SYNTAX run_query_tree_passes = 1 SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c WHERE a.number = b.number AND b.number = c.number;
```

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

### ClickHouse SQL tumbleStart() function example

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

Illustrates the tumbleStart() function in ClickHouse SQL, which returns the inclusive lower bound of a tumbling time window. This function is useful for identifying the start of a fixed-duration, continuous time window.

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

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

### Create and Replace Table Example (ClickHouse SQL - Cloud)

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

This SQL example illustrates the process of creating and then replacing a table in ClickHouse Cloud. It demonstrates how to modify table structure and clear data using the CREATE OR REPLACE TABLE statement in a cloud environment.

```sql
CREATE DATABASE base;

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;

1    test

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

INSERT INTO base.t1 VALUES (2, null);

SELECT * FROM base.t1;

2    

REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

3
```

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

### Get Row Number Across All Blocks - ClickHouse SQL

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

The `rowNumberInAllBlocks()` function returns a unique row number for each row processed across all data blocks, starting from 0. It does not take any arguments. The returned value is of type UInt64.

```sql
SELECT rowNumberInAllBlocks()
FROM
(
    SELECT * FROM system.numbers_mt LIMIT 10
)
SETTINGS max_block_size = 2
```

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

### Get Transaction ID in ClickHouse SQL

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

Retrieves the unique identifier for the current transaction. This function returns a tuple containing the transaction's start CSN, local transaction ID, and host ID. It requires experimental transaction support to be enabled.

```sql
BEGIN TRANSACTION;
SELECT transactionID();
ROLLBACK;
```

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

### Configure Local File Source (DDL)

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Demonstrates configuring a local file as a dictionary source using a DDL query, including path, format, and optional settings.

```sql
SOURCE(FILE(path './user_files/os.tsv' format 'TabSeparated'))
SETTINGS(format_csv_allow_single_quotes = 0)
```

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

### Get Server UUID (ClickHouse SQL)

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

The serverUUID function returns the universally unique identifier (UUID v4) that was generated when the ClickHouse server was first started. This UUID is persistent across server restarts. It is useful for identifying specific server instances.

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

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

### ClickHouse SQL tumbleEnd() function example

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

Demonstrates the usage of the tumbleEnd() function in ClickHouse SQL to get the exclusive upper bound of a tumbling time window. This function helps in determining the end of a fixed-duration, non-overlapping time window.

```SQL
SELECT tumbleEnd(now(), toIntervalDay('1'))
```

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

### Get Descendants from Hierarchical Dictionary

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

The dictGetDescendants function recursively retrieves all descendants of a given key in a hierarchical dictionary up to a specified level. If level is 0, it returns all descendants. The function requires the dictionary name, the key to start from, and the hierarchy level. It returns an array of UInt64 representing the descendant keys.

```sql
SELECT dictGetDescendants('hierarchical_dictionary', 0, 2);
```

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

### Basic ORDER BY Clause Examples in ClickHouse SQL

Source: https://clickhouse.com/docs/sql-reference/statements/select/order-by

Demonstrates various ways to use the ORDER BY clause in ClickHouse SQL, including sorting by multiple columns, positional arguments, and the ALL keyword. It also shows how to disable positional arguments and the ALL keyword using settings.

```sql
SELECT visits, search_phrase
FROM example_table
ORDER BY visits, search_phrase;
```

```sql
SELECT column1, column2
FROM example_table
ORDER BY 2, 1;
```

```sql
SELECT * FROM example_table ORDER BY ALL;
```

```sql
SET enable_positional_arguments = 0;
SET enable_order_by_all = 0;
```

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

### Configure Local File Source (XML)

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Example of configuring a local file as a dictionary source using XML, specifying the file path and format.

```xml
<source>
  <file>
    <path>/opt/dictionaries/os.tsv</path>
    <format>TabSeparated</format>
  </file>
  <settings>
      <format_csv_allow_single_quotes>0</format_csv_allow_single_quotes>
  </settings>
</source>
```

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

### ClickHouse SQL Type Promotion Example

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

Demonstrates ClickHouse SQL's integer size promotion rules for arithmetic operations by showing the type changes with repeated addition of zeros.

```sql
SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)

```

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

### Get Week Number with toWeek() in ClickHouse SQL

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

The toWeek() function in ClickHouse SQL returns the week number for a given date or datetime. It supports various modes to define the start of the week and the week number range. An alias for this function is 'week'.

```sql
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
```

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

### Example: Using generateSeries table function

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

Illustrates the use of the `generateSeries` table function to create a table with a sequence of integers. This is useful for generating test data or series.

```sql
SELECT * FROM generateSeries(1,5);

```

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

### Get ISO Week Number with toISOWeek

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

Returns the ISO 8601 week number for a given date or datetime value. Weeks start on Monday, and the first week of the year includes January 4th. The function supports an optional timezone argument.

```sql
SELECT toDate('2016-12-27') AS date, toISOWeek(date) AS isoWeek
```

```sql
SELECT toDate('2025-12-29') AS date, toISOWeek(date) AS isoWeek, toYear(date) AS year
```

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

### Configure MySQL Source with Socket (XML)

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Example configuration for a MySQL data source using XML, specifying a local socket connection. This is useful for connecting to MySQL on the same host.

```xml
<source>
  <mysql>
      <host>localhost</host>
      <socket>/path/to/socket/file.sock</socket>
      <user>clickhouse</user>
      <password>qwerty</password>
      <db>db_name</db>
      <table>table_name</table>
      <where>id=10</where>
      <invalidate_query>SQL_QUERY</invalidate_query>
      <fail_on_connection_loss>true</fail_on_connection_loss>
      <query>SELECT id, value_1, value_2 FROM db_name.table_name</query>
  </mysql>
</source>
```

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

### Partitioning with groupArray()

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

Demonstrates partitioning a table by `part_key` and using `groupArray(value)` to aggregate values within each partition. The output shows each row along with the array of values belonging to its partition.

```sql
SELECT
    part_key,
    value,
    order,
    groupArray(value) OVER (PARTITION BY part_key) AS frame_values
FROM wf_partition
ORDER BY
    part_key ASC,
    value ASC;

```

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

### Generate Dates using numbers() Function

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

Shows a practical example of using the numbers() table function to generate a sequence of dates. By adding the generated numbers to a starting date, it creates a series of consecutive dates, useful for time-series analysis or data generation.

```sql
-- Generate a sequence of dates from 2010-01-01 to 2010-12-31
SELECT toDate('2010-01-01') + number AS d FROM numbers(365);
```

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

### ClickHouse ATTACH TABLE Example: Create and Select

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

An example demonstrating the creation of a table using ATTACH with data from a file, followed by querying the attached table. It includes dropping the table if it exists, inserting data into a temporary file table, attaching the table, and then selecting from it.

```sql
DROP TABLE IF EXISTS test;
INSERT INTO TABLE FUNCTION file('01188_attach/test/data.TSV', 'TSV', 's String, n UInt8') VALUES ('test', 42);
ATTACH TABLE test FROM '01188_attach/test' (s String, n UInt8) ENGINE = File(TSV);
SELECT * FROM test;
```

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

### Get Row Number Within a Block - ClickHouse SQL

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

The `rowNumberInBlock()` function returns the ordinal number of the current row within its data block, starting from 0 for each block. It has no arguments and returns a UInt64 value. This function is useful for processing rows independently within each block.

```sql
SELECT rowNumberInBlock()
FROM
(
    SELECT * FROM system.numbers_mt LIMIT 10
) SETTINGS max_block_size = 2
```

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

### ClickHouse SQL: Inserting Data for fileCluster Example

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

Shows how to insert sample data into CSV files on ClickHouse cluster nodes, which can then be read using the fileCluster table function. This setup ensures that the files exist and have consistent content across nodes for the fileCluster function to work correctly.

```sql
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');
```

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

### Create and Populate Partition Table

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

Defines a temporary table `wf_partition` with columns `part_key`, `value`, and `order`, and populates it with sample data. This table is used to demonstrate 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);

```

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

### Basic DDL Dictionary Source Configuration

Source: https://clickhouse.com/docs/sql-reference/dictionaries

Shows the equivalent DDL query format for configuring a dictionary source, using a placeholder for the source type and parameters.

```sql
CREATE DICTIONARY dict_name (...) 
...
SOURCE(SOURCE_TYPE(param1 val1 ... paramN valN)) -- Source configuration
...
```

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

### Tree Traversal using Recursive CTE

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

This example shows how to traverse a tree structure stored in a ClickHouse table using a recursive CTE. The CTE 'search_tree' starts from a root node (id=0) and recursively selects child nodes based on their parent_id. The final SELECT statement retrieves all traversed nodes.

```sql
WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree;
```

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

### Get Current Shard Index (ClickHouse SQL)

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

The shardNum function returns the index of the shard that is processing a portion of data for a distributed query. Shard indices start from 1. For non-distributed queries, it returns a constant value of 0. This function helps identify which shard is handling specific data segments.

```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;
```

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

### Sum Integers 1-100 using Recursive CTE

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

This example demonstrates a recursive Common Table Expression (CTE) in ClickHouse SQL to sum integers from 1 to 100. It utilizes the RECURSIVE modifier to achieve self-referencing query behavior. The CTE 'test_table' starts with 1 and iteratively adds 1 until the number is less than 100.

```sql
WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;
```

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

### Get Distinct JSON Paths and Types with ClickHouse SQL

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

This example shows how to obtain distinct JSON paths along with their corresponding data types from a JSON column in ClickHouse. It uses the `distinctJSONPathsAndTypes` function, which returns tuples of (path, type). The `SETTINGS date_time_input_format` clause is included for proper handling of date-time formats.

```sql
SELECT arrayJoin(distinctJSONPathsAndTypes(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
SETTINGS date_time_input_format = 'best_effort'
```

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

### Explain Query Plan Steps (ClickHouse SQL)

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

Dumps the query plan steps for a given SQL query. Various settings can be used to control the output, such as `header`, `description`, `indexes`, `projections`, `actions`, `json`, `input_headers`, `column_structure`, and `distributed`.

```sql
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
```

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

### Get Distinct JSON Paths with ClickHouse SQL

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

This example demonstrates how to retrieve all distinct JSON paths from a JSON column within a ClickHouse table. It utilizes the `distinctJSONPaths` function and `arrayJoin` to flatten the results. The function requires a JSON column as input and can be used with data stored in various formats, including S3.

```sql
SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
```

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

### Create Table and Insert Data for Moving Average Example in ClickHouse

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

This SQL code defines a simple `sensors` table with `String`, `DateTime`, and `Float` columns and populates it with sample time-series data. This setup is used to demonstrate the calculation of a moving average over a 10-day period, leveraging the `toDate()` function to group data by day.

```sql
CREATE TABLE sensors
(
    `metric` String,
    `ts` DateTime,
    `value` Float
)
ENGINE = Memory;

insert into sensors values('ambient_temp', '2020-01-01 00:00:00', 16),
                          ('ambient_temp', '2020-01-01 12:00:00', 16),
                          ('ambient_temp', '2020-01-02 11:00:00', 9),
                          ('ambient_temp', '2020-01-02 12:00:00', 9),
                          ('ambient_temp', '2020-02-01 10:00:00', 10),
                          ('ambient_temp', '2020-02-01 12:00:00', 10),
                          ('ambient_temp', '2020-02-10 12:00:00', 12),
                          ('ambient_temp', '2020-02-10 13:00:00', 12),
                          ('ambient_temp', '2020-02-20 12:00:01', 16),
                          ('ambient_temp', '2020-03-01 12:00:00', 16),
                          ('ambient_temp', '2020-03-01 12:00:00', 16),
                          ('ambient_temp', '2020-03-01 12:00:00', 16);
```

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

### ClickHouse SQL - Example: Create and Assign Settings Profile

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

This example demonstrates how to create a specific settings profile named 'max_memory_usage_profile' in ClickHouse SQL. It sets the 'max_memory_usage' parameter with a specific value and defines minimum and maximum constraints. The profile is then assigned to the user 'robin'.

```sql
CREATE SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000
TO robin

```

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

### Calculate Time Series Changes with Individual Samples

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

This example demonstrates how to use `timeSeriesChangesToGrid` with individual timestamp and value pairs. It first converts arrays of timestamps and values into separate rows and then applies the function to calculate changes on a defined time grid. The function takes start timestamp, end timestamp, grid step, and staleness window as parameters.

```sql
WITH
    -- NOTE: the gap between 130 and 190 is to show how values are filled for ts = 180 according to window parameter
    [110, 120, 130, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 5, 5, 8, 12, 13]::Array(Float32) AS values, -- array of values corresponding to timestamps above
    90 AS start_ts,       -- start of timestamp grid
    90 + 135 AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    45 AS window_seconds  -- "staleness" window
SELECT timeSeriesChangesToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)
FROM
(
    -- This subquery converts arrays of timestamps and values into rows of `timestamp`, `value`
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);

```

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

### Explain Query Plan Steps with JSON Output (ClickHouse SQL)

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

Explains query plan steps and outputs the result in JSON format. When `json=1`, step names include a unique identifier, and each node is a dictionary with 'Node Type' and 'Plans' keys. Additional keys like 'Description', 'Header', and 'Indexes' can be included based on settings.

```sql
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
```

```sql
EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;
```

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

### Get Dictionary Value as UInt64 (ClickHouse SQL)

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

Fetches a dictionary attribute value and casts it to UInt64, irrespective of the dictionary's setup. The function requires the dictionary name, attribute name, and an ID expression. It returns the corresponding attribute value or the configured null value if the key doesn't exist. ClickHouse will raise an error if the value cannot be parsed or does not conform to the attribute's data type.

```SQL
SELECT dictGetUInt64('all_types_dict', 'UInt64_value', 1)
```

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

### Example: Using the file table function to read CSV data

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

Demonstrates how to use the `file` table function to read data from a CSV file. This function treats the file content as a table, allowing standard SQL queries.

```bash
echo "1, 2, 3" > example.csv

```

```sql
./clickhouse client
:) SELECT * FROM file('example.csv')

```

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

### ClickHouse SQL: Examples of Lightweight UPDATE

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

Illustrative examples of the lightweight UPDATE statement in ClickHouse. The first example updates a single column 'Title' based on 'EventDate'. The second example increments 'hits' and updates 'time' based on the 'path'.

```sql
UPDATE hits SET Title = 'Updated Title' WHERE EventDate = today();

UPDATE wikistat SET hits = hits + 1, time = now() WHERE path = 'ClickHouse';
```

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

### Show CREATE Table, Dictionary, View, or Database Statement

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

Retrieves the CREATE query for a specified table, dictionary, view, or database. It returns a single string column containing the creation DDL. Note that for system tables, a 'fake' query is returned.

```sql
SHOW CREATE TABLE [db.]table;
SHOW CREATE DICTIONARY [db.]dictionary;
SHOW CREATE VIEW [db.]view;
SHOW CREATE DATABASE [db.]database;
```

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

### Resample Time Series Data with Individual Timestamp/Value Pairs

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

This example demonstrates how to use the timeSeriesResampleToGridWithStaleness function to resample time series data provided as individual timestamp and value pairs. It defines a time grid using start, end, and step parameters, and a staleness window to determine the recency of samples. The input data is converted from arrays to rows using arrayJoin and arrayZip.

```sql
WITH
    -- NOTE: the gap between 140 and 190 is to show how values are filled for ts = 150, 165, 180 according to staleness window parameter
    [110, 120, 130, 140, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 4, 5, 5, 8, 12, 13]::Array(Float32) AS values, -- array of values corresponding to timestamps above
    90 AS start_ts,       -- start of timestamp grid
    90 + 120 AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    30 AS window_seconds  -- "staleness" window
SELECT timeSeriesResampleToGridWithStaleness(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)
FROM
(
    -- This subquery converts arrays of timestamps and values into rows of `timestamp`, `value`
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);
```

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

### Create ClickHouse Dictionary for All Data Types

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

This SQL snippet demonstrates how to create a ClickHouse table named 'all_types_test' that includes columns for various ClickHouse data types. It is intended to be used as a source for creating a dictionary.

```sql
CREATE TABLE all_types_test (
    `id` UInt32,
    
    -- String type
    `String_value` String,
    
    -- Unsigned integer types
    `UInt8_value` UInt8,
    `UInt16_value` UInt16,
    `UInt32_value` UInt32,
    `UInt64_value` UInt64,
    
    -- Signed integer types
    `Int8_value` Int8,
    `Int16_value` Int16,
    `Int32_value` Int32,
    `Int64_value` Int64,
    
    -- Floating point types
    `Float32_value` Float32,
    `Float64_value` Float64,
    
    -- Date/time types
    `Date_value` Date,
    `DateTime_value` DateTime,
    
    -- Network types
    `IPv4_value` IPv4,
    `IPv6_value` IPv6,
    
    -- UUID type
    `UUID_value` UUID
) ENGINE = MergeTree() 
ORDER BY id;
```