Magnus is a Self-Serve Tool that enables Google BigQuery users to Orchestrate and Schedule their Tasks via Workflows.
Users can put together a Workflow quickly with great flexibility, without the skills-set of a programmer, and without much learning curve.
Magnus allows collaborations between users, sharing of business logic, and re-usability.
Users have great visibility into workflows execution History as well as ability to set configurable notification about both run-time and design-time of Workflow.
Some Concepts
Workflows are made up of Tasks
There are dozen of different Task Types available.
Tasks are executed in the order they appear in Workflow.
GoTo and Loop Tasks can control/change execution order.
Execute Workflow Task enables a Workflow to run another Workflow.
There are Parameters in Workflows
Built-In and Custom Parameters provide great level of flexibility for user to parameterize Workflow
Magic Parameters are generated on fly for user providing contextual parameters to be used
Parameters are evaluated at a run-time in one round.
Special setting allows enabling double-evaluation - parameters referencing another parameters
Any textual input across Workflow and Tasks can be parameterized.
User who creates the workflow is the owner of the workflow
Owner can add Delegates and users to Share With this workflow.
Delegates of a Workflow can edit and run the workflows
Delegate can be added by Owner or existing Delegate.
Delegate can be removed by Owner or another Delegate.
Workflow can be shared
Owner can share with others.
Shared users cannot edit workflow.
Workflow can be Scheduled
Only one schedule is allowed in current version.
User can Disable or Enable Schedule any time.
Workflow's Lifetime controls when Schedule is in effect.
Note: Workflow can be run Manually or via Execute Workflow Task even if it is Disabled and/or not Scheduled and/or Lifetime is out.
Billing Project
Each Magnus user has one billing project assigned. This is the BQ project billed when BQ jobs are submitted to Google during Workflow executions.
By default, visibility of Public Workflows is limited to user's billing project.
That means, given a Public Workflow that the user is not owner/delegate/shared with, this Public Workflow can be viewed by the user if and only if the Workflow Owner belongs to the same billing project as the user.
Moreover, when a user views Public Workflows either from Workflows Browser or Workflows History Browser, this user can only view those Public Workflows from Owners of the same billing project as the user.
For example, if user U belongs to billing project P, when viewing Public Workflows from Workflows Browser, user U can only see those Public Workflows from Owners of billing project P.
Viewable Project
Viewable Project extends a user's visibility of Public Workflows to projects different from the user's billing project.
Magnus user can be granted view permission in BQ projects that are different from his/her billing project. This gives user visibility into Public Workflows from owners of other billing projects.
For example, if user U is granted view permissions to projects X, Y, and Z, when viewing Public Workflows from Workflows Browser, not only user U can see the Public Workflows from his/her own billing project, he/she can also see Public Workflows from the viewable projects X, Y, and Z.
More Details available in below charts
Workflow Header
| Item / Element | Type | Description | Rules |
| Workflow ID | Input | Workflow ID/Name |
Letters, numbers, or underscores starting with letter - up to 50 chars Must be Unique across ALL workflows |
| Description | Input | Workflow Description | Free text |
| Owner | Input |
Workflow Owner Can Edit, Save, Run, See History, Copy, Archive, Add/Remove Delegates and manage Share With list When Workflow is executed by Scheduler – it is run under Owner’s account |
Usually Creator. Can be changed by Admin up to 100 chars |
| Delegates | Input |
Workflow Delegates Can Edit, Save, Run, See History, Copy, Archive, Add/Remove Delegates and manage Share With list When run via Manual Run or Execute Workflow Task – workflow is run under Delegate’s account (unless “always run by owner” flag is set) |
Delegate can be added or removed by Owner or other Delegate(s) List is Comma separated |
| Share With List | Input |
Users which this workflow shared with These users can View Only (Workflow and History) and Run via Execute Workflow Task Only! They cannot Edit, Save, Copy, Run via Manual Run! When Run by such User – workflow is run under User’s account (unless “always run by owner” flag is set) |
Shared With User can be either User or Domain. List is Comma separated Only Owner or Delegate can Manage Share With List To share with domain – Owner/Delegate must be whitelisted |
| Always Run by Owner | Icon | If set, workflow is always run under owner account even if run by delegate (via manual run or Execute Workflow Task) or by user from shared with list (via Execute Workflow Task) | Set by Admin Only |
| Lifetime | Gadget |
Start and End Date as well as Set Switch If Scheduled and Enabled – sets time frame for when those properties to be considered in effect |
Usually used to set Time Frame for schedule to be in effect (still requires Enabled property to be set) |
| Schedule | Gadget | Workflow Schedule. Quick Selection (multiple preset choices) or Advance Mode (via Quartz Cron) | |
| Time Zone | Selector | Workflow time zone. This affects when the Workflow is run and time-related built-in parameters. | |
| Billing Project From | Gadget | Run-User: use the Magnus-assigned billing project of the user who is running the Workflow. Owner: use the Magnus-assigned billing project of the Workflow Owner. Custom: Specify a billing project manually or using Selector ... |
Parameter is not allowed |
| Enabled | Checkbox |
Used for quick or temporary Enabling / Disabling Workflow’s Schedule. Does not affect ability to be run via Manual Run or via Execute Workflow Task |
|
| Public | Checkbox |
If set – Magnus users who have the same billing project as the Workflow Owner, or who have visibility to the billing project of the Workflow Owner, can see this workflow in Workflows Browser and History Browser. They can View Workflow and its History Details |
|
| Save | Button |
Validate and Save Workflow If critical issues are found – save will fail If non-critical issues are found - User will be notified and asked before proceeding |
|
| Exit | Button | Exit Workflow without Saving | |
| Run | Button | Manual Run. Any changes are Saved before run | |
| Show / Hide Disabled Tasks | Icon |
Show / Hide Switch. If Workflow has some Tasks disabled – they can be hidden and then shown again |
|
| Collapse / Expand Tasks | By Clicking on Workflow Header Title – User can collapse / Expand all Workflows Tasks |
Workflow Footer
| Item / Element | Type | Description | Rules |
| Insert Task | Buttons | Click on respective button will insert respective Task into respective Position | |
| Insert Position | Selector | Allows to choose position to insert new Task into | Auto-populated with existing Tasks and their Positions |
| Save | Button | Same as in Workflow Header | |
| Exit | Button | Same as in Workflow Header | |
| Run | Button | Same as in Workflow Header | |
| Copy | Button | Allows to make a Copy of Workflow |
Copy of Workflow is Created for user to Edit and Save User must change Workflow Name(ID) By Default “_COPY” is added to the end of name User can change Workflow Owner before copy saved first time |
Task Types
| Type / Description | Main Features / Elements |
|
Task Container / Panel All Tasks are hosted by generic Task Container with common features Each Task has
Most of Text Input based elements accept parameters |
Enable / Disable Ignore Failure Optional Flip Side for more/advanced options Move Up / Down Close / Delete Task Collapse / Expand Task Panel |
|
BigQuery Task Executes BigQuery with most of available properties set by user Waits for respective job completion before passing control to next Task
|
Parametrized Query Destination: Table, View, Parameter Write / Destination Preferences Legacy / Standard SQL Inline or URI based UDF Max Billing Tier / Bytes Priority Retention Sandbox Mode – Test/Validate with Parameters Substitution |
|
Export to Cloud Storage Task Export BigQuery Table to Cloud Storage |
Export Format: CSV/JSON/Avro Compression: None/GZIP Field Delimiter Include Header |
|
API Call Task Call any REST API – mostly designed to run Google API, but any can go! Response Header and Response Body can be assigned to Output Parameters for further processing/analyzing (in Script Task for example) Response Header requires Record Type Parameter Response Body requires String Type Parameter |
Request URL Request Method Content Type Request Headers Request Body Response Header Response Body Google API Specific Presets
|
|
Script Task Executes Script of your choice (JavaScript, Go, Lua) Any number of parameters can be passed (by value) into the Script Script Return will be assigned to Return Parameter |
Supported Languages:
|
|
FTP Export (Out) Task Transfer Content of BigQuery Table to FTP Destination Three-step process:
|
FTP Protocol: SFTP, FTP, FTPS Username/Password required Export Format: CSV/JSON/Avro Compression: None/GZIP Field Delimiter Include Header
 User should be in whitelist to use this task |
|
FTP to GCS (In) Task Allows FTP File(s) from FTP URI to Cloud Storage One or multiple (with star wildcard) file(s) or whole folder can be transferred Original or new file name (if one file) Comma separated list of transferred files will be assigned to Parameter |
FTP Protocol: SFTP, FTP, FTPS Username/Password required
|
|
Execute Workflow Task Executes another [Target] Workflow as a part of current [Caller] Workflow User can choose from Owned, Delegated, Shared or All available for given user Workflows All custom parameters from Target Workflow are presented in Advanced Properties for user to set By Default (inherit-var) – values in Target Workflow will be used User can also pass (by value) specific value to Target Workflow either by setting value manually (set-value) or by assigning any Caller’s parameter (choose from list)
|
Wait If Running Option in Non-Shared Mode Target Parameters as Arguments (by value) Non-Shared vs Shared Mode
|
|
Email Task Send Email with optional Attachment All elements accept parameters Email is sent on behalf of Magnus with Reply To – owner of workflow account |
Subject; To/Cc/Bcc Body: Plain Text or HTML Attachment: File from Cloud Storage or On Fly Text or HTML
|
|
Loop Task Run set of Tasks in the Loop Loop is executed over the collection of rows (BigQuery Table) For each row iteration is executed Within each iteration row’s values are assigned to respective fields of Iteration Parameter which is required must be of Record Type Those fields are named after respective table’s fields and can be accessed via 1-based index or via field name and used in any task within Loop Loop Task Has its Own Footer with mini Add-Task Console Positions here are presented local to Loop Note: Tasks still can be moved Up and Down / Inside and Outside Loop Task Expanding/Collapsing Tasks from Workflow Header will respectively expand/collapse whole Loop Task with all inner Tasks Expanding/Collapsing Loop Task itself controls expanded/collapsed state of inner Tasks only |
Loop Type: For Each Iteration Data: BigQuery Table Loop Specific Failure handling: 1. Ignore Failure, Exit Loop OR 2. Skip to Next Iteration
|
|
Go-To Task Routes Workflow Flow based on Condition Respective Tasks are set to go for Condition evaluated to True or False
|
Parameter Check: Parameter-Condition-Value Expression Check: Lua based expression Pre-Build Tasks
|
|
Misc Task Provides some miscellaneous features |
Sleep Raise Custom Failure Bubble up Message/Status (with Ignore Failure on) |
|
Export to MySQL Task Export BigQuery Table to MySQL Three-steps-process:
|
Source: BigQuery Table Destination: MySQL DB/Table (Credentials required with certain permissions granted) Write Preferences:
Where Clause: for Replace Data mode Encoding Options:
|
|
Export to Netezza Task Similar to Export to MySql DB list is preset in config with respective white list |
Retired |
| Time Slice Task Helper task to support Range/Snapshot Decorators  |
|
|
Hub Task Waits for a list of BQ Jobs or Magnus Workflow to complete. |
Parametrized Items and Timeout |
Built-In Parameters
| Parameter Name | Type | Description | Example | Comments |
| var_date | String | Current Date (on Magnus Host) | 20160830 | |
| var_daydate | String | Current Date | 2016-08-30 | |
| var_year | String | Current Year | 2016 | |
| var_month | String | Current Month | 08 | |
| var_day | String | Current Day | 30 | |
| var_hour | String | Current Hour | 01 | |
| var_yesterday | String | Yesterday’s Date | 20160829 | |
| var_runId | Number | Runs Counter - Auto-incremented (can be reset) | 1 |
All or With No Failure only? What is behavior in Shared execution? |
| var_time | Number | Workflow Start - UNIX timestamp in milliseconds | 1472510722805 | |
| var_prevTime | Number | Previous Workflow Start - UNIX timestamp in milliseconds (can be reset) | 1472510367650 |
All or With No Failure only? What is behavior in Shared execution? |
| var_workflowId | String | Workflow name(id) | auto | |
| var_taskId | String | Task name(id) within respective task | auto | |
| var_timezone | String | Workflow time zone | ||
Magic and Sys Parameters
| Parameter Name | Type | Description |
| var_{taskid}_output | String | |
| var_{taskid}_return | String | |
| var_{taskid}_failed | Boolean | Failed status of TaskID – true or false |
| var_{taskid}_failure_message | String | Failure Message of TaskID |
| var_sys_workflow_prevtask_failed | Boolean | Failed Status of previous Task |
| var_sys_workflow_prevtask_failure_message | String | Failure Message of previous Task |
| var_iterationid | Number | Iteration Count (auto-incremented with each next iteration; reset for each new loop) |
| var_sys_double_eval_params | Boolean | If set to true – parameters are evaluated twice |
| var_sys_save_notification_enabled | Boolean | When the workflow is updated, send an email to the workflow owner and the user who made the change (if not set - off) |
| var_sys_notification_enabled | Boolean | Disable(false) / Enable (true) Workflow Notification (enabled if not set) |
| var_sys_notification_recipients | String | Comma-separated list of notification recipients (owner – if not set) |
| var_sys_notification_event | String | Set even to notify about: failure, success, completion (if not set – failure) |
| var_sys_notification_scope | String | Send notification if run as: manual, scheduled, aa (if not set – scheduled) |
| var_sys_notification_extra | String | Any extra information to include in the email notification |