Создание и изменение внешних таблиц службы хранилища Azure
Команды, приведенные в этой статье, можно использовать для создания или изменения внешней таблицы службы хранилища Azure в базе данных, из которой выполняется команда. Внешняя таблица службы хранилища Azure ссылается на данные, расположенные в Хранилище BLOB-объектов Azure, Azure Data Lake Store 1-го поколения или Azure Data Lake Store 2-го поколения.
Примечание
Если таблица существует, .create команда завершится ошибкой. Используйте .create-or-alter или .alter для изменения существующих таблиц.
Разрешения
Для .create требуются по крайней мере разрешения пользователя базы данных, а для — .alter по крайней мере разрешения на Администратор таблиц.
Для .create-or-alter внешней таблицы с использованием проверки подлинности с помощью управляемого удостоверения требуются разрешения AllDatabasesAdmin .
Синтаксис
(.create | .alter.create-or-alter | ) externaltableСхема)=storagekindTableName( [partitionby(Секции) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Свойство [, ...])]
Примечание
kind предназначен storage для всех типов внешних хранилищ данных службы хранилища Azure. blob и adl являются устаревшими терминами.
Дополнительные сведения о соглашениях о синтаксисе.
Параметры
| Имя | Тип | Обязательно | Описание |
|---|---|---|---|
| TableName | string |
✔️ | Имя внешней таблицы, которая соответствует правилам имен сущностей . Имя внешней таблицы не может совпадать с именем обычной таблицы в той же базе данных. |
| Схема | string |
✔️ | Схема внешних данных представляет собой разделенный запятыми список из одного или нескольких имен столбцов и типов данных, где каждый элемент имеет формат ColumnName:ColumnType. Если схема неизвестна, используйте infer_storage_schema для вывода схемы на основе содержимого внешнего файла. |
| Секции | string |
Разделенный запятыми список столбцов, по которым секционируется внешняя таблица. Столбец секционирования может существовать в самом файле данных или как часть пути к файлу. Сведения о том, как должно выглядеть это значение, см. в разделе Форматирование секций . | |
| Формат пути | string |
Формат пути URI к папке внешних данных для использования с секциями. См. раздел Формат пути. | |
| DataFormat | string |
✔️ | Формат данных, который может быть любым из форматов приема. Мы рекомендуем использовать Parquet формат для внешних таблиц, чтобы повысить производительность запросов и экспорта, если не используется JSON сопоставление путей. При использовании внешней таблицы для сценария экспорта можно использовать только следующие форматы: CSV, TSVJSON и Parquet. |
| StorageConnectionString | string |
✔️ | Один или несколько разделенных запятыми путей к Хранилище BLOB-объектов Azure контейнерам BLOB-объектов, файловой системе Azure Data Lake 2-го поколения или контейнерам Azure Data Lake 1-го поколения, включая учетные данные. Тип хранилища внешней таблицы определяется предоставленными строками подключения. См. статью Строки подключения к хранилищу. |
| Свойство | string |
Пара свойств "ключ-значение" в формате PropertyName=PropertyValue. См . дополнительные свойства. |
Примечание
CSV-файлы с не идентичной схемой могут привести к смещению или отсутствовать. Рекомендуется разделять CSV-файлы с отдельными схемами для разделения контейнеров хранилища и определять внешнюю таблицу для каждого контейнера хранилища с правильной схемой.
Совет
Предоставьте более одной учетной записи хранения, чтобы избежать регулирования хранилища при экспорте больших объемов данных во внешнюю таблицу. Экспорт распределяет операции записи между всеми предоставленными учетными записями.
Аутентификация и авторизация
Метод проверки подлинности для доступа к внешней таблице основан на строка подключения, предоставленных во время ее создания, и разрешения, необходимые для доступа к таблице, зависят от метода проверки подлинности.
В следующей таблице перечислены поддерживаемые методы проверки подлинности для внешних таблиц службы хранилища Azure и разрешения, необходимые для чтения или записи в таблицу.
| Метод проверки подлинности | Хранилище BLOB-объектов Azure / Data Lake Storage 2-го поколения | Azure Data Lake Storage 1-го поколения |
|---|---|---|
| Олицетворение | Разрешения на чтение: Читатель данных BLOB-объектов хранилища Разрешения на запись: Участник данных BLOB-объектов хранилища |
Разрешения на чтение: Читатель Разрешения на запись: Вклад |
| Управляемое удостоверение | Разрешения на чтение: Читатель данных BLOB-объектов хранилища Разрешения на запись: Участник данных BLOB-объектов хранилища |
Разрешения на чтение: Читатель Разрешения на запись: Вклад |
| Маркер общего доступа (SAS) | Разрешения на чтение: Список и чтение Разрешения на запись: Написать |
Этот метод проверки подлинности не поддерживается в 1-м м поколениях. |
| маркер доступа Microsoft Entra | Дополнительные разрешения не требуются. | Дополнительные разрешения не требуются. |
| Ключ доступа к учетной записи хранения | Дополнительные разрешения не требуются. | Этот метод проверки подлинности не поддерживается в 1-м м поколениях. |
Форматирование секций
Список секций представляет собой любое сочетание столбцов секционирования, указанное с помощью одной из форм, показанных в следующей таблице.
| Тип секции | Синтаксис | Примечания |
|---|---|---|
| Виртуальный столбец | PartitionName: (datetime | string) |
Дополнительные сведения о виртуальных столбцах. |
| Значение строкового столбца | PartitionName:string=Имя столбца |
|
| Хэш значений строкового столбца | PartitionName:long=hash(Имя столбца,Номер) |
Хэш является номером по модулю. |
| Усеченный столбец datetime (значение) | PartitionName:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) |
См. документацию по функциям startofyear, startofmonth, startofweek или startofday . |
| Усеченное значение столбца даты и времени (bin) | PartitionName:datetime=bin(Имя столбца,Timespan) |
См. дополнительные сведения о функции bin . |
Формат пути
Параметр PathFormat позволяет указать формат пути URI для папки внешних данных в дополнение к секциям. Он состоит из последовательности элементов секционирования и разделителей текста. Элемент partition ссылается на секцию, объявленную в предложении partition by , а разделитель текста — это любой текст, заключенный в кавычки. Последовательные элементы секционирования должны быть разделены с помощью разделителя текста.
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
Чтобы создать исходный префикс пути к файлу, элементы секции отрисовываются в виде строк и разделяются соответствующими разделителями текста. С помощью макроса datetime_pattern (datetime_pattern(DateTimeFormat,PartitionName)) можно указать формат, используемый для отрисовки значения секции даты и времени. Макрос соответствует спецификации формата .NET и позволяет заключать описатели формата в фигурные скобки. Например, следующие два формата эквивалентны:
- 'год='гггг'/месяц='ММ
- year={yyyy}/month={MM}
По умолчанию значения даты и времени отрисовываются в следующих форматах:
| Функция секционирования | Формат по умолчанию |
|---|---|
startofyear |
yyyy |
startofmonth |
yyyy/MM |
startofweek |
yyyy/MM/dd |
startofday |
yyyy/MM/dd |
bin(Столбца, 1d) |
yyyy/MM/dd |
bin(Столбца, 1h) |
yyyy/MM/dd/HH |
bin(Столбца, 1m) |
yyyy/MM/dd/HH/mm |
Совет
Чтобы проверка правильность определения секций и PathFormat, используйте свойство sampleUris или filesPreview при создании внешней таблицы.
Виртуальные столбцы
При экспорте данных из Spark столбцы секций (предоставленные методу модуля записи кадра partitionBy данных) не записываются в файлы данных.
Этот процесс позволяет избежать дублирования данных, так как данные уже присутствуют в именах папок (например, column1=<value>/column2=<value>/), и Spark может распознать их при чтении.
Внешние таблицы поддерживают чтение этих данных в виде virtual colums. Виртуальные столбцы могут быть любого типа string или datetime, и указываются с помощью следующего синтаксиса:
.create external table ExternalTable (EventName:string, Revenue:double)
kind=storage
partition by (CustomerName:string, Date:datetime)
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))
dataformat=parquet
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Чтобы выполнить фильтрацию по виртуальным столбцам в запросе, укажите имена секций в предикате запроса:
external_table("ExternalTable")
| where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Необязательные свойства
| Свойство | Тип | Описание |
|---|---|---|
folder |
string |
Папка таблицы |
docString |
string |
Строка документирования таблицы |
compressed |
bool |
Если задано значение , указывает, сжимаются ли файлы как .gz файлы (используются только в сценарии экспорта ). |
includeHeaders |
string |
Для текстовых форматов с разделителями (CSV, TSV, ...) указывает, содержат ли файлы заголовок. Возможные значения: All (все файлы содержат заголовок), FirstFile (первый файл в папке содержит заголовок), None (заголовок не содержится). |
namePrefix |
string |
Если задано значение , указывает префикс файлов. При операциях записи все файлы будут записываться с этим префиксом. При операциях чтения считываются только файлы с этим префиксом. |
fileExtension |
string |
Если задано значение , указывает расширения файлов. При записи имена файлов будут заканчиваться этим суффиксом. При чтении будут считываться только файлы с этим расширением. |
encoding |
string |
Указывает, как кодируется текст: UTF8NoBOM (по умолчанию) или UTF8BOM. |
sampleUris |
bool |
Если задано значение , результат команды предоставляет несколько примеров URI смоделированных внешних файлов данных, как они ожидаются определением внешней таблицы. Этот параметр помогает проверить правильность определения параметров Partitions и PathFormat . |
filesPreview |
bool |
Если этот параметр задан, одна из таблиц результатов команды содержит предварительный просмотр команды .show external table artifacts . Как и sampleUri, параметр помогает проверить параметры Partitions и PathFormat определения внешней таблицы. |
validateNotEmpty |
bool |
Если задано значение , строки подключения проверяются на наличие в них содержимого. Команда завершится ошибкой, если указанное расположение универсального кода ресурса (URI) не существует или если нет достаточных разрешений для доступа к нему. |
dryRun |
bool |
Если задано, определение внешней таблицы не сохраняется. Этот параметр полезен для проверки определения внешней таблицы, особенно в сочетании с параметром filesPreview или sampleUris . |
Примечание
Доступ к внешней таблице не выполняется во время создания, только во время запроса и экспорта. Используйте необязательное validateNotEmpty свойство во время создания, чтобы убедиться, что определение таблицы является допустимым и хранилище доступно.
Совет
Дополнительные сведения о роли namePrefix и fileExtension свойствах, которые играют в фильтрации файлов данных во время запроса, см. в разделе Логика фильтрации файлов .
Логика фильтрации файлов
При запросе внешней таблицы производительность улучшается за счет фильтрации нерелевантных файлов внешнего хранилища. Процесс итерации файлов и принятия решения о том, следует ли обрабатывать файл, выглядит следующим образом:
Создайте шаблон URI, представляющий место, где находятся файлы. Изначально шаблон URI равен строка подключения, предоставленному как часть определения внешней таблицы. Если определены какие-либо секции, они отрисовываются с помощью PathFormat, а затем добавляются в шаблон URI.
Для всех файлов, найденных в созданных шаблонах URI, проверка, которые:
- Значения секций соответствуют предикатам, используемым в запросе.
- Имя BLOB-объекта начинается с
NamePrefix, если такое свойство определено. - Имя BLOB-объекта заканчивается
FileExtensionна , если такое свойство определено.
После выполнения всех условий файл извлекается и обрабатывается.
Примечание
Исходный шаблон URI создается с использованием значений предиката запроса. Это лучше всего подходит для ограниченного набора строковых значений, а также для закрытых диапазонов времени.
Примеры
Несекционированная внешняя таблица
В следующей несекционируемой внешней таблице файлы должны размещаться непосредственно в определенных контейнерах:
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Секционирование по дате
В следующей внешней таблице, секционируемой по дате, файлы должны быть помещены в каталоги формата yyyy/MM/dddatetime по умолчанию:
.create external table ExternalTable (Timestamp:datetime, x:long, s:string)
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d))
dataformat=csv
(
h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)
Секционировано по месяцам
В следующей внешней таблице, секционируемой по месяцам, каталог имеет year=yyyy/month=MMформат :
.create external table ExternalTable (Timestamp:datetime, x:long, s:string)
kind=storage
partition by (Month:datetime = startofmonth(Timestamp))
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month))
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Секционирование по имени и дате
В следующей внешней таблице данные сначала секционируются по имени клиента, а затем по дате. Это означает, что ожидаемая структура каталогов, например: customer_name=Softworks/2019/02/01
.create external table ExternalTable (Timestamp:datetime, CustomerName:string)
kind=storage
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp))
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Секционирование по хэшу и дате
Следующая внешняя таблица сначала секционируется по хэшу имени клиента (по модулю десять), а затем по дате. Ожидаемая структура каталогов, например, customer_id=5/dt=20190201, а имена файлов данных заканчиваются расширением .txt :
.create external table ExternalTable (Timestamp:datetime, CustomerName:string)
kind=storage
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp))
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date))
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")
Фильтрация по столбцам секционирования в запросе
Чтобы выполнить фильтрацию по столбцам секционирования в запросе, укажите имя исходного столбца в предикате запроса:
external_table("ExternalTable")
| where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Образец вывода
| TableName | TableType | Папка | DocString | Свойства | ConnectionStrings | Секции | Формат пути |
|---|---|---|---|---|---|---|---|
| ExternalTable | BLOB-объект | ExternalTables | Docs | {"Format":"Csv","Compressed":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} | ["https://storageaccount.blob.core.windows.net/container1;*******"] | [{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] | "customer_id=" CustomerId "/dt=" datetime_pattern("ггггММдд",Дата) |
См. также
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по