Share via

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 table Administración.

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

Syntax

(.create | .create-or-alter.alter | ) externaltableEsquema)=storagekindTableName( [partitionby(Particiones) [pathformat(=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 Tipo Requerido Descripción
TableName string ✔️ Un nombre de tabla externo que se adhiere a 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 varios 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 cómo debe tener 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, JSONTSVy Parquet.
StorageConnectionString string ✔️ Una o varias rutas de acceso separadas por comas a Azure Blob Storage contenedores de blobs, 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 las cadenas de conexión proporcionadas. Consulte cadenas de conexión de almacenamiento.
Propiedad string Un 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: Escribir		 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 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 Syntax 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,Número) El hash es modulo Number.
Columna datetime truncada (valor) PartitionName:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) Consulte la documentación sobre las funciones startofyear, startofmonth, startofweek o startofday .
Valor de columna datetime truncado (bin) PartitionName:datetime=bin(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 de archivo original, los elementos de partición se representan como cadenas y se separan con 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 .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(Columna, 1d) yyyy/MM/dd
bin(Columna, 1h) yyyy/MM/dd/HH
bin(Columna, 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 puede 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 datetime, y se especifican mediante 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 En el caso de 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 (ningún archivo contiene 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. Al escribir, los nombres de los 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, las cadenas de conexión se validan para tener contenido en ellas. Se producirá un error en el comando si la ubicación de 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 es válida y que el almacenamiento es accesible.

Sugerencia

Para más información sobre el rol namePrefix y fileExtension las propiedades juegan 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, se mejora el rendimiento filtrando los archivos de almacenamiento externos irrelevantes. El proceso de iteración de archivos y decidir 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 termina 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 siguiente tabla externa con particiones 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=20190201y 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