`$sql`¶

Note

Beta

The support for SQL format queries is available as a Beta feature. The feature and the corresponding documentation may change at any time during the Beta stage.

$sql processes a SQL query of the data in a collection. It must be the first stage in the pipeline.

Syntax¶

{
  $sql: {
    statement: "<SQL-statement>",
    format: "jdbc",
    formatVersion: <version-number>,
    dialect: "mysql"
  }
}

Fields¶

Field	Type	Description	Necessity
`dialect`	string	SQL dialect used by the statement. Value must be `mysql`.	Required
`format`	string	Structure of the output documents. Value must be `jdbc`.	Required
`formatVersion`	int	Version of the requested output format. Atlas Data Lake supports the following versions for the jdbc output format: `1` `2` (latest) If omitted, defaults to the latest version of the requested output format.	Optional
`statement`	string	SQL query or command to run.	Required

Output¶

If the SQL statement is not a query or a command that returns a result set, the $sql stage does not return any documents.

The output differs based on the format and format version used in the query.

The value field value differs based on whether or not the result set is empty.

If the SQL statement is a query, the $sql stage returns one document per row in the SQL result set. Each document includes a single array field named values that contains documents representing the columns in the result set and their values. For example:

{
  "values": [
    {
      "database": "<databaseName>",
      "table": "<tableName>",
      "tableAlias": "<tableAlias>",
      "column": "<columnName>",
      "columnAlias": "<columnAlias>",
      "bsonType": "<bsonType>",
      "value": "<columnValue>"
    },
    ...
  ]
}

Field	Type	Description
`emptyResultSet`	boolean	Flag that indicates if the result set of the query is empty. Value is `true` if the result set of the query is empty. Atlas Data Lake returns this field only if the result set of the query is empty.
`values`	array of documents	Column metadata including column values.
`values[n].database`	string	Name of the database. For queries against `DUAL`, the field has a `null` value.
`values[n].table`	string	Name of the table. For computed columns, the field has a `null` value.
`values[n].tableAlias`	string	Alias for the table. If the query provides no alias, value is the same as the table name. For computed columns, the field has a `null` value.
`values[n].column`	string	Name of the column. For computed columns, the field has a `null` value.
`values[n].columnAlias`	string	Alias for the column. If the query provides no alias, value is the same as the column name. For computed columns, the field contains the column name.
`values[n].bsonType`	string	Type of value. See Supported BSON Types for more information.
`values[n].value`	BSON data type	Column value. Value is `null` if the result set of the query is empty.

Example¶

The following example shows the $sql syntax for querying a sampleDB.egData collection:

{
  $sql: {
    statement: "select * from egData limit 2",
    format: "jdbc",
    formatVersion: 1,
    dialect: "mysql",
  }
}

Give Feedback

← Retrieve Data Lake Query History Supported Features →

$sql¶

Syntax¶

Fields¶

Output¶

Example¶

`$sql`¶