SQL Resolvers

SQL resolvers enable you to load and transform data from your data sources as feature values.

​

Defining SQL Resolvers

There are two ways to define SQL resolvers. The first is to write a SQL query in a file with the .chalk.sql extension, and the second is to use the make_sql_file_resolver function, which will generate a SQL file resolver based on the function arguments at build time.

​

SQL File Resolvers

To define a SQL resolver in a file, create a file with the .chalk.sql extension, add comments to specify the data source the SQL query will run on and the feature class whose features it will resolve, and write the SQL query.

-- source: pg_users
-- resolves: User

select id, name, age, hobbies from users;

The output column names in the SQL query must match the feature names in the feature class specified in the resolves comment. The data source specified in the source comment must also match the name of the data source configured in the Chalk dashboard. Each SQL file resolver must only resolve features within the same feature class, but a resolver does not have to resolve all features within the feature class.

​

Generating SQL File Resolvers

To generate SQL file resolvers at build time, use the make_sql_file_resolver function. This function enables you to define one or many SQL file resolvers at a time that may have slight variations, for example querying a different data source in a different environment. The following code would generate an equivalent SQL file resolver to the one above:

from chalk import make_sql_file_resolver

make_sql_file_resolver(
    name="get_user",
    source="pg_users",
    resolves="User",
    query="select id, name, age, hobbies from users;"
)

​

SQL Resolver Configurations

As with Python resolvers, SQL resolvers can be configured through many optional parameters. For generating SQL file resolvers, these parameters are passed as arguments to the make_sql_file_resolver function. For SQL file resolvers, these parameters are specified in the comments at the top of the file.

-- These parameters are required in all SQL file resolvers
-- source: pg
-- resolves: User

-- These parameters are optional in all SQL file resolvers
-- kind: online <-- default is "online," but can also be "offline" or "streaming"
-- incremental: <-- read more about incremental resolvers here: https://docs.chalk.ai/docs/sql#incremental-queries
--   mode: row
--   lookback_period: 60m
--   incremental_column: updated_at
-- count: 1 <-- the number of rows the resolver should return. This can be set to 1, 'one', 'one_or_none', or 'all'
-- timeout: 5m <-- the maximum time the resolver should run before timing out
-- cron: 0 0 * * * <-- a cron expression to schedule the resolver to run at a specific time
-- owner: 'helly@lumonindustries.com' <-- the email of the owner of the resolver. Alerts can be routed to owners.
-- tags: ['user', 'profile'] <-- tags to help organize resolvers in the Chalk dashboard
-- environment: 'production' <-- the environment in which the resolver should run
--total: True <-- whether this resolver returns all ID's of a given namespace.


select id, name, age, hobbies, updated_at from users;

For the full list of configurations, see here.