Skip to main content

Data APIs

The Propel Data APIs let you expose any Data Pool (ClickHouse table) as an API. This guide covers the different APIs with examples of various data visualizations.

You can test your GraphQL API requests without writing a single line of code in our API Playground.

SQL API​

In Propel, you can use SQL to query your data. This way, you can easily take advantage of Propel's fast response times, high availability, and access controls with the convenience of familiar SQL syntax.

Read the docs

query {
  sqlV1(input: {
    query: """
      SELECT *
      FROM "TacoSoft Demo Data"
      LIMIT 3
    """
  }) {
    columns {
      columnName
    }
    rows
  }
}

Time Series​

The Time Series API returns values and labels for every time granule over the requested relative or absolute time range.

Read the docs

# Revenue over time
query {
  timeSeries(input: {
    metric: {
      sum: {
        dataPool: {
          name: "TacoSoft Demo Data"
        },
        measure: {
          columnName: "taco_total_price"
        }
      }
    },
    granularity: DAY,
    timeRange: {
      relative: LAST_N_DAYS,
      n: 30
    },
    filterSql: "restaurant_name = 'El Buen Sabor'"
  }) {
      labels
      values
    }
  }

Counter​

The Counter API returns a single value for the requested relative or absolute time range.

Read the docs

Counter metric example cardCounter metric example card
# Last 30 days comparison
query {
  revenueLast30Days: counter(input: {
    metric: {
      sum: {
        dataPool: {
          name: "TacoSoft Demo Data"
        },
        measure: {
          columnName: "taco_total_price"
        }
      }
    },
    timeRange: {
      relative: THIS_MONTH
    },
    filterSql: "restaurant_name = 'La Taqueria'"
  }) {
    value
  }

  revenuePrevious30Days: counter(input: {
    metric: {
      sum: {
        dataPool: {
          name: "TacoSoft Demo Data"
        },
        measure: {
          columnName: "taco_total_price"
        }
      }
    },
    timeRange: {
      relative: PREVIOUS_MONTH
    },
    filterSql: "restaurant_name = 'La Taqueria'"
  }) {
    value
  }
}

Data Grid​

The Data Grid API returns individual records from a Data Pool with the convenience of built-in pagination, filtering, and sorting.

Read the docs

query {
  dataGrid(input: {
    dataPool: {
      name: "TacoSoft Demo Data"
    },
    timeRange: {
      relative: THIS_YEAR
    },
    columns: [
      "timestamp",
      "order_item_id",
      "taco_name",
      "taco_total_price"],
    orderByColumn: 1,
    sort: DESC,
    filterSql: "restaurant_name = 'La Taqueria'",
    first: 5
  }) {
    headers
    rows
    pageInfo {
      hasNextPage
      hasPreviousPage
      endCursor
      startCursor
    }
  }
}

Leaderboard​

The Leaderboard API returns an ordered list of aggregated values grouped by a given dimension.

Read the docs

# Top 10 restaurants by revenue
query {
  leaderboard(input: {
    metric: {
      sum: {
        dataPool: {
          name: "TacoSoft Demo Data"
        },
        measure: {
          columnName: "taco_total_price"
        }
      }
    },
    sort: DESC,
    timeRange: {
      relative: LAST_N_DAYS,
      n: 30
    },
    rowLimit: 10,
    dimensions: [
      {
        columnName: "restaurant_name"
      }
    ],
    filterSql: ""
  }) {
    headers
    rows
  }
}

Metric Report​

The Metric Report API returns one or multiple metrics grouped by a common dimension.

Read the docs

Leaderboard metric example cardLeaderboard metric example card
# Fetch a report showing the revenue and taco order
# count for the top performing restaurant-taco pairs.
query {
  metricReport(input: {
    timeRange: {
      relative: LAST_N_DAYS,
      n: 30
    },
    dimensions: [
      {
        columnName: "restaurant_name"
      },
      {
        columnName: "taco_name"
      }
    ],
    metrics: [
      {
        metric: {
          sum: {
            dataPool: {
              name: "TacoSoft Demo Data"
            },
            measure: {
              columnName: "taco_total_price"
            }
          }
        }
      },
      {
        metric: {
          countDistinct: {
            dataPool: {
              name: "TacoSoft Demo Data"
            },
            dimension: {
              columnName: "order_id"
            }
          }
        }
      }
    ],
    orderByColumn: 1,
    first: 10
  }) {
    headers
    rows
    pageInfo {
      startCursor
      endCursor
      hasNextPage
      hasPreviousPage
    }
  }
}

Top Values​

The Top Values API returns an array of the most frequent non-null values in a given column.

Read the docs

query {
  topValues(input: {
    dataPool: {
      name: "TacoSoft Demo Data"
    },
    columnName: "taco_name",
    timeRange: {
      relative: THIS_YEAR
    },
    maxValues: 5
  }) {
    values
  }
}

How to test your GraphQL query​

Once you have a GraphQL query for your data that you want to test, there are a few ways to go about it.

1. GraphQL Explorer​

You can check out our GraphQL Explorer, log in to your Propel account, and test your query there.

2. cURL or a similar tool​

If you'd like to test your query with curl or a similar tool, you can first create an access token like so:

curl -X POST https://auth.us-east-2.propeldata.com/oauth2/token \
-H "Content-Type: application/x-www-form-urlencoded" \
-d "grant_type=client_credentials&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET"

You can find your client ID and secret on Propel Console in the Applications page.

Once you have your access token, you can use it to test your query like this:

curl -X POST https://api.us-east-2.propeldata.com/graphql \
-H "Authorization: Bearer <ACCESS_TOKEN>" \
-H "Content-Type: application/json" \
-d '{
  "query": "query LeaderboardExample1($input: LeaderboardInput!) { leaderboard(input: $input) { headers rows }}",
  "variables": {
    "input": {
      "metricName": "Revenue",
      "sort": "DESC",
      "timeRange": {
        "relative": "LAST_N_DAYS",
        "n": 30
      },
      "rowLimit": 10,
      "dimensions": [
        {
          "columnName": "restaurant_name"
        }
      ],
      "filters": []
    }
  }
}'

3. JavaScript​

Here's an example of how you can run a GraphQL query using fetch in vanilla JavaScript.

const query = `
  query LeaderboardExample1($input: LeaderboardInput!) {
    leaderboard(input: $input) {
      headers
      rows
    }
  }
`

const variables = {
  input: {
    metricName: 'Revenue',
    sort: 'DESC',
    timeRange: {
      relative: 'LAST_N_DAYS',
      n: 30
    },
    rowLimit: 10,
    dimensions: [{ columnName: 'restaurant_name' }],
    filters: []
  }
}

// Fetch access token
fetch('https://auth.us-east-2.propeldata.com/oauth2/token', {
  method: 'post',
  body: `grant_type=client_credentials&client_id=${process.env.CLIENT_ID}&client_secret=${process.env.SECRET}`
  headers: { 'Content-Type': 'application/x-www-form-urlencoded' }
})
  .then((response) => response.json())
  .then((jsonData) => jsonData.access_token)

  // Post the GraphQL query
  .then((access_token) => {
    return fetch('https://api.us-east-2.propeldata.com/graphql', {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${access_token}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({ query, variables })
    })
  })
  .then((response) => response.json())
  .then((data) => console.log(data))
tip

In production code, make sure not to expose your client ID and secret on the frontend.

For a more comprehensive example that shows you the whole flow from creating an OAuth 2.0 client, obtaining an access token, and running and processing a GraphQL query, you can check Propel Next.js Starter App.

Additional guides​