Knowledge Base

NQL Best Practices

Best Practices for NQL

Crafting efficient and understandable NQL queries is crucial for leveraging the Narrative Data Collaboration Platform effectively. These combined best practices ensure clarity, reduce ambiguity, and maintain a standard query structure.

1. Query Structure and Naming Conventions

When writing NQL queries, adhering to structured naming conventions and clear query design is essential. This includes:

  • Quoting Conventions: Always use double quotes (") around table names, dataset field names, attribute names, and attribute property names to adhere to NQL syntax rules and avoid potential conflicts with reserved keywords or special characters.
  • Fully Qualified Names: Refer to all tables by their fully qualified names, including the full namespaces, in the FROM clause to avoid ambiguity. For example, "narrative"."rosetta_stone" for Rosetta Stone datasets.
  • Table and Field Names Only in SELECT Statements: In the SELECT statement, avoid using the fully qualified name including the namespace (e.g. "company_data" or "rosetta_stone".) You can refer directly to the table name or attribute/field names. For queries involving multiple tables, alias the tables and use these aliases to refer to the fields in the SELECT statement.
  • Aliasing for Clarity: Alias table names rather than using their fully qualified names in the SELECT statement. This practice enhances readability and helps to clearly distinguish between different tables and fields, especially in complex queries involving JOIN operations.
  • No Ambiguity: Ensure there is no ambiguity when referring to fields, especially in queries involving multiple tables or JOINs. This includes using aliases effectively and choosing clear, descriptive names for these aliases.

Example Query

The following example query illustrates these combined best practices in action, querying Rosetta Stone datasets for user engagement metrics while adhering to the structured query design:

  rs."unique_id" AS "User ID", 
  COUNT(rs."event_id") AS "Total Events", 
  approx_count_distinct(sd."session_id") AS "Unique Sessions"
  "narrative"."rosetta_stone" AS rs
  "company_data"."user_sessions" AS sd ON rs."unique_id" = sd."user_id"
  rs."event_timestamp" > CURRENT_TIMESTAMP - INTERVAL '7' DAYS
  AND sd."session_duration" > INTERVAL '5' MINUTE
  "Total Events" DESC
LIMIT 100;

In this query:

  • We use fully qualified names for tables in the FROM and JOIN clauses, enhancing clarity and specificity.
  • The SELECT statement avoids namespace prefixes, directly referencing field names and employing aliases (rs for Rosetta Stone, sd for user sessions data).
  • Table and field aliases are used to simplify the SELECT, WHERE, and JOIN clauses, making the query easier to read and understand.
  • Double quotes are used around all field and alias names, adhering to NQL syntax rules.

By following these best practices, NQL queries become more structured, understandable, and efficient, facilitating clearer analysis and decision-making based on the data retrieved.

< Back

Hi! I’m Rosetta, your big data assistant. Ask me anything! If you want to talk to one of our wonderful human team members, let me know! I can schedule a call for you.