Справочник по языку SQL для ускорения запросов
Ускорение запросов поддерживает язык ANSI, аналогичный SQL, для выражения запросов к содержимому больших двоичных объектов. Диалект SQL для ускорения запросов — это подмножество ANSI SQL с ограниченным набором поддерживаемых типов данных, операторов и т. д. Оно также расширяет ANSI SQL для поддержки запросов к иерархическим частично структурированным форматам данных, таким как JSON.
Синтаксис SELECT
Единственной инструкцией SQL, поддерживаемой ускорением запросов, является инструкция SELECT. В этом примере возвращается каждая строка, для которой выражение возвращает значение true.
SELECT * FROM table [WHERE expression] [LIMIT limit]
Для данных в формате CSV table должно быть BlobStorage. Это означает, что запрос будет выполняться для любого большого двоичного объекта, указанного в вызове функции REST. Для данных в формате JSON table является "дескриптором таблицы". См. раздел Дескрипторы таблицы в этой статье.
В следующем примере для каждой строки, для которой выражение WHERE возвращает значение true, эта инструкция вернет новую строку, созданную на основе вычисления каждого из выражений проекции.
SELECT expression [, expression ...] FROM table [WHERE expression] [LIMIT limit]
Вы можете указать один или несколько отдельных столбцов в составе выражения SELECT (например, SELECT Title, Author, ISBN).
Примечание
В выражении SELECT можно использовать до 49 отдельных столбцов. Если вам нужно, чтобы результаты SELECT содержали более 49 столбцов, используйте в выражении SELECT подстановочный знак (*) (пример: SELECT *).
В следующем примере возвращается статистическое вычисление (например, среднее значение конкретного столбца) для каждой строки, для которой expression возвращает значение true.
SELECT aggregate_expression FROM table [WHERE expression] [LIMIT limit]
В следующем примере возвращаются подходящие смещения для разделения большого двоичного объекта в формате CSV. См. раздел Sys.Split этой статьи.
SELECT sys.split(split_size)FROM BlobStorage
Типы данных
| Тип данных | Описание |
|---|---|
| INT | 64-разрядное целое число со знаком. |
| FLOAT | 64-разрядное ("двойной точности") значение с плавающей запятой. |
| STRING | Строка переменной длины в Юникоде. |
| timestamp | Момент времени. |
| BOOLEAN | True или False. |
При чтении значений из данных в формате CSV все значения считываются как строки. Строковые значения могут быть преобразованы в другие типы с помощью выражений CAST. Значения могут быть неявно приведены к другим типам в зависимости от контекста. дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
Выражения
Ссылки на поля
Для данных в формате JSON или данных в формате CSV со строкой заголовка на поля можно ссылаться по имени. Имена полей можно заключать в кавычки или использовать без кавычек. Заключенные в кавычки имена полей заключаются в двойные кавычки (") и могут содержать пробелы, а также чувствительны к регистру. В именах полей без кавычек регистр не учитывается и они не могут содержать специальные символы.
В данных формата CSV на поля также можно ссылаться по порядковому номеру с префиксом в виде символа подчеркивания (_). Например, на первое поле можно ссылаться как на _1, либо на одиннадцатое поле можно ссылаться как на _11. Ссылки на поля по порядковому номеру удобно использовать для данных в формате CSV, которые не содержат строки заголовка. В этом случае единственный способ ссылки на конкретное поле — по порядковому номеру.
Операторы
Поддерживаются следующие стандартные операторы SQL.
| Оператор | Описание |
|---|---|
= |
Проверяет равенство двух выражений (оператор сравнения). |
!= |
Проверяет неравенство одного выражения другому (оператор сравнения). |
<> |
Сравнивает два выражения на верность того, не равно ли одно выражение другому (оператор сравнения). |
< |
Сравнивает два выражения на верность того, не меньше ли одно выражение другого (оператор сравнения). |
<= |
Сравнивает два выражения на верность того, не меньше ли или не равно ли одно выражение другому (оператор сравнения). |
> |
Сравнивает два выражения на верность того, не больше ли одно выражение другого (оператор сравнения). |
>= |
Сравнивает два выражения на верность того, больше или равно одно выражение другому (оператор сравнения). |
+ |
складывает два числа. С помощью этого арифметического оператора сложения можно также прибавлять число дней к дате. |
- |
Вычитает одно число из другого (оператор арифметического вычитания). |
/ |
Выполняет деление одного числа на другое (арифметический оператор деления). |
* |
Умножает два выражения (арифметический оператор умножения). |
% |
Возвращает остаток от деления одного числа на другое. |
AND |
Выполняет побитовую логическую операцию «И» между двумя целочисленными значениями. |
OR |
Выполняет побитовую логическую операцию OR для двух указанных целочисленных значений, которые преобразуются в двоичные выражения в инструкциях Transact-SQL. |
NOT |
Инвертирует входное логическое значение. |
CAST |
Преобразует выражение одного типа данных в другой. |
BETWEEN |
Определяет диапазон для проверки. |
IN |
Определяет, совпадает ли указанное значение с одним из значений, содержащихся во вложенном запросе или списке. |
NULLIF |
Возвращает значение NULL, если два указанных выражения равны. |
COALESCE |
Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL. |
Если типы данных слева и справа от оператора различаются, автоматическое преобразование будет выполняться в соответствии с указанными здесь правилами: Приоритет типов данных (Transact-SQL).
Язык SQL с ускорением запросов поддерживает только небольшое подмножество типов данных, обсуждаемых в этой статье. Дополнительные сведения см. в разделе Типы данных этой статьи.
Приведения
Язык SQL с ускорением запросов поддерживает оператор CAST в соответствии с правилами, приведенными здесь: Преобразование типов данных (ядро СУБД).
Язык SQL с ускорением запросов поддерживает только небольшое подмножество типов данных, обсуждаемых в этой статье. Дополнительные сведения см. в разделе Типы данных этой статьи.
Строковые функции
Язык SQL для ускорения запросов поддерживает следующие стандартные строковые функции SQL.
| Функция | Описание |
|---|---|
| CHAR_LENGTH | Возвращает длину строкового выражения в символах, если это строковое выражение имеет символьный тип данных; в противном случае возвращает длину строкового выражения в байтах (наименьшее целое число не меньше числа бит, деленного на 8). (Эта функция аналогична функции CHARACTER_LENGTH.) |
| CHARACTER_LENGTH | Возвращает длину строкового выражения в символах, если это строковое выражение имеет символьный тип данных; в противном случае возвращает длину строкового выражения в байтах (наименьшее целое число не меньше числа бит, деленного на 8). (Эта функция аналогична функции CHAR_LENGTH.) |
| LOWER | Возвращает символьное выражение после преобразования символов верхнего регистра в символы нижнего регистра. |
| UPPER | Возвращает символьное выражение, в котором символы нижнего регистра преобразованы в символы верхнего регистра. |
| SUBSTRING | Возвращает часть символьного, двоичного, текстового или графического выражения в SQL Server. |
| TRIM | Удаляет символ пробела (32) или другие заданные символы в начале и конце строки. |
| LEADING | Удаляет символ пробела (32) или другие заданные символы в начале строки. |
| TRAILING | Удаляет символ пробела (32) или другие заданные символы в конце строки. |
Вот несколько примеров.
| Функция | Пример | Результат |
|---|---|---|
| CHARACTER_LENGTH | SELECT CHARACTER_LENGTH('abcdefg') from BlobStorage |
7 |
| CHAR_LENGTH | SELECT CHAR_LENGTH(_1) from BlobStorage |
1 |
| LOWER | SELECT LOWER('AbCdEfG') from BlobStorage |
abcdefg |
| UPPER | SELECT UPPER('AbCdEfG') from BlobStorage |
ABCDEFG |
| SUBSTRING | SUBSTRING('123456789', 1, 5) |
23456 |
| TRIM | TRIM(BOTH '123' FROM '1112211Microsoft22211122') |
Microsoft |
Функции данных
Поддерживаются следующие стандартные функции даты SQL:
DATE_ADDDATE_DIFFEXTRACTTO_STRINGTO_TIMESTAMP
На данный момент все форматы даты стандартного IS08601 преобразованы.
Функция DATE_ADD
Язык SQL для ускорения запросов поддерживает год, месяц, день, час, минуту, секунду для функции DATE_ADD.
Примеры:
DATE_ADD(datepart, quantity, timestamp)
DATE_ADD('minute', 1, CAST('2017-01-02T03:04:05.006Z' AS TIMESTAMP)
Функция DATE_DIFF
Язык SQL для ускорения запросов поддерживает год, месяц, день, час, минуту, секунду для функции DATE_DIFF.
DATE_DIFF(datepart, timestamp, timestamp)
DATE_DIFF('hour','2018-11-09T00:00+05:30','2018-11-09T01:00:23-08:00')
Функция EXTRACT
Для функции EXTRACT, отличной от даты, поддерживаемой функцией DATE_ADD, язык SQL с ускорением запросов поддерживает timezone_hour и timezone_minute в качестве части даты.
Примеры:
EXTRACT(datepart FROM timestampstring)
EXTRACT(YEAR FROM '2010-01-01T')
Функция TO_STRING
Примеры:
TO_STRING(TimeStamp , format)
TO_STRING(CAST('1969-07-20T20:18Z' AS TIMESTAMP), 'MMMM d, y')
В этой таблице описаны строки, которые можно использовать для указания формата выходных данных функции TO_STRING.
| Строка форматирования | Выходные данные |
|---|---|
| yy | Год в формате 2 цифр — 1999 как "99" |
| y | Год в формате 4 цифр |
| гггг | Год в формате 4 цифр |
| M | Месяц года — 1 |
| ММ | Месяц с добавлением ноля — 01 |
| MMM | Сокр. месяц года — янв. |
| ММММ: | Полный месяц — май |
| d | День месяца (1-31) |
| дд | День месяца с добавлением ноля (01-31) |
| а | До или после полудня |
| h | Час дня (1-12) |
| hh | Час дня с добавлением ноля (01-12) |
| H | Час дня (0-23) |
| HH | Час дня с добавлением ноля (00-23) |
| m | Минута часа (0-59) |
| ММ | Минута часа с добавлением ноля (00-59) |
| s | Секунда минуты (0-59) |
| сс | Секунда минуты с добавлением ноля (00-59) |
| S | Доля секунды (0,1-0,9) |
| SS | Доля секунды (0,01-0,99) |
| SSS | Доля секунды (0,001-0,999) |
| X | Смещение в часах |
| XX или XXXX | Смещение в часах и минутах (+ 0430) |
| XX или XXXX | Смещение в часах и минутах (-07:00) |
| x | Смещение в часах (7) |
| xx или xxxx | Смещение в часах и минутах (+ 0530) |
| Xxx или xxxxx | Смещение в часах и минутах (+05:30) |
Функция TO_TIMESTAMP
Поддерживаются только форматы IS08601.
Примеры:
TO_TIMESTAMP(string)
TO_TIMESTAMP('2007T')
Примечание
Можно также использовать функцию UTCNOW для получения системного времени.
Статистические выражения
Инструкция SELECT может содержать либо одно, либо несколько выражений проекции или одно статистическое выражение. Поддерживаются следующие статистические функции.
| Выражение | Описание |
|---|---|
| COUNT(*) | Возвращает количество записей, соответствующих выражению предиката. |
| COUNT(expression) | Возвращает число записей, для которых выражение не имеет значение null. |
| AVG(выражение) | Возвращает среднее для значений, отличных от NULL. |
| MIN(expression) | Возвращает минимум для значений, отличных от NULL. |
| MAX(expression | Возвращает максимум для значений, отличных от NULL. |
| SUM(expression) | Возвращает сумму всех значений, отличных от NULL. |
MISSING
Оператор IS MISSING является единственным нестандартным, поддерживаемым языком SQL для ускорения запросов. Если в данных JSON отсутствует поле из определенной входной записи, то поле выражения IS MISSING будет иметь логическое значение true.
Дескрипторы таблиц
Для данных CSV имя таблицы всегда равно BlobStorage. Пример:
SELECT * FROM BlobStorage
Для данных JSON доступны дополнительные параметры:
SELECT * FROM BlobStorage[*].path
Это позволяет выполнять запросы к подмножествам данных JSON.
Для запросов JSON можно указать путь в части предложения FROM. Эти пути могут помочь при анализе подмножества данных JSON. Эти пути могут ссылаться на значения массива и объекта JSON.
Давайте рассмотрим пример подробнее, чтобы разобраться в этом.
Это наш пример данных:
{
"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
}
]
}
Возможно, вас интересует только объект JSON warehouses из указанных выше данных. Объект warehouses является типом массива JSON, поэтому его можно упомянуть в предложении FROM. Образец запроса может выглядеть примерно так.
SELECT latitude FROM BlobStorage[*].warehouses[*]
Запрос получает все поля, но выбирает только широту.
Если требуется получить доступ только к значению объекта JSON dimensions, можно использовать ссылку на этот объект в запросе. Пример:
SELECT length FROM BlobStorage[*].dimensions
Это также ограничивает доступ к членам объекта dimensions. Если требуется получить доступ к другим членам полей JSON и внутренним значениям объектов JSON, можно использовать запросы, как показано в следующем примере:
SELECT weight,warehouses[0].longitude,id,tags[1] FROM BlobStorage[*]
Примечание
BlobStorage и BlobStorage[*] ссылаются на весь объект. Однако если в предложении FROM есть путь, то необходимо использовать BlobStorage[*].path
Sys.Split
Это специальная форма инструкции SELECT, которая доступна только для данных в формате CSV.
SELECT sys.split(split_size) FROM BlobStorage
Используйте эту инструкцию в тех случаях, когда необходимо загрузить и затем обработать записи данных в формате CSV в пакетах. Таким образом, можно обрабатывать записи параллельно, вместо того чтобы загружать все записи за один раз. Эта инструкция не возвращает записи из CSV-файла. Вместо этого он возвращает коллекцию размеров пакетов. Затем можно использовать каждый из размеров пакетов для получения пакета записей данных.
Используйте параметр split_size, чтобы указать число байтов, которое должен содержать каждый пакет. Например, если вы хотите обрабатывать только 10 МБ данных одновременно, то оператор будет выглядеть следующим образом: SELECT sys.split(10485760)FROM BlobStorage так как 10 МБ равно 10 485 760 байт. Каждый пакет будет содержать столько записей, сколько может уместиться в 10 МБ.
В большинстве случаев размер каждого пакета будет немного выше указанного числа. Это обусловлено тем, что пакет не может содержать частичную запись. Если последняя запись в пакете начинается до окончания порогового значения, пакет будет больше, чтобы он мог содержать всю запись. Размер последнего пакета, скорее всего, будет меньше указанного размера.
Примечание
Значение split_size должно быть не менее 10 МБ (10485760).