Author: Alexey Milovidov, 2017-12-13.
Custom Partitioning Key
CREATE TABLE ... ENGINE = MergeTree
ORDER BY CounterID, Date
PARTITION BY toStartOfWeek(Date)
SETTINGS index_granularity = 8192
Distributed DDL Queries
CREATE TABLE ... ON CLUSTER cluster
* doesn't work with cross-replication;
* for ALTER of Replicated tables, requires enabling an option;
Geospatial Functions:
— pointInPolygon;
— pointInEllipses;
— greatCircleDistance;
SELECT pointInPolygon((lat, lon),
[(6, 0), (8, 4), (5, 8), (0, 2), ...])
CatBoost Model Integration
SELECT modelEvaluate('purchase', f1, ... fn)
Working with Date and Time Intervals:
SELECT date + INTERVAL 1 MONTH
— support for time zones with non-integer UTC offset;
— extending the valid range to year 2105.
Array Functions:
— arrayConcat, arraySlice;
— arrayPushFront, arrayPushBack, arrayPopFront, arrayPopBack;
— arrayReduce, arraySort, arrayReverseSort, arrayStringConcat;
Dictionary Improvements
— dictionary introspection:
CREATE DATABASE dictionaries ENGINE = Dictionary
— invalidate_query for dictionary updates;
— SYSTEM RELOAD DICTIONARY;
Sessions in HTTP Interface:
curl 'http://host:port/?session_id=123&session_timeout=60' \
-d 'CREATE TEMPORARY TABLE t'
Native SSL support for HTTP and TCP server.
Sending progress in HTTP headers.
Improvements for SQL Compatibility:
— limited support for table aliases and compound names;
— ANSI-style name quoting;
— data type aliases;
— INSERT SELECT by positions, not by names;
— type casting in INSERT SELECT;
— NULLS FIRST/LAST for ORDER BY.
ODBC Driver:
+ BI tools started working;
− still many holes in implementation;
NULL Support.
Clear Timezone Handling.
What will the query return?
SELECT toStartOfHour(
toDateTime('2017-12-12 01:02:03'),
'Europe/London') AS t
Full Multi-dimensional Array Support.
CREATE TABLE t
(
x Array(Array(String)),
z Nested(
x Array(String),
y Nested(...))
)
ENGINE = MergeTree ORDER BY x
Copying Data Between Clusters.
Connecting External Tables from MySQL, ODBC.
Table functions mysql, odbc.
SELECT ...
FROM mysql(
'host:port',
'db', 'table',
'user', 'password')
O_DIRECT When Merging Large Parts.
Support for Computed DEFAULT Expressions for Semi-structured Formats JSONEachRow, TSKV.
Support for Protobuf and Parquet Formats for Input-Output.
Using Index for Queries with IN (subquery).
Moving Conditions from WHERE Inside Subqueries.
Type Casting for UNION ALL.
Support for Subqueries in VIEW.
Support for Short-circuit Expression Evaluation.
SELECT number != 0
? intDiv(10, number)
: 0
FROM system.numbers LIMIT 10
WITH ROLLUP and WITH CUBE Modifiers for GROUP BY.
Using Primary Key for ORDER BY, GROUP BY.
Ability to Use Custom Compression Algorithms
for Individual Columns.
Index Structures for Skipping Data Blocks in Table.
Ability to Store Data on Different Partitions Within a Server.
Ability to Create Dictionaries via DDL Queries.
Don't Return Empty Set When Aggregating over Empty Set.
LDAP Integration.
UPDATE/DELETE Support.
Resource Pools for Query Execution.
Proper Syntax for JOIN.
Cloud Tables.
Website: https://clickhouse.com/
Google groups: https://groups.google.com/forum/#!forum/clickhouse
Mailing list: [email protected]
Telegram chat: https://telegram.me/clickhouse_ru (already 1238 participants)
and https://telegram.me/clickhouse_en
GitHub: https://github.com/ClickHouse/ClickHouse/
Twitter: https://twitter.com/ClickHouseDB