Getting Started with Queries

Analytic Queries let marketers and data scientists retrieve data from the Loyalty platform, typically for the purpose of using it within a Dashboard, or to export the results for use in some other system or platform. Common use cases for Queries include counting Members or Activity records that meet certain conditions.

Note: Creating Queries in Loyalty requires proficiency in writing SQL.

The Analytics Queries screen is used to view, create, and manage your Queries. To access the Analytics Queries screen, select Analytics from the top navigation menu, then select Settings > Queries from the side navigation menu.

For more details on how to search for a Query, see Search for a Query.

Execution Types

Loyalty supports multiple Query types, called Execution Types. Each Execution Type runs against a different data source, and is designed for specific use cases.

Trino

The Trino Execution Type is being rolled out to Loyalty clients on a region-by-region basis, as a replacement for the existing Hive Execution Type. Once Trino has been enabled in your region, the best practice recommendation is to use the Trino Execution Type for most Queries.

Trino is ANSI SQL compliant and differs from Hive Query Language with respect to query semantics and support for certain user-defined functions. For more information on Trino syntax, see the official Trino documentation.

Trino Queries execute against a dedicated data source that is optimized specifically for analytics. This data source includes Member and Activity data. For more information, see Trino / Hive Tables.

For information on user-defined functions available for the Trino Execution Type, see Trino / Hive User Defined Functions.

Hive

Hive Queries execute against a dedicated data source that is optimized specifically for analytics. This data source includes Member and Activity data. For more information, see Trino / Hive Tables.

For information on user-defined functions available for the Hive Execution Type, see Trino / Hive User Defined Functions.

Over the course of 2026, the engine used to execute Queries in Loyalty will change from one that processes Hive syntax, to one that expects Trino query syntax.

The roll out of the Trino Execution Type in Loyalty will be done by region, with Australia and Europe to be completed in calendar Q2. The North America region will follow later. When Trino is enabled, where possible, Trino syntax will be generated automatically for existing Hive queries. For cases where Hive queries include semantics that can not be automatically converted to Trino, your Zeta team will assist with reconfiguring the query using Trino. Queries used to support critical path reporting and export flows are recommended to be tested before transitioning to the Trino syntax.

To support this transition, the platform allows you to create parallel Hive and Trino versions of a single Query (see Define the Query Code for more information). This feature allows you to convert your existing Hive Query to the Trino syntax for testing and validation purposes, while still being able to toggle back to Hive if necessary. The platform also provides a built-in conversion tool for converting a Hive Query to the Trino syntax. For more information, see Convert Hive Query to Trino.

If you have a Query with both a Hive and a Trino version, you can toggle between the two versions, but only one version can be selected at a time. The system will always execute the currently selected Execution Type. You can identify the currently selected Execution Type by looking in the Query String section of the Definition tab on the Query Details screen. You can also toggle ALL Hive Queries to use their Trino Execution Type (or vice versa). See Switch Execution Types for Hive / Trino for more information.

SQL

Note: The SQL Execution Types should be used only with guidance from your Zeta team. Poorly written Queries can negatively impact your live, production database.

The SQL Execution Type executes against a transactional data source that includes information such as Responses, Challenges, Rewards, Gift Cards, and so forth.

Spark

Note: The Spark Execution Types should be used only with guidance from your Zeta team. Poorly written Queries can negatively impact your live, production database.

Spark SQL allows you to read and write data in a variety of structured formats, such as JSON, Hive Tables, and Parquet. Loyalty's real-time data is stored in Hbase, which Spark queries can access. All the Hbase data gets synchronized to Hive on a schedule throughout the day, so that queries and reports can access this data without impacting the performance of Hbase.

Exports

The platform supports only Standard Exports for exporting Queries. See Export Queries for more information.

You can also export a file containing the results of a Query. The platform supports the following options for exporting Query results:

Imports

The platform supports only Standard Imports for importing Queries. See Import Queries for more information.

You can also import a file containing the results that you want to use for a Query. See Create a Query for more information.

Create a Query Refresh a Query Export Query Results