Author: Alexey Milovidov, 2018-04-25.
Custom partition key
CREATE TABLE ... ENGINE = MergeTree
ORDER BY CounterID, Date
PARTITION BY toStartOfWeek(Date)
SETTINGS index_granularity = 8192
Geospatial functions:
— pointInPolygon;
— pointInEllipses;
— greatCircleDistance;
SELECT pointInPolygon((lat, lon),
[(6, 0), (8, 4), (5, 8), (0, 2), ...])
Integration with CatBoost machine learning models
SELECT modelEvaluate('purchase', f1, ... fn)
Working with date and time intervals:
SELECT date + INTERVAL 1 MONTH
— support for time zones with fractional offset from UTC;
— extended supported time range up to year 2105.
— timeDiff, add/subtractInterval functions.
— EXTRACT(... FROM ...) operator.
Proper support for timezones
+ toTimeZone function
Q: What is the result of this query?
SELECT toStartOfHour(
toDateTime('2017-12-12 01:02:03'),
'Asia/Shanghai') AS t
Distributed DDL queries
CREATE TABLE ... ON CLUSTER cluster
* doesn't work with cross-over replication;
* doesn't work properly with NAT;
Support for storing multidimensional arrays in tables.
CREATE TABLE t
(
x Array(Array(String)),
z Nested(x Array(String), ...)
)
ENGINE = MergeTree ORDER BY x
Extended array functions:
— arrayConcat, arraySlice;
— arrayPushFront, arrayPushBack, arrayPopFront, arrayPopBack;
— arrayReduce, arraySort, arrayReverseSort, arrayStringConcat;
— arrayHasAll, arrayHasAny, arrayIntersect, arrayCumSum;
Improvements in dictionaries
— introspection of dictionaries:
CREATE DATABASE dictionaries ENGINE = Dictionary
— invalidate_query for updating dictionaries;
— update_field for partial dictionary reload;
— SYSTEM RELOAD DICTIONARY;
Sessions in HTTP interface:
curl 'http://host:port/?session_id=123&session_timeout=60' \
-d 'CREATE TEMPORARY TABLE t'
Native support for SSL in HTTP and TCP servers.
Node to node traffic encryption.
Sending progress info in HTTP headers.
Distributed cluster copier
— for copying between different clusters
or resharding within single cluster.
Using index for IN (subquery).
SELECT ...
WHERE key IN
(
SELECT ...
)
Using index for IN with tuples.
SELECT ...
WHERE (key1, key2) IN
(
SELECT ...
)
Querying foreign databases: MySQL, ODBC.
Through table functions mysql, odbc:
SELECT ...
FROM mysql(
'host:port',
'db', 'table',
'user', 'password')
Or through CREATE TABLE ... ENGINE = MySQL(...)
Improvements in SQL compatibility:
— Don't return empty resultset in case of aggregating by empty data.
— limited support for table aliases and qualified names;
— name quoting in ANSI style;
— type aliases for compatibility;
— INSERT SELECT match columns by positions;
— type conversions in INSERT SELECT;
— type conversions in UNION ALL;
— NULLS FIRST/LAST for ORDER BY.
ODBC driver:
+ Tableau basically works;
− but we still have many obstacles;
Support for NULLs.
Remains:
— NULLs in external dictionaries;
— NULLs in "transform" function;
O_DIRECT for large background merges.
Support for complex DEFAULT expressions for semistructured formats JSONEachRow, TSKV.
Support for Protobuf and Parquet formats for I/O.
Predicate push down from WHERE into subqueries.
Support for subqueries in VIEWs.
Support for short-circuit evaluation of expressions.
SELECT number != 0
? intDiv(10, number)
: 0
FROM system.numbers LIMIT 10
WITH ROLLUP and WITH CUBE modifiers for GROUP BY.
Taking advantage of primary key for ORDER BY and GROUP BY.
Allow to use custom compression algorithms
for each column.
Secondary index data structures for data skipping.
Allow to store data in different block devices on single server.
Allow to create external dictionaries through DDL query.
LDAP integration for managing user access rights.
Support for UPDATE/DELETE.
Resource pools; resource sharing while executing multiple concurrent queries.
Proper (SQL compatible) JOIN syntax.
"Cloud" tables.
Web site: https://clickhouse.com/
Google groups: https://groups.google.com/forum/#!forum/clickhouse
Maillist: [email protected]
Telegram chat: https://telegram.me/clickhouse_en and https://telegram.me/clickhouse_ru (now with over 1500 members)
GitHub: https://github.com/ClickHouse/ClickHouse/
Twitter: https://twitter.com/ClickHouseDB
+ meetups. Moscow, Saint-Petersburg, Novosibirsk, Ekaterinburg, Minsk, Nizhny Novgorod, Berlin, Palo Alto, Beijing.