Query acceleration SQL language reference

Query acceleration supports an ANSI SQL-like language for expressing queries over blob contents. The query acceleration SQL dialect is a subset of ANSI SQL, with a limited set of supported data types, operators, etc., but it also expands on ANSI SQL to support queries over hierarchical semi-structured data formats such as JSON.

SELECT Syntax

The only SQL statement supported by query acceleration is the SELECT statement. This example returns every row for which expression returns true.

SELECT * FROM table [WHERE expression] [LIMIT limit]

For CSV-formatted data, table must be BlobStorage. This means that the query will run against whichever blob was specified in the REST call. For JSON-formatted data, table is a "table descriptor." See the Table Descriptors section of this article.

In the following example, for each row for which the WHERE expression returns true, this statement will return a new row that is made from evaluating each of the projection expressions.

SELECT expression [, expression ...] FROM table [WHERE expression] [LIMIT limit]

You can specify one or more specific columns as part of the SELECT expression (for example, SELECT Title, Author, ISBN).

The following example returns an aggregate computation (For example: the average value of a particular column) over each of the rows for which expression returns true.

SELECT aggregate_expression FROM table [WHERE expression] [LIMIT limit]

The following example returns suitable offsets for splitting a CSV-formatted blob. See the Sys.Split section of this article.

SELECT sys.split(split_size)FROM BlobStorage

Data Types

When reading values from CSV-formatted data, all values are read as strings. String values may be converted to other types using CAST expressions. Values may be implicitly cast to other types depending on context. for more info, see Data type precedence (Transact-SQL).

Expressions

Referencing fields

For JSON-formatted data, or CSV-formatted data with a header row, fields may be referenced by name. Field names can be quoted or unquoted. Quoted field names are enclosed in double-quote characters ("), may contain spaces, and are case-sensitive. Unquoted field names are case-insensitive, and may not contain any special characters.

In CSV-formatted data, fields may also be referenced by ordinal, prefixed with an underscore (_) character. For example, the first field may be referenced as _1, or the eleventh field may be referenced as _11. Referencing fields by ordinal is useful for CSV-formatted data that does not contain a header row, in which case the only way to reference a particular field is by ordinal.

Operators

The following standard SQL operators are supported:

If data types on the left and right of an operator are different, then automatic conversion will be performed according to the rules specified here: Data type precedence (Transact-SQL).

The query acceleration SQL language supports only a very small subset of the data types discussed in that article. See the Data Types section of this article.

Casts

The query acceleration SQL language supports the CAST operator, according to the rules here: Data type conversion (Database Engine).

The query acceleration SQL language supports only a tiny subset of the data types discussed in that article. See the Data Types section of this article.

String functions

The query acceleration SQL language supports the following standard SQL string functions:

Here's a few examples:

Date functions

The following standard SQL date functions are supported:

• DATE_ADD
• DATE_DIFF
• EXTRACT
• TO_STRING
• TO_TIMESTAMP

Currently, all date formats of standard IS08601 are converted.

DATE_ADD function

The query acceleration SQL language supports year, month, day, hour, minute, second for the DATE_ADD function.

Examples:

DATE_ADD(datepart, quantity, timestamp)
DATE_ADD('minute', 1, CAST('2017-01-02T03:04:05.006Z' AS TIMESTAMP)

DATE_DIFF function

The query acceleration SQL language supports year, month, day, hour, minute, second for the DATE_DIFF function.

DATE_DIFF(datepart, timestamp, timestamp)
DATE_DIFF('hour','2018-11-09T00:00+05:30','2018-11-09T01:00:23-08:00') 

EXTRACT function

For EXTRACT other than date part supported for the DATE_ADD function, the query acceleration SQL language supports timezone_hour and timezone_minute as date part.

Examples:

EXTRACT(datepart FROM timestampstring)
EXTRACT(YEAR FROM '2010-01-01T')

TO_STRING function

Examples:

TO_STRING(TimeStamp , format)
TO_STRING(CAST('1969-07-20T20:18Z' AS TIMESTAMP),  'MMMM d, y')

This table describes strings that you can use to specify the output format of the TO_STRING function.

TO_TIMESTAMP function

Only IS08601 formats are supported.

Examples:

TO_TIMESTAMP(string)
TO_TIMESTAMP('2007T')

Aggregate Expressions

A SELECT statement may contain either one or more projection expressions or a single aggregate expression. The following aggregate expressions are supported:

MISSING

The IS MISSING operator is the only non-standard that the query acceleration SQL language supports. For JSON data, if a field is missing from a particular input record, the expression field IS MISSING will evaluate to the Boolean value true.

Table Descriptors

For CSV data, the table name is always BlobStorage. For example:

SELECT * FROM BlobStorage

For JSON data, additional options are available:

SELECT * FROM BlobStorage[*].path

This allows queries over subsets of the JSON data.

For JSON queries, you can mention the path in part of the FROM clause. These paths will help to parse the subset of JSON data. These paths can reference to JSON Array and Object values.

Let's take an example to understand this in more detail.

This is our sample data:

{
  "id": 1,
  "name": "mouse",
  "price": 12.5,
  "tags": [
    "wireless",
    "accessory"
  ],
  "dimensions": {
    "length": 3,
    "width": 2,
    "height": 2
  },
  "weight": 0.2,
  "warehouses": [
    {
      "latitude": 41.8,
      "longitude": -87.6
    }
  ]
}

You might be interested only in the warehouses JSON object from the above data. The warehouses object is a JSON array type, so you can mention this in the FROM clause. Your sample query can look something like this.

SELECT latitude FROM BlobStorage[*].warehouses[*]

The query gets all fields but selects only the latitude.

If you wanted to access only the dimensions JSON object value, you could use refer to that object in your query. For example:

SELECT length FROM BlobStorage[*].dimensions

This also limits your access to members of the dimensions object. If you want to access other members of JSON fields and inner values of JSON objects, then you might use a queries such as shown in the following example:

SELECT weight,warehouses[0].longitude,id,tags[1] FROM BlobStorage[*]

Sys.Split

This is a special form of the SELECT statement, which is available only for CSV-formatted data.

SELECT sys.split(split_size) FROM BlobStorage

Use this statement in cases where you want to download and then process CSV data records in batches. That way you can process records in parallel instead of having to download all records at one time. This statement doesn't return records from the CSV file. Instead, it returns a collection of batch sizes. You can then use each batch size to retrieve a batch of data records.

Use the split_size parameter to specify the number of bytes that you want each batch to contain. For example, if you want to process only 10 MB of data at a time, you're statement would look like this: SELECT sys.split(10485760)FROM BlobStorage because 10 MB is equal to 10,485,760 bytes. Each batch will contain as many records as can fit into those 10 MB.

In most cases, the size of each batch will be slightly higher than the number that you specify. That's because a batch cannot contain a partial record. If the last record in a batch starts before the end of your threshold, the batch will be larger so that it can contain the complete record. The size of the last batch will likely be smaller than the size that you specify.

See also

• Azure Data Lake Storage query acceleration
• Filter data by using Azure Data Lake Storage query acceleration