SQL Compatibility

Hydrolix implements a significant part of the Clickhouse SQL query language, which is an ANSI SQL-compliant. Hydrolix uses the Clickhouse SQL parser natively before creating an execution plan, therefore most ClickHouse operations should work on Hydrolix except where noted.

This documentation is meant to augment or clarify the original Clickhouse Documentation and note any differences in the Hydrolix implementation.

While most functions are called out, not all are. It is safe to assume that functions that are not called out explicitly or by category are implemented and work as expected.

Clickhouse SQL syntax and functions as expected, except as noted below.

Differences and notes

Clickhouse	Hydrolix
Function `divide` and `/` operator returns `NaN, +inf, or -inf` when dividng by '0'	Returns null when dividing by `0`.
`uniqHLL12` implemented and not recommended for use.	`uniqHLL` is available, and also not recommended for use. Hydrolix architecture means that this type of estimation is not necessary. It is not included in Hydrolix documentation, but will run if called.
`uniqExact` works on multiple sets of values.	`uniqExact` works on one set of values. Use `uniqCombined` or `uniq` instead.
Block functions operate on Clickhouse blocks of data, including `blockNumber`, `blockSize`, `rowNumberInBlock` and `rowNumberInAllBlocks`	Blocks are an internal concept to Hydrolix. `block` functions should not be used.
Database and filesystem functions return values. The methods include `filesystemAvailable`,`filesystemCapacity`, `currentDatabase`	These methods will throw an exception in Hydrolix. They are not relevant with the Hydrolix architecture.

Only the SELECT statement supported

Hydrolix currently supports only SELECT through the Query API. Statements other than SELECT do not currently work in Hydrolix.

Unsupported SELECT statement options

The following options are not currently supported in Hydrolix:

Clickhouse Option
Distributed Subqueries

Untested Clickhouse Functions

Untested functions may work as expected, but are not currently part of the Hydrolix test suite.

Untested Function families

Functions in these Clickhouse function families are not currently being tested with Hydrolix.

Support of the JOIN function

Hydrolix supports JOIN between separate tables and projects.

For JOIN to work, the types need to be the same between the two fields you'll use in the function.

An alias is required for the tables you want to join.

As Hydrolix is using a timestamp to index data as the primary key, make sure both tables have a timestamp.

If your data don't have timestamp, you can modify your transform to add a timestamp field like the following:

{
    "name": "timestamp",
    "datatype": {
        "type": "epoch",
        "format": "ms",
        "virtual": true,
        "primary": true,
        "resolution": "ms",
        "script": "Date.now()"
    }
}

This will add the current timestamp at ingest time in a new field called "timestamp."

This is an example of JOIN query:

SELECT CountryId,
    Country,
    Count
FROM (
        SELECT toUInt32(clientCountryId) as CountryId,
            COUNT() as Count
        FROM sample.`performance_logs`
        WHERE timestamp between '2018-07-01 00:00:00' AND '2018-07-02 00:00:00'
        GROUP BY CountryId
    ) a ANY LEFT JOIN (
        SELECT id as CountryId,
            Country
        FROM sample.lookup
    ) b USING (CountryId)
ORDER BY Count DESC

curl --data-binary @- https://try3.hydrolix.live/query <<EOF
SELECT CountryId,
    Country,
    Count
FROM (
        SELECT toUInt32(clientCountryId) as CountryId,
            COUNT() as Count
        FROM sample.performance_logs
        WHERE timestamp between '2018-07-01 00:00:00' AND '2018-07-02 00:00:00'
        GROUP BY CountryId
    ) a ANY LEFT JOIN (
        SELECT id as CountryId,
            Country
        FROM sample.lookup
    ) b USING (CountryId)
ORDER BY Count DESC
EOF