Table of Contents

What is Qwery?

Qwery is a general-purpose programming language with a SQL-like syntax. Qwery is currently being developed as a SQL-driven tool for Extract Transform and Loading (ETL) workflows, but could be used to write many types of applications, even Games! (See demos/BreakOutDemo.sql in this repository)

Getting Started

Qwery offers 3 database connectivity options to choose from:

  1. JDBC driver (com.qwery.database.jdbc.QweryDriver) (found in ./app/jdbc-driver)
  2. Qwery CLI - commandline interface (com.qwery.database.jdbc.QweryCLI) (packaged with JDBC driver)
  3. QweryScript - SQL script executor (com.qwery.runtime.QweryScript) (found in ./app/core)
Build the Qwery JDBC driver

To build the JDBC driver:

sbt "project jdbc_driver" clean assembly

The generated binary can be found at app/jdbc-driver/target/scala-2.13/jdbc-driver-assembly-0.5.0.jar

Once you've built the JDBC driver, you ready to perform one of the following:

  • Connect to a standalone Qwery server.
  • Startup a local embedded Qwery server and connect to it.
  • Use the CLI tool to connect to any other JDBC database.
Qwery CLI and QweryScript

Start the previously created JDBC driver without arguments to enter interactive mode:

java -jar ./app/jdbc-driver/target/scala-2.13/jdbc-driver-assembly-0.5.0.jar
Entering interactive mode.
Choose one of the following startup options:
1. Start dedicated Qwery Server
2. Start CLI with embedded Qwery Server
3. Start CLI and connect to remote Qwery Server
4. Start CLI and connect to generic JDBC Database
5. Start SQL script in standalone mode
Choice (1..5)> 

From here you can run Qwery as a dedicated server or client-server. Additionally, some of the above options can be executed directly via the commandline:

  1. Start in client-server mode: java -jar app/jdbc-driver/target/scala-2.13/jdbc-driver-assembly-0.5.0.jar <port>
  2. Start in client mode: java -cp app/jdbc-driver/target/scala-2.13/jdbc-driver-assembly-0.5.0.jar <host> <port>

Once you're up and running

In the Qwery CLI, here's a little advice. The following a few commands will save you some headaches:

  1. this - produces a table containing all variables defined within the current scope.
  2. help - produces a table containing all available commands.
  3. @@objects - produces a table containing all created objects (tables, views, types, etc.)

Example usage:


For example, let's request help for every command starting with "create"

help 'create%'
name description example
create external function Creates an external function create external function myFunc from class('com.qwery.udf.MyFunc') using jar('/home/ldaniels/shocktrade/jars/shocktrade-0.8.jar
create external table Creates an external table create external table customers ( customer_uid uuid, name string, address string, ingestion_date int64) input format is 'json' location is './datasets/customers/json/'
create function Creates a function create function if not exists calc_add(a int, b int) as select @a + @b
create index Creates a table index create index stocks_symbol on stocks (symbol) comment is 'Index on Stock symbols'
create macro Creates a database MACRO create macro DUMP from "DUMP FROM %L:source ?WHERE +?%c:condition ?LIMIT +?%e:n" comment is 'This is a convenience MACRO for selecting data' select * from @@source where @condition is true
create procedure Creates a database procedure create procedure testInserts(industry string) as select symbol, name, sector, industry, summaryQuote from Customers where industry = @industry
create table Creates a database table create table Stocks ( symbol varchar(8), exchange enum (AMEX, NASDAQ, NYSE, OTCBB, OTHEROTC), lastSale double, lastSaleTime date)
create type Creates a database type create type mood comment is 'mood type' as enum (sad, okay, happy)
create view Creates a view create view if not exists OilAndGas as select symbol, name, sector, industry, summaryQuote from Customers where industry = 'Oil/Gas Transmission'
create webservice Creates a webservice create webservice if not exists getStocks (symbol VARCHAR(80)) { select * from @@stocks where symbol = @symbol }

The table variable this produces a result containing all user-created variables in the current scope. Consider the following example:

set @x = 7
set @s = 'Hello World'
name type value capacity sizeInBytes
s VARCHAR(11) Hello World null 11
x INT32 7 null 4


Qwery supports:

  1. In-memory table variables (e.g. VAR @@stocks TABLE(symbol VARCHAR(8), exchange VARCHAR(8), lastSale DOUBLE))
  2. Custom SQL statements via MACRO (e.g. ~ INSERT 10 RANDOM STOCKS INTO @@stocks ~)
  3. Stored Procedures (e.g. CREATE PROCEDURE processTickers(@exchange STRING) { ... })
  4. Stored Functions (e.g. CREATE FUNCTION getTickers(@symbol STRING) { ... })
  5. Stored Types (e.g. CREATE TYPE TransactionType AS TABLE (price DOUBLE, transactionTime DATE))
  6. Common Table Expressions (e.g. WITH tickerLookup (symbol, exchange) { ... })
  7. DO/WHILE statements (e.g. DO { ... } WHILE @cnt < 5)
  8. FOR statements (e.g. FOR @item IN (SELECT symbol, lastSale FROM @@stocks) { ... })
  9. IF expressions (e.g. SELECT IF(@value >= 100, 'Excellent', IF(@value >= 50, 'Satisfactory', 'Unsatisfactory')) AS rating)
  10. IF-ELSE statements (e.g. IF(@value >= 100) SELECT 'Excellent' AS rating ELSE IF(@value >= 50) SELECT 'Satisfactory' AS rating ELSE SELECT 'Unsatisfactory' AS rating)
  11. WHILE/DO statements (e.g. WHILE @cnt < 5 DO { ... })
  12. INTERSECT/SUBTRACT/UNION (e.g. SELECT @@stocks + @@mutatualFunds - @@etfs)
  13. INTERSECT/SUBTRACT/UNION (e.g. SELECT @@stocks UNION @@mutatualFunds SUBTRACT @@etfs)
  14. String Interpolation (e.g. 'name: {{ @name }}, age: {{ @age }}')
  15. PostgreSQL-style casting operator (::) (e.g. '1234'::INTEGER yields 1234)

Aggregate Queries

   exchange AS market,
   SUM(lastSale) AS sumLastSale,
   MIN(lastSale) AS minLastSale,
   MAX(lastSale) AS maxLastSale,
   AVG(lastSale) AS avgLastSale,
   COUNT(*) AS total,
   COUNT(DISTINCT(symbol)) AS tickers
FROM `stocks.transaction_history`
GROUP BY exchange
market sumLastSale minLastSale maxLastSale avgLastSale total tickers
OTCBB 6229909.28 0.00 983.82 249.39 24980 23177
NYSE 6258087.96 0.00 995.56 250.59 24973 23223
NASDAQ 6295374.46 0.00 994.70 249.98 25183 23426
AMEX 6157848.41 0.00 988.63 247.66 24864 23155

Nested Tables

CREATE TABLE `stocks.transaction_history` (
  symbol STRING(8) NOT NULL,
  exchange STRING(8) NOT NULL,
  transactions TABLE (
    price DOUBLE,
    transactionTime DATE
INSERT INTO `stocks.transaction_history` (symbol, exchange, transactions)
VALUES ('AAPL', 'NASDAQ', '{"price":156.39, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('AMD', 'NASDAQ', '{"price":56.87, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('INTC','NYSE', '{"price":89.44, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('AMZN', 'NASDAQ', '{"price":988.12, "transactionTime":"2021-08-05T19:23:11.000Z"}'),
       ('SHMN', 'OTCBB', '[{"price":0.0010, "transactionTime":"2021-08-05T19:23:11.000Z"},{"price":0.0011, "transactionTime":"2021-08-05T19:23:12.000Z"}]')
SELECT symbol, exchange, TO_JSON(transactions) AS transactions
FROM `stocks.transaction_history`
symbol exchange transactions
AAPL NASDAQ [{"__id":"0","price":"156.39","transactionTime":"Thu Aug 05 19:23:11 PDT 2021"}]
AMD NASDAQ [{"__id":"0","price":"56.87","transactionTime":"Thu Aug 05 19:23:11 PDT 2021"}]
AMZN NASDAQ [{"__id":"0","price":"988.12","transactionTime":"Thu Aug 05 19:23:11 PDT 2021"}]
INTC NYSE [{"__id":"0","price":"89.44","transactionTime":"Thu Aug 05 19:23:11 PDT 2021"}]
SELECT symbol, exchange, EXPLODE(transactions)
FROM `stocks.transaction_history`
WHERE symbol = 'SHMN'
symbol exchange price transactionTime
SHMN OTCBB 0.001 Thu Aug 05 19:23:11 PDT 2021
SHMN OTCBB 0.0011 Thu Aug 05 19:23:11 PDT 2021

String Interpolation

VAR @name: STRING = 'Larry Jerry'
VAR @age: INT8 = 32
VAR @title: STRING = 'name: {{ @name }}, age: {{ @age }}'
SELECT @title
name: Larry Jerry, age: 32


CREATE VIEW IF NOT EXISTS securities.stocks_view
COMMENT IS 'NYSE Stock symbols sorted by last sale'
   symbol AS ticker,
   exchange AS market,
   ROUND(lastSale, 1) AS roundedLastSale,
FROM `securities.stocks`
WHERE exchange = 'NYSE'
market lastSale roundedLastSale maxLastSale avgLastSale total tickers
NYSE 6229909.28 0.00 983.82 249.39 24980 23177
NYSE 6258087.96 0.00 995.56 250.59 24973 23223
NYSE 6295374.46 0.00 994.70 249.98 25183 23426
NYSE 6157848.41 0.00 988.63 247.66 24864 23155


First, let's create a webservice.

USE examples

-- create a table
CREATE TABLE Travelers (id UUID, lastName VARCHAR(12), firstName VARCHAR(12), destAirportCode VARCHAR(3))
INSERT INTO Travelers (id, lastName, firstName, destAirportCode)
VALUES ('7bd0b461-4eb9-400a-9b63-713af85a43d0', 'JONES', 'GARRY', 'SNA'), (UUID(), 'JONES', 'DEBBIE', 'SNA'),
       (UUID(), 'JONES', 'TAMERA', 'SNA'), (UUID(), 'JONES', 'ERIC', 'SNA'),
       (UUID(), 'ADAMS', 'KAREN', 'DTW'), (UUID(), 'ADAMS', 'MIKE', 'DTW'),
       (UUID(), 'JONES', 'SAMANTHA', 'BUR'), (UUID(), 'SHARMA', 'PANKAJ', 'LAX')

-- create the webservice
CREATE WEBSERVICE findTraveler (firstName varchar(80), lastName varchar(80)) {
   SELECT * FROM Travelers WHERE firstName = @firstName AND lastName = @lastName

Next, let's call the service from Scala:

val response = scala.io.Source.fromURL(s"").use(_.mkString)
assert(response == """[{"__id":0,"destAirportCode":"SNA","lastName":"JONES","__src_id":0,"firstName":"GARRY","id":"7bd0b461-4eb9-400a-9b63-713af85a43d0"}]""")

MACRO Development

Qwery facilitates the creation of custom SQL commands via MACROs. Here's an example of a MACRO that bulk inserts random stock quotes:

drop macro if exists `INSERT`
create macro `INSERT` via template('INSERT %e:total RANDOM STOCKS INTO %L:myQuotes') {
    -- @cnt is a local variable
    var @cnt: int32 = 0
    println 'Generating {{ @total }} random stock quotes...'
    do {
        set @cnt = @cnt + 1
        insert into @@myQuotes (lastSaleTime, lastSale, exchange, symbol)
        select NEW DATE() as lastSaleTime,
               ROUND(500 * RAND(0.99), 4) as lastSale,
               ['AMEX', 'NASDAQ', 'NYSE', 'OTCBB'][rand(4)] as exchange,
               Random_string(['A' TO 'Z'], 4) as symbol
    } while @cnt < @total
    select 'INSERTED' as action, @cnt as count

Next, let's create a new table to store the stock quotes.

VAR @@stockQuotes TABLE(symbol VARCHAR(4), exchange VARCHAR(5), lastSale DOUBLE, lastSaleTime DATE)[1000]

Next, use the MACRO:

~ INSERT 1000 RANDOM STOCKS INTO @@stockQuotes ~

Above we've asked the MACRO to create and insert 1,000 random stock quotes into our @@stockQuotes table.

Finally, let's see the data we've generated:

select * from @@stockQuotes limit 5
| symbol | exchange | lastSale | lastSaleTime             |
| AMD    | NASDAQ   | 67.55    | 2022-06-17T19:02:01.917Z |
| AAPL   | NYSE     | 123.55   | 2022-06-17T19:02:01.917Z |
| GE     | NASDAQ   | 89.55    | 2022-06-17T19:02:01.917Z |
| PEREZ  | OTCBB    | 0.001    | 2022-06-17T19:02:01.917Z |
| AMZN   | NYSE     | 1234.55  | 2022-06-17T19:02:01.917Z |

MACRO Template tags
tag description / function example
? optional attribute tag ?LIMIT +?%e:limit matches LIMIT 100
+? optionally required sequence tag ?ORDER +?BY +?%o:sortFields matches ORDER BY Symbol DESC
%a a single atom %a:name matches Tickers or 'Tickers'
%aa a list of atoms %aa:name matches symbol, exchange, lastSale
%A a list of arguments %A:args matches (1,2,3)
%c a conditional expression %c:condition matches x = 1 and y = 2
%C a combo-box / choice %C(mode,INTO,OVERWRITE) matches INSERT INTO or INSERT OVERWRITE
%e a single expression %e:expression matches 2 * (x + 1)
%E a list of expressions %E:fields matches field1, 'hello', 5 + new java.util.Date(), ..., fieldN
%f a single field name %f:field matches lastSale
%F a list of field names %F:fields matches field1, field2, ..., fieldN
%IFE IF EXISTS clause %IFE:exists matches IF EXISTS
%IFNE IF NOT EXISTS clause %IFNE:exists matches IF NOT EXISTS
%jc a Java Class reference %jc:class matches java.util.Date
%jj a Java Jar reference %jj:jar matches libs/utils.jar
%J a JOIN clause %J:joins matches INNER JOIN stocks ON A.symbol = B.ticker
%L a location or table %L:table matches accounts or @@accounts
%LQ a location or table or sub-query %LQ:query matches accounts or @@accounts or (SELECT ...)
%N an instruction or code block %N:code matches PRINTLN 'Hello World'
%o an ordered field list %o:orderedFields matches field1 DESC, field2 ASC
%P a collection of parameters %P:params matches name STRING, age INTEGER, dob DATE
%p properties ('quoteChar'='~', 'separatorChar'=',')
%q indirect query source (queries, tables and variables) %q:source matches AddressBook or @@addressBook or ( SELECT * FROM AddressBook )
%Q direct query source (queries and variables) %Q:query matches @@addressBook or SELECT * FROM AddressBook)
%r regular expression matching %r``\\d{3,4}\\S+`` matches 123ABC`
%i an infrastructure type (e.g. table) %i:name matches Customers
%T a type %T:myType matches Decimal(20,2)
%U update field assignments %U:assign matches SET lastSale = 25, code = 'R'
%v variable reference %v:variable matches SET @variable = 5
%V insert values (queries, VALUES and variables) %V:data matches @@numbers or (SELECT ...) or VALUES (...)
%z quoted text values %z:comment matches 'This is a comment'


Build Requirements

Building the application

sbt clean assembly

Running the tests

sbt clean test