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).

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

Синтаксис

Соглашения о синтаксисе 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'}

}

Аргументы

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.

Тип формата DELIMITEDTEXT поддерживает этот метод сжатия:

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

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

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

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

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

Внимание

PolyBase использует пользовательский формат даты только для импорта данных. Для записи данных во внешний файл пользовательский формат не используется.

Если параметр 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'

Внимание

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

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

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

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

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

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

Тип данных Пример Description
datetime DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fff Кроме года, месяца и дня этот формат данных включает 00-24 часа, 00-59 минут, 00-59 секунд и трехзначное обозначение миллисекунд.
datetime DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffftt Кроме года, месяца и дня этот формат данных включает 00-12 часов, 00-59 минут, 00-59 секунд и трехзначное обозначение миллисекунд и указание времени суток: AM, am, PM или pm.
smalldatetime DATE_FORMAT = yyyy-MM-dd HH:mm Кроме года, месяца и дня этот формат данных включает 00-23 часа, 00-59 минут.
smalldatetime DATE_FORMAT = yyyy-MM-dd hh:mmtt Кроме года, месяца и дня этот формат данных включает 00-11 часов, 00-59 минут и указание времени суток: AM, am, PM или pm. Секунды не указаны.
date DATE_FORMAT = yyyy-MM-dd Год, месяц и день. Элемент времени не включен.
date DATE_FORMAT = yyyy-MMM-dd Год, месяц и день. Если месяц указывается с MMM, входное значение равно единице или строковым значениям, Jan, Feb, Mar, Apr, May,Jun, Jul, Aug, Sep, Oct, Novили Dec.
datetime2 DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff Кроме года, месяца и дня этот формат данных включает 00-23 часа, 00-59 минут, 00-59 секунд и семизначное обозначение миллисекунд.
datetime2 DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt Кроме года, месяца и дня этот формат данных включает 00-11 часов, 00-59 минут, 00-59 секунд и семизначное обозначение миллисекунд и указание времени суток: AM, am, PM или pm.
datetimeoffset DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff zzz Кроме года, месяца и дня этот формат данных включает 00-23 часа, 00-59 минут, 00-59 секунд, семизначное обозначение миллисекунд и смещение часового пояса, которое указывается во входящем файле в виде {+&#124;-}HH:ss. Например, так как время в Лос-Анджелесе без перехода на летнее время на 8 часов отстает от времени UTC, значение -08:00 во входящем файле задает часовой пояс для Лос-Анджелеса.
datetimeoffset DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt zzz Кроме года, месяца и дня этот формат данных включает 00-11 часов, 00-59 минут, 00-59 секунд и семизначное обозначение миллисекунд, указание времени суток (AM, am, PM или pm) и смещение часового пояса. См. в описании в предыдущей строке.
time DATE_FORMAT = HH:mm:ss Нет значения даты, только 00-23 часа 00-59 минут и 00-59 секунд.

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

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

datetime smalldatetime Дата datetime2 datetimeoffset
[M[M]]M-[d]d-[yy]yy HH:mm:ss[.fff] [M[M]]M-[d]d-[yy]yy HH:mm[:00] [M[M]]M-[d]d-[yy]yy [M[M]]M-[d]d-[yy]yy HH:mm:ss[.fffffff] [M[M]]M-[d]d-[yy]yy HH:mm:ss[.fffffff] zzz
[M[M]]M-[d]d-[yy]yy hh:mm:ss[.fff][tt] [M[M]]M-[d]d-[yy]yy hh:mm[:00][tt] [M[M]]M-[d]d-[yy]yy hh:mm:ss[.fffffff][tt] [M[M]]M-[d]d-[yy]yy hh:mm:ss[.fffffff][tt] zzz
[M[M]]M-[yy]yy-[d]d HH:mm:ss[.fff] [M[M]]M-[yy]yy-[d]d HH:mm[:00] [M[M]]M-[yy]yy-[d]d [M[M]]M-[yy]yy-[d]d HH:mm:ss[.fffffff] [M[M]]M-[yy]yy-[d]d HH:mm:ss[.fffffff] zzz
[M[M]]M-[yy]yy-[d]d hh:mm:ss[.fff][tt] [M[M]]M-[yy]yy-[d]d hh:mm[:00][tt] [M[M]]M-[yy]yy-[d]d hh:mm:ss[.fffffff][tt] [M[M]]M-[yy]yy-[d]d hh:mm:ss[.fffffff][tt] zzz
[yy]yy-[M[M]]M-[d]d HH:mm:ss[.fff] [yy]yy-[M[M]]M-[d]d HH:mm[:00] [yy]yy-[M[M]]M-[d]d [yy]yy-[M[M]]M-[d]d HH:mm:ss[.fffffff] [yy]yy-[M[M]]M-[d]d HH:mm:ss[.fffffff] zzz
[yy]yy-[M[M]]M-[d]d hh:mm:ss[.fff][tt] [yy]yy-[M[M]]M-[d]d hh:mm[:00][tt] [yy]yy-[M[M]]M-[d]d hh:mm:ss[.fffffff][tt] [yy]yy-[M[M]]M-[d]d hh:mm:ss[.fffffff][tt] zzz
[yy]yy-[d]d-[M[M]]M HH:mm:ss[.fff] [yy]yy-[d]d-[M[M]]M HH:mm[:00] [yy]yy-[d]d-[M[M]]M [yy]yy-[d]d-[M[M]]M HH:mm:ss[.fffffff] [yy]yy-[d]d-[M[M]]M HH:mm:ss[.fffffff] zzz
[yy]yy-[d]d-[M[M]]M hh:mm:ss[.fff][tt] [yy]yy-[d]d-[M[M]]M hh:mm[:00][tt] [yy]yy-[d]d-[M[M]]M hh:mm:ss[.fffffff][tt] [yy]yy-[d]d-[M[M]]M hh:mm:ss[.fffffff][tt] zzz
[d]d-[M[M]]M-[yy]yy HH:mm:ss[.fff] [d]d-[M[M]]M-[yy]yy HH:mm[:00] [d]d-[M[M]]M-[yy]yy [d]d-[M[M]]M-[yy]yy HH:mm:ss[.fffffff] [d]d-[M[M]]M-[yy]yy HH:mm:ss[.fffffff] zzz
[d]d-[M[M]]M-[yy]yy hh:mm:ss[.fff][tt] [d]d-[M[M]]M-[yy]yy hh:mm[:00][tt] [d]d-[M[M]]M-[yy]yy hh:mm:ss[.fffffff][tt] [d]d-[M[M]]M-[yy]yy hh:mm:ss[.fffffff][tt] zzz
[d]d-[yy]yy-[M[M]]M HH:mm:ss[.fff] [d]d-[yy]yy-[M[M]]M HH:mm[:00] [d]d-[yy]yy-[M[M]]M [d]d-[yy]yy-[M[M]]M HH:mm:ss[.fffffff] [d]d-[yy]yy-[M[M]]M HH:mm:ss[.fffffff] zzz
[d]d-[yy]yy-[M[M]]M hh:mm:ss[.fff][tt] [d]d-[yy]yy-[M[M]]M hh:mm[:00][tt] [d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] [d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] zzz

Сведения.

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

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

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

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

  • Буквы 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
);