Créer et modifier des tables externes Stockage Azure
Les commandes de cet article peuvent être utilisées pour créer ou modifier une table externe Stockage Azure dans la base de données à partir de laquelle la commande est exécutée. Une table externe Stockage Azure référence les données situées dans Stockage Blob Azure, Azure Data Lake Store Gen1 ou Azure Data Lake Store Gen2.
Remarque
Si la table existe, la .create commande échoue avec une erreur. Utilisez ou .alter modifiez .create-or-alter des tables existantes.
Autorisations
Pour .create exiger au moins des autorisations utilisateur de base de données et .alter exiger au moins des autorisations d’administrateur de table.
Pour .create-or-alter une table externe à l’aide de l’authentification d’identité managée, les autorisations AllDatabasesAdmin sont requises.
Syntaxe
(.create | | .alter.create-or-alter) externaltableSchémastorage=)kind TableName([partitionby(Partitions) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Propriété [, ...])]
Remarque
kindest storage destiné à tous les types de magasins de données externes Stockage Azure. blob et adl sont dépréciés.
En savoir plus sur les conventions de syntaxe.
Paramètres
| Nom | Type | Requise | Description |
|---|---|---|---|
| TableName | string |
✔️ | Nom de table externe qui respecte les règles de noms d’entité. Une table externe ne peut pas avoir le même nom qu’une table normale dans la même base de données. |
| Schéma | string |
✔️ | Le schéma de données externes est une liste séparée par des virgules d’un ou plusieurs noms de colonnes et types de données, où chaque élément suit le format : ColumnName:ColumnType. Si le schéma est inconnu, utilisez infer_storage_schema pour déduire le schéma en fonction du contenu du fichier externe. |
| Partitions | string |
Liste séparée par des virgules de colonnes par lesquelles la table externe est partitionnée. La colonne de partition peut exister dans le fichier de données lui-même ou dans le cadre du chemin d’accès au fichier. Consultez la mise en forme des partitions pour savoir comment cette valeur doit ressembler. | |
| PathFormat | string |
Format de chemin d’URI du dossier de données externe à utiliser avec des partitions. Voir le format du chemin d’accès. | |
| DataFormat | string |
✔️ | Format de données, qui peut être l’un des formats d’ingestion. Nous vous recommandons d’utiliser le format des tables externes pour améliorer les performances de requête et d’exportation, sauf si vous utilisez JSON le Parquet mappage de chemins d’accès. Lorsque vous utilisez une table externe pour un scénario d’exportation, vous êtes limité aux formats suivants : CSV, TSVJSON et Parquet. |
| StorageConnectionString | string |
✔️ | Un ou plusieurs chemins séparés par des virgules pour Stockage Blob Azure conteneurs d’objets blob, les systèmes de fichiers Azure Data Lake Gen 2 ou les conteneurs Azure Data Lake Gen 1, y compris les informations d’identification. Le type de stockage de table externe est déterminé par les chaîne de connexion fournies. Consultez les chaîne de connexion de stockage. |
| Propriété | string |
Paire de propriétés clé-valeur au format PropertyName=PropertyValue. Voir les propriétés facultatives. |
Remarque
Les fichiers CSV dont le schéma n’est pas identique peuvent entraîner l’apparition de données décalées ou manquantes. Nous vous recommandons de séparer les fichiers CSV avec des schémas distincts pour séparer les conteneurs de stockage et de définir une table externe pour chaque conteneur de stockage avec le schéma approprié.
Conseil
Fournissez plus d’un compte de stockage unique pour éviter la limitation du stockage tout en exportant de grandes quantités de données vers la table externe. L’exportation distribue les écritures entre tous les comptes fournis.
Authentification et autorisation
La méthode d’authentification permettant d’accéder à une table externe est basée sur la chaîne de connexion fournie lors de sa création, et les autorisations requises pour accéder à la table varient en fonction de la méthode d’authentification.
Le tableau suivant répertorie les méthodes d’authentification prises en charge pour Stockage Azure tables externes et les autorisations nécessaires pour lire ou écrire dans la table.
| Méthode d'authentification | Stockage Blob Azure / Data Lake Storage Gen2 | Data Lake Storage Gen 1 |
|---|---|---|
| Emprunt d'identité | Autorisations de lecture : Lecteur de données Blob de stockage Autorisations d’écriture : Contributeur aux données Blob de stockage |
Autorisations de lecture : Lecteur Autorisations d’écriture : Contributeur |
| Identité gérée | Autorisations de lecture : Lecteur de données Blob de stockage Autorisations d’écriture : Contributeur aux données Blob de stockage |
Autorisations de lecture : Lecteur Autorisations d’écriture : Contributeur |
| Jeton d’accès partagé (SAS) | Autorisations de lecture : Liste + Lecture Autorisations d’écriture : Écriture |
Cette méthode d’authentification n’est pas prise en charge dans Gen1. |
| Jeton d’accès Microsoft Entra | Aucune autorisation supplémentaire n’est requise. | Aucune autorisation supplémentaire n’est requise. |
| Clé d’accès au compte de stockage | Aucune autorisation supplémentaire n’est requise. | Cette méthode d’authentification n’est pas prise en charge dans Gen1. |
Mise en forme des partitions
La liste des partitions est n’importe quelle combinaison de colonnes de partition, spécifiée à l’aide de l’un des formulaires indiqués dans le tableau suivant.
| Type de partition | Syntaxe | Notes |
|---|---|---|
| Colonne virtuelle | PartitionName: (datetime | string) |
En savoir plus sur les colonnes virtuelles. |
| Valeur de colonne de chaîne | PartitionName ColumnName:string= |
|
| Hachage de valeur de colonne de chaîne() | PartitionName ColumnName=:longhash(,Number) |
Le hachage est modulo Number. |
| Colonne datetime tronquée (valeur) | PartitionName (startofyearstartofmonthstartofdaystartofweek | | | ) (ColumnName:datetime=) |
Consultez la documentation sur les fonctions startofyear, startofmonth, startofweek ou startofday . |
Valeur de colonne =(bindatetime tronquée TimeSpan ColumnName,) |
En savoir plus sur la fonction bin . |
Format du chemin d’accès
Le paramètre PathFormat vous permet de spécifier le format du chemin d’URI du dossier de données externes en plus des partitions. Il se compose d’une séquence d’éléments de partition et de séparateurs de texte. Un élément de partition fait référence à une partition déclarée dans la clause de partition by , et le séparateur de texte est n’importe quel texte placé entre guillemets. Les éléments de partition consécutifs doivent être séparés à l’aide du séparateur de texte.
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
Pour construire le préfixe de chemin d’accès de fichier d’origine, les éléments de partition sont rendus sous forme de chaînes et séparés par des séparateurs de texte correspondants. Vous pouvez utiliser la datetime_pattern macro (datetime_pattern(DateTimeFormat,PartitionName)) pour spécifier le format utilisé pour le rendu d’une valeur de partition datetime. La macro respecte la spécification de format .NET et permet aux spécificateurs de format d’être placés entre crochets courbés. Par exemple, les deux formats suivants sont équivalents :
- 'year='aaaa'/month='MM
- year={aaaa}/month={MM}
Par défaut, les valeurs datetime sont rendues à l’aide des formats suivants :
| Fonction de partition | Format par défaut |
|---|---|
startofyear |
yyyy |
startofmonth |
yyyy/MM |
startofweek |
yyyy/MM/dd |
startofday |
yyyy/MM/dd |
bin(Colonne, 1d) |
yyyy/MM/dd |
bin(Colonne, 1h) |
yyyy/MM/dd/HH |
bin(Colonne, 1m) |
yyyy/MM/dd/HH/mm |
Conseil
Pour vérifier l’exactitude de la définition partitions et PathFormat , utilisez la propriété sampleUris ou filesPreview lors de la création d’une table externe.
Colonnes virtuelles
Lorsque les données sont exportées à partir de Spark, les colonnes de partition (fournies à la méthode de l’enregistreur de trame de partitionBy données) ne sont pas écrites dans des fichiers de données.
Ce processus évite la duplication des données, car les données sont déjà présentes dans les noms de dossiers (par exemple), column1=<value>/column2=<value>/et Spark peut la reconnaître en lecture.
Les tables externes prennent en charge la lecture de ces données sous la forme .virtual colums Les colonnes virtuelles peuvent être de type string ou datetime, et sont spécifiées à l’aide de la syntaxe suivante :
.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'
)
Pour filtrer par colonnes virtuelles dans une requête, spécifiez les noms de partition dans le prédicat de requête :
external_table("ExternalTable")
| where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Propriétés facultatives
| Propriété | Type | Description |
|---|---|---|
folder |
string |
Dossier de la table |
docString |
string |
Chaîne qui documente la table |
compressed |
bool |
Si la valeur est définie, indique si les fichiers sont compressés en tant que .gz fichiers (utilisés dans le scénario d’exportation uniquement) |
includeHeaders |
string |
Pour les formats de texte délimités (CSV, TSV, ...), indique si les fichiers contiennent un en-tête. Les valeurs possibles sont les suivantes : All (tous les fichiers contiennent un en-tête), FirstFile (le premier fichier d’un dossier contient un en-tête), None (aucun fichier ne contient d’en-tête). |
namePrefix |
string |
Si elle est définie, indique le préfixe des fichiers. Lors des opérations d’écriture, tous les fichiers sont écrits avec ce préfixe. Lors des opérations de lecture, seuls les fichiers avec ce préfixe sont lus. |
fileExtension |
string |
Si elle est définie, indique les extensions de fichier des fichiers. En écriture, les noms de fichiers se terminent par ce suffixe. En lecture, seuls les fichiers avec cette extension de fichier sont lus. |
encoding |
string |
Indique comment le texte est encodé : UTF8NoBOM (par défaut) ou UTF8BOM. |
sampleUris |
bool |
Si elle est définie, le résultat de la commande fournit plusieurs exemples d’URI de fichiers de données externes simulés, car ils sont attendus par la définition de table externe. Cette option permet de vérifier si les paramètres Partitions et PathFormat sont définis correctement. |
filesPreview |
bool |
Si elle est définie, l’une des tables de résultats de commande contient un aperçu de la commande .show external table artifacts . Comme sampleUri, l’option permet de valider les paramètres Partitions et PathFormat de la définition de table externe. |
validateNotEmpty |
bool |
Si elle est définie, les chaîne de connexion sont validées pour avoir du contenu. La commande échoue si l’emplacement d’URI spécifié n’existe pas ou s’il existe des autorisations insuffisantes pour y accéder. |
dryRun |
bool |
Si elle est définie, la définition de table externe n’est pas conservée. Cette option est utile pour valider la définition de table externe, en particulier conjointement avec le ou sampleUris le filesPreview paramètre. |
Remarque
La table externe n’est pas accessible lors de la création, uniquement pendant la requête et l’exportation. Utilisez la validateNotEmpty propriété facultative pendant la création pour vous assurer que la définition de table est valide et que le stockage est accessible.
Conseil
Pour en savoir plus sur le rôle namePrefix et fileExtension les propriétés joués dans le filtrage des fichiers de données pendant la requête, consultez la section logique de filtrage des fichiers.
Logique de filtrage de fichiers
Lors de l’interrogation d’une table externe, les performances sont améliorées en filtrant les fichiers de stockage externes non pertinents. Le processus d’itération des fichiers et de décider si un fichier doit être traité est le suivant :
Créez un modèle d’URI qui représente un emplacement où se trouvent les fichiers. Initialement, le modèle d’URI est égal à une chaîne de connexion fournie dans le cadre de la définition de table externe. S’il existe des partitions définies, elles sont rendues à l’aide de PathFormat, puis ajoutées au modèle d’URI.
Pour tous les fichiers trouvés sous le ou les modèles d’URI créés, vérifiez que :
- Les valeurs de partition correspondent aux prédicats utilisés dans une requête.
- Le nom de l’objet blob commence par
NamePrefix, si une telle propriété est définie. - Le nom de l’objet blob se termine par
FileExtension, si une telle propriété est définie.
Une fois toutes les conditions remplies, le fichier est récupéré et traité.
Remarque
Le modèle d’URI initial est généré à l’aide de valeurs de prédicat de requête. Cela fonctionne mieux pour un ensemble limité de valeurs de chaînes, ainsi que pour des intervalles de temps fermés.
Exemples
Table externe non partitionnée
Dans la table externe non partitionnée suivante, les fichiers doivent être placés directement sous le ou les conteneurs définis :
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Partitionné par date
Dans le tableau externe suivant partitionné par date, les fichiers sont censés être placés sous les répertoires du format yyyy/MM/dddatetime par défaut :
.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'
)
Partitionné par mois
Dans le tableau externe suivant partitionné par mois, le format de répertoire est 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'
)
Partitionné par nom et date
Dans le tableau externe suivant, les données sont partitionnées en premier par nom de client, puis par date, ce qui signifie que la structure de répertoires attendue est, par exemple : 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'
)
Partitionné par hachage et date
La table externe suivante est partitionnée d’abord par hachage de nom de client (modulo ten), puis par date. La structure de répertoires attendue est, par exemple, customer_id=5/dt=20190201et les noms de fichiers de données se terminent par l’extension .txt :
.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")
Filtrer par colonnes de partition dans une requête
Pour filtrer par colonnes de partition dans une requête, spécifiez le nom de colonne d’origine dans le prédicat de requête :
external_table("ExternalTable")
| where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Exemple de sortie
| TableName | TableType | Dossier | DocString | Propriétés | ConnectionStrings | Partitions | PathFormat |
|---|---|---|---|---|---|---|---|
| ExternalTable | Objet blob | ExternalTables | Documentation | {"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) |
Contenu connexe
- Interroger des tables externes.
- Exportez des données vers une table externe.
- Exportation continue de données vers une table externe.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour