About Queries

A query is SQL that is run from the Queries tab against database tables in the Customer 360 tab. A query returns a refined and filtered subset of useful customer data.

Queries tab

The Queries tab provides an overview of the status of every query. A table shows the status and details. Queries are listed by row. The details include the date and time at which this query last ran, along with the number of records (rows) that were returned during the last completed run.

Queries tab

Note

A query with SLA status is guaranteed to run with every automated workflow and is actively monitored by Amperity.

Query types

A query may be in one of the following types:

Type

Icon

Description

SQL queries

SQL queries are built using the SQL Query Editor.

Visual query

Visual queries are built Visual Query Editor.

Query status indicators

Every query is assigned one of the following status indicators:

Status

Icon

Description

Draft

A draft query that may only be run against the customer 360 database from a query editor.

Activated, not run

An active query that has not yet run against the customer 360 database.

Waiting to run

An active query that is waiting in the queue to be run against the customer 360 database.

Running

An active query that is in the queue running against the customer 360 database.

Completed

A completed query is an active query that has run successfully through Amperity as part of an orchestration within the past 24 hours.

Inactive

An active query that has not run in the past 30 days.

Error

An active query that returned some type of error while running against the customer 360 database.

Search queries

You can search for queries on the Queries tab. From the search box, type a string and then hit the ENTER key. The list of queries is filtered to contain only those queries that match the search critera. Each query in the list has available to it the same actions as it would from the unfiltered queries list. Click the name of the query to view it. Use the individual query menu to edit, make a copy, run, download, move, or delete.

Click Clear to return to the unfiltered queries list.

Data Explorer

The Data Explorer provides a detailed way to navigate through data tables in Amperity. The Data Explorer displays each column in the data table as a row, with the column name, data type, associated semantic, and a data example. A sample of real table data is available available on another tab.

The Data Explorer may be accessed from the Queries tab via the Open Data Explorer link in the right-side navigation. This link is visible when working in the Visual Query Editor or the SQL Query Editor. When clicked it will open the data explorer for the selected database and will display all of the tables in the database. Click any of the databases to expore the schema, view sample data, and details.

To open the Data Explorer

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

  2. Under Database, click Open Data Explorer.

  3. Click any table name to view columns and rows, schemas, example data, and table details.

  4. When finished exploring the data, click Close.

Docked mode

A docked mode for the data explorer is available from the Queries tab when building queries. This mode of the data explorer shows a list of tables in the customer 360 database, and then for each table a list of columns and data types. This mode provides a useful quick reference for tables, columns, and data types as you are building SQL queries.

Full-screen mode

A full-screen mode for the data explorer is available from most areas within Amperity that shows data tables in the customer 360 database. This mode enables detailed exploration of each table, including an overview, the data table schema, examples of data, and source table details.

The data explorer, as opened from within the Stitch tab in Amperity.

Table schema

The Schemas view in the Data Explorer displays information about each column in the table, along with an example, and information about completion, uniqueness, and cardinality.

Completion

Completion is a simple percentage of data rows that contain some value within a column.

Tip

Ensure that prior to filtering a column that it actually contains data that can match the search criteria.

Cardinality vs. uniqueness

Cardinality is a measure of how many unique values are present in data. A higher cardinality indicates a larger percentage of unique values, whereas a lower cardinality indicates a higher percentage of repeat values.

Uniqueness divides the number of unique values–cardinality–by the number of rows in a table.

Use cardinality and uniqueness to help guide the creation of well-behaved JOIN operations when authoring SQL queries.

  • Avoid using JOIN operations when columns have lower cardinality. The high frequency of duplicate values will result in a row for every possible match.

  • Columns with low uniqueness values as keys on both sides of a JOIN operation will run more slowly and is less likely to return the desired results.

  • Empty fields (i.e. NULL values) are counted as duplicates, i.e. “not unique”. For example: a field with 90% completion and 90% uniqueness has different values for each of the non-empty rows.

Table examples

The Examples view in the Data Explorer shows actual data for a randomly selected set of rows in the data table.

Note

Users with restricted access to PII will not see data for PII-restricted columns, but will see data for all other columns.

Table details

The Details view in the Data Explorer shows summary data about the table, along with information about which source tables were used to build it.

Query editor

The SQL Query Editor is the user interface for a full SQL query engine based on Presto SQL that interacts with customer database tables in Amperity. The SQL Query Editor relies primarily on using the SELECT statement, along with common table expressions, joins, functions, and other components of Presto SQL to build and design advanced queries.

Use the Query Editor to build SQL queries against tables and columns in your customer 360 database to support any downstream workflow. The Query Editor uses Presto SQL.

Queries tab, SQL Segment Editor

Queries may be authored using the Visual Query Editor or the SQL Query Editor. Click Create, and then select the query editor to open. Queries that are already created have an icon that shows from which query editor they were authored.

  • indicates the query was created using the Visual Query Editor.

  • indicates the query was created using the SQL Query Editor.

All queries must be activated before they can run as part of a scheduled workflow.

Note

Amperity is a multi-user system and the set of queries for your company is shared across all users. That means that if one user creates a draft query, another can open and edit it, so work can be easily passed between people on your team.

However, it also means that if 2 users are editing the same thing at the same time, their changes will collide. Amperity resolves this by applying the last set of changes saved as a whole. This will always keep the query in a consistent state (it will never be half-implemented). But changes that were saved first will be overwritten. As a result, we strongly recommend that you coordinate changes to specific objects in Amperity with others on your team.

About Presto SQL

Presto is a distributed SQL query engine that is designed to efficiently query vast amounts of data using distributed queries. Presto is used by the Amperity SQL segment editors to define segments, which are SQL queries that return data from stitched data tables.

Amperity queries are built using Presto SQL to define a SELECT statement. Please refer to the About Presto SQL.

All queries that are built via the SQL Query Editor are done using a SELECT statement. In some cases, a WITH clause is used along with the SELECT statement. Each select statement can additional functionality, such as WHERE, LEFT JOIN, GROUP BY, ORDER BY, LIMIT clauses, CASE expressions, functions, operators, and other componetns that are part of Presto SQL, which is the underlying SQL engine for both the Visual Query Editor and SQL Query Editor.

Validate queries

SQL queries are validated from the SQL Query Editor by clicking the Run Query button. The results of the query are returned in the results window. The quality of the results can be inspected, and then fine-tuned. Errors in the syntax are reported in the results window.

How-tos

This section describes tasks related to building queries in Amperity:

Add comment to query

You can add a comments to a SQL query to explain sections or to prevent execution of that line within a SQL statement.

To add comments to a query

  1. From the Queries tab, open the menu for a SQL query, and then select Edit. This opens the SQL Query Editor.

  2. Select a row in the SQL query to which a comment will be added. A row must have content; you may need to add the comment first, and then comment it out.

    – OR –

    Select a line in the SQL statement that should not be run.

  3. Below the SQL query, click Comment. -- is added at the start of the line and it is commented out.

For example, compare a SQL query without comments:

SELECT
  amperity_id
  ,(.20*Recency + .30*Frequency + .50*Monetary) AS NewRFM
  ,RfmScore AS DatabaseGeneratedRFMSCORE_WithoutWeights
FROM Customer360
LIMIT 100

to a SQL query with comments:

SELECT
  amperity_id

  -- default with no weighting:
  -- ,(.33*Recency + .34*Frequency + .33*Monetary) AS NewRFM

  -- versus a 20/30/50 weighting:
  ,(.20*Recency + .30*Frequency + .50*Monetary) AS NewRFM
  ,RfmScore AS DatabaseGeneratedRFMSCORE_WithoutWeights
FROM Customer360
LIMIT 100

Add query

Queries may be authored using the Visual Query Editor or the SQL Query Editor. Click Create and then select the query editor to open. Queries that are already created have an icon that shows from which query editor they were authored. All queries must be activated before they can run as part of a scheduled workflow.

You can add a query in the following ways

Add as copy

Use the Make a copy option to add a copy of an existing query. This will create a draft query with the same settings and SQL query as the copied query, along with a placeholder name. Rename the query, and then make any other changes before activating it.

To add a query as a copy of an existing query

  1. From the Queries tab, open the menu for a query, and then select Make a copy. This refreshes the recently edited queries list to add a query with “COPY - DRAFT” appended to the name.

    The copied query will be of the same type–visual or SQL–as the original query.

  2. From the Queries tab, open the menu for a query, and then select Edit. This will open a query editor.

  3. Make your changes to the query, and then validate them.

  4. Click Activate.

  5. In the Activate Query dialog box, select Activate.

Add as SQL query

The SQL Query Editor is the user interface for a full SQL query engine based on Presto SQL that interacts with customer database tables in Amperity. The SQL Query Editor relies primarily on using the SELECT statement, along with common table expressions, joins, functions, and other components of Presto SQL to build and design advanced queries.

To add a query using the SQL Query Editor

  1. From the Queries tab click Create, and then select SQL Query. This opens the SQL Query Editor.

  2. Under Database, select a database. The Customer 360 database is selected by default.

  3. Define the query against the selected database.

  4. Click Run Query and debug any issues that may arise.

  5. Click Activate.

Add as visual query

The Visual Query Editor is a user interface that allows creating queries using Presto SQL without having to write SQL. The Visual Query Editor is combination of filters for WHERE and AND clauses organized as a series of configurable drop-down menus and pick-lists that build SQL queries that run against tables in the customer 360 database.

To add a query using the Visual Query Editor

  1. From the Queries tab click Create, and then select Visual Query. This opens the Visual Query Editor.

  2. Define the query using the drop-down menus and buttons.

  3. Click Run Query and debug any issues that may arise.

  4. Click Activate.

Add to orchestration

Use the Orchestration option to define a schedule for a query.

To add a query to an orchestration

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

    Tip

    The query does not need to be in edit mode to configure an orchestration.

  2. Under Being Sent To click Add one now. This opens the Add Orchestration dialog box.

  3. Follow the steps to add an orchestration. The steps will vary depending on the destination, the data template, and the orchestration.

  4. Click Save.

Autocomplete table names

Start typing table names in the SQL Query Editor and a list of available tables will appear. Use an arrow key to select a table, which autocompletes the rest of the table name.

Browse tables and columns

A docked mode for the data explorer is available from the Queries tab when building queries. This mode of the data explorer shows a list of tables in the customer 360 database, and then for each table a list of columns and data types. This mode provides a useful quick reference for tables, columns, and data types as you are building SQL queries.

To browse tables and columns

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

    Tip

    The query does not need to be in edit mode to browse tables and columns.

  2. Under Database, select a database from the drop-down menu. The list of tables is updated to show the tables in that database.

  3. Under Table, expand the name of a table. Details include the number of records in the table, a list of columns, and for each column its data type.

Comment lines

Use comments to mark out lines that document how the SQL query is designed or to keep lines that may be useful in the query within the query, but not in a state where they are run as part of the query.

To comment lines in a SELECT statement

  1. From the SQL Query Editor, select one or more lines of SQL.

  2. Next to Database, click Comment.

Copy column names

You can copy the name of a column from the list of tables available to the SQL Query Editor. Use this to type fewer characters and to ensure that columns with long names are added to the query accurately.

To copy a column name

  1. From the Queries tab, open the menu for a SQL query, and then select Edit. This opens the SQL Query Editor.

  2. Under Database, select a database from the drop-down menu. The list of tables is updated to show the tables in that database.

  3. Under Table, expand the name of a table.

  4. Click a column name to copy it.

  5. Paste the column name into SQL query.

Copy SELECT statement

When adding a table, it’s often necessary to reference many columns within that table. You can copy the SELECT statement for any table to use as a starting point for a table.

Tip

Use the copy SELECT statement functionality as a way to leverage existing queries that are similar to your use case as a template when adding queries. Be sure to review the SELECT statement and make any necessary updates for your new use case.

To copy a SELECT statement

  1. From the Queries tab, open the menu for a SQL query, and then select Edit. This opens the SQL Query Editor.

  2. Under Database, select a database from the drop-down menu. The list of tables is updated to show the tables in that database.

  3. Under Table, to the left of the table name click the icon to copy the SELECT statement.

  4. Paste the SELECT statement into SQL query.

Delete query

Use the Delete option to remove a query from Amperity. This should be done carefully. Verify that both upstream and downstream processes no longer depend on this query prior to deleting it.

To delete a query

  1. From the Queries tab, open the menu for a query, and then select Delete. The Delete Query dialog box opens.

  2. Click Delete.

Discard a draft query

While a query is in draft mode it may be discarded. An active query that is opened for editing automatically creates an instance of that query as a draft query with “–Draft” appended to the name. Keep the Queries tab free of long-lived draft queries as much as possible.

To discard a draft query

From the Queries tab, open the menu for a query with “– DRAFT” appended to the name, and then select Discard.

Warning

Discarding draft queries does not open a confirmation dialog box. Discarded draft queries are removed immediately.

Download query

You can download query results as a CSV file. The CSV format is supported by many applications, which makes the format a great way to test the potential of orchestrating queries for downstream applications and workflows.

Note

You cannot download the results of any query that returns an error.

To download a query as a CSV file

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

  2. Click Run Query to run the query. Wait for it to return results.

  3. Click Download.

  4. A CSV file with a filename that is identical to the query name is downloaded to your local machine.

Edit query

Use the Edit option in the row for a specific query to make changes. A very common scenario for editing a query is to update a query after new data has been added to Amperity. Changes to queries are saved automatically as a draft query. Any changes made to the query must be re-activated before they can be added to a scheduled workflow.

Note

When an active query is edited Amperity creates a copy of it and appends “–Draft” to the name. The query must be reactivated to apply changes and upon reactivation it will replace the active query.

To edit a query

  1. From the Queries tab, open the menu for a query, and then select Edit. This opens a query editor with the query labled a draft query.

  2. Make your changes.

  3. Click Run Query and debug any issues that may arise.

  4. Click Activate.

Enable performance mode

You may enable performance mode for any query that returns large result sets. Performance mode wraps a CREATE TABLE operation around the SELECT statement in the query, writes the results to an Apache Parquet file, and then makes that file available to Amperity as a table.

Performance mode is significantly faster than running a query in non-performance mode, but has the following limitations:

  • Sort order is not guaranteed because results are written in parallel to many files.

  • The UNION clause requires data types to match exactly.

  • Column names cannot contain spaces.

  • Column names are output in lowercase.

  • Small queries may take longer.

To enable performance mode

  1. From the Queries tab, open a query.

  2. Under Query Settings, select the Enable performance mode checkbox.

  3. Click Activate.

Enable query alerts

Any query can be set to trigger an alert or to fail based upon a configured threshold. This is useful as a way to ensure specific queries never orchestrate inaccurate or incomplete data. Additionally, this feature can be used to write “tests” against underlying databases to ensure specific conditions are met. For instance, a single loyalty ID should never be associated with multiple Amperity IDs. This test can be written as SQL, and alert or abort thresholds can be set so that we immediately know if this condition ever occurs.

Note

When query alerts are configured, alerts are triggered within Amperity, are sent to the #query-alerts Slack channel, and (for SLA queries) pages Tier 1 on-call.

There are two alert levels:

  1. Warn posts to #query-alerts Slack channel.

  2. Error stops the workflow, pages Tier1 on-call if SLA tenant, and posts to #query-alerts Slack channel.

To enable query alerts

  1. From the Queries tab, open the menu for a query, and then select Edit. This opens a query editor with the query labled a draft query.

  2. Select the Enable query alerts checkbox.

  3. Specify the threshold for the number of records over which notifications are sent to the #query-alerts channel in Amperity Slack.

  4. Specify the threshold for the number of records over which the job is cancelled, Tier-1 on-call support is paged, and notifications are sent to the #query-alerts channel in Amperity Slack.

  5. Click Activate. The query will run automatically when upstream data changes and alerts will be sent when thresholds are exceeded.

Format SQL

You can format the SQL from the SQL Query Editor. Click the Format button next to Databases under the query window. This will apply standardized formatting to the entire query.

Open query

You can view details for any query, including the SQL query associated with the query, along with any refresh, SLA, or alert settings, and configuration details for destinations, data templates, and orchestrations.

To open a query (view-only)

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor with the query as view-only.

  2. Click Edit to make changes. (This will change the query to a draft query, which will require re-activation when you are done making changes.)

Organize queries

A folder helps you organize the list of segments in the Queries tab. Up to three levels may be added.

You can organize the queries shown in the Queries tab:

Add folder

Folders may be expanded (or collapsed) to view (and hide) the list of queries and subfolders contained within.

To add a folder

  1. From the Queries tab click Create, and then select Add Folder. This opens the Create Folder dialog box.

  2. Enter the name for the folder.

  3. Click Save.

Add subfolder

Use the Create folder option in the menu to add up to three levels of subfolders. All folder names must be unique.

To add a subfolder

  1. From the Queries tab, open the menu for a folder, and then select Create folder. This opens the Create Folder dialog box.

  2. Enter the name for the folder.

  3. Click Save.

Move query

Use the Move option to move around and organize the list of folders and queries. Folders may be expanded (or collapsed) to view (and hide) the list of queries and subfolders contained within.

To move a query

  1. From the Queries tab, open the menu for a query, and then select Move. This opens the Move Query dialog box.

  2. Select the name of an existing folder to which a query will be moved, and then click Move.

Hint

If the folder to which a query will be moved is not present in the list of folders, you can add it directly from the Move Query dialog box. Click the + New folder link, type a name for the folder, and then select it.

Preview results

You can preview the results of a SQL query by clicking the Run Query button. This will do one of the following things:

  • Return the first 100 results of the query to the preview pane directly below the query editor.

  • Return an empty table.

  • Return some type of error.

Use the preview results pane to fine-tune your queries, to make sure they return the data you want, and to make sure they don’t contain any errors.

Tip

SQL queries in the Query Editor often evaluate millions of records and can take a few minutes to run. You may use other areas of Amperity while a query is being run.

For draft queries, setting a LIMIT 100 while developing a query is often enough to test and validate query design against very large data sets.

To preview query results

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

  2. Click Run Query to run the query. Wait for it to return results.

  3. Example the columns and the data that is in them.

  4. Adjust your query as necessary until it runs correctly.

  5. Click Activate.

Refresh query automatically

A query may be configured to be refreshed automatically. When enabled, an active query will be run automatically whenever an upstream process is refreshed.

To refresh a query automatically when upstream data changes

  1. From the Queries tab, open the menu for a query, and then select Edit. This opens a query editor with the query labled a draft query.

  2. Under Query Settings, select the Refresh automatically checkbox. (This option is required to enable query alerts.)

  3. Click Activate. The query will run automatically when upstream data changes.

Rename query

Use the Rename option to rename a query. This should be done carefully. Verify that both upstream and downstream processes no longer depend on this query prior to renaming it.

To rename a query

  1. From the Queries tab, open the menu for a query, and then select Edit. This opens a query editor with the query labled a draft query.

  2. In the name field, type the new query name.

  3. Click Activate.

Run query

Amperity processes data in batches on automated schedules that start with collecting the data, bringing it into the system, creating the customer 360 database, and then sending the results to any number of downstream workflows.

To run a query

  1. From the Queries tab, open the menu for a query, and then select Run.

  2. After a few moments, a notification will appear in the Notifications list that reports the status of the query run.

Run as SLA query

A service level agreement (SLA) is condition in Amperity that guarantees that a process will run successfully. In the rare case where a process does not run successfully it is treated with the highest level of urgency by Amperity on-call systems and support engineers.

A query that is configured to run as an SLA query is guaranteed to run with every automated workflow and is actively monitored by Amperity.

Caution

A query should be thoroughly tested prior to configuring it to be run as an SLA query. Before configuring a query to run as an SLA query, be sure to:

  1. Verify that all upstream and downstream workflows are configured correctly.

  2. Verify the customer 360 database has all of the correct tables and columns necessary to support the desired query.

  3. Peer review the SQL query, if possible.

  4. Validate the query from the query editor to make sure that the results contain the desired data points.

  5. Configure a destination to receive the query results, run the destination manually, and then verify the destination received the query results.

To run as an SLA query

  1. From the Queries tab, open the menu for a query, and then select Edit. This opens a query editor with the query labled a draft query.

  2. Under Query Settings, select the Refresh automatically checkbox. This option is required to run a query as an SLA query.

  3. Select the Set as SLA query checkbox.

  4. Click Activate. The query will run automatically when upstream data changes.

Run selection

You can run part of a statement from the SQL Query Editor.

To run a selection of a SQL query

  1. From the Queries tab, open the menu for a SQL query, and then select Edit. This opens the SQL Query Editor with the SQL query labled a draft query.

  2. Within the SQL query, highlight part of the statement, and then selct Run selection.

    If the selected SQL is valid, Amperity will run it and return the results.

Select database

You can build a query against any database that is visible from the Customer 360 tab.

To select a database

  1. From the Queries tab click Create, and then select SQL Query. This opens the SQL Query Editor.

  2. Under Database, select a database. The Customer 360 database is selected by default.

  3. Build your query against the list of tables that are available in that database.

Uncomment lines

Some lines within a SQL query are commented out to prevent them from running as a normal part of the query. These lines may be useful when the query is run manually. Uncomment them before running the query.

To uncomment lines in a SELECT statement

  1. From the SQL Query Editor, select one or more lines of SQL that is commented out.

  2. Next to Database, click Uncomment.

Use keyboard shortcuts

The SQL Queries Editor supports the following keyboard shortcuts:

Action

Shortcut

Autocomplete

Click Control + Space at the same time to autocomplete.

Comment line

Click Command + / (forward slash) at the same time to comment out the line or selection.

Extend selection

Click Shift + Arrow at the same time to extend selection by word or line in the direction of the arrow key.

Format query

Click Command + L at the same time to apply default formatting to the query or selection.

Move to previous word

Click Control + Left Arrow at the same time to move to the previous word in the SQL query.

Move to next word

Click Control + Right Arrow at the same time to move to the next word in the SQL query.

Move to start of line

Click Command + Left Arrow at the same time to move to the start of a line in the SQL query.

Move to end of line

Click Command + Right Arrow at the same time to move to the end of a line in the SQL query.

Move to start of query

Click Command + Up Arrow at the same time to move to the beginning of a SQL query.

Move to end of query

Click Command + Down Arrow at the same time to move to the end of a SQL query.

Run current line of SQL query

Click Command + Shift + Enter at the same time to run the current line of a SQL query.

Run SQL query

Click Command + Enter at the same time to run a SQL query.

Run up to current line of SQL query

Click Command + Option + Enter at the same time to run the SQL query up to the current line.

View notifications

Notifications for the Queries tab appear after Amperity has processed a query and passed the results to a downstream process. Notifications typically indicate successful outcomes. Less often, notifications contain details for non-successful outcomes, such as failures related to upstream or downstream processes. If a notification is about a non-successful outcome, the details for why and what happened can be found in the notification itself. Click More to view the full notification. In some cases viewing the log files may be helpful. In many cases, fix the root cause of the non-successful outcome, and then re-run the process manually.

View query

You can view details for any SQL query, including the SQL query associated with the SQL query, along with any refresh, SLA, or alert settings, and configuration details for destinations, data templates, and orchestrations.

To view a SQL query

  1. From the Queries tab, open the menu for a SQL query, and then select View. This opens the SQL Query Editor with the SQL query as view-only.

  2. Click Edit to make changes. (This will change the query to a draft query, which will require re-activation when you are done making changes.)

View query details

Query details show the time at which the query last ran, when it was activated, to which database it’s associated, what type of workflow it has been assigned, the destination to which it’s being sent, and visual graph that shows trends over time.

To view query details

  1. From the Queries tab, from the menu for a query, click the right angle icon. This opens the details pane for that query.

  2. Click the name of the query to view the orchestration details. The View Orchestrations dialog box opens. You can fully manage the orchestration from this dialog box.

  3. Click Run now to run the query from the details pane.

View recently edited queries

Recently edited queries are sorted at the top of the Queries tab.