# Introduction (/docs/concepts)



Introduction [#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-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](/docs/deployment).

SQL underpins and defines database behavior [#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](/docs/reference/clickhouse-compatibility)
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 [#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](/docs/concepts/resource-model).

[Storage and compute](/docs/concepts/storage-and-compute) explains how table
parts, replicas, and background work use the cluster's resources.

Store data before defining its schema [#store-data-before-defining-its-schema]

The service uses RawMergeTree by default for all its tables.
[Raw tables](/docs/concepts/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](/docs/concepts/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.

<SnippetTabs label="Insert events with different fields">
  <SnippetTab value="API">
    ```bash
    # 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"}'
    ```
  </SnippetTab>

  <SnippetTab value="CLI">
    ```bash
    # 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"
    ```
  </SnippetTab>

  <SnippetTab value="SQL">
    ```sql
    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;
    ```
  </SnippetTab>
</SnippetTabs>

| amount | event    | plan   | user\_id |
| ------ | -------- | ------ | -------- |
| `NULL` | signup   | free   | 1        |
| 19.9   | purchase | `NULL` | 2        |

Values keep their types. A [dynamic field](/docs/concepts/dynamic-fields) 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.

<SnippetTabs label="Inspect stored Dynamic types">
  <SnippetTab value="API">
    ```bash
    # 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"}'
    ```
  </SnippetTab>

  <SnippetTab value="CLI">
    ```bash
    # 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"
    ```
  </SnippetTab>

  <SnippetTab value="SQL">
    ```sql
    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;
    ```
  </SnippetTab>
</SnippetTabs>

| stored\_type | rows |
| ------------ | ---- |
| Int64        | 1    |
| String       | 1    |

Query and optimize data [#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](/docs/concepts/dynamic-fields#runtime-type-compatibility-warnings)
are runtime type-compatibility warnings about incompatible `Dynamic` values
handled during query execution. See [Queries and results](/docs/concepts/queries-and-results).

[Adaptive optimization](/docs/concepts/adaptive-optimization) connects the
mechanisms that change physical layouts as data and query patterns evolve.

[Sorting keys](/docs/concepts/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.

<SnippetTabs label="Set a table sorting key">
  <SnippetTab value="API">
    ```bash
    # 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"}'
    ```
  </SnippetTab>

  <SnippetTab value="CLI">
    ```bash
    # 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'
    ```
  </SnippetTab>

  <SnippetTab value="SQL">
    ```sql
    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);
    ```
  </SnippetTab>
</SnippetTabs>

[Projections](/docs/concepts/projections) store alternate layouts for a table.
A query can use a matching projection to reduce its work. When
[automatic projections](/docs/reference/sql/settings/merge-tree#enable_auto_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.

<SnippetTabs label="Create and populate a projection">
  <SnippetTab value="API">
    ```bash
    # 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"}'
    ```
  </SnippetTab>

  <SnippetTab value="CLI">
    ```bash
    # 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'
    ```
  </SnippetTab>

  <SnippetTab value="SQL">
    ```sql
    -- 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;
    ```
  </SnippetTab>
</SnippetTabs>

Change existing data [#change-existing-data]

[Mutations](/docs/concepts/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.

Control access and share compute [#control-access-and-share-compute]

[Access control](/docs/concepts/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](/docs/concepts/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 [#connect-sources-and-automate-work]

[Apps, connectors, and transforms](/docs/concepts/apps-connectors-and-transforms)
are service features that connect source data to tables. Apps enable protocols
such as [OpenTelemetry](/docs/integrations/opentelemetry),
while connectors read external sources such as
[Kafka](/docs/guides/ingest/kafka-connector).
[Transforms](/docs/guides/transform/transforms) reshape supported payloads into
rows before insert. The resulting data remains available through SQL.

[SQL workflows](/docs/concepts/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](/docs/concepts/resource-model) to understand
where your data and permissions belong.