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": "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
  }
]

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

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": "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": "JAMES",
    "last_name": "WILLIAMS",
    "gender": "Male",
    "n_with_this_name": 32
  },
  {
    "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
  }
]


-- BEGIN MALLOY QUERY
(SELECT 
   base."first_name" as "first_name",
   base."last_name" as "last_name",
   base."gender" as "gender",
   COUNT(1) as "n_with_this_name"
FROM '../data/users.parquet' as base
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...