Одна из интерпретаций термина NoSQL - «Не только SQL». Является ли это фактическим утверждением или отчуждением, открытым для обсуждения. Однако никто не может отрицать, что все больше и больше нереляционных источников данных используются в различных средах. Это часто приводит к тому, что технологии SQL и NoSQL используются бок о бок, несмотря на различия как в структуре самих данных, так и в механизмах, которые хранят информацию.

Неизбежная проблема для традиционных разработчиков баз данных и администраторов баз данных - это затраты времени и усилий, необходимых для освоения нового набора технологий и любых новых языков, необходимых для обработки данных NoSQL. К счастью для разработчиков SQL (и, действительно, для любого, кто имеет хорошее знание SQL), разработчики Cosmos DB позаботились о миллионах пользователей SQL, которым нужен путь в новый дивный мир NoSQL. Они достигли этого, предоставив SQL API для доступа к документам в формате JSON, хранящимся в базе данных Cosmos.

Этот подход дает одно мгновенное и всеобъемлющее преимущество для программистов на SQL Server (или других реляционных базах данных): вы мгновенно чувствуете себя как дома с основным языком, используемым для извлечения данных.

Однако документы JSON далеки от реляционных структур, а хранилища документов NoSQL - очень разные звери по сравнению с реляционными базами данных. Следовательно, SQL, используемый для запроса документов JSON, во многом отличается от обычного SQL, который вы используете с SQL Server. Кроме того, Cosmos DB SQL строго ограничен по сравнению с T-SQL. Тем не менее, несмотря на свои ограничения, API-интерфейс Cosmos DB SQL обеспечивает простой способ понимания и использования баз данных документов. Получение практических знаний о том, как запрашивать Cosmos DB, может открыть новые горизонты, которые позволят вам интегрировать данные из реляционного мира и мира NoSQL.

Основы

Cosmos DB - мультимодельная база данных NoSql. В настоящее время она может обрабатывать три типа нереляционных данных:

• Базы документов
• Графические базы данных
• Базы данных ключ-значение

Только одна из этих моделей данных может быть запрошена с использованием SQL в базе данных Cosmos. Это база данных документов. Здесь хотелось бы добавить, что Cosmos DB SQL касается только запросов к базам документов. Там не задействован DDL (язык определения данных).

База данных документов - это нереляционная база данных, предназначенная для хранения документов, которые не имеют фиксированной структуры. Ведущие базы данных документов используют JavaScript Object Notation (JSON) в качестве формата для структурирования данных. Хорошая новость относительно такого легкого и легко изменяемого подхода заключается в том, что вы не связаны жесткой схемой. Плохая новость заключается в том, что вы вообще не связаны никакой схемой. Эта текучесть имеет много преимуществ, но требует затрат при запросе данных JSON. Две основные проблемы, с которыми вы, вероятно, столкнетесь как разработчик SQL:

• Схема при чтении - вместо схемы базы данных, являющейся частью структуры базы данных, при написании запроса определяется любая необходимая структура.
• Вложенные структуры - документы JSON - это объекты, которые могут содержать полные данные, описывающие автономную единицу. Они могут быть чрезвычайно сложными и представлять структуру, для которой потребовался бы ряд таблиц в базе данных SQL Server.

К счастью, Cosmos DB SQL был адаптирован, чтобы помочь вам преодолеть проблемы, связанные с использованием языка запросов к реляционной базе данных для запроса нереляционных данных, хранящихся во вложенных структурах JSON, - как вы увидите в следующей статье.

Когда вам понадобится Cosmos DB SQL?

Перед тем, как перейти к деталям нового или, по крайней мере, немного другого диалекта языка программирования, давайте ответим на наверняка возникший у вас вопрос, зачем вам сначала нужно приложить усилия, чтобы освоить его. Несколько потенциальных причин:

• Cosmos DB может хранить документы JSON в действительно впечатляющем масштабе, что делает его отличным хранилищем при работе с терабайтами файлов JSON. Возможности SQL Server как хранилища документов JSON полностью защищены Cosmos DB.
• Cosmos DB может стать идеальным дополнением к SQL Server в качестве службы хранения JSON. Это связано с тем, что SQL Server предоставляет аналитические возможности, отсутствующие в базе данных Cosmos. На практике вы можете извлечь тонко отфильтрованные данные из огромного хранилища документов в базе данных Cosmos, а затем загрузить этот (бесконечно меньший набор данных) в таблицы SQL Server в качестве наборов строк для углубленного анализа. Это позволяет избежать необходимости внедрения совершенно другого набора технологий для предоставления аналитики через хранилища документов JSON.
• Вы можете использовать PolyBase в SQL Server 2019 для подключения к коллекциям Cosmos DB. В этом случае вам, вероятно, понадобится базовый SQL-файл Cosmos DB для выравнивания данных JSON, чтобы их можно было использовать в качестве внешней таблицы.
• Даже если вы анализируете данные JSON только в Cosmos DB, вам, вероятно, понадобится этот SQL-код Cosmos DB при написании хранимых процедур на основе JavaScript и пользовательских функций.
• Если вы подключаетесь к Cosmos DB с помощью ODBC, вам, вероятно, потребуется сгладить данные JSON. Такой подход неизбежно потребует использования Cosmos DB SQL.
• В качестве источника данных в действиях  Azure Data Factory можно использовать SQL API SQL Cosmos DB (и коллекции документов SQL поверх JSON).

Несомненно, есть много других причин, но возможно некоторые из вышеперечисленных побудят вас рассмотреть Cosmos DB.

Необходимый инструментарий

Вам нужно что-то из этого, чтобы практиковать SQL в Cosmos DB:

• Учетная запись Cosmos DB в Azure
• Cosmos DB Emulator.

В этой статье мы предполагаем, что вы используете эмулятор Cosmos DB, хотя и эмулятор, и Cosmos DB в Azure будут одинаково хорошо работать при обучении запросу документов JSON с использованием SQL.

Пример данных

Поскольку эта статья представляет собой постепенное введение в запросы к документам Cosmos DB, вы будете использовать простые документы JSON, хранящиеся в одной коллекции в Cosmos DB.

Структура JSON, которую вы можете использовать для отработки основ SQL-запросов с использованием Cosmos DB, полностью сглажена и выглядит следующим образом:

Использование примеров данных

Образцы документов находятся в прикрепленном zip-файле (CosmosDBQueries.zip), который необходимо установить на диск C: в каталоге C:\CosmosDB. Семь документов, из которых состоит коллекция, находятся в подкаталоге simplecars.

Первое, что нужно сделать, это создать коллекцию. Предлагаем использовать / CountryName в качестве ключа раздела в этом примере. Затем вам нужно загрузить документы. Поскольку их всего несколько, вы можете просто нажать кнопку New Document и вставить содержимое каждого файла в отдельный документ, а затем сохранить его.

Существует также опция загрузки для загрузки нескольких файлов, которые вы можете использовать вместо этого. Однако в данный момент в эмуляторе Cosmos DB это не работает.

Конечно, в реальном мире существуют решения, которые вам нужно будет изучить, если вы загружаете сотни тысяч документов и терабайты данных. Однако эти методы выходят за рамки этого поста.

Основная терминология

Для ясности, есть несколько основных определений, которые должны помочь вам преодолеть разрыв между SQL и миром документов JSON:

• Коллекцию можно считать базой данных
• Документ в целом приравнивается к набору записей (хотя вложенная структура делает его более похожим на XML)
• Атрибут - это поле или столбец

Эти точки сравнения не должны восприниматься слишком буквально, а просто предоставлены в качестве начальной ступеньки, чтобы помочь вам с первоначальным пониманием, если вы никогда раньше не видели базы данных документов.

Основные SQL-запросы

Начните с самого простого возможного примера. Запустив эмулятор Cosmos DB, введите и выполните следующий запрос:

SELECT * FROM C

Вы увидите все документы в текущей коллекции, возвращенные в качестве выходных данных, каждый из которых похож на образец документа, показанный выше.

Как бы ни был прост этот запрос, поскольку это первый запрос, который вы создали с помощью Cosmos DB SQL, мы последовательно прокомментируем это. Во-первых, данные возвращаются как JSON, даже если вы используете SQL для запроса документов. По сути, JSON является форматом данных. Второй момент заключается в том, что вы не можете указать коллекцию для запроса в SQL. Следовательно, вам нужно убедиться, что вы открываете окно запроса из требуемой коллекции. Однако вы можете использовать что угодно в качестве определения источника данных. Запрос может читать:

SELECT * FROM thecurrentcollectionbecauseIlikedreamingaboutcars

При выполнении простых запросов исходный JSON возвращается, поскольку он существует в коллекции. Вы узнаете, как формировать выходной JSON позже в этой статье и во второй статье этой серии.

И последнее замечание: технически вы также можете вернуть полный документ JSON с этим SQL:

SELECT * FROM ROOT

Теперь, для большей избирательности, попробуйте этот короткий запрос:

Это вывод, который вы должны увидеть следующим образом (здесь, очевидно, он является усеченным представлением вывода):

Выполнение этого запроса возвращает только два доступных атрибута JSON. Здесь стоит отметить, что вы должны использовать имя коллекции или псевдоним при обращении к атрибутам. Обычно проще использовать короткие псевдонимы для коллекции, например:

SELECT s.InvoiceNumber, s.TotalSalePrice  

FROM saleselements AS s

Ключевое слово AS используется, когда псевдонимы коллекций не является обязательными. Имена атрибутов чувствительны к регистру, хотя их неправильное написание не остановит выполнение запроса и предотвратит возвращение атрибута в вывод.

Конечно, вы можете добавить псевдонимы к атрибутам:

SELECT s.InvoiceNumber, s.SalePrice AS Price

FROM   saleselements AS s

Выполнение этого выдаст следующий JSON:

Однако, когда вы создаете псевдонимы для атрибутов, вы должны помнить, что:

• Ключевое слово AS является необязательным
• Псевдонимы чувствительны к регистру.
• Псевдонимы должны соответствовать соглашениям об именах JavaScript - вы не можете начинать псевдоним с цифры или другого не алфавитного символа, кроме знака доллара или подчеркивания

Вы не можете добавить псевдоним для *, поэтому вы не можете написать

SELECT c.* FROM c

Конечно, спецификация JSON называет атрибуты «members», а данные для каждого члена - элементом, но использование атрибута word для описания имени фрагмента данных настолько распространено, что желательно его использовать.

Cosmos DB SQL будет возвращать строки, числа и даты в точности так, как они хранятся в документе JSON - как вы можете увидеть, если попытаетесь выполнить этот запрос:

Конечно, Cosmos DB SQL может выполнять основную арифметику:

SELECT   s.InvoiceNumber

     ,s.SalePrice - s.Cost AS GrossProfit

FROM saleselements AS s

Как вы можете видеть ниже:

{

       "InvoiceNumber": "GBPGB011",

       "GrossProfit": 1700

}

В качестве варианта темы вы всегда можете написать предложения SELECT следующим образом:

Действительно, вы можете смешивать и сопоставлять обозначения «атрибута точки псевдонима» и обозначения «квадратная скобка и двойная кавычка псевдонима» (в этом случае точки, используемые для связи псевдонима и имени атрибута) внутри одного и того же запроса.

Простые операторы WHERE

Время перейти к выбору данных. В конце концов, Cosmos DB может хранить терабайты документов JSON и позволяет вам масштабировать запросы для использования большей или меньшей вычислительной мощности, чтобы вы могли сбалансировать требования времени и стоимости запроса (в финансовом смысле) для удовлетворения каждого отдельного требования.

Основной текстовый фильтр

Предположим, что вы хотите изолировать все счета, где был продан Bentley. Это не должно обременять ваши знания SQL, и, надеемся, убедит вас, что Cosmos DB SQL соответствует базовым принципам SQL:

Вы должны увидеть результат, подобный следующему:

Да, вы используете двойные кавычки, чтобы заключить в них строку. Это может стать неожиданностью для разработчиков SQL Server (хотя, возможно, не так для пользователей других баз данных). Справедливости ради, вы также можете использовать одинарные кавычки, если чувствуете, что иначе предаете свое наследие SQL.

Числовые фильтры

Фильтрация по числам в атрибутах JSON - это, вероятно, то, чего вы ожидаете. Вы можете написать условие WHERE, подобное этому, чтобы указать точную цифру:

Тогда результат будет следующим:

А теперь часть, которая сбивает с толку. Вы можете фильтровать поле и одновременно включать его в предложение SELECT, как показано ниже (хотя это будет указывать на достоверность фильтра в результатах):

Этот запрос даст следующий результат:

Чтобы завершить тему элементарных условий WHERE, вы также можете убедиться, что:

• Операторы AND, OR, NOT работают так же, как в T-SQL
• Операторы =,! =,> =, <=, <> позволяют указывать пороговые значения в запросах
• Вы можете использовать скобки для создания более сложной логики запросов

Алфавитные Диапазоны

Если вам когда-либо потребуется возвращать документы, основанные на алфавитном диапазоне в атрибуте, то одним из способов получения желаемого результата является использование такого вида SQL:

Фильтры даты и времени

Cosmos DB SQL допускает некоторые более продвинутые методы фильтрации в условии WHERE. Следует отметить, что хотя возможности значительно более ограничены, чем возможности, предлагаемые T-SQL, тем не менее можно включить (и исключить) нерелевантные документы JSON, отфильтровывая элементы даты и времени.

JSON не имеет типа даты как таковой. Вместо этого он использует строку. Следовательно, вы рискуете встретить даты в любом из множества форматов. В результате вы будете углубляться в определенный формат строки для фильтрации по датам и времени.

Формат в данных примера примерно соответствует формату ISO 8601, который выглядит следующим образом: ГГГГ-ММ-ДДЧЧ: ММ: СС. Это означает, что поиск определенной даты и времени означает представление даты и времени в виде строки ISO следующим образом:

Если вы хотите установить верхнюю или нижнюю границу для даты или времени, вы можете использовать стандартные операторы сравнения:> =, <=, <> или! =.

Использование диапазонов дат

Отсюда следует, что для определения диапазона дат не требуется ничего, кроме простого оператора AND в условии WHERE.

Это вернет данные из трех документов:

Или, возможно, более элегантно, вы можете использовать стандартную конструкцию BETWEEN … AND:

Это возвращает данные из тех же трех документов:

Применение фильтров времени

Фильтрация по времени, по сути, означает извлечение элементов часов, минут и, возможно, секунд или миллисекунд из строки даты. Итак, чтобы найти все автомобили, проданные в период с 8:45 до 12:00, вы должны написать:

Это должно вернуть данные из двух документов в коллекции:

Отсутствие схемы в документах JSON означает, что дата может быть указана в совершенно другом строковом формате или даже в виде числа (скажем, в формате ГГГГММДД, где приведенные выше данные будут 20150131).

Ясно, что необходимые запросы должны быть адаптированы к используемому формату даты, и в этом случае теоретический запрос для совершенно другой коллекции (где даты хранились по-разному) может выглядеть следующим образом:

Опять же, свободная форма JSON в означает, что вам придется проявлять изобретательность и ловкость ума в написанных вами запросах.

Ловушки для неосторожных

Cosmos DB SQL требует заключать нестандартные атрибуты в двойные кавычки (и квадратные скобки). Как мы упоминали выше, в этом случае для псевдонима коллекции не используется точечная система записи. Если вы хотите вывести один из встроенных атрибутов коллекции, вам нужно написать SQL следующим образом:

Стоит также отметить, что вы не можете использовать несанкционированные элементы, такие как начальные числа в псевдонимах, даже если вы заключаете псевдоним в двойные кавычки.

При фильтрации текстов, содержащих специальные символы, необходимо знать, что некоторые символы экранированы в Cosmos DB SQL. Следовательно, вот что вам нужно ввести, чтобы включить эти символы в предложение WHERE:

Одиночная цитата: \ ’

Двойная цитата: \ ”

Обратная косая черта: \\

Есть, конечно, много других, но мы позволим вам углубиться в документацию, чтобы найти их.

Теперь, когда основы были рассмотрены (и, надеемся, вы уверены, что ваши навыки SQL могут быть применены к базам данных документов), пришло время рассмотреть некоторые аспекты Cosmos DB SQL более подробно.

Сортировка вывода

Cosmos DB SQL также содержит условие ORDER BY, поэтому вы можете писать следующие запросы:

Вероятно, неудивительно, что вы увидите набор результатов, похожий на следующий:

Однако на данном этапе  на горизонте появляется ряд ограничений. Поскольку, хотя вы можете добавить ключевые слова ASC/DESC к условию ORDER BY (как вы можете в T-SQL), здесь применяются следующие ограничения:

• Ключевые слова ASCENDING и DESCENDING не поддерживаются
• Сортировка по нескольким атрибутам невозможна
• Вы не можете сортировать псевдоним для атрибута - вы должны ссылаться на атрибут, которому предшествует псевдоним коллекции и точка, как вы делаете в предложении SELECT
• Вы не можете применять ORDER BY к чему-либо кроме имени поля
• Вы не можете сортировать по вычисленному значению

Подводя итоги

Вот несколько примеров для общих агрегатов:

Нахождение количества документов в коллекции

Запрос на возврат количества документов в коллекции является небольшим расширением стандартного SQL:

Здесь вы добавляете ключевое слово VALUE, чтобы получить только число, а не структуру JSON. Следовательно, результат выглядит так:

[    7

]

Вы также можете написать этот запрос как:

[

   424950

]

Однако здесь появляются некоторые довольно глубокие ограничения (по крайней мере, с текущей версией Cosmos DB).

• Может быть только одна агрегация на запрос
• Псевдоним нельзя добавить к агрегации, если вы используете ключевое слово VALUE
• Вы получите сообщение об ошибке, если запрос представляет собой перекрестный запрос и вы не используете ключевое слово VALUE. Разделы Cosmos DB (как и следовало ожидать) представляют собой базовый подход к проектированию систем хранения и ускорения запросов. Однако создание разделов - это отдельная тема, в которую мы не можем сейчас углубляться.

Интересно, что вы можете обойти принудительное использование ключевого слова VALUE при выполнении запросов к разделам с использованием такого подзапроса, как этот:

В этом случае вывод выглядит примерно так:

[

   {

       "$1": 424950

   },

   {

       "$1": 0

   }

]

Возможно, вам интересно, откуда берутся имена атрибутов $1. Проще говоря, это происходит, когда вы не используете псевдоним для подзапроса или функции в Cosmos DB SQL. Представьте, что это похоже на столбцы без псевдонимов в T-SQL, которые отображаются как (без имени столбца) в выводе запроса.

Использование подзапроса также позволяет использовать несколько совокупных значений в расчете. В следующем примере вы можете увидеть разницу между максимальной и средней ценой продажи:

Как вы можете видеть, вы не обязательно должны задавать подзапрос:

Конечно, вы можете отфильтровать набор данных, к которому применяется расчет:

Это дает соответствующий вывод:

[

   77500

]

Как вы можете видеть здесь, применение ключевого слова VALUE возвращает только это значение, и, следовательно, в выводе отсутствует имя атрибута.

Формирование вывода

Cosmos DB SQL позволяет формировать выходные данные SQL-запроса несколькими способами - почти так же, как «обычный» SQL-запрос. Некоторые из методов, которые вы можете применить, описаны в этом разделе.

Возврат первых N документов

Cosmos DB SQL имитирует T-SQL, когда дело доходит до возврата только определенного количества документов из коллекции. Здесь даже не требуется показывать результат запроса.

Конкатенация атрибутов

Функция Cosmos DB SQL CONCAT() проста, но эффективна. Взгляните на следующий пример:

Эта функция позволяет объединять несколько полей и / или статических значений для создания одного выходного атрибута, например:

Замена значений в выводе

Если вам нужно заменить определенные значения в результирующем JSON, вы можете использовать функцию REPLACE(). И, да, вы можете использовать несколько вложенных операций замены, если вам нужно.

Вот соответствующий результат запроса (из конечных объектов, возвращаемых запросом):

Строковые функции - простая классика

Cosmos DB SQL содержит большинство основных строковых функций, которые вам могут потребоваться. Некоторые из них используются в следующем запросе:

Вот результат для одного документа:

Вы должны знать, что:

• SUBSTRING() основано на 0.
• Вы также можете использовать функции LTRIM () и RTRIM (), если вам нужно.

Финальная пара строковых функций

Cosmos DB SQL имеет всего несколько десятков строковых функций, и мы уже видели многие из наиболее полезных. Если они вам нужны, вы всегда можете добавить в свой арсенал следующее:

REPLICATE()

LENGTH()

INDEX_OF()

Математические функции

Cosmos DB SQL поставляется с базовым набором математических функций.

Взгляните на результат:

Они значительно более ограничены, чем их эквиваленты T-SQL, и выполняют только базовое округление.

Есть несколько других, таких как ABS (), SQRT (), SQUARE () плюс небольшой набор тригонометрических функций, если они вам понадобятся.

Логические (имитационные) операторы CASE - троичные функции

ANSI SQL имеет оператор CASE. Cosmos DB SQL вместо этого использует троичный логический подход. То есть он использует константы ? и : чтобы разбить код на элементы  test ? true : false.

Чтобы увидеть это в действии, взгляните на следующий фрагмент кода Cosmos DB SQL:

Этот код проверяет любой из Франции, Германии или Испании в атрибуте CountryName и возвращает EU, если какой-либо из этих элементов найден, и Other, если они не найдены, как вы можете видеть ниже:

К счастью, Cosmos DB SQL позволяет вам вкладывать троичную логику (как вы, возможно, сделали, используя функцию IF () в Excel или функции IIF ())  T-SQL.

Заключение

Теперь, когда вы знакомы с основами Cosmos DB SQL, я надеюсь, что у вас возникнет соблазн рассмотреть возможность использования этой мощной и масштабируемой базы данных документов в качестве потенциально изменяющего игру расширения вашей среды реляционных баз данных, и этот конкретный подход к NoSQL в качестве ценной части вашего набора навыков.

Мы не отрицаем, что SQL, реализованный поверх Cosmos DB, строго ограничен. Хотя вы часто можете найти обходные пути, чтобы устранить наихудшие из его ограничений, вы по существу застряли с простыми запросами SQL, которые используют SELECT, FROM, WHERE и ORDER BY. Например, вы не найдете ни одного предложения GROUP BY или HAVING, а запросы связаны с одной коллекцией. Его аналитические возможности ограничены отсутствием функций управления окнами.

С другой стороны, явная простота языка запросов, а также легкость, с которой вы можете адаптировать свои текущие навыки и научиться запрашивать документы JSON без необходимости изучать новую структуру и синтаксис, являются сильными сторонами в его пользу.

Однако мы еще не закончили с экскурсией по Cosmos DB SQL. Во второй статье будут описаны некоторые более продвинутые возможности языка, которые можно использовать для работы с более сложными структурами документов, которые допускает JSON. Эта статья также предоставит некоторые советы и рекомендации по обходу некоторых ограничений, присущих запросам документов JSON с помощью SQL.

No Comments