Datamart APIs
Create a datamart
Pick the tables, columns, joins and tenancy that your metrics query.
curl --request POST https://api.usedatabrain.com/api/v2/data-app/datamarts \ --header "Authorization: Bearer $DATABRAIN_SERVICE_TOKEN" \ --header "Content-Type: application/json" \ --data '{ "name": "Sales", "datasourceName": "Sales warehouse", "tableList": [ { "name": "orders", "schemaName": "public", "clientColumn": "customer_id", "columns": [ { "name": "id" }, { "name": "amount", "allowAggregate": true, "defaultAggregation": "SUM" }, { "name": "created_at", "isApplyTimezone": true } ] }, { "name": "customers", "schemaName": "public", "columns": [{ "name": "id" }, { "name": "name" }] } ], "relationships": [ { "relationshipName": "orders_customers", "parentTableName": "orders", "parentColumnName": "customer_id", "childTableName": "customers", "childColumnName": "id", "cardinality": "ManyToOne", "join": "LEFT JOIN" } ], "tenancySettings": { "tenancyLevel": "TABLE", "clientColumnType": "STRING", "schemaName": "public", "tableName": "customers", "tableClientNameColumn": "name", "tablePrimaryKeyColumn": "id" } }'Auth
A service token. See Authentication.
ApiTokenAuthorizationBearer <token>A service token or a data app API token.
Body
Unknown fields are refused. Names are case-sensitive.
Datamart name, unique in your organization. Other endpoints call it datamartName
Name of a connected datasource.
At least one table.
7 fields in tableList
Table name, as in the synced schema.
Schema of the table. Can be database.schema
At least one column.
11 fields in columns
Column name, as in the schema. Any name if isCustomColumn is true
Alias.
Display name.
Hide the column. Default false
A calculated column. Default false
Required with isCustomColumn. SQL expression of the calculated column.
The expression is already an aggregate.
Convert this date column to the guest token's timezone. Default false
ALL, AVG, SUM, COUNT, COUNT_DISTINCT, MAX, MIN, MEDIAN, PERCENTILE_CONT or PERCENTILE_DISC
ASC or DESC
The table's tenant column, for TABLE tenancy.
Display name.
Hide the table from metric builders. Default false
Stored with the table.
Joins between the tables.
7 fields in relationships
Name of the join.
One side of the join.
One side of the join.
The other side.
The other side.
OneToOne, OneToMany, ManyToOne or ManyToMany
INNER JOIN, LEFT JOIN, RIGHT JOIN or FULL JOIN
How each customer's rows are scoped.
7 fields in tenancySettings
TABLE (a tenant column), DATABASE (a schema or database per tenant) or MULTI_DATABASE
Required for TABLE. The table that lists your tenants.
Required for TABLE. The table that lists your tenants.
Required for TABLE. Column with the tenant name.
Required for TABLE. Column with the tenant id, the value you send as clientId
Default database for DATABASE and MULTI_DATABASE
tableList
The data type comes from the schema. You do not send it.
relationships
Relationships are not checked against tableList. Check the names yourself.
Response
{ "id": "Sales" }id is the datamart name. To get its UUID, list datamarts.
Errors
| Status | error.code | Why |
|---|---|---|
| 400 | INVALID_REQUEST_BODY | A field is missing, unknown or wrong. The name is taken. tableList or columns is empty |
| 400 | INVALID_REQUEST_BODY | A table is not in the schema. hint and details.reason: "SCHEMA_TABLE_NOT_FOUND" say which. Grant access to it, then sync |
| 400 | INVALID_REQUEST_BODY | A column is not in the table: Invalid column names in table. Please verify all column names match the schema. |
| 400 | DATASOURCE_NAME_ERROR | No datasource has this name |
| 500 | INTERNAL_SERVER_ERROR | The datamart could not be saved. Leaving out schemaName also ends here |
For errors that every endpoint returns, see Authentication.
Next, add a semantic layer.
curl -X POST 'https://api.usedatabrain.com/api/v2/data-app/datamarts' \ -H 'Content-Type: application/json' \ -d '{ "name": "Sales", "datasourceName": "Sales warehouse", "tableList": [ { "name": "orders", "schemaName": "public", "clientColumn": "customer_id", "columns": [ { "name": "id" }, { "name": "amount", "allowAggregate": true, "defaultAggregation": "SUM" }, { "name": "created_at", "isApplyTimezone": true } ] }, { "name": "customers", "schemaName": "public", "columns": [ { "name": "id" }, { "name": "name" } ] } ], "relationships": [ { "relationshipName": "orders_customers", "parentTableName": "orders", "parentColumnName": "customer_id", "childTableName": "customers", "childColumnName": "id", "cardinality": "ManyToOne", "join": "LEFT JOIN" } ], "tenancySettings": { "tenancyLevel": "TABLE", "clientColumnType": "STRING", "schemaName": "public", "tableName": "customers", "tableClientNameColumn": "name", "tablePrimaryKeyColumn": "id" }}'{ "id": "Sales"}