Civis enables you to write JavaScript, allowing you to take advantage of variable declaration, loops, conditionals, and more.  With only a basic understanding of JavaScript, you can write an advanced set of SQL queries to help meet your organization's needs.

Getting Started

1. From the top navigation menu, select Code, and then Scripts. Next, click on New Script in the top-right corner of the page.
2. Select JavaScript from the New Script pane.

Setting Up the Script

1. Write your JavaScript code into the code box in the center of the page.
2. Once your JavaScript is ready, click Run Now in the top-right corner of the page.

log('running insert')
query("insert into your.table values(1, 'Chicago')")

log('running select')
var res = query("select id, name from your.table")
if (res.length === 0) {
  log('found zero rows')
} else {
  log('found ' + res.length + ' rows')
}

Custom Functions

In addition to normal JavaScript functions and the useful Underscore JavaScript library, there are a handful of custom functions you can use in Civis:

civis.api.call(method, path, parameters)

Calls the Civis API and returns the response.  For example, to get a temporary S3 credential usable for the next 15 minutes: civis.api.call("get","/credentials/27/temporary",{duration: 15 * 60})

query(sql)

query(sql, hashOrObj)

Runs the SQL against the Redshift database and returns the results as an array of objects. If running an INSERT, UPDATE, or DELETE statement, 'query' returns the number of rows affected. If running a SELECT and no results are returned, the function will return an empty string (""). For best performance, avoid using in-platform JavaScript to work with large result sets (more than several hundred rows) and instead use SQL scripts.

log(msg)

log(msg, hashOrObj)

Logs the message to the job's run log.

setMemory(name, value)

Saves 'value' so that it can be retrieved in a subsequent run with 'getMemory'.

getMemory(name)

Returns the value remembered as 'name' using the 'setMemory' function.

Variable Embedding

You can take variables you have declared in your JavaScript and embed them in the job success notification email.  Open the Notify pane (paper airplane icon) and include {{variable}} in the Email Body text box.

If you would like to save the results of a query, format the result set, and include it in the Email Body of the job success notification, grab the code from here and make sure to include {{mdTable}} in the Email Body section of the Notify pane.

Variable embedding can also be used within your JavaScript script.  Here are some examples:

log("the answer is: {{answer}}", {answer: 42})

var rows = query('select id, name from your.table')
log("the first row is id: {{id}}, name: {{name}}", rows[0])

var rows = query('select count(*) from your.otherTable where id = {{id}}', rows[0])

Limitations

• JavaScript scripts only support simple data types for BigQuery queries. Types like Google::Cloud::Bigquery::Time, Hash, StringIO, DateTime and BigDecimal are not supported.