Switchboard Script for SQL Users
Switchboard Script (SBS) was designed to closely resemble standard SQL, the lingua franca of data work. However, SBS does a number of things that SQL cannot do - describe complex data transformation and workflow steps, define data sources and sinks, and configure monitoring. In addition, SBS attempts to streamline SQL syntax that may be redundant or unnecessary for data operations work.
Aggregations
SBS aggregations do not require “GROUP BY”
Switchboard can aggregate columns for a group of rows, similar to SQL. However, Switchboard does not need an explicit GROUP BY clause like SQL does. Instead, we determine the key columns automatically:
table my_new_table is select
key1, key1, max(creation_date) as max_date
from my_table;
You can filter groups matching only certain aggregation criteria:
table my_new_table is select
key1, key1, count() as group_count
from my_table
having group_count >= 10;
Conditionals
Use ? instead of CASE
SQL conditional statments use the CASE syntax. Switchboard Script uses a more compact “ternary” conditional format.
SQL
SELECT
CASE
WHEN user_name == "lauren" THEN "daenerys"
WHEN user_name == "gene" THEN "drogo"
WHEN user_name == "michael" THEN "bran"
WHEN user_name == "topher" THEN "jon"
ELSE "joffrey"
END as character
FROM t1;
Switchboard Script
table t2 is select
user_name == literal "lauren" ? "daenerys" :
user_name == literal "gene" ? "drogo" :
user_name == literal "michael" ? "bran" :
user_name == literal "topher" ? "jon" :
"joffrey" as character
from t1;
List Comprehensions
Use this SBS shorthand to refer to many columns at once
Switchboard makes it easy to deal with data with hundreds, if not thousands, of columns. Switchboard provides a “column comprehension” syntax for addressing lists of columns succinctly.
SQL
SELECT
column_1,
column_2,
column_3,
... /* hundreds of lines of column names */
column_998,
column_999,
column_1000
FROM my_table;
Switchboard Script
table my_new_table is select
<< my_table.$c for c in my_table
from my_table;
It’s also possible to leave out specific columns. For example, you can leave out column_1 and column_2 by using the except
clause:
table my_new_table is select
<< my_table.$c for c in my_table except [column_1, column_2]
from my_table;
Columns name versus labels
Get literal: Anything that’s not a column name in SBS need to be declared ‘literal’
SBS introduces the concept of the literal tag, to definitively identify anything that is meant to be considered as a literal string (and not a reference to a column name).
Here’s a transformation step with a new column (called new_column) with nothing but hello world in every row:
table my_new_table is select
column_1,
column_2,
literal 'hello world' as my new_column
from my_table;
Or, you could concatenate the value of every column in the table with a suffix:
table my_new_table is select
original_column,
original_column + literal '_01' as my new_column_with_a_suffix
from my_table;
Use literal whenever you use a regular expression in a function.
table my_new_table is select
extract(original_column, literal "(?<key>\w+)=(?<value>\w+)") as new_column
from my_table;