Поделиться через

Функции T-SQL, которые поддерживаются в Azure Synapse SQL

  • Статья

Azure Synapse SQL — это служба аналитики больших данных, которая позволяет выполнять запросы и анализ данных с помощью языка T-SQL. Вы можете использовать для анализа данных диалект SQL, соответствующий стандарту ANSI, который используется в SQL Server и Базе данных SQL Azure.

Язык Transact-SQL используется в бессерверном пуле SQL, а выделенная модель может ссылаться на разные объекты и имеет некоторые отличия в наборе поддерживаемых функций. На этой странице перечислены основные отличия языка Transact-SQL в разных моделях потребления Synapse SQL.

Объекты базы данных

Модели потребления в Synapse SQL позволяют использовать разные объекты базы данных. В следующей таблице представлено сравнение поддерживаемых типов объектов.

Object Выделенные Бессерверные приложения
Таблицы Да Нет, таблицы в базе данных не поддерживаются. Бессерверный пул SQL может запрашивать только внешние таблицы, которые ссылаются на данные, хранящиеся в Azure Data Lake Storage или Dataverse.
Представления Да. Представления могут использовать элементы языка запросов, доступные в выделенной модели. Да, можно создавать представления для внешних таблиц, запросы с функцией OPENROWSET и другие представления. Представления могут использовать элементы языка запросов, доступные в бессерверной модели.
Схемы Да Да, схемы поддерживаются. Используйте схемы для изоляции разных клиентов и размещения их таблиц на схемах.
Временные таблицы Да Временные таблицы могут использоваться только для хранения некоторых данных из системных представлений, литералов или других временных таблиц. Также поддерживается ОБНОВЛЕНИЕ и УДАЛЕНИЕ во временной таблице. К временным таблицам можно присоединить системные представления. Нельзя выбрать данные из внешней таблицы, чтобы вставить их во временную таблицу или соединить временную таблицу с внешней таблицей. Эти операции завершатся ошибкой, поскольку внешние данные и временные таблицы нельзя смешивать в одном запросе.
Определяемые пользователем процедуры Да Да, хранимые процедуры можно размещать в любых пользовательских базах данных (не в базе данных master). Процедуры могут просто считывать внешние данные и использовать элементы языка запросов, доступные в бессерверном пуле.
Определяемые пользователем функции Да Да. Поддерживаются только встроенные функции с табличным значением. Скалярные определяемые пользователем функции не поддерживаются.
Триггеры No Нет, бессерверные пулы SQL не допускают изменения данных, поэтому триггеры не могут реагировать на изменения данных.
Внешние таблицы Да. См. поддерживаемые форматы данных. Да, внешние таблицы доступны и могут использоваться для чтения данных из Azure Data Lake Storage или Dataverse. См. поддерживаемые форматы данных.
Кэширование запросов Да, несколько форм (кэширование на твердотельных накопителях, кэширование в памяти, кэширование ResultSet). Кроме того, поддерживаются материализованные представления. Нет, кэшируется только статистика файлов.
Кэширование результирующего набора Да Нет, результаты запроса не кэшируются. Кэшируется только статистика файлов.
Материализованные представления Да Нет, материализованные представления не поддерживаются в бессерверных пулах SQL.
Табличные переменные Нет, используйте временные таблицы Нет, табличные переменные не поддерживаются.
Распределение таблиц Да Нет, распределения таблиц не поддерживаются.
Индексы таблиц Да Нет, индексы не поддерживаются.
Секционирование таблиц в хранилище данных SQL Да. Внешние таблицы не поддерживают секционирование. Вы можете секционировать файлы, используя структуру папок Hive-partition, и создавать секционированные таблицы в Spark. Секционирование Spark будет синхронизировано с бессерверным пулом. Если вы не используете Spark, вы можете секционировать файлы в структуре папок и создавать секционированные представления в структуре секций папок, но внешние таблицы нельзя создавать в секционированных папках.
Статистика Да Да, статистика создается во внешних файлах.
Управление рабочими нагрузками, классы ресурсов и контроль параллелизма Да, см. Управление рабочими нагрузками, классы ресурсов и контроль параллелизма. Нет, управление ресурсами, назначенными запросам, невозможно. Бессерверный пул SQL автоматически управляет ресурсами.
Управление затратами Да, при использовании действий по вертикальному масштабированию. Да, вы можете ограничить ежедневное, еженедельное или ежемесячное использование бессерверного пула с помощью портала Azure или процедуры T-SQL.

Язык запросов

Языки запросов, используемые в Synapse SQL, могут поддерживать разный набор функций в зависимости от модели потребления. В следующей таблице приведены наиболее важные отличия в диалектах языка запросов для Transact-SQL:

Оператор Выделенные Бессерверные приложения
Инструкция SELECT Да. Инструкция SELECT поддерживается, но некоторые предложения запроса Transact-SQL, такие как FOR XML/FOR JSON, MATCH, OFFSET/FETCH, не поддерживаются. Да, инструкция SELECT поддерживается, но некоторые предложения запроса Transact-SQL, такие как FOR XML, MATCH, PREDICT, GROUPNG SETS и предложения запроса, не поддерживаются.
Инструкция INSERT Да № Отправка новых данных в Data Lake с помощью Spark или других средств. Используйте Azure Cosmos DB с аналитическим хранилищем для высоко транзакционных рабочих нагрузок. CETAS можно использовать для создания внешней таблицы и вставки данных.
Инструкция UPDATE Да Нет, обновите данные Parquet/CSV с помощью Spark, и изменения будут автоматически доступны в бессерверном пуле. Используйте Azure Cosmos DB с аналитическим хранилищем для высоко транзакционных рабочих нагрузок.
Инструкция DELETE Да Нет, удалите данные Parquet/CSV с помощью Spark, и изменения будут автоматически доступны в бессерверном пуле. Используйте Azure Cosmos DB с аналитическим хранилищем для высоко транзакционных рабочих нагрузок.
Инструкция MERGE Да (предварительная версия) Нет, объедините данные Parquet/CSV с помощью Spark, и изменения будут автоматически доступны в бессерверном пуле.
Инструкция CTAS Да Нет, инструкция CREATE TABLE AS SELECT не поддерживается в бессерверном пуле SQL.
Инструкция CETAS Да, можно выполнить начальную загрузку во внешнюю таблицу с помощью CETAS. Да, можно выполнить начальную загрузку во внешнюю таблицу с помощью CETAS. CETAS поддерживает форматы выходных данных Parquet и CSV.
Транзакции Да Да, транзакции применимы только к объектам метаданных.
Метки Да Нет, метки не поддерживаются в бессерверных пулах SQL.
Загрузка данных Да. Лучше всего использовать инструкцию COPY, но система поддерживает для загрузки данных как массовую загрузку (BCP), так и CETAS. Нет, вы не можете загружать данные в бессерверный пул SQL, поскольку данные хранятся во внешнем хранилище. Сначала вы можете загрузить данные во внешнюю таблицу с помощью инструкции CETAS.
Экспорт данных Да. Использование CETAS. Да. Вы можете экспортировать данные из внешнего хранилища (Azure Data Lake, Dataverse, Azure Cosmos DB) в озеро данных Azure с помощью CETAS.
Типы Да, все типы Transact-SQL, за исключением cursor, hierarchyid, ntext, text и image, rowversion, пространственных типов, sql_variant и xml Да, поддерживаются все типы Transact-SQL, за исключением cursor, hierarchyid, ntext, text и image, rowversion, пространственных типов, sql_variant, xml и табличного типа. Сведения о том, как сопоставлять типы столбцов Parquet с типами SQL см. здесь.
Запросы баз данных No Да, межбазовые запросы и ссылки на имена, состоящие из трех частей, включая инструкцию USE. Запросы могут ссылаться на бессерверные базы данных SQL или базы данных Lake в той же рабочей области. Запросы к нескольким рабочим областям не поддерживаются.
Встроенные или системные функции (анализ) Да, все функции аналитики, преобразования, даты и времени, логические и математические функции из Transact-SQL, кроме CHOOSE и PARSE. Да, поддерживаются все функции аналитики, преобразования, даты и времени, а также логические и математические функции, из Transact-SQL.
Встроенные или системные функции (строка) Да. Все функции Transact-SQL для работы со строками, JSON и параметрами сортировки, кроме STRING_ESCAPE и TRANSLATE. Да. Поддерживаются все функции Transact-SQL для работы со строками, JSON и параметрами сортировки.
Встроенные или системные функции (криптографические) Частично HASHBYTES является единственной поддерживаемой криптографической функцией в бессерверных пулах SQL.
Встроенные или системные функции с табличным значением Да, все функции наборов строк в Transact-SQL, кроме OPENXML, OPENDATASOURCE, OPENQUERY и OPENROWSET. Да, поддерживаются все функции наборов строк в Transact-SQL, кроме OPENXML, OPENDATASOURCE и OPENQUERY.
Встроенные или системные статистические функции Все встроенные статистические выражения Transact-SQL, за исключением CHECKSUM_AGG и GROUPING_ID. Да, поддерживаются все встроенные статистические функции Transact-SQL.
Операторы Да, все операторы Transact-SQL, кроме !> и !< Да, поддерживаются все операторы Transact-SQL.
Управление потоком Да. Все инструкции Transact-SQL для управления потоком, за исключением CONTINUE, GOTO, RETURN, USE и WAITFOR. Да. Поддерживаются все инструкции управления потоком Transact-SQL. Запрос SELECT в условии WHILE (...) не поддерживается.
Инструкции DDL (CREATE, ALTER, DROP) Да. Все инструкции DDL из Transact-SQL, применимые к поддерживаемым типам объектов. Да, поддерживаются все инструкции DDL из Transact-SQL, применимые к поддерживаемым типам объектов.

Безопасность

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

Функция Выделенные Бессерверные приложения
Имена входа Недоступно (в базах данных поддерживаются только автономные пользователи). Да, поддерживаются идентификаторы Microsoft Entra на уровне сервера и имена входа SQL.
Пользователи Недоступно (в базах данных поддерживаются только автономные пользователи). Да, пользователи базы данных поддерживаются.
Автономные пользователи Да. Примечание. Только один пользователь Microsoft Entra может быть неограниченным администратором Нет, автономные пользователи не поддерживаются.
Аутентификация по имени пользователя и паролю в SQL Да Да, пользователи могут получить доступ к бессерверному пулу SQL, используя свои имена пользователей и пароли.
Проверка подлинности Microsoft Entra Да, пользователи Microsoft Entra Да, имена входа Microsoft Entra и пользователи могут получить доступ к бессерверным пулам SQL с помощью удостоверений Microsoft Entra.
Сквозная проверка подлинности Microsoft Entra Да Да, сквозная проверка подлинности Microsoft Entra применима к именам входа Microsoft Entra. Удостоверение пользователя Microsoft Entra передается в хранилище, если учетные данные не указаны. Сквозная проверка подлинности Microsoft Entra недоступна для пользователей SQL.
Проверка подлинности маркера подписанного URL-адреса хранилища (SAS) No Да, используя УЧЕТНЫЕ ДАННЫЕ ДЛЯ БАЗЫ ДАННЫХ с маркером подписанного URL-адреса во ВНЕШНЕМ ИСТОЧНИКЕ ДАННЫХ или УЧЕТНЫХ ДАННЫХ на уровне экземпляра с подписанным URL-адресом.
Проверка подлинности по ключу доступа к хранилищу Да, с использованием DATABASE SCOPED CREDENTIAL в EXTERNAL DATA SOURCE. Нет, используйте маркер SAS вместо ключа доступа к хранилищу.
Проверка подлинности по управляемому удостоверению для службы хранилища Да, с использованием управляемого удостоверения службы. Да, запрос может получить доступ к хранилищу с помощью учетных данных управляемого удостоверения рабочей области.
Проверка подлинности удостоверения приложения хранилища или субъекта-службы (SPN) Да Да, можно создать учетные данные с идентификатором приложения субъекта-службы, которые будут использоваться для проверки подлинности в хранилище.
Роли сервера No Да, поддерживаются роли sysadmin, public и другие серверные роли.
УЧЕТНЫЕ ДАННЫЕ УРОВНЯ СЕРВЕРА No Да, учетные данные уровня сервера используются OPENROWSET функцией, которая не использует явный источник данных.
Разрешения — на уровне сервера No Да, например, CONNECT ANY DATABASE и SELECT ALL USER SECURABLES позволяют пользователю считывать данные из любых баз данных.
роли базы данных Да Да, можно использовать роли db_owner, db_datareader и db_ddladmin.
DATABASE SCOPED CREDENTIAL Да, используется во внешних источниках данных. Да, учетные данные уровня базы данных можно использовать во внешних источниках данных для определения метода проверки подлинности хранилища.
Разрешения — на уровне базы данных Да Да, можно предоставлять, отклонять или отзывать разрешения для объектов базы данных.
Разрешения — на уровне схемы Да, включая применение операций GRANT, DENY и REVOKE к разрешениям пользователей и (или) имен входа для схемы. Да, вы можете указать разрешения на уровне схемы, включая применение операций GRANT, DENY и REVOKE к разрешениям пользователей и (или) имен входа для схемы.
Разрешения — на уровне объектов Да, включая применение операций GRANT, DENY и REVOKE к разрешениям пользователей. Да, вы можете применять операции GRANT, DENY и REVOKE к разрешениям пользователей и (или) имен входа для поддерживаемых системных объектов.
Разрешения — защита на уровне столбцов Да Безопасность на уровне столбцов поддерживается в бессерверных пулах SQL для представлений, а не для внешних таблиц. В случае внешних таблиц можно создать логическое представление поверх внешней таблицы и применить безопасность на уровне столбцов.
Безопасность на уровне строк Да Нет, встроенная поддержка безопасности на уровне строк отсутствует. Используйте пользовательские представления в качестве обходного решения.
Маскирование данных Да Нет, встроенное маскирование данных не поддерживается в бессерверных пулах SQL. В качестве обходного пути используйте представления SQL-оболочки, которые явно маскируют некоторые столбцы.
Встроенные или системные функции безопасности и идентификации Некоторые функции и операторы безопасности Transact-SQL: CURRENT_USER, HAS_DBACCESS, IS_MEMBER, IS_ROLEMEMBER, SESSION_USER, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS, OPEN/CLOSE MASTER KEY Поддерживаются некоторые функции и операторы безопасности Transact-SQL: CURRENT_USER, HAS_DBACCESS, HAS_PERMS_BY_NAME, IS_MEMBER, IS_ROLEMEMBER, IS_SRVROLEMEMBER, SESSION_USER, SESSION_CONTEXT, SUSER_NAME, SUSER_SNAME, SYSTEM_USER, USER, USER_NAME, EXECUTE AS и REVERT. Функции безопасности нельзя использовать для запроса по внешним данным (сначала сохраните результат в переменной, чтобы использовать ее в запросе).
Прозрачное шифрование данных (TDE) Да Нет, прозрачное шифрование данных не поддерживается.
Обнаружение и классификация данных Да Нет, обнаружение и классификация данных не поддерживается.
Оценка уязвимостей Да Нет, оценка уязвимостей недоступна.
Расширенная защита от угроз Да Нет, расширенная защита от угроз не поддерживается.
Аудит Да Да, аудит поддерживается в бессерверных пулах SQL.
Правила брандмауэра Да Да, правила брандмауэра можно установить на конечной точке бессерверного SQL.
Частная конечная точка Да Да, частную конечную точку можно установить в бессерверном пуле SQL.

Выделенный и бессерверный пулы SQL используют для запрашивания данных стандартный язык Transact-SQL. Подробные сведения о различиях см. в справочнике по языку Transact-SQL.

Функции платформы

Функция Выделенные Бессерверные приложения
Масштабирование Да Бессерверный пул SQL автоматически масштабируется в зависимости от рабочей нагрузки.
Приостановка и возобновление Да Бессерверный пул SQL автоматически деактивируется, если он не используется и активируется при необходимости. Действие пользователя не требуется.
Резервные копии баз данных Да № Данные хранятся во внешних системах (ADLS, Cosmos DB), поэтому убедитесь, что вы выполняете резервное копирование данных в источнике. Убедитесь, что в системе управления версиями используются метаданные SQL (таблица, представление, определения процедур и разрешения пользователя). Определения таблиц в базе данных Lake хранятся в метаданных Spark, поэтому убедитесь, что определения таблиц Spark также хранятся в системе управления версиями.
Восстановление базы данных Да № Данные хранятся во внешних системах (ADLS, Cosmos DB), поэтому для переноса данных необходимо восстановить исходные системы. Убедитесь, что метаданные SQL (таблица, представление, определения процедур и разрешения пользователя) находится в системе управления версиями, чтобы можно было повторно создать объекты SQL. Определения таблиц в базе данных Lake хранятся в метаданных Spark, поэтому убедитесь, что определения таблиц Spark также хранятся в системе управления версиями.

Инструменты

Вы можете использовать разные средства, чтобы подключиться к Synapse SQL для запроса по данным.

Средство Выделенные Бессерверные приложения
Synapse Studio Да, скрипты SQL. Да, скрипты SQL можно использовать в Synapse Studio. Используйте SSMS или ADS вместо Synapse Studio, если в результате возвращается большой объем данных.
Power BI Да Да, вы можете использовать Power BI для создания отчетов на бессерверном пуле SQL. Для создания отчетов рекомендуется использовать режим импорта.
Служба Azure Analysis Services Да Да, вы можете загружать данные в Azure Analysis Service с помощью бессерверного пула SQL.
Azure Data Studio (ADS) Да Да, можно использовать Azure Data Studio (версия 1.18.0 или выше) для запросов к бессерверному пулу SQL. Поддерживаются скрипты SQL и записные книжки SQL.
SQL Server Management Studio (SSMS) Да Да, можно использовать SQL Server Management Studio (версия 18.5 или выше) для запросов к бессерверному пулу SQL. SSMS показывает только объекты, доступные в бессерверных пулах SQL.

Примечание.

С помощью SSMS можно подключиться и отправить запрос к бессерверному пулу SQL. Эти средства частично поддерживаются, начиная с версии 18.5, и используется только для подключения и выполнения запросов.

Большинство приложений используют стандартный язык Transact-SQL, который может выполнять запросы по выделенным и бессерверным моделям потребления в Synapse SQL.

Доступ к данным

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

Тип хранилища Выделенные Бессерверные приложения
Внутреннее хранилище Да Нет, данные помещаются в аналитическое хранилище Azure Data Lake или Azure Cosmos DB.
Azure Data Lake версии 2 Да Да, можно использовать внешние таблицы и функцию OPENROWSET для чтения данных из ADLS. Узнайте, как настроить управление доступом.
Хранилище BLOB-объектов Azure Да Да, можно использовать внешние таблицы и функцию OPENROWSET для чтения данных из хранилища BLOB-объектов Azure. Узнайте, как настроить управление доступом.
Azure SQL или SQL Server (удаленный) No Нет, бессерверный пул SQL не может ссылаться на базу данных SQL Azure. Вы можете ссылаться на бессерверные пулы SQL из Azure SQL, используя эластичные запросы или связанные серверы.
Dataverse Нет, данные Azure Cosmos DB можно загрузить в выделенный пул с помощью Azure Synapse Link в бессерверном пуле SQL (с помощью ADLS) или Spark. Да, таблицы Dataverse можно считывать с помощью Synapse Link для Dataverse с помощью Azure Data Lake.
Транзакционное хранилище Azure Cosmos DB No Нет, вы не можете получить доступ к контейнерам Azure Cosmos DB для обновления данных или чтения данных из хранилища транзакций Azure Cosmos DB. Используйте пулы Spark для обновления хранилища транзакций Azure Cosmos DB .
Аналитическое хранилище Azure Cosmos DB Нет, данные Azure Cosmos DB можно загрузить в выделенный пул с помощью Azure Synapse Link в бессерверном пуле SQL (с помощью ADLS), ADF, Spark или другого средства загрузки. Да, вы можете запросить аналитическое хранилище Azure Cosmos DB с помощью Azure Synapse Link.
Таблицы Apache Spark (в рабочей области) No Да, бессерверный пул может считывать таблицы PARQUET и CSV с помощью синхронизации метаданных.
Таблицы Apache Spark (удаленный репозиторий) No Нет, бессерверный пул может получить доступ только к таблицам PARQUET и CSV, созданным в пулах Apache Spark в той же рабочей области Synapse. Однако можно вручную создать внешнюю таблицу, ссылающуюся на расположение внешней таблицы Spark.
Таблицы Databricks (удаленный репозиторий) No Нет, бессерверный пул может получить доступ только к таблицам PARQUET и CSV, созданным в пулах Apache Spark в той же рабочей области Synapse. Однако можно вручную создать внешнюю таблицу, ссылающуюся на расположение таблицы Databricks.

Форматы данных

Анализируемые данные могут храниться в разных форматах. Все доступные для анализа варианты форматов перечислены в следующей таблице.

Формат данных Выделенные Бессерверные приложения
С разделителями Да Да, можно запрашивать файлы с разделителями.
CSV Да (многосимвольные разделители не поддерживаются). Да, можно запрашивать CSV-файлы. Для повышения производительности используйте PARSER_VERSION 2.0, который обеспечивает более быстрый анализ. При добавлении строк в CSV-файлы убедитесь, что вы запрашиваете файлы как добавляемые.
Parquet Да Да, можно запрашивать файлы Parquet, включая файлы с вложенными типами.
Hive ORC Да Нет, бессерверные пулы SQL не могут читать формат Hive ORC.
Hive RC Да Нет, бессерверные пулы SQL не могут читать формат Hive RC.
JSON Да Да, вы можете запрашивать JSON-файлы, используя текстовый формат с разделителями и функции T-SQL JSON.
Avro No Нет, бессерверные пулы SQL не могут читать формат Avro.
Delta Lake No Да, можно запрашивать файлы Delta Lake, включая файлы с вложенными типами.
Модель общих данных (CDM) No Нет, бессерверный пул SQL не может считывать данные, хранящиеся с использованием Common Data Model.

Следующие шаги

Рекомендации по работе с выделенным и бессерверным пулами SQL см. в следующих статьях: