ClickHouse 25.2 版本发布说明

本文字数：7668；估计阅读时间：20 分钟

SELECT *
FROM 
  numbers_mt(100_000) AS left_table 
    INNER JOIN
  numbers_mt(1_000_000_000) AS right_table 
    USING number
FORMAT Null
SETTINGS query_plan_join_swap_table = 0;
复制

0 rows in set. Elapsed: 12.275 sec. Processed 1.00 billion rows, 8.00 GB (81.48 million rows/s., 651.80 MB/s.)
Peak memory usage: 64.25 GiB.
复制

0 rows in set. Elapsed: 6.345 sec. Processed 1.00 billion rows, 8.00 GB (157.61 million rows/s., 1.26 GB/s.)
Peak memory usage: 64.07 GiB.
复制

wget --continue 'https://datasets.clickhouse.com/hits_compatible/hits.tsv.gz'
gzip -d hits.tsv.gz
复制

INSERT INTO hits 
SELECT * 
FROM file('hits.tsv');
复制

SELECT * 
FROM hits 
INTO OUTFILE 'hits.parquet'
复制

SELECT * 
FROM hits 
INTO OUTFILE 'hits_old.parquet'
SETTINGS output_format_parquet_write_bloom_filter = 0;
复制

du -h *.parquet
复制

 10G  hits.parquet
9.1G  hits_old.parquet
复制

SELECT count()
FROM file('hits_old.parquet')
WHERE UserID = 8511964386843448775;
复制

   ┌─count()─┐
1. │      89 │
   └─────────┘


1 row in set. Elapsed: 0.184 sec. Processed 60.28 million rows, 5.51 GB (327.86 million rows/s., 29.97 GB/s.)
Peak memory usage: 90.01 MiB.
复制

SELECT count()
FROM file('hits.parquet')
WHERE UserID = 8511964386843448775;
复制

   ┌─count()─┐
1. │      89 │
   └─────────┘


1 row in set. Elapsed: 0.123 sec. Processed 85.22 million rows, 8.30 GB (692.02 million rows/s., 67.40 GB/s.)
Peak memory usage: 87.37 MiB.
复制

select uniq(UserID)
FROM hits 
WHERE LocalEventTime > '2013-07-09'
AND EventDate > LocalEventTime;
复制

┌─uniq(UserID)─┐
│      2690891 │ -- 2.69 million
└──────────────┘
复制

┌─explain───────────────────────────────────┐
│ Expression ((Project names + Projection)) │
│   Aggregating                             │
│     Expression (Before GROUP BY)          │
│       Expression                          │
│         ReadFromMergeTree (default.hits)  │
│         Indexes:                          │
│           PrimaryKey                      │
│             Condition: true               │
│             Parts: 24/24                  │
│             Granules: 12463/12463         │
└───────────────────────────────────────────┘
复制

┌─explain─────────────────────────────────────────────┐
│ Expression ((Project names + Projection))           │
│   Aggregating                                       │
│     Expression (Before GROUP BY)                    │
│       Expression                                    │
│         ReadFromMergeTree (default.hits)            │
│         Indexes:                                    │
│           PrimaryKey                                │
│             Keys:                                   │
│               EventDate                             │
│             Condition: (EventDate in [15896, +Inf)) │
│             Parts: 3/3                              │
│             Granules: 7807/12377                    │
└─────────────────────────────────────────────────────┘
复制

storage_configuration:
  disks:
    backups:
      type: local
      path: /tmp/
backups:
  allowed_disk: backups
  allowed_path: /tmp/
复制

BACKUP DATABASE default 
TO File('backup1')
复制

   ┌─id───────────────────────────────────┬─status─────────┐
1. │ 087a18a7-9155-4f72-850d-9eaa2034ca07 │ BACKUP_CREATED │
   └──────────────────────────────────────┴────────────────┘
复制

CREATE DATABASE backup 
ENGINE = Backup('default', File('backup1'));
复制

SHOW TABLES FROM backup;
复制

SELECT count()
FROM backup.hits
WHERE UserID = 8511964386843448775;
复制

   ┌─count()─┐
1. │      89 │
   └─────────┘


1 row in set. Elapsed: 0.034 sec. Processed 16.63 million rows, 133.00 MB (488.05 million rows/s., 3.90 GB/s.)
Peak memory usage: 475.41 KiB.
复制

CREATE TABLE t 
ENGINE = DeltaLake(
  s3, 
  filename = 'xyz/', 
  url = 'http://minio1:9001/test/'
)
SETTINGS allow_experimental_delta_kernel_rs = 1;
复制

curl http://localhost:8123/ -d "SELECT CounterID, count() FROM hits GROUP BY ALL WITH TOTALS ORDER BY count() DESC LIMIT 10 FORMAT JSONCompactEachRowWithProgress"
复制

{"progress":{"read_rows":"99997497","read_bytes":"399989988","total_rows_to_read":"99997497","elapsed_ns":"30403000"}}
{"meta":[{"name":"CounterID","type":"Int32"},{"name":"count()","type":"UInt64"}]}
{"row":[3922, "8527070"]}
{"row":[199550, "7115413"]}
{"row":[105857, "5783214"]}
{"row":[46429, "4556155"]}
{"row":[225510, "4226610"]}
{"row":[122612, "3574170"]}
{"row":[244045, "3369562"]}
{"row":[233773, "2941425"]}
{"row":[178321, "2712905"]}
{"row":[245438, "2510103"]}
{"totals":[0, "99997497"]}
{"rows_before_limit_at_least":6506}
复制