Skip to main content

Nuklai Nexus-Compatible SQL (NXSQL)

This tutorial explains how to write NXSQL that interacts correctly with Nuklai Nexus virtualized data lake systems where every dataset is scoped under a named lake. These systems layer logical naming and abstraction over the physical data, requiring strict referencing rules and some dialect-specific behaviors. Below we explore how to construct queries, reference tables and columns, apply functions, and avoid common pitfalls.

1. Table Referencing: Fully Qualified Paths Are Mandatory​

Unlike typical SQL environments, you cannot reference a table using just its name. You must include the lake name as a prefix to every table reference.

Invalid​

SELECT * FROM "orders"

Valid​

SELECT * FROM "sales_lake"."orders"

Always use "lake_name"."table_name" — double quotes required.

2. Column Referencing: Fully Qualify or Use Aliases​

Similarly, when referencing columns—especially in SELECT, WHERE, HAVING, ORDER BY, etc.—you must fully qualify both table and lake if not using aliases.

Invalid​

SELECT "orders"."amount" FROM "sales_lake"."orders"

Valid​

SELECT "sales_lake"."orders"."amount" FROM "sales_lake"."orders"

3. Use Aliases to Avoid Verbosity​

To make queries more readable, you can assign an alias to a table using the AS keyword.

SELECT o."amount"
FROM "sales_lake"."orders" AS o
WHERE o."amount" > 100

Aliases are your friend in this dialect—use them liberally to clean up long lake-prefixed names.

4. Double Quotes Are Not Optional​

You must quote all identifiers (lake names, table names, and column names) using double quotes.

Invalid​

SELECT sales_lake.orders.amount FROM sales_lake.orders

Valid​

SELECT "sales_lake"."orders"."amount" FROM "sales_lake"."orders"

Forgetting quotes will result in errors or misinterpreted names.

5. Table Joins​

When joining tables from the same or different lakes, every table must be fully qualified.

SELECT c."name", o."amount"
FROM "sales_lake"."customers" AS c
JOIN "sales_lake"."orders" AS o ON c."id" = o."customer_id"

Joins across lakes:

SELECT c."name", o."amount"
FROM "crm_lake"."customers" AS c
JOIN "sales_lake"."orders" AS o ON c."id" = o."customer_id"

6. Filtering with WHERE, HAVING​

All filters referencing columns must use the full qualification or rely on aliases.

SELECT o."amount"
FROM "sales_lake"."orders" AS o
WHERE o."amount" > 100

7. GROUP BY, ORDER BY​

Same rules apply here. If you're not using aliases in your SELECT, you must fully qualify the columns.

SELECT c."region", COUNT(*) AS order_count
FROM "sales_lake"."customers" AS c
JOIN "sales_lake"."orders" AS o ON c."id" = o."customer_id"
GROUP BY c."region"
ORDER BY order_count DESC

8. Subqueries and CTEs​

When nesting queries, fully qualify within each scope, but use aliases to simplify the outer queries.

WITH recent_orders AS (
  SELECT o."id", o."customer_id", o."amount"
  FROM "sales_lake"."orders" AS o
  WHERE o."order_date" >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
)
SELECT c."name", r."amount"
FROM recent_orders AS r
JOIN "sales_lake"."customers" AS c ON c."id" = r."customer_id"

9. Functions and Expressions​

Most standard SQL functions are supported: SUM, COUNT, AVG, DATE_SUB, etc. But remember:

  • Arguments must be properly qualified if they reference columns.
  • Avoid non-standard functions unless you know the engine supports them.

10. Unsupported Features / Gotchas​

This SQL dialect looks ANSI-ish, but watch for these gotchas:

Reserved Keywords as Identifiers​

Always quote names like user, group, order, etc.

INFORMATION_SCHEMA or SHOW TABLES​

Not typically supported. Use the lake's UI or metadata endpoints to explore structure.

DDL / DML Not Supported​

You can't run:

CREATE TABLE ...
INSERT INTO ...
UPDATE ...

These environments are read-only or virtualized for SQL. All data access is read-through or federated.

11. Nested Fields (if applicable)​

If your data has nested JSON-like fields (e.g. from Parquet or NoSQL sources), you can reference them using dot notation:

SELECT o."shipping_address"."city"
FROM "sales_lake"."orders" AS o

You can alias nested structures too if needed.

12. Tips & Best Practices​

  • Always start by aliasing your tables to avoid repetition.
  • Keep a consistent quoting convention: double quotes for everything.
  • When debugging errors, ensure every identifier is fully scoped to the lake.
  • Use LIMIT early during exploration to avoid large scans.
  • Be explicit over clever—these engines often skip optimizations if queries are ambiguous.

Example: Clean, Idiomatic Query​

SELECT 
  c."name", 
  COUNT(o."id") AS total_orders, 
  SUM(o."amount") AS revenue
FROM "sales_lake"."customers" AS c
JOIN "sales_lake"."orders" AS o ON c."id" = o."customer_id"
WHERE o."status" = 'completed'
GROUP BY c."name"
ORDER BY revenue DESC
LIMIT 10

If you follow these principles, your queries will be compatible with virtual data lake environments and avoid the common errors related to resolution, scoping, or syntax.