Informations de référence sur l’accélération des requêtes en langage SQL
L’accélération des requêtes prend en charge un langage de type SQL ANSI pour exprimer des requêtes sur le contenu des blobs. Le dialecte SQL d’accélération des requêtes est un sous-ensemble de SQL ANSI, avec un ensemble limité de types de données, d’opérateurs, etc. pris en charge, mais il s’étend également au SQL ANSI pour prendre en charge les requêtes sur des formats de données hiérarchiques semi-structurés tels que JSON.
Syntaxe SELECT
La seule instruction SQL prise en charge par l’accélération des requêtes est l’instruction SELECT. Cet exemple retourne chaque ligne pour laquelle l’expression retourne la valeur true.
SELECT * FROM table [WHERE expression] [LIMIT limit]
Pour les données au format CSV, table doit être . Cela signifie que la requête sera exécutée sur le blob spécifié dans l’appel REST. Pour les données au format JSON, table est un « descripteur de table ». Consultez la section Descripteurs de table de cet article.
Dans l’exemple suivant, pour chaque ligne pour laquelle l’expression WHERE retourne la valeur true, cette instruction renverra une nouvelle ligne créée à partir de l’évaluation de chacune des expressions de projection.
SELECT expression [, expression ...] FROM table [WHERE expression] [LIMIT limit]
Vous pouvez spécifier une ou plusieurs colonnes spécifiques dans l’expression SELECT (par exemple : SELECT Title, Author, ISBN).
Notes
Le nombre maximal de colonnes spécifiques que vous pouvez utiliser dans l’expression SELECT est 49. Si vous avez besoin que votre instruction SELECT retourne plus de 49 colonnes, utilisez un caractère générique (*) pour l’expression SELECT (par exemple : SELECT *).
L’exemple suivant retourne un calcul d’agrégation (par exemple, la valeur moyenne d’une colonne particulière) sur chacune des lignes pour lesquelles l’expression retourne la valeur true.
SELECT aggregate_expression FROM table [WHERE expression] [LIMIT limit]
L’exemple suivant retourne des décalages appropriés pour le fractionnement d’un blob au format CSV. Consultez la section Sys.Split de cet article.
SELECT sys.split(split_size)FROM BlobStorage
Types de données
| Type de données | Description |
|---|---|
| INT | Entier signé 64 bits. |
| FLOAT | Virgule flottante (« double précision ») 64 bits. |
| STRING | Chaîne Unicode de longueur variable. |
| timestamp | Limite dans le temps. |
| BOOLEAN | True ou false. |
Lors de la lecture de valeurs à partir de données au format CSV, toutes les valeurs sont lues en tant que chaînes. Les valeurs de chaîne peuvent être converties en d’autres types à l’aide d’expressions CAST. Les valeurs peuvent être castées implicitement en d’autres types en fonction du contexte. Pour plus d’informations, consultez Priorité des types de données (Transact-SQL).
Expressions
Référencement des champs
Pour les données au format JSON ou les données au format CSV avec une ligne d’en-tête, les champs peuvent être référencés par nom. Les noms de champs peuvent être entre guillemets ou sans guillemets. Les noms de champs entre guillemets sont placés entre guillemets doubles ("), peuvent contenir des espaces et respectent la casse. Les noms de champs sans guillemets ne respectent pas la casse ni ne doivent contenir de caractères spéciaux.
Dans les données au format CSV, les champs peuvent également être référencés par un ordinal, précédé d’un trait de soulignement (_). Par exemple, le premier champ peut être référencé par _1, ou le onzième champ par _11. Le référencement des champs par ordinal est utile pour les données au format CSV qui ne contiennent pas de ligne d’en-tête, auquel cas le seul moyen de référencer un champ particulier est par un nombre ordinal.
Opérateurs
Les opérateurs SQL standard suivants sont pris en charge :
| Opérateur | Description |
|---|---|
= |
Compare l'égalité de deux expressions (opérateur de comparaison). |
!= |
Teste si une expression est différente d'une autre expression (opérateur de comparaison). |
<> |
Compare deux expressions avec l’opérateur de comparaison différent de. |
< |
Compare deux expressions avec l’opérateur de comparaison inférieur à. |
<= |
Compare deux expressions avec l’opérateur de comparaison inférieur ou égal à. |
> |
Compare deux expressions avec l’opérateur de comparaison supérieur à. |
>= |
Compare deux expressions avec l'opérateur de comparaison supérieur ou égal à. |
+ |
Additionne deux nombres. Cet opérateur arithmétique d'addition peut aussi ajouter un nombre de jours à une date. |
- |
Effectue une soustraction entre deux nombres (opérateur de soustraction arithmétique). |
/ |
Divise un nombre par un autre (opérateur arithmétique de division). |
* |
Multiplie deux expressions (opérateur arithmétique de multiplication). |
% |
Renvoie le reste d'un nombre divisé par un autre. |
AND |
Effectue une opération AND logique au niveau du bit avec deux valeurs entières. |
OR |
Exécute une opération logique OR au niveau du bit entre deux valeurs entières spécifiées, traduites en expressions binaires dans les instructions Transact-SQL. |
NOT |
Inverse une entrée booléenne. |
CAST |
Convertit une expression d'un type de données à un autre. |
BETWEEN |
Définit un intervalle sur lequel la recherche doit porter. |
IN |
Détermine si une valeur donnée correspond à la valeur d'une liste ou d'une sous-requête. |
NULLIF |
Retourne une valeur NULL si les deux expressions spécifiées sont égales. |
COALESCE |
Évalue les arguments dans l’ordre et retourne la valeur actuelle de la première expression qui ne prend pas initialement la valeur NULL. |
Si les types de données à gauche et à droite d’un opérateur sont différents, la conversion automatique sera effectuée en fonction des règles spécifiées ici : Priorité des types de données (Transact-SQL).
Le langage SQL d’accélération des requêtes prend en charge uniquement un très petit sous-ensemble des types de données décrits dans cet article. Consultez la section Types de données de cet article.
Casts
Le langage SQL d’accélération des requêtes prend en charge l’opérateur CAST, conformément aux règles ci-dessous : Conversion de types de données (Moteur de base de données).
Le langage SQL d’accélération des requêtes prend en charge uniquement un minuscule sous-ensemble des types de données décrits dans cet article. Consultez la section Types de données de cet article.
Fonctions de chaînes
Le langage SQL d’accélération des requêtes prend en charge les fonctions de chaîne SQL standard suivantes :
| Fonction | Description |
|---|---|
| CHAR_LENGTH | Retourne la longueur en caractères de l’expression de chaîne, si l’expression de chaîne est de type de données character ; sinon, retourne la longueur en octets de l’expression de chaîne (le plus petit entier qui n’est pas inférieur au nombre de bits divisé par 8). (Cette fonction est la même que la fonction CHARACTER_LENGTH.) |
| CHARACTER_LENGTH | Retourne la longueur en caractères de l’expression de chaîne, si l’expression de chaîne est de type de données character ; sinon, retourne la longueur en octets de l’expression de chaîne (le plus petit entier qui n’est pas inférieur au nombre de bits divisé par 8). (Cette fonction est la même que la fonction CHAR_LENGTH.) |
| LOWER | Retourne une chaîne de caractères après avoir transformé les caractères majuscules en caractères minuscules. |
| UPPER | Transforme les caractères d'une expression en minuscules en caractères majuscules. |
| SUBSTRING | Retourne une partie d’une expression de type caractère, binaire, texte ou image dans SQL Server. |
| TRIM | Supprime le caractère espace char(32) ou d’autres caractères spécifiés au début et à la fin d’une chaîne. |
| LEADING | Supprime l’espace char(32) ou d’autres caractères spécifiés au début d’une chaîne. |
| TRAILING | Supprime l’espace char(32) ou d’autres caractères spécifiés à la fin d’une chaîne. |
Voici quelques exemples :
| Fonction | Exemple | Résultats |
|---|---|---|
| CHARACTER_LENGTH | SELECT CHARACTER_LENGTH('abcdefg') from BlobStorage |
7 |
| CHAR_LENGTH | SELECT CHAR_LENGTH(_1) from BlobStorage |
1 |
| LOWER | SELECT LOWER('AbCdEfG') from BlobStorage |
abcdefg |
| UPPER | SELECT UPPER('AbCdEfG') from BlobStorage |
ABCDEFG |
| SUBSTRING | SUBSTRING('123456789', 1, 5) |
23456 |
| TRIM | TRIM(BOTH '123' FROM '1112211Microsoft22211122') |
Microsoft |
Fonctions de date
Les fonctions de date SQL standard suivantes sont prises en charge :
DATE_ADDDATE_DIFFEXTRACTTO_STRINGTO_TIMESTAMP
À l’heure actuelle, tous les formats de date des IS08601 standard sont convertis.
Fonction DATE_ADD
Le langage SQL d’accélération des requêtes prend en charge l’année, le mois, le jour, l’heure, la minute et la seconde pour la fonction DATE_ADD.
Exemples :
DATE_ADD(datepart, quantity, timestamp)
DATE_ADD('minute', 1, CAST('2017-01-02T03:04:05.006Z' AS TIMESTAMP)
Fonction DATE_DIFF
Le langage SQL d’accélération des requêtes prend en charge l’année, le mois, le jour, l’heure, la minute et la seconde pour la fonction DATE_DIFF.
DATE_DIFF(datepart, timestamp, timestamp)
DATE_DIFF('hour','2018-11-09T00:00+05:30','2018-11-09T01:00:23-08:00')
Fonction EXTRACT
Pour l’extraction autre que l’élément de date pris en charge pour la fonction DATE_ADD, le langage SQL d’accélération des requêtes prend en charge timezone_hour et timezone_minute en tant qu’élément de date.
Exemples :
EXTRACT(datepart FROM timestampstring)
EXTRACT(YEAR FROM '2010-01-01T')
Fonction TO_STRING
Exemples :
TO_STRING(TimeStamp , format)
TO_STRING(CAST('1969-07-20T20:18Z' AS TIMESTAMP), 'MMMM d, y')
Ce tableau décrit les chaînes que vous pouvez utiliser pour spécifier le format de sortie de la fonction TO_STRING.
| Chaîne de format | Output |
|---|---|
| yy | Année au format à 2 chiffres : « 99 » pour 1999 |
| y | Année au format à 4 chiffres |
| aaaa | Année au format à 4 chiffres |
| M | Mois de l’année : 1 |
| MM | Mois complété par un zéro : 01 |
| MMM | Abbr. Mois de l’année : JAN |
| MMMM | Nom de mois complet : mai |
| d | Jour du mois (1 à 31) |
| jj | Jour du mois complété par un zéro (01 à 31) |
| a | AM ou PM |
| h | Heure de la journée (1 à 12) |
| hh | Heure de la journée complétée par un zéro (01 à 12) |
| H | Heure de la journée (0 à 23) |
| HH | Heure de la journée complétée par un zéro (00 à 23) |
| m | Minute de l’heure (0 à 59) |
| MM | Minute complétée par un zéro (00 à 59) |
| s | Secondes des minutes (0 à 59) |
| ss | Secondes complétées par un zéro (00 à 59) |
| S | Fraction de secondes (0,1 à 0,9) |
| SS | Fraction de secondes (0,01 à 0,99) |
| SSS | Fraction de secondes (0,001 à 0,999) |
| X | Décalage en heures |
| XX ou XXXX | Décalage en heures et minutes (+0430) |
| XXX ou XXXXX | Décalage en heures et minutes (-07:00) |
| x | Décalage en heures (7) |
| xx ou xxxx | Décalage en heures et minutes (+0530) |
| Xxx ou xxxxx | Décalage en heures et minutes (+05:30) |
Fonction TO_TIMESTAMP
Seuls les formats ISO 8601 sont pris en charge.
Exemples :
TO_TIMESTAMP(string)
TO_TIMESTAMP('2007T')
Notes
Vous pouvez également utiliser la fonction UTCNOW pour récupérer l’heure système.
Expressions d’agrégation
Une instruction SELECT peut contenir une ou plusieurs expressions de projection ou une expression d’agrégation unique. Les expressions d’agrégation suivantes sont prises en charge :
| Expression | Description |
|---|---|
| COUNT(*) | Retourne le nombre d’enregistrements qui correspondent à l’expression de prédicat. |
| COUNT(expression) | Retourne le nombre d’enregistrements pour lesquels l’expression n’a pas une valeur nulle. |
| AVG(expression) | Retourne la moyenne de toutes les valeurs non nulles de l’expression. |
| MIN(expression) | Retourne la valeur non nulle minimale de l’expression. |
| MAX(expression | Retourne la valeur non nulle maximale de l’expression. |
| SUM(expression) | Retourne la somme de toutes les valeurs non nulles d’une expression. |
MISSING
L’opérateur IS MISSING est le seul opérateur non standard pris en charge par le langage SQL d’accélération des requêtes. Pour les données JSON, si un champ est manquant dans un enregistrement d’entrée particulier, le champ d’expression IS MISSING prend la valeur booléenne true.
Descripteurs de table
Pour les données CSV, le nom de la table est toujours BlobStorage. Par exemple :
SELECT * FROM BlobStorage
Pour les données JSON, des options supplémentaires sont disponibles :
SELECT * FROM BlobStorage[*].path
Cela permet d’interroger des sous-ensembles de données JSON.
Pour les requêtes JSON, vous pouvez indiquer le chemin d’accès dans le cadre de la clause FROM. Ces chemins d’accès permettent d’analyser le sous-ensemble de données JSON. Ces chemins d’accès peuvent faire référence à des valeurs Objet et Tableau JSON.
Prenons un exemple pour comprendre cela plus en détail.
Voici notre exemple de données :
{
"id": 1,
"name": "mouse",
"price": 12.5,
"tags": [
"wireless",
"accessory"
],
"dimensions": {
"length": 3,
"width": 2,
"height": 2
},
"weight": 0.2,
"warehouses": [
{
"latitude": 41.8,
"longitude": -87.6
}
]
}
Parmi les données ci-dessus, vous pouvez être intéressé uniquement par l’objet JSON warehouses. L’objet warehouses est un type de tableau JSON. Vous pouvez donc l’indiquer dans la clause FROM. Votre exemple de requête peut se présenter comme suit.
SELECT latitude FROM BlobStorage[*].warehouses[*]
La requête récupère tous les champs, mais sélectionne uniquement la latitude.
Si vous souhaitez accéder uniquement à la valeur de l’objet JSON dimensions, vous pouvez utiliser la référence à cet objet dans votre requête. Par exemple :
SELECT length FROM BlobStorage[*].dimensions
Cela limite également votre accès aux membres de l’objet dimensions. Si vous souhaitez accéder à d’autres membres de champs JSON et aux valeurs internes d’objets JSON, vous pouvez utiliser une requête telle que celle illustrée dans l’exemple suivant :
SELECT weight,warehouses[0].longitude,id,tags[1] FROM BlobStorage[*]
Notes
BlobStorage et BlobStorage[*] font tous deux référence à l’objet entier. Toutefois, si vous avez un chemin dans la clause FROM, vous devez utiliser BlobStorage[*].path
Sys.Split
Il s’agit d’une forme spéciale de l’instruction SELECT, qui est uniquement disponible pour les données au format CSV.
SELECT sys.split(split_size) FROM BlobStorage
Utilisez cette instruction dans les cas où vous souhaitez télécharger et traiter les enregistrements de données CSV par lots. De cette façon, vous pouvez traiter les enregistrements en parallèle au lieu de devoir télécharger tous les enregistrements en même temps. Cette instruction ne retourne pas d’enregistrements du fichier CSV. Au lieu de cela, elle retourne une collection de tailles de lot. Vous pouvez ensuite utiliser chaque taille de lot pour récupérer un lot d’enregistrements de données.
Utilisez le paramètre split_size pour spécifier le nombre d’octets que doit contenir chaque lot. Par exemple, si vous souhaitez traiter uniquement 10 Mo de données à la fois, l’instruction ressemble à ceci : SELECT sys.split(10485760)FROM BlobStorage, car 10 Mo est égal à 10 485 760 octets. Chaque lot contiendra autant d’enregistrements que possible dans ces 10 Mo.
Dans la plupart des cas, la taille de chaque lot sera légèrement supérieure au nombre que vous spécifiez. Cela est dû au fait qu’un lot ne peut pas contenir un enregistrement partiel. Si le dernier enregistrement d’un lot commence avant que votre seuil soit atteint, le lot est plus volumineux afin de pouvoir contenir l’enregistrement complet. La taille du dernier lot sera probablement inférieure à la taille que vous spécifiez.
Notes
Le valeur de split_size doit être au minimum de 10 Mo (10485760).