Configuration
Configuring your project is done with a simple YAML file, named quary.yaml
and placed at the root of a project. The file defines which database to use and how to connect to it. The following are the different options for configuring the database.
BigQuery
BigQuery is a cloud based database, it is a good choice for large projects and for production. It is operated by Google and is part of the Google Cloud Platform. To use BigQuery you need to create a project in the Google Cloud Platform and enable BigQuery for the project. You can configure the connection to BigQuery with the following configuration.
bigQuery:
projectId: my-project-id
datasetId: my-dataset-id
Snowflake
Snowflake is a cloud-based data warehousing platform that offers scalable, on-demand storage and computing power and that separates storage and compute, enabling users to independently scale and pay for each.
snowflake:
accountUrl: account-url
clientId: client-id
clientSecret: client-secret
role: your-quary-role
database: database-name
schema: schema-name
warehouse: warehouse-name
Clickhouse
Clickhouse is a column-oriented database management system developed by Yandex. It is designed for OLAP workloads and is a good choice for large-scale data storage and analysis. Unlike other databases, Clickhouse does not have a concept of a schema, so you only need to specify the database.
clickhouse:
database: transform
All connection details are passed as environment variables. To connect to Clickhouse you need to set the following environment variables: CLICKHOUSE_HOST
, CLICKHOUSE_PORT
, CLICKHOUSE_USER
and CLICKHOUSE_PASSWORD
.
Postgres
Postgres is a popular open-source database. It is a good choice for small to medium projects and for production.
postgres:
schema: transform
All connection details are passed as environment variables. The following environment variables are used to connect to the database. PGHOST
, PGPORT
(if not set defaults to 5432), PGUSER
, PGPASSWORD
, PGDATABASE
. They are the same as those used by the psql
command line tool.
Model Configuration
For Postgres, models can also be configured in order to better suit your needs. There are two main configurations available, unlogged
and indexes
.
Unlogged Table
Details about unlogged tables can be found in the Postgres documentation. Unlogged tables are not written to the write-ahead log, which makes them faster to write to. They are not crash safe, so they are risks to consider.
They can be configured as follows:
models:
- name: stg_orders
materialization: table
database_config:
unlogged: true
Indexes
Indexes can be added to tables to speed up queries. They can be configured as follows. The columns
field is a list of columns to index, and the unique
field is a boolean that specifies if the index should be unique.
models:
- name: stg_orders
materialization: table
database_config:
indexes:
- columns: [order_id]
unique: true
- columns:
- order_date
- order_id
Redshift
Redshift is a cloud-based data warehousing service provided by Amazon Web Services (AWS). It is designed for large-scale data storage and analysis.
redshift:
schema: your-schema
All connection details are passed as environment variables. The following environment variables are used to connect to the database:
RSHOST
RSUSER
RSPASSWORD
RSDATABASE
RSPORT
(if not set defaults to 5432)
DuckDb
DuckDb is an in-process SQL database, it's a feature-rich SQL dialect that it is a great way to query data locally on your machine.
File Based
The database is stored in a file on the disk and is persisted between runs. In this case the path to the file is specified, and each run creates the file if it does not exist.
duckdb:
path: database.db
In Memory
The database is created in memory and is lost when the application is closed. It is faster than the file-based version and is good for rapid testing.
duckdbInMemory: {}
SQLite
SQLite is a database type that runs locally, it is a file-based database. It is a good choice for small projects and for testing. It can be operated in two manners
In Memory
The database is created in memory and is lost when the application is closed. It is faster than the file-based version and is good for rapid testing.
sqliteInMemory: {}
File Based
The other option is to use a file based database, where the database is stored in a file on the disk and is persisted between runs. In this case the path to the file is specified, and each run creates the file if it does not exist. It is useful for testing, for small projects and when you want to use the database in different applications.
sqlite:
path: db.sqlite
Environment variables
In order to make your set up easier, you can use a .env
files to set environment variables. By default, quary will pick up variables inside of .env
. You can specify other .env
files with the -e
flag. For example, the following will read both .env
files.
quary init -e='.env' -e='local.env'
Pre-run scripts
Pre-run scripts allow you to execute arbitrary SQL code before the main Quary run. These scripts are useful for various setup tasks, such as:
- Defining user-defined functions (UDFs)
- Installing extensions
- Setting up temporary tables or views
- Configuring session parameters
To create pre-run scripts, create one ore more SQL files containing the desired pre-run code and specify the paths to these in your quary.yaml
configuration file as following.
duckdbInMemory: {}
pre_run_scripts:
- path/to/script1.sql
- path/to/script2.sql