Understand how RawTree organizes data, handles evolving fields, and controls queries.

Introduction

RawTree is an analytics database forked from ClickHouse, designed for raw and semi-structured data. It builds on ClickHouse's SQL engine and database constructs, with changes to evolving fields, storage, and optimization. You can insert JSON without defining every column, then query its fields with SQL.

RawTree and its services

RawTree means the database throughout these docs. RawTree Cloud is the hosted service. RawTree BYOC (Bring Your Own Cloud) deploys the database infrastructure in your own cloud account.

Both service deployments surround RawTree with APIs, service authentication, cluster management, and integrations. Where these docs say the service, they mean RawTree Cloud or RawTree BYOC, as applicable to your deployment. Available integrations and operational controls depend on the deployment. See Deployment.

SQL underpins and defines database behavior

You can use SQL for more than retrieving rows. RawTree uses declarative SQL statements and named database objects to define data access, physical layouts, and resource use.

Concern	Database constructs
Data and physical layouts	Databases, tables, sorting keys, indexes, and projections.
Identity and access	Users, roles, grants, and row policies.
Query settings and resource use	Settings profiles, workloads, and resources.
Inspection	System tables, query statistics, and `EXPLAIN`.

You can configure and query supported objects through SQL, but the service also manages objects on your behalf and may on occassion requiere using one of the Service surfaces (API, CLI, etc.). For example, a database-role API key uses a database user whose assigned roles determine its data privileges.

See ClickHouse compatibility and API scope to understand separate engine behavior from interface restrictions.

Organizations, service accounts, cluster provisioning, and workflow schedules belong to the service. They are separate from SQL users, roles, and workloads. The following concepts explain how these layers fit together.

Organize your data

In the service, an organization groups members and clusters. A cluster supplies the compute resources for its databases, and each database contains tables. Tables hold the rows you insert and query.

This hierarchy also defines the scope of access and configuration. For example, an API key belongs to an organization and a cluster. See Resource model.

Storage and compute explains how table parts, replicas, and background work use the cluster's resources.

Store data before defining its schema

The service uses RawMergeTree by default for all its tables. Raw tables accept rows (JSON or other) with different fields. The first insert through the service can create the destination table. New fields can arrive in later rows without a column-definition step. The Ingestion model explains insert outcomes, partial writes, and retries.

RawTree exposes JSON paths as SQL columns, such as user.id. It stores undeclared fields in the internal __raw_data column. Declared columns can coexist with those fields. This means that both user.id and __raw_data.user.id will work on SELECT statements. Some functionality, such as TTLs, may require explicit __raw_data. expressions.

# Insert two events with three fields each.
# The first has plan, while the second has amount.
curl -X POST \
  "https://api.rawtree.com/v1/tables/intro_events?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '[
    {"event":"signup","user_id":1,"plan":"free"},
    {"event":"purchase","user_id":2,"amount":19.9}
  ]'

# Return both fields. A field missing from a row appears as NULL.
curl -X POST \
  "https://api.rawtree.com/v1/query?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM intro_events ORDER BY user_id LIMIT 10"}'

# Insert two events with three fields each.
# The first has plan, while the second has amount.
rtree insert --database analytics --table intro_events \
  --data '[
    {"event":"signup","user_id":1,"plan":"free"},
    {"event":"purchase","user_id":2,"amount":19.9}
  ]'

# Return both fields. A field missing from a row appears as NULL.
rtree query --database analytics --json \
  "SELECT * FROM intro_events ORDER BY user_id LIMIT 10"

CREATE TABLE analytics.intro_events ENGINE = RawMergeTree;

-- Insert two events with three fields each.
-- The first has plan, while the second has amount.
INSERT INTO analytics.intro_events (__raw_data) VALUES
  ('{"event":"signup","user_id":1,"plan":"free"}'),
  ('{"event":"purchase","user_id":2,"amount":19.9}');

-- Return both fields. A field missing from a row appears as NULL.
SELECT * FROM analytics.intro_events ORDER BY user_id LIMIT 10;

amount	event	plan	user_id
`NULL`	signup	free	1
19.9	purchase	`NULL`	2

Values keep their types. A dynamic field can contain a number in one row and a string in another. A known field that is absent from a row returns NULL. Type conversion makes the intended meaning explicit when a query must combine different stored types.

# Insert the same field as a string in one row and an integer in the other.
curl -X POST \
  "https://api.rawtree.com/v1/tables/intro_types?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '[
    {"event":"request","duration_ms":"42"},
    {"event":"request","duration_ms":42}
  ]'

# dynamicType() reports each stored type without converting the values.
curl -X POST \
  "https://api.rawtree.com/v1/query?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sql":"SELECT dynamicType(duration_ms) AS stored_type, count() AS rows FROM intro_types GROUP BY stored_type ORDER BY stored_type"}'

# Insert the same field as a string in one row and an integer in the other.
rtree insert --database analytics --table intro_types \
  --data '[
    {"event":"request","duration_ms":"42"},
    {"event":"request","duration_ms":42}
  ]'

# dynamicType() reports each stored type without converting the values.
rtree query --database analytics --json \
  "SELECT dynamicType(duration_ms) AS stored_type, count() AS rows
   FROM intro_types
   GROUP BY stored_type
   ORDER BY stored_type"

CREATE TABLE analytics.intro_types ENGINE = RawMergeTree;

-- Insert the same field as a string in one row and an integer in the other.
INSERT INTO analytics.intro_types (__raw_data) VALUES
  ('{"event":"request","duration_ms":"42"}'),
  ('{"event":"request","duration_ms":42}');

-- dynamicType() reports each stored type without converting the values.
SELECT dynamicType(duration_ms) AS stored_type, count() AS rows
FROM analytics.intro_types
GROUP BY stored_type
ORDER BY stored_type;

stored_type	rows
Int64	1
String	1

Query and optimize data

SQL lets you filter, aggregate, and join data across tables. Select the fields you need and inspect the result metadata, statistics, and hints. Query hints are runtime type-compatibility warnings about incompatible Dynamic values handled during query execution. See Queries and results.

Adaptive optimization connects the mechanisms that change physical layouts as data and query patterns evolve.

Sorting keys determine how table parts store rows and help queries skip data. A part is a stored chunk of a table. RawTree can choose keys per part and revise those choices during background merges from observed filter use. An explicit table key overrides automatic key selection. Storage order does not guarantee result order.

# Insert three requests.
curl -X POST \
  "https://api.rawtree.com/v1/tables/intro_requests?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '[
    {"service":"api","status":200,"duration_ms":12},
    {"service":"api","status":500,"duration_ms":85},
    {"service":"worker","status":200,"duration_ms":30}
  ]'

# Set a sorting key on service and status.
# The new key applies to future inserts and later merges.
# The change does not immediately rewrite existing parts.
curl -X PATCH \
  "https://api.rawtree.com/v1/tables/intro_requests?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sorting_key":"service, status"}'

# Insert three requests.
rtree insert --database analytics --table intro_requests \
  --data '[
    {"service":"api","status":200,"duration_ms":12},
    {"service":"api","status":500,"duration_ms":85},
    {"service":"worker","status":200,"duration_ms":30}
  ]'

# Set a sorting key on service and status.
# The new key applies to future inserts and later merges.
# The change does not immediately rewrite existing parts.
rtree table update --database analytics intro_requests \
  --sorting-key 'service, status'

CREATE TABLE analytics.intro_requests ENGINE = RawMergeTree;

-- Insert three requests.
INSERT INTO analytics.intro_requests (__raw_data) VALUES
  ('{"service":"api","status":200,"duration_ms":12}'),
  ('{"service":"api","status":500,"duration_ms":85}'),
  ('{"service":"worker","status":200,"duration_ms":30}');

-- Set a sorting key on service and status.
-- The new key applies to future inserts and later merges.
-- The change does not immediately rewrite existing parts.
ALTER TABLE analytics.intro_requests
MODIFY ORDER BY (service, status);

Projections store alternate layouts for a table. A query can use a matching projection to reduce its work. When automatic projections are enabled, RawTree can create them from repeated query patterns. Projections add storage and work during inserts and merges, so their benefit depends on the queries they serve.

# Use intro_requests from the previous example.
# Store counts by service in a projection.
# Projection definitions use explicit __raw_data paths.
# Queries can still refer to service.
curl -X POST \
  "https://api.rawtree.com/v1/query?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sql":"ALTER TABLE intro_requests ADD PROJECTION by_service (SELECT `__raw_data.service`, count() GROUP BY `__raw_data.service`)"}'

# Populate the projection for existing data in the background.
# Command acceptance does not mean that every existing part contains the projection.
curl -X POST \
  "https://api.rawtree.com/v1/query?database=analytics" \
  --header "Authorization: Bearer $RAWTREE_API_KEY" \
  --header "Content-Type: application/json" \
  -d '{"sql":"ALTER TABLE intro_requests MATERIALIZE PROJECTION by_service"}'

# Use intro_requests from the previous example.
# Store counts by service in a projection.
# Projection definitions use explicit __raw_data paths.
# Queries can still refer to service.
rtree query --database analytics \
  'ALTER TABLE intro_requests ADD PROJECTION by_service (SELECT `__raw_data.service`, count() GROUP BY `__raw_data.service`)'

# Populate the projection for existing data in the background.
# Command acceptance does not mean that every existing part contains the projection.
rtree query --database analytics \
  'ALTER TABLE intro_requests MATERIALIZE PROJECTION by_service'

-- Use intro_requests from the previous example.
-- Store counts by service in a projection.
-- Projection definitions use explicit __raw_data paths.
-- Queries can still refer to service.
ALTER TABLE analytics.intro_requests
ADD PROJECTION by_service
(
  SELECT `__raw_data.service`, count()
  GROUP BY `__raw_data.service`
);

-- Populate the projection for existing data in the background.
-- Command acceptance does not mean that every existing part contains the projection.
ALTER TABLE analytics.intro_requests
MATERIALIZE PROJECTION by_service;

Change existing data

Mutations apply changes to existing table parts. They run asynchronously. Acceptance of a command does not mean that every part is updated. Their progress and completion state help you track operations such as materializing a projection for existing data.

Access control determines what a caller can do. The service offers permission-based API keys for broad cluster access. Database roles and grants can restrict access to selected tables and columns. Row policies filter reads for the roles to which they apply.

Workload scheduling controls how queries compete for server resources. A workload hierarchy can separate interactive queries from batch work. Settings profiles can assign a workload to a database role and constrain changes to that setting. Access controls and resource controls address different needs.

Connect sources and automate work

Apps, connectors, and transforms are service features that connect source data to tables. Apps enable protocols such as OpenTelemetry, while connectors read external sources such as Kafka. Transforms reshape supported payloads into rows before insert. The resulting data remains available through SQL.

SQL workflows are service-managed jobs that run saved SQL on demand or on a schedule. Read queries can send their results to sinks, such as a webhook. An INSERT INTO ... SELECT workflow writes query results into an existing table. Workflows reuse queries after data arrives, while transforms shape data at ingestion.

Continue with the Resource model to understand where your data and permissions belong.

Introduction

On this page