Execute
The execute API provides the ability to execute queries, procedures and multiple DSL statements.
POST/execute/query
This API is used to execute a SQL query against the TDV server.
Parameters:
None
Request Body
Example Value - Schema
{"standardSQL": true,
“query”: “string”,
“skipRows”: 0,
“maxRows”: 0
“dataServiceName”: “string”
“blocking”: true
}
standardSQL - The default value is true. Set this to false for performing a data preview. The query must be a composite query (non-standard).
isBlocking - The default value is true. Set this to false to execute query in an asynchronous fashion.
skipRows - If this value is set, then that many number of rows will be skipped in the execution output before returning any results. If 'skipRows' is greater than the total possible number of rows, then no rows will be returned. The default value is 0.
maxRows - If this value is set, then the result will contain at most 'maxRows' number of rows. If 'maxRows' is fewer than the total number of rows of data available, then additional calls to "getNextBatch" will need to be made to get the rest of the available data. If 'maxRows' is not set, then the result row count is request memory-bound.
Note: skipRows and maxRows are used for a blocking query execution.
Following are the status codes that are returned:
• 206 (Partial Content) - This exception occurs when partial results are obtained due to memory constraints. The result is set in the entity.
• 409 (Conflict) - This exception occurs when a request is canceled before being consumed completely.
• 406 (Not Acceptable) - This exception occurs when the input request id represents a blocking request.
• 500 (Internal Server Error) or 400 (Bad Request) - This occurs when a request fails.
• 200 (OK) - When the maxRows is set and is greater than the number of available rows in the result, then all the available rows are returned with this status code.
Example to execute a query to get a property of a sql script procedure.
curl -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d "{\"query\":\"SELECT * FROM model.ALL_RESOURCE_PROPERTIES WHERE property_name = 'script' AND metadata_id = (SELECT PROCEDURE_ID FROM model.ALL_PROCEDURES WHERE PROCEDURE_NAME = 'ctasScript' AND parent_path = '/shared/examples')\", \"standardSQL\":true}"
Example to search for “product”
curl -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d '{\"standardSQL\":false, \"query\":\"select * from /lib/resource/\"Search\"('product',null,null,null,null,null,null,null)\", \"blocking\":false}'
Example to search for “product”, filter “column” and “parameter” field types and filter resource types “table” and “procedure”'
curl -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d '{\"standardSQL\":false, \"query\":\"select * from /lib/resource/\"Search\"('product','column,parameter','table,procedure',null,null,null,null,null)\", \"blocking\":false}'
Example to search for “product”, filter “annotation” field type and mark start of search as { and stop of search as }
curl -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d '{\"standardSQL\":false, \"query\":\"select * from /lib/resource/\"Search\"('product','annotation',null,'StartSel:<,StopSel:>',null,null,null,null)\", \"blocking\":false}'
Example to search for “product” and report data flows
curl -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d '{\"standardSQL\":false, \"query\":\"select * from /lib/resource/\"Search\"('product',null,null,null,1,null,null,null)\", \"blocking\":false}'
Example to search for “product”, assign fieldRanks for “column” and “parameter” fields, set rowOffset to 10 to skip 10 rows in the result,set rowLimit to 30 rows in the result.
curl -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json" -d '{\"standardSQL\":false, \"query\":\"select * from /lib/resource/\"Search\"('product',null,null,null,null,'column:3,parameter:3',10,30)\", \"blocking\":false}'
POST/execute/procedure
This API is used to execute procedure specified by path / type.
Parameters:
None
Request Body
Example Value - Schema
[
"includeMetadata": true,
“parameterBeanList”: [
{
“definition”: “string”,
“value”: “string”
}
],
“path”: “string”,
“type”: “string”,
“blocking”: true
]
Example to execute procedure "/shared/examples/LookupProduct"
curl -X POST -u admin:admin "http://localhost:9400/rest/execute/v1/actions/procedure/invoke" -H "Content-Type:application/json" -d "{\"blocking\":\"true\",\"includeMetadata\":\"true\", \"parameterBeanList\":[{\"definition\":\"INTEGER\", \"value\":\"1\"}],\"path\":\"/shared/examples/LookupProduct\", \"type\":\"PROCEDURE\"}"
POST/execute/cancel
This API is used to cancel asynchronous/synchronous sql request with the specified execution ids. Procedure requests are unsupported for cancellation.h
Parameters:
None
Request Body
Example Value - Schema
[
"string"
]
Example to cancel a request
curl -X POST "http://localhost:9400/rest/execute/v1/actions/cancel/invoke" -H "Content-Type: application/json" -d "[\"400014-0\"]"
GET/execute/nextBatch
This API is used to get next batch of data. Only Asynchronous sql request execution ids may be specified. When batch size is unspecified, it is defaulted to the server configuration 'defaultFetchRows'. The various status codes returned are:
• 206 (Partial Content) - This exception occurs when partial results are obtained due to memory constraints. The result is set in the entity.
• 409 (Conflict) - This exception occurs when a request is canceled before being consumed completely.
• 406 (Not Acceptable) - This exception occurs when the input request id represents a blocking request.
• 500 (Internal Server Error) or 400 (Bad Request) - This occurs when a request fails.
• 200 (OK) - This status code indicates that there has been no errors. Following are some scenarios:
— When the batchSize is greater than the number of available rows in the result, then all the available rows are returned with this status code.
— When the result is completely consumed, an empty result set is returned with this status code.
— When no request is found for the specified request id, an empty result set is returned with this status code.
Parameters:
Name | Description | Parameter Type |
executionId | Execution ID | string |
batchSize | Batch size to fetch. When 'batchSize' is not set, the value set in server configuration 'defaultFetchRows' is used. | integer |
Example to get next batch of data
curl -X GET -u admin:admin "http://localhost:9400/rest/execute/v1/actions/nextBatch/invoke?executionId=400014-0&batchSize=5" -H "Content-Type:application/json"
POST/execute/DSL
This API is used to execute the specified list of DSL statements.
Parameters:
None
Request Body
Example Value - Schema
[
"string"
]
Example to Drop a Sql script procedure and create one using two DSL statements.
curl -d "[\"CREATE SCRIPT /shared/examples/ctasScript DEFINE AS PROCEDURE ctasScript() BEGIN CREATE TABLE /shared/examples/ds_inventory/tutorial/sampleTable as select OrderId, ProductID, Discount, OrderDate, CompanyName, CustomerContactFirstName, CustomerContactLastName, CustomerContactPhone FROM /shared/examples/ViewOrder; END\",\"DROP SCRIPT /shared/examples/ctasScript\"]" -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
POST/execute/sqlscript
This API is used to execute procedure specified by scriptText.
Parameters:
None
Request Body
Example Value - Schema
[
"includeMetadata": true,
“parameterBeanList”: [
{
“definition”: “string”,
“value”: “string”
}
],
“scriptText”: “string”,
“blocking”: true
]
Example to execute procedure using script text
curl -X POST -u admin:admin "http://localhost:9400/rest/execute/v1/actions/sqlscript/invoke" -H "Content-Type:application/json" -d "{\"blocking\":\"true\",\"includeMetadata\":\"true\", \"parameterBeanList\":[{\"definition\":\"INTEGER\", \"value\":\"1\"}],\"scriptText\":\"PROCEDURE LookupProduct(IN desiredProduct INTEGER,OUT result CURSOR (ProductName VARCHAR(50),ProductID INTEGER,ProductDescription VARCHAR(255))) BEGIN OPEN result FOR SELECT products.ProductName, products.ProductID, products.ProductDescription FROM /shared/examples/ds_inventory/tutorial/products products WHERE products.ProductID = desiredProduct; END\"}"