Compartir vía

Creación y modificación de tablas externas de Azure Storage

  • Artículo

Los comandos de este artículo se pueden usar para crear o modificar una tabla externa de Azure Storage en la base de datos desde la que se ejecuta el comando. Una tabla externa de Azure Storage hace referencia a datos ubicados en Azure Blob Storage, Azure Data Lake Store Gen1 o Azure Data Lake Store Gen2.

Nota:

Si la tabla existe, se producirá un error en el .create comando. Use .create-or-alter o .alter para modificar las tablas existentes.

Permisos

Para .create requerir al menos permisos de usuario de base de datos y para .alter requerir al menos permisos de administrador de tablas.

Para .create-or-alter una tabla externa mediante la autenticación de identidad administrada, se requieren permisos AllDatabasesAdmin .

Sintaxis

(.create.alter.create-or-alter | | ) external table Esquema) kind= storage TableName ([ Particiones) [pathformat =partition(by (PathFormat)]] dataformat = DataFormat (StorageConnectionString [, ...] ) [with (Propiedad [, ...])]

Nota:

kind es storage para todos los tipos de almacén de datos externos de Azure Storage. blob y adl están en desuso.

Obtenga más información sobre las convenciones de sintaxis.

Parámetros

Nombre Type Obligatorio Descripción
TableName string ✔️ Nombre de tabla externo que cumple las reglas de nombres de entidad. Una tabla externa no puede tener el mismo nombre que una tabla normal en la misma base de datos.
Esquema string ✔️ El esquema de datos externos es una lista separada por comas de uno o más nombres de columna y tipos de datos, donde cada elemento sigue el formato: ColumnName : ColumnType. Si el esquema es desconocido, use infer_storage_schema para deducir el esquema en función del contenido del archivo externo.
Particiones string Lista separada por comas de columnas por las que se particiona la tabla externa. La columna de partición puede existir en el propio archivo de datos o como parte de la ruta de acceso del archivo. Consulte formato de particiones para obtener información sobre el aspecto de este valor.
PathFormat string Un formato de ruta de acceso de URI de carpeta de datos externo que se va a usar con particiones. Consulte el formato de ruta de acceso.
DataFormat string ✔️ El formato de datos, que puede ser cualquiera de los formatos de ingesta. Se recomienda usar el formato de las tablas externas para mejorar el Parquet rendimiento de las consultas y la exportación, a menos que use JSON la asignación de rutas de acceso. Cuando se usa una tabla externa para el escenario de exportación, se limita a los siguientes formatos: CSV, JSON TSVy Parquet.
StorageConnectionString string ✔️ Una o varias rutas de acceso separadas por comas a contenedores de blobs de Azure Blob Storage, sistemas de archivos de Azure Data Lake Gen 2 o contenedores de Azure Data Lake Gen 1, incluidas las credenciales. El tipo de almacenamiento de tablas externas viene determinado por los cadena de conexión proporcionados. Consulte cadena de conexión de almacenamiento.
Propiedad string Par de propiedades clave-valor con el formato PropertyName = PropertyValue. Consulte las propiedades opcionales.

Nota:

Los archivos CSV con esquema no idéntico pueden dar lugar a que los datos aparezcan desplazados o que falten. Se recomienda separar archivos CSV con esquemas distintos para separar contenedores de almacenamiento y definir una tabla externa para cada contenedor de almacenamiento con el esquema adecuado.

Sugerencia

Proporcione más de una sola cuenta de almacenamiento para evitar la limitación de almacenamiento al exportar grandes cantidades de datos a la tabla externa. La exportación distribuirá las escrituras entre todas las cuentas proporcionadas.

Autenticación y autorización

El método de autenticación para acceder a una tabla externa se basa en el cadena de conexión proporcionado durante su creación y los permisos necesarios para acceder a la tabla varían en función del método de autenticación.

En la tabla siguiente se enumeran los métodos de autenticación admitidos para las tablas externas de Azure Storage y los permisos necesarios para leer o escribir en la tabla.

Método de autenticación Azure Blob Storage/Data Lake Storage Gen2 Data Lake Storage Gen1
Suplantación Permisos de lectura: Lector de datos de Storage Blob
Permisos de escritura: Colaborador de datos de Storage Blob		 Permisos de lectura: Lector
Permisos de escritura: Colaborador
Identidad administrada Permisos de lectura: Lector de datos de Storage Blob
Permisos de escritura: Colaborador de datos de Storage Blob		 Permisos de lectura: Lector
Permisos de escritura: Colaborador
Token de acceso compartido (SAS) Permisos de lectura: Lista y lectura
Permisos de escritura: escritura		 Este método de autenticación no se admite en Gen1.
Token de acceso de Microsoft Entra No se requieren permisos adicionales. No se requieren permisos adicionales.
Clave de acceso de la cuenta de almacenamiento No se requieren permisos adicionales. Este método de autenticación no se admite en Gen1.

Formato de particiones

La lista de particiones es cualquier combinación de columnas de partición, especificada mediante uno de los formularios que se muestran en la tabla siguiente.

Tipo de partición Sintaxis Notas
Columna virtual PartitionName : (datetime | string) Obtenga más información sobre las columnas virtuales.
Valor de columna de cadena PartitionName = : string ColumnName
Hash de valor de columna de cadena() PartitionName =long hash(: ColumnName Number, ) El hash es modulo Number.
Columna datetime truncada (valor) PartitionName datetime : = (startofyearstartofweek | | | startofdaystartofmonth) ( ColumnName ) Consulte la documentación sobre las funciones startofyear, startofmonth, startofweek o startofday .
Columna datetime truncada (valor) PartitionName bin = ( datetime : ColumnName , TimeSpan ) Obtenga más información sobre la función bin .

Formato de ruta de acceso

El parámetro PathFormat permite especificar el formato de la ruta de acceso URI de la carpeta de datos externa además de las particiones. Consta de una secuencia de elementos de partición y separadores de texto. Un elemento de partición hace referencia a una partición declarada en la cláusula partition by y el separador de texto es cualquier texto entre comillas. Los elementos de partición consecutivos se deben separar mediante el separador de texto.

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

Para construir el prefijo de ruta de acceso del archivo original, los elementos de partición se representan como cadenas y se separan con los separadores de texto correspondientes. Puede usar la datetime_pattern macro (datetime_pattern(DateTimeFormat, PartitionName)) para especificar el formato usado para representar un valor de partición datetime. La macro se adhiere a la especificación de formato de .NET y permite que los especificadores de formato se incluyan entre corchetes. Por ejemplo, los dos formatos siguientes son equivalentes:

  • 'year='aaaa'/month='MM
  • year={aaaa}/month={MM}

De forma predeterminada, los valores datetime se representan con los siguientes formatos:

Función de partición Formato predeterminado
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Column, 1d) yyyy/MM/dd
bin(Column, 1h) yyyy/MM/dd/HH
bin(Column, 1m) yyyy/MM/dd/HH/mm

Sugerencia

Para comprobar la corrección de la definición partitions y PathFormat , use la propiedad sampleUris o filesPreview al crear una tabla externa.

Columnas virtuales

Cuando los datos se exportan desde Spark, las columnas de partición (que se proporcionan al método del escritor de partitionBy tramas de datos) no se escriben en archivos de datos. Este proceso evita la duplicación de datos porque los datos ya están presentes en los nombres de carpeta (por ejemplo, column1=<value>/column2=<value>/) y Spark pueden reconocerlos al leerlos.

Las tablas externas admiten la lectura de estos datos en forma de virtual colums. Las columnas virtuales pueden ser de tipo string o datetimey se especifican con la sintaxis siguiente:

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

Para filtrar por columnas virtuales en una consulta, especifique los nombres de partición en el predicado de consulta:

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Propiedades opcionales

Propiedad Tipo Descripción
folder string Carpeta de la tabla
docString string Cadena que documenta la tabla
compressed bool Si se establece, indica si los archivos se comprimen como .gz archivos (solo se usan en el escenario de exportación).
includeHeaders string Para los formatos de texto delimitados (CSV, TSV, ...), indica si los archivos contienen un encabezado. Los valores posibles son: All (todos los archivos contienen un encabezado), FirstFile (el primer archivo de una carpeta contiene un encabezado), None (no hay archivos que contengan un encabezado).
namePrefix string Si se establece, indica el prefijo de los archivos. En las operaciones de escritura, todos los archivos se escribirán con este prefijo. En las operaciones de lectura, solo se leen los archivos con este prefijo.
fileExtension string Si se establece, indica las extensiones de archivo de los archivos. En escritura, los nombres de archivos terminarán con este sufijo. En lectura, solo se leerán los archivos con esta extensión de archivo.
encoding string Indica cómo se codifica el texto: UTF8NoBOM (valor predeterminado) o UTF8BOM.
sampleUris bool Si se establece, el resultado del comando proporciona varios ejemplos de URI de archivos de datos externos simulados según lo esperado por la definición de tabla externa. Esta opción ayuda a validar si los parámetros Partitions y PathFormat se definen correctamente.
filesPreview bool Si se establece, una de las tablas de resultados del comando contiene una vista previa del comando .show external table artifacts . Al igual que sampleUri, la opción ayuda a validar los parámetros Partitions y PathFormat de la definición de tabla externa.
validateNotEmpty bool Si se establece, los cadena de conexión se validan para tener contenido en ellos. Se producirá un error en el comando si la ubicación del URI especificada no existe o si no hay permisos suficientes para acceder a él.
dryRun bool Si se establece, la definición de tabla externa no se conserva. Esta opción es útil para validar la definición de tabla externa, especialmente junto con el filesPreview parámetro o sampleUris .

Nota:

No se tiene acceso a la tabla externa durante la creación, solo durante la consulta y exportación. Use la propiedad opcional durante la validateNotEmpty creación para asegurarse de que la definición de tabla sea válida y que el almacenamiento sea accesible.

Sugerencia

Para más información sobre el rol namePrefix y fileExtension las propiedades que desempeñan en el filtrado de archivos de datos durante la consulta, consulte la sección lógica de filtrado de archivos.

Lógica de filtrado de archivos

Al consultar una tabla externa, el rendimiento se mejora filtrando los archivos de almacenamiento externos irrelevantes. El proceso de iteración de archivos y la decisión de si se debe procesar un archivo es el siguiente:

  1. Cree un patrón de URI que represente un lugar donde se encuentran los archivos. Inicialmente, el patrón de URI es igual a un cadena de conexión proporcionado como parte de la definición de tabla externa. Si hay particiones definidas, se representan mediante PathFormat y, a continuación, se anexan al patrón de URI.

  2. Para todos los archivos que se encuentran en los patrones de URI creados, compruebe que:

    • Los valores de partición coinciden con predicados usados en una consulta.
    • El nombre del blob comienza con NamePrefix, si se define dicha propiedad.
    • El nombre del blob finaliza con FileExtension, si se define dicha propiedad.

Una vez que se cumplen todas las condiciones, el archivo se captura y procesa.

Nota:

El patrón de URI inicial se crea mediante valores de predicado de consulta. Esto funciona mejor para un conjunto limitado de valores de cadena, así como para intervalos de tiempo cerrados.

Ejemplos

Tabla externa sin particiones

En la siguiente tabla externa sin particiones, se espera que los archivos se coloquen directamente en los contenedores definidos:

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Particionado por fecha

En la tabla externa siguiente particionada por fecha, se espera que los archivos se coloquen en directorios del formato yyyy/MM/dddatetime predeterminado :

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

Particionado por mes

En la tabla externa siguiente particionada por mes, el formato de directorio es 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' 
)

Particionado por nombre y fecha

En la tabla externa siguiente, los datos se particionan primero por nombre de cliente y, a continuación, por fecha, lo que significa que la estructura de directorios esperada es, por ejemplo, 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' 
)

Particionado por hash y fecha

La tabla externa siguiente se particiona primero por hash de nombre de cliente (módulo diez) y, a continuación, por fecha. La estructura de directorios esperada es, por ejemplo, customer_id=5/dt=20190201, y los nombres de archivo de datos terminan con la .txt extensión :

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

Filtrar por columnas de partición en una consulta

Para filtrar por columnas de partición en una consulta, especifique el nombre de columna original en el predicado de consulta:

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Salida de ejemplo

TableName TableType Carpeta DocString Propiedades ConnectionStrings Particiones PathFormat
ExternalTable Blob ExternalTables Docs {"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("yyyyMMdd",Date)

Contenido relacionado