CREATE EXTERNAL TABLE (Transact-SQL)

Обзор: SQL Server

Эта команда создает внешнюю таблицу для PolyBase для доступа к данным, хранящимся в кластере Hadoop или Хранилище BLOB-объектов Azure внешней таблице PolyBase, которая ссылается на данные, хранящиеся в кластере Hadoop или Хранилище BLOB-объектов Azure.

Область применения: SQL Server 2016 или более поздней версии

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

Виртуализация и загрузка данных с помощью PolyBase
Операции массовой загрузки в SQL Server или базе данных SQL с помощью BULK INSERT или OPENROWSET

См. также CREATE EXTERNAL DATA SOURCE и DROP EXTERNAL TABLE.

Синтаксис

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

Аргументы

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Имя создаваемой таблицы, состоящее из одной, двух или трех частей. Для внешней таблицы SQL хранит только метаданные таблицы, а также базовую статистику о файле или папке, на которую ссылается Hadoop или Хранилище BLOB-объектов Azure. Фактические данные не перемещаются или хранятся в SQL Server.

Внимание

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

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE поддерживает возможность настроить имя столбца, тип данных, допустимость значений NULL и параметры сортировки. Параметр DEFAULT CONSTRAINT нельзя использовать с внешними таблицами.

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

LOCATION = путь_к_папке_или_файлу

Указывает путь к папке или файлу и имя файла для фактических данных в Хранилище BLOB-объектов Azure или Hadoop. Кроме того, начиная с SQL Server 2022 (16.x) поддерживается совместимое с S3 хранилище объектов. Расположение начинается с корневой папки. Она представляет собой расположение данных, указанное во внешнем источнике данных.

В SQL Server инструкция CREATE EXTERNAL TABLE создает путь к папке и саму папку, если она еще не существует. Затем вы можете использовать инструкцию INSERT INTO, чтобы экспортировать данные из локальной таблицы SQL Server во внешний источник данных. Дополнительные сведения: Запросы PolyBase.

Если вы укажете LOCATION в качестве папки, запрос PolyBase, который выбирает из внешней таблицы, извлечет файлы из этой папки и всех ее вложенных папок. Как и Hadoop, PolyBase не возвращает скрытые папки. Кроме того, не возвращаются файлы, имя которых начинается с подчеркивания (_) или точки (.).

В следующем примере изображения, если LOCATION='/webdata/'запрос PolyBase вернет строки из mydata.txt и mydata2.txt. Он не возвращается mydata3.txt , так как это файл в скрытой вложенной папке. Он также не вернет файл _hidden.txt, так как тот является скрытым.

Схема папок и файловых данных для внешних таблиц.

Чтобы изменить значение по умолчанию и только для чтения в корневой папке, установите для атрибута <polybase.recursive.traversal> значение false в файле конфигурации core-site.xml. Этот файл находится под <SqlBinRoot>\PolyBase\Hadoop\Conf корнем bin SQL Server. Например, C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

DATA_SOURCE = external_data_source_name

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

FILE_FORMAT = external_file_format_name

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

Форматы внешних файлов можно повторно использовать несколькими похожими внешними файлами.

Параметры отклонения

Этот параметр можно использовать только с внешними источниками данных с TYPE = HADOOP.

Можно указать параметры отклонения, определяющие, как PolyBase будет обрабатывать грязные записи, извлеченные из внешнего источника данных. Запись считается "грязной", если фактический тип данных или количество столбцов не совпадают с определениями столбцов во внешней таблице.

Если вы не указываете или не меняете значения отклонения, PolyBase использует значения по умолчанию. Сведения о параметрах отклонения сохраняются как дополнительные метаданные при создании внешней таблицы с помощью инструкции CREATE EXTERNAL TABLE. При последующем выполнении инструкции SELECT или SELECT INTO SELECT для выбора данных из внешней таблицы PolyBase будет использовать параметры отклонения для определения числа или процента строк, которые можно отклонить, прежде чем запрос завершится ошибкой. Запрос будет возвращать (частичные) результаты, пока не будет превышено пороговое значение отклонения. Затем он выдаст соответствующее сообщение об ошибке.

REJECT_TYPE = value | percentage

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

значение

REJECT_VALUE указан в виде литерала, а не в процентах. Запрос завершится ошибкой, если число отклоненных строк превышает reject_value.

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

процент

REJECT_VALUE указывается в процентах, а не в виде литерала. Запрос завершится ошибкой, если процент неудачных строк превышает reject_value. Процент недопустимых строк вычисляется с интервалами.

REJECT_VALUE = reject_value

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

Если задано REJECT_TYPE = value, значение reject_value должно быть целым числом от 0 до 2 147 483 647.

Если задано REJECT_TYPE = percentage, значение reject_value должно быть числом с плавающей точкой от 0 до 100.

REJECT_SAMPLE_VALUE = reject_sample_value

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

Параметр reject_sample_value должен быть целым числом от 0 до 2 147 483 647.

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

Примечание.

Поскольку PolyBase вычисляет процент отклоненных строк с интервалами, фактический процент отклоненных строк может превысить reject_value.

Пример:

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

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

REJECTED_ROW_LOCATION = расположение каталога

Область применения: SQL Server 2019 CU6 и более поздних версий, Azure Synapse Analytics.

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

Если указанный файл не существует, PolyBase создаст его от вашего имени. Создается дочерний каталог с именем "_rejectedrows". Благодаря наличию символа "_" каталог исключается из других процессов обработки данных, если не указан в явном виде в параметре LOCATION. В этом каталоге существует папка, созданная на основе времени отправки нагрузки в формате YearMonthDay -HourMinuteSecond (например). 20230330-173205 В эту папку записываются файлы двух типов: файлы причин и файлы данных. Этот параметр можно применять только с внешними источниками данных, имеющими TYPE = HADOOP, и для внешних таблиц с использованием DELIMITEDTEXT FORMAT_TYPE. Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE и CREATE EXTERNAL FILE FORMAT.

Как файлы причин, так и файлы данных имеют идентификаторы queryID, связанные с инструкцией CTAS. Так как данные и причины хранятся в отдельных файлах, эти файлы имеют соответствующие суффиксы.

Разрешения

Требуются следующие разрешения:

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT (применяется только к внешним источникам данных Hadoop и службы хранилища Azure)
CONTROL DATABASE (применяется только к внешним источникам данных Hadoop и службы хранилища Azure)

Обратите внимание, что учетные данные для удаленного входа в систему, указанные в параметре DATABASE SCOPED CREDENTIAL, который используется в команде CREATE EXTERNAL TABLE, должны иметь разрешение на чтение для пути, таблицы или коллекции во внешнем источнике данных, указанном в параметре LOCATION. Если вы планируете использовать EXTERNAL TABLE для экспорта данных во внешний источник данных Hadoop или службы хранилища Azure, указанные учетные данные должны иметь разрешение на запись для пути, указанного в LOCATION. Обратите внимание, что Hadoop в настоящее время не поддерживается в SQL Server 2022 (16.x).

Для Хранилища BLOB-объектов Azure при настройке ключей доступа и подписанного URL-адреса (SAS) на портале Azure, в учетных записях хранения Хранилища BLOB-объектов Azure или ADLS 2-го поколения нужно выбрать Предоставленные разрешения, чтобы предоставить по крайней мере разрешения Чтение и Запись. Также может потребоваться разрешение List при поиске по папкам. Необходимо также выбрать элементы Контейнер и Объект в качестве допустимых типов ресурсов.

Внимание

Разрешение ALTER ANY EXTERNAL DATA SOURCE предоставляет любому субъекту возможность создания и изменения объекта внешнего источника данных и, таким образом, также предоставляет возможность доступа ко всем учетным данным уровня базы данных в базе данных. Это разрешение следует рассматривать как высоко привилегированное, поэтому его следует предоставлять только доверенным субъектам в системе.

Обработка ошибок

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

Замечания

В нерегламентированных сценариях запросов, таких как SELECT FROM EXTERNAL TABLE, PolyBase хранит строки, полученные из внешнего источника данных во временной таблице. После выполнения запроса PolyBase удаляет временную таблицу. В таблицах SQL не сохраняются постоянные данные.

Если выполняется импорт, например SELECT INTO FROM EXTERNAL TABLE, PolyBase сохраняет строки, полученные из внешнего источника данных, в виде постоянных данных в таблице SQL. Новая таблица создается при выполнении запросов, когда PolyBase извлекает внешние данные.

PolyBase может передать некоторые вычисления запросов в Hadoop для повышения производительности запросов. Это действие называется включением предиката. Для его выполнения задайте параметр расположения диспетчера ресурсов Hadoop в CREATE EXTERNAL DATA SOURCE.

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

ограничения

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

Вы можете создать несколько внешних таблиц, которые ссылаются на разные внешние источники данных. Если же вы одновременно выполняете запросы к различным источникам данных Hadoop, каждый источник Hadoop должен иметь одинаковый параметр конфигурации сервера hadoop connectivity. Например, невозможно одновременно выполнить запрос к кластеру Cloudera Hadoop и кластеру Hortonworks Hadoop, поскольку они используют разные параметры конфигурации. Сведения о параметрах конфигурации и поддерживаемых сочетаниях см. в разделе Конфигурация подключения к PolyBase.

Если внешняя таблица использует DELIMITEDTEXT, CSVPARQUETили DELTA как типы данных, внешние таблицы поддерживают статистику только для одного столбца для каждой CREATE STATISTICS команды.

Только эти инструкции DDL допускаются для внешних таблиц:

CREATE TABLE и DROP TABLE
CREATE STATISTICS и DROP STATISTICS
CREATE VIEW и DROP VIEW

Неподдерживаемые конструкции и операции:

Ограничение DEFAULT для столбцов внешней таблицы
Операции DML обновления, вставки и удаления

Ограничения запросов

PolyBase может обработать не более 33 тысяч файлов на папку при выполнении параллельных запросов PolyBase со степенью 32. Это максимальное число включает файлы и вложенные папки в каждой папке HDFS. Если степень параллелизма меньше 32, пользователь может выполнять запросы PolyBase к папкам в HDFS, если в них содержится более 33 тысяч файлов. Рекомендуется указывать короткие пути к внешним файлам и следить за тем, чтобы в каждой папке HDFS было не более 30 тысяч файлов. При ссылке на слишком большое число файлов может возникнуть исключение, связанное с нехваткой памяти на виртуальной машине Java.

Ограничения по ширине таблицы

В PolyBase в SQL Server 2016 ширина строки должна составлять не более 32 КБ в связи с максимальным размером допустимой строки в определении таблицы. Если схема суммы столбцов превышает 32 КБ, PolyBase не сможет запросить данные.

Ограничения типов данных

Во внешних таблицах PolyBase нельзя использовать следующие типы данных:

geography
geometry
hierarchyid
image
text
nText
xml
Любой пользовательский тип

Ограничения, связанные с источником данных

Oracle

Синонимы Oracle не поддерживаются для использования с PolyBase.

Внешние таблицы для коллекций MongoDB, содержащих массивы

Чтобы создать внешние таблицы для коллекций MongoDB, содержащих массивы, следует использовать Расширение Data Virtualization для Azure Data Studio. С его помощью можно создать инструкцию CREATE EXTERNAL TABLE на основе схемы, обнаруженной драйвером ODBC PolyBase для MongoDB. Действия по преобразованию в плоскую структуры выполняются драйвером автоматически. Кроме того, можно использовать sp_data_source_objects (Transact-SQL) для обнаружения схемы коллекции (столбцов) и создания внешней таблицы вручную. Хранимая sp_data_source_table_columns процедура также автоматически выполняет преобразование в плоскую структуру с помощью драйвера ODBC PolyBase для MongoDB. Расширение Виртуализации данных для Azure Data Studio и sp_data_source_table_columns используйте те же внутренние хранимые процедуры для запроса внешней схемы.

Блокировка

Общая блокировка на объект SCHEMARESOLUTION.

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

Файлы данных для внешней таблицы хранятся в Hadoop или Хранилище BLOB-объектов Azure. Эти файлы данных создаются и управляются вашими собственными процессами. В ваши обязанности входит управление безопасностью внешних данных.

Примеры

А. Создайте внешнюю таблицу с данными с текстовыми разделителями.

В этом примере показаны все действия по созданию внешней таблицы с данными, отформатированными в виде файлов с текстовыми разделителями. В нем определяется внешний источник данных mydatasource и формат внешнего файла myfileformat. Затем эти объекты уровня базы данных указываются в инструкции CREATE EXTERNAL TABLE. Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE и CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. Создайте внешнюю таблицу с данными в формате RCFile.

В этом примере показаны все действия по созданию внешней таблицы с данными, отформатированными в виде файлов RCFile. В нем определяется внешний источник данных mydatasource_rc и формат внешнего файла myfileformat_rc. Затем эти объекты уровня базы данных указываются в инструкции CREATE EXTERNAL TABLE. Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE и CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. Создайте внешнюю таблицу с данными в формате ORC.

В этом примере показаны все действия по созданию внешней таблицы с данными, отформатированными в виде файлов ORC. В нем определяется внешний источник данных mydatasource_orc и формат внешнего файла myfileforma_orc. Затем эти объекты уровня базы данных указываются в инструкции CREATE EXTERNAL TABLE. Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE и CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Запрос данных Hadoop

ClickStream — это внешняя таблица, соединенная с текстовым файлом с разделителями employee.tbl в кластере Hadoop. Следующий запрос выглядит так же, как запрос к стандартной таблице. Однако этот запрос извлекает данные из Hadoop, а затем вычисляет результаты.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

Е. Объединение данных Hadoop с данными SQL

Этот запрос выглядит так же, как стандартный запрос JOIN для двух таблиц SQL. Разница в том, что PolyBase получает сведения о посещениях из Hadoop, а затем объединяет их с таблицей UrlDescription. Одна таблица является внешней, а другая — стандартной таблицей SQL.

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. Импорт данных из Hadoop в таблицу SQL

В этом примере создается новая таблица SQL — ms_user, в которой постоянно хранятся результаты объединения стандартной таблицы SQL user и внешней таблицы ClickStream.

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. Создание внешней таблицы для SQL Server

Перед созданием учетных данных на уровне базы данных в базе данных должен быть главный ключ для защиты учетных данных. Дополнительные сведения см. в разделах CREATE MASTER KEY и CREATE DATABASE SCOPED CREDENTIAL.

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

Создание нового внешнего источника данных с именем SQLServerInstance и внешней таблицы с именем sqlserver.customer.

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

I. Создание внешней таблицы для Oracle

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. Создание внешней таблицы для Teradata

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. Создание внешней таблицы для MongoDB

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. Запрос хранилища объектов, совместимых с S3, через внешнюю таблицу

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

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

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

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

Дополнительные сведения о связанных понятиях см. в следующих статьях:

* База данных SQL Azure *

Обзор: База данных SQL Azure

В Базе данных SQL Azure создает внешнюю таблицу для эластичных запросов (предварительная версия).

См. также CREATE EXTERNAL DATA SOURCE.

Синтаксис

-- Create a table for use with elastic query
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH ( <sharded_external_table_options> )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<sharded_external_table_options> ::=
        DATA_SOURCE = external_data_source_name,
        SCHEMA_NAME = N'nonescaped_schema_name',
        OBJECT_NAME = N'nonescaped_object_name',
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]
    )
[;]

Аргументы

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

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

Внимание

<column_definition> [ ,...n ]

Примечание.

Типы данных Text, nText и XML не поддерживаются для столбцов во внешних таблицах базы данных SQL Microsoft Azure.

Параметры сегментированных внешних таблиц

Указывает внешний источник данных (не источник данных SQL Server) и метод распределения для эластичного запроса.

DATA_SOURCE

Предложение DATA_SOURCE определяет внешний источник данных (карту сегментов) для внешней таблицы. Пример см. в разделе Создание внешних таблиц.

Внимание

База данных SQL Azure поддерживает создание внешних таблиц для типов EXTERNAL DATA SOURCE RDMS и SHARD_MAP_MANAGER. База данных SQL Azure не поддерживает создание внешних таблиц для Хранилище BLOB-объектов Azure.

SCHEMA_NAME и OBJECT_NAME

Предложения SCHEMA_NAME и OBJECT_NAME сопоставляют определение внешней таблицы с таблицей в другой схеме. Если эти предложения отсутствуют, то предполагается, что удаленный объект является таблицей "dbo", имя которой совпадает с именем внешней таблицы. Это особенно необходимо, если имя удаленной таблицы уже занято в базе данных, где создается внешняя таблица. Например, вы хотите определить внешнюю таблицу для получения общего представления из представлений каталогов или динамических административных представлений, которые находятся на вашем развернутом уровне данных. Так как представления каталогов и динамические административные представления уже существуют локально, их имена нельзя использовать для определения внешней таблицы. Вместо этого вы можете задать другое имя и в предложениях SCHEMA_NAME и OBJECT_NAME использовать имя представления каталога или динамического административного представления. Пример см. в разделе Создание внешних таблиц.

DISTRIBUTION

Необязательно. Он необходим только для баз данных типа SHARD_MAP_MANAGER. Этот аргумент указывает, следует считать таблицу сегментированной или реплицированной. Если задан аргумент SHARDED (имя столбца), данные из разных таблиц не перекрываются. REPLICATED указывает, что таблицы содержат одинаковые данные в каждом сегменте. ROUND_ROBIN указывает, что для распределения данных используется метод конкретного приложения.

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

SHARDED означает, что данные секционируются по базе данных горизонтально. Ключ секционирования для распределения данных указывается в параметре sharding_column_name.
REPLICATED означает, что в каждой базе данных имеются идентичные копии таблицы. Вы должны самостоятельно позаботиться о соответствии реплик во всех базах данных.
ROUND_ROBIN означает, что таблица секционируется горизонтально с применением метода распределения в зависимости от приложений.

Разрешения

Пользователи, имеющие доступ к внешней таблице, автоматически получают доступ к базовой удаленной таблице с учетными данными, указанными в определении внешнего источника данных. Старайтесь избегать нежелательного повышения прав с использованием учетных данных для внешнего источника данных. Методы GRANT или REVOKE применяются к внешней таблице так же, как и к обычной. Определив внешний источник данных и внешние таблицы, вы можете использовать все возможности T-SQL для создания запросов к внешним таблицам.

Обработка ошибок

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

Замечания

В нерегламентированных сценариях запросов, таких как SELECT FROM EXTERNAL TABLE, База данных SQL хранит строки, полученные из внешнего источника данных во временной таблице. После выполнения запроса база данных SQL удаляет временную таблицу. В таблицах SQL не сохраняются постоянные данные.

Если выполняется импорт, например SELECT INTO FROM EXTERNAL TABLE, база данных SQL сохраняет строки, полученные из внешнего источника данных, в виде постоянных данных в таблице SQL. Новая таблица создается при выполнении запросов, когда база данных SQL извлекает внешние данные.

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

ограничения

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

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

Только эти инструкции DDL допускаются для внешних таблиц:

CREATE TABLE и DROP TABLE.
CREATE VIEW и DROP VIEW.

Неподдерживаемые конструкции и операции:

Ограничение DEFAULT для столбцов внешней таблицы.
Удаление, вставка и обновление на языке обработки данных DML.
Динамическое маскирование данных для столбцов внешней таблицы.
В Базе данных SQL Azure не поддерживаются курсоры для внешних таблиц.

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

-- Assuming External.Orders is an external table and Customer is a local table.
-- This query  will copy the whole of the external locally as the predicate needed
-- to filter isn't known at compile time. Its only known during execution of the query

SELECT Orders.OrderId, Orders.OrderTotal
FROM External.Orders
WHERE CustomerId IN (
        SELECT TOP 1 CustomerId
        FROM Customer
        WHERE CustomerName = 'MyCompany'
);

Использование внешних таблиц предотвращает параллелизм в плане запроса.

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

Ограничения типов данных

Во внешних таблицах PolyBase нельзя использовать следующие типы данных:

geography
geometry
hierarchyid
image
text
nText
xml
Любой пользовательский тип

Блокировка

Общая блокировка на объект SCHEMARESOLUTION.

Примеры

А. Создание внешней таблицы для базы данных SQL Azure

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

B. Создание внешней таблицы для сегментированного источника данных

В этом примере удаленное динамическое административное представление сопоставляется с внешней таблицей с помощью предложений SCHEMA_NAME и OBJECT_NAME.

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,
  [request_id] int NOT NULL,
  [start_time] datetime NOT NULL,
  [status] nvarchar(30) NOT NULL,
  [command] nvarchar(32) NOT NULL,
  [sql_handle] varbinary(64),
  [statement_start_offset] int,
  [statement_end_offset] int,
  [cpu_time] int NOT NULL)
WITH
(
  DATA_SOURCE = MyExtSrc,
  SCHEMA_NAME = 'sys',
  OBJECT_NAME = 'dm_exec_requests',
  DISTRIBUTION=ROUND_ROBIN
);

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

Дополнительные сведения о внешних таблицах в Базе данных SQL Azure см. в следующих статьях:

База данных SQL Azure

* Azure Synapse
Analytics *

Обзор: Azure Synapse Analytics

Внешняя таблица используется в следующих целях:

Выделенные пулы SQL могут запрашивать, импортировать и хранить данные из Hadoop, Хранилище BLOB-объектов Azure и Azure Data Lake Storage 1-го поколения и 2-го поколения.
Бессерверные пулы SQL могут запрашивать, импортировать и хранить данные из Хранилище BLOB-объектов Azure, Azure Data Lake Storage 1-го поколения и 2-го поколения. Бессерверные не поддерживаются TYPE=Hadoop.

См. также CREATE EXTERNAL DATA SOURCE и DROP EXTERNAL TABLE.

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

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name,
        [ , <reject_options> [ ,...n ] ]
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]         
    )
[;]
<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=
{
    | REJECT_TYPE = value, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

Аргументы

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Имя создаваемой таблицы, состоящее из одной, двух или трех частей. Для внешней таблицы только метаданные таблицы вместе с базовой статистикой о файле или папке, на которую ссылается Azure Data Lake, Hadoop или Хранилище BLOB-объектов Azure. При создании внешних таблиц фактические данные не перемещаются и не сохраняются.

Внимание

<column_definition> [ ,...n ]

Примечание.

Устаревшие типы textданных и ntext XML не поддерживаются для столбцов во внешних таблицах Для Synapse Analytics.

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

LOCATION = путь_к_папке_или_файлу

Указывает папку или путь к файлу и имя файла для фактических данных в Azure Data Lake, Hadoop или Хранилище BLOB-объектов Azure. Расположение начинается с корневой папки. Она представляет собой расположение данных, указанное во внешнем источнике данных. Инструкция CREATE EXTERNAL TABLE AS SELECT создает путь к папке и саму папку, если она еще не существует. CREATE EXTERNAL TABLE не создает путь к папке.

В следующем примере изображения, если LOCATION='/webdata/'запрос PolyBase вернет строки из mydata.txt и mydata2.txt. Он не возвращается mydata3.txt , так как он находится в подпапке скрытой папки. Он также не вернет файл _hidden.txt, так как тот является скрытым.

Схема папок и файловых данных для внешних таблиц.

В отличие от внешних таблиц Hadoop, собственные внешние таблицы не возвращают вложенные папки, если только не указано /** в конце пути. В этом примере, если LOCATION='/webdata/'запрос бессерверного пула SQL возвращает строки из mydata.txt. Файлы mydata2.txt и mydata3.txt не возвращаются, так как они находятся во вложенной папке. Таблицы Hadoop будут возвращать все файлы в любой вложенной папке.

Как Hadoop, так и собственные внешние таблицы пропускают файлы с именами, которые начинаются с подчеркивания (_) или точки (.).

DATA_SOURCE = external_data_source_name

Указывает имя внешнего источника данных, содержащего расположение внешних данных. Это расположение находится в Azure Data Lake. Для создания внешнего источника данных используйте инструкцию CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

TABLE_OPTIONS

Задает набор параметров, описывающих способ чтения используемых файлов. В настоящее время доступен только параметр {"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}, который указывает внешней таблице игнорировать обновления, внесенные в базовые файлы, даже если это может привести к несогласованности операций чтения. Используйте этот параметр только в особых случаях, когда выполняется частое добавление файлов. Этот параметр доступен в бессерверном пуле SQL для формата CSV.

Параметры REJECT

Параметры отклонения доступны в предварительной версии для бессерверных пулов SQL в Azure Synapse Analytics.

Этот параметр можно использовать только с внешними источниками данных с TYPE = HADOOP.

Параметр формата PARSER_VERSION поддерживается только в бессерверных пулах SQL.

REJECT_TYPE = value | percentage

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

значение

REJECT_VALUE указан в виде литерала, а не в процентах. Запрос PolyBase завершится ошибкой, если число отклоненных строк превышает reject_value.

Например, если задано REJECT_VALUE = 5 и REJECT_TYPE = value, запрос PolyBase SELECT завершится ошибкой после отклонения пяти строк.

процент

REJECT_VALUE указывается в процентах, а не в виде литерала. Запрос PolyBase завершится ошибкой, когда процент отклоненных строк превысит reject_value. Процент недопустимых строк вычисляется с интервалами.

REJECT_VALUE = reject_value

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

Если задано REJECT_TYPE = value, значение reject_value должно быть целым числом от 0 до 2 147 483 647.
Если задано REJECT_TYPE = percentage, значение reject_value должно быть числом с плавающей точкой от 0 до 100. Процент действителен только для выделенных пулов SQL, где TYPE=HADOOP.

Запрос завершится ошибкой, если число отклоненных строк превышает reject_value. Например, если задано REJECT_VALUE = 5 и REJECT_TYPE = value, запрос SELECT завершится ошибкой после отклонения пяти строк.

REJECT_SAMPLE_VALUE = reject_sample_value

Параметр reject_sample_value должен быть целым числом от 0 до 2 147 483 647.

Примечание.

Пример:

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

REJECTED_ROW_LOCATION = расположение каталога

Если указанный путь не существует, он будет создан. Дочерний каталог создается с именем _rejectedrows. Символ _ гарантирует, что каталог экранируется для другой обработки данных, если не указано явно в параметре расположения.

В бессерверных пулах SQL путь — YearMonthDay_HourMinuteSecond_StatementIDэто путь. Вы можете использовать идентификатор инструкции, чтобы сопоставить папку с запросом, в результате которого она была создана.
В выделенных пулах SQL создается путь на основе времени отправки нагрузки в формате YearMonthDay -HourMinuteSecond, например 20180330-173205.

В этой папке записываются два типа файлов, _reason файл и файл данных.

Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE.

В бессерверных пулах error.json SQL файл содержит массив JSON с возникшими ошибками, связанными с отклоненными строками. Каждый элемент, представляющий ошибку, имеет следующие атрибуты:

Атрибут	Description
Ошибка	Причина, по которой строка отклонена.
Строка	Порядковый номер отклоненной строки в файле.
Column	Порядковый номер отклоненного столбца.
Значение	Значение отклоненного столбца. Если длина значения превышает 100 символов, будут показаны только первые 100.
Файлы	Путь к файлу, которому принадлежит строка.

Разрешения

Требуются следующие разрешения:

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

Примечание.

Разрешения CONTROL DATABASE необходимы для создания только MASTER KEY, DATABASE SCOPED CREDENTIAL и EXTERNAL DATA SOURCE.

Обратите внимание, что имя входа, создающее внешний источник данных, должно иметь разрешение на чтение и запись во внешний источник данных, расположенный в Hadoop или Хранилище BLOB-объектов Azure.

Внимание

Обработка ошибок

Замечания

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

Обратите внимание на исходные данные с помощью параметров сортировки UTF-8. Для любых исходных данных с помощью сортировки UTF-8 необходимо вручную указать параметры сортировки, отличные от UTF-8, каждый столбец UTF-8 в инструкции CREATE EXTERNAL TABLE. Это связано с тем, что поддержка UTF-8 не распространяется на внешние таблицы. При попытке создать внешнюю таблицу с параметрами сортировки UTF-8 вы получите сообщение об ошибке Unsupported collation . Если параметры сортировки базы данных внешней таблицы являются параметрами сортировки UTF-8, создание внешней таблицы завершится ошибкой, если не указать явное параметры сортировки столбцов, отличные от UTF-8, например [UTF8_column] varchar(128) COLLATE LATIN1_GENERAL_100_CI_AS_KS_WS NOT NULL,.

Бессерверные и выделенные пулы SQL в Azure Synapse Analytics используют различные базы кода для виртуализации данных. Бессерверные пулы SQL поддерживают собственную технологию виртуализации данных. Выделенные пулы SQL поддерживают как собственную, так и виртуализацию данных PolyBase. Виртуализация данных PolyBase используется при создании TYPE=HADOOPвнешнего источника данных.

ограничения

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

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

Только эти инструкции DDL допускаются для внешних таблиц:

CREATE TABLE и DROP TABLE
CREATE STATISTICS и DROP STATISTICS
CREATE VIEW и DROP VIEW

Неподдерживаемые конструкции и операции:

Ограничение DEFAULT для столбцов внешней таблицы
Операции DML обновления, вставки и удаления
Динамическое маскирование данных для столбцов внешней таблицы

Ограничения запросов

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

Ограничения по ширине таблицы

В PolyBase в хранилище данных Azure ширина строки должна составлять не более 1 МБ в связи с максимальным размером допустимой строки в определении таблицы. Если схема суммы столбцов превышает 1 МБ, PolyBase не сможет запросить данные.

Ограничения типов данных

Во внешних таблицах PolyBase нельзя использовать следующие типы данных:

geography
geometry
hierarchyid
image
text
nText
xml
Любой пользовательский тип

Блокировка

Общая блокировка на объект SCHEMARESOLUTION.

Примеры

А. Импорт данных из ADLS 2-го поколения в Azure Synapse Analytics

Примеры для ADLS 1-го поколения см. в статье Создание внешнего источника данных.

-- These values come from your Azure Active Directory Application used to authenticate to ADLS Gen 2.
CREATE DATABASE SCOPED CREDENTIAL ADLUser
WITH IDENTITY = '<clientID>@\<OAuth2.0TokenEndPoint>',
SECRET = '<KEY>' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'abfss://data@pbasetr.azuredatalakestore.net'
);

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT
    , FORMAT_OPTIONS ( FIELD_TERMINATOR = '|'
       , STRING_DELIMITER = ''
      , DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'
      , USE_TYPE_DEFAULT = FALSE
      )
);

CREATE EXTERNAL TABLE [dbo].[DimProduct_external]
( [ProductKey] [int] NOT NULL,
  [ProductLabel] nvarchar NULL,
  [ProductName] nvarchar NULL )
WITH
(
    LOCATION='/DimProduct/' ,
    DATA_SOURCE = AzureDataLakeStore ,
    FILE_FORMAT = TextFileFormat ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

CREATE TABLE [dbo].[DimProduct]
WITH (DISTRIBUTION = HASH([ProductKey] ) )
AS SELECT * FROM
[dbo].[DimProduct_external] ;

B. Импорт данных из Parquet в Azure Synapse Analytics

В следующем примере создается внешняя таблица. Затем он возвращает первую строку:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
);
GO
SELECT TOP 1 * FROM census_external_table;

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

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

База данных SQL Azure

* Analytics
Platform System (PDW) *

Обзор: система платформы аналитики

Внешняя таблица используется в следующих целях:

Запрос данных из Hadoop или Хранилища BLOB-объектов Azure с помощью инструкций Transact-SQL.
Импорт и хранение данных из Hadoop или Хранилище BLOB-объектов Azure в систему платформы Аналитики.

См. также CREATE EXTERNAL DATA SOURCE и DROP EXTERNAL TABLE.

Синтаксис

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

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

}

Аргументы

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Имя создаваемой таблицы, состоящее из одной, двух или трех частей. Для внешней таблицы система платформы Аналитики хранит только метаданные таблицы, а также базовую статистику о файле или папке, на которую ссылается Hadoop или Хранилище BLOB-объектов Azure. Никакие данные не перемещаются и не хранятся в Системе платформы аналитики.

Внимание

<column_definition> [ ,...n ]

LOCATION = путь_к_папке_или_файлу

Указывает путь к папке или файлу и имя файла для фактических данных в Хранилище BLOB-объектов Azure или Hadoop. Расположение начинается с корневой папки. Она представляет собой расположение данных, указанное во внешнем источнике данных.

В Системе платформы аналитики инструкция CREATE EXTERNAL TABLE AS SELECT создает путь к папке и саму папку, если она еще не существует. CREATE EXTERNAL TABLE не создает путь к папке.

Схема папок и файловых данных для внешних таблиц.

Чтобы изменить значение по умолчанию и считывать данные только из корневой папки, установите для атрибута <polybase.recursive.traversal> значение false в файле конфигурации core-site.xml. Этот файл находится под <SqlBinRoot>\PolyBase\Hadoop\Conf\ корнем bin SQL Server. Например, C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn\.

DATA_SOURCE = external_data_source_name

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

FILE_FORMAT = external_file_format_name

Параметры отклонения

Этот параметр можно использовать только с внешними источниками данных с TYPE = HADOOP.

REJECT_TYPE = value | percentage

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

значение

Например, если задано REJECT_VALUE = 5 и REJECT_TYPE = value, запрос PolyBase SELECT завершится ошибкой после отклонения пяти строк.

процент

REJECT_VALUE = reject_value

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

Если задано REJECT_TYPE = value, значение reject_value должно быть целым числом от 0 до 2 147 483 647.

Если задано REJECT_TYPE = percentage, значение reject_value должно быть числом с плавающей точкой от 0 до 100.

REJECT_SAMPLE_VALUE = reject_sample_value

Параметр reject_sample_value должен быть целым числом от 0 до 2 147 483 647.

Примечание.

Пример:

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

Разрешения

Требуются следующие разрешения:

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
CONTROL DATABASE

Внимание

Обработка ошибок

Замечания

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

ограничения

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

Только эти инструкции DDL допускаются для внешних таблиц:

CREATE TABLE и DROP TABLE
CREATE STATISTICS и DROP STATISTICS
CREATE VIEW и DROP VIEW

Неподдерживаемые конструкции и операции:

Ограничение DEFAULT для столбцов внешней таблицы
Операции DML обновления, вставки и удаления
Динамическое маскирование данных для столбцов внешней таблицы

Ограничения запросов

Ограничения по ширине таблицы

В Azure Synapse Analytics это ограничение было увеличено до 1 МБ.

Ограничения типов данных

Во внешних таблицах PolyBase нельзя использовать следующие типы данных:

geography
geometry
hierarchyid
image
text
nText
xml
Любой пользовательский тип

Блокировка

Общая блокировка на объект SCHEMARESOLUTION.

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

Примеры

А. Соединение данных HDFS с данными Системы платформы аналитики

SELECT cs.user_ip FROM ClickStream cs
JOIN [User] u ON cs.user_ip = u.user_ip
WHERE cs.url = 'www.microsoft.com';

B. Импорт данных строк из HDFS в распределенную таблицу Системы платформы аналитики

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = HASH (url) )
AS SELECT url, event_date, user_ip FROM ClickStream;

C. Импорт данных строк из HDFS в реплицированную таблицу Системы платформы аналитики

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = REPLICATE )
AS SELECT url, event_date, user_ip
FROM ClickStream;

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

Дополнительные сведения о внешних таблицах в Analytics Platform System см. в следующих статьях:

База данных SQL Azure

* Управляемый экземпляр SQL Azure *