SQL Queries

PushMetrics comes with a powerful SQL editor that allows querying almost any SQL database.

It can be used to both receive data and write data to the database.

Query results data can be used in subsequent tasks.

Prerequisites: You need to have at least one database connection configured in order to use this task.

Creating SQL Queries

SQL Editor

The SQL Editor has the following inputs:

Query Name: Used for referencing the query and its results
Database Connection: Select which database the query should run against
SQL code: Write your SQL statement. Supports Jinja templating, e.g. a parameter value can be used inside the SQL statement like this: {{ parameter_1 }}
Limit: Visual interface to apply row limits to queries

Data Explorer

In the left sidebar, you can explore the database's contents like schemas, tables, and columns - making it easier to develop your SQL queries.

When hovering over a table name, you can also preview 100 rows of any table and copy a SELECT * statement to your clipboard.

Execution

To execute a query, simply hit the Play button or use the keyboard shortcut ⌘ + enter.

0:00

Jinja Preview

You can create dynamic queries using Jinja templating syntax.
This is useful to parameterize queries, use loops or if/else statements in your SQL statements.

You can easily preview the rendered query by clicking on the {{ button:

Query Results

Query results are displayed below the SQL Editor.

You can sort columns
You can create a chart from the results data
You can check the details of the run </>
You can download the data as CSV or XLSX

💡

Row Limit: PushMetrics currently supports query results with a maximum of 100k rows. This limit will be applied even if no limit is present in your query. Contact us if you need a higher limit.

Results History

PushMetrics has a useful feature that keeps a history of your last query runs, so you can compare results between multiple run easily.

Referencing Queries and Results Data

You can use both the query statement and the results data in other tasks using the following methods:

Method	Description
query_name	returns the query as a string Example Usage:`{{ query_1 }}` Example Output:`SELECT * FROM my_table`
.data	returns the most recent query result as a dictionary Example Usage:`{{ query_1.data }}` Example Output:`[{'category': 'Furniture', 'city': 'Henderson', 'country': 'United States'}, {'category': 'Furniture', 'city': 'Henderson', 'country': 'United States'}]`
.data[row_index]	returns a single row of the most recent query result Example Usage:`{{ query_1.data[0] }}` Example Output:`{'category': 'Furniture', 'city': 'Henderson', 'country': 'United States'}`
.data[row_index].column_name	returns a single cell value of the most recent query result Example Usage:`{{ query_1.data[0].category }}` Example Output:`Furniture`
.export(filename, file_format='csv', csv_delimiter=' , ' , public=false)	generates a .csv or .xlsx file from the most recent results and returns a secure file URL. The file can be made public. Options: `filename`text (default: query_name) `file_format` 'csv' (default) or 'xlsx' `csv_delimiter` text (default:`,`) `public` true or false (default) Example Usage:`{{ query_1.export(filename='myfile', file_format='xlsx') }}` Example Output:`https://s3-eu-central-1.amazonaws.com/.../.../myfile.xlsx`