CREATE EXTERNAL FILE FORMAT (Transact-SQL)

Область применения: SQL Server 2016 (13.x) и более поздних версий Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

Создает объект формата внешнего файла, определяя внешние данные, которые сохранены в Hadoop, Хранилище BLOB-объектов Azure или Azure Data Lake Store, либо данные для входных и выходных потоков, связанных с внешними потоками. Создание формата внешнего файла — обязательное условие для создания внешней таблицы. Создавая формат внешнего файла, вы указываете фактическую структуру данных, на которые ссылается внешняя таблица. Инструкции по созданию внешней таблицы см. в статье CREATE EXTERNAL TABLE (Transact-SQL).

Поддерживаются следующие форматы файлов.

• Текст с разделителями

• Hive RCFile

  Не применяется к Azure Synapse Analytics, Управляемый экземпляр SQL Azure или SQL Server 2022 (16.x).

• Hive ORC

  Не применяется к Azure Synapse Analytics, Управляемый экземпляр SQL Azure или SQL Server 2022 (16.x).

• Parquet

• JSON

  Применяется только к SQL Azure для пограничных вычислений. Сведения об использовании функции OPENROWSET для импорта данных JSON на других платформах см. в статье Импорт документов JSON на SQL Server или Запрос файлов JSON с помощью бессерверного пула SQL в Azure Synapse Analytics.

• Разностная версия

  Применяется только к бессерверным пулам SQL в Azure Synapse Analytics и SQL Server 2022 (16.x).

Синтаксис

Соглашения о синтаксисе Transact-SQL

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

<format_options> ::=
{
    FIELD_TERMINATOR = field_terminator
    | STRING_DELIMITER = string_delimiter
    | FIRST_ROW = integer -- Applies to: Azure Synapse Analytics and SQL Server 2022 and later versions
    | DATE_FORMAT = datetime_format
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | ENCODING = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}

}

--Create an external file format for RC files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = {
        'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
      | 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
    }
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec' ]);

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

--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 JSON files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
    FORMAT_TYPE = JSON
     [ , DATA_COMPRESSION = {
        'org.apache.hadoop.io.compress.SnappyCodec'
      | 'org.apache.hadoop.io.compress.GzipCodec'
      | 'org.apache.hadoop.io.compress.DefaultCodec' }
    ]);

-- Create an external file format for delta table files (serverless SQL pools in Synapse analytics and SQL Server 2022).
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
         FORMAT_TYPE = DELTA
      );

Аргументы

file_format_name

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

FORMAT_TYPE

Задает формат внешних данных.

• FORMAT_TYPE = PARQUET

  задает формат Parquet.

• FORMAT_TYPE = ORC

  Задает формат ORC. Для использования этого параметра во внешнем кластере Hadoop требуется Hive версии 0.11 или выше. В Hadoop формат файла ORC обеспечивает более качественное сжатие и более высокую производительность, чем формат файла RCFILE.

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  Указывает формат файла Record Columnar (RcFile). Этот параметр требует задания метода сериализации и десериализации куч (SerDe). То же требование действует при использовании Hive/HiveQL в Hadoop для отправки запросов файлам RC. Обратите внимание, что метод SerDe учитывает регистр.

  Примеры задания RCFile с двумя методами SerDe, которые поддерживаются PolyBase.

  • FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
  • FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'

• FORMAT_TYPE = РАЗДЕЛИТЕЛЬ

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

• FORMAT_TYPE = JSON

  Определяет формат JSON. Применяется только к SQL Azure для пограничных вычислений.

• FORMAT_TYPE = DELTA

  Задает формат Delta Lake. Применяется к бессерверным пулам SQL в Azure Synapse Analytics и SQL Server 2022 (16.x).

FORMAT_OPTIONS

Необязательно. Только для типов текстовых данных с разделителями.

Поддержка PARSER_VERSIONтолько бессерверных пулов SQL в Azure Synapse Analytics.

Бессерверные пулы SQL не поддерживают DATE_FORMAT этот параметр.

DATA_COMPRESSION = data_compression_method

Задает метод сжатия данных для внешних данных. Если параметр DATA_COMPRESSION не задан, по умолчанию используются несжатые данные.

Для правильной работы сжатые Gzip файлы должны иметь файловое расширение .gz.

Варианты форматов текста с разделителями

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

FIELD_TERMINATOR = field_terminator

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

Примеры:

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Указывает признак конца поля для данных строкового типа в текстовом файле с разделителями. Разделитель строк имеет длину один или несколько символов и заключен в одинарные кавычки. По умолчанию используется пустая строка "". Для гарантированной поддержки рекомендуется использовать один или несколько символов ASCII.

Примеры:

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22' — двойная кавычка в шестнадцатеричном формате

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E' — две тильды (например, ~~)

FIRST_ROW = first_row_int

Область применения: Azure Synapse Analytics, SQL Server 2022 и более поздних версий

Задает номер строки, которая читается первой во всех файлах во время загрузки PolyBase. Этот параметр может принимать значения 1–15. Если задано значение 2, первая строка в каждом файле (строка заголовка) при загрузке данных пропускается. Строки пропускаются по признакам конца строк (/r/n, /r, /n). Если для экспорта используется этот вариант, строки добавляются в данные, чтобы гарантировать возможность прочтения файла без потери данных. Если задано значение >2, первой экспортируется строка с названиями столбцов внешней таблицы.

DATE_FORMAT = datetime_format

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

Если параметр DATE_FORMAT не задан или является пустой строкой, PolyBase использует следующие форматы по умолчанию:

• datetime: 'yyyy-MM-dd HH:mm:ss'

• smalldatetime: 'yyyy-MM-dd HH:mm'

• date: 'yyyy-MM-dd'

• datetime2: 'yyyy-MM-dd HH:mm:ss'

• datetimeoffset: 'yyyy-MM-dd HH:mm:ss'

• time: 'HH:mm:ss'

Примеры форматов даты приводятся в следующей таблице:

Примечания об этой таблице:

• Год, месяц и день могут иметь различные форматы и порядок. В таблице показан только формат ymd. Месяц может обозначаться одной или двумя цифрами или тремя символами. День может обозначаться одной или двумя цифрами. Год может обозначаться двумя или четырьмя цифрами.

• Миллисекунды (fffffff) не требуются.

• am или pm (tt) не требуются. Значение по умолчанию — AM.

Поддерживаемые форматы даты и времени

Формат внешнего файла может описывать большое число форматов даты и времени:

Сведения.

• Для разделения значений месяца, дня и года необходимо использовать один из следующих разделителей: -, / или .. Для простоты в таблице используется только разделитель -.

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

• Для разделения значений времени используйте символ :.

• Буквы в квадратных скобках необязательны.

• Буквы tt обозначают время суток [AM|PM|am|pm]. По умолчанию используется AM. Если задано значение tt, значение часа (hh) должно находиться в диапазоне от 0 до 12.

• Буквы zzz обозначают смещение часового пояса для текущего часового пояса системы в формате {+ |-} HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }

Указывает способ обработки отсутствующих значений в текстовых файлах с разделителями, когда PolyBase извлекает данные из текстового файла. Значение по умолчанию — FALSE.

• TRUE

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

  • 0 Значение , если столбец определен как числовой столбец. Столбцы decimal не поддерживаются и приводят к ошибке.

  • Пустая строка "", если столбец является строковым.

  • 1900-01-01, если столбец является столбцом дат.

  • В Azure Synapse Analytics USE_TYPE_DEFAULT=true не поддерживается FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0'.

• FALSE

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

ENCODING = {'UTF8' | 'UTF16'}

В Azure Synapse Analytics и Analytics Platform System (PDW) (APS CU7.4) PolyBase может считывать текстовые файлы в кодировке UTF8 и UTF16-LE.

В SQL Server PolyBase не поддерживает чтение файлов в кодировке UTF16.

Разрешения

Требуется разрешение ALTER ANY EXTERNAL FILE FORMAT.

Замечания

Формат внешнего файла ограничен базой данных в SQL Server и Azure Synapse Analytics. Она находится в области сервера в системе платформы аналитики (PDW).

Все варианты форматов являются необязательными и применяются только к текстовым файлам с разделителями.

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

Ограничения

Разделитель строк в текстовых файлах с разделителями должен поддерживаться объектом LineRecordReader в Hadoop. Значение должно быть \r, \n или \r\n. Эти разделители не настраиваются пользователем.

Сочетания поддерживаемых методов SerDe с RCFiles, а также поддерживаемые методы сжатия данных перечислены ранее в этой статье. Поддерживаются не все комбинации.

Максимальное число одновременно выполняющихся процессов PolyBase — 32. При выполнении 32 параллельных запросов каждый запрос может прочитать не более 33 000 файлов из внешнего файлового расположения. Корневая папка и каждая вложенная папка тоже считаются файлами. Если степень параллелизма меньше 32, внешнее файловое расположение может содержать более 33 000 файлов.

Из-за ограничения на количество файлов во внешней таблице рекомендуется хранить не более 30 000 файлов в корневой и вложенных папках внешнего файлового расположения. Кроме того, рекомендуется не создавать много вложенных папок в корневом каталоге. При ссылке на слишком большое число файлов может возникнуть исключение, связанное с нехваткой памяти на виртуальной машине Java.

При экспорте данных в Hadoop или хранилище BLOB-объектов Azure с помощью PolyBase передаются только данные без имен столбцов (метаданных), как определено в команде CREATE EXTERNAL TABLE.

Блокировка

Принимает совмещаемую блокировку на объекте EXTERNAL FILE FORMAT.

Производительность

При использовании сжатых файлов всегда приходится идти на компромисс: перенести меньше данных между внешним источником данных и SQL Server, но увеличить использование ресурсов ЦП для сжатия и распаковывания данных.

Сжатые текстовые файлы Gzip разделить невозможно. Чтобы повысить производительность сжатых текстовых файлов Gzip, рекомендуется создать несколько хранимых в одном каталоге файлов во внешнем источнике данных. Такая файловая структура позволяет PolyBase читать и распаковывать данные быстрее, используя несколько процессов чтения и распаковывания. Оптимальное количество сжатых файлов — максимальное число процессов чтения данных на вычислительном узле. В SQL Server и Analytics Platform System (PDW) максимальное число процессов чтения данных на узел составляет 8 для каждого узла. В Azure Synapse Analytics 2-го поколения это число составляет 20. В Azure Synapse Analytics максимальное количество процессов чтения данных на узел зависит от SLO. Дополнительные сведения см. в шаблонах и стратегиях загрузки Azure Synapse Analytics.

Примеры

А. Создание формата внешнего файла DELIMITEDTEXT

В этом примере создается формат внешнего файла textdelimited1 для текстового файла с разделителями. Параметры, заданные для FORMAT_OPTIONS, указывают, что поля в файле необходимо разделять вертикальной чертой |. Текстовый файл также сжимается с помощью кодека Gzip. Если параметр DATA_COMPRESSION не задан, сжатие текстового файла не выполняется.

Для текстового файла с разделителями метод сжатия данных может быть кодеком по умолчанию org.apache.hadoop.io.compress.DefaultCodec или кодеком Gzip org.apache.hadoop.io.compress.GzipCodec.

CREATE EXTERNAL FILE FORMAT textdelimited1
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = '|',
        DATE_FORMAT = 'MM/dd/yyyy' ),
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
);

B. Создание формата внешнего файла RCFile

В этом примере создается внешний формат файла для RCFile, который использует serialization/deserialization метод org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. Кроме того, указано, что в качестве метода сжатия данных необходимо использовать кодек по умолчанию. Если параметр DATA_COMPRESSION не задан, по умолчанию сжатие не выполняется.

CREATE EXTERNAL FILE FORMAT rcfile1
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe',
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'
);

C. Создание формата внешнего файла ORC

В этом примере создается формат внешнего файла для файла ORC, который сжимает данные с помощью метода сжатия данных org.apache.io.compress.SnappyCodec. Если параметр DATA_COMPRESSION не задан, по умолчанию сжатие не выполняется.

CREATE EXTERNAL FILE FORMAT orcfile1
WITH (
    FORMAT_TYPE = ORC,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

D. Создание формата внешнего файла PARQUET

В этом примере создается формат внешнего файла для файла Parquet, который сжимает данные с помощью метода сжатия данных org.apache.io.compress.SnappyCodec. Если параметр DATA_COMPRESSION не задан, по умолчанию сжатие не выполняется.

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

Е. Создание текстового файла с разделителями с пропуском строки заголовка

Область применения: Azure Synapse Analytics и SQL Server 2022 (16.x) и более поздних версий.

В этом примере создается формат внешнего файла для файла CSV с одной строкой заголовка. Дополнительные сведения см. в разделе Виртуализация CSV-файла с помощью PolyBase.

CREATE EXTERNAL FILE FORMAT skipHeader_CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 2,
          USE_TYPE_DEFAULT = True)
);

F. Создание формата внешнего файла JSON

Область применения: SQL Azure для пограничных вычислений.

В этом примере создается формат внешнего файла для файла JSON, который сжимает данные с помощью метода сжатия данных org.apache.io.compress.SnappyCodec. Если параметр DATA_COMPRESSION не задан, по умолчанию сжатие не выполняется. Этот пример применяется к SQL Azure для пограничных вычислений и в настоящее время не поддерживается для других продуктов SQL.

CREATE EXTERNAL FILE FORMAT jsonFileFormat
WITH (
    FORMAT_TYPE = JSON,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

G. Создание формата внешнего файла для таблицы Delta

В этом примере создается формат внешнего файла для формата файла с типом таблицы Delta. Этот пример применяется к SQL Server 2022 (16.x). Дополнительные сведения см. в разделе Виртуализация таблицы Delta с помощью PolyBase.

CREATE EXTERNAL FILE FORMAT DeltaFileFormat
WITH (
    FORMAT_TYPE = DELTA
);

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

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• sys.external_file_formats (Transact-SQL)