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:
filenametext (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