> ## Documentation Index
> Fetch the complete documentation index at: https://www.propeldata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL API

> Execute SQL queries from your application.

<div />

## Use cases

<CardGroup cols={1}>
  <Card title="Bespoke data access patterns">
    Query with SQL directly for access patterns that don't fit into the other APIs.
  </Card>
</CardGroup>

## Features

<AccordionGroup>
  <Accordion title="Query with SQL syntax">
    Query your data using familiar SQL syntax over the API.

    See the [SQL Reference](/docs/sql-reference/overview) documentation for more details.
  </Accordion>

  <Accordion title="Query Data Pools using their unique name or ID.">
    Using the Data Pool ID:

    ```sql theme={"system"}
    SELECT * FROM DPOXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
    ```

    Using the Data Pool Name without spaces:

    ```sql theme={"system"}
    SELECT * FROM events
    ```

    Using the Data Pool Name with spaces:

    ```sql theme={"system"}
    SELECT * FROM "TacoSoft Demo Data"
    ```
  </Accordion>

  <Accordion title="ClickHouse and Postgres SQL dialects">
    Compatible with PostgreSQL and ClickHouse SQL dialects.

    ```graphql theme={"system"}
    query {
      sqlV1(input: {
        query: "..."
        dialect: "CLICKHOUSE"
      }) {
      ...
      }
    }
    ```
  </Accordion>
</AccordionGroup>

## Example

### Example 1: Simple SQL query

Get the first 3 rows from the "TacoSoft Demo Data" Data Pool.

<CodeGroup>
  ```graphql Query theme={"system"}
  query {
    sqlV1(input: {
      query: """
        SELECT
          order_id,
          quantity,
          taco_name,
          sauce_name
        FROM "TacoSoft Demo Data"
        LIMIT 3
      """
    }) {
      columns {
        columnName
      }
      rows
    }
  }
  ```

  ```json Response theme={"system"}
  {
    "sqlV1": {
      "columns": [
        {
          "columnName": "order_id"
        },
        {
          "columnName": "quantity"
        },
        {
          "columnName": "taco_name"
        },
        {
          "columnName": "sauce_name"
        }
      ],
      "rows": [
        [
          "ddb63195-3053-44d1-8a7e-fc7b29de4685",
          "1",
          "Chorizo",
          "Queso Blanco"
        ],
        [
          "ddb63195-3053-44d1-8a7e-fc7b29de4685",
          "2",
          "Breakfast",
          "Salsa Verde"
        ],
        [
          "ddb63195-3053-44d1-8a7e-fc7b29de4685",
          "3",
          "Veggie",
          "Chipotle Mayo"
        ]
      ]
    }
  }
  ```
</CodeGroup>

## Usage

## Arguments

Input to the SqlV1 api.

<ParamField path="query" type="String" required>
  The SQL query.
</ParamField>

<ParamField path="dialect" type="SqlDialectV1">
  The SQL dialect to use. If not provided, the query is parsed on a best-effort basis.

  <Expandable title="SqlDialectV1">
    The SQL dialect to use when parsing queries.

    <ParamField path=" ">
      * `POSTGRESQL`: Parse as PostgreSQL-compatible SQL.
      * `CLICKHOUSE`: Parse as ClickHouse-compatible SQL.
    </ParamField>
  </Expandable>
</ParamField>

## Response

Response from the SQL API.

<ParamField path="columns" type="[SqlColumnResponse!]" required>
  The column names in the same order as present in the `data` field.

  <Expandable title="SqlColumnResponse">
    <ParamField path="columnName" type="String" required>
      The name of the returned column.
    </ParamField>

    <ParamField path="type" type="ColumnType" required>
      The returned column's type.

      <Expandable title="ColumnType">
        The Propel data types.

        <ParamField path=" ">
          * `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.
        </ParamField>
      </Expandable>
    </ParamField>

    <ParamField path="isNullable" type="Boolean" required>
      Whether the column is nullable, meaning whether it accepts a null value.
    </ParamField>
  </Expandable>
</ParamField>

<ParamField path="rows" type="[[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.
</ParamField>

<ParamField path="info" type="QueryInfo" required>
  The Query statistics and metadata.

  <Expandable title="QueryInfo">
    The Query Info object. It contains metadata and statistics about a Query performed.

    <ParamField path="id" type="ID" required>
      The Query's unique identifier.
    </ParamField>

    <ParamField path="createdAt" type="DateTime" required>
      The date and time in UTC when the Query was created.
    </ParamField>

    <ParamField path="createdBy" type="String" required>
      The unique identifier of the actor that performed the Query.
    </ParamField>

    <ParamField path="modifiedAt" type="DateTime" required>
      The date and time in UTC when the Query was last modified.
    </ParamField>

    <ParamField path="modifiedBy" type="String" required>
      The unique identifier of the actor that modified the Query.
    </ParamField>

    <ParamField path="bytesProcessed" type="String" required>
      The bytes processed by the Query.
    </ParamField>

    <ParamField path="durationInMilliseconds" type="Int" required>
      The duration of the Query in milliseconds.
    </ParamField>

    <ParamField path="recordsProcessed" type="String" required>
      The number of records processed by the Query.
    </ParamField>

    <ParamField path="resultingBytes" type="Int" required>
      The bytes returned by the Query.
    </ParamField>

    <ParamField path="resultingRecords" type="Int" required>
      The number of records returned by the Query.
    </ParamField>

    <ParamField path="propeller" type="Propeller">
      The Propeller used for this query.

      <Expandable title="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.

        <ParamField path=" ">
          * `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
        </ParamField>
      </Expandable>
    </ParamField>

    <ParamField path="status" type="QueryStatus" required>
      The Query status.

      <Expandable title="QueryStatus">
        The Query status.

        <ParamField path=" ">
          * `COMPLETED`: The Query was completed succesfully.
          * `ERROR`: The Query experienced an error.
          * `TIMED_OUT`: The Query timed out.
        </ParamField>
      </Expandable>
    </ParamField>

    <ParamField path="type" type="QueryType" required>
      The Query type.

      <Expandable title="QueryType">
        The Query type.

        <ParamField path=" ">
          * `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.
        </ParamField>
      </Expandable>
    </ParamField>

    <ParamField path="subtype" type="QuerySubtype">
      The Query subtype.

      <Expandable title="QuerySubtype">
        The Query subtype.

        <ParamField path=" ">
          * `COUNTER`: Indicates a Metric counter Query.
          * `TIME_SERIES`: Indicates a Metric time series Query.
          * `LEADERBOARD`: Indicates a Metric leaderboard Query.
        </ParamField>
      </Expandable>
    </ParamField>

    <ParamField path="sql" type="String" required>
      The SQL the query executed.
    </ParamField>
  </Expandable>
</ParamField>

## Embeddable UI components

<CardGroup cols={2}>
  <Card title="useSql" icon="code" href="/docs/embeddable-ui/components/use-sql">
    A React Hook for querying data using raw SQL.
  </Card>
</CardGroup>