Create and share R scripts

You can create and share your custom R scripts in ThoughtSpot.

Overview

We support R only in Falcon deployments (data imported into ThoughtSpot), and R integration is disabled by default. It is not available when creating connections and linking to external databases in real time.

Each ThoughtSpot cluster is capable of running an R analysis on your data. To perform your own R analysis, start with a simple search and select the R icon on the right.

Use the Custom R Script dialog and settings to enter your script, set which columns to include for analysis, and indicate what output data to expect from your script (PNG or CSV).

R icon at the right of the screen

ThoughtSpot also supports the sharing of scripts among users to enable you to share your powerful R analyses across the system, and allow others to run your scripts on different search results.

How it works

An R script in ThoughtSpot is based on of your original search (both the data and the schema), using the columns you select for the analysis. You can select all columns used in the original search or a subset of those columns, depending on the script.

ThoughtSpot auto-generates objects with variable names by which you can refer to data elements in your script.

You can refer to the data in the selected columns by using vectors that ThoughtSpot generates for these before the script is run. The first column you select has the variable name .param0, the second column you select has the variable name .param1, and so on. This naming scheme continues if there are more columns.

ThoughtSpot also provides an automatically-generated data frame object, df, that contains all selected column vectors. The data frame is R’s representation of a table (a 2D data structure containing rows and columns).

When the script executes, it passes the information to the server to run the analysis, and displays the answer as a visualization, in either PNG or CSV (table) format.

As we demonstrate in the following examples, you can chain R analyses together by running another R script on the answer to a previous script.

Write a custom script

To add a custom R script, start by running a search, then select the R icon R icon on the toolbar.

R icon at the middle right of the screen

Add your script into the Custom R Script dialog, and set the column bindings and output type appropriately, as shown in the following examples.

Add your script in the Custom R Script section. Specify the columns that should be included

For an R script to produce a meaningful output in ThoughtSpot, the script should generate a CSV or PNG file. Output should be written to the token #output_file#. CSV output is rendered as a simple table in ThoughtSpot and PNG output is rendered as a static PNG.

Basic R script to generate CSV data

This is an example of a basic R script that generates CSV data. The df variable name must be lowercase.

write.csv(df,  #output_file#)

The generated data is displayed as a table when you run the analysis:

R script output as a table

This basic script returns the same table results as the table view for a normal ThoughtSpot query, barring some additional formatting.

Table results for a normal ThoughtSpot query

Basic R script to generate a PNG graphic

This is an example of a basic R script that generates PNG data as its output.

png(#output_file#)
plot(.param0, .param1)

The result is a static PNG:

Basic R png

Column bindings and output file type

  • Under Column(s) for R analysis, you specify the data you want to send to R and how to send it. If you do not make any choices here, ThoughtSpot selects all columns in the search, in the order they appear in the search bar.

    In the preceding examples, Year (Date) is .param0, and Sales is .param1. To verify this, view the Columns for R analysis section. You can see that date is .param0, and Sales is .param1. Reordering the columns changes the column bindings/params. To reorder the columns, use the up and down arrows that appear when you hover over a parameter in the list under Column(s) for R analysis.

  • Under Output File Type, select PNG or CSV, depending on your output requirements.

Script options

You can select these icons in the R script dialog to get more options:

  • The R script information icon next to Custom R Script provides a basic reference guide for creating an R analysis in ThoughtSpot. To see the guide, vlick on the information icon.

  • The More menu icon more options menu icon provides a menu with options to save the script, overwrite the script using a previously saved R script, or share your R script with other users in the system.

  • The arrow at the upper right of the script dialog box popout arrow opens a popout editor that gives you a larger space in which to view and edit your R script.

K-Means clustering example scripts

This script loads the ggplot2 visualization package, labels the columns inside of the ThoughtSpot generated data frame object (df), and runs a K-Means clustering algorithm.

library(ggplot2)

kmeansOutput <- kmeans(df[1:2], 3, nstart = 20)
kmeansOutput$cluster <- as.factor(kmeansOutput$cluster)
png(file=#output_file#, width=400, height=350, res=72)
print(ggplot(df, aes(.param0, .param1, color = kmeansOutput$cluster)) + geom_point())

When you run this script on the results of the original sales date search, you get the following visualization as a PNG.

Sample R visualization PNG

You can run another R script directly on this result to get CSV results:

kmeansOutput <- kmeans(df[1:2], 3, nstart = 20);
df$Cluster <- as.factor(kmeansOutput$cluster);
write.csv(df, file=#output_file#, row.names=FALSE);

The script for CSV output generates a table:

Sample R visualization CSV

Save and share your script

To save a script, choose Save As from the options menu more options menu icon for the script. After you name and save it, your script will show in the list of options when you select Load script template.

More menu for a Custom R Script. Select Save As.

To share a script, select Share from the options menu more options menu icon.

More script examples

For more example scripts, check out the blog post on Using R Analysis in ThoughtSpot for Time Series Forecasting, or load one of the ThoughtSpot-provided scripts:

  • Binomial Logistic Regression

  • K-Means Clustering Plot

  • K-Means Clustering Table

  • Time Series Outlier Detection

  • Time Series Forecast

List of ThoughtSpot-provided scripts