Quickstart

The easiest way to run some Trilogy is our interactive demo.

After that, the online studio showcases core features for interactive analysis and query writing and model reuse, and is closer to DBeaver or other IDEs.

Tips

We'd strongly recommend starting with one of these easy to use, no-install online tools. If Trilogy grabs your interested, continue on into the below options to get deeper in the ecosystem.

If you want to build programattic integrations, Python is the most flexible and generic entrypoint, either through the package or the CLI.

The VsCode extension can be used as an alternative development environment.

Python

Trilogy as a language has a core implementation in Python. The SDK gives you these options:

• Use the CLI to run Trilogy files [.preql]
• Evaluate Trilogy inline in python against queries in string format
• Construct Trilogy queries using python models through the authoring API, with no text format at all

Your entry point should depend on your preferences; are you a CLI fan or do you like python object oreiented APIs?

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:

Tips

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.