Query Resource

A query resource represents a stored GS SQL statement and its cached results. Results are cached on the server and can be retrieved with the GET query/{queryUid}/results call. A query entity can be configured to run on a defined schedule. The Schedule entity is part of the Query entity's JSON body.

The Query resource is different from the gql resource which takes a GS SQL statement, executes it and returns the results.

Query Schedule dayOfWeekTypes:
  • SUN
  • MON
  • TUE
  • WED
  • THUR
  • FRI
  • SAT

Query Schedule repeatType:
  • DAY
  • WEEK
  • MONTH
  • YEAR

Query Schedule monthYearDayType:
  • DAY
  • FIRST
  • SECOND
  • THIRD
  • FOURTH
  • LAST

GET query


Returns a list of all query entities or a single fully populated query if an ID parameter is used.
 Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token token compatible? Yes

Resource URL

http://grovestreams.com/api/query

Parameters

id
 optional
 The id of an existing query to return. Returns the full query definition. Example /query?id=query1

Example Requests

/query

Response Body {
  "success": true,
  "gsql_query": [
    {
      "uid": "",
      "name": "Component List",
      "id": "cl_id"
    },
    {
      "uid": "",
      "name": "Small Units",
      "id": ""
    },
    {
      "uid": "",
      "name": "Large Units",
      "id": ""
    }
  ],
  "message": "",
  "totalCount": 3
}

GET query/{queryUid}


Returns a query with the specified uid.
 Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible? Yes

Resource URL

http://grovestreams.com/api/query/{queryUid}

Parameters

id
 optional
 The id of an existing query to return. Returns the full query definition. {queryUid} will be ignored if the id parameter is used. Example /query/blah?id=query1

Example Requests

/query/57b08e18-df08-415e-b663-94db1d53bd20

Response Body {
  "success": true,
  "gsql_query": {
    "hasSchedule": true,
    "lastRunDate": 0,
    "errElapsed": 0,
    "errRowCount": 0,
    "sql": "Select Distinct cname from Stream",
    "elapsed": 0,
    "uid": "4cd969f3-e4c2-35b0-b96c-b9c7cdff843f",
    "schedule": {
      "dayOfWeekTypes": [
        "SUN",
        "MON",
        "TUE",
        "WED",
        "THUR",
        "FRI",
        "SAT"
      ],
      "hasEndingDate": true,
      "runningUser": {
        "uid": "0e1f2626-db68-371e-bf90-8c62aa77c415",
        "name": "Fred Flintstone"
      },
      "numRepeatTypeIntvls": 1,
      "endingDate": 1656565200000,
      "repeatType": "DAY",
      "monthYearDayType": "DAY",
      "startingDate": 1648648800000
    },
    "name": "Component List",
    "errRunDate": 0,
    "id": "cl_id",
    "rowCount": 0,
    "errText": ""
  },
  "message": ""
}

GET query/new


Returns a default query with a unique UID. Does not persist the new query in the store. It's the callers responsibility to PUT the new query and register it.

The Schedule's runningUser information will default to the caller when a session or OAuth token is used. The startingDate will default to now and the endingDate will default to 3 months from now using the caller's time zone.
Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible Yes

Resource URL

http://grovestreams.com/api/query/new

Parameters

none

Example Requests

/query/new

Response Body {
  "success": true,
  "gsql_query": {
    "hasSchedule": false,
    "lastRunDate": 0,
    "errElapsed": 0,
    "errRowCount": 0,
    "sql": "",
    "elapsed": 0,
    "uid": "4cd969f3-e4c2-35b0-b96c-b9c7cdff843f",
    "schedule": {
      "dayOfWeekTypes": [
        "SUN",
        "MON",
        "TUE",
        "WED",
        "THUR",
        "FRI",
        "SAT"
      ],
      "hasEndingDate": true,
      "runningUser": {
        "uid": "0e1f2626-db68-371e-bf90-8c62aa77c415",
        "name": "Fred Flintstone"
      },
      "numRepeatTypeIntvls": 1,
      "endingDate": 1656565200000,
      "repeatType": "DAY",
      "monthYearDayType": "DAY",
      "startingDate": 1648648800000
    },
    "name": "",
    "errRunDate": 0,
    "id": "",
    "rowCount": 0,
    "errText": ""
  },
  "message": ""
}



PUT query


Inserts a new query.

The uid of the query can be blank. If it is blank a uid will be created on the server and the response body will contain the new uid.
Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible Yes

Resource URL

http://grovestreams.com/api/query

Parameters

parentFolderUid optional
 The UID of the tools content repository folder to place this new query into. The new query will be placed into the root folder if this parameter is missing.

Example Requests

/query

Request Body {
  "gsql_query": {
    "schedule": {
      "runningUser": {
        "uid": "0e1f2626-db68-371e-bf90-8c62aa77c415"
      },
      "repeatType": "DAY",
      "numRepeatTypeIntvls": 1,
      "monthYearDayType": "DAY",
      "startingDate": 1648648800000,
      "endingDate": 1656565200000,
      "hasEndingDate": true,
      "dayOfWeekTypes": "SUN,MON,TUE,WED,THUR,FRI,SAT"
    },
    "uid": "4cd969f3-e4c2-35b0-b96c-b9c7cdff843f",
    "name": "Component List",
    "id": "cl_id",
    "sql": "Select Distinct cname from Stream",
    "hasSchedule": true
  }
}

POST query


Used to update an existing query.
 Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible Yes

Resource URL

http://grovestreams.com/api/query

Parameters

none

Example Requests

/query

Request Body

{
  "gsql_query": {
    "schedule": {
      "runningUser": {
        "uid": "0e1f2626-db68-371e-bf90-8c62aa77c415"
      },
      "repeatType": "DAY",
      "numRepeatTypeIntvls": 1,
      "monthYearDayType": "DAY",
      "startingDate": 1648648800000,
      "endingDate": 1656565200000,
      "hasEndingDate": true,
      "dayOfWeekTypes": "SUN,MON,TUE,WED,THUR,FRI,SAT"
    },
    "uid": "4cd969f3-e4c2-35b0-b96c-b9c7cdff843f",
    "name": "Component List",
    "id": "cl_id",
    "sql": "Select Distinct cname from Stream",
    "hasSchedule": true
  }
}

DELETE query/{queryUid}


Deletes a query and all of its cached results.
 Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible Yes

Resource URL

http://grovestreams.com/api/query/{queryUid}

Parameters

id
 optional
 The id of an existing query to delete. {queryUid} will be ignored if the id parameter is used.

Example Requests

/query/d251a8f2-f7b9-4df7-886d-b24c7f4929d4

Response Body
{
    "message" : "Deleted Query with uid 'd251a8f2-f7b9-4df7-886d-b24c7f4929d4'",
    "success" : true
}



POST query/{queryUid}/run


Runs the query's GS SQL and places the results in the query's cache on the Grove Streams servers. Overwrites any existing results in the cache.

The calling user needs content store "E" (Execute rights) for this call. The GS SQL query will run under the calling user's security rights and their Time Zone unless this Query has a Schedule, then the Schedule's Running User is used. 		Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible No

Resource URL

http://grovestreams.com/api/query/{queryUid}/run

Parameters

id
 optional
 The id of an existing query to run. {queryUid} will be ignored if the id parameter is used. Example: query/blah/run?id=query1
wait optional
 true/false. Defaults to false when missing. The call will run immediately outside the process queue and will return only after the results cache has been refreshed when "wait" is true. The call will place the execution within the organization's process queue and return before the results are updated in the cache.

Example Requests

/query/d251a8f2-f7b9-4df7-886d-b24c7f4929d4/run?wait=true

Request Body
None

Response Body
None


GET query{queryUid}/results


Used to get a set of rows from a query's result cache.

The calling user needs content store R (read) read rights to make this call when the call is using a session or oauth token.
Resource Information
Rate Limited? Yes
session, oauth and org tokens compatible? Yes
api_key token compatible Yes

Resource URL

http://grovestreams.com/api/query/{queryUid}/results

Parameters

id
 optional
 The id of an existing query to run. {queryUid} will be ignored if the id parameter is used. Example: query/blah/results?id=query1
cols Optional true/false. Defaults to false if missing. Includes column headers when set to true.
stats Optional true/false. Defaults to false if missing. Includes run statistics when set to true. Statistics such as the time last run.
error Optional true/false. Defaults to false if missing. Includes the last set of error information from when/if an error occurred during a results refresh when set to true.
start Optional The starting row number to retrieve. Defaults to 1 if missing. 1 is the first row number.
limit Optional The number of rows to return. Returns all of the rows from "start" row to the last row if missing.

Example Requests

query/4cd969f3-e4c2-35b0-b96c-b9c7cdff843f/results?stats&cols&error&start=1&limit=10000

Request Response

{
  "stats": {
    "elapsed": 175,
    "lastRunDate": 1648650040973,
    "rowCount": 10
  },
  "rows": [
    [
      "r1p2"
    ],
    [
      "r2p3"
    ],
    [
      "Personal Computer"
    ],
    [
      "r1p1"
    ],
    [
      "Currency Exchange Rates"
    ],
    [
      "Jobs"
    ],
    [
      "PC Aggregate CPU"
    ],
    [
      "Refrigerator Smart Plug Sensor"
    ],
    [
      "Garage Temperature and Light Sensor"
    ],
    [
      "r2p4"
    ]
  ],
  "error": {
    "errRunDate": 0,
    "errElapsed": 0,
    "errRowCount": 0,
    "errText": ""
  },
  "cols": [
    {
      "valueType": "STRING",
      "name": "cname"
    }
  ]
}