Skip to main content

High-performance Dataset and ClickHouse Syntax Notes

Window Functions

BI versions earlier than 5.9 do not support window functions by default. To use them, upgrade the BI system to version 5.9 or later. In addition, high-performance datasets require ClickHouse version 22 or later.

Case Sensitivity

a. Function names: Some ClickHouse functions are case-sensitive. For example, writing toDate() as todate() causes an error. Some functions are not case-sensitive. For example, both length() and Length() can return the string length. Note that String must be capitalized in the cast function, such as cast ([Field] as String), where S must be uppercase. Pay attention to case in actual use.

b. Table names and field names: ClickHouse is also case-sensitive for field names. For example, if DDL defines a table named orders with a field named Itemnum, you must write select Itemnum from orders to query this field. Writing the field as ItemNum or the table as Orders causes an error.

Function Differences

a. Common function names and syntax in ClickHouse may differ from regular databases. For example, to convert a string to a date in ClickHouse, use toDate(), not to_date() or str_to_date(). The official documentation does not describe toDate() in detail. Based on testing, this function appears to support only numbers, such as toDate(11111), or strings in formats such as yyyy-MM-dd, yyyy/MM/dd, yyyy MM dd, and corresponding formats with hours, minutes, and seconds. Other date string formats may cause errors. To parse date strings in other formats, such as yyyyMMdd, preprocess the string first or use the parseDateTimeBestEffort() function. This function automatically supports various date string formats. For details, see the official documentation.

b. Many type conversion functions in ClickHouse have OrNull and OrZero suffixes. These suffixes handle data or NULL values that do not fall within the processing range of the corresponding function. For example, toInt8('12.34') fails and reports an error because 12.34 is not an integer string and cannot be parsed by toInt8. If you use toInt8OrZero('12.34'), it returns 0. If you use toInt8OrNull('12.34'), it returns null.

Data Type Differences

a. Basic data types in ClickHouse are similar to those in other databases, but some type names differ. For example, integer fields in other databases may be int, smallint, or bigint, while ClickHouse uses Int8, Int16, Int32, and similar types.

b. ClickHouse has a unique type wrapper called Nullable:

Strictly speaking, Nullable is less a standalone data type than a special marker or restriction on a specific data type. A field with Nullable() allows missing values to be stored as NULL by default. For example, a field of type Nullable(Int8) can store Int8 values, and rows without a specific value are stored as NULL.

Nullable() is important because ClickHouse enforces data type restrictions strictly. In the scenario above, if the field type is Int8 and the incoming value contains NULL, an error occurs.

Single Quotes and Double Quotes

Spark commonly uses English double quotes to quote strings, and single quotes are also compatible. ClickHouse strictly requires English single quotes (''); otherwise, it reports an error.

Using the dateDiff Function

In Spark, the datediff function requires only two parameters: [End Date Field] and [Start Date Field]. For example, datediff([Termination Date], [Hire Date]) returns the number of days between "Termination Date" and "Hire Date" in the same row. In ClickHouse, dateDiff requires three or four parameters.

dateDiff('unit', startdate, enddate, [timezone])
/*--
Supported time units: second, minute, hour, day, week, month, quarter, year.
[timezone] is optional. Fill it in based on the actual scenario.
--*/ 
dateDiff( 'day',[Start Date],[End Date])
/*-------Calculate the difference in days between two dates----------*/

date_sub Function and subtractDays Function

The date_sub function uses Spark syntax. date_sub([Sales Date],1) returns the date one day before the sales date.
The subtractDays function uses ClickHouse syntax, and the writing style is the same: subtractDays([Sales Date],1), which returns the date one day before the sales date.

ClickHouse requires the year to be between 1970 and 2139. Otherwise, it reports an error.

ClickHouse exception , code :321, host :xxx.xx.x.xx, port :xxxx; Code :xxx, e .displayText0= DB : Exception : Input value 2932783 of a column "UInt16” is greater than max allowed Date value , which is 49710( version 20.4.5.36( official build ))**