Analytics data should be stored in a different relational database than all other types of data. Queries to this database are performed with the DatamartContext API.
| Info |
|---|
Despite of its name, |
Types of tables that can be queried with the DatamartContext API:
Datamarts
Data Sources
Data Feeds
Collection of Rows
The simplest form of a query is a non-aggregating query. Be careful about the number of rows your query returns – it might be very large.
| Note |
|---|
The returned value from |
| Note |
|---|
You should close the stream as soon as possible. |
Transactions.groovy
| Code Block | |
|---|---|
|
...
...
|
...
void forEachTransaction(Closure callback){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDatamart('Transaction')
def query = dataMartAPI.newQuery(table, false)
.selectAll()
def results = dataMartAPI.streamQuery(query)
// StreamResults is not iterable. Iterate in this non-standard way.
while(results.next()){ // ❶
def row = results.get()
callback.call(row) // ❷
}
results.close() // ❸
} |
❶ Iterate the table rows. Since the result is neither a collection nor an iterator, use for/while loop.
❷ Do something for each row.
❸ Close the stream after usage.
Where Filters
Limit the rows that are included in your results by applying an SQL WHERE clause. The rows that are included in the query must satisfy the filter that is provided to the where() method.
ExchangeRates.groovy
| Code Block | |
|---|---|
|
...
...
|
...
BigDecimal getExchangeRate(Date validFrom, String currencyFrom, String currencyTo){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDataSource('ccy')
def filters = [
Filter.lessOrEqual('CcyValidFrom', validFrom),
Filter.greaterThan('CcyValidTo', validFrom),
Filter.equal('CcyFrom', currencyFrom),
Filter.equal('CcyTo', currencyTo),
]
def query = dataMartAPI.newQuery(table, true)
.select('CcyExchangeRate', 'ExchangeRate')
.where(*filters) // ❶
def result = dataMartAPI.executeQuery(query)
return result.getData().getAt(0)?.ExchangeRate
} |
❶ Filter the rows with a WHERE filter.
Aggregation
More often than not, queries to Datamarts perform some sort of aggregation. Turn the query into an aggregation query by setting the second argument to newQuery() to true. Now you can wrap the fields that you want with an SQL aggregate function.
| Info |
|---|
You can declare aliases which are especially useful when you use aggregate functions. |
Transactions.groovy
| Code Block | ||
|---|---|---|
|
...
BigDecimal getTotalSales(Date dateFrom, Date dateTo){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDatamart('Transaction')
def dateFilters = [
Filter.greaterOrEqual('InvoiceDate', dateFrom),
Filter.lessThan('InvoiceDate', dateTo),
]
def query = dataMartAPI.newQuery(table, true)
.select('SUM(InvoicePrice)', 'TotalSales') // ❶
.where(*dateFilters)
def result = dataMartAPI.executeQuery(query)
return result.getData().getAt(0).TotalSales
} |
❶ Aggregate the InvoicePrice field values. The use of an alias means that the result Map will have the key 'TotalSales', instead of InvoicePrice.
Grouping
Rows can can be grouped together according to the value of some field(s). This will automatically happen when the column in the select statement is not wrapped within an aggregate function.
Transactions.groovy
| Code Block | |
|---|---|
|
...
...
|
...
List<Map<String, Object>> getTotalSalesByYear(){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDatamart("Transaction")
def query = dataMartAPI.newQuery(table, true)
.select('InvoiceDateYear') // ❶
.select('SUM(InvoicePrice)', 'TotalSales') // ❷
.orderBy('InvoiceDateYear')
def result = dataMartAPI.executeQuery(query)
return result.getData().toList()
} |
❶ Group by the InvoiceDateYear field values. This field is of the type dimension.
❷ Aggregate the InvoiceDateYear field values. This field is not of the type dimension.
Filtering
After performing an aggregation, you can filter the query results with a SQL HAVING clause. Apply it with the having() method on the query builder.
Transactions.groovy
| Code Block | |
|---|---|
|
...
...
|
...
List<Map<String, Object>> getTotalSalesByYear(BigDecimal totalSalesThreshold){
def dataMartAPI = api.getDatamartContext()
def table = dataMartAPI.getDatamart("Transaction")
def resultFilters = [Filter.greaterOrEqual('TotalSales', totalSalesThreshold)]
def query = dataMartAPI.newQuery(table, true)
.select('InvoiceDateYear')
.select('SUM(InvoicePrice)', 'TotalSales')
.orderBy('InvoiceDateYear')
.having(*resultFilters) // ❶
def result = dataMartAPI.executeQuery(query)
return result.getData().toList()
} |
❶ Apply a filter to the query results.
Distinct Values
To retrieve only unique values from a Data Source, use the selectDistinct() method.
DataMart.groovy
| Code Block | ||
|---|---|---|
|
...
List getUniqueValues(
String dataMartName,
String dimensionColumnName
) {
def dataSourceAPI = api.getDatamartContext()
def table = dataSourceAPI.getDatamart(dataMartName)
def query = dataSourceAPI.newQuery(table, true)
.select(dimensionColumnName)
.selectDistinct() // ❶
def results = dataSourceAPI.executeQuery(query)
return results.getData().toList()[dimensionColumnName]
} |
❶ selectDistinct() ensures that all retrieved values are unique.