Quickstart

The easiest way to try out Trilogy is in the demo. After that, Python is the most flexible and generic.

VsCode can be used as a [very alpha] more integrated development environmnent.

Trilogy Studio is targeted for those looking to do interactive analysis and query writing, and is closer to DBeaver or other IDes

Python

Trilogy as a language is only implemented in Python. The SDK is straightforward to install and gives you 3 options.

• Use the CLI to run trilogy files on disk
• Evaluate trilogy inline in python off text form
• Construct trilogy queries directly, without the text format

Installation

pip install pytrilogy

You can run this query online against our demo backend.

My First Query

Run this query online against the demo backend.

SELECT 1 + 1->test;

Once installed, you can use the trilogy CLI tool to run queries with the syntax trilogy run <query/path> <backend>. Currently, passing in complex DB config isn't supported, so this will work only with engines that support running with default environment configurations [duckdb, bigquery].

trilogy run "SELECT 1 + 1->test;" duckdb

A Longer Hello World

To write a useful script, you'll usually need to define some datasources representing objects in your database.

Hello World

Run this query online against the demo backend.

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

In Python

It's straightforward to import and run Trilogy in a python script.

from trilogy import Dialects, Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("SELECT 1 + 1->test;")

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

Environments

Environments are used to define the schema and tables that are available to the query.

The easiest way to start is just to populate your environment with definitions in line.

You can create reusable environments by defining them in a .preql file and parsing later.

from trilogy import Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("""
key user_id int;
property user_id.name string;

datasource users (
    uid:user_id,
    name:name
)
grain(user_id)
query '''
select 1 uid, 'Bach' as "name"
union all 
select 2, 'Mozart',
union all
select 3, 'Beethoven'
''';

# run our query
select 
    user_id,
    name,
    len(name)->name_length
;

""")

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

Backend Support

Trilogy uses sqlalchemy to connect to databases. You can install the necessary drivers with the following commands:

TIP

DuckDB is the default backend, and requires no additional configuration.

pip install pytrilogy[bigquery]
pip install pytrilogy[snowflake]
...etc

Snowflake Config

Snowflake requires account info.

from trilogy.dialect.config import SnowflakeConfig
executor = Dialects.SNOWFLAKE.default_executor(
            environment=presto_model,
            conf=SnowflakeConfig(
                account="account", username="user", password="password"
            ),
        )

Postgres Config

Postgres requires a connection string.

from trilogy import Dialects
from trilogy.dialect.config import  PostgresConfig

executor = Dialects.POSTGRES.default_executor(
    environment=presto_model,
    conf=PostgresConfig(
        host="account", port="port", username="user", password="password", database="db"
    ),
)

Loading Model File

Models can be imported from a path.

from trilogy import Environment, Dialects
from pathlib import Path

executor = Dialects.DUCK_DB.default_executor(environment=Environment.from_file(Path('./model.preql')))

For more information, see the concepts page.

VSCode Extension

Trilogy has [very alpha] availablity as a VSCode extension. You can install it from the marketplace.