Использование OPENROWSET с бессерверным пулом SQL в Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse — это реляционное хранилище корпоративного масштаба на основе озера данных, с архитектурой, готовой к будущему, встроенной ИИ и новыми функциями. Если вы не знакомы с хранилищем данных, начните с Fabric Data Warehouse. Существующие рабочие нагрузки выделенного пула SQL могут обновляться до Fabric для доступа к новым возможностям в области науки о данных, аналитики в реальном времени и отчетности.

Функция OPENROWSET(BULK...) позволяет получить доступ к файлам в службе хранилища Azure. Функция OPENROWSET считывает содержимое удаленного источника данных (например, файла) и возвращает содержимое в виде набора строк. В ресурсе бессерверного пула SQL доступ к поставщику больших наборов строк OPENROWSET осуществляется путем вызова функции OPENROWSET и указанием параметра BULK.

Из предложения OPENROWSET запроса можно ссылаться на функцию FROM, как если бы это было имя таблицы OPENROWSET. Функция также поддерживает массовые операции с помощью встроенного поставщика BULK, позволяющего считывать данные из файла и возвращать их в виде набора строк.

Примечание.

Функция OPENROWSET не поддерживается в выделенном пуле SQL.

Источник данных

Функция OPENROWSET в Synapse SQL считывает содержимое файлов из источника данных. Источник данных — это учетная запись хранения Azure, которую можно явно указать в функции OPENROWSET или динамически выводить из URL-адреса файлов, которые вы намерены считать. Функция OPENROWSET может содержать необязательный параметр DATA_SOURCE для указания источника данных с нужными файлами.

  • OPENROWSET без DATA_SOURCE позволяет считать содержимого файлов напрямую по URL-адресу, указанному в параметре BULK:

    SELECT *
FROM OPENROWSET(BULK 'http://<storage account>.dfs.core.windows.net/container/folder/*.parquet',
                FORMAT = 'PARQUET') AS [file]

Это быстрый и простой способ чтения содержимого файлов без предварительной настройки. Этот параметр позволяет использовать опцию базовой аутентификации для доступа к хранилищу (сквозная аутентификация Microsoft Entra для входов Microsoft Entra и маркер SAS для входов SQL).

  • OPENROWSET с DATA_SOURCE можно использовать для доступа к файлам в указанной учетной записи хранения:

    SELECT *
FROM OPENROWSET(BULK '/folder/*.parquet',
                DATA_SOURCE='storage', --> Root URL is in LOCATION of DATA SOURCE
                FORMAT = 'PARQUET') AS [file]

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

    Внимание

    OPENROWSET без DATA_SOURCE обеспечивает быстрый и простой способ доступа к файлам хранилища, но поддерживает мало возможностей для проверки подлинности. Например, учетные записи Microsoft Entra могут получать доступ к файлам только с помощью своей учетной записи Microsoft Entra или к общедоступным файлам. Если вам нужны более мощные возможности для проверки подлинности, используйте параметр DATA_SOURCE и определите учетные данные, которые нужно использовать для доступа к хранилищу.

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

Пользователь базы данных должен иметь разрешение ADMINISTER BULK OPERATIONS, чтобы использовать функцию OPENROWSET.

Администратор хранилища также должен предоставить пользователю доступ к файлам, предоставив действительный маркер SAS или предоставив субъекту Microsoft Entra доступ к файлам хранилища. Узнайте больше об управлении доступом к хранилищу в этой статье.

OPENROWSET используйте следующие правила, чтобы определить, как выполнять проверку подлинности для хранилища:

  • OPENROWSET Механизм проверки подлинности без DATA_SOURCE зависит от типа вызывающего объекта.
    • Любой пользователь может использовать OPENROWSET без DATA_SOURCE, чтобы считывать общедоступные файлы в службе хранилища Azure.
    • Логины Microsoft Entra могут получить доступ к защищенным файлам с помощью собственного удостоверения личности Microsoft Entra, если хранилище Azure позволяет пользователю Microsoft Entra получить доступ к основным файлам (например, если вызывающий объект имеет Storage Reader разрешение на хранилище Azure).
    • SQL-входы также могут использовать OPENROWSET без DATA_SOURCE для доступа к общедоступным файлам, файлам, защищенным с помощью маркера SAS, или для управляемой идентификации в рабочей области Synapse. Чтобы разрешить доступ к файлам хранилища, необходимо создать учетные данные уровня сервера.
  • При использовании OPENROWSET механизм проверки подлинности определяется в учетных данных, относящихся к базе данных, назначенных источнику данных, на который ссылаются. Этот параметр позволяет получить доступ к общедоступному хранилищу или воспользоваться хранилищем с помощью маркера SAS, Управляемого удостоверения рабочей области или удостоверения Microsoft Entra вызывающего абонента (если вызывающий объект является субъектом Microsoft Entra). Если DATA_SOURCE ссылается на хранилище Azure, которое не является общедоступным, придется создать учетные данные уровня базы данных и указать их в DATA SOURCE, чтобы разрешить доступ к файлам хранилища.

Вызывающий должен иметь REFERENCES разрешение на учетные данные для аутентификации в хранилище.

Синтаксис

--OPENROWSET syntax for reading Parquet or Delta Lake files
OPENROWSET  
( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ]
    FORMAT= ['PARQUET' | 'DELTA'] }  
)  
[WITH ( {'column_name' 'column_type' }) ]
[AS] table_alias(column_alias,...n)

--OPENROWSET syntax for reading delimited text files
OPENROWSET  
( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ] 
    FORMAT = 'CSV'
    [ <bulk_options> ]
    [ , <reject_options> ] }  
)  
WITH ( {'column_name' 'column_type' [ 'column_ordinal' | 'json_path'] })  
[AS] table_alias(column_alias,...n)
 
<bulk_options> ::=  
[ , FIELDTERMINATOR = 'char' ]    
[ , ROWTERMINATOR = 'char' ] 
[ , ESCAPECHAR = 'char' ] 
[ , FIRSTROW = 'first_row' ]     
[ , FIELDQUOTE = 'quote_characters' ]
[ , DATA_COMPRESSION = 'data_compression_method' ]
[ , PARSER_VERSION = 'parser_version' ]
[ , HEADER_ROW = { TRUE | FALSE } ]
[ , DATAFILETYPE = { 'char' | 'widechar' } ]
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]

<reject_options> ::=  
{  
    | MAXERRORS = reject_value,  
    | ERRORFILE_DATA_SOURCE = <data source name>,
    | ERRORFILE_LOCATION = '/REJECT_Directory'
}

Аргументы

У вас есть три варианта входных файлов, содержащих целевые данные для запроса. Допустимые значения:

  • "CSV" — включает любой текстовый файл с разделителями (разделители строк и столбцов). Любой символ можно использовать в качестве разделителя полей, например TSV: FIELDTERMINATOR = tab.

  • 'PARQUET' — это двоичный файл в формате Parquet.

  • Delta — набор файлов Parquet, организованных в формате Delta Lake (предварительная версия).

Значения, содержащие пробелы, являются недопустимыми. Например, "CSV" не является допустимым значением.

'unstructured_data_path'

Путь_неструктурированных_данных, определяющий путь к данным, может быть абсолютным или относительным путем:

  • Абсолютный путь в формате \<prefix>://\<storage_account_path>/\<storage_path> позволяет пользователю напрямую читать файлы.
  • Относительный путь в формате <storage_path>, который должен использоваться с параметром DATA_SOURCE и описывает шаблон файла в расположении <>storage_account_path, определённом в EXTERNAL DATA SOURCE.

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

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

'<путь_к_хранилищу>'

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

Для выбора нескольких файлов или папок можно использовать подстановочные знаки. Допускается использование нескольких несмежных подстановочных знаков. Ниже приведен пример для считывания всех файлов .csv, в начале имени которых указано population, из всех папок, в начале имени которых указано /csv/population:
https://sqlondemandstorage.blob.core.windows.net/csv/population*/population*.csv

Если указать, что unstructured_data_path является папкой, тогда запрос бессерверного пула SQL будет извлекать файлы из этой папки.

Вы можете настроить бессерверный пул SQL, так чтобы он перемещался по папкам, указав /* в конце пути, как показано в примере: https://sqlondemandstorage.blob.core.windows.net/csv/population/**

Примечание.

В отличие от Hadoop и PolyBase, бессерверный пул SQL не возвращает вложенные папки, если только вы не указали /** в конце пути. Как и Hadoop и PolyBase, он не возвращает файлы, имя которых начинается с подчеркивания (_) или точки (.).

Если в приведенном ниже примере unstructured_data_path = https://mystorageaccount.dfs.core.windows.net/webdata/, после обращения в бессерверный пул SQL будут возвращены строки из mydata.txt. Файлы mydata2.txt и mydata3.txt не возвращаются, так как они находятся во вложенной папке.

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

[WITH ( {'column_name' 'column_type' [ 'column_ordinal'] }) ]

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

  • Для чтения всех столбцов в файлах данных CSV укажите имена столбцов и их типы данных. Если нужно считать подмножество столбцов, используйте порядковые номера, чтобы выбрать столбцы из исходных файлов данных по порядковому номеру. Столбцы будут привязаны по порядковому обозначению. Если используется HEADER_ROW = TRUE, привязка столбца выполняется по имени столбца вместо порядковой позиции.

    Tip

    Вы также можете опустить предложение WITH для CSV-файлов. Типы данных будут автоматически выводиться из содержимого файла. Аргумент HEADER_ROW можно использовать для указания существования строки заголовка. Тогда имена столбцов регистра будут считываться из этой строки заголовка. Узнайте больше об автоматическом обнаружении схемы.

  • Для файлов Parquet или Delta Lake укажите имена столбцов, которые соответствуют именам столбцов в исходных файлах данных. Столбцы будут привязаны по имени, при этом учитывается регистр. Если предложение WITH опущено, будут возвращены все столбцы из файлов Parquet.

    Внимание

    Имена столбцов в файлах Parquet и Delta Lake чувствительны к регистру. Если указать имя столбца с регистром, отличным от регистра имени столбца в файлах, NULL значения будут возвращены для этого столбца.

column_name (имя_столбца) = имя выходного столбца. Если указано, это имя заменяет имя столбца в исходном файле и имя столбца, указанное в пути JSON, если таковое имеется. Если json_path не указан, он будет автоматически добавлен как $.column_name. Проверьте поведение аргумента json_path.

column_type (тип_столбца) = тип данных в выходном столбце. Выполняется неявное преобразование типов данных.

column_ordinal = порядковое число столбца в исходных файлах. Этот аргумент не учитывается для файлов Parquet, так как привязка выполняется по имени. В следующем примере из CSV-файла будет возвращен только второй столбец:

WITH (
    --[country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2 2
    --[year] smallint,
    --[population] bigint
)

json_path = выражение пути JSON к столбцу или вложенному свойству. Режим пути по умолчанию является нестрогим.

Примечание.

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

<пакетные_параметры>

FIELDTERMINATOR ='field_terminator'

Указывает разделитель поля, который нужно использовать. Признак конца поля по умолчанию — запятая (",").

ROWTERMINATOR ='row_terminator'

Указывает спецификатор конца строки, который нужно использовать. Если терминатор строки не указан, будет использоваться один из терминаторов по умолчанию. По умолчанию для PARSER_VERSION = "1.0" используются терминаторы \r\n, \n и \r. По умолчанию для PARSER_VERSION = "2.0" используются разделители \r\n и \n.

Примечание.

При использовании PARSER_VERSION='1.0' и указании \n (новая строка) в качестве конца строки, он будет автоматически сочетаться с символом \r (возврат каретки), что приведет к окончанию строки \r\n.

ESCAPE_CHAR = char

Указывает символ в файле, который используется для экранирования самого себя и всех значений разделителей в файле. Если за escape-символом следует значение, отличное от него самого или какого-либо из значений разделителей, при считывании этого значения escape-символ пропускается.

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

FIRSTROW = "first_row"

Указывает номер первой строки для загрузки. Значение по умолчанию — 1. Оно указывает на первую строку в используемом файле данных. Номера строк определяются подсчетом разделителей строк. FIRSTROW имеет нумерацию, начинающуюся с 1.

FIELDQUOTE = "field_quote"

Определяет символ, который будет использоваться в качестве символа кавычки в CSV-файле. Если значение не указано, будет использоваться символ кавычек (").

DATA_COMPRESSION = "метод_cжатия_данных"

Позволяет указать метод сжатия. Поддерживается только в PARSER_VERSION = "1.0". Поддерживается следующий метод сжатия:

  • GZIP

PARSER_VERSION = "версия_парсера"

Позволяет указать версию средства синтаксического анализа, которая используется при чтении файлов. В настоящее время поддерживаются версии 1.0 и 2.0 средства синтаксического анализа для CSV-файлов.

  • PARSER_VERSION = "1.0"
  • PARSER_VERSION = "2.0"

Средство синтаксического анализа для CSV-файлов версии 1.0 обладает широким набором функций и используется по умолчанию, Версия 2.0 создана для производительности и не поддерживает все параметры и кодировки.

Особенности средства синтаксического анализа CSV версии 1.0:

  • Следующие параметры не поддерживаются: HEADER_ROW.
  • По умолчанию используются терминаторы \r\n, \n и \r.
  • Если в качестве конца строки указан \n (newline), ему будет автоматически предшествовать символ \r (возврата каретки), что создаёт окончание строки \r\n.

Особенности средства синтаксического анализа для CSV-файлов версии 2.0:

  • Поддерживаются не все типы данных.
  • Максимальная длина символьного столбца — 8000.
  • Размер строки не может превышать 8 МБ.
  • Следующие параметры не поддерживаются: DATA_COMPRESSION.
  • Пустая строка в кавычках ("") интерпретируется как пустая строка.
  • Параметр DATEFORMAT SET не учитывается.
  • Поддерживаемый формат для типа данных DATE: ГГГГ-ММ-ДД
  • Поддерживаемый формат для типа данных TIME: HH:MM:SS[.доли секунд]
  • Поддерживаемый формат для типа данных DATETIME2: ГГГГ-ММ-ДД HH:MM:SS[.доли секунд]
  • Терминаторы по умолчанию \r\n и \n.

HEADER_ROW = { TRUE | FALSE } // заголовочная строка = { ИСТИНА | ЛОЖЬ }

Указывает, содержит ли CSV-файл строку заголовка. По умолчанию 'FALSE.' поддерживается в PARSER_VERSION='2.0'. Если установлено значение TRUE, имена столбцов будут считываться из первой строки в соответствии с аргументом FIRSTROW. Если значение TRUE и схема указаны с помощью WITH, привязка имен столбцов будет выполняться по имени столбца, а не порядковых позиций.

DATAFILETYPE = { "char" | 'widechar' }

Указывает на кодировку: char используется для файлов UTF8, а widechar — для файлов UTF16.

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

Указывает кодовую страницу данных в файле данных. Значение по умолчанию — 65001 (кодировка UTF-8). Дополнительные сведения об этом параметре доступны здесь.

ROWSET_OPTIONS = "{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}"

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

Опции отклонения

Примечание.

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

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

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

MAXERRORS = значение_отклонения

Указывает количество строк, которые могут быть отклонены, прежде чем произойдет сбой запроса. MAXERRORS должно быть целым числом от 0 до 2 147 483 647.

ERRORFILE_DATA_SOURCE = источник_данных

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

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

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

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

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

Быстрый синтаксический анализ текста с разделителями

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

Автоматическое обнаружение схем

Файлы CSV и Parquet можно запрашивать, не зная и не указывая схему, опустив предложение WITH. Имена столбцов и типы данных будут выводиться из файлов.

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

Имена столбцов для CSV-файлов можно считывать из строки заголовка. Можно указать, существует ли строка заголовка, с помощью аргумента HEADER_ROW. Если HEADER_ROW = FALSE, будут использоваться универсальные имена столбцов: C1, C2, ... Cn where n — число столбцов в файле. Типы данных будут выводиться из первых 100 строк данных. Проверьте считывание CSV-файлов без указания схемы для примеров.

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

Внимание

В некоторых случаях из-за недостатка информации не удается вычислить подходящий тип данных, поэтому вместо него используется больший тип данных. Это приводит к нагрузке на производительность и особенно важно для символьных столбцов, которые будут интерпретированы как varchar(8000). Для оптимальной производительности проверьте выводимые типы данных и используйте соответствующие типы данных.

Сопоставление типов данных для Parquet

Файлы Parquet и Delta Lake содержат описания типов для каждого столбца. В приведенной ниже таблице показано, как типы Parquet сопоставляются с собственными типами SQL.

Тип паркета Логический тип Parquet (аннотация) Тип данных SQL
BOOLEAN bit
BINARY/ BYTE_ARRAY varbinary
DOUBLE float
FLOAT real
INT32 int
INT64 bigint
INT96 datetime2
FIXED_LEN_BYTE_ARRAY бинарный
BINARY UTF8 varchar *(сопоставление UTF8)
BINARY STRING varchar *(параметры сортировки UTF8)
BINARY ENUM varchar *(сопоставление UTF8)
FIXED_LEN_BYTE_ARRAY UUID (универсальный уникальный идентификатор) уникальный идентификатор
BINARY DECIMAL десятичное число
BINARY JSON varchar(8000) *(сопоставление UTF8)
BINARY BSON Не поддерживается
FIXED_LEN_BYTE_ARRAY DECIMAL десятичное число
BYTE_ARRAY ИНТЕРВАЛ Не поддерживается
INT32 INT(8, истина) smallint
INT32 INT(16, истина) smallint
INT32 INT(32, true) int
INT32 INT(8, false) tinyint
INT32 INT(16, false) int
INT32 INT(32, false) bigint
INT32 ДАТА Дата
INT32 DECIMAL десятичное число
INT32 Время (миллисекунды) time
INT64 INT(64, true) bigint
INT64 INT(64, false) decimal(20,0)
INT64 DECIMAL десятичное число
INT64 ВРЕМЯ (МИКРОСЕКУНДЫ) time
INT64 ВРЕМЯ (наносекунды) Не поддерживается
INT64 TIMESTAMP (нормализовано в формат UTC) (MILLIS / MICROS) datetime2
INT64 TIMESTAMP (не нормализовано в формат UTC) (MILLIS / MICROS) bigint — убедитесь, что значение bigint явно отрегулировано с учётом смещения часового пояса перед преобразованием в значение даты и времени (datetime).
INT64 TIMESTAMP (NANOS) Не поддерживается
Сложный тип СПИСОК varchar(8000), сериализуется в JSON
Сложный тип карта varchar(8000), сериализуется в JSON

Примеры

Считывание CSV-файлов без указания схемы

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

SELECT 
    *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) as [r]

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

SELECT 
    *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0') as [r]

Чтение файлов Parquet без указания схемы

В следующем примере возвращаются все столбцы первой строки из набора данных переписи в формате Parquet без указания имен столбцов и типов данных.

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    ) AS [r]

Чтение файлов Delta Lake без указания схемы

В приведенном ниже примере возвращаются все столбцы первой строки из набора данных переписи в формате Delta Lake без указания имен столбцов и типов данных:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='DELTA'
    ) AS [r]

Чтение определенных столбцов из CSV-файла

В следующем примере из файлов population*.csv возвращаются только два столбца с порядковыми номерами 1 и 4. В файлах нет строки заголовка, поэтому чтение начинается с первой строки.

SELECT 
    * 
FROM OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/csv/population/population*.csv',
        FORMAT = 'CSV',
        FIRSTROW = 1
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2 1,
    [population] bigint 4
) AS [r]

Чтение определенных столбцов из файла Parquet

В следующем примере возвращаются только два столбца первой строки из набора данных переписи в формате Parquet:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    )
WITH (
    [stateName] VARCHAR (50),
    [population] bigint
) AS [r]

Определение столбцов с помощью путей JSON

В следующем примере показано, как можно использовать выражения пути JSON в предложении WITH и в чем заключается различие между строгими и нестрогими режимами пути:

SELECT 
    TOP 1 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=20*/*.parquet',
        FORMAT='PARQUET'
    )
WITH (
    --lax path mode samples
    [stateName] VARCHAR (50), -- this one works as column name casing is valid - it targets the same column as the next one
    [stateName_explicit_path] VARCHAR (50) '$.stateName', -- this one works as column name casing is valid
    [COUNTYNAME] VARCHAR (50), -- STATEname column will contain NULLs only because of wrong casing - it targets the same column as the next one
    [countyName_explicit_path] VARCHAR (50) '$.COUNTYNAME', -- STATEname column will contain NULLS only because of wrong casing and default path mode being lax

    --strict path mode samples
    [population] bigint 'strict $.population' -- this one works as column name casing is valid
    --,[population2] bigint 'strict $.POPULATION' -- this one fails because of wrong casing and strict path mode
)
AS [r]

Укажите несколько файлов и папок в пути BULK

В следующем примере показано, как использовать пути к нескольким файлам или папкам в параметре BULK:

SELECT 
    TOP 10 *
FROM  
    OPENROWSET(
        BULK (
            'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=2000/*.parquet',
            'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=2010/*.parquet'
        ),
        FORMAT='PARQUET'
    )
AS [r]

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

Дополнительные образцы см. в кратком руководстве по хранилищу данных запросов, чтобы узнать, как использовать OPENROWSET для чтения CSV, PARQUET, DELTA LAKE, а также форматов файлов JSON. Ознакомьтесь с рекомендациями, чтобы иметь возможность достичь оптимальной производительности. Вы также узнаете, как сохранить результаты запроса в службе хранилища Azure с помощью CETAS.

  • Last updated on