Использование внешних таблиц в Synapse SQL

Внешняя таблица указывает на данные, расположенные в Hadoop, большом двоичном объекте в службе хранилища Azure или Azure Data Lake Storage. Внешние таблицы используются для чтения данных из файлов или записи данных в файлы, расположенные в службе хранилища Azure. В Synapse SQL внешние таблицы можно использовать для чтения внешних данных с помощью выделенного пула SQL или бессерверного пула SQL.

В зависимости от типа внешнего источника данных можно использовать два типа внешних таблиц:

  • Внешние таблицы Hadoop, которые можно использовать для чтения и экспорта данных в различных форматах данных, таких как CSV, Parquet и ORC. Внешние таблицы Hadoop доступны в выделенных пулах SQL, но недоступны в бессерверных пулах SQL.
  • Собственные внешние таблицы, которые можно использовать для чтения и экспорта данных в различных форматах данных, таких как CSV и Parquet. Собственные внешние таблицы доступны в бессерверных пулах SQL. Они предоставляются в общедоступной предварительной версии в выделенных пулах SQL.

Основные различия между Hadoop и собственными внешними таблицами представлены в следующей таблице.

Тип внешней таблицы Hadoop Собственный
Выделенный пул SQL Доступно Таблицы Parquet предоставляются в общедоступной предварительной версии.
Бессерверный пул SQL Недоступно Доступно
Поддерживаемые форматы С разделителями/CSV, Parquet, ORC, Hive RC и RC Бессерверный пул SQL: с разделителями/CSV, Parquet и Delta Lake (предварительная версия)
Выделенный пул SQL: Паркет
Устранение секций папок Нет Только для секционированных таблиц, синхронизированных из пулов Apache Spark в рабочей области Synapse с бессерверными пулами SQL
Пользовательский формат расположения Да Да, с помощью подстановочных знаков, например /year=*/month=*/day=*
Рекурсивное сканирование папок Нет Только в бессерверных пулах SQL, если в конце пути расположения указано /**
Включение фильтра хранилища Нет Да в бессерверном пуле SQL. Для раскрытия строки необходимо использовать параметры сортировки Latin1_General_100_BIN2_UTF8для столбцовVARCHAR.
Проверка подлинности хранилища Ключ доступа к хранилищу, транзитная служба AAD, управляемое удостоверение, пользовательское удостоверение приложения Azure AD Подписанный URL-адрес (SAS), транзитный ресурс AAD, управляемое удостоверение

Примечание

Собственные внешние таблицы в формате Delta Lake находятся в общедоступной предварительной версии. Дополнительные сведения см. в статье Запрашивание файлов Delta Lake (предварительная версия). CETAS не поддерживает экспорт контента в формате Delta Lake.

Внешние таблицы в выделенном пуле SQL и бессерверном пуле SQL

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

  • Запрос по данным в хранилище BLOB-объектов Azure и Azure Data Lake Storage 2-го поколения с использованием инструкций Transact-SQL.
  • Сохраните результаты запросов в файлах в Хранилище BLOB-объектов Azure или Azure Data Lake Storage с помощью CETAS.
  • Импортируйте данные из хранилища BLOB-объектов Azure и Azure Data Lake Storage и храните их в выделенном пуле SQL (только таблицы Hadoop в выделенном пуле).

Примечание

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

Учебник по загрузке данных см. в статье Загрузка данных из хранилища BLOB-объектов Azure в хранилище данных SQL Azure с помощью PolyBase.

Вы можете создать внешние таблицы в пулах Synapse SQL, выполнив следующие действия:

  1. СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ для ссылки на внешнее хранилище Azure и указать учетные данные, которые следует использовать для доступа к хранилищу.
  2. СОЗДАТЬ ВНЕШНИЙ ФОРМАТ ФАЙЛА для описания формата файлов CSV или Parquet.
  3. СОЗДАТЬ ВНЕШНЮЮ ТАБЛИЦУ поверх файлов, размещенных в источнике данных с таким же форматом файлов.

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

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

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

CREATE EXTERNAL DATA SOURCE

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

Синтаксис инструкции CREATE EXTERNAL DATA SOURCE

Внешние источники данных с TYPE=HADOOP доступны только в выделенных пулах SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

Аргументы инструкции CREATE EXTERNAL DATA SOURCE

data_source_name

Задает определенное пользователем имя для источника данных. В базе данных это имя должно быть уникальным.

Расположение

LOCATION = '<prefix>://<path>' предоставляет протокол и путь подключения к внешнему источнику данных. В расположении можно использовать следующие шаблоны:

Внешний источник данных Префикс расположения Путь к расположению
хранилище BLOB-объектов Azure wasb[s] <container>@<storage_account>.blob.core.windows.net
хранилище BLOB-объектов Azure http[s] <storage_account>.blob.core.windows.net/<container>/subfolders
Azure Data Lake Store Gen 1 http[s] <storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Store Gen 2 http[s] <storage_account>.dfs.core.windows.net/<container>/subfolders

Префикс https: позволяет использовать в пути вложенную папку.

Учетные данные

CREDENTIAL = <database scoped credential>. Это необязательные учетные данные, которые будут использоваться для проверки подлинности в службе хранилища Azure. Внешний источник данных без учетных данных может получить доступ к общедоступной учетной записи хранения или использовать удостоверение Azure AD вызывающей стороны для доступа к файлам в хранилище.

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

TYPE

TYPE = HADOOP — параметр, который указывает, что для доступа к базовым файлам следует использовать технологию на основе Java. Этот параметр не может использоваться в бессерверном пуле SQL, если в него встроен собственный модуль чтения.

Пример инструкции CREATE EXTERNAL DATA SOURCE

Следующий пример создает внешний источник данных Hadoop в выделенном пуле SQL для Azure Data Lake 2-го поколения, который указывает на набор данных New York:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

В следующем примере создается внешний источник данных для Azure Data Lake 2-го поколения, который указывает на общедоступный набор данных New York.

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

CREATE EXTERNAL FILE FORMAT

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

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

Синтаксис инструкции CREATE EXTERNAL FILE FORMAT

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

Аргументы инструкции CREATE EXTERNAL FILE FORMAT

file_format_name задает имя формата внешнего файла.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT] задает формат внешних данных.

  • PARQUET задает формат Parquet.
  • DELIMITEDTEXT задает формат текста с разделителями столбцов (которые также называются признаками конца поля).

FIELD_TERMINATOR = field_terminator применяется только для текстовых файлов с разделителями. Признак конца поля задает один или несколько символов, отмечающих окончание каждого поля (столбца) в файле с разделителями текста. По умолчанию используется вертикальная черта ("|").

Примеры:

  • FIELD_TERMINATOR = '|'
  • FIELD_TERMINATOR = ' '
  • FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter задает признак конца поля для данных строкового типа в текстовом файле с разделителями. Разделитель строк имеет длину один или несколько символов и заключен в одинарные кавычки. По умолчанию используется пустая строка ("").

Примеры:

  • STRING_DELIMITER = '"'
  • STRING_DELIMITER = '*'
  • STRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int указывает номер строки, которая считывается первой во всех файлах. Если задано значение 2, при загрузке данных в каждом файле пропускается первая строка (строка заголовка). Строки пропускаются по признакам конца строк (/r/n, /r, /n).

USE_TYPE_DEFAULT = { TRUE | FALSE } указывает способ обработки отсутствующих значений в текстовых файлах с разделителями, когда извлекаются данные из текстового файла.

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

  • 0, если столбец определен как числовой. Столбцы decimal не поддерживаются, а их наличие вызовет ошибку.
  • Пустая строка (""), если столбец является строковым.
  • 1900-01-01, если столбец является столбцом дат.

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

Encoding = {'UTF8' | 'UTF16'} — бессерверный пул SQL умеет считывать текстовые файлы с разделителями в кодировке UTF8 или UTF16.

DATA_COMPRESSION = data_compression_method задает метод сжатия данных для внешних данных.

Тип файлового формата PARQUET поддерживает следующие методы сжатия:

  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

Этот аргумент игнорируется при чтении из внешних таблиц PARQUET, но используется при записи во внешние таблицы с помощью CETAS.

Тип формата файлов DELIMITEDTEXT поддерживает следующий метод сжатия:

  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = 'parser_version' позволяет указать версию средства синтаксического анализа, которая используется при чтении CSV-файлов. Доступные версии средства синтаксического анализа: 1.0 и 2.0. Этот параметр доступен только в бессерверных пулах SQL.

Пример инструкции CREATE EXTERNAL FILE FORMAT

В следующем примере создается формат внешнего файла для данных о переписи населения:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

CREATE EXTERNAL TABLE

Инструкция CREATE EXTERNAL TABLE создает в Synapse SQL внешнюю таблицу для доступа к данным в хранилище BLOB-объектов Azure или Azure Data Lake Storage.

Синтаксис инструкции CREATE 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
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<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'
}   

Аргументы инструкции CREATE EXTERNAL TABLE

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

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

<column_definition>, ...n ]

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

Важно!

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

При чтении из файлов Parquet можно указать только нужные столбцы, чтобы пропустить все остальные.

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

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

Рекурсивные данные для внешних таблиц

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

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

DATA_SOURCE = external_data_source_name

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

FILE_FORMAT = external_file_format_name

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

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

Примечание

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

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

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

REJECT_TYPE = value

В данный момент это единственное поддерживаемое значение. Уточняет, что параметр REJECT_VALUE указан как литеральное значение.

value

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

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

REJECT_VALUE = reject_value

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

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

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

Указывает каталог во внешнем источнике данных, в который должны записываться строки и соответствующий файл ошибок. Если расположение по указанному пути не существует, служба создаст его от вашего имени. Создается дочерний каталог с именем "rejectedrows". Благодаря символу " " каталог исключается из других процессов обработки данных, если он явно не указан в параметре LOCATION. В этом каталоге создается папка, имя которой соответствует времени отправки загруженных данных. Оно имеет следующий формат: "ГодМесяцДень-ЧасМинутаСекунда-ИдентификаторИнструкции" (например, 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). Вы можете использовать идентификатор инструкции, чтобы сопоставить папку с запросом, в результате которого она была создана. В этой папке записываются два файла: error.json и файл данных.

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

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

TABLE_OPTIONS

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

Разрешения для CREATE EXTERNAL TABLE

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

Пример инструкции CREATE EXTERNAL TABLE

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

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

Создание внешних таблиц из файла в Azure Data Lake и запросы по ним

Используя возможности просмотра Data Lake в Synapse Studio, вы можете создавать внешнюю таблицу и выполнять запросы к ней через пул Synapse SQL, просто щелкнув файл правой кнопкой мыши. Создание внешних таблиц одним щелчком в учетной записи хранения ADLS 2-го поколения поддерживается только для файлов Parquet.

Предварительные требования

  • У вас должен быть доступ к рабочей области с как минимум ролью доступа Storage Blob Data Contributor к учетной записи ADLS Gen2 или спискам управления доступом (ACL), которые позволяют запрашивать файлы.

  • Вам нужны по меньшей мере права на создание внешних таблиц и на выполнение запросов к ним в пуле Synapse SQL (выделенном или бессерверном).

На панели "Данные" выберите файл, из которого нужно создать внешнюю таблицу.

externaltable1

Откроется диалоговое окно. Выберите выделенный или бессерверный пул SQL, присвойте таблице имя и щелкните "Открыть скрипт"

externaltable2

Автоматически создается скрипт SQL на основании схемы, определенной по файлу.

externaltable3

Выполните скрипт. Этот скрипт автоматически выполняет инструкцию SELECT Top 100 *.

externaltable4

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

externaltable5

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

См. статью CETAS , чтобы узнать, как сохранить результаты запроса во внешней таблице в хранилище Azure. Вы также можете начать работать с запросами ко внешним таблицам Apache Spark для Azure Synapse.