Playground

Overview

Goliath features a playground that offers a wealth of query and result options

1. Toolbar presents graphical icon options including selecting Editor modes, running or editing queries, and downloading results
2. Query/Script Editor allows construction of queries through rich text, an abundance of query options, ability to save query as view, and presentation of status info
3. UDF (User Defined Function) features are presented via the UDF editor with both inline and URI options
4. Parameters enhance development of queries by allowing builtin and custom parameters to be added to the query

Below are detailed descriptions for each feature

Toolbar

Mode

The query can be run in different modes

Mode	Description
SQL	runs BigQuery query
GoBQ	runs Golang script

Pre-run

Before a query is run the following icons are available:

Icon	Action	Description
	Legacy SQL / Standard SQL	Query will run in either Legacy SQL mode or Standard SQL. Click to change. See also Auto-detection
	New Query	Clears text area and resets options
or F5	Run Query	Executes query in text area along with options OR Executes query in focus along with options There are two ways to set query in focus Highlight portion of query to run Separate multiple queries by adding extra line with ##bq at a line's start position before each query and place cursor (w/o highlighting) in area of query to run. For example ##bq text of query 1 ##bq text of query 2 See Multi Query section for details

Paging Control

Page size is available in Standard, Enhanced, and Preview modes.  Page size limits the number of result rows displayed. 

Auto-Size

A check indicates Auto and that the number of rows is automatically determined by the available browser space

Page size

Unchecking allows the number of rows to be specified

Post-run

After a query is run the following icons are available:

Icon	Action	Description
	New Query	Clears text area and resets options
	Download Result	Download results to local file
	Save as Table	Saves results to table
	Pivot/Chart Result	Opens Visualization Panel
	Last Query Stats	Displays job details from previously run query

 

Information and User Settings

Help links and the name of the logged in user is displayed in the upper right section of the window

Tour opens a browser tab that walks the user through a tour of Goliath features

Help/Guides offers links to documentation on core features

Settings provides a popup with setting options

• Billing Project: Is the project where jobs that are run are billing to.  If a user has access to multiple billing projects they can select the desired billing project to use from the list
• Show on Load: Is the project that is loaded in the navigator when Goliath is loaded. To change the project that shows on load select from the drop down menu.
• Enable Hints display various hints while using the Goliath
• Use legacy SQL as default defaults the SQL version to legacy when Goliath is loaded
• Auto-detect SQL mode by default sets the default to auto-detect when Goliath is loaded
• Metadata Refresh Enable frequently refreshes BigQuery project, dataset, and table metadata used for listing items in navigator.  Un-checking this item does not automatically refresh the metadata.
• Take the Goliath Tour opens a browser tab that walks the user through a tour of Goliath features
• Save / Load Settings saves and loads user settings to/from a local file.  Custom projects, custom buckets, Show on Load, Hints Enabled, User Legacy SQL as default dialect, and Auto-detect SQL mode by default are selections that are saved and loaded. To save settings click on Save, enter file name, and click save to save file locally.  To load settings click on Load, navigate to the file, select the file, and click load.
• Goliath Help brings the user to this awesome Wiki resource documenting all Goliath features

Query / Script Editor

Features

Languages Support

1. BigQuery Legacy and Standard SQL dialects
2. JavaScript (UDF)
3. Go (GoBQ)

Line numbering

Query editor contains line numbers for each line of text

 

Context Sensitive Syntax Coloring

Keywords are color coded

 

Auto Completion with Hints

Keyword autocomplete Ctrl-Space for list of keyword selections Select from list to add to query

Bracket Matching

Brackets such as (), [], and {} color code match Place cursor next to bracket to color code identify its corresponding bracket

 

Embracing Selection in Brackets

Words can be embraced in brackets such as (), {}, [], <>, “”, ‘’, and `` Highlight text to embrace Type a qualifying open bracket to embrace selection

Multiline Cursor and Selection

Multiple cursors can be made across the same and multiple lines Place cursor at desired location Click Ctrl and place cursor at desired location Repeat step 2 until all cursors are created Multiple selections can be made across the same and multiple lines Select desired content Click Ctrl + select desired content Repeat step 2 until all desired content is selected

Block Selection

Select consecutive multiple lines Hit tab to indent Hit shift + tab to reduce indent

Full Window Mode

Query text area can be set to full window mode by For PC users click F11 For all users click on the full window icon in upper left Hit ESC, F11 (PC users), or click full window icon to leave full window mode

Multiline Comments

To add multiline comments follow steps for Multiline Cursor and Selection (7) above and then type comments

Multiline

See Multiline Cursor and Selection and Multiline Comments above

Multi Query

Multiple queries can be composed in the query text area.  
Construct multiple queries by separating them using ##bq
Query in focus is the query that will be selected to run.  Query in focus selection can be made by highlighting text or positioning cursor between ##bq separators.

	Adding multiple queries to the query text area Separate queries by ##bq Construct query
	One of two ways to set query in focus is by highlighting text Highlight text to set query in focus Hit run icon or F5
	The second of two ways to set query in focus is by placing the cursor between the query separators (##bq) Identify query to set in focus Place cursor in area between query separators (##bq) Hit run icon or F5
	To validate a query within multiple queries set the query in focus Identify query to evaluate Set the query in focus Wait for selection to be validated

Intellisense for Data Objects

Project/Dataset/Table Selection

Shift + Ctrl and space opens projects list along with column count

A table can be selected and added to query editor by Hold Shift + Ctrl and click space to bring up project list. From the display of projects and dataset count select project to list datasets From the display of project.datasets and table counts select dataset to list tables From list of tables make a selection to add it to query editor

Make selection to list project datasets along with table count.  The project is added to query editor

Make selection to list dataset tables.  The dataset is added to query editor

Make selection to add table to query editor

Column Selection

Add tables to query editor

Columns can be selected and added to the query editor by After SELECT statement click Ctrl + space to bring up Intellisense list.  Notice names of tables from query editor appear at top of list Make table selection to list table’s columns along with datatype Make a column selection to add the column to query editor

Place cursor between SELECT and FROM then Ctrl + space to bring up column selection for those tables 

Make a selection to view columns along with data types for that table

Select column to add it to query editor

Meta-tables

Shift + Ctrl and space opens projects list along with column count

A meta-table can be selected and added to query editor by Hold Shift + Ctrl and click space to bring up project list. From the display of projects and dataset count select project to list datasets From the display of project.datasets and table counts scroll to bottom of list to see meta-tables From list of meta-tables make a selection to add it to query editor

 

Make selection to list project datasets along with table count.  The project is added to query editor

Make selection to list dataset tables.  The dataset is added to query editor.  Scroll to bottom of list to see meta-tables

Make selection to add meta-table to query editor

Pseudo Column

Add tables to query editor Place cursor between SELECT and FROM then Ctrl + space to bring up column selection for those tables  Make a selection to view columns along with data types for that table. Pseudo columns are on the bottom of list Select pseudo column to add it to query editor

If a table has a pseudo column such as _PARTITIONTIME for a partitioned table the pseudo column is available for selection After SELECT statement click Ctrl + space to bring up Intellisense list.  Notice names of tables from query editor appear at top of list Make table selection to list table’s columns along with datatype.  Pseudo columns are on bottom of list Make a pseudo column selection to add the column to query editor

Action Bar (Buttons)

Available in SQL Mode

Button	Action
Run Query	Also F5 works as a Run Query Executes query in text area along with options OR Executes query in focus along with options There are two ways to set query in focus Highlight portion of query to run Separate multiple queries by adding extra line with ##bq at a line's start position before each query and place cursor (w/o highlighting) in area of query to run. For example ##bq text of query 1 ##bq text of query 2 See Multi Query section for details
Cancel Query	Appears in place of the Run Query button while a query is being executed. This button opens a confirmation dialogue to confirm the intention to cancel the query. If confirmed, currently running query will be cancelled.
Load	Loads query or workspace For query: inserts query text from chosen local file into query area For workspace: loads query, options, and parameters
Save	Saves query or workspace For query: text from query area is saved to local file For workspace: text from query, options, and parameters are saved to local file
Save View	Prompts for GBQ destination to save query as view
Show/Hide Options	Expands/Closes query options
Parameters	Opens parameters window.  Parses query/input fields for parameters and adds them to parameter list
Status Info	Displays validation message and estimated query cost

Available in GoBQ mode

Button	Action
Run Script	Executes script in text area. or F5
Load	Loads script or workspace For script: inserts text from chosen local file into script area For workspace: loads script, options, and parameters
Save	Saves script or workspace For script: text from script area is saved to local file For workspace: text from script, options, and parameters are saved to local file
Parameters	Opens parameters window.  Parses script for parameters and adds them to parameter list

Options

When a destination table has been added in Options and the options section is hidden - text identifying that a destination table has been supplied is displayed under action bar 

Regular Options (as in Native Web UI)

Input	Description
Destination	Enter name of project:database.table where results of query are saved to. Project is optional. Default(billing) project will be used then
Write Disposition	Write preferences (Write if empty/Append to table/Overwrite table)
SQL Dialect	When checked query is run as Legacy SQL.  Unchecked query is run as Standard SQL
Max Billing Tier	Maximum number used to limit query resources used.  A run exceeding provided value will cancel
Max Billing Bytes	Maximum number used to limit query resources used.  A run exceeding provided value will cancel
Edit UDF	Opens Dialog to add UDF source

Additional Options

Input	Description
Retention	Number of days/hour before table expires
SQL Dialect Auto-detect	When checked text in query is automatically checked to determine SQL version. When unchecked switching between SQL versions is manual
Enhanced	When checked query results are not flattened. Only for Legacy SQL

 

UDF

Concept: Inline vs URI

1. Inline is added directly to query
2. URI is an external reference

Editor

Inline Code Type in the text area to generate the function to be used or Load the function from a local file Click to save the file locally or Click to save the file to Google Cloud Storage Click when the function is ready to be used

URI Type the Google Cloud Storage URI for the function file Click to use in query

Parameters

Concept of Parameters here in Goliath

Parameter Actions

Parameters can be used in query text area, GoBQ text area, Inline UDF text area, and Options input boxes 1. Parameters are identified by <var_parametername>, :var_parametername, @var_parametername, or @parametername a. <> syntax - uses the value only - so parameter replaced with its value b. : syntax - uses both the value and data type - so parameter replaced with its typed value. (for example - for string type parameter is replaced with "value" c. @ syntax - uses both the value and data type - so parameter replaced with its typed value. (for example - for string type parameter is replaced with "value" 2. Click Parameters button to open Parameters window and add custom parameters defined in query/input boxes. If Parameters window is already open, this button will close the window. 3. A list of built-in parameters are displayed along with any custom parameters added when Parameters is clicked.  Parameters not in use are greyed.  Double-click on any parameter name to add it to the current editor. 4. Custom parameters can also be added by filling out name, type, and value from Parameters table and clicking + 5. Value fields that contain no value are bordered red when parameter is used and yellow when the parameter is not in use 6. Parameters with record type have accompanying schema input field 7. Click to refresh built-in parameter to reflect the current date/time. 8. Click to remove custom parameter

Parameter Menu Options

Menu Item	Action
Collect	Adds parameters not already included to list
Reset	Resets built-in parameter values to default and clears custom parameter values
Run-Time Preview	Opens modal and displays content with parameters substituted with values (see below)
Double Evaluation	Enables second round of parameter evaluation
Remove Unused	Removes all unused (greyed) custom parameters
Show: Built-In	When enabled built-in parameters are displayed  When disabled built-in parameters are hidden
Show: Unused	When enabled parameters that are not in use are displayed When disabled parameters that are not in use are hidden

Run-Time Preview

Working with parameters

Run-Time Preview will display workspace in PopUp with substituted values

Click on Run-Time Preview to display substitution of parameters with their value

Click on Apply to Workspace to permanently substitute values to workspace.  This action cannot be undone

Record Parameter

Record parameter consists two parts: Schema and Value.  It is different from other parameter type in the sense that a record parameter can contain more than one field.  For example, user can create a Record parameter named var_car that contains all attributes of a car.  The attributes of the car can then be accessed by field name: <var_car[id]>, <var_car[brand]>, <var_car[color]>, <var_car[country]>, <var_car[year]>.  They can also be accessed by index: <var_car[1]>, … <var_car[5]>.  So instead of creating five separate parameters, the attributes of the car are encapsulated in one Record parameter.

To create

1. Enter name of the parameter.
2. Select record as its type.
3. Enter the value.  If there is more than one field, separate the fields by comma.  For example:

a. If a string field contains comma, wrap the string with quotes.
    In this example, <var_car[3]> is the string "Red,Blue":

b. To escape double quote in a string field, use 2 double quotes:
    In this example, <var_car[2]> is the string Ford "Falcon":

4. Enter the schema.  It is expressed in the format of:  name type [,name type …]
where type can be: string, number, or boolean.
For example:

a. The full schema from the example is:
    id number,brand string,color string,country string,year number

b. Field type in the schema is optional.  If it is not specified, all fields will be treated as string.  For example, all fields will be treated as string for this schema:
id,brand,color,country,year

c. Schema is optional.  If it is not specified, fields of the record parameter can be accessed by index (starts from 1): <var_car[1]>, <var_car[2]>, …, <var_car[5]>

To access

1. The field of a record parameter can be accessed:

a. By index (starts from 1):
This returns the first field of the record parameter:
<var_car[1]>, :var_car[1], @var_car[1], or @car[1]
b. By field name:
<var_car[brand], :var_car[brand], @var_car[brand], or @car[brand]

2. The index 0 returns the last field.
For example:
<var_car[0]> is equivalent to <var_car[5]> following the example where the record has 5 fields.

3. :var_car[#] returns the field count of the record parameter, which is 5 following the example.

4. If the subscript has no index or field name:

a. <var_car[]> is the string representation of the record parameter, for example:
10,Ford,Red,USA,2016

Workspace

Concept

Workspace includes Mode, Options, Query/Script Area, and Parameters Mode Query/Script Area Options Parameters

Save Workspace

	Click on Save to bring up Save Workspace dialogue
	Add name of file Check to include saving of options.  Uncheck to not include saving of options Check to include saving parameters.  Uncheck to not include saving of parameters Click Save button to save workspace.  File is saved to default download folder
	Include Options Entries, selections, and UDF in options will be saved when checking Include Options from Save Query/Workspace
	Include Parameters Parameter options, built in parameters, and custom parameters are saved along with their type and value when include parameters is checked in Save Query/Workspace
	File is saved to default download folder The file extension is Sql when Include Options and Include Parameters are unchecked Workspace when either Include Options or Include Parameters is checked

Load Workspace

	Click on Load to bring up Load Workspace dialogue
	Click on Choose File to open default download location.  Search and select file to load. Click Load to load the chosen file
Before load workspace After load workspace (with Include Options and Include Parameters checked)	Loading Workspace will populate all saved areas and options into the Workspace area