open bouquet readme

Open Bouquet Developer Hub

Welcome to the Open Bouquet documentation hub for developers. You'll find comprehensive guides and documentation to help you start working with Bouquet as quickly as possible, as well as providing you with support if you get stuck on anything.

Below you will find links to our Quick Start Guide and to the general User Guide.
If you're new to Open Bouquet, there is an Introduction section at the start of the User Guide.
If you prefer the "Just Do It" approach, then I would suggest the Quick Start Guide ("Getting started with Bouquet") which explains how to get started with Bouquet from installation to working with projects and queries -- including links to more detailed information while you're working on through the quick start guide.

This method performs a query on a Bookmark or a Domain and returns the result as a paginated data table.

The result of a query is automatically cached server-side. Therefore, performing the same query repeatedly will not cause performance issues; this is no need to cache the result on the application side.

Still, it is a good practice to set a 'limit' on the query, especially for interactive queries. If you don't, although the server will paginate the result internally to avoid memory overflow, you may have trouble retrieving the result in JSON. If you really need to extract a large table, use the /export API method which provides streaming results.

You can also paginate the results to simplify display.

It is possible to change the layout of the table using the 'data' parameter.

Interactive exploration

This API method supports the style=HTML paremeter for interactive exploration.

HTTP Request

'GET http://yourserverdomain/v4.2/analytics/<REFERENCE>/query'

Example of running the default query for a Domain

# run a default query on the domain 'Sales' in project 'Demo'
curl -X GET 
    - -header 'Accept: application/json' 
    - -header 'Authorization: Bearer TOKEN'
    "http://yourserverdomain/v4.2/analytics/'Demo'.'Sales'/query

TOKEN

Remember to provide a valid OAuth token!

URL Parameters

Parameter
Required
Description

REFERENCE

true

The identifier of the Bookmark/Domain to retrieve

Query Parameters

Note that you can use the API without providing any additional parameters. In that case, the API will compute the default analysis for the reference. If it is a bookmarked query, it will use the bookmark default analysis. In case of a Domain, it will do an no-op analysis.

Parameter
Required
Description

groupBy

false

A multi-valued parameter that requires a list of valid expressions to group the result by. See the Expression Reference Guide for details. Usually you will provide a dimension name by which you want to group the result.
Notes :
1 - The list can be empty: in that case the resulting dataset will be maximum one row if you provide metrics to compute. Each value must be a valid expression in the Bookmark or Domain scope.
2 - You can use a wildcard (*) as a value to replace the default reference settings. In case of a Bookmark, it will be replaced by the selected dimensions; in case of a Domain, it will be replaced by ALL dimensions (use with care).

metrics

false

A multi-valued parameter that requires a list of valid expressions to compute. See the Expression Reference Guide for details. Usually you will pass a metric name upon which you want to aggregate the data.
Notes :
1 - The list can be empty: in that case only the group by is applied (However the query will still perform a group by: if you want to export raw data, include a unique key in the list, such as a customer identifier).
2 - You can also use any valid expression (usually of numeric value) by combining dimensions, metrics and functions.

filters

false

A list of conditional expressions to filter the data on. See the Expression Reference Guide for detail. Usually you can use any dimension of type conditional (also called segment), or create a simple condition using constant values, for instance. You can also use any valid conditional expression by combining dimensions, metrics and functions.
Note :
1 - The list can be empty.

period

false

The period attribute is a shorthand to filter on a timeframe. The expression output type is expected to be a date or a timestamp. You can use only a simple date dimension. If this parameter is set, you should provide a timeframe parameter too - see next row.

timeframe

false

The 'timeframe' parameter is used to define the date or time range of the period. You can either use explicit values (in that case you should provide both a lower and an upper bound, though you can pass a null value to create a semi-open range) or use an alias value or a constant expression. See the Period definition for details.

compareTo

false

The 'compareTo' parameter is used in conjunction with the 'timeframe' parameter to create an automatic comparison between present and past metrics values. Setting a value will automatically turn the compare feature on (see compareTo section for details). You can use either explicit values (in that case you should provide both a lower and an upper bound, though you can pass a null value to create a semi-open range) or an alias value or a constant expression. Note that the alias values for the 'compareTo' parameter are different from the 'timeframe' parameter.

orderBy

false

A list of valid sort expressions to order the result. A valid sort expression is enclosed by the 'DESC()' or 'ASC()' functions. In order to define the value to sort on, you can use either an index value, the name of a column (Dimension or Metric), or any valid expression.
You can also choose how to sort null values if you want an order different than the one chosen by the database.
ASC_NULLS_FIRST ASC_NULLS_LAST
DESC_NULLS_FIRST
DESC_NULLS_LAST
Note :
1 - The list can be empty.

rollup

false

A list of valid rollup expressions to define how to compute sub-totals. A valid rollup identifies a query groupBy column. See Computing Rollup for details.
Note :
1 - The list can be empty.

limit

false

The 'limit' parameter simply limits the number of rows extracted server side. This is mandatory for the /query API to avoid memory overflow. If you need a full ResultSet, you should rather use the /export API which is designed to handle a large number of rows, potentially exceeding the server internal memory. See Query Pagination for details.

offset

false

The 'offset' parameter allows pagination of the rows extracted server side. The default value is to start at the first row. See Query Pagination for details.

maxResults

false

The 'maxResults' parameter controls the client-side pagination, and defines the page size.
Note :
1 - Paginating is unexpensive since the complete query (with limit and offset) is stored in the server cache. See Query Pagination for details.

startIndex

false

The 'startIndex' parameter controls the client-side pagination, and defines the page starting row.
Note :
1 - Paginating is unexpensive since the complete query (with limit and offset) is stored in the server cache. See Query Pagination for details.

lazy

false

When the 'lazy' parameter is set to true, the server tries to retrieve the results from the cache only. If it is not available, no actual computation is performed at all.
Note :
1 - The server may try to perform some transformation if it cannot lookup the exact same query from the cache (that's the smart cache). If the result is not in cache, the /query API returns an error code 404. See Controlling Query Execution for details.

timeout

false

The timeout is in milliseconds. If provided, the request execution will be interrupted if its computation exceeds the provided timeout. In that case, the API will return an ComputingInProgressAPIException.
Notes :
1 - The execution will continue server side, you are only interrupting the request. You can run the same request again (with or without timeout) in order to get the reply (each request is actually pooled, so performing multiple request is actually running the query only once).
2 - The error message contains a QueryID that identifies running query. You can use the /status/QueryID API in order to get information regarding the query (see /status API) or cancel the execution.

state

false

The 'state' parameter, if provided, is used to apply the state configuration to the current domain. It will override any other settings. You can copy an application state - it will compute the corresponding query.
Note :

  • However an application can store custom information and do additional computation, that won't be supported by the API. Also the state must be for the same domain for this to work.

data

false

This parameter defines how to layout the data output. You can use it in conjunction with style and envelope to further customize the reply. See the Supported Data Layouts for more information.

style

false

This parameter defines the reply style. The style can modify the response type in order to get a HTML version of the reply. It can also modify the content of the JSON reply by altering the way it references objects. If 'style=HUMAN' the API will use object names, so you can read and easily understand the output; the downside is that those outputs are fragile to name changes, so you cannot use them to build an application for instance. If you need the references to be immutable, you should to use 'style=ROBOT', the downside being that the output is going to be difficult to read.
Note :
1 - You can switch between 'HUMAN' and 'ROBOT' easily, only the references are modified.

envelope

false

This parameter defines the content of the reply. You can use it to further customize what information you need, from a full reply including the explicit query as it is interpreted by the API, reply meta-information, header and data; or you can just ask for the data. See Style Parameter for details.

Default Query

If no parameter is provided, the method will try to compute a default query.

In case of a Bookmark reference, the /query API will compute the Bookmark pre-defined analysis, with default settings.

In case of a Domain, the /query API will just add the available metrics and compute totals. If a date or timestamp Dimension is defined, the /query API will also try to use it to restrict the query to the current month.

Using State

If a stateID is provided using the 'state' parameter, the /query API will try to apply the state. Note that the state must be defined on the same Domain for this to work.

Period & TimeFrame

The 'period' parameter defines the Expression to use as the main Period for filtering on 'timeframe' and also to perform time-over-time comparison (see Comparing Present and Past Metrics).

The 'period' definition must resolve to a simple Dimension - so in practice you cannot use functions here. Note that there are two ways you can reference a dimension:

  • using the Dimension name (or ID): ''customer > registration date''
  • by combining relations: ''Customer'.'registration date''

Once the 'period' is defined, you can then use the 'PERIOD' alias in the expression you are using for other parameters (for example 'groupBy' or 'orderBy'). Note that you can also use the alias 'PERIOD' in Expressions to extract the first day of month of the date: for example use 'MONTHLY('__PERIOD')' if you want to aggregate results by month.

You can use the 'filters' parameter to filter on period. This may be useful for special cases, for example to define a non-constant period filter. If you use directly the 'filter' parameter, the '__PERIOD' alias is not available.

The 'timeframe' parameter is an array of values that you can use to filter the data on the 'period' value.
If the 'timeframe' is not defined, even if the 'period' is defined, no filter is applied.
If the 'timeframe' is defined, you must also define the 'period' (unless there is a default value available for it).

There are three ways to define the 'timeframe':

  • using an explicit date range
  • using an alias
  • using a expression

TimeFrame defined using explicit date range

You can simply provide the date or timestamp value as a formatted string. The API supports the following formats:

  • ISO 8601 timestamp: 'yyyy-MM-dd'T'HH:mm:ss.SSSZ'
  • ISO 8601 date: 'yyyy-MM-dd'
  • default Java timestamp format: 'EEE MMM dd HH:mm:ss zzz yyyy'

TimeFrame defined using an alias

Four aliases are currently available :

  • '__LAST_7_DAYS'
  • '__CURRENT_MONTH'
  • '__PREVIOUS_MONTH'
  • '__CURRENT_YEAR'

TimeFrame defined using an expression

The timeframe can define a constant expression.

Comparing Present and Past metrics

The 'compareTo' parameter accepts the same definitions as the 'timeframe' parameter, except for the aliases. The 'compareTo' parameter defines a different set of aliases that you can use to easily define relative comparisons:

  • '__COMPARE_TO_PREVIOUS_PERIOD'
  • '__COMPARE_TO_PREVIOUS_MONTH'
  • '__COMPARE_TO_PREVIOUS_YEAR'

Null values

We can not guarantee the result of a request using the compareTo parameter if the dimension/expression of a groupBy contains null values.
We are addressing this issue and will correct it in a future release.

Growth Compare

Bouquet API can give you the growth in percent for the period you are comparing.
To do that you need to add the parameter "&computeGrowth=True" in your request (doing a POST or a GET query).

Ordering the results

The easiest way to define a sorting order is to use an existing ResultSet column and provide the position of the column. For example, in order to sort on the first column, we would use 'ASC(0)' .

The position is zero-based, so the first column is 0, the second is 1, etc.

Sort by a compareTo metric

You cannot use an index based orderBy to sort by a compareTo metric. In that case (if the 'compareTo' parameter is set), indexes do not take into account the compareTo columns generated by the query. If you need to sort the result based on a compareTo column you must use the explicit column name (see ordering by comparison metric for details)

Controlling Query Execution

Different parameters allow you to control and monitor the Query execution:

  • using Lazy execution only to get results from the cache
  • using a timeout to control how long to wait for the result
  • once the Query has timed out, it is then possible to monitor progress and cancel it, see Query Status for details.

Lazy execution

When the 'lazy' parameter is set to true, the server tries to retrieve the result from the cache only. If it is not possible, no actual computation is performed at all. Note that the server may try to perform some transformations if it cannot lookup the exact same query from the cache (that's the smart cache).

If the query is not in cache, the reply will return a code 404 with an error NOT_IN_CACHE.

Note that if you use the 'lazy=noError', the /query API will return a valid reply instead of an error code, with an empty ResultSet and an error message identifying the cause (Not in cache). This may be useful in some situations!

Query Pagination

The API provides the ability to paginate results. There are two ways to paginate the results:

  • limit the result returned by the database. This is controlled by the 'limit' and 'offset' parameters. Note that in order to prevent any memory overhead while returning the data through the API, the /Query API enforces a limit, always defined at 'limit=1000'.
    Internally Open Bouquet uses streaming and cache pagination in order to control memory footprint so that even with a high limit value can be handled. Still, if you want to export a full data-set without any limitation, instead you should use the '/export' API which is specifically designed for that.

  • paginate the data-table to limit the size of the data returned to the API client. This is controlled by the 'maxResults' and 'startIndex' parameters. Since the data-table is already cached server side, using the client pagination is very fast and does not imply any database round-trip (unless the cache has been invalidated).

Database Pagination

The 'limit' parameter defines the maximum number of rows to retrieve from the database. The 'offset' parameter defines the first row to retrieve.
For example:

  • in order to retrieve the first hundred rows, use: 'limit=100' & 'offset=0'
  • in order to retrieve the next page, use: 'limit=100' & 'offset=100'

Client Pagination

The 'maxResults' parameter defines the maximum number of rows to send back to the client. The 'startIndex' parameter defines the first row to retrieve.
For example:

  • in order to retrieve the first ten rows, use: 'maxResults=10' & 'startIndex=0'
  • in order to retrieve the next page, use: 'maxResults=10' & 'startIndex=10'

How can I paginate through a complete data-set?

You can use the database & client pagination and also the query result to easily iterate through a complete data-set.
Say, for example, that you are running a new query without much knowledge of the size of the output. You are using some default values for pagination: 'limit=1000 & maxresults=100' in order to preview the results in a table. The /query output provides you information regarding the size of the data-set:

  • 'result.info.totalSize=1000'
  • 'result.info.complete=false'

In that case the 'result.info.complete' flag turns to 'false' to signal you that you can use the offset to keep retrieving data from the database. This is because the data-set is larger than the limit : here the server hits the database pagination limit.

In order to get the next page, just run the same query using 'limit=1000' & 'offset=1000' & 'maxResults=10'.
Note that when 'result.info.complete=true', the 'result.info.totalSize' returns the size of the current page.

You can also get information regarding the client pagination:

  • 'result.info.pageSize' returns the value of the 'maxResults' parameter
  • 'result.info.startIndex' returns the value of the 'startIndex' parameter

Reply

Supported Data Layouts

The 'data' parameter allows to select the output layout.

Data Value
Table Layout

'TABLE'

returns a matrix, that is an array of rows. A row is an array of cell values.

'RECORDS'

returns the table as an array of records. Each row is a record of the form '{"columnName":value,...}'

'TRANSPOSE'

if the query has multiple metrics, transposes the table to create a row for each record/metric. It adds two additional columns 'metric' and 'value'

'SQL'

additionally you can retrieve the SQL code for the Query instead of the results

'LEGACY'

returns a JSON compatible with legacy API - this is for internal use only, you should not use it, as it will be deprecated soon.

Note that you can play with the 'envelope' parameter in order to customize further the reply. For instance if you want to get only the data as the reply, you can add 'envelope=DATA' parameter. If 'data=SQL' is used, this will return just the SQL statement. See Envelope Parameter for details.

Query