Skip to main content

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

CodeDescription
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

CodeDescription
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

CodeDescription
200"status": "ok"

Sample error response

CodeExample 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

CodeDescription
200"status": "ok"

Sample error response

CodeExample 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

CodeDescription
200"status": "ok"

Sample error response

CodeExample body
404 Not Found"error": "This data cube does not exist. Please check the name again."

Sample response

{
"status": "ok",
"message": "Deleted successfully"
}