Narrative Query Language (NQL): Overview

Narrative Query Language (NQL) is a specialized query language designed for the Narrative Data Collaboration Platform. Use NQL to perform queries on datasets, forecast for available data, and more. 

Quickstart

Get started with NQL by running some simple queries:

Find rows containing gender='female':

EXPLAIN
SELECT
narrative.rosetta_stone."unique_id"."value",
narrative.rosetta_stone."hl7_gender"."gender"
FROM
narrative.rosetta_stone
WHERE
narrative.rosetta_stone."hl7_gender"."gender" = 'female'
AND narrative.rosetta_stone."event_timestamp" > '2023-10-01'
AND narrative.rosetta_stone._price_cpm_usd <= 2.0;

NQL Query Editor in Data Studio UI

The NQL Query Editor is integrated into the Data Studio UI. This feature allows you to write queries starting with the SELECT statement and execute them either as a forecast (EXPLAIN), or by creating a new dataset from the output of your query (CREATE MATERIALIZED VIEW).

Navigate to the NQL Query Editor here.

Datasets

NQL operates on one or more datasets. You can reference your own company's data by querying the special namespace schema company_data. Learn more about Datasets.

Each dataset must be made accessible via Access Rules. Access rules determine who can query a dataset and at what price. Learn more about Access Rules.

You need to have an access rule in place to query any dataset, even if you own the dataset.

Example:

SELECT
company_data."20"."Workout_Timestamp",
company_data."20"."Total_Output" AS output
FROM
company_data."20"

Rosetta Stone Attributes

The Narrative platform features a standardized global data attribute catalog called "Rosetta Stone." Elements of the Rosetta Stone catalog are organized into attributes, which can be simple attribute types such as numbers or strings, or more complex objects containing multiple properties.

When querying a Rosetta Stone attribute, you use the specialized namespace "rosetta_stone" and refer to the attribute by name.

Example:

SELECT narrative.rosetta_stone."event_timestamp"

When querying attributes that are of type object, you must refer to the child properties directly (you cannot select the attribute itself without referring to at least one property.)

Example:

SELECT narrative.rosetta_stone."unique_id"."value",
narrative.rosetta_stone."unique_id"."type"

You can query these attributes if the underlying dataset has been exposed to you by an appropriate Access Rule. Learn more about Rosetta Stone Attributes.

Access Rules

Access Rules regulate query permissions, specifying datasets, authorized users, queryable fields, pricing, and additional constraints. Learn more about Access Rules.

Identifiers

Reserved Fields

  • _price_cpm_usd: Specifies the maximum cost-per-mille (CPM) in US Dollars that you are willing to pay for 1000 rows of data. Setting this to 0 explicitly filters out access rules with a price, querying only free data. Omitting a CPM filter applies no filter, allowing targeting of data at any price.

Identifier Rules

  • Use double quotes for identifiers
  • NQL operates in "strict mode"; identifiers must be fully qualified

Query Features

Cost Control

Control query costs by filtering on unit price per thousand rows (_price_cpm_usd) or setting an overall budget for the query (LIMIT).

SELECT [...] WHERE [...table.column <filter_expression>] [_price_cpm_usd <= <price>] [LIMIT 100.50 USD PER CALENDAR_MONTH]

Setting _price_cpm_usd to 0 filters out access rules with a price, querying only free data. Omitting a CPM filter applies no filter, allowing targeting of data at any price.

Specifying LIMIT 0 USD PER CALENDAR_MONTH implies purchasing only data with zero cost, and if LIMIT is not included, a limit of 0 is assumed. To specify no budget constraint, use NO LIMIT.

Data and Cost Forecasting

Use EXPLAIN <query> to generate a forecast.

Forecasts return information about the amount of data that will be returned from running a query, as well as the price to execute the query itself and the cost of querying the data (if relevant).

Creating Materialized Views

In NQL, creating a materialized view not only caches query results but also creates a new, unique dataset within the Narrative Data Collaboration Platform. Unlike typical databases where materialized views are used to cache expensive query results for performance improvement, in NQL, these views result in a net new dataset.

Creating a materialized view effectively creates a new dataset with a unique name. Such datasets cannot ingest data from other sources. Data purchase costs may or may not be incurred when executing a query that materializes data as a new dataset, depending on the underlying query's access rules.

Example Query:

CREATE MATERIALIZED VIEW "sports_events"
DISPLAY_NAME = 'Sports Events View'
DESCRIPTION = 'This view contains data for sports events with a timestamp greater than 2023-10-05, where the sports team is not null, and the price CPM in USD is less than or equal to 2.0.'
EXPIRE = 'P90D'
STATUS = 'active'
TAGS = ('sports', 'events')
WRITE_MODE = 'append'
EXTENDED_STATS = 'all'
PARTITIONED_BY narrative.rosetta_stone.event_timestamp DAY
REFRESH_SCHEDULE = '@daily'
AS
SELECT
narrative.rosetta_stone.unique_id,
narrative.rosetta_stone.sports_team,
narrative.rosetta_stone.event_timestamp
FROM
narrative.rosetta_stone
WHERE
narrative.rosetta_stone.event_timestamp > '2023-10-05'
AND narrative.rosetta_stone.sports_team IS NOT NULL
AND narrative.rosetta_stone._price_cpm_usd <= 2.0
LIMIT
50 USD PER CALENDAR_MONTH

Metadata Options

The following metadata is applied to the dataset that is created as a result of the CREATE MATERIALIZED VIEW command. These settings may be mutable, so they are not stored in the query after the initial execution; after executing the command, these settings can be accessed and modified using the Dataset API.

DISPLAY_NAME: A user-friendly name for the materialized view.

DESCRIPTION: Provides a clear explanation of the view's content and purpose.

EXPIRE: Sets the expiration period for the view in ISO 8601 format. Default is to retain_everything. Allowed Values:

  • expireWhen > ISO PERIOD
  • retain_everything
  • expire_everything

STATUS: Indicates the current status of the view, such as 'active'.

TAGS: A set of identifiers for categorization or referencing, with '_nio_materialized_view' always included.

WRITE_MODE: Determines how new data is added to the view, either 'append' or 'overwrite'. Default is append.

EXTENDED_STATS: Specifies whether to include extended statistics ('all' or 'none'). Default is 'all'.

PARTITIONED_BY: Allows partitioning of the view for efficient querying, with the default partition always present. Additional partitions can be specified with the format <field> <transform>.

REFRESH_SCHEDULE: Defines the frequency of updates for the view. Accepts cron expressions or enums ('@hourly', '@daily', '@weekly', '@monthly', '@once'). 

Querying an Access Rule Directly

An access rule has two identifiers: an `access_rule_name` and an `access_rule_id`. Access rule names are human readable and must be created explicitly, while access rule ids are created automatically during the initial set up for each access rule. NQL supports querying access rules directly through `access_rule_name` and not `access_rule_id`.

NQL supports querying internal access rules (access rules on datasets in the same company seat) or external access rules (access rules on datasets in a different company seat) directly. Querying an access rule is the third method of querying datasets in NQL, in addition to the Rosetta Stone attribute catalog and dataset ids.

Querying Internal Access Rules

An access rule name is added after the company identifier. When querying data in your own company seat, an access rule name always follows `company_data`.
SELECT pd.hashed_emails
FROM company_data.access_rule_for_private_deal pd

Querying External Access Rules

An access rule name is added after the company identifier. When querying data in your own company seat, an access rule name always follows `company_slug`.
SELECT teams.baseball_teams
FROM company_slug.access_rule_unique_name_1 teams

Embedded Namespaces in NQL - _rosetta_stone

NQL supports attribute querying via the Rosetta Embedded Namespace. This namespace is facilitated by `_rosetta_stone`, a direct method to query Rosetta Stone attributes. `_rosetta_stone` acts as an attribute reference within the dataset or access rule. `_rosetta_stone` must follow either a dataset's `unique_name`, a dataset's `id`, or an access rule's `name`. In case of an absence of mappings or an incorrect attribute reference, the query will return an error.

Basic Usage

SELECT ds_identifier._rosetta_stone."attribute_name" AS alias_name
FROM dataset_source
  • ds_identifier: Alias or identifier for the dataset. A dataset can be referenced by its id or unique_name.
  • attribute_name: The name of the Rosetta Stone attribute to be mapped.
  • alias_name: An optional alias for the selected attribute.

Example with Single Dataset

    SELECT ds_123._rosetta_stone."event_timestamp" AS event_time
FROM company_data.ds_123 AS ds_123

Example with Joining Multiple Datasets

 SELECT
ds_123._rosetta_stone."attribute_1" AS attribute_from_a,
ds_456._rosetta_stone."attribute_2" AS attribute_from_b,
ds_123.email,
ds_456.username
FROM
company_data.ds_123 AS ds_123
JOIN
company_data.ds_456 AS ds_456
ON
ds_123.user_id = ds_456.user_id

In this example:

  • The first Rosetta Stone attribute (attribute_1) is being pulled from dataset ds_123.
  • The second Rosetta Stone attribute (attribute_2) is being pulled from dataset ds_456.

Example with Nested Properties

    SELECT
ds_123._rosetta_stone."nested"."attribute" AS nested_attribute
FROM
company_data.ds_123 AS ds_123
  • For nested properties, the same dot notation is used within the _rosetta_stone namespace.

Example with Filtering With Rosetta Attributes

SELECT
ds_123._rosetta_stone."unique_id"."value" AS id
FROM
company_data.ds_123 AS ds_123
WHERE
ds_123.id = 123
  • Here, the WHERE clause uses the Rosetta attribute unique_id.value from dataset ds_123 for filtering.

API and API References

API Services

See full details in our API Documentation

  • /nql/run: Executes the NQL query.
  • /nql/parse: Parses NQL syntax.
  • /nql/compile: Compiles the NQL query into the underlying SQL engine, but does not execute the comand.

See Also