CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Область применения:SQL Server 2022 (16.x) и более поздних версий Azure Synapse Analytics AnalyticsPlatform System (PDW)

Создает внешнюю таблицу, а затем параллельно экспортирует результаты инструкции Transact-SQL SELECT.

• Система платформы Azure Synapse Analytics и Analytics поддерживает Hadoop или хранилище BLOB-объектов Azure.
• SQL Server 2022 (16.x) и более поздних версий поддерживают CREATE EXTERNAL TABLE AS SELECT (CETAS) для создания внешней таблицы, а затем параллельного экспорта результата инструкции SELECT Transact-SQL SELECT в Azure Data Lake Storage (ADLS) 2-го поколения, учетной записи хранения Azure версии 2 и хранилища объектов, совместимого с S3.

Соглашения о синтаксисе Transact-SQL

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Одно-трехкомпонентное имя таблицы, создаваемой в базе данных. Для внешней таблицы реляционная база данных хранит только метаданные таблицы.

Имя столбца таблицы.

Область применения: Azure Synapse Analytics и Analytics Platform System

'hdfs_folder'**
Указывает место записи результатов инструкции SELECT во внешнем источнике данных. Расположение — это имя папки. Оно может включать путь относительно корневой папки кластера Hadoop или Хранилища BLOB-объектов. PolyBase создает путь и папку, если она еще не существует.

Внешние файлы записываются hdfs_folder в и именуются QueryID_date_time_ID.format, где ID является добавочным идентификатором и format является экспортируемым форматом данных. Например, QID776_20160130_182739_0.orc.

LOCATION должен указывать на папку и иметь конечный путь/, например: aggregated_data/

Область применения: SQL Server 2022 (16.x) и более поздних версий

prefix://path[:port] предоставляет протокол подключения (префикс), путь и при необходимости порт к внешнему источнику данных, где будет записан результат инструкции SELECT.

Если назначение является хранилищем объектов, совместимым с S3, сначала должен существовать контейнер, но PolyBase может создавать вложенные папки при необходимости. SQL Server 2022 (16.x) поддерживает Azure Data Lake Storage 2-го поколения, служба хранилища Azure учетную запись 2 и хранилище объектов, совместимое с S3. В настоящее время файлы ORC не поддерживаются.

Задает имя объекта внешнего источника данных, которое содержит расположение, где хранятся или будут храниться внешние данные. Это расположение — кластер Hadoop или Хранилище BLOB-объектов Azure. Для создания внешнего источника данных используйте инструкцию CREATE EXTERNAL DATA SOURCE (Transact-SQL).

Задает имя объекта формата внешнего файла, который хранит формат внешнего файла данных. Чтобы создать формат внешнего файла, используйте CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Параметры REJECT не применяются во время выполнения инструкции CREATE EXTERNAL TABLE AS SELECT. Они указываются здесь, чтобы база данных могла использовать их позже при импорте данных из внешней таблицы. Позже, когда инструкция CREATE TABLE AS SELECT выбирает данные из внешней таблицы, базы данных будет использовать параметры отклонения, чтобы определить число и процент строк, которые, возможно, не удастся импортировать до завершения импорта.

• REJECT_VALUE = reject_value

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

• REJECT_TYPE = значение | процент

  Указывает, является ли параметр REJECT_VALUE литеральным значением или процентом.

  • значение

    Используется, если REJECT_VALUE является литеральным значением, а не процентом. База данных перестает импортировать строки из внешнего файла данных, когда количество неудачных строк превышает reject_value.

    Например, если REJECT_VALUE = 5 и REJECT_TYPE = valueбаза данных перестает импортировать строки после того, как пять строк не удалось импортировать.

  • процент

    Используется, если REJECT_VALUE является процентом, а не литеральным значением. База данных перестает импортировать строки из внешнего файла данных, если процент неудачных строк превышает reject_value. Процент недопустимых строк вычисляется с интервалами. Допустимо только в выделенных пулах SQL, если TYPE=HADOOP.

• REJECT_SAMPLE_VALUE = reject_sample_value

  Обязательный, если REJECT_TYPE = percentage. Указывает количество строк, которые необходимо импортировать, прежде чем база данных пересчитывает процент неудачных строк.

  Например, если задано REJECT_SAMPLE_VALUE = 1000, база данных рассчитает процент отклоненных строк после попытки импортировать 1000 строк из внешнего файла данных. Если процент неудачных строк меньше reject_value, база данных пытается загрузить еще 1000 строк. База данных продолжает пересчитывать процент неудачных строк после попытки импортировать все дополнительные 1000 строк.

  Примечание

  Так как база данных вычисляет процент отклоненных строк с интервалами, фактический процент отклоненных строк может превысить значение reject_value.

  Пример:

  В этом примере показано, как три параметра REJECT взаимодействуют друг с другом. Например, если REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100может произойти следующий сценарий:

  • База данных пытается загрузить первые 100 строк, из которых 25 отклонены и 75 загружены.
  • Показатель строк с ошибками составляет 25 %, что меньше значения отклонения в 30 %. Следовательно, останавливать загрузку не нужно.
  • База данных пытается загрузить следующие 100 строк. На этот раз 25 строк загружены и 75 отклонены.
  • Показатель отклоненных строк вычислен повторно и составляет 50 %. Процент отклоненных строк превысил значение отклонения 30 %.
  • Загрузка завершится ошибкой с 50 % отклоненных строк после попытки загрузить 200 строк, что превышает заданный лимит 30 %.

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

Заполняет новую таблицу результатами выполнения инструкции SELECT. select_criteria являются основной частью инструкции SELECT и определяют, какие данные нужно скопировать в новую таблицу. Сведения об инструкциях SELECT см. в статье SELECT (Transact-SQL).

Параметры столбцов

• column_name [ ,... n ]

  В именах столбцов не могут использоваться параметры столбцов, указанные в разделе, посвященном инструкции CREATE TABLE. Вместо этого можно указать необязательный список из одного или нескольких имен столбцов для новой таблицы. Столбцы в новой таблице используют указанные имена. При указании имен столбцов число столбцов в списке столбцов должно совпадать с числом столбцов в результатах выборки. Если имена столбцов не указаны, новая целевая таблица использует имена столбцов в результатах инструкции select.

  Вы не можете указать другие параметры столбца, такие как типы данных, параметры сортировки или значение NULL. Каждый из этих атрибутов определяется на основе результатов выполнения инструкции SELECT. Тем не менее инструкцию SELECT можно использовать для изменения атрибутов. Пример см. в разделе Использование инструкции CETAS для изменения атрибутов столбца.

Для выполнения этой команды пользователю базы данных потребуются все следующие разрешения и роли:

• Разрешение ALTER SCHEMA для локальной схемы, которая будет содержать новую таблицу, или наличие предопределенной роли базы данных db_ddladmin.
• Разрешение CREATE TABLE или наличие предопределенной роли базы данных db_ddladmin.
• Разрешение SELECT для любых объектов, на которые ссылается select_criteria.

Учетные данные для входа в систему должны обладать всеми нижеперечисленными разрешениями:

• АДМИНИСТРИРОВАНИЕ МАССОВЫХ ОПЕРАЦИЙ
• ИЗМЕНЕНИЕ ЛЮБЫХ ВНЕШНИХ ИСТОЧНИКОВ ДАННЫХ
• ИЗМЕНИТЬ ЛЮБОЙ ВНЕШНИЙ ФОРМАТ ФАЙЛА
• Как правило, у вас должны быть разрешения на содержимое папки List и запись в папку LOCATION для CETAS.
• В Azure Synapse Analytics и analytics Platform System разрешение на запись для чтения и записи в внешнюю папку в кластере Hadoop или в хранилище BLOB-объектов Azure.
• В SQL Server 2022 (16.x) также необходимо задать соответствующие разрешения во внешнем расположении. Разрешение на запись для вывода данных в расположение и разрешение на чтение для доступа к нему.
• Для Хранилище BLOB-объектов Azure и Azure Data Lake 2-го SHARED ACCESS SIGNATURE поколения маркер должен быть предоставлен следующим привилегиям в контейнере: чтение, запись, создание списка.
• Для хранилища Allowed Servicesблогов Azure установите флажок : Blob для создания маркера SAS.
• Для Azure Data Lake 2-го Allowed Servicesпоколения установите флажки и ContainerObject флажки для создания маркера SAS.

Если CREATE EXTERNAL TABLE AS SELECT экспортирует данные в текстовый файл с разделителями, файл отклонений для строк, которые не удалось экспортировать.

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

Если CREATE EXTERNAL TABLE AS SELECT отменена или завершается ошибкой, база данных пытается удалить все новые файлы и папки, уже созданные во внешнем источнике данных.

В Azure Synapse Analytics и analytics Platform System база данных сообщает об ошибках Java, возникающих во время экспорта данных во время экспорта данных.

После завершения инструкции CREATE EXTERNAL TABLE AS SELECT можно выполнить запросы Transact-SQL во внешней таблице. Эти операции импортируют данные в базу данных в течение длительности запроса, если вы не импортируете с помощью инструкции CREATE TABLE AS SELECT.

Имя и определение внешней таблицы хранятся в метаданных базы данных. Данные хранятся во внешнем источнике данных.

Оператор CREATE EXTERNAL TABLE AS SELECT всегда создает непартиментированную таблицу, даже если исходная таблица секционирована.

Для SQL Server 2022 (16.x) параметр allow polybase export должен быть включен с помощью sp_configure. Дополнительные сведения см. в статье Установка параметра конфигурации allow polybase export.

Для планов запросов в Azure Synapse Analytics и Analytics Platform System, созданных с помощью EXPLAIN, база данных использует следующие операции плана запросов для внешних таблиц: перемещение "Внешнее перемешивание", перемещение "Внешняя трансляция", перемещение "Внешнее секционирование".

В Analytics Platform System в качестве предварительного условия для создания внешней таблицы администратор устройства должен настроить подключение к Hadoop. Дополнительные сведения см. в разделе с инструкциями по настройке подключения к внешним данным (Analytics Platform System) в документации по APS, доступной для скачивания в Центре загрузки Майкрософт.

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

База данных не проверяет подключение к внешнему источнику данных при восстановлении резервной копии базы данных, которая содержит внешнюю таблицу. Если исходный источник недоступен, восстановление метаданных внешней таблицы по-прежнему будет выполнено успешно, но операции SELECT во внешней таблице завершаются ошибкой.

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

Внешние таблицы не поддерживают операции языка обработки данных (DML). Например, нельзя использовать инструкции Transact-SQL update, insert или delete Transact-SQL для изменения внешних данных.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW и DROP VIEW — это все операции языка описания данных (DDL), которые можно выполнять во внешних таблицах.

• В выделенных пулах SQL Azure Synapse Analytics и системе платформы Аналитики PolyBase может использовать не более 33 000 файлов для каждой папки при выполнении 32 одновременных запросов PolyBase. Это максимальное число включает файлы и вложенные папки в каждой папке HDFS. Если степень параллелизма меньше 32, пользователь может выполнять запросы PolyBase к папкам в HDFS, если в них содержится более 33 тысяч файлов. Мы рекомендуем пользователям Hadoop и PolyBase указывать короткие пути к файлам и использовать не более 30 тысяч файлов на папку HDFS. Если указать слишком большое число файлов, может возникнуть исключение, связанное с нехваткой памяти на виртуальной машине Java.

• В бессерверных пулах SQL внешние таблицы нельзя создавать в расположении, где в настоящее время есть данные. Чтобы повторно использовать расположение, которое использовалось для хранения данных, необходимо вручную удалить его в ADLS. Дополнительные ограничения и рекомендации см. в рекомендациях по оптимизации фильтров.

В выделенных пулах SQL Azure Synapse Analytics и системе платформы Аналитики, если CREATE EXTERNAL TABLE AS SELECT выбирает из RCFile, значения столбцов в RCFile не должны содержать символ канала (|).

SET ROWCOUNT (Transact-SQL) не влияет на CREATE EXTERNAL TABLE AS SELECT. Чтобы обеспечить аналогичное поведение, воспользуйтесь инструкцией TOP (Transact-SQL).

Ознакомьтесь с ограничениями именования и ссылок на контейнеры, большие двоичные объекты и метаданные для ограничений имен файлов.

Следующие символы, присутствующих в данных, могут привести к ошибкам, включая отклоненные записи с CREATE EXTERNAL TABLE AS SELECT в файлы Parquet.

В Azure Synapse Analytics и Analytics Platform System это также относится к файлам ORC.

• |
• " (символ кавычки)
• \r\n
• \r
• \n

Чтобы использовать CREATE EXTERNAL TABLE AS SELECT с этими символами, необходимо сначала запустить инструкцию CREATE EXTERNAL TABLE AS SELECT, чтобы экспортировать данные в текстовые файлы с разделителями, где их можно преобразовать в Parquet или ORC с помощью внешнего средства.

При работе с файлами CREATE EXTERNAL TABLE AS SELECT parquet создается один файл parquet на доступную ЦП до заданной максимальной степени параллелизма (MAXDOP). Каждый файл может увеличиться до 190 ГБ, после этого SQL Server создаст больше файлов Parquet по мере необходимости.

Указание запроса OPTION (MAXDOP n) влияет только на часть SELECT CREATE EXTERNAL TABLE AS SELECT. Он не влияет на количество файлов parquet. Считается, что считается только MAXDOP уровня базы данных и уровня экземпляра MAXDOP.

Принимает общую блокировку на объект SCHEMARESOLUTION.

CETAS можно использовать для хранения результирующих наборов со следующими типами данных SQL:

• бинарный
• varbinary
• обугливать
• Варчар
• nchar
• nvarchar
• smalldate
• Дата
• дата/время
• дата-время2
• смещение даты и времени
• Время
• десятичное
• числовой
• с плавающей запятой
• настоящий
• Бигинт
• tinyint
• smallint
• INT
• Бигинт
• кусочек
• деньги
• smallmoney

Область применения: Azure Synapse Analytics и Analytics Platform System

В следующем примере создается новая внешняя таблица с именем hdfsCustomer. Для этого используются определения столбцов и данные из исходной таблицы dimCustomer.

Определение таблицы хранится в базе данных, а результаты инструкции SELECT экспортируются /pdwdata/customer.tbl в файл во внешнем источнике данных Hadoop customer_ds. Этот файл форматируется в соответствии с форматом внешнего файла customer_ff.

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

Путь hdfs://xxx.xxx.xxx.xxx:5000/files/, предшествующий каталогу "Клиент", должен уже существовать. Если каталог клиента не существует, база данных создает каталог.

Конечное расположение Hadoop и имя файла будут иметь вид hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

Область применения: Azure Synapse Analytics и Analytics Platform System

В этом запросе показан базовый синтаксис для использования указания соединения запроса с оператором CREATE EXTERNAL TABLE AS SELECT. После отправки запроса база данных использует стратегию хэш-соединения для создания плана запроса. Дополнительные сведения об указаниях соединения и использовании предложения OPTION см. в статье Предложение OPTION (Transact-SQL).

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

Область применения: Azure Synapse Analytics и Analytics Platform System

В этом примере инструкция CETAS используется для изменения типов данных, допустимости значений NULL и параметров сортировки для нескольких столбцов в таблице FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

Область применения: SQL Server 2022 (16.x)

В следующем примере создается новая внешняя таблица с именем ext_sales , которая использует данные из таблицы SalesOrderDetailAdventureWorks2022. Необходимо включить параметр конфигурации экспорта polybase.

Результат инструкции SELECT будет сохранен в хранилище объектов, совместимом с S3, ранее настроенным и именованным s3_eds, и соответствующими учетными данными, созданными как s3_dsc. Местом расположения файла Parquet будет <ip>:<port>/cetas/sales.parquet, в котором cetas — ранее созданный сегмент хранилища.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

Область применения: SQL Server 2022 (16.x)

В следующем примере создается новая внешняя таблица с именем Delta_to_Parquet, которая использует тип данных Delta Table, расположенный в хранилище объектов, совместимом с S3, и записывает результат в другой источник данных с именем s3_deltas3_parquet parquet. Для этого в примере используется команда OPENROWSET. Необходимо включить параметр конфигурации экспорта polybase.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

Область применения: бессерверные пулы SQL Azure Synapse Analytics и выделенные пулы SQL.

Используйте следующий пример в качестве шаблона для написания CETAS с пользовательским представлением в качестве источника, используя управляемое удостоверение для проверки подлинности и конечную точку abfs::

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'abfs[s]://<file_system>@<account_name>.dfs.core.windows.net/<path>/<file_name>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Область применения: бессерверные пулы SQL Azure Synapse Analytics и выделенные пулы SQL.

В этом примере мы видим пример кода шаблона для написания CETAS с пользовательским представлением в качестве источника, используя управляемое удостоверение в качестве проверки подлинности и https:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

• СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• СОЗДАТЬ ВНЕШНЮЮ ТАБЛИЦУ (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics, Parallel Data Warehouse)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• Инструкция ALTER TABLE (Transact-SQL)

Область применения: Управляемый экземпляр SQL Azure

Создает внешнюю таблицу, а затем параллельно экспортирует результаты инструкции Transact-SQL SELECT.

Для выполнения следующих задач можно использовать CREATE EXTERNAL TABLE AS SELECT (CETAS):

• Создайте внешнюю таблицу на основе файлов Parquet или CSV в хранилище BLOB-объектов Azure или Azure Data Lake Storage (ADLS) 2-го поколения.
• Экспортируйте результаты инструкции T-SQL SELECT параллельно в созданную внешнюю таблицу.
• Дополнительные возможности виртуализации данных Управляемый экземпляр SQL Azure см. в разделе "Виртуализация данных" с Управляемый экземпляр SQL Azure.

Соглашения о синтаксисе Transact-SQL

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

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

Указывает место записи результатов инструкции SELECT во внешнем источнике данных. Она представляет собой расположение данных, указанное во внешнем источнике данных. LOCATION должен указывать на папку и иметь конечную точку /. Пример: aggregated_data/.

Папка назначения для CETAS должна быть пустой. Если путь и папка еще не существуют, они создаются автоматически.

Задает имя объекта внешнего источника данных, которое содержит расположение, где будут храниться внешние данные. Для создания внешнего источника данных используйте инструкцию CREATE EXTERNAL DATA SOURCE (Transact-SQL).

Задает имя объекта формата внешнего файла, который хранит формат внешнего файла данных. Чтобы создать формат внешнего файла, используйте CREATE EXTERNAL FILE FORMAT (Transact-SQL). В настоящее время поддерживаются только форматы внешний файлов с FORMAT_TYPE=PARQUET и FORMAT_TYPE=DELIMITEDTEXT. Сжатие GZip для формата DELIMITEDTEXT не поддерживается.

Секционирует выходные данные на несколько путей к файлу parquet. Секционирование происходит на заданные столбцы (), сопоставляя подстановочные знаки (column_name*) в LOCATION с соответствующим столбцом секционирования. Количество столбцов в части PARTITION должно соответствовать количеству подстановочных знаков в LOCATION. Для секционирования должен быть хотя бы один столбец, который не используется.

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Дополнительные сведения см. в статье WITH common_table_expression (Transact-SQL).

Заполняет новую таблицу результатами выполнения инструкции SELECT. select_criteria являются основной частью инструкции SELECT и определяют, какие данные нужно скопировать в новую таблицу. Сведения об инструкциях SELECT см. в статье SELECT (Transact-SQL).

У вас должны быть разрешения на вывод содержимого папки и запись в путь LOCATION для работы CETAS.

Поддерживаемые методы проверки подлинности — это управляемое удостоверение или маркер подписанного URL-адреса (SAS).

• Если вы используете управляемое удостоверение для проверки подлинности, убедитесь, что субъект-служба управляемого экземпляра SQL имеет роль участника данных BLOB-объектов хранилища в целевом контейнере.
• Если вы используете маркер SAS, требуются разрешения на чтение, запись и список .
• Для хранилища Allowed Servicesблогов Azure установите флажок : Blob для создания маркера SAS.
• Для Azure Data Lake 2-го Allowed Servicesпоколения установите флажки и ContainerObject флажки для создания маркера SAS.

Управляемое удостоверение, назначаемое пользователем, не поддерживается. Сквозная проверка подлинности Microsoft Entra не поддерживается. Идентификатор Microsoft Entra (ранее — Azure Active Directory).

Для выполнения этой команды пользователю базы данных потребуются все следующие разрешения и роли:

• Разрешение ALTER SCHEMA для локальной схемы, которая будет содержать новую таблицу, или наличие предопределенной роли базы данных db_ddladmin.
• Разрешение CREATE TABLE или наличие предопределенной роли базы данных db_ddladmin.
• Разрешение SELECT для любых объектов, на которые ссылается select_criteria.

Учетные данные для входа в систему должны обладать всеми нижеперечисленными разрешениями:

• АДМИНИСТРИРОВАНИЕ МАССОВЫХ ОПЕРАЦИЙ
• ИЗМЕНЕНИЕ ЛЮБЫХ ВНЕШНИХ ИСТОЧНИКОВ ДАННЫХ
• ИЗМЕНИТЬ ЛЮБОЙ ВНЕШНИЙ ФОРМАТ ФАЙЛА

CETAS хранит результирующие наборы со следующими типами данных SQL:

• бинарный
• varbinary
• обугливать
• Варчар
• nchar
• nvarchar
• smalldatetime
• Дата
• дата/время
• дата-время2
• смещение даты и времени
• Время
• десятичное
• числовой
• с плавающей запятой
• настоящий
• Бигинт
• tinyint
• smallint
• INT
• Бигинт
• кусочек
• деньги
• smallmoney

• CREATE EXTERNAL TABLE AS SELECT (CETAS) для Управляемого экземпляра SQL Azure по умолчанию отключен. Дополнительные сведения см. в следующем разделе: "Отключено по умолчанию".
• Дополнительные сведения об ограничениях или известных проблемах виртуализации данных в Управляемый экземпляр SQL Azure см. в разделе "Ограничения" и "Известные проблемы".

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

База данных не проверяет подключение к внешнему источнику данных при восстановлении резервной копии базы данных, которая содержит внешнюю таблицу. Если исходный источник недоступен, восстановление метаданных внешней таблицы по-прежнему выполняется успешно, но операции SELECT во внешней таблице завершаются ошибкой.

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

Внешние таблицы не поддерживают операции языка обработки данных (DML). Например, нельзя использовать инструкции Transact-SQL update, insert или delete Transact-SQL для изменения внешних данных.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW и DROP VIEW — это все операции языка описания данных (DDL), которые можно выполнять во внешних таблицах.

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

SET ROWCOUNT (Transact-SQL) не влияет на CREATE EXTERNAL TABLE AS SELECT. Чтобы обеспечить аналогичное поведение, воспользуйтесь инструкцией TOP (Transact-SQL).

Ознакомьтесь с ограничениями именования и ссылок на контейнеры, большие двоичные объекты и метаданные для ограничений имен файлов.

Файлы можно хранить в Azure Data Lake Storage 2-го поколения или Хранилище BLOB-объектов Azure. Чтобы запросить файлы, необходимо указать расположение в определенном формате и использовать префикс типа расположения, соответствующий типу внешнего источника и конечной точки или протокола, например в следующих примерах:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

CREATE EXTERNAL TABLE AS SELECT (CETAS) позволяет экспортировать данные из управляемого экземпляра SQL в внешнюю учетную запись хранения, поэтому существует вероятность кражи данных с этими возможностями. Поэтому CETAS по умолчанию отключен для Управляемый экземпляр SQL Azure.

CETAS для Управляемый экземпляр SQL Azure можно включить только через метод, требующий повышенных разрешений Azure, и не может быть включен через T-SQL. Из-за риска несанкционированного кражи данных CETAS нельзя включить с помощью sp_configure хранимой процедуры T-SQL, но вместо этого требуется, чтобы действие пользователя за пределами управляемого экземпляра SQL.

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

Для этого также можно создать пользовательскую роль, требуя действия чтения и записи для Microsoft.Sql/managedInstances/serverConfigurationOptions действия.

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

az login
$subscriptionId = "<your subscription ID>"
az account set --subscription $subscriptionId

# Enable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '1'

# Disable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '0'

# Get server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option show --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport'

# Enter your Azure subscription ID
$SubscriptionID = "<YourSubscriptionIDHERE>"

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceNameHere>"

# ===============================================================================
# INVOKING THE API CALL -- REST OF THE SCRIPT IS NOT USER CONFIGURABLE
# ===============================================================================
# Log in and select a subscription if needed.

if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

# Build the URI for the API call.
$resource_group_name = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $resource_group_name+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/serverConfigurationOptions/allowPolybaseExport" + "?api-version=2022-08-01-preview"

# Build the API request body.
$properties = @{serverConfigurationOptionValue = 1}
$bodyFull = @{properties = $properties}
$jsonBody = $bodyFull | ConvertTo-Json

# Get auth token and build the HTTP request header.
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# Invoke API call. API call creates an Asynchronous operation that will change the configuration setting. 
# In rare cases that your SQL MI is down when the async operation will run, the async operation may fail.
# You can check the status of the Async operation using the callback URL in the response header.
# Look for response header parameter: Azure-AsyncOperation.
Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $jsonBody

В любое время можно проверить текущее состояние параметра конфигурации CETAS.

Подключитесь к управляемому экземпляру SQL. Запустите следующий T-SQL и просмотрите value столбец ответа. После завершения изменения конфигурации сервера результаты этого запроса должны соответствовать требуемому параметру.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Дополнительные действия по устранению неполадок с виртуализацией данных в Управляемый экземпляр SQL Azure см. в разделе "Устранение неполадок". Обработка ошибок и распространенные сообщения об ошибках для CETAS в Управляемый экземпляр SQL Azure приведены ниже.

Если CREATE EXTERNAL TABLE AS SELECT экспортирует данные в текстовый файл с разделителями, файл отклонений для строк, которые не удалось экспортировать.

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

Эти распространенные сообщения об ошибках имеют краткие объяснения CETAS для Управляемый экземпляр SQL Azure.

1. Указание расположения, уже существующего в хранилище.

   Решение. Очистка расположения хранилища (включая моментальный снимок) или изменение параметра расположения в запросе.

   Пример сообщения об ошибке: Msg 15842: Cannot create external table. External table location already exists.

2. Значения столбцов, отформатированные с помощью объектов JSON.

   Решение. Преобразование столбца значений в один столбец VARCHAR или NVARCHAR или набор столбцов с явно определенными типами.

   Пример сообщения об ошибке: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

3. Недопустимый параметр расположения (например, несколько //).

   Решение. Исправлен параметр расположения.

   Пример сообщения об ошибке: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

4. Отсутствует один из обязательных параметров (DATA_SOURCE, FILE_FORMAT, LOCATION).

   Решение. Добавьте отсутствующий параметр в запрос CETAS.

   Пример сообщения об ошибке: Msg 46505: Missing required external DDL option 'FILE_FORMAT'

5. Проблемы с доступом (недопустимые учетные данные, истекшие учетные данные или учетные данные с недостаточными разрешениями). Альтернативная возможность — недопустимый путь, в котором управляемый экземпляр SQL получил ошибку 404 из хранилища.

   Решение. Проверка допустимости учетных данных и разрешений. Кроме того, убедитесь, что путь действителен и хранилище существует. Используйте путь URL-адреса adls://<container>@<storage_account>.blob.core.windows.net/<path>/.

   Пример сообщения об ошибке: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

6. Расположение части DATA_SOURCE содержит подстановочные знаки.

   Решение. Удалите подстановочные знаки из расположения.

   Пример сообщения об ошибке: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

7. Количество подстановочных знаков в параметре LOCATION и количество секционированных столбцов не совпадают.

   Решение. Убедитесь в том же количестве подстановочных знаков в LOCATION, что и столбцы секций.

   Пример сообщения об ошибке: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

8. Имя столбца в предложении PARTITION не соответствует столбцам в списке.

   Решение. Убедитесь, что столбцы в PARTITION допустимы.

   Пример сообщения об ошибке: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

9. Столбец, указанный более одного раза в списке PARTITION.

   Решение. Убедитесь, что столбцы в предложении PARTITION уникальны.

   Пример сообщения об ошибке: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

10. Столбец был указан несколько раз в списке PARTITION или не соответствует столбцам из списка SELECT.

    Решение. Убедитесь, что в списке секций отсутствуют дубликаты, а столбцы секций существуют в части SELECT.

    Примеры сообщений об ошибках: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. или Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.

11. Использование всех столбцов в списке PARTITION.

    Решение. Хотя бы один из столбцов из части SELECT не должен находиться в части PARTITION запроса.

    Пример сообщения об ошибке: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.

12. Функция отключена.

    Решение. Включение функции с помощью раздела "Отключено по умолчанию " в этой статье.

    Пример сообщения об ошибке: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Принимает общую блокировку на объект SCHEMARESOLUTION.

В этом примере представлен код для написания CETAS с представлением в качестве источника с помощью управляемого системой удостоверения проверки подлинности.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

В этом примере представлен код для написания CETAS с представлением в качестве источника, используя маркер SAS в качестве проверки подлинности.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

В следующих двух примерах показано, как выгрузить некоторые данные из локальной таблицы во внешнюю таблицу, хранящуюся в виде файлов parquet в контейнере хранилища BLOB-объектов Azure. Они предназначены для работы с AdventureWorks2022 базой данных. В этом примере показано создание внешней таблицы в виде одного файла parquet, где в следующем примере показано, как создать внешнюю таблицу и разделить ее на несколько папок с файлами parquet.

В приведенном ниже примере используется управляемое удостоверение для проверки подлинности. Таким образом, убедитесь, что субъект-служба Управляемый экземпляр SQL Azure имеет роль участника данных BLOB-объектов хранилища в контейнере Хранилище BLOB-объектов Azure. Кроме того, можно изменить пример и использовать маркеры секрета общего доступа (SAS) для проверки подлинности.

В следующем примере вы создадите внешнюю таблицу в один файл parquet в Хранилище BLOB-объектов Azure, выбрав из SalesOrderHeader таблицы заказы старше 1-января 2014 года:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

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

Создайте внешнюю таблицу из SalesOrderHeader данных, выполнив шаги из примера B, но секционирование внешней таблицы по OrderDate годам и месяцам. При запросе секционированных внешних таблиц мы можем воспользоваться устранением секций для повышения производительности.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

• СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• СОЗДАТЬ ВНЕШНЮЮ ТАБЛИЦУ (Transact-SQL)
• DROP TABLE (Transact-SQL)
• Инструкция ALTER TABLE (Transact-SQL)