Malloy Documentation
search

SQL sources, introduced in Malloy version 0.56, replace the previous method of including SQL queries in a Malloy model, SQL blocks.

Sometimes it can be useful to base Malloy models off of SQL queries. You can do so by using the .sql() connection method.

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

These SQL sources can be used any place a table source can be used:

document
run: my_sql_source -> { 
  group_by: first_name 
  aggregate: user_count is count()
}
QUERY RESULTS
first_​nameuser_​count
FRANK1
RICHARD1
JAMES1
JEREMY1
STEPHEN1
[
  {
    "first_name": "FRANK",
    "user_count": 1
  },
  {
    "first_name": "RICHARD",
    "user_count": 1
  },
  {
    "first_name": "JAMES",
    "user_count": 1
  },
  {
    "first_name": "JEREMY",
    "user_count": 1
  },
  {
    "first_name": "STEPHEN",
    "user_count": 1
  }
]
SELECT 
   my_sql_source."first_name" as "first_name",
   COUNT( 1) as "user_count"
FROM (
  SELECT
    first_name,
    last_name,
    gender
  FROM '../data/users.parquet'
  LIMIT 10
) as my_sql_source
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Unlike other kinds of sources, SQL sources can be used like a query in some cases.

They can be run directly:

document
run: duckdb.sql("select 1 as one")
QUERY RESULTS
one
1
[
  {
    "one": 1
  }
]
select 1 as one

And they can also be defined as a query:

document
query: my_sql_query is duckdb.sql("select 1 as one")
run: my_sql_query
QUERY RESULTS
one
1
[
  {
    "one": 1
  }
]
select 1 as one

Note: you can only run a SQL source as a query when it is defined as a query: or included directly in a run: statement.

Extending SQL Sources

Like other kinds of source, SQL sources can be extended to add reusable computations.

document
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 (A.K.A. "Turducken")

Malloy queries can be embedded in SQL blocks as well. When %{ and } appear inside a """ quoted (but not a " or ' quoted) string of an SQL source, the Malloy query between the brackets is compiled and replaced with the SELECT statement generated from the query.

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

source: malloy_in_sql_query is duckdb.sql("""
  SELECT * FROM
  (%{ // Malloy query starts with the %{
    users -> {
      limit: 10 group_by: first_name, last_name, gender
      aggregate: n_with_this_name is count()
    }
  })  -- Malloy query ends after the }
  WHERE n_with_this_name > 10
""")

run: malloy_in_sql_query -> { select: * }
QUERY RESULTS
first_​namegenderlast_​namen_​with_​this_​name
MARYFemaleSMITH45
JAMESMaleSMITH40
MICHAELMaleSMITH32
ROBERTMaleSMITH32
JAMESMaleWILLIAMS32
[
  {
    "first_name": "MARY",
    "gender": "Female",
    "last_name": "SMITH",
    "n_with_this_name": 45
  },
  {
    "first_name": "JAMES",
    "gender": "Male",
    "last_name": "SMITH",
    "n_with_this_name": 40
  },
  {
    "first_name": "MICHAEL",
    "gender": "Male",
    "last_name": "SMITH",
    "n_with_this_name": 32
  },
  {
    "first_name": "ROBERT",
    "gender": "Male",
    "last_name": "SMITH",
    "n_with_this_name": 32
  },
  {
    "first_name": "JAMES",
    "gender": "Male",
    "last_name": "WILLIAMS",
    "n_with_this_name": 32
  }
]
SELECT 
   malloy_in_sql_query."first_name" as "first_name",
   malloy_in_sql_query."gender" as "gender",
   malloy_in_sql_query."last_name" as "last_name",
   malloy_in_sql_query."n_with_this_name" as "n_with_this_name"
FROM (
  SELECT * FROM
  (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
)  -- Malloy query ends after the }
  WHERE n_with_this_name > 10
) as malloy_in_sql_query

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