As many of you may know, I am a big fan of the undocumented REST API endpoints. I do have my first dive into them in my previous post Access Script Activities via API.
For this post, I want to highlight the Undocumented API endpoints for Query Activities. I do want to point out that these undocumented endpoints are not officially supported by SFMC, meaning you will get no support from SFMC on them and they are ‘use at your own risk’.
So I don’t bury the lead, you may want to scroll down and check out the Validate endpoint for a neat thing.
Retrieve all Queries
GET /automation/v1/queries/
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/jsonThis will return all queries inside of your account. You can place specificity on this either:
- via a filter
?$filter=Name%20like%20'ContactUpdateTest' - or by adding the Query Definition ID to the end of the URL
/automation/v1/queries/{{queryDefinitionId}}
Sample Return (for Bulk/Filtered)
{
"count": 1,
"page": 1,
"pageSize": 25,
"items": [
{
"queryDefinitionId": "xxxx-xxxx-xxxx-xxxx",
"name": "RestAPI",
"key": "RestAPI",
"description": "",
"queryText": "{{query content}}",
"targetName": "00_openTest",
"targetKey": "xxxx-xxxx-xxxx-xxxx",
"targetId": "xxxx-xxxx-xxxx-xxxx",
"targetDescription": "{{Description}}",
"createdDate": "2019-03-14T11:40:55.18",
"modifiedDate": "2019-03-14T11:51:14.453",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"categoryId": 13760,
"isFrozen": false
}
]
}Another cool thing to note is that if you use the queryDefinitionId to specify a specific query, you get an extra property returned validatedQueryText which appears to be the final SQL that SFMC creates from your SQL and runs on their server.
{
"queryDefinitionId": "xxxx-xxxx-xxxx-xxxx",
"name": "REST_API",
"key": "xxxx-xxxx-xxxx-xxxx",
"description": "",
"queryText": " SELECT\n \n SubscriberKey, \n COUNT(SubscriberKey) as TotalSent FROM _Sent\n WHERE EventDate BETWEEN DATEADD(month, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP\n GROUP BY SubscriberKey\n \n HAVING COUNT (SubscriberKey) > 1",
"targetName": "REST_API",
"targetKey": "REST_API",
"targetId": "xxxx-xxxx-xxxx-xxxx",
"targetDescription": "",
"createdDate": "2019-04-22T06:37:30.923",
"modifiedDate": "2019-04-22T06:43:45.993",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nINSERT INTO C7203368.[GG_HavingSQLTest] ([SubscriberKey], [TotalSent])\r\nSELECT querydef.[SubscriberKey], querydef.[TotalSent]\r\nFROM ( SELECT SubscriberKey, COUNT(SubscriberKey) as TotalSent FROM C7203368._Sent WHERE EventDate BETWEEN DATEADD(month, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP GROUP BY SubscriberKey HAVING COUNT (SubscriberKey) > 1) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
"categoryId": xxxxxx,
"isFrozen": false
}For the super nerd, this is pretty darn cool! It won’t really help much, but it can give some good insight into what SFMC does to our SELECT Queries and how we can better optimize to fit inside that context.
Retrieve Queries by Category ID (Folder)
GET /automation/v1/queries/category/{{categoryId}}
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/jsonThis will return a response identical to the general or filtered Retrieve above. This can be useful for grabbing specific queries if they do not have a common naming theme or similar to be able to filter them all into a single call.
Check if a Query is Running
This may feel like a weird thing to have as an endpoint, but it opens up the possibility to verify that a query is not already running before starting it, allows you to gather run time by monitoring how long it is ‘isRunning’, etc.
GET /automation/v1/queries/{{queryID}}/actions/isrunning/
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/jsonSample Return
{
"queryDefinitionId": "{{queryID}}",
"isRunning": false
}Run/Start a Query Activity
This endpoint is super helpful for creating outside or ‘mini’ automations via scripts instead of Automation Studio. The Return is very basic with just “OK” or “Error”.
POST /automation/v1/queries/{{queryID}}/actions/start/
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/jsonSample Return
"OK"
Validates SQL for Query Activity
This is great for those that do not want to utilize the UI to make their queries and do not want to have multiple failures on their create API calls. The errors section below will provide you with error messages much more helpful than what you get from the UI when SFMC finds validation errors in your SQL.
For instance if you accidently type nConvert(...) instead of Convert(...) in your query, the UI will say: Error while parsing query. Please check the syntax
where as this will return: "An error occurred while checking the query syntax. Errors: 'nCONVERT' is not a recognized built-in function name.\r\n" .
FRICKIN COOL!
POST /automation/v1/queries/actions/validate/
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/json
{
"Text": "{{query content}}"
}Sample Return:
{
"queryValid": true,
"errors": []
}Create A new Query Activity
This will bypass the need to create via the UI and allow you to programatically build queries. It also can be used for ‘one-off’ builds outside the UI, for instance, let’s say your account has over 5000 DEs and it takes like 5 minutes for the Automation Studio Query Builder wizard to collect this info for you to select one. Using this instead will be seconds instead of minutes.
POST /automation/v1/queries
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/json
{
"name": "REST_API",
"key": "REST_API",
"description": "",
"queryText": "{{query content}}",
"targetName": "00_openTest",
"targetKey": "7BA84828-64F6-48DC-9213-E299FEA1AC89",
"targetId": "abfaf821-8046-e911-a2d9-1402ec939129",
"targetDescription": "Created via REST API",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"categoryId": 12345
}Sample Response
{
"queryDefinitionId": "1766acc7-b476-414b-86d6-eed8f5878b27",
"name": "REST_API",
"key": "REST_API",
"description": "",
"queryText": "{{query content}}",
"targetName": "00_openTest",
"targetKey": "7BA84828-64F6-48DC-9213-E299FEA1AC89",
"targetId": "abfaf821-8046-e911-a2d9-1402ec939129",
"targetDescription": "Open Data View - Do Not Use - Only Copy",
"createdDate": "2019-05-30T13:39:29.46",
"modifiedDate": "2019-05-30T13:39:29.46",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"validatedQueryText": "{{validated query content}}",
"categoryId": 12345,
"isFrozen": false
}Please do note that this also has the validatedQueryText field like I mentioned in the specified GET. This is a neat little bonus.
Update an Existing Query
I view this same as the creation endpoint. If you want to programatically make a change to multiple queries, this is your endpoint. OR if you don’t want to wait for the UI to be able to load and do its thing via the wizard, you can just shoot the API call over and get it done in seconds.
PATCH /automation/v1/queries/{{queryDefinitionId}}
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/json
{
"queryDefinitionId": "{{queryDefinitionId}}",
"name": "Rest_API",
"key": "xxxxxx-xxxx-xxxx-xxxx-xxxxx",
"description": "Rest_API",
"queryText": " SELECT\n \n SubscriberKey, \n COUNT(SubscriberKey) as TotalSent FROM _Sent\n WHERE EventDate BETWEEN DATEADD(month, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP\n GROUP BY SubscriberKey\n \n HAVING COUNT (SubscriberKey) > 1",
"targetName": "DEName",
"targetKey": "DEKey",
"targetId": "xxxxxx-xxxx-xxxx-xxxx-xxxxx",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"categoryId": 123456
}
Sample Response
{
"queryDefinitionId": "xxxx-xxxx-xxxx-xxxx",
"name": "REST_API",
"key": "xxxx-xxxx-xxxx-xxxx",
"description": "",
"queryText": " SELECT\n \n SubscriberKey, \n COUNT(SubscriberKey) as TotalSent FROM _Sent\n WHERE EventDate BETWEEN DATEADD(month, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP\n GROUP BY SubscriberKey\n \n HAVING COUNT (SubscriberKey) > 1",
"targetName": "REST_API",
"targetKey": "REST_API",
"targetId": "xxxx-xxxx-xxxx-xxxx",
"targetDescription": "",
"createdDate": "2019-04-22T06:37:30.923",
"modifiedDate": "2019-04-22T06:43:45.993",
"targetUpdateTypeId": 0,
"targetUpdateTypeName": "Overwrite",
"validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nINSERT INTO C7203368.[GG_HavingSQLTest] ([SubscriberKey], [TotalSent])\r\nSELECT querydef.[SubscriberKey], querydef.[TotalSent]\r\nFROM ( SELECT SubscriberKey, COUNT(SubscriberKey) as TotalSent FROM C7203368._Sent WHERE EventDate BETWEEN DATEADD(month, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP GROUP BY SubscriberKey HAVING COUNT (SubscriberKey) > 1) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
"categoryId": xxxxxx,
"isFrozen": false
}Delete an Existing Query
We have been able to create, edit, and retrieve, but what about being able to programatically delete Queries? Well! Good news is that there IS an endpoint for that.
DELETE /automation/v1/queries/{{queryDefinitionId}}
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/jsonSample Response
"OK"
As you can tell, the response is very verbose and detailed. It would be hard to find anything they missed that you might want to collect….but in all seriousness, this response of essentially pass/fail should be enough for the majority of use-cases.
Validate a Specific Query
This next one I stuck at the end because I really and truly do not understand what exactly is different on this one than in the above validation endpoint other than this one requires a query definition ID inside the url. You would think, oh since its got the ID, I can just validate it based on that and not need to put the query text in…well you are wrong! This is exact same format and exact same results as above…just a more complex endpoint. Maybe there is some feature that I have not dug up yet…but if there is, it is pretty well hidden.
POST /automation/v1/queries/{{queryDefinitionID}}/actions/validate/
Host: {{yourendpoint}}.rest.marketingcloudapis.com
Authorization: Bearer {{Oauth Key}}
Content-Type: application/json
{
"Text": "{{query content}}"
}Sample Response
{
"queryValid": true,
"errors": []
}Well! That is everything as far as Undocumented Query Endpoints. Hopefully this is helpful to someone. I will be working on sharing more of what I have discovered of REST undocumented endpoints in another blog post at some point in the near future. Let me know if you have anything in specific you want me to take a look at or investigate.
Hi! I’m getting “The following field is required: ‘targetUpdateTypeId” when I try and use ” “targetUpdateTypeName”: “Append”
Do you happen to know what the “targetUpdateTypeId” value would be for Append and Overwrite?
Here are the values for targetUpdateTypeId for possible data actions for query activities.
targetUpdateTypeId: 0 -> Overwrite
targetUpdateTypeId: 1 -> Update
targetUpdateTypeId: 2 -> Append
Hope this helps!
These seem to work in the top-level BU, but I get a 404 error in other BUs. Is there a change that needs to be made for non-top-level?