Clickhouse SQL Compatibility

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`.
`uniqHLL` 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 and exception in Hydrolix. They are not relevant with the Hydrolix architecture.

Only SELECT statement supported

Hydrolix currenly 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 currently supported in Hydrolix

Clickhouse Option
ARRAY JOIN
FORMAT
INTO OUTFILE
JOIN(All)
LIMIT
LIMIT BY
PREWHERE
SAMPLE
UNION ALL
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.

Untested functions

These specific Clickhouse functions are not currently being tested with Hydrolix.

Clickhouse Function
dictGetUUID
finalizeAggregation
groupArrayInsert
joinGet
modelEvaluate
randConstant
retention
sequenceCount
sequenceMatch
sumMapFiltered
uniqUpTo
windowFunnel

Unsupported Clickhouse functions

The following Clickhouse functions are part of the Hydrolix test suite, but not currently supported.

Clickhouse Function
addressToLine
bitmapTransform
convertCharset
CRC32IEEE
CRC64
currentUser
FDQN
finalizeAggregation
groupArrayMovingAverage
groupArrayMovingSum
javaHashUTF16LE
joinGet
multiMatchAllIndices
multiFuzzyMatchAllIndices
quantileTDigest
runningAccumulate

Support of JOIN function

Hydrolix supports join between separates tables and project.

For JOIN to work the types needs to be the same between the 2 fields you'll use in the function.

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

As Hydrolix is using timestamp to index data as the primary key make sure both table 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: