SQL API

Execute SQL queries from your application.

Use cases

Bespoke data access patterns

Query with SQL directly for access patterns that don’t fit into the other APIs.

Features

Query with SQL syntax

Query your data using familiar SQL syntax over the API.

See the SQL Reference documentation for more details.

Query Data Pools using their unique name or ID.

Using the Data Pool ID:

SELECT * FROM DPOXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

Using the Data Pool Name without spaces:

SELECT * FROM events

Using the Data Pool Name with spaces:

SELECT * FROM "TacoSoft Demo Data"

ClickHouse and Postgres SQL dialects

Compatible with PostgreSQL and ClickHouse SQL dialects.

query {
  sqlV1(input: {
    query: "..."
    dialect: "CLICKHOUSE"
  }) {
  ...
  }
}

Example

Example 1: Simple SQL query

Get the first 3 rows from the “TacoSoft Demo Data” Data Pool.

Query

query {
  sqlV1(input: {
    query: """
      SELECT
        order_id,
        quantity,
        taco_name,
        sauce_name
      FROM "TacoSoft Demo Data"
      LIMIT 3
    """
  }) {
    columns {
      columnName
    }
    rows
  }
}

Usage

Arguments

Input to the SqlV1 api.

query · String · required

The SQL query.

dialect · SqlDialectV1

The SQL dialect to use. If not provided, the query is parsed on a best-effort basis.

Show SqlDialectV1

The SQL dialect to use when parsing queries.

​

• POSTGRESQL: Parse as PostgreSQL-compatible SQL.
• CLICKHOUSE: Parse as ClickHouse-compatible SQL.

Response

Response from the SQL API.

columns · [SqlColumnResponse!] · required

The column names in the same order as present in the data field.

Show SqlColumnResponse

columnName · String · required

The name of the returned column.

type · ColumnType · required

The returned column’s type.

Show ColumnType

The Propel data types.

​

• BOOLEAN: True or false.
• STRING: A variable-length string.
• FLOAT: A 32-bit signed double-precision floating point number.
• DOUBLE: A 64-bit signed double-precision floating point number.
• INT8: An 8-bit signed integer, with a minimum value of -2⁷ and a maximum value of 2⁷-1.
• INT16: A 16-bit signed integer, with a minimum value of -2¹⁵ and a maximum value of 2¹⁵-1.
• INT32: A 32-bit signed integer, with a minimum value of -2³¹ and a maximum value of 2³¹-1.
• INT64: A 64-bit signed integer, with a minimum value of -2⁶³ and a maximum value of 2⁶³-1.
• DATE: A date without a timestamp. For example, “YYYY-MM-DD”.
• TIMESTAMP: A date with a timestamp. For example, “yyy-MM-dd HH:mm:ss”.
• JSON: A JavaScript Object Notation (JSON) document.
• CLICKHOUSE: A ClickHouse-specific type.

isNullable · Boolean · required

Whether the column is nullable, meaning whether it accepts a null value.

rows · [[String]!] · required

The data gathered by the SQL query. The data is returned in an N x M matrix format, where the first dimension are the rows retrieved, and the second dimension are the columns. Each cell can be either a string or null, and the string can represent a number, text, date or boolean value.

info · QueryInfo · required

The Query statistics and metadata.

Show QueryInfo

The Query Info object. It contains metadata and statistics about a Query performed.

id · ID · required

The Query’s unique identifier.

createdAt · DateTime · required

The date and time in UTC when the Query was created.

createdBy · String · required

The unique identifier of the actor that performed the Query.

modifiedAt · DateTime · required

The date and time in UTC when the Query was last modified.

modifiedBy · String · required

The unique identifier of the actor that modified the Query.

bytesProcessed · String · required

The bytes processed by the Query.

durationInMilliseconds · Int · required

The duration of the Query in milliseconds.

recordsProcessed · String · required

The number of records processed by the Query.

resultingBytes · Int · required

The bytes returned by the Query.

resultingRecords · Int · required

The number of records returned by the Query.

propeller · Propeller

The Propeller used for this query.

Show Propeller

A Propeller determines your Application’s query processing power. The larger the Propeller, the faster the queries and the higher the cost. Every Propel Application (and therefore every set of API credentials) has a Propeller that determines the speed and cost of queries.

​

• P1_X_SMALL: Max records per second: 5,000,000 records per second
• P1_SMALL: Max records per second: 25,000,000 records per second
• P1_MEDIUM: Max records per second: 100,000,000 records per second
• P1_LARGE: Max records per second: 250,000,000 records per second
• P1_X_LARGE: Max records per second: 500,000,000 records per second

status · QueryStatus · required

The Query status.

Show QueryStatus

The Query status.

​

• COMPLETED: The Query was completed succesfully.
• ERROR: The Query experienced an error.
• TIMED_OUT: The Query timed out.

type · QueryType · required

The Query type.

Show QueryType

The Query type.

​

• METRIC: Indicates a Metric Query.
• STATS: Indicates a Dimension Stats Query.
• REPORT: Indicates a Report Query.
• RECORDS: Indicates a Record Table Query.
• RECORDS_BY_UNIQUE_ID: Indicates records queried by unique ID.
• SQL: Indicates a SQL Query.
• TOP_VALUES: Indicates a Top Values Query.

subtype · QuerySubtype

The Query subtype.

Show QuerySubtype

The Query subtype.

​

• COUNTER: Indicates a Metric counter Query.
• TIME_SERIES: Indicates a Metric time series Query.
• LEADERBOARD: Indicates a Metric leaderboard Query.

sql · String · required

The SQL the query executed.

Embeddable UI components

useSql

A React Hook for querying data using raw SQL.