CREATE EXTERNAL FILE FORMAT (Transact-SQL)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores de Azure SQL Instancia administrada Azure Synapse Analytics Analytics Platform System (PDW)

Crea un objeto de formato de archivo externo que define datos externos almacenados en Hadoop, Azure Blob Storage, Azure Data Lake Store o para los flujos de entrada y salida asociados con flujos externos. La creación de un formato de archivo externo es un requisito previo para crear una tabla externa. Al crear un formato de archivo externo, se especifica el diseño real de los datos a los que hace referencia una tabla externa. Para crear una tabla externa, vea CREATE EXTERNAL TABLE (Transact-SQL).

Se admiten los formatos de archivo siguientes:

• Texto delimitado

• Hive RCFile

  No se aplica a Azure Synapse Analytics, Azure SQL Managed Instance ni a SQL Server 2022 (16.x).

• Hive ORC

  No se aplica a Azure Synapse Analytics, Azure SQL Managed Instance ni a SQL Server 2022 (16.x).

• Parquet

• JSON

  Solo se aplica a Azure SQL Edge. Para obtener información sobre el uso de OPENROWSET para importar datos JSON en otras plataformas, consulte Importar documentos JSON en SQL Server o Consulta de archivos JSON mediante un grupo de SQL sin servidor en Azure Synapse Analytics.

• Delta

  Se aplica solo a los grupos de SQL sin servidor en Azure Synapse Analytics y SQL Server 2022 (16.x).

Sintaxis

Convenciones de sintaxis de 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
      );

Argumentos

file_format_name

Especifica un nombre para el formato de archivo externo.

FORMAT_TYPE

Especifica el formato de los datos externos.

• FORMAT_TYPE = PARQUET

  Especifica un formato Parquet.

• FORMAT_TYPE = ORC

  Especifica un formato ORC (Optimized Row Columnar). Esta opción requiere Hive versión 0.11 o superior en el clúster externo de Hadoop. En Hadoop, el formato de archivo ORC ofrece un rendimiento y una comprensión mejores que el formato de archivo RCFILE.

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  Especifica un formato de archivo de Record Columnar. Esta opción exige especificar un método Serializer y Deserializer (SerDe) de Hive. Este requisito es el mismo si se usa Hive/HiveQL en Hadoop para consultar archivos RC. Tenga en cuenta que el método SerDe distingue mayúsculas de minúsculas.

  Ejemplos de especificación de RCFile con los dos métodos SerDe que admite 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 = DELIMITEDTEXT

  Especifica un formato de texto con delimitadores de columna, también denominados terminadores de campo.

• FORMAT_TYPE = JSON

  Especifica un formato JSON. Solo se aplica a Azure SQL Edge.

• FORMAT_TYPE = DELTA

  Especifica un formato de Delta Lake. Se aplica a los grupos de SQL sin servidor en Azure Synapse Analytics y a SQL Server 2022 (16.x).

FORMAT_OPTIONS

Opcional. Solo para tipos de datos de texto delimitados.

Solo los grupos de SQL sin servidor en Azure Synapse Analytics admiten PARSER_VERSION.

Los grupos de SQL sin servidor no admiten la opción DATE_FORMAT.

DATA_COMPRESSION = data_compression_method

Especifica el método de compresión de datos de los datos externos. Si no se especifica DATA_COMPRESSION, el valor predeterminado es datos sin comprimir.

Para que funcionen correctamente, los archivos comprimidos de Gzip deben tener la extensión de archivo ".gz".

Opciones de formato de texto delimitado

Las opciones de formato descritas en esta sección son opcionales y solo se aplican a archivos de texto delimitado.

FIELD_TERMINATOR = field_terminator

Se aplica solo a los archivos de texto delimitado. El terminador de campo especifica uno o varios caracteres que marcan el final de cada campo (columna) en el archivo de texto delimitado. El valor predeterminado es el carácter de barra vertical |. Para garantizar la compatibilidad, se recomienda usar uno o varios caracteres ASCII.

Ejemplos:

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Especifica el terminador de campo de los datos de tipo cadena en el archivo de texto delimitado. El delimitador de cadena tiene una longitud de uno o más caracteres y se escribe entre comillas simples. El valor predeterminado es la cadena vacía "". Para garantizar la compatibilidad, se recomienda usar uno o varios caracteres ASCII.

Ejemplos:

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22' (comilla doble hexadecimal)

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E' (dos tildes, por ejemplo, ~~)

FIRST_ROW = first_row_int

Se aplica a: Azure Synapse Analytics, SQL Server 2022 y versiones posteriores.

Especifica el número de filas que se leen en primer lugar en todos los archivos durante una carga de PolyBase. Este parámetro puede tener los valores comprendidos entre 1 y 15. Si el valor se establece en dos, se omite la primera fila de cada archivo (fila de encabezado) al cargar los datos. Las filas se omiten en función de la existencia de terminadores de fila (/r/n, /r, /n). Cuando esta opción se usa para la exportación, se agregan filas a los datos para asegurarse de que el archivo pueda leerse sin pérdida de datos. Si el valor se establece en >2, la primera fila exportada son los nombres de columna de la tabla externa.

DATE_FORMAT = datetime_format

Especifica un formato personalizado para todos los datos de fecha y hora que pueden aparecer en un archivo de texto delimitado. Si el archivo de origen usa formatos de fecha y hora predeterminados, esta opción no es necesaria. Solo se permite un formato de fecha y hora personalizado por archivo. No se puede especificar más de un formato de fecha y hora personalizado por archivo. Pero puede usar más de un formato de fecha y hora si cada uno de ellos es el predeterminado de su tipo de datos respectivo en la definición de la tabla externa.

Si DATE_FORMAT no se especifica o es una cadena vacía, PolyBase usa los formatos predeterminados siguientes:

• 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'

Los formatos de fecha de ejemplo se encuentran en la tabla siguiente:

Notas sobre la tabla:

• El año, el mes y el día pueden adoptar distintos formatos y órdenes. La tabla muestra solo el formato amd. El mes puede tener uno o dos dígitos, o tres caracteres. El día puede tener uno o dos dígitos. El año puede tener dos o cuatro dígitos.

• No se requieren milisegundos (fffffff).

• am o pm (tt) no es necesario. El valor predeterminado es AM.

Formatos de fecha y hora admitidos

El formato de archivo externo puede describir un gran número de formatos de fecha y hora:

Detalles:

• Para separar los valores de mes, día y año, debe usar uno de los siguientes separadores: -, / o .. Para simplificar, en la tabla solo se usa el separador -.

• Para especificar el mes como texto, use tres o más caracteres. Los meses con uno o dos caracteres se interpretan como un número.

• Para separar valores de hora, use el símbolo :.

• Las letras entre corchetes son opcionales.

• Las letras tt designan [A. M.|P. M.|a. m.|p. m.]. a.m. es el valor predeterminado. Cuando se especifica tt, el valor de hora (hh) debe estar comprendido entre 0 y 12.

• Las letras zzz designan el desfase de zona horaria de la zona horaria actual del sistema en el formato {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }

Especifica cómo administrar valores que faltan en archivos de texto delimitado cuando PolyBase recupera datos del archivo de texto. El valor predeterminado es FALSE.

• TRUE

  Al recuperar datos del archivo de texto, cada valor que falta se almacena mediante el valor predeterminado del tipo de datos de la columna correspondiente en la definición de la tabla externa. Por ejemplo, reemplace un valor que falta con:

  • 0 si la columna se define como una columna numérica. Las columnas decimales no se admiten y generarán un error.

  • Cadena vacía "" si la columna es una columna de cadena.

  • 1900-01-01 si la columna es una columna de fecha.

  • En Azure Synapse Analytics, USE_TYPE_DEFAULT=true no se admite para FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0'.

• FALSE

  Almacena todos los valores que faltan como NULL. Los valores NULL que se almacenan mediante la palabra NULL en el archivo de texto delimitado se importan como la cadena NULL.

ENCODING = {'UTF8' | 'UTF16'}

En Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW) (APS CU7.4), PolyBase puede leer archivos de texto delimitados y codificados con UTF8 y UTF16-LE.

En SQL Server, PolyBase no puede leer archivos codificados UTF16.

Permisos

Requiere el permiso ALTER ANY EXTERNAL FILE FORMAT.

Observaciones

El formato de archivo externo es de ámbito de base de datos en SQL Server y Azure Synapse Analytics. Es de ámbito de servidor en Sistema de la plataforma de análisis (PDW).

Las opciones de formato son todas opcionales y solo se aplican a archivos de texto delimitado.

Cuando los datos se almacenan en uno de los formatos comprimidos, PolyBase primero descomprime los datos para devolver los registros de datos.

Limitaciones

El delimitador de filas de los archivos de texto delimitado tiene que ser compatible con LineRecordReader de Hadoop. Debe ser \r, \n o \r\n. El usuario no puede configurar estos delimitadores.

Las combinaciones de métodos SerDe compatibles con RCFiles y los métodos de compresión de datos admitidos se han enumerado anteriormente en este artículo. No se admiten todas las combinaciones.

El número máximo de consultas de PolyBase simultáneas es 32. Si se están ejecutando 32 consultas simultáneas, cada consulta puede leer un máximo de 33.000 archivos de la ubicación del archivo externo. La carpeta raíz y cada subcarpeta también cuentan como un archivo. Si el grado de simultaneidad es menor que 32, la ubicación del archivo externo puede contener más de 33.000 archivos.

Debido a la limitación en el número de archivos en la tabla externa, se recomienda almacenar menos de 30.000 archivos en la raíz y las subcarpetas de la ubicación del archivo externo. Además, se recomienda mantener el número de subcarpetas bajo el directorio raíz en un número pequeño. Si hay referencias a demasiados archivos, podría producirse una excepción de memoria insuficiente de Máquina virtual Java.

Al exportar datos a Hadoop o Azure Blob Storage mediante PolyBase, solo se exportan los datos y no los nombres de columna (metadatos), tal como se define en el comando CREATE EXTERNAL TABLE.

Bloqueo

Toma un bloqueo compartido en el objeto EXTERNAL FILE FORMAT.

Rendimiento

El uso de archivos comprimidos siempre tiene el inconveniente de tener que elegir entre transferir menos datos entre el origen de datos externo y SQL Server y aumentar el uso de CPU para comprimir y descomprimir los datos.

Los archivos de texto comprimidos de Gzip no son divisibles. Para mejorar el rendimiento de los archivos de texto comprimidos de Gzip, se recomienda generar varios archivos que se almacenen en el mismo directorio del origen de datos externo. Esta estructura de archivos permite a PolyBase leer y descomprimir los datos con mayor rapidez al usar varios procesos de lector y descompresión. El número ideal de archivos comprimidos es el número máximo de procesos de lector de datos por nodo de proceso. En SQL Server y el Sistema de la plataforma de análisis (PDW), el número máximo de procesos del lector de datos es de ocho por nodo, excepto en Azure Synapse Analytics Gen2, que es de 20 lectores por nodo. En Azure Synapse Analytics, el número máximo de procesos de lector de datos por nodo varía según el SLO. Para obtener más información, consulte Modelos y estrategias de carga de Azure Synapse Analytics.

Ejemplos

A. Crear un formato de archivo externo DELIMITEDTEXT

En este ejemplo se crea un formato de archivo externo denominado textdelimited1 para un archivo de texto delimitado. Las opciones enumeradas para FORMAT_OPTIONS especifican que los campos del archivo deben separarse con un carácter de barra vertical |. El archivo de texto además se comprime con el códec de Gzip. Si no se especifica DATA_COMPRESSION, se descomprime el archivo de texto.

En un archivo de texto delimitado, el método de compresión de datos puede ser el códec predeterminado, org.apache.hadoop.io.compress.DefaultCodec, o el códec de 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. Crear un formato de archivo externo RCFile

Este ejemplo crea un formato de archivo externo para un archivo RCFile que usa el método org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe de serialization/deserialization. Además se especifica el uso del códec predeterminado para el método de compresión de datos. Si no se especifica DATA_COMPRESSION, el valor predeterminado es ninguna compresión.

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. Crear un formato de archivo externo ORC

En este ejemplo se crea un formato de archivo externo para un archivo ORC que comprime los datos con el método de compresión de datos org.apache.io.compress.SnappyCodec. Si no se especifica DATA_COMPRESSION, el valor predeterminado es ninguna compresión.

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

D. Crear un formato de archivo externo PARQUET

En este ejemplo se crea un formato de archivo externo para un archivo Parquet que comprime los datos con el método de compresión de datos org.apache.io.compress.SnappyCodec. Si no se especifica DATA_COMPRESSION, el valor predeterminado es ninguna compresión.

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

E. Crear un archivo de texto delimitado y omitir la fila de encabezado

Se aplica a: Azure Synapse Analytics y SQL Server 2022 (16.x) y versiones posteriores.

En este ejemplo se crea un formato de archivo externo para el archivo CSV con una sola fila de encabezado. Para obtener más información, consulte Virtualización de un archivo CSV con 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. Crear un formato de archivo externo JSON

Se aplica a: Azure SQL Edge.

En este ejemplo se crea un formato de archivo externo para un archivo JSON que comprime los datos con el método de compresión de datos org.apache.io.compress.SnappyCodec. Si no se especifica DATA_COMPRESSION, el valor predeterminado es ninguna compresión. Este ejemplo se aplica a Azure SQL Edge y actualmente no es compatible con otros productos de SQL.

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

G. Crear un formato de archivo externo de tabla delta

En este ejemplo se crea un formato de archivo externo para el formato de archivo de tipo de tabla delta. Este ejemplo se aplica a SQL Server 2022 (16.x). Para obtener más información, consulte Virtualización de un archivo de tabla delta con PolyBase.

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

Contenido relacionado

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