SQL Blocks

VSCode

Sometimes it is useful to add SQL statements into a Malloy file. You can do so by using the sql: keyword. An SQL statement has two properties.

select: -- Has a string value which is bracketed with triple quotes """
connection: -- A string value which is the name of the connection (if not specified the default connection will be used)

run: duckdb.sql("""
    SELECT
      first_name,
      last_name,
      gender
    FROM '../data/users.parquet'
    LIMIT 10
  """)

QUERY RESULTS

first_name	last_name	gender
FRANK	HUGHES	Male
HAROLD	GATEWOOD	Male
SCOTT	JOACHIM	Male
JAMES	ALLEN	Male
JEREMY	CASAS	Male

[
  {
    "first_name": "FRANK",
    "last_name": "HUGHES",
    "gender": "Male"
  },
  {
    "first_name": "HAROLD",
    "last_name": "GATEWOOD",
    "gender": "Male"
  },
  {
    "first_name": "SCOTT",
    "last_name": "JOACHIM",
    "gender": "Male"
  },
  {
    "first_name": "JAMES",
    "last_name": "ALLEN",
    "gender": "Male"
  },
  {
    "first_name": "JEREMY",
    "last_name": "CASAS",
    "gender": "Male"
  }
]


    SELECT
      first_name,
      last_name,
      gender
    FROM '../data/users.parquet'
    LIMIT 10

Sources from SQL Blocks

Sources can be created from a SQL block, e.g.

source: limited_users is duckdb.sql("""
    SELECT
      first_name,
      last_name,
      gender
    FROM '../data/users.parquet'
    LIMIT 10
  """) extend {
  measure: user_count is count()
}

run: limited_users -> {
  aggregate: user_count
}

QUERY RESULTS

user_count
10

[
  {
    "user_count": 10
  }
]

SELECT 
   COUNT( 1) as "user_count"
FROM (
    SELECT
      first_name,
      last_name,
      gender
    FROM '../data/users.parquet'
    LIMIT 10
  ) as limited_users

Embedding Malloy queries in an SQL block ( Turducken )

Malloy queries can be embedded in SQL blocks as well. When %{ and } appear inside the """ string of a select: statement, the Malloy query between the brackets is compiled and replaced with the SELECT statement generated from the query.

source: users is duckdb.table('../data/users.parquet')

run: duckdb.sql("""
-- BEGIN MALLOY QUERY
%{
  users -> {
    limit: 10 group_by: first_name, last_name, gender
    aggregate: n_with_this_name is count()
  }
}
-- END MALLOY QUERY
""")

QUERY RESULTS

first_name	last_name	gender	n_with_this_name
MARY	SMITH	Female	45
JAMES	SMITH	Male	40
MICHAEL	SMITH	Male	32
ROBERT	SMITH	Male	32
JAMES	WILLIAMS	Male	32

[
  {
    "first_name": "MARY",
    "last_name": "SMITH",
    "gender": "Female",
    "n_with_this_name": 45
  },
  {
    "first_name": "JAMES",
    "last_name": "SMITH",
    "gender": "Male",
    "n_with_this_name": 40
  },
  {
    "first_name": "MICHAEL",
    "last_name": "SMITH",
    "gender": "Male",
    "n_with_this_name": 32
  },
  {
    "first_name": "ROBERT",
    "last_name": "SMITH",
    "gender": "Male",
    "n_with_this_name": 32
  },
  {
    "first_name": "JAMES",
    "last_name": "WILLIAMS",
    "gender": "Male",
    "n_with_this_name": 32
  }
]


-- BEGIN MALLOY QUERY
(SELECT 
   users."first_name" as "first_name",
   users."last_name" as "last_name",
   users."gender" as "gender",
   COUNT( 1) as "n_with_this_name"
FROM '../data/users.parquet' as users
GROUP BY 1,2,3
ORDER BY 4 desc NULLS LAST
LIMIT 10
)
-- END MALLOY QUERY

> We have referred to this feature the "Turducken" because you then take the SQL block and wrap it in an SQL source. It isn't the perfect name for infinte nesting, but it is amusing...