HogQL

HogQL is our take on SQL (Structured Query Language), a language used to manage and access data. HogQL is effectively a wrapper around ClickHouse SQL, with a few tweaks, such as simplified event and person property access.

Is HogQL real SQL? Yes, it's a translation layer over ClickHouse SQL. You can use most ClickHouse features in HogQL, including JOINs and subqueries. To learn more about ClickHouse, and how it differs from traditional SQL databases, read PostHog's ClickHouse Manual.

HogQL is currently in public beta. This means it's not yet a perfect experience, and the language itself may still change. Follow along with the development here.

HogQL expressions

HogQL expressions can be used inside insights and dashboards as filters and/or breakdown values. When filtering or breaking down, select the "HogQL" tab, and add your expression.

SQL insights

SQL insights enables you to create and edit insights using full SQL queries instead of the UI. Clicking the {} button in the top right corner of an insight shows you the SQL schema used to generate the insight which you can edit.

Event explorer

The event explorer replaces the "Live Events" tab in PostHog. It provides more functionality including date filtering, event counts, HogQL queries, aggregations and column configuration.

Database

To display all the tables you can query, check out the new "Database" tab under "Data Management".

We're working hard on making all these public. Follow along in the relevant Github issue, or send us a support ticket if you want to get access.

API access

Will there be API pricing? The HogQL API is free to use while it's in the public beta, and we work out the details. After we launch for real, we plan to charge a competitive rate for heavy usage. Stay tuned.

To access HogQL via the PostHog API, make a POST request to /api/project/:id/query with the following JSON payload:

{"query": {"kind": "HogQLQuery", "query": "select * from events limit 100"}}

The response is in the format:

export interface HogQLQueryResponse {
    /** The input query */
    query?: string
    /** An array of result arrays */
    results?: any[][]
    /** Returned column types */
    types?: string[]
    /** Returned column names/aliases */
    columns?: string[]
    /** Generated HogQL query with expressions inlined */
    hogql?: string
    /** Generated ClickHouse query for debugging */
    clickhouse?: string
}

While in the public beta, the response format may still change.

HogQL guide

Strings and quotes

Quotation symbols work the same way they would work with ClickHouse, which inherits from ANSI SQL:

• Single quotes (') for Strings literals.
• Double quotes (") and Backticks (`) for DataBase identifiers.

For example:

SELECT * FROM events WHERE properties.`$browser` = 'Chrome'

Property access

To access a property stored on an event or person, just use dot notation. For example properties.$browser or person.properties.$initial_browser.

Nested property or JSON access, such as properties.$some.nested.property, works as well.

PostHog's properties include always include $ as a prefix, while custom properties do not (unless you add it).

Property identifiers must be known at query time. For dynamic access, use the JSON manipulation functions from below on the properties field directly.

Property types

If you have specified a type for an event or person property under "Data Management", it will be returned in this type. All other properties are returned as strings.

For example:

SELECT round(properties.$screen_width * properties.$screen_height / 1000000, 2) as `Screen MegaPixels` FROM events LIMIT 100

This works because $screen_width and $screen_height are both defined as numeric properties. Thus you can multiply them.

To cast a string property into a different type, use type conversion functions, such as toFloat().

You can find a full list of properties and their types in your data management tab.

Supported ClickHouse functions

The following ClickHouse functions are enabled. This list is ever expanding. Please submit a pull request if we're missing something obvious.

Type conversion

• toInt
• toFloat
• toDecimal
• toDate
• toDateTime
• toUUID
• toString
• toJSONString
• parseDateTime
• parseDateTimeBestEffort

Arithmetic

• plus
• minus
• multiply
• divide
• intDiv
• intDivOrZero
• modulo
• moduloOrZero
• positiveModulo
• negate
• abs
• gcd
• lcm
• max2
• min2
• multiplyDecimal
• divideDecimal

Arrays and strings in common

• empty
• notEmpty
• length
• reverse
• in
• notIn

Arrays

• array
• range
• arrayConcat
• arrayElement
• has
• hasAll
• hasAny
• hasSubstr
• indexOf
• arrayCount
• countEqual
• arrayEnumerate
• arrayEnumerateUniq
• arrayPopBack
• arrayPopFront
• arrayPushBack
• arrayPushFront
• arrayResize
• arraySlice
• arraySort
• arrayReverseSort
• arrayUniq
• arrayJoin
• arrayDifference
• arrayDistinct
• arrayEnumerateDense
• arrayIntersect
• arrayReverse
• arrayFilter
• arrayFlatten
• arrayCompact
• arrayZip
• arrayAUC
• arrayMap
• arrayFill
• arraySplit
• arrayReverseFill
• arrayReverseSplit
• arrayExists
• arrayAll
• arrayFirst
• arrayLast
• arrayFirstIndex
• arrayLastIndex
• arrayMin
• arrayMax
• arraySum
• arrayAvg
• arrayCumSum
• arrayCumSumNonNegative
• arrayProduct

Comparison

• equals
• notEquals
• less
• greater
• lessOrEquals
• greaterOrEquals

Logical

• and
• or
• xor
• not

Type conversions

• toInt
• toFloat
• toDecimal
• toDate
• toDateTime
• toUUID
• toString
• toJSONString
• parseDateTime
• parseDateTimeBestEffort

Dates and times

• toTimeZone
• timeZoneOf
• timeZoneOffset
• toYear
• toQuarter
• toMonth
• toDayOfYear
• toDayOfMonth
• toDayOfWeek
• toHour
• toMinute
• toSecond
• toUnixTimestamp
• toStartOfYear
• toStartOfISOYear
• toStartOfQuarter
• toStartOfMonth
• toLastDayOfMonth
• toMonday
• toStartOfWeek
• toStartOfDay
• toStartOfHour
• toStartOfMinute
• toStartOfSecond
• toStartOfFiveMinutes
• toStartOfTenMinutes
• toStartOfFifteenMinutes
• toTime
• toISOYear
• toISOWeek
• toWeek
• toYearWeek
• age
• dateDiff
• dateTrunc
• dateAdd
• dateSub
• timeStampAdd
• timeStampSub
• now
• NOW
• nowInBlock
• today
• yesterday
• timeSlot
• toYYYYMM
• toYYYYMMDD
• toYYYYMMDDhhmmss
• addYears
• addMonths
• addWeeks
• addDays
• addHours
• addMinutes
• addSeconds
• addQuarters
• subtractYears
• subtractMonths
• subtractWeeks
• subtractDays
• subtractHours
• subtractMinutes
• subtractSeconds
• subtractQuarters
• timeSlots
• formatDateTime
• dateName
• monthName
• fromUnixTimestamp
• toModifiedJulianDay
• fromModifiedJulianDay
• toIntervalSecond
• toIntervalMinute
• toIntervalHour
• toIntervalDay
• toIntervalWeek
• toIntervalMonth
• toIntervalQuarter
• toIntervalYear

Strings

• lengthUTF8
• leftPad
• rightPad
• leftPadUTF8
• rightPadUTF8
• lower
• upper
• lowerUTF8
• upperUTF8
• isValidUTF8
• toValidUTF8
• repeat
• format
• reverseUTF8
• concat
• substring
• substringUTF8
• appendTrailingCharIfAbsent
• convertCharset
• base58Encode
• base58Decode
• tryBase58Decode
• base64Encode
• base64Decode
• tryBase64Decode
• endsWith
• startsWith
• trim
• trimLeft
• trimRight
• encodeXMLComponent
• decodeXMLComponent
• extractTextFromHTML
• ascii
• concatWithSeparator

Searching in strings

• position
• positionCaseInsensitive
• positionUTF8
• positionCaseInsensitiveUTF8
• multiSearchAllPositions
• multiSearchAllPositionsUTF8
• multiSearchFirstPosition
• multiSearchFirstIndex
• multiSearchAny
• match
• multiMatchAny
• multiMatchAnyIndex
• multiMatchAllIndices
• multiFuzzyMatchAny
• multiFuzzyMatchAnyIndex
• multiFuzzyMatchAllIndices
• extract
• extractAll
• extractAllGroupsHorizontal
• extractAllGroupsVertical
• like
• ilike
• notLike
• notILike
• ngramDistance
• ngramSearch
• countSubstrings
• countSubstringsCaseInsensitive
• countSubstringsCaseInsensitiveUTF8
• countMatches
• regexpExtract

Replacing in strings

• replace
• replaceAll
• replaceOne
• replaceRegexpAll
• replaceRegexpOne
• regexpQuoteMeta
• translate
• translateUTF8

Conditional

• if
• multiIf

Mathematical

• e
• pi
• exp
• log
• ln
• exp2
• log2
• exp10
• log10
• sqrt
• cbrt
• erf
• erfc
• lgamma
• tgamma
• sin
• cos
• tan
• asin
• acos
• atan
• pow
• power
• intExp2
• intExp10
• cosh
• acosh
• sinh
• asinh
• atanh
• atan2
• hypot
• log1p
• sign
• degrees
• radians
• factorial
• width_bucket

Rounding

• floor
• ceil
• trunc
• round
• roundBankers
• roundToExp2
• roundDuration
• roundAge
• roundDown

Maps

• map
• mapFromArrays
• mapAdd
• mapSubtract
• mapPopulateSeries
• mapContains
• mapKeys
• mapValues
• mapContainsKeyLike
• mapExtractKeyLike
• mapApply
• mapFilter
• mapUpdate

Splitting strings

• splitByChar
• splitByString
• splitByRegexp
• splitByWhitespace
• splitByNonAlpha
• arrayStringConcat
• alphaTokens
• extractAllGroups
• ngrams
• tokens

Bit

• bitAnd
• bitOr
• bitXor
• bitNot
• bitShiftLeft
• bitShiftRight
• bitRotateLeft
• bitRotateRight
• bitSlice
• bitTest
• bitTestAll
• bitTestAny
• bitCount
• bitHammingDistance

Bitmap

• bitmapBuild
• bitmapToArray
• bitmapSubsetInRange
• bitmapSubsetLimit
• subBitmap
• bitmapContains
• bitmapHasAny
• bitmapHasAll
• bitmapCardinality
• bitmapMin
• bitmapMax
• bitmapTransform
• bitmapAnd
• bitmapOr
• bitmapXor
• bitmapAndnot
• bitmapAndCardinality
• bitmapOrCardinality
• bitmapXorCardinality
• bitmapAndnotCardinality

URLs

• protocol
• domain
• domainWithoutWWW
• topLevelDomain
• firstSignificantSubdomain
• cutToFirstSignificantSubdomain
• cutToFirstSignificantSubdomainWithWWW
• port
• path
• pathFull
• queryString
• fragment
• queryStringAndFragment
• extractURLParameter
• extractURLParameters
• extractURLParameterNames
• URLHierarchy
• URLPathHierarchy
• encodeURLComponent
• decodeURLComponent
• encodeURLFormComponent
• decodeURLFormComponent
• netloc
• cutWWW
• cutQueryString
• cutFragment
• cutQueryStringAndFragment
• cutURLParameter

JSON

• isValidJSON
• JSONHas
• JSONLength
• JSONArrayLength
• JSONType
• JSONExtractUInt
• JSONExtractInt
• JSONExtractFloat
• JSONExtractBool
• JSONExtractString
• JSONExtractKey
• JSONExtractKeys
• JSONExtractRaw
• JSONExtractArrayRaw
• JSONExtractKeysAndValuesRaw

Geo

• greatCircleDistance
• geoDistance
• greatCircleAngle
• pointInEllipses
• pointInPolygon

Nullable

• isNull
• isNotNull
• coalesce
• ifNull
• nullIf
• assumeNotNull
• toNullable

Tuples

• tuple
• tupleElement
• untuple
• tupleHammingDistance
• tupleToNameValuePairs
• tuplePlus
• tupleMinus
• tupleMultiply
• tupleDivide
• tupleNegate
• tupleMultiplyByNumber
• tupleDivideByNumber
• dotProduct

Time window

• tumble
• hop
• tumbleStart
• tumbleEnd
• hopStart
• hopEnd

Distance window

• L1Norm
• L2Norm
• LinfNorm
• LpNorm
• L1Distance
• L2Distance
• LinfDistance
• LpDistance
• L1Normalize
• L2Normalize
• LinfNormalize
• LpNormalize
• cosineDistance

Other

• isFinite
• isInfinite
• ifNotFinite
• isNaN
• bar
• transform
• formatReadableDecimalSize
• formatReadableSize
• formatReadableQuantity
• formatReadableTimeDelta

Supported aggregations

The following aggregations are enabled. This list is ever expanding. Please submit a pull request if we're missing something obvious.

Standard aggregate functions

• count
• countIf
• min
• minIf
• max
• maxIf
• sum
• sumIf
• avg
• avgIf
• any
• anyIf
• stddevPop
• stddevPopIf
• stddevSamp
• stddevSampIf
• varPop
• varPopIf
• varSamp
• varSampIf
• covarPop
• covarPopIf
• covarSamp
• covarSampIf

ClickHouse-specific aggregate functions

• anyHeavy
• anyHeavyIf
• anyLast
• anyLastIf
• argMin
• argMinIf
• argMax
• argMaxIf
• avgWeighted
• avgWeightedIf
• groupArray
• groupUniqArray
• groupArrayInsertAt
• groupArrayInsertAtIf
• groupArrayMovingAvg
• groupArrayMovingAvgIf
• groupArrayMovingSum
• groupArrayMovingSumIf
• groupBitAnd
• groupBitAndIf
• groupBitOr
• groupBitOrIf
• groupBitXor
• groupBitXorIf
• groupBitmap
• groupBitmapIf
• groupBitmapAnd
• groupBitmapAndIf
• groupBitmapOr
• groupBitmapOrIf
• groupBitmapXor
• groupBitmapXorIf
• sumWithOverflow
• sumWithOverflowIf
• deltaSum
• deltaSumIf
• deltaSumTimestamp
• deltaSumTimestampIf
• sumMap
• sumMapIf
• minMap
• minMapIf
• maxMap
• maxMapIf
• skewSamp
• skewSampIf
• skewPop
• skewPopIf
• kurtSamp
• kurtSampIf
• kurtPop
• kurtPopIf
• uniq
• uniqIf
• uniqExact
• uniqExactIf
• uniqHLL12
• uniqHLL12If
• uniqTheta
• uniqThetaIf
• simpleLinearRegression
• simpleLinearRegressionIf
• contingency
• contingencyIf
• cramersV
• cramersVIf
• cramersVBiasCorrected
• cramersVBiasCorrectedIf
• theilsU
• theilsUIf
• maxIntersections
• maxIntersectionsIf
• maxIntersectionsPosition
• maxIntersectionsPositionIf