Macros in Scheduler

In the Qubole Scheduler, commands need access to the context of the instance. The scheduler provides access to the context through macros. For more information, see Using Macros in Qubole.

New macros can be defined using the Javascript language. The daily_tick_data table is used in the example given below. The example query is:

SELECT stock_symbol, max(high), min(low), sum(volume)
FROM daily_tick_data
WHERE date1 > '$sevendaysago$' AND date1 <= '$yesterday$'
GROUP BY stock_symbol

Macros can be accessed or defined using the Javascript language. Only assignment statements are valid. Loops, function definitions, and all other language constructs are not supported. Assignment statements can use all operators and functions defined for the objects used in the statements. Defined macros can be used in subsequent statements.

Javascript Language and Modules

The following Javascript libraries are available.

Library Description Link to Documentation
Moment.js Provides many date/time related functions. Ensure that the moment.js timezone functionality matches with the timezone used by the scheduler. Qubole uses Moment JS version 2.6.0. Moment.js
Moment-tokens Provides strftime formats Moment-tokens

The macros shown in the query are defined as follows:

Note

Ensure that the moment.js timezone functionality matches with the timezone used by the scheduler.

sevendaysago = Qubole_nominal_time.clone().subtract('days', 7).strftime('%Y-%m-%d')
yesterday = Qubole_nominal_time.clone().subtract('days', 1).strftime('%Y-%m-%d')

The following examples shows adding timezone function in the Moment Javascript.

India Standard Time Zone

sevendaysago = Qubole_nominal_time.clone().subtract('days', 7).tz('Asia/Kolkata').format('YYYY-MM-DD')
yesterday = Qubole_nominal_time.clone().subtract('days', 1).tz('Asia/Kolkata').format('YYYY-MM-DD')

US Pacific Standard Time Zone

sevendaysago = Qubole_nominal_time.clone().subtract('days', 7).tz('America/Los_Angeles').format('YYYY-MM-DD')
yesterday = Qubole_nominal_time.clone().subtract('days', 1).tz('America/Los_Angeles').format('YYYY-MM-DD')

See Moment JS Timezones for information on other time zones.

System Variables

The system variables are described in the following table.

Qubole_nominal_time A moment object representing the time when this instance is supposed to run.
Qubole_nominal_time_iso Qubole_nominal_time is in ISO 8601 format.

For more information, see Understanding the Qubole Scheduler Concepts.

See Creating a New Schedule for more information on setting Macros using the Qubole user interface.