Создание и изменение внешних таблиц службы хранилища Azure

Статья
11/23/2024

Область применения: ✅Microsoft Fabric ✅Azure Data Explorer

Команды, приведенные в этой статье, можно использовать для создания или изменения служба хранилища 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) external table Схема storage =) kindtableName( [partition by(Секции) [pathformat= (PathFormat)]] dataformat = DataFormat (StorageConnectionString [, ...] ) [ Свойство [,with (...]])

Примечание.

kindпредназначен storage для всех служба хранилища Azure типов внешнего хранилища данных. blob и adl являются устаревшими терминами.

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

Параметры

Имя (название)	Type	Обязательно	Описание
TableName	`string`	✔️	Имя внешней таблицы, которое соответствует правилам имен сущностей . Внешняя таблица не может иметь то же имя, что и обычная таблица в той же базе данных.
Схема	`string`	✔️	Схема внешних данных представляет собой разделенный запятыми список одного или нескольких имен столбцов и типов данных, где каждый элемент соответствует формату ColumnName `:` ColumnType. Если схема неизвестна, используйте infer_storage_schema для вывода схемы на основе содержимого внешнего файла.
Секции	`string`		Разделенный запятыми список столбцов, по которым секционируется внешняя таблица. Столбец секционирования может существовать в самом файле данных или как часть пути к файлу. Ознакомьтесь с форматированием секций, чтобы узнать, как это значение должно выглядеть.
PathFormat	`string`		Формат пути к папке внешних данных URI для использования с секциями. См . формат пути.
DataFormat	`string`	✔️	Формат данных, который может быть любым из форматов приема. Мы рекомендуем использовать `Parquet` формат для внешних таблиц для повышения производительности запросов и экспорта, если только не используется `JSON` сопоставление путей. При использовании внешней таблицы для сценария экспорта вы ограничены следующими форматами: `CSV`и `TSVJSON` `Parquet`.
StorageConnectionString	`string`	✔️	Один или несколько путей, разделенных запятыми, для Хранилище BLOB-объектов Azure контейнеров BLOB-объектов, файловых систем Azure Data Lake 2-го поколения или контейнеров Azure Data Lake 1-го поколения, включая учетные данные. Тип хранилища внешней таблицы определяется указанными строка подключения. См. строка подключения хранилища.
Свойство	`string`		Пара свойств key-value в формате PropertyName `=` PropertyValue. См . необязательные свойства.

Примечание.

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

Совет

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

Проверка подлинности и авторизация

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

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

Authentication method	Хранилище 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` \| `startofmonth` \| `startofweek` \| `startofday` `)`	См. документацию по функциям startofyear, startofmonth , startofweek или startofday.
Усеченное значение столбца `=` `(` `bin` datetime ColumnName `,` TimeSpan `)`	Дополнительные сведения о функции bin .

Формат пути

Параметр PathFormat позволяет указать формат для пути URI внешней папки данных в дополнение к секциям. Он состоит из последовательности элементов секционирования и разделителей текста. Элемент секции ссылается на секцию, объявленную в предложении секции by , а разделитель текста — любой текст, заключенный в кавычки. Последовательные элементы секции должны быть разделены с помощью разделителя текста.

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

Чтобы создать префикс исходного пути к файлу, элементы секции отображаются как строки и разделены соответствующими разделителями текста. Макрос (DateTimeFormat, PartitionName)) можно использовать datetime_pattern для указания формата, используемого для отображения значения секции datetime.datetime_pattern( Макрос соответствует спецификации формата .NET и позволяет описателям формата заключаться в фигурные скобки. Например, следующие два формата эквивалентны:

'year='yyyy'/month='MM
year={yyyy}/month={MM}

По умолчанию значения datetime отображаются с помощью следующих форматов:

Функция секционирования	Формат по умолчанию
`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")

Необязательные свойства

Свойство	Type	Описание
`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`, параметр помогает проверить параметры Секций и PathFormat для определения внешней таблицы.
`validateNotEmpty`	`bool`	Если задано, строка подключения проверяются на наличие содержимого в них. Эта команда завершится ошибкой, если указанное расположение URI не существует или недостаточно разрешений для доступа к нему.
`dryRun`	`bool`	Если задано, определение внешней таблицы не сохраняется. Этот параметр полезен для проверки определения внешней таблицы, особенно в сочетании с параметром или `sampleUris` параметром`filesPreview`.

Примечание.

Во время создания внешняя таблица не обращается только во время запроса и экспорта. Используйте необязательное 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' 
)

Секционирование по хэшу и дате

Следующая внешняя таблица секционируется сначала хэшом имени клиента (modulo ten), а затем по дате. Ожидаемая структура каталогов, например, 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	Секции	PathFormat
ExternalTable	BLOB-объект	ExternalTables	Документация	{"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("yyyyMdd",Date)

Запрос внешних таблиц.

Поделиться через

Создание и изменение внешних таблиц службы хранилища Azure

Разрешения

Синтаксис

Параметры

Проверка подлинности и авторизация

Форматирование секций

Формат пути

Виртуальные столбцы

Необязательные свойства

Логика фильтрации файлов

Примеры

Внешняя таблица без секционирования

Секционирование по дате

Секционирование по месяцам

Секционирование по имени и дате

Секционирование по хэшу и дате

Фильтрация по столбцам секционирования в запросе

Обратная связь

Дополнительные ресурсы

Поделиться через

Создание и изменение внешних таблиц службы хранилища Azure

Разрешения

Синтаксис

Параметры

Проверка подлинности и авторизация

Форматирование секций

Формат пути

Виртуальные столбцы

Необязательные свойства

Логика фильтрации файлов

Примеры

Внешняя таблица без секционирования

Секционирование по дате

Секционирование по месяцам

Секционирование по имени и дате

Секционирование по хэшу и дате

Фильтрация по столбцам секционирования в запросе

Связанный контент

Обратная связь

Дополнительные ресурсы