Installation

Python

Trilogy is currently available only in a python implementation, installable via the PyTrilogy package from pypi.

pip install pytrilogy

PyTrilogy can be used as a CLI tool or as a python library. Trilogy executables files are suffixed .preql, such as my_model.preql.

Hello World - CLI

Save the following code in a file named hello.preql

key sentence_id int;
property sentence_id.word_one string; # comments after a definition
property sentence_id.word_two string; # are syntactic sugar for adding
property sentence_id.word_three string; # a description to it

# comments in other places are just comments

# define our datasources as queries in duckdb
datasource word_one(
    sentence: sentence_id,
    word:word_one
)
grain(sentence_id)
query '''
select 1 as sentence, 'Hello' as word
union all
select 2, 'Bonjour'
''';

datasource word_two(
    sentence: sentence_id,
    word:word_two
)
grain(sentence_id)
query '''
select 1 as sentence, 'World' as word
union all
select 2 as sentence, 'World'
''';

datasource word_three(
    sentence: sentence_id,
    word:word_three
)
grain(sentence_id)
query '''
select 1 as sentence, '!' as word
union all
select 2 as sentence, '!'
''';

# an actual select statement
SELECT
    --sentence_id,
    word_one || ' ' || word_two ||  word_three as hello_world, # outputs must be named, trailing commas are okah
WHERE
    sentence_id = 1
;
# semicolon termination for all statements

Run the following from the directory the file is in.

trilogy run hello.preql duckdb

Hello World - Python

Python scripts will use the following core models:

Environments, to manage the semantic models.

Dialects, to bind to particular backends.

Executors, to run queries.

The easiest way to get started is to use the Environment.from_file method to load a model from a file, and then use the Dialects.DUCK_DB.default_executor method to create an executor for the DuckDB backend. Note that a select query being parsed from a file will not run until explicitly callled by an executor.

from trilogy import Environment, Dialects, Executor
from pathlib import Path

path = Path(__file__).parent / 'hello.preql'

env = Environment.from_file(path)


exec:Executor = Dialects.DUCK_DB.default_executor(environment=env)

results = exec.execute_text('''SELECT
	--sentence_id,
	word_one || ' ' || word_two as hello_world, # outputs must be named, trailing commas are okay
WHERE
	sentence_id = 1
;''')

for result_set in results:
    for row in result_set.fetchall():
        print(row)

Public Models

A curated list of public models is available via the trilogy-public-models repository. These models can be directly imported and used to query these common data models.

pip install trilogy-public-models

from trilogy_public_models.bigquery import usa_names