Propel Data Pools are ClickHouse tables with a pipeline to ingest data from a data source. With the API, you can create an empty Data Pool that creates table in Propel’s Serverless ClickHouse.

See the Create Data Pool API reference documentation.

​
API Arguments

The table below describes the input parameters for the createDataPoolV2 API.

ArgumentDescriptionRequired
uniqueNameThe Data Pool’s unique name. If not specified, Propel will set the ID as the unique name.No
descriptionThe Data Pool’s description.No
timestampThe table’s default timestamp column. Used as the default timestamp for the Query APIs.No
columnsThe list of column names and their types.Yes
accessControlEnabledEnables or disables access control for the Data Pool. If the Data Pool has access control enabled, Applications must be assigned Data Pool Access Policies in order to query the Data Pool and its Metrics.No
tableSettingsInputThe tableSettingsInput object define how the Data Pool’s table is created in ClickHouse. See table settings below.No

​
Table settings

The table settings define how the table is created in ClickHouse. To learn more about table engines, read our guide on How to select a table engine and sorting key.

​
Creating tables with TTL

You can configure Time-To-Live (TTL) settings for your Data Pool to automatically manage data lifecycle. TTL allows you to:

  • Delete old data after a specified time interval
  • Roll up aging data into aggregations before deletion

​
TTL syntax

TTL expr
  [DELETE][, expr DELETE] ...
  [WHERE conditions]
  [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]

​
Example TTL expressions

A table that deletes data after 12 months:

toDateTime(created_at) + INTERVAL 12 MONTH DELETE

A table that deletes data after 12 months where event type is error:

toDateTime(created_at) + INTERVAL 12 MONTH DELETE WHERE event_type = 'error'

A table that deletes data after 12 months and rolls up data into a count and sum aggregations:

toDateTime(created_at) + INTERVAL 12 MONTH DELETE, toDateTime(created_at) + INTERVAL 12 MONTH GROUP BY toStartOfDay(created_at) SET event_count = count(*), sum_quantity = sum(quantity)

Some notes on the TTL expression:

  • The TTL clause must be a prefix of the primary key. Therefore, you need to add toStartOfDay(created_at) to the sorting key.
  • Your Data Pool needs to have the columns event_count and sum_quantity to store the aggregated results.
  • You need to set the following default column values for the aggregations to work:
    • event_count to 1
    • sum_quantity to quantity

See the example on how to create a table with TTL in the Console and API.

​
Examples

​
Example 1: Create a MergeTree table

This example shows how to create a Data Pool using the API and Terraform.

mutation {
  createDataPoolV2(
    input: {
      uniqueName: "New TacoSoft Demo Data"
      description: "A sample dataset consisting of orders for a taco ordering SaaS"
      timestamp: { columnName: "timestamp" }
      columns: [
        { columnName: "quantity", type: INT32, isNullable: false },
        { columnName: "taco_name", type: STRING, isNullable: false },
        { columnName: "sauce_name", type: STRING, isNullable: false },
        { columnName: "restaurant_id", type: STRING, isNullable: false },
        { columnName: "restaurant_name", type: STRING, isNullable: false },
        { columnName: "taco_total_price", type: FLOAT, isNullable: false },
        { columnName: "order_item_id", type: STRING, isNullable: false },
        { columnName: "tortilla_id", type: STRING, isNullable: false },
        { columnName: "toppings", type: JSON, isNullable: false },
        { columnName: "sauce_id", type: STRING, isNullable: false },
        { columnName: "taco_unit_price", type: FLOAT, isNullable: false },
        { columnName: "order_id", type: STRING, isNullable: false },
        { columnName: "order_item_generated_at", type: TIMESTAMP, isNullable: false },
        { columnName: "taco_id", type: STRING, isNullable: false },
        { columnName: "timestamp", type: TIMESTAMP, isNullable: false },
        { columnName: "tortilla_name", type: STRING, isNullable: false }
      ]
      accessControlEnabled: true
      tableSettings: {
        engine: {
          mergeTree: {
            type: MERGE_TREE
          }
        }
        orderBy: ["timestamp"]
      }
    }
  ) {
    dataPool {
      id
      uniqueName
      accessControlEnabled
      description
      tableSettings {
        orderBy
      }
    }
  }
}

​
Example 2: A table that deletes data after 12 months

In the “Table settings” step of the Data Pool creation wizard, click “Advanced”, and you can set the TTL for your Data Pool.

Was this page helpful?

Connect & SDKsInserting data