CREATE EXTERNAL TABLE (Transact-SQL)

Создает внешнюю таблицу.

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

Дополнительные сведения о соглашениях о синтаксисе см. в статье Соглашения о синтаксисе в Transact-SQL.

Выберите продукт

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

* SQL Server *  

База данных SQL

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

Azure Synapse
Аналитики

Analytics Platform
System (PDW)

 

Обзор: SQL Server

Эта команда создает внешнюю таблицу для PolyBase для доступа к данным, хранящимся в кластере Hadoop или внешней таблице PolyBase хранилища BLOB-объектов Azure, которая ссылается на данные, хранящиеся в кластере 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. Эти файлы данных создаются и управляются вашими собственными процессами. В ваши обязанности входит управление безопасностью внешних данных.

Примеры

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

В этом примере показаны все действия по созданию внешней таблицы с данными, отформатированными в виде файлов с текстовыми разделителями. В нем определяется внешний источник данных 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
    )
;

Б. Создайте внешнюю таблицу с данными в формате 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
    )
;

В. Создайте внешнюю таблицу с данными в формате 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';

Е. Импорт данных из 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

Дальнейшие действия

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

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• Расширение Data Virtualization для Azure Data Studio