Pivot data cubes API
You can use the Pivot data cubes API to create, read, and modify data cubes.
Pivot must be running in native users mode.
Get all data cubes
Returns a list of all data cubes.
URL
GET /api/v1/data-cubes
Success response
| Code | Description |
|---|---|
| 200 | "status": "ok" |
Sample request
curl -X GET 'http://PIVOT_URL/api/v1/data-cubes' \
--header 'x-imply-api-token:<token>'
Sample response
Show sample response
{
"dataCube": {
"name": "ef58e",
"title": "Wikipedia",
"queryMode": "sql",
"source": {
"value": "wikipedia",
"connectionName": "druid",
"type": "direct"
},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2016-06-27T00:00:11.080Z",
"end": "2016-06-27T21:31:02.498Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "added",
"title": "Added",
"formula": "t.\"added\"",
"type": "NUMBER"
},
{
"name": "channel",
"title": "Channel",
"formula": "t.\"channel\"",
"type": "STRING"
},
{
"name": "cityName",
"title": "City Name",
"formula": "t.\"cityName\"",
"type": "STRING"
},
{
"name": "comment",
"title": "Comment",
"formula": "t.\"comment\"",
"type": "STRING"
},
{
"name": "commentLength",
"title": "Comment Length",
"formula": "t.\"commentLength\"",
"type": "NUMBER"
},
{
"name": "countryIsoCode",
"title": "Country Iso Code",
"formula": "t.\"countryIsoCode\"",
"type": "STRING"
},
{
"name": "countryName",
"title": "Country Name",
"formula": "t.\"countryName\"",
"type": "STRING"
},
{
"name": "deleted",
"title": "Deleted",
"formula": "t.\"deleted\"",
"type": "NUMBER"
},
{
"name": "delta",
"title": "Delta",
"formula": "t.\"delta\"",
"type": "NUMBER"
},
{
"name": "deltaBucket",
"title": "Delta Bucket",
"formula": "t.\"deltaBucket\"",
"type": "NUMBER"
},
{
"name": "diffUrl",
"title": "Diff Url",
"formula": "t.\"diffUrl\"",
"type": "STRING"
},
{
"name": "flags",
"title": "Flags",
"formula": "t.\"flags\"",
"type": "STRING"
},
{
"name": "isAnonymous",
"title": "Is Anonymous",
"formula": "t.\"isAnonymous\"",
"type": "STRING"
},
{
"name": "isMinor",
"title": "Is Minor",
"formula": "t.\"isMinor\"",
"type": "STRING"
},
{
"name": "isNew",
"title": "Is New",
"formula": "t.\"isNew\"",
"type": "STRING"
},
{
"name": "isRobot",
"title": "Is Robot",
"formula": "t.\"isRobot\"",
"type": "STRING"
},
{
"name": "isUnpatrolled",
"title": "Is Unpatrolled",
"formula": "t.\"isUnpatrolled\"",
"type": "STRING"
},
{
"name": "metroCode",
"title": "Metro Code",
"formula": "t.\"metroCode\"",
"type": "STRING"
},
{
"name": "namespace",
"title": "Namespace",
"formula": "t.\"namespace\"",
"type": "STRING"
},
{
"name": "page",
"title": "Page",
"formula": "t.\"page\"",
"type": "STRING"
},
{
"name": "regionIsoCode",
"title": "Region Iso Code",
"formula": "t.\"regionIsoCode\"",
"type": "STRING"
},
{
"name": "regionName",
"title": "Region Name",
"formula": "t.\"regionName\"",
"type": "STRING"
},
{
"name": "user",
"title": "User",
"formula": "t.\"user\"",
"type": "STRING"
}
],
"measures": [
{
"name": "count",
"title": "Number of Events",
"formula": "COUNT(*)",
"numberStyle": {
"type": "si",
"leading": 1,
"minDecimals": 2,
"maxDecimals": 2,
"integer": true,
"abbreviationSpace": true
}
},
{
"name": "sum_added",
"title": "Added",
"formula": "SUM(t.\"added\")"
},
{
"name": "sum_commentLength",
"title": "Comment Length",
"formula": "SUM(t.\"commentLength\")"
},
{
"name": "sum_delta",
"title": "Delta",
"formula": "SUM(t.\"delta\")"
},
{
"name": "sum_deltaBucket",
"title": "Delta Bucket",
"formula": "SUM(t.\"deltaBucket\")"
},
{
"name": "sum_deleted",
"title": "Deleted",
"formula": "SUM(t.\"deleted\")"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT5M"
},
{
"name": "kttm2683",
"title": "kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"user@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-25T00:00:00.031Z",
"end": "2019-08-25T23:59:59.686Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
},
{
"name": "agent_category",
"title": "Agent Category",
"formula": "t.\"agent_category\"",
"type": "STRING"
},
{
"name": "agent_type",
"title": "Agent Type",
"formula": "t.\"agent_type\"",
"type": "STRING"
},
{
"name": "browser",
"title": "Browser",
"formula": "t.\"browser\"",
"type": "STRING"
},
{
"name": "browser_version",
"title": "Browser Version",
"formula": "t.\"browser_version\"",
"type": "STRING"
},
{
"name": "city",
"title": "City",
"formula": "t.\"city\"",
"type": "STRING"
},
{
"name": "continent",
"title": "Continent",
"formula": "t.\"continent\"",
"type": "STRING"
},
{
"name": "country",
"title": "Country",
"formula": "t.\"country\"",
"type": "STRING"
},
{
"name": "event_subtype",
"title": "Event Subtype",
"formula": "t.\"event_subtype\"",
"type": "STRING"
},
{
"name": "event_type",
"title": "Event Type",
"formula": "t.\"event_type\"",
"type": "STRING"
},
{
"name": "forwarded_for",
"title": "Forwarded For",
"formula": "t.\"forwarded_for\"",
"type": "STRING"
},
{
"name": "language",
"title": "Language",
"formula": "t.\"language\"",
"type": "SET/STRING"
},
{
"name": "loaded_image",
"title": "Loaded Image",
"formula": "t.\"loaded_image\"",
"type": "STRING"
},
{
"name": "number",
"title": "Number",
"formula": "t.\"number\"",
"type": "STRING"
},
{
"name": "os",
"title": "Os",
"formula": "t.\"os\"",
"type": "STRING"
},
{
"name": "path",
"title": "Path",
"formula": "t.\"path\"",
"type": "STRING"
},
{
"name": "platform",
"title": "Platform",
"formula": "t.\"platform\"",
"type": "STRING"
},
{
"name": "referrer",
"title": "Referrer",
"formula": "t.\"referrer\"",
"type": "STRING"
},
{
"name": "referrer_host",
"title": "Referrer Host",
"formula": "t.\"referrer_host\"",
"type": "STRING"
},
{
"name": "region",
"title": "Region",
"formula": "t.\"region\"",
"type": "STRING"
},
{
"name": "remote_address",
"title": "Remote Address",
"formula": "t.\"remote_address\"",
"type": "STRING"
},
{
"name": "screen",
"title": "Screen",
"formula": "t.\"screen\"",
"type": "STRING"
},
{
"name": "session",
"title": "Session",
"formula": "t.\"session\"",
"type": "STRING"
},
{
"name": "session_length",
"title": "Session Length",
"formula": "t.\"session_length\"",
"type": "NUMBER"
},
{
"name": "timezone",
"title": "Timezone",
"formula": "t.\"timezone\"",
"type": "STRING"
},
{
"name": "timezone_offset",
"title": "Timezone Offset",
"formula": "t.\"timezone_offset\"",
"type": "STRING"
},
{
"name": "version",
"title": "Version",
"formula": "t.\"version\"",
"type": "STRING"
},
{
"name": "window",
"title": "Window",
"formula": "t.\"window\"",
"type": "STRING"
}
],
"measures": [
{
"name": "count",
"title": "Number of Events",
"formula": "COUNT(*)",
"numberStyle": {
"type": "si",
"leading": 1,
"minDecimals": 2,
"maxDecimals": 2,
"integer": true,
"abbreviationSpace": true
}
},
{
"name": "sum_session_length",
"title": "Session Length",
"formula": "SUM(t.\"session_length\")"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT5M"
}
}
Get a data cube
Returns a data cube with a specified name. Note that the data cube name is not the same as the name that appears in the Pivot UI. Get all data cubes to find the data cube name.
URL
GET /api/v1/data-cubes/{name}
Success response
| Code | Description |
|---|---|
| 200 | "status": "ok" |
Sample request
curl -X GET 'http://PIVOT_URL/api/v1/data-cubes/533d' \
--header 'x-imply-api-token:<token>'
Sample response
Show sample response
{
"dataCube": {
"name": "533d",
"title": "Kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"options": {},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-19T00:00:00.234Z",
"end": "2019-08-19T23:59:59.877Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
},
{
"name": "agent_category",
"title": "Agent Category",
"formula": "t.\"agent_category\"",
"type": "STRING"
},
{
"name": "agent_type",
"title": "Agent Type",
"formula": "t.\"agent_type\"",
"type": "STRING"
},
{
"name": "browser",
"title": "Browser",
"formula": "t.\"browser\"",
"type": "STRING"
},
{
"name": "browser_version",
"title": "Browser Version",
"formula": "t.\"browser_version\"",
"type": "STRING"
},
{
"name": "city",
"title": "City",
"formula": "t.\"city\"",
"type": "STRING"
},
{
"name": "continent",
"title": "Continent",
"formula": "t.\"continent\"",
"type": "STRING"
},
{
"name": "country",
"title": "Country",
"formula": "t.\"country\"",
"type": "STRING"
},
{
"name": "event_subtype",
"title": "Event Subtype",
"formula": "t.\"event_subtype\"",
"type": "STRING"
},
{
"name": "event_type",
"title": "Event Type",
"formula": "t.\"event_type\"",
"type": "STRING"
},
{
"name": "forwarded_for",
"title": "Forwarded For",
"formula": "t.\"forwarded_for\"",
"type": "STRING"
},
{
"name": "language",
"title": "Language",
"formula": "t.\"language\"",
"type": "STRING"
},
{
"name": "loaded_image",
"title": "Loaded Image",
"formula": "t.\"loaded_image\"",
"type": "STRING"
},
{
"name": "number",
"title": "Number",
"formula": "t.\"number\"",
"type": "STRING"
},
{
"name": "os",
"title": "Os",
"formula": "t.\"os\"",
"type": "STRING"
},
{
"name": "path",
"title": "Path",
"formula": "t.\"path\"",
"type": "STRING"
},
{
"name": "platform",
"title": "Platform",
"formula": "t.\"platform\"",
"type": "STRING"
},
{
"name": "referrer",
"title": "Referrer",
"formula": "t.\"referrer\"",
"type": "STRING"
},
{
"name": "referrer_host",
"title": "Referrer Host",
"formula": "t.\"referrer_host\"",
"type": "STRING"
},
{
"name": "region",
"title": "Region",
"formula": "t.\"region\"",
"type": "STRING"
},
{
"name": "remote_address",
"title": "Remote Address",
"formula": "t.\"remote_address\"",
"type": "STRING"
},
{
"name": "screen",
"title": "Screen",
"formula": "t.\"screen\"",
"type": "STRING"
},
{
"name": "session",
"title": "Session",
"formula": "t.\"session\"",
"type": "STRING"
},
{
"name": "session_length",
"title": "Session Length",
"formula": "t.\"session_length\"",
"type": "NUMBER"
},
{
"name": "timezone",
"title": "Timezone",
"formula": "t.\"timezone\"",
"type": "STRING"
},
{
"name": "timezone_offset",
"title": "Timezone Offset",
"formula": "t.\"timezone_offset\"",
"type": "STRING"
},
{
"name": "version",
"title": "Version",
"formula": "t.\"version\"",
"type": "STRING"
},
{
"name": "window",
"title": "Window",
"formula": "t.\"window\"",
"type": "STRING"
}
],
"measures": [
{
"name": "count",
"title": "Number of Events",
"formula": "COUNT(*)",
"numberStyle": {
"type": "si",
"leading": 1,
"minDecimals": 2,
"maxDecimals": 2,
"integer": true,
"abbreviationSpace": true
}
},
{
"name": "sum_session_length",
"title": "Session Length",
"formula": "SUM(t.\"session_length\")"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT5M"
}
}
Create a data cube
Creates a data cube with a specified name.
URL
POST /api/v1/data-cubes
Sample request
Show sample request
curl -X POST 'http://PIVOT_URL/api/v1/data-cubes' \
--header 'x-imply-api-token: <token>' \
--header 'Content-Type: application/json' \
--data-raw '{
"dataCube": {
"name": "Kttm_cube",
"title": "Kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"options": {},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-19T00:00:00.234Z",
"end": "2019-08-19T23:59:59.877Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
}
],
"measures": [
{
"name": "count",
"title": "Number of Events",
"formula": "COUNT(*)",
"numberStyle": {
"type": "si",
"leading": 1,
"minDecimals": 2,
"maxDecimals": 2,
"integer": true,
"abbreviationSpace": true
}
},
{
"name": "sum_session_length",
"title": "Session Length",
"formula": "SUM(t.\"session_length\")"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT5M"
}
}'
Success response
| Code | Description |
|---|---|
| 200 | "status": "ok" |
Sample error response
| Code | Example body |
|---|---|
| 400 Bad Request | "err": "ft.name 'Kttm cube' is not a URL safe name. Try 'Kttm_cube' instead?" |
Sample response
Show sample response
{
"status": "ok",
"message": "Created successfully",
"dataCube": {
"name": "Kttm_cube",
"title": "Kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"options": {},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-19T00:00:00.234Z",
"end": "2019-08-19T23:59:59.877Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
}
],
"measures": [
{
"name": "count",
"title": "Number of Events",
"formula": "COUNT(*)",
"numberStyle": {
"type": "si",
"leading": 1,
"minDecimals": 2,
"maxDecimals": 2,
"integer": true,
"abbreviationSpace": true
}
},
{
"name": "sum_session_length",
"title": "Session Length",
"formula": "SUM(t.\"session_length\")"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT5M"
}
}
Update a data cube
Updates the details of a data cube with a specified name. Note that the data cube name is not the same as the name that appears in the Pivot UI. Get all data cubes to find a data cube name.
URL
POST /api/v1/data-cubes/{name}
Sample request
The following example request adds a dimension for country and changes defaultRefreshRate to PT1M. The name in the request body must match the {name} in the URL. You can't update the name field.
At a minimum, you must provide name and source in the dataCube object. You must provide all properties you want to retain in the data cube, even if you don't want to update them. Pivot recreates the data cube with the details you specify in the request.
Show sample request
curl -X POST 'http://PIVOT_URL/api/v1/data-cubes/Kttm_cube' \
--header 'x-imply-api-token: <token>' \
--header 'Content-Type: application/json' \
--data-raw '{
"dataCube": {
"name": "Kttm_cube",
"title": "Kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-19T00:00:00.234Z",
"end": "2019-08-19T23:59:59.877Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
},
{
"name": "country",
"title": "Country",
"formula": "t.\"country\"",
"type": "STRING"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT1M"
}
}'
Success response
| Code | Description |
|---|---|
| 200 | "status": "ok" |
Sample error response
| Code | Example body |
|---|---|
| 400 Bad Request | ”error": "the name you entered does not match" |
Show sample response
Sample response
{
"status": "ok",
"message": "Updated successfully",
"dataCube": {
"name": "Kttm_cube",
"title": "Kttm",
"queryMode": "sql",
"source": {
"value": "kttm",
"connectionName": "druid",
"type": "direct"
},
"options": {},
"readAccess": {
"access": "all"
},
"modifyAccess": {
"access": "single",
"users": [
"admin@example.com"
]
},
"downloadAccess": {
"access": "all"
},
"attributes": [
{
"name": "__time",
"type": "TIME",
"nativeType": "__time",
"range": {
"start": "2019-08-19T00:00:00.234Z",
"end": "2019-08-19T23:59:59.877Z",
"bounds": "[]"
}
}
],
"dimensions": [
{
"name": "__time",
"title": "Time",
"formula": "t.\"__time\"",
"type": "TIME"
},
{
"name": "adblock_list",
"title": "Adblock List",
"formula": "t.\"adblock_list\"",
"type": "STRING"
},
{
"name": "country",
"title": "Country",
"formula": "t.\"country\"",
"type": "STRING"
}
],
"specialTimeDimension": "__time",
"enforceTimeFilter": true,
"defaultRefreshRate": "PT1M"
}
}
Delete a data cube
Deletes a data cube with a specified name.
URL
DELETE /api/v1/data-cubes/{name}
Sample request
curl -X DELETE 'http://PIVOT_URL/api/v1/data-cubes/Kttm_cube' \
--header 'x-imply-api-token:<token>'
Success response
| Code | Description |
|---|---|
| 200 | "status": "ok" |
Sample error response
| Code | Example body |
|---|---|
| 404 Not Found | "error": "This data cube does not exist. Please check the name again." |
Sample response
{
"status": "ok",
"message": "Deleted successfully"
}