Overview

Google BigQuery provides a list of REST APIs that perform many functions on projects, datasets, tables, and jobs. These resource types can be deleted, inserted, listed, patched, and obtained. Performing actions on APIs is an easy way to quickly perform desired functions. Also other Google APIs are available, like Cloud Storage API, Prediction API and many others

Magnus provides easy access to any REST APIs (not just Google API but any REST API, for example AWS API or Unison API, etc.) through the API Call Task.

For example, using the API Call Task - tables can be listed, evaluated, and then deleted, table schemas can be updated, datasets can be created, and many other actions can be performed. The only limitation is what is available through the APIs.

APIs offer a set of protocols that help provide a solution for performing tasks. Being able to access APIs, especially Google API, opens a powerful tool for use. Magnus features API Task to provide this ability.

API Task provides URL submission for HTTP requests, a variety of request methods (including GET, POST, and DELETE), and assignment of parameters for response headers and body

Front Panel

Flip Panel

Add API Task

Under Add Task, select the desired position from the "Add to position" drop down list, then click +API Call.

Request URL/Method
Request URL: This is the request URL of the API call. Parameters can be used.
Request Method: This is the request method of the API call. Supported methods are: GET, POST, DELETE, PUT, HEAD, and TRACE. If Auto is selected from the drop down list, the API call will be made via method POST if there is a Request body, otherwise, it does a GET.

Content Type
Content Type: This is the content type of the request body. If this is left empty, the default content type "application/json; charset=utf-8" will be used. Parameters can be used.

Request Body/Headers
Request Body: This is the request body of the API call. Parameters can be used. The textbox can be expanded to a popup mode by clicking the icon . User can select from a predefined set of templates for the Request Body by clicking the icon . User cal also click on the API Wizard button to explore more Google APIs and build the request.
Response Headers: To capture the response headers from the API call, select a custom parameter of type record from the drop down list.
The selected custom parameter will become workflow-owned.
Response Body: To capture the response body from the API call, select a custom parameter of string type from the drop down list.
The selected custom parameter will become workflow-owned.

Advanced Panel
Advanced panel: Click the icon to show advanced properties.
1. By default, user's Google authentication token is included in the request header when the API is called. To disable this feature, uncheck the checkbox:
2. User can include additional headers when the API is called. Additional headers should be new line delimited, each line of the format: header: value
  
  Parameters can be used.
3. User can choose to collect BQ Jobs, Magnus Workflows, or items with custom JSON path from the response body. User can then use Hub Task to wait for the collected BQ Jobs or Magnus Workflows to complete. See Hub Task for more details.
  
  Select the type of item to collect:
  
  Specify the parameter to add the item to:
  
  By default, the new item will be appended to the specified parameter. To overwrite the parameter instead, check Overwrite List:
  
  Important:
  
  If user chooses to append to the specified parameter and not overwrite, care must be taken to make sure that the parameter is cleared before first use. Otherwise, since this parameter is Workflow-owned, its state is preserved through Workflow executions. Thus, if user does not clear the parameter before first use, new items will be appended accumulatively to the parameter for each Workflow execution.
  
  Here are some suggestions on how to clear the parameter before first use:
  
  #a. In the Workflow, identify the first API Task with item collection, and check the checkbox Overwrite List. This ensures that the parameter is cleared and overwritten for the first use. Then for subsequent API Task Tasks with item collection, leave the checkbox Overwrite List unchecked, so that new items will be appended to the parameter.
  #b. At the beginning of the Workflow, add a Script Task to clear the parameter. This is a Script Task that returns an empty string and its Return Parameter is set to the parameter to clear.
  #c. At the end of the Workflow, add a Script Task to clear the parameter. This is a Script Task that returns an empty string and its Return Parameter is set to the parameter to clear.

Collect Items using Custom JSON Path

JSON path can use the dot-notation
$.store.book[0].title

or the bracket-notation
$['store']['book'][0]['title']  

Operators

Operator	Description
$	The root element to query. This starts all path expressions.
@	The current node being processed by a filter predicate.
*	Wildcard. Available anywhere a name or numeric are required.
..	Deep scan. Available anywhere a name is required.
.<name>	Dot-notated child.
['<name>' (, '<name>')]	Bracket-notated child or children
[<number> (, <number>)]	Array index or indexes
[start:end]	Array slice operator
[?(<expression>)]	Filter expression. Expression must evaluate to a boolean value.

Functions

Functions can be invoked at the tail end of a path - the input to a function is the output of the path expression. The function output is dictated by the function itself.

Function	Description	Output
min()	Provides the min value of an array of numbers	Double
max()	Provides the max value of an array of numbers	Double
avg()	Provides the average value of an array of numbers	Double
stddev()	Provides the standard deviation value of an array of numbers	Double
length()	Provides the length of an array Integer	Integer

Filter Operators

Filters are logical expressions used to filter arrays. A typical filter would be [?(@.age > 18)] where @ represents the current item being processed. More complex filters can be created with logical operators && and ||. String literals must be enclosed by single or double quotes ([?(@.color == 'blue')] or [?(@.color == "blue")]).

Operator	Description
==	left is equal to right (note that 1 is not equal to '1')
!=	left is not equal to right
<	left is less than right
<=	left is less or equal to right
>	left is greater than right
>=	left is greater than or equal to right
=~	left matches regular expression [?(@.name =~ /foo.*?/i)]
in	left exists in right [?(@.size in ['S', 'M'])]
nin	left does not exists in right
subsetof	left is a subset of right [?(@.sizes subsetof ['S', 'M', 'L'])]
size	size of left (array or string) should match right
empty	left (array or string) should be empty

Path Examples

Given the json:

{

"store": {

"book": [

{

"category": "reference",

"author": "Nigel Rees",

"title": "Sayings of the Century",

"price": 8.95

{

"category": "fiction",

"author": "Evelyn Waugh",

"title": "Sword of Honour",

"price": 12.99

{

"category": "fiction",

"author": "Herman Melville",

"title": "Moby Dick",

"isbn": "0-553-21311-3",

"price": 8.99

{

"category": "fiction",

"author": "J. R. R. Tolkien",

"title": "The Lord of the Rings",

"isbn": "0-395-19395-8",

"price": 22.99

}

"bicycle": {

"color": "red",

"price": 19.95

}

"expensive": 10

}

JSON Path	Result
$.store.book[*].author	The authors of all books
$..author	All authors
$.store.*	All things, both books and bicycles
$.store..price	The price of everything
$..book[2]	The third book
$..book[-2]	The second to last book
$..book[0,1]	The first two books
$..book[:2]	All books from index 0 (inclusive) until index 2 (exclusive)
$..book[1:2]	All books from index 1 (inclusive) until index 2 (exclusive)
$..book[-2:]	Last two books
$..book[2:]	Book number two from tail
$..book[?(@.isbn)]	All books with an ISBN number
$.store.book[?(@.price < 10)]	All books in store cheaper than 10
$..book[?(@.price <= $['expensive'])]	All books in store that are not "expensive"
$..book[?(@.author =~ /.*REES/i)]	All books matching regex (ignore case)
$..*	Give me every thing
$..book.length()	The number of books

API Wizard

User can click on the API Wizard button to explore more Google APIs and build the request.
The API Wizard by default shows only qualified Google APIs, which are APIs that are within granted scopes. For example, if user has already previously granted bigquery OAuth scope to Potens application, then BigQuery API will show up in the API drop down.
If user wants to explore all Google APIs, regardless if the APIs are qualified or not, simply uncheck the checkbox:

When the workflow runs, the API Task will fail if the Google API is not qualified.
To get to the API method, simply select the API, resource, then method from the dropdown lists.
User can then build the request body:
When ready, click the button Done to copy request URL, method, and body back to the API Task in the workflow.

Retry Policy

When Magnus makes the API call on behalf of the user to the specified Request URL, Magnus considers the returned HTTP code 2xx as successful. Magnus will retry for 10 times with 30 seconds of sleep in between for these HTTP error code:

401: Unauthorized error
5xx: Backend error

Magnus does not retry on any other HTTP error code.

Limitations

The max response body allowed is 32 MB. If the API Task receives a response body greater than 32 MB, the Task will fail.
The collected items list cannot exceed 5 million characters in length.

FAQ

Can I send the API request via the method PATCH?Answer: Yes, only if the API accepts X-HTTP-Method-Override. For example, Google BigQuery and Storage APIs accept X-HTTP-Method-Override. Steps:
1. Select POST as the Request Method
2. Advanced panel: Click the icon to show advanced properties.
3. Add X-HTTP-Method-Override: PATCH to the Request Headers.

Examples

API Task allows user to make API calls to Google Big Query and Google Cloud Storage.

Screen Elements:

The API supports HTTP GET and POST operations. If Request Body is empty, it's a GET, else it's a POST.
Request URL: This is the URL of the http request to the API. This field supports the use of parameters
Request Body: This contains the request body in json format for a POST operation. This field supports the user of parameters
Response Parameter: The user may create a parameter to hold the HTTP Response
API Explorer: Shows links to investigate Google Big Query and Google Cloud Storage API
Example:
Here is a typical way to create an API Task:
Say we want to create an API call to dump the content of a csv file on GCS into a big query table. We will do the following:

Navigate to API Explorer and click on BigQuery API. For this example, we want to pick bigquery.jobs.insert
Authorize (click on authorize request button and allow)
Build a working request and make sure it works (see example below)
Copy the request url (first line, starting after POST) and paste into API Task Request URL
Copy the request body (after first line, before the red Response) and paste into API Task Request Body
Make adjustments such as replacing strings with parameters, etc. if necessary
Done!

Example output from step 3 for a successful operation:

Articles > API Call Task

Overview

Add API Task

Request URL/Method

Content Type

Request Body/Headers

Advanced Panel

Collect Items using Custom JSON Path

Operators

Functions

Filter Operators

Path Examples

API Wizard

Retry Policy

Limitations

FAQ

Examples