SQL Sources

VSCode

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.

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:

run: my_sql_source -> { 
  group_by: first_name 
  aggregate: user_count is count()
}

QUERY RESULTS

first_name	user_count
FRANK	1
HAROLD	1
RICHARD	1
CLARENCE	1
JAMES	1

[
  {
    "first_name": "FRANK",
    "user_count": 1
  },
  {
    "first_name": "HAROLD",
    "user_count": 1
  },
  {
    "first_name": "RICHARD",
    "user_count": 1
  },
  {
    "first_name": "CLARENCE",
    "user_count": 1
  },
  {
    "first_name": "JAMES",
    "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:

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:

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.

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.

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_name	gender	last_name	n_with_this_name
MARY	Female	SMITH	45
JAMES	Male	SMITH	40
ROBERT	Male	SMITH	32
MICHAEL	Male	SMITH	32
JAMES	Male	WILLIAMS	32

[
  {
    "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": "ROBERT",
    "gender": "Male",
    "last_name": "SMITH",
    "n_with_this_name": 32
  },
  {
    "first_name": "MICHAEL",
    "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