ClickHouse リリース 26.8

1ALTER TABLE uk_price_paid
2ATTACH PARTITION ID 'all'
3FROM uk_price_paid;

1SELECT count() FROM uk_price_paid;

1┌───count()─┐
2│ 243619704 │
3└───────────┘

1INSERT INTO FUNCTION url('http://localhost:8080/uploads/london-sales.parquet', 'Parquet')
2SELECT * FROM uk_price_paid
3SETTINGS run_query_in_background = 1;

1Query id: 59eabb9f-33f4-412b-b2f3-b090fe7b4bbf
2
3Ok.
4
50 rows in set. Elapsed: 0.001 sec.

1SELECT query_id, query,
2      round(elapsed, 2) AS elapsed_seconds,
3      read_rows, written_rows
4FROM system.processes
5WHERE query NOT ILIKE '%system.processes%'
6ORDER BY elapsed DESC;

1Row 1:
2──────
3query_id:        59eabb9f-33f4-412b-b2f3-b090fe7b4bbf
4query:           INSERT INTO FUNCTION url('http://localhost:8080/uploads/uk.parquet', 'Parquet')
5SELECT * FROM uk_price_paid
6SETTINGS run_query_in_background = 1;
7elapsed_seconds: 8.52
8read_rows:       179243204 -- 179.24 million
9written_rows:    179243204 -- 179.24 million

1SELECT event_time, type, query_duration_ms,
2      read_rows, result_rows
3FROM system.query_log
4WHERE query_id = '59eabb9f-33f4-412b-b2f3-b090fe7b4bbf'
5ORDER BY event_time;

1┌──────────event_time─┬─type────────┬─query_duration_ms─┬─read_rows─┬─result_rows─┐
2│ 2026-09-03 12:32:05 │ QueryStart  │                 0 │         0 │           0 │
3│ 2026-09-03 12:32:17 │ QueryFinish │             11798 │ 243619704 │   243619704 │
4└─────────────────────┴─────────────┴───────────────────┴───────────┴─────────────┘

1SELECT
2      'Baker Street' ~ 'street$' AS matchesCaseSensitively,
3      'Baker Street' ~* 'street$' AS matchesCaseInsensitively,
4      'Baker Street' !~ 'street$' AS doesNotMatchCaseSensitively,
5      'Baker Street' !~* 'street$' AS doesNotMatchCaseInsensitively
6FORMAT Vertical;

1Row 1:
2──────
3matchesCaseSensitively:        0
4matchesCaseInsensitively:      1
5doesNotMatchCaseSensitively:   1
6doesNotMatchCaseInsensitively: 0

1WITH arrayMap(x -> rand(x), range(1, 10)) AS random_values
2SELECT random_values[[1, 3, 5]];

1┌─arrayElement(ran⋯ues, [1, 3, 5])─┐
2│ [344408953,1157293782,407846805] │
3└──────────────────────────────────┘

1WITH monthlyPrices AS
2(
3    SELECT district, toStartOfMonth(date) AS month,
4           round(median(price)) AS medianPrice
5    FROM uk_price_paid
6    WHERE town = 'LONDON'
7      AND district IN ('CAMDEN', 'CITY OF WESTMINSTER', 'KENSINGTON AND CHELSEA')
8      AND date >= '2024-01-01' AND date < '2025-01-01'
9    GROUP BY district, month
10)
11SELECT district,
12       arraySort(groupArray((month, medianPrice)))[[1, 4, 7, 10]] AS quarterlyPrices
13FROM monthlyPrices
14GROUP BY district
15HAVING count() = 12
16ORDER BY district;

1Row 1:
2──────
3district:        CAMDEN
4quarterlyPrices: [('2024-01-01',760000),('2024-04-01',731000),('2024-07-01',762500),('2024-10-01',805000)]
5
6Row 2:
7──────
8district:        CITY OF WESTMINSTER
9quarterlyPrices: [('2024-01-01',1215000),('2024-04-01',1037500),('2024-07-01',935000),('2024-10-01',875000)]
10
11Row 3:
12──────
13district:        KENSINGTON AND CHELSEA
14quarterlyPrices: [('2024-01-01',1205000),('2024-04-01',1187500),('2024-07-01',1100000),('2024-10-01',1200000)]

1SELECT parseQueryToJSON($sql$
2SELECT count() FROM uk_price_paid
3$sql$)::JSON
4FORMAT Vertical;

1{
2  "type": "SelectWithUnionQuery",
3  "union_mode": "UNION_DEFAULT",
4  "list_of_selects": {
5    "type": "ExpressionList",
6    "children": [
7      {
8        "type": "SelectQuery",
9        "select": {
10          "type": "ExpressionList",
11          "children": [
12            {"type": "Function", "name": "count",
13             "arguments": {"type": "ExpressionList"}}
14          ]
15        },
16        "tables": {
17          "type": "TablesInSelectQuery",
18          "children": [
19            {"type": "TablesInSelectQueryElement",
20             "table_expression": {
21               "type": "TableExpression",
22               "database_and_table_name": {
23                 "type": "TableIdentifier", "name": "uk_price_paid"}}}
24          ]
25        }
26      }
27    ]
28  }
29}

1SELECT formatQueryFromJSON(parseQueryToJSON($sql$
2SELECT count() FROM uk_price_paid
3$sql$));

1┌─formatQueryFromJ⋯_price_paid\n'))─┐
2│ SELECT count() FROM uk_price_paid │
3└───────────────────────────────────┘

1SET enable_json_ast_dialect = 1, dialect = 'clickhouse_json';

1┌───count()─┐
2│ 243619704 │
3└───────────┘

1CREATE USER mark
2IDENTIFIED WITH no_password
3VALID UNTIL '2026-10-04';

1CREATE USER mark
2IDENTIFIED WITH no_password
3VALID FOR INTERVAL 2 WEEKS;

1SHOW CREATE USER mark
2FORMAT LineAsString;

1CREATE USER mark 
2IDENTIFIED WITH no_password 
3VALID UNTIL '2026-09-18 10:06:29'

1ALTER USER mark VALID FOR INTERVAL 1 WEEK;

1CREATE USER mark 
2IDENTIFIED WITH no_password 
3VALID UNTIL '2026-09-11 10:08:34'

1CREATE TABLE src (id UInt64) 
2ORDER BY id;
3INSERT INTO src 
4SELECT number FROM numbers(100000);

1CREATE TABLE dst (id UInt64) 
2ORDER BY id;

1./clickhouse client -q "
2  INSERT INTO src SELECT number + 100000 FROM numbers(50)
3  WHERE sleepEachRow(0.05) = 0;" &
4
5sleep 0.5
6
7./clickhouse client -m -q "
8  SET materialized_views_populate_atomically = 0;
9  CREATE MATERIALIZED VIEW mv TO dst
10  POPULATE AS SELECT id FROM src;"
11
12wait

1SELECT (SELECT count() FROM src) AS sourceRows,
2       (SELECT count() FROM dst) AS dstRows,
3       (SELECT uniqExact(id) FROM dst) AS dstDistinct,
4       sourceRows - dstRows AS lost,
5       dstRows - dstDistinct AS duplicated
6FORMAT PrettyCompact;

1┌─sourceRows─┬─dstRows─┬─dstDistinct─┬─lost─┬─duplicated─┐
2│     100050 │  100000 │      100000 │   50 │          0 │
3└────────────┴─────────┴─────────────┴──────┴────────────┘

1./clickhouse client -q "
2  INSERT INTO src SELECT number + 100000 FROM numbers(50)
3  WHERE sleepEachRow(0.05) = 0;" &
4
5sleep 0.5
6
7./clickhouse client -m -q "
8  SET materialized_views_populate_atomically = 1;
9  CREATE MATERIALIZED VIEW mv TO dst
10  POPULATE AS SELECT id FROM src;"
11
12wait

1┌─sourceRows─┬─dstRows─┬─dstDistinct─┬─lost─┬─duplicated─┐
2│     100050 │  100050 │      100050 │    0 │          0 │
3└────────────┴─────────┴─────────────┴──────┴────────────┘

1sha256sum /var/lib/clickhouse/unidic-cwj-202512.zip

1d94216b589d15d05c408ed59abc5259086703ebbac14e225b5314e4cd106c4db

1<clickhouse>
2  <tokenizer>
3    <japanese>
4      <dictionary_location>file:///var/lib/clickhouse/unidic-cwj-202512.zip</dictionary_location>
5      <dictionary_sha>d94216b589d15d05c408ed59abc5259086703ebbac14e225b5314e4cd106c4db</dictionary_sha>
6    </japanese>
7  </tokenizer>
8</clickhouse>

1CREATE TABLE reviews
2(
3    id UInt64,
4    text String,
5    INDEX text_idx text TYPE text(tokenizer = 'japanese')
6)
7ENGINE = MergeTree
8ORDER BY id;
9
10INSERT INTO reviews VALUES
11(1, '渋谷の新しい寿司屋で美味しいうにを食べた'),      -- "ate" (食べた)
12(2, '大阪のラーメンは最高だった、また食べたい'),      -- "want to eat" (食べたい)
13(3, '京都で抹茶アイスを食べながら散歩した'),          -- "while eating" (食べながら)
14(4, '新幹線に乗って富士山を見に行った');              -- no mention of eating
15
16SELECT id, text
17FROM reviews
18WHERE hasAnyTokens(text, ['食べ'], 'japanese')
19ORDER BY id;

1CREATE TABLE table_coarse
2(
3    key UInt64,
4    str String,
5    INDEX text_idx str TYPE text(tokenizer = chinese) -- default: coarse_grained
6)
7ENGINE = MergeTree ORDER BY key;
8
9CREATE TABLE table_fine
10(
11    key UInt64,
12    str String,
13    INDEX text_idx str TYPE text(tokenizer = chinese('fine_grained'))
14)
15ENGINE = MergeTree ORDER BY key;
16
17INSERT INTO table_coarse VALUES
18    (1, '他考上了大学，很开心'), -- standalone 大学
19    (2, '北京邮电大学的通信工程专业很强'), -- compound 北京邮电大学
20    (3, '我毕业于北京大学计算机系'), -- compound 北京大学
21    (4, '今天天气不错，适合散步'); -- no mention
22
23INSERT INTO table_fine SELECT * FROM table_coarse;

1SELECT
2    key,
3    str
4FROM table_coarse
5WHERE hasAllTokens(str, '大学')
6ORDER BY key ASC;

1SELECT
2    key,
3    str
4FROM table_fine
5WHERE hasAllTokens(str, '大学')
6ORDER BY key ASC;

1SELECT tokens('บ้าน', 'asciiCJK');

1┌─tokens('บ้าน', 'asciiCJK')─┐
2│ ['บ','้','า','น']          │
3└───────────────────────────┘

1-- "ม้าอยู่บนภูเขา" = "The horse is on the mountain" (no usage of "house")
2SELECT tokens('ม้าอยู่บนภูเขา', 'asciiCJK');

1┌─tokens('ม้าอยู่บนภูเขา', 'asciiCJK')──────────────────────┐
2│ ['ม','้','า','อ','ย','ู','่','บ','น','ภ','ู','เ','ข','า'] │
3└───────────────────────────────────────────────────────┘

1SELECT hasAllTokens('ม้าอยู่บนภูเขา', 'บ้าน', 'asciiCJK');

1┌─hasAllTokens⋯'asciiCJK')─┐
2│                        1 │
3└──────────────────────────┘
4-- false positive

1SELECT tokens('ม้าอยู่บนภูเขา', 'icu', 'th');

1┌─tokens('ม้าอยู่⋯icu', 'th')─┐
2│ ['ม้า','อยู่','บน','ภูเขา']  │
3└──────────────────────────┘
4-- "horse", "is/located", "on", "mountain"

1SELECT hasAllTokens('ม้าอยู่บนภูเขา', 'บ้าน', 'icu(''th'')');

1┌─hasAllTokens⋯u(\'th\')')─┐
2│                        0 │
3└──────────────────────────┘

1SELECT
2    tokens('C++', 'splitByNonAlpha'),
3    tokens('C#', 'splitByNonAlpha'),
4    tokens('F#', 'splitByNonAlpha')
5FORMAT Vertical;

1tokens('C++'⋯yNonAlpha'): ['C']
2tokens('C#',⋯yNonAlpha'): ['C']
3tokens('F#',⋯yNonAlpha'): ['F']

1SELECT hasAllTokens('I am an expert in C++', 'C#', 'splitByNonAlpha');

1┌─hasAllTokens⋯yNonAlpha')─┐
2│                        1 │
3└──────────────────────────┘

1CREATE TABLE hold_my_beer
2(
3id UInt64,
4description String,
5INDEX idx description TYPE text(tokenizer = splitByRegexp('[^\p{L}\p{N}#+]+'))
6)
7ENGINE = MergeTree ORDER BY id;
8
9INSERT INTO hold_my_beer VALUES
10    (1, 'I am an expert in C++'),
11    (2, 'I am an expert in C#'),
12    (3, 'I use Arch');
13
14SELECT id, description FROM hold_my_beer WHERE hasAllTokens(description, 'C++') OR hasAllTokens(description, 'C#');

1┌─id─┬─description───────────┐
2│  1 │ I am an expert in C++ │
3│  2 │ I am an expert in C#  │
4└────┴───────────────────────┘

1SELECT count(), avg(star_rating) FROM url('s3://datasets-documentation/amazon_reviews/amazon_reviews_2015.snappy.parquet');

1CREATE DATABASE datasets
2ENGINE = URL('https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/');
3USE datasets;
4
5SELECT count() FROM 'amazon_reviews_2015.snappy.parquet';

1┌──count()─┐
2│ 41905631 │ -- 41.91 million
3└──────────┘

1SELECT count() 
2FROM 'amazon_reviews_2014.snappy.parquet';

1┌──count()─┐
2│ 44127569 │ -- 44.13 million
3└──────────┘

1SELECT * FROM 'hits.tsv';
2SELECT * FROM 'https://example.com/hits.tsv';
3SELECT * FROM 's3://mybucket/hits.tsv';
4SELECT * FROM table;

1<clickhouse>
2    <named_collections>
3        <bigquery_credentials>
4            <project>bigquery-clickhouse</project>
5            <dataset>stackoverflow</dataset>
6            <table>badges</table>
7            <service_account_key><![CDATA[your_service_key]]></service_account_key>
8        </bigquery_credentials>
9    </named_collections>
10</clickhouse>

1SELECT *
2FROM system.named_collections;

1┌─name────────┬─collection───────────────────────────────────────────────────────────────────┬─source─┬─create_query─┐
2│ my_bigquery │ {'dataset':'[HIDDEN]','project':'[HIDDEN]','service_account_key':'[HIDDEN]'} │ CONFIG │              │
3└─────────────┴──────────────────────────────────────────────────────────────────────────────┴────────┴──────────────┘

1DESCRIBE TABLE bigquery(bigquery_credentials);

1Query id: dd1e8285-b286-4892-8770-eeed49ef6919
2
3┌─name─────┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
4│ Id       │ Nullable(Int64)                │              │                    │         │                  │                │
5│ UserId   │ Nullable(Int64)                │              │                    │         │                  │                │
6│ Name     │ Nullable(String)               │              │                    │         │                  │                │
7│ Date     │ Nullable(DateTime64(6, 'UTC')) │              │                    │         │                  │                │
8│ Class    │ Nullable(Int64)                │              │                    │         │                  │                │
9│ TagBased │ Nullable(Int64)                │              │                    │         │                  │                │
10└──────────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
11
126 rows in set. Elapsed: 0.602 sec.

1CREATE TABLE bq_badges
2(
3    Id       Int64,
4    UserId   Int64,
5    Name     LowCardinality(String),
6    Date     DateTime64(6, 'UTC'),
7    Class    Int64,
8    TagBased Int64
9)
10ENGINE = MergeTree
11ORDER BY (Id);
12
13INSERT INTO bq_badges SELECT * FROM bigquery(bigquery_credentials) LIMIT 5; -- I don't want to max out my credit card
14
15SELECT * FROM bq_badges;

1┌───────Id─┬───UserId─┬─Name────────┬───────────────────────Date─┬─Class─┬─TagBased─┐
2│  3336768 │  1033808 │ Copy Editor │ 2012-05-03 22:19:43.187000 │     1 │        0 │
3│  6687355 │    19299 │ .net        │ 2013-06-15 03:03:52.690000 │     1 │        1 │
4│ 12885730 │  3892259 │ Copy Editor │ 2015-01-31 14:43:59.373000 │     1 │        0 │
5│ 39830489 │ 10659482 │ Copy Editor │ 2020-12-09 11:16:11.410000 │     1 │        0 │
6│ 40413130 │  1386551 │ Copy Editor │ 2021-01-29 18:00:54.783000 │     1 │        0 │
7└──────────┴──────────┴─────────────┴────────────────────────────┴───────┴──────────┘
8
95 rows in set. Elapsed: 0.005 sec.

1SET allow_database_iceberg = 1;
2SET allow_insert_into_iceberg = 1;

1CREATE DATABASE tables
2ENGINE = DataLakeCatalog(
3    'https://s3tables.us-east-1.amazonaws.com/iceberg'
4)
5SETTINGS
6    catalog_type = 's3tables',
7    region = 'us-east-1',
8    warehouse = 'arn:aws:s3tables:us-east-1:123456789012:bucket/analytics';

1SHOW TABLES FROM tables;
2
3SELECT *
4FROM tables.`ns.events`
5LIMIT 10;

1-- Replace … with values matching your table's columns.
2INSERT INTO tables.`ns.events` VALUES (…);

1SET allow_database_iceberg = 1;
2SET allow_insert_into_iceberg = 1;
3
4CREATE DATABASE horizon
5ENGINE = DataLakeCatalog(
6    'https://<org>-<account>.snowflakecomputing.com/polaris/api/catalog'
7)
8SETTINGS
9    catalog_type = 'horizon',
10    warehouse = 'ICEBERG_DB',
11    catalog_credential = '<PAT>',
12    auth_scope = 'session:role:<ROLE>',
13    vended_credentials = 1;

1SHOW TABLES FROM horizon;
2
3SELECT *
4FROM horizon.`schema.trades`
5LIMIT 10;

1-- Replace … with values matching your table's columns.
2INSERT INTO horizon.`schema.trades` VALUES (…);

1SELECT referenced_data_file, deleted_rows
2FROM url(
3    'https://raw.githubusercontent.com/ClickHouse/ClickHouse/693dee22dda0d7ff23343c326754ccfc24f3237f/tests/queries/0_stateless/data_puffin/file_properties_ok.puffin',
4    'Puffin'
5);

1┌─referenced_data_file───────────┬─deleted_rows─┐
2│ /data/table/part-00000.parquet │ [2,5]        │
3└───────────────────────────────┴──────────────┘

1SELECT blob_type, properties
2FROM url(
3    'https://raw.githubusercontent.com/ClickHouse/ClickHouse/693dee22dda0d7ff23343c326754ccfc24f3237f/tests/queries/0_stateless/data_puffin/file_properties_ok.puffin',
4    'PuffinMetadata'
5);

1SELECT URL, Title
2FROM s3(
3    'https://clickhouse-public-datasets.s3.amazonaws.com/hits_compatible/hits.parquet',
4    NOSIGN
5)
6ORDER BY EventTime DESC, WatchID DESC
7LIMIT 10
8SETTINGS query_plan_optimize_lazy_materialization_for_object_storage = 1;

1SELECT count()
2FROM s3(
3    'https://clickhouse-public-datasets.s3.amazonaws.com/hits_compatible/hits.parquet',
4    NOSIGN
5)
6WHERE MobilePhoneModel = 'GT-C3262';

1┌─count()─┐
2│     200 │
3└─────────┘