Skip to main content

ClickHouse Table Schemas

ClickHouse database is used as Prisme Analytics events store. This page document schemas of Prisme Analytics tables.

Sessions Table

Sessions are stored in sessions table with the following schemas:

Column nameTypeDescription
domainStringDomain from which events originate.
session_timestampDateTime (ALIAS)Date and time at which session was created.
entry_timestampDateTime (ALIAS)Same as session_timestamp.
entry_pathStringPath of this session's first viewed page.
exit_timestampDateTimeDate and time of this session's last viewed page.
exit_pathStringPath of this session's last viewed page?
visitor_idStringUnique visitor identifier.
is_anonBoolWhether the visitor is anonymous.
session_uuidUUIDUUIDv7 associated to this session.
session_idUInt128 (ALIAS)session_uuid as a UInt128, use this field if you want to sort on session_uuid.
operating_systemLowCardinality(String)Visitor's Operating System (OS)
browser_familyLowCardinality(String)Visitor's browser family (e.g. Chrome, Firefox, etc)
deviceLowCardinality(String)Visitor's device name.
referrer_domainStringReferrer of entry page.
country_codeLowCardinality(String)ISO 3166-1 alpha-2 – two-letter country codes. (XX is used when country is unknown)
utm_sourceStringUTM source parameter.
utm_mediumStringUTM medium parameter.
utm_campaignStringUTM campaign parameter.
utm_termStringUTM term parameter.
utm_contentStringUTM content parameter.
versionUInt16 (ALIAS)Version field of VersionelCollapsingMergeTree
pageviewsUInt16 (ALIAS version)Number of pageviews associated to this session.
is_bounceUInt16 (ALIAS)Whether this session is a bounce or not.

Events Tables

Page Views Events

Page Views Events are stored in pageviews table with the following schemas:

Column nameTypeDescription
timestampDateTimeDate and time at which pageview events occurred.
domainStringDomain from which events originate.
pathStringViewed page path.
visitor_idStringThe visitor ID.
session_uuidUUIDThe session UUIDv7.

Custom Events

Custom Events are stored in events_custom table with the following schemas:

Column nameTypeDescription
timestampDateTimeDate and time at which pageview events occurred.
domainStringDomain from which events originate.
pathStringViewed page path.
visitor_idStringA visitor ID.
session_uuidUUIDThe session UUIDv7.
nameStringName of the event.
keysArray(String)Top level keys of event JSON object.
valuesArray(String)Top level values of event JSON object.

Prisme stores custom events JSON in two columns, keys and values, instead of a single String column to improve query performances. Unfortunately, this is not practical, that's why we provide a event_property helper function.

tip

It is recommended to avoid nested JSON objects as it may impact performance at query time.

Let's suppose you're website send click events with two properties, x and y, cursor position on X and Y axis respectively:

document.addEventListener('click', (ev) => {
window.prisme.trigger('click', { x: ev.clientX, y: ev.clientY })
})

You want to retrieve clicks at position (128, 64).

SELECT * FROM prisme.events_custom
WHERE name = 'click'
AND event_property('x') == '128'
AND event_property('y') == '64';
note

Properties are compared against a String value as event_property helper function always returns a JSON string. You can cast value if needed using appropriate type conversion function.

Now let's say we want to retrieve clicks in the first 100 pixels on Y axis. We need an integer value.

In that case, a simple cast is the more appropriate:

SELECT * FROM prisme.events_custom
WHERE name = 'click'
AND toInt64OrZero(event_property('y')) <= 100;

If you're deeling with a more complex case, you may need more specialized JSON functions. You can find the list of all ClickHouse JSON functions here.