ATataccama
Reference data

Execute an SQL statement

Read published reference data with an SQL SELECT statement. Unlike the per-table filter endpoints, one statement can read several tables and join them on a condition of your choosing. The statement field describes the currently supported SQL.

This endpoint only reads data and never modifies it. Only SELECT statements are accepted. A statement reads the latest published version of each table it names. All tables are read from one consistent snapshot, even if a publish completes while the statement runs.

The response contains either the complete result or an error. Results are never truncated. A response holds at most 10000 rows: if the result would be larger, the statement is rejected, so add a LIMIT of 10000 or fewer, or narrow the statement. A LIMIT above 10000 is rejected before the statement runs, even if the statement would have returned fewer rows than that. A statement is also rejected if the response, or a single value in it, would be too large to return, or if the statement runs too long.

Check meta.next to see whether the response holds the whole result: when it is present, the result continues beyond this response. A result that does not fit within these limits is rejected rather than split across responses, so narrow the statement instead. meta.total is not returned.

A problem with the statement itself returns 400 Bad Request: invalid syntax, an unknown column, an unsupported construct, an evaluation error such as division by zero or a failed cast, a timeout, or a result that exceeds a size limit. The detail field explains the reason and, where applicable, names the size limit or the line and column in the statement. detail is a human-readable message: its wording can change, and no field in the response identifies the specific cause.

Your API client needs read permission on every table the statement names. If the request has no valid token, or the statement names a table you cannot read, the whole statement fails with 403 Forbidden. A table that does not exist returns the same 403 with the same detail as a table you cannot read, so the two cases are intentionally indistinguishable. Do not treat them differently.

post/reference-data/v1/sql/execute

Request

Request body

statementstring required

An SQL SELECT statement over published reference data.

Names. Refer to tables by their reference data table name and to columns by their attribute name. Both are matched case-insensitively. A result column is named by its alias, exactly as written. A result column without an alias takes the attribute's own name, not the spelling used in the statement. If a table is renamed, the statements that read it stop working.

Supported syntax

SELECT <item> [, <item>]...
FROM <table> [[AS] <alias>]
[ [INNER | {LEFT | RIGHT | FULL} [OUTER]] JOIN <table> [[AS] <alias>] ON <condition> ]...
[ WHERE <condition> ]
[ ORDER BY <item> [ASC | DESC] [, ...] ]
[ LIMIT <count> ]

A select item is a column or a scalar expression over columns and literals. A computed item must have an alias (AS); an alias on a plain column is optional. Result column names must be unique, compared case-insensitively, so alias at least one of two columns that would otherwise get the same name. Every result column must have a type, so a bare NULL is rejected; cast it instead, for example CAST(NULL AS VARCHAR). ORDER BY accepts a result column by name or by position, or any expression that could be a select item, including a column that is read but not selected. If a column name exists in more than one table in the statement, qualify it with the name or alias of its table.

Reference attributes. An attribute that references another table reads as the value of the attribute it points to in that table, with that attribute's data type. meta.columns therefore reports it as STRING, or whatever the referenced attribute's type is, never as an identifier such as a URN. This is the same value the record endpoints return for the attribute. Otherwise it behaves like any other attribute, and a reference that points to no record reads as null.

Expressions. An expression can use:

  • the comparison operators =, <>, <, <=, >, >=
  • the predicates IS NULL, IS NOT NULL, IN, NOT IN, LIKE, NOT LIKE, ILIKE, NOT ILIKE, each LIKE form optionally followed by ESCAPE '<character>'
  • the Boolean operators AND, OR, NOT
  • the arithmetic operators +, -, *, /, and unary + and -
  • the concatenation operator ||
  • CASE expressions
  • the functions CHARACTER_LENGTH, SUBSTRING, POSITION, UPPER, LOWER, TRIM, CONCAT, OVERLAY, GREATEST, LEAST, LPAD, RPAD, ABS, MOD, CEIL, FLOOR, ROUND, TRUNCATE, POWER, SQRT, EXP, SIGN, COALESCE, NULLIF, REGEXP_REPLACE, and CAST

CAST supports the target types VARCHAR, CHAR, SMALLINT, INTEGER, BIGINT, DECIMAL, FLOAT, REAL, DOUBLE, BOOLEAN, DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, and TIMESTAMP WITH LOCAL TIME ZONE. Casting to TIME or TIME WITH TIME ZONE is allowed in a condition but not in a select item, because a result column cannot have either type.

To use a date or time in an expression, cast an RFC 3339 string literal: a full-date for DATE, a full-time for TIME, a date-time for TIMESTAMP. Other formats are rejected, including relative words such as today and infinity values. Only a string literal can be cast this way; a date or time value can be cast to another date or time type without restriction.

How expressions are evaluated

These behaviors are worth knowing before you write an expression:

  • Dividing two whole numbers truncates the result: 7 / 2 is 3.
  • Division by zero is an error, and so is a result that overflows its type. INTEGER is 32-bit, LONG is 64-bit, and DOUBLE is binary64 floating point. Overflow never wraps around and never produces an infinity.
  • LIKE and ILIKE treat only % and _ as wildcards. To match one of them literally, add ESCAPE '<character>': the escape character makes the character after it literal, so 'a!_b' ESCAPE '!' matches a_b. The escape must be exactly one character.
  • CONCAT and || are not interchangeable. CONCAT ignores nulls and returns an empty string when all values are null. With ||, a single null makes the whole result null.
  • CAST to FLOAT produces the same double-precision type as CAST to DOUBLE.

Not supported. Anything outside the supported syntax is rejected, in particular SELECT *, aggregation and GROUP BY, window functions, subqueries, and set operations such as UNION. Expressions and joins can nest at most 256 levels deep.

Further restrictions

Three restrictions do not follow from the syntax:

  • A statement cannot read the same table twice, so a table cannot be joined to itself.
  • A table with effective dates enabled cannot be read, and an attribute that references such a table cannot be used. The rest of the table that contains that attribute can be read normally.
  • A reference attribute can only be used after its own table has been read. If b has a reference attribute ref, FROM a JOIN b ON b.ref = a.x is rejected and FROM b JOIN a ON b.ref = a.x is accepted.

Example request

{
  "statement": "SELECT c.country_code AS code,\n       c.country_name AS name,\n       r.region_name AS region\nFROM Countries c\nLEFT JOIN Regions r ON c.region_code = r.region_code\nWHERE c.population > 1000000\nLIMIT 100\n"
}

Response

The statement's result.

Example response

{
  "meta": {
    "next": "MjA=",
    "total": 150,
    "columns": [
      {
        "name": "region",
        "dataType": "STRING"
      }
    ]
  }
}

Changes

No recorded changes to this operation across all 1 revision of this API.