Bonjour à la communauté,
Dans cet article, je vais présenter mon application iris-mlm-explainer

Cette application web se connecte au service SQL d'InterSystems Cloud pour créer, entraîner, valider et prédire des modèles d'apprentissage automatique, faire des Prédictions et afficher un tableau de bord de tous les modèles entraînés avec une explication du fonctionnement d'un modèle d'apprentissage automatique ajusté. Le tableau de bord fournit des graphiques interactifs sur les performances du modèle, les importances des caractéristiques, les contributions des caractéristiques aux prédictions individuelles, les graphiques de dépendance partielle, les valeurs SHAP (interaction), la visualisation des arbres de décision individuels, etc.

Conditions préalables

• Vous devez avoir un compte à SQL d'InterSystems Cloud
• Vous devez avoir <a book="" fr="" getting-started-installing-git="" git-scm.com="" https:="" v2="">Git</a> installé localement.
• Vous devez avoir <a downloads="" https:="" www.python.org="">Python3</a> installé localement.  

Démarrage

Nous allons suivre les étapes suivantes pour créer et afficher le tableau de bord explicatif d'un modèle :

• Etape 1 : Fermeture/git pull du référentiel

• Étape 2 : Connexion au portail de service SQL d'InterSystems Cloud

  • Étape 2.1 : Ajout et gestion de fichiers
  • Étape 2.2 : Importation des fichiers DDL et des fichiers de données
  • Étape 2.3 : Création du modèle
  • Étape 2.4 : Entraînement du modèle
  • Étape 2.5 : Validation du modèle
  • Étape 3 : Activation de l'environnement virtuel Python

• Étape 4 : Exécution de l'application Web pour la prédiction

• Étape 5 : Exploration du tableau de bord explicatif

Etape 1 : Fermeture/git Extraction du référentiel

Commençons donc par la première étape

Créer un dossier et Cloner/utiliser le git pull pour le référentiel dans n'importe quel répertoire local.

git clone https://github.com/mwaseem75/iris-mlm-explainer.git

Étape 2 : Connexion au portail de service SQL d'InterSystems Cloud

Connectez-vous au portail InterSystems Cloud Service Portal

Sélectionner le déploiement en cours

Étape 2.1 : Ajout et gestion des fichiers

Cliquez sur Ajout et gestion de fichiers (Add and Manage Files)

Le référentiel contient les fichiers USA_Housing_tables_DDL.sql(DDL pour créer les tables), USA_Housing_train.csv(données d'entraînement), et USA_Housing_validate.csv(pour la validation) dans le dossier datasets. Sélectionnez le bouton de téléchargement pour ajouter ces fichiers.

Étape 2.2 : Importation des fichiers DDL et des fichiers de données

Cliquez sur Importation de fichiers, puis sur le bouton radio Instruction(s) DDL ou DML, puis sur le bouton suivant.

Cliquez sur le bouton radio Intersystems IRIS et cliquez ensuite sur le bouton suivant

Sélectionnez le fichier USA_Housing_tables_DDL.sql et appuyez sur le bouton d'importation de fichiers.

Cliquez sur le bouton d'importation "Import" dans la boîte de dialogue de confirmation pour créer le tableau.

###

Cliquez sur le bouton des outils de requête SQL (SQL Query tools) pour vérifier que les tableaux sont créés.

###

Importez des fichiers de données

Cliquez sur Importation de fichiers (Import files), puis sur le bouton radio Données CSV (CSV data), et enfin sur le bouton suivant.

Selectionnez le fichier USA_Housing_train.csv et cliquez sur le bouton suivant

###

Sélectionnez le fichier USA_Housing_train.csv dans la liste déroulante, cochez les cases d'importation de lignes en tant que ligne d'en-tête et de noms de champs dans la ligne d'en-tête correspondant aux noms de colonnes dans le tableau sélectionné, puis cliquez sur Importation de fichiers.

cliquer sur "importation" dans la boîte de dialogue de confirmation

Assurez-vous que 4000 lignes sont mises à jour

Procédez de la même manière pour importer le fichier USA_Housing_validate.csv qui contient 1500 enregistrements.

Étape 2.3 : Création du modèle

Cliquez sur les outils IntegratedM et sélectionnez Créer un panneau (Create Panel).

Saisissez USAHousingPriceModel dans le champ de nom du modèle (Model Name), sélectionnez le tableau usa_housing_train et Prix dans la liste déroulante des champs à prédire (Field to predict). Cliquez sur le bouton "Création du modèle" pour créer le modèle.

Étape 2.4 : Entraînement du modèle

sélectionnez le panneau d'entraînement (Train Panel), sélectionnez USAHousingPriceModel dans la liste déroulante du modèle à entraîner et saisissez USAHousingPriceModel_t1 dans le champ du nom du modèle d'entraînement (Train Model Name)

###

Le modèle sera entraîné une fois l'état de fonctionnement (Run Status) achevé

###

Étape 2.5 : Validation du modèle

Sélectionnez le panneau de validation (Validate Panel), sélectionnez USAHousingPriceModel_t1 dans le modèle entraîné pour valider la liste déroulante, sélectionnez usa_houseing_validate dans le tableau pour valider le modèle à partir de la liste déroulante et cliquez sur le bouton de validation du modèle.

###

Cliquez sur affichage des mesures de validation pour visualiser les mesures.

Cliquez sur l'icône graphique pour afficher le graphique Prédiction VS Réalité.

Étape 3 : Activation de l'environnement virtuel Python

Le référentiel contient déjà un dossier d'environnement virtuel python (venv) avec toutes les bibliothèques nécessaires.

Il suffit d'activer l'environnement
Pour Unix ou MacOS :

<span class="hljs-meta">$</span><span class="bash"> <span class="hljs-built_in">source</span> venv/bin/activate</span>

Pour Windows:

venv\scripts\activate

Étape 4 : Définir les paramètres de connexion à InterSystems SQL Cloud

Le référentiel contient le fichier config.py. Il suffit de l'ouvrir et de le paramétrer

Mettez les mêmes valeurs que celles utilisées dans InterSystems SQL Cloud

Étape 4 : Exécution de l'application Web pour la prédiction

Exécutez la commande suivante dans l'environnement virtuel pour démarrer notre application principale

python app.py

###

Pour démarrer l'application, naviguez jusqu'à http://127.0.0.1:5000/

Entrez l'âge de la maison, le nombre de pièces, le nombre de chambres et la population de la région pour obtenir la prédiction

Étape 5 : Exploration du tableau de bord explicatif

Enfin, exécutez la commande suivante dans l'environnement virtuel pour démarrer notre application principale

python expdash.py

Pour démarrer l'application, naviguez jusqu'à http://localhost:8050/

L'application répertorie tous les modèles entraînés ainsi que notre modèle USAHousingPriceModel. Cliquez sur "aller au panneau de bord" ("go to dashboard") pour voir l'explication du modèle.

Importance des fonctionnalités. Quelles sont les fonctionnalités ayant eu l'impact le plus important ?

Mesures quantitatives de la performance des modèles : dans quelle mesure la valeur prédite est-elle proche de la valeur observée ?

Prédiction et Comment chaque fonctionnalité a-t-elle contribué à la prédiction ?

Ajustez les valeurs des fonctionnalités pour modifier la prédiction

Sommaire des SHAPs, Classement des caractéristiques par valeurs de SHAPs

Sommaire des interactions, classement des fonctionnalités par valeur d'interaction de SHAP

Arbres de décision, affichage des arbres de décision individuels dans la forêt aléatoire

Merci

Dans ce didacticiel, j'aimerais parler des requêtes de classe (Class Query). Pour être plus précis, à propos des requêtes basées sur du code écrit par l'utilisateur :

Keywords:  IRIS, IntegratedML, apprentissage automatique, Covid-19, Kaggle 

Objectif

J'ai récemment remarqué un jeu de données Kaggle permettant de prédire si un patient Covid-19 sera admis en soins intensifs. Il s'agit d'un tableur de 1925 enregistrements comprenant 231 colonnes de signes vitaux et d'observations, la dernière colonne " USI " valant 1 pour Oui ou 0 pour Non.

Ce jeu de données représente un bon exemple de ce que l'on appelle une tâche "traditionnelle de ML". Les données semblent avoir une quantité suffisante et une qualité relativement bonne. Il pourrait avoir de meilleures chances d'être appliqué directement sur le kit IntegratedML demo. Quelle serait donc l'approche la plus simple pour un test rapide basé sur les pipelines ML normaux par rapport à l'approche possible avec IntegratedML ?

Champ d'application

Nous examinerons brièvement quelques étapes normales de ML, telles que :

• Analyse des données (EDA)
• Sélection des caractéristiques
• Sélection du modèle
• Ajustement des paramètres du modèle via le quadrillage

Vs. 

• Approches ML intégrées via SQL.

Il est exécuté sur un serveur AWS Ubuntu 16.04 avec Docker-compose, etc.  

Environnement

Nous allons réutiliser l'environnement Docker de integredML-demo-template:

Le fichier de bloc-notes suivant est exécuté sur "tf2jupyter", et IRIS avec IntegratedML sur "irismlsrv". Docker-compose fonctionne sur un AWS Ubuntu 16.04.

Données et tâches

Le jeu de données contient 1925 enregistrements collectés auprès de 385 patients, chacun comportant exactement 5 enregistrements de rendez-vous. Sur ses 231 colonnes, une seule, "USI", constitue notre cible d'apprentissage et de prédiction, et les 230 autres colonnes pourraient toutes être utilisées comme entrées de quelque manière que ce soit. L'unité de soins intensifs a une valeur binaire de 1 ou 0. À l'exception de deux colonnes qui semblent être des chaînes catégorielles (présentées comme "objet" dans le cadre de données), toutes les autres sont numériques.

import numpy as np
import pandas as pd
from sklearn.impute import SimpleImputer
import matplotlib.pyplot as plt
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import classification_report, roc_auc_score, roc_curve
import seaborn as sns
sns.set(style="whitegrid")

import os
for dirname, _, filenames in os.walk('./input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

./input/datasets_605991_1272346_Kaggle_Sirio_Libanes_ICU_Prediction.xlsx

df = pd.read_excel("./input/datasets_605991_1272346_Kaggle_Sirio_Libanes_ICU_Prediction.xlsx")
df

<th>
  IDENTIFIANT_DE_VISITE_DU_PATIENT
</th>

<th>
  ÂGE_AU-DESSUS65
</th>

<th>
  ÂGE_POURCENTAGE
</th>

<th>
  GENRE
</th>

<th>
  GROUPE DE MALADIES 1
</th>

<th>
  GROUPE DE MALADIES 2
</th>

<th>
  GROUPE DE MALADIES 3
</th>

<th>
  GROUPE DE MALADIES 4
</th>

<th>
  GROUPE DE MALADIES 5
</th>

<th>
  GROUPE DE MALADIES 6
</th>

<th>
  ...
</th>

<th>
  DIFFÉRENCE_DE_TEMPÉRATURE
</th>

<th>
  DIFFÉRENCE_DE SATURATION_D'OXYGÈNE
</th>

<th>
  DIFFÉRENCE_DE_TENSION_DIASTOLIQUE_REL
</th>

<th>
  DIFFÉRENCE_DE_TENSION_SISTOLIQUE_REL
</th>

<th>
  DIFFÉRENCE_DU_RYTHME_CARDIAQUE_REL
</th>

<th>
  DIFFÉRENCE_DE_TAUX_RESPIRATOIRE_REL
</th>

<th>
  DIFFÉRENCE_DE_TEMPÉRATURE_REL
</th>

<th>
  DIFFÉRENCE_DE_SATURATION_D'OXYGÈNE_REL
</th>

<th>
  FENÊTRE
</th>

<th>
  ICU
</th>

<td>
</td>

<td>
  1
</td>

<td>
  âge de 60-69 ans
</td>

<td>
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  1.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  0-2
</td>

<td>
</td>

<td>
</td>

<td>
  1
</td>

<td>
  âge de 60-69 ans
</td>

<td>
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  1.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  2-4
</td>

<td>
</td>

<td>
</td>

<td>
  1
</td>

<td>
  âge de 60-69 ans
</td>

<td>
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  1.0
</td>

<td>
  ...
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  4-6
</td>

<td>
</td>

<td>
</td>

<td>
  1
</td>

<td>
  âge de 60-69 ans
</td>

<td>
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  1.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  NaN
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  6-12
</td>

<td>
</td>

<td>
</td>

<td>
  1
</td>

<td>
  âge de 60-69 ans
</td>

<td>
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  1.0
</td>

<td>
  ...
</td>

<td>
  -0.238095
</td>

<td>
  -0.818182
</td>

<td>
  -0.389967
</td>

<td>
  0.407558
</td>

<td>
  -0.230462
</td>

<td>
  0.096774
</td>

<td>
  -0.242282
</td>

<td>
  -0.814433
</td>

<td>
  AU-DESSUS_12
</td>

<td>
  1
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  ...
</td>

<td>
  384
</td>

<td>
</td>

<td>
  âge de 50-59 ans
</td>

<td>
  1
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  0-2
</td>

<td>
</td>

<td>
  384
</td>

<td>
</td>

<td>
  âge de 50-59 ans
</td>

<td>
  1
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  2-4
</td>

<td>
</td>

<td>
  384
</td>

<td>
</td>

<td>
  âge de 50-59 ans
</td>

<td>
  1
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  4-6
</td>

<td>
</td>

<td>
  384
</td>

<td>
</td>

<td>
  âge de 50-59 ans
</td>

<td>
  1
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  ...
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  -1.000000
</td>

<td>
  6-12
</td>

<td>
</td>

<td>
  384
</td>

<td>
</td>

<td>
  âge de 50-59 ans
</td>

<td>
  1
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  1.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  0.0
</td>

<td>
  ...
</td>

<td>
  -0.547619
</td>

<td>
  -0.838384
</td>

<td>
  -0.701863
</td>

<td>
  -0.585967
</td>

<td>
  -0.763868
</td>

<td>
  -0.612903
</td>

<td>
  -0.551337
</td>

<td>
  -0.835052
</td>

<td>
  AU-DESSUS_12
</td>

<td>
</td>

1925 lignes × 231 colonnes

df.dtypes

IDENTIFIANT_DE_VISITE_DU_PATIENT        int64
ÂGE_AU-DESSUS65                     int64
ÂGE_POURCENTAGE                  object
GENRE                          int64
GROUPE DE MALADIES 1            float64
                               ...   
DIFFÉRENCE_DE_TAUX_RESPIRATOIRE_REL     float64
DIFFÉRENCE_DE_TEMPÉRATURE_REL          float64
DIFFÉRENCE_DE SATURATION_D'OXYGÈNE_REL    float64
FENÊTRE                         object
USI                             int64
Longeur: 231, dtype: object

Il existe certainement plusieurs options pour définir ce problème et ses approches. La première option qui nous vient à l'esprit est qu'il peut s'agir d'un problème fondamental de "classification binaire". Nous pouvons traiter les 1925 enregistrements comme des enregistrements individuels "apatrides", qu'ils proviennent ou non du même patient. Bien sûr, il pourrait également s'agir d'un problème de "régression" si nous traitions les valeurs de l'unité de soins intensifs et d'autres valeurs comme étant toutes numériques.

Il existe certainement d'autres approches possibles. Par exemple, nous pouvons considérer que l'ensemble de données comporte 385 jeux distincts de courtes "séries temporelles", chacun pour un patient. Nous pourrions dissoudre le jeu entier en 385 jeux distincts pour Train/Val/Test, et pourrions-nous essayer des modèles d'apprentissage profond tels que CNN ou LSTM pour capturer la "phase ou le modèle de développement des symptômes" caché dans chaque jeu pour chaque patient individuel ? C'est possible. Ce faisant, nous pourrions également appliquer une augmentation des données pour enrichir les données de test par divers moyens. Il s'agit là d'un sujet qui dépasse le cadre de cet article.

Dans cet article, nous nous contenterons de tester rapidement l'approche ML dite "traditionnelle" par rapport à l'approche IntegratedML (une approche AutoML)..  

Approche ML "traditionnelle" ?

Il s'agit d'un jeu de données relativement normalisé par rapport à la plupart des cas réels, à l'exception de quelques valeurs manquantes, de sorte que nous pourrions sauter la partie relative à l'ingénierie des caractéristiques et utiliser directement les colonnes comme caractéristiques. Passons donc directement à la sélection des caractéristiques.

Imputation des données manquantes

Il faut d'abord s'assurer que toutes les valeurs manquantes sont remplies au moyen d'une imputation simple :

df_cat = df.select_dtypes(include=['object'])
df_numeric = df.select_dtypes(exclude=['object'])
imp = SimpleImputer(missing_values=np.nan, strategy='mean')
idf = pd.DataFrame(imp.fit_transform(df_numeric))
idf.columns = df_numeric.columns
idf.index = df_numeric.index
idf.isnull().sum()

Sélection sur les caractéristiques

Nous pouvons certainement utiliser la fonction de corrélation normale intégrée dans la base de données pour calculer la corrélation entre les valeurs de chaque colonne et les unités de soins intensifs.

l'ingénierie des caractéristiques - corrélation {#featuring-engineering---correlation}

idf.drop(["PATIENT_VISIT_IDENTIFIER"],1)
idf = pd.concat([idf,df_cat ], axis=1)
cor = idf.corr()
cor_target = abs(cor["ICU"])
relevant_features = cor_target[cor_target>0.1]   # correlation above 0.1
print(cor.shape, cor_target.shape, relevant_features.shape)
#relevant_features.index
#relevant_features.index.shape

Il répertorie 88 caractéristiques présentant une corrélation >0,1 avec la valeur cible de l'unité de soins intensifs. Ces colonnes peuvent être directement utilisées comme entrée de notre modèle

J'ai également exécuté quelques autres "méthodes de sélection de caractéristiques" qui sont normalement utilisées dans les tâches traditionnelles de ML :

Sélection des caractéristiques - Chi carré {#feature-selection---Chi-squared}

from sklearn.feature_selection import SelectKBest
from sklearn.feature_selection import chi2
from sklearn.preprocessing import MinMaxScaler
X_norm = MinMaxScaler().fit_transform(X)
chi_selector = SelectKBest(chi2, k=88)
chi_selector.fit(X_norm, y)
chi_support = chi_selector.get_support()
chi_feature = X.loc[:,chi_support].columns.tolist()
print(str(len(chi_feature)), 'selected features', chi_feature)

88 caractéristiques sélectionnées ['ÂGE_AU-DESSUS65', 'GENRE', 'GROUPE DE MALADIES 1', ... ... 'P02_VENEUS_MIN', 'P02_VENEUS_MAX', ... ... RATURE_MAX', 'DIFFÉRENCE_DE_TENSION_ARTÉRIELLE_DIASTOLIQUE', ... ... 'DIFFÉRENCE_DE_TEMPÉRATURE_REL', 'DIFFÉRENCE_DE SATURATION_D'OXYGÈNE_REL']

Sélection des caractéristiques - Corrélation de Pearson 

def cor_selector(X, y,num_feats):
    cor_list = []
    feature_name = X.columns.tolist()
    # calculate the correlation with y for each feature
    for i in X.columns.tolist():
        cor = np.corrcoef(X[i], y)[0, 1]
        cor_list.append(cor)
    # replace NaN with 0
    cor_list = [0 if np.isnan(i) else i for i in cor_list]
    # feature name
    cor_feature = X.iloc[:,np.argsort(np.abs(cor_list))[-num_feats:]].columns.tolist()
    # Sélection des caractéristiques? 0 for not select, 1 for select
    cor_support = [Vrai if i in cor_feature else False for i in feature_name]
    return cor_support, cor_featurecor_support, cor_feature = cor_selector(X, y, 88)
print(str(len(cor_feature)), 'selected features:  ', cor_feature)

88 caractéristiques sélectionnées:   ['TEMPÉRATURE_MOYENNE', 'TENSION_DIASTOLIQUE_MAX', ... ... 'DIFFÉRENCE_DE_TAUX_ RESPIRATOIRE', 'AUX_ RESPIRATOIRE_MAX']

Sélection des caractéristiques - élimination de caractéristiques récursives (RFE) {#feature-selection---Recursive-Feature-Elimination-(RFE)}

from sklearn.feature_selection import RFE
from sklearn.linear_model import LogisticRegression
rfe_selector = RFE(estimator=LogisticRegression(), n_features_to_select=88, step=100, verbose=5)
rfe_selector.fit(X_norm, y)
rfe_support = rfe_selector.get_support()
rfe_feature = X.loc[:,rfe_support].columns.tolist()
print(str(len(rfe_feature)), 'selected features: ', rfe_feature)

Estimateur d'ajustement avec 127 caractéristiques.
88 caractéristiques sélectionnées:  ['ÂGE_AU-DESSUS65', 'GENRE', ... ... 'DIFFÉRENCE_DE_TAUX_ RESPIRATOIRE_REL', 'DIFFÉRENCE_DE_TEMPÉRATURE_REL']

Sélection des caractéristiques - Lasso

ffrom sklearn.feature_selection import SelectFromModel
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import MinMaxScaler
X_norm = MinMaxScaler().fit_transform(X)
embeded_lr_selector = SelectFromModel(LogisticRegression(penalty="l2"), max_features=88)
embeded_lr_selector.fit(X_norm, y)
embeded_lr_support = embeded_lr_selector.get_support()
embeded_lr_feature = X.loc[:,embeded_lr_support].columns.tolist()
print(str(len(embeded_lr_feature)), 'selected features', embeded_lr_feature)

65 caractéristiques sélectionnées ['ÂGE_AU-DESSUS65', 'GENRE', ... ... 'DIFFÉRENCE_DE_TAUX_ RESPIRATOIRE_REL', 'DIFFÉRENCE_DE_TEMPÉRATURE_REL']

Sélection des caractéristiques - RF Tree-based: SelectFromModel

from sklearn.feature_selection import SelectFromModel
from sklearn.ensemble import RandomForestClassifier
embeded_rf_selector = SelectFromModel(RandomForestClassifier(n_estimators=100), max_features=227)
embeded_rf_selector.fit(X, y)
embeded_rf_support = embeded_rf_selector.get_support()
embeded_rf_feature = X.loc[:,embeded_rf_support].columns.tolist()
print(str(len(embeded_rf_feature)), 'selected features', embeded_rf_feature)

48 selected features ['ÂGE_AU-DESSUS65', 'GENRE', ... ... 'DIFFÉRENCE_DE_TEMPÉRATURE_REL', 'DIFFÉRENCE_DE SATURATION_D'OXYGÈNE_REL']

Sélection des caractéristiques - LightGBM or XGBoost {#feature-selection---LightGBM-or-XGBoost}

from sklearn.feature_selection import SelectFromModel
from lightgbm import LGBMClassifierlgbc=LGBMClassifier(n_estimators=500, learning_rate=0.05, num_leaves=32, colsample_bytree=0.2,
            reg_alpha=3, reg_lambda=1, min_split_gain=0.01, min_child_weight=40)embeded_lgb_selector = SelectFromModel(lgbc, max_features=128)
embeded_lgb_selector.fit(X, y)embeded_lgb_support = embeded_lgb_selector.get_support()
embeded_lgb_feature = X.loc[:,embeded_lgb_support].columns.tolist()
print(str(len(embeded_lgb_feature)), 'selected features:  ', embeded_lgb_feature)
embeded_lgb_feature.index

56 selected features:   ['ÂGE_AU-DESSUS65', 'GENRE', 'HTN', ... ... 'DIFFÉRENCE_DE_TEMPÉRATURE_REL', 'DIFFÉRENCE_DE SATURATION_D'OXYGÈNE_REL']

Sélection des caractéristiques - Les regrouper tous {#feature-selection---Ensemble-them-all}

feature_name = X.columns.tolist()
# regrouper toute la sélection
feature_selection_df = pd.DataFrame({'Feature':feature_name, 'Pearson':cor_support, 'Chi-2':chi_support, 'RFE':rfe_support, 'Logistics':embeded_lr_support, 'Random Forest':embeded_rf_support, 'LightGBM':embeded_lgb_support})
# compter les temps sélectionnés pour chaque caractéristique
feature_selection_df['Total'] = np.sum(feature_selection_df, axis=1)
# afficher les 100 premières
num_feats = 227
feature_selection_df = feature_selection_df.sort_values(['Total','Feature'] , ascending=False)
feature_selection_df.index = range(1, len(feature_selection_df)+1)
feature_selection_df.head(num_feats)
df_selected_columns = feature_selection_df.loc[(feature_selection_df['Total'] > 3)]
df_selected_columns

Nous pouvons dresser la liste des caractéristiques qui ont été sélectionnées dans le cadre d'au moins quatre méthodes :

... ...

Nous pouvons certainement choisir ces 58 caractéristiques. Entre-temps, l'expérience nous a appris que la sélection des caractéristiques n'est pas nécessairement toujours un vote démocratique ; le plus souvent, elle peut être spécifique au problème du domaine, aux données spécifiques et parfois au modèle ou à l'approche ML spécifique que nous allons adopter plus tard.

Sélection des caractéristiques - Outils tiers 

Il existe des outils industriels et des outils AutoML largement utilisés, par exemple DataRobot qui peut fournir une bonne sélection automatique des caractéristiques :  

Le graphe DataRobot ci-dessus montre, sans surprise, que les valeurs de fréquence respiratoire et de tension artérielle sont les caractéristiques les plus pertinentes pour l'admission en soins intensifs.    

Sélection des caractéristiques - Sélection finale
Dans ce cas, j'ai fait quelques expériences rapides et j'ai remarqué que la sélection des caractéristiques par LightGBM donnait un résultat un peu meilleur, c'est pourquoi nous n'utiliserons que cette méthode de sélection.   

df_selected_columns = embeded_lgb_feature  # mieux que la sélection ensemblistedataS = pd.concat([idf[df_selected_columns],idf['ICU'], df_cat['FENÊTRE']],1)
dataS.ICU.value_counts()
print(dataS.shape)

(1925, 58)

Nous pouvons voir que 58 caractéristiques sont sélectionnées, c'est-à-dire ni trop peu, ni trop beaucoup, ce qui semble être la bonne quantité pour ce problème spécifique de classification binaire à cible unique. 

Déséquilibre des données

plt.figure(figsize=(10,5))
count = sns.countplot(x = "USI",data=data)
count.set_xticklabels(["Non admis", "Admis"])
plt.xlabel("Admission à l'USI")
plt.ylabel("Nombre de patients")
plt.show()

Cela indique que les données sont déséquilibrées, seuls 26 % des enregistrements étant admis en USI. Cela aura un impact sur les résultats et nous pouvons donc envisager des approches normales d'équilibrage des données telles que SMOTE, etc.

Nous pouvons essayer toutes sortes d'autres AED pour analyser les différentes distributions de données en conséquence. 

Exécuter une formation de base en LR

Le site Kaggle propose de jolis carnets d'entraînement rapide que nous pouvons exécuter rapidement en fonction de notre propre sélection de colonnes de caractéristiques. Commençons par une exécution rapide du classificateur LR pour le pipeline de formation :

data2 = pd.concat([idf[df_selected_columns],idf['USI'], df_cat['FENÊTRE']],1)   
data2.AGE_ABOVE65 = data2.AGE_ABOVE65.astype(int)
data2.ICU = data2.ICU.astype(int)
X2 = data2.drop("USI",1)
y2 = data2.ICU

from sklearn.preprocessing import LabelEncoder
label_encoder = LabelEncoder()
X2.WINDOW = label_encoder.fit_transform(np.array(X2["FENÊTRE"].astype(str)).reshape((-1,)))

confusion_matrix2 = pd.crosstab(y2_test, y2_hat, rownames=['Réel'], colnames=['Prédit'])
sns.heatmap(confusion_matrix2, annot=Vrai, fmt = 'g', cmap = 'Reds') print("ORIGINAL")
print(classification_report(y_test, y_hat))
print("USI = ",roc_auc_score(y_test, y_hat),'\n\n')
print("ENCODAGE D'ÉTIQUETTE")
print(classification_report(y2_test, y2_hat))
print("ASC = ",roc_auc_score(y2_test, y2_hat))
y2hat_probs = LR.predict_proba(X2_test)
y2hat_probs = y2hat_probs[:, 1] fpr2, tpr2, _ = roc_curve(y2_test, y2hat_probs) plt.figure(figsize=(10,7))
plt.plot([0, 1], [0, 1], 'k--')
plt.plot(fpr, tpr, label="Base")
plt.plot(fpr2,tpr2,label="Étiquette encodée")
plt.xlabel('Taux de faux positifs')
plt.ylabel('Taux de vrais positifs')
plt.title('Courbe ROC')
plt.legend(loc="meilleur")
plt.show()

ORIGINAL
              précision    rappel  score f1   support
           0       0.88      0.94      0.91       171
           1       0.76      0.57      0.65        54
    exactitude                           0.85       225
   moyenne macro       0.82      0.76      0.78       225
moyenne pondérée       0.85      0.85      0.85       225
ASC=  0.7577972709551657
LABEL ENCODING
              précision    rappel  score f1   support
           0       0.88      0.93      0.90       171
           1       0.73      0.59      0.65        54
    accuracy                           0.85       225
   moyenne macro       0.80      0.76      0.78       225
moyenne pondérée       0.84      0.85      0.84       225
ASC =  0.7612085769980507

Il semble qu'il atteigne une AUC de 76 %, avec une précision de 85 %, mais le rappel pour les patients admis en réanimation n'est que de 59 % - il semble y avoir trop de faux négatifs. Ce n'est certainement pas l'idéal - nous ne voulons pas passer à côté des risques réels de l'USI pour le dossier d'un patient. Toutes les tâches suivantes seront donc axées sur l'objectif sur la manière d'augmenter le taux de rappel, en réduisant le FN, avec une précision globale quelque peu équilibrée, nous l'espérons.

Dans les sections précédentes, nous avons mentionné des données déséquilibrées, de sorte que notre premier réflexe serait de stratifier l'ensemble de test et de le MODIFIER pour obtenir un ensemble de données plus équilibré.

#stratifier les données de test, afin de s'assurer que les données de train et de test ont le même ratio de 1:0
X3_train,X3_test,y3_train,y3_test = train_test_split(X2,y2,test_size=225/1925,random_state=42, stratify = y2, shuffle = Vrai) <span> </span>
# former et prédire
LR.fit(X3_train,y3_train)
y3_hat = LR.predict(X3_test)

#MODIFIER les données pour faire de l'UCI 1:0 une distribution équilibrée
from imblearn.over_sampling import SMOTE sm = SMOTE(random_state = 42)
X_train_res, y_train_res = sm.fit_sample(X3_train,y3_train.ravel())
LR.fit(X_train_res, y_train_res)
y_res_hat = LR.predict(X3_test)

#recréer la matrice de confusion, etc.
confusion_matrix3 = pd.crosstab(y3_test, y_res_hat, rownames=['Actual'], colnames=['Predicted'])
sns.heatmap(confusion_matrix3, annot=Vrai, fmt = 'g', cmap="YlOrBr")
print("LABEL ENCODING + STRATIFY")
print(classification_report(y3_test, y3_hat))
print("ASC = ",roc_auc_score(y3_test, y3_hat),'\n\n')
print("SMOTE")
print(classification_report(y3_test, y_res_hat))
print("ASC = ",roc_auc_score(y3_test, y_res_hat))
y_res_hat_probs = LR.predict_proba(X3_test)
y_res_hat_probs = y_res_hat_probs[:, 1]
fpr_res, tpr_res, _ = roc_curve(y3_test, y_res_hat_probs) plt.figure(figsize=(10,10))

#Et tracez la courbe ROC comme précédemment.

LABEL ENCODING + STRATIFY (CODAGE D'ÉTIQUETTES + STRATIFICATION)
              précision    rappel  f1 score   support
           0       0.87      0.99      0.92       165
           1       0.95      0.58      0.72        60
    exactitude                           0.88       225
   moyenne macro       0.91      0.79      0.82       225
moyenne pondérée       0.89      0.88      0.87       225
ASC =  0.7856060606060606
SMOTE
              précision    rappel  f1 score   support
           0       0.91      0.88      0.89       165
           1       0.69      0.75      0.72        60
    exactitude                           0.84       225
   moyenne macro       0.80      0.81      0.81       225
moyenne pondérée       0.85      0.84      0.85       225
ASC =  0.8143939393939393

Les traitements des données par STRATIFY (stratification) et SMOT (optimisation) semblent donc améliorer le rappel, qui passe de 0,59 à 0,75, avec une précision globale de 0,84. 

Maintenant que le traitement des données est largement effectué comme d'habitude pour le ML traditionnel, nous voulons savoir quel pourrait être le(s) meilleur(s) modèle(s) dans ce cas ; peuvent-ils faire mieux, et pouvons-nous alors essayer une comparaison globale relative ?

Comparaison de l'entraînement à la course de différents modèles: 

Poursuivons l'évaluation de quelques algorithmes de ML couramment utilisés, et générons un tableau de bord de résultats à comparer à l'aide de diagrammes en boîte à moustaches :

# comparer les algorithmes
from matplotlib import pyplot
from sklearn.model_selection import train_test_split
from sklearn.model_selection import cross_val_score
from sklearn.model_selection import StratifiedKFold
from sklearn.linear_model import LogisticRegression
from sklearn.tree import DecisionTreeClassifier
from sklearn.neighbors import KNeighborsClassifier
from sklearn.discriminant_analysis import LinearDiscriminantAnalysis
from sklearn.naive_bayes import GaussianNB
from sklearn.svm import SVC
#Importer un modèle arborescent aléatoire
from sklearn.ensemble import RandomForestClassifier
from xgboost import XGBClassifier# Répertorier les algorithmes ensemble
models = []
models.append(('LR', <strong>LogisticRegression</strong>(solver='liblinear', multi_class='ovr')))
models.append(('LDA', LinearDiscriminantAnalysis()))
models.append(('KNN', <strong>KNeighborsClassifier</strong>()))
models.append(('CART', <strong>DecisionTreeClassifier</strong>()))
models.append(('NB', <strong>GaussianNB</strong>()))
models.append(('SVM', <strong>SVC</strong>(gamma='auto')))
models.append(('RF', <strong>RandomForestClassifier</strong>(n_estimators=100)))
models.append(('XGB', <strong>XGBClassifier</strong>())) #clf = XGBClassifier()
# évaluer chaque modèle à tour de rôle
résultats = []
noms = []
pour nom, modèler dans modèles :
    kfold = StratifiedKFold(n_splits=10, random_state=1)
    cv_results = cross_val_score(model, X_train_res, y_train_res, cv=kfold, scoring='f1')  ## exactitude, précision, rappel
    results.append(cv_results)
    names.append(name)
    print('%s: %f (%f)' % (name, cv_results.mean(), cv_results.std()))# Comparer les performances de tous les modèles. Question - Souhaitez-vous voir un article intégré sur le site ?
pyplot.figure(4, figsize=(12, 8))
pyplot.boxplot(résultats, étiquettes=noms)
pyplot.title('Comparaison des algorithmes')
pyplot.show()

LR: 0.805390 (0.021905) LDA: 0.803804 (0.027671) KNN: 0.841824 (0.032945) CART: 0.845596 (0.053828)
NB: 0.622540 (0.060390) SVM: 0.793754 (0.023050) RF: 0.896222 (0.033732) XGB: 0.907529 (0.040693)

Ce qui précède semble montrer que le classificateur XGB et le classificateur de la forêt aléatoire "Random Forest" obtiendraient un meilleur score F1 que les autres modèles.

Comparons leurs résultats réels sur le même ensemble de données de test normalisées :

Temps d'importation
from pandas import read_csv
from sklearn.model_selection import train_test_split
from sklearn.metrics import classification_report
from sklearn.metrics import confusion_matrix
from sklearn.metrics import accuracy_score
from sklearn.svm import SVCpour nom, modèler dans modèles :
    print(name + ':\n\r')
    start = time.clock()
    model.fit(X_train_res, y_train_res)
    print("Temps de formation pour ", model, " ", time.clock() - start)
    predictions = model.predict(X3_test) #(X_validation)
    # Evaluate predictions
    print(accuracy_score(y3_test, predictions))  # Y_validation
    print(confusion_matrix(y3_test, predictions))
    print(classification_report(y3_test, predictions))

LR:
Temps de formation pour  LogisticRegression(multi_class='ovr', solver='liblinear')   0.02814499999999498
0.8444444444444444
[[145  20]
 [ 15  45]]
              précision    rappel  f1 score   support
           0       0.91      0.88      0.89       165
           1       0.69      0.75      0.72        60
    exactitude                           0.84       225
   moyenne macro       0.80      0.81      0.81       225
moyenne pondérée       0.85      0.84      0.85       225

LDA:
Temps de formation pour  LinearDiscriminantAnalysis()   0.2280070000000194
0.8488888888888889
[[147  18]
 [ 16  44]]
              précision    rappel  f1 score   support
           0       0.90      0.89      0.90       165
           1       0.71      0.73      0.72        60
    exactitude                           0.85       225
   moyenne macro       0.81      0.81      0.81       225
moyenne pondérée       0.85      0.85      0.85       225

KNN:
Temps de formation pour  KNeighborsClassifier()   0.13023699999999394
0.8355555555555556
[[145  20]
 [ 17  43]]
              précision    rappel  f1 score   support
           0       0.90      0.88      0.89       165
           1       0.68      0.72      0.70        60
    exactitude                           0.84       225
   moyenne macro       0.79      0.80      0.79       225
moyenne pondérée       0.84      0.84      0.84       225

CART:
Temps de formation pour  DecisionTreeClassifier()   0.32616000000001577
0.8266666666666667
[[147  18]
 [ 21  39]]
              précision    rappel  f1 score   support
           0       0.88      0.89      0.88       165
           1       0.68      0.65      0.67        60
    exactitude                           0.83       225
   moyenne macro       0.78      0.77      0.77       225
moyenne pondérée       0.82      0.83      0.83       225

NB:
Temps de formation pour  GaussianNB()   0.0034229999999979555
0.8355555555555556
[[154  11]
 [ 26  34]]
              précision    rappel  f1 score   support
           0       0.86      0.93      0.89       165
           1       0.76      0.57      0.65        60
    exactitude                           0.84       225
   moyenne macro       0.81      0.75      0.77       225
moyenne pondérée       0.83      0.84      0.83       225

SVM:
Temps de formation pour  SVC(gamma='auto')   0.3596520000000112
0.8977777777777778
[[157   8]
 [ 15  45]]
              précision    rappel  f1 score   support
           0       0.91      0.95      0.93       165
           1       0.85      0.75      0.80        60
    exactitude                           0.90       225
   moyenne macro       0.88      0.85      0.86       225
moyenne pondérée       0.90      0.90      0.90       225

RF:
Temps de formation pour  RandomForestClassifier()   0.50123099999999
0.9066666666666666
[[158   7]
 [ 14  46]]
              précision    rappel  f1 score   support
           0       0.92      0.96      0.94       165
           1       0.87      0.77      0.81        60
    exactitude                           0.91       225
   moyenne macro       0.89      0.86      0.88       225
moyenne pondérée       0.91      0.91      0.90       225

XGB:
Temps de formation pour  XGBClassifier(base_score=0.5, booster='gbtree', colsample_bylevel=1,
              colsample_bynode=1, colsample_bytree=1, gamma=0, gpu_id=-1,
              importance_type='gain', interaction_constraints='',
              learning_rate=0.300000012, max_delta_step=0, max_depth=6,
              min_child_weight=1, missing=nan, monotone_constraints='()',
              n_estimators=100, n_jobs=0, num_parallel_tree=1, random_state=0,
              reg_alpha=0, reg_lambda=1, scale_pos_weight=1, subsample=1,
              tree_method='exact', validate_parameters=1, verbosity=Aucun)   1.649520999999993
0.8844444444444445
[[155  10]
 [ 16  44]]
              précision    rappel  f1 score   support
           0       0.91      0.94      0.92       165
           1       0.81      0.73      0.77        60
    exactitude                           0.88       225
   moyenne macro       0.86      0.84      0.85       225
moyenne pondérée       0.88      0.88      0.88       225

Le résultat semble être que RF est en fait meilleur que XGB. Cela pourrait signifier que XGB est peut-être un peu plus surajouté d'une manière ou d'une autre. Le résultat de RFC est également légèrement meilleur que celui de LR.

Exécuter le modèle sélectionné en poursuivant "Ajustement des paramètres via la recherche par quadrillage"

Supposons maintenant que nous ayons choisi le modèle de classificateur de la forêt aléatoire "Random Forest Classifier". Nous pouvons effectuer une nouvelle recherche sur la grille de ce modèle pour voir s'il est possible d'obtenir des résultats un peu plus performants. 

Rappelez-vous que notre objectif est toujours d'optimiser le rappel dans ce cas, en minimisant le nombre de faux négatifs concernant les risques possibles pour l'USI lors de la rencontre avec le patient, nous utiliserons donc 'recall_score' pour réajuster le quadrillage ci-dessous. Une fois de plus, la validation croisée 10 fois sera utilisée comme d'habitude, étant donné que notre ensemble de test ci-dessus a toujours été fixé à environ 12 % de ces 2915 enregistrements.

from sklearn.model_selection import GridSearchCV
# Créer la grille de paramètres sur la base des résultats de la recherche aléatoire

param_grid = {'bootstrap': [Vrai],
 'ccp_alpha': [0.0],
 'class_weight': [Aucun],
 'criterion': ['gini', 'entropy'],
 'max_depth': [Aucun],
 'max_features': ['auto', 'log2'],             
 'max_leaf_nodes': [Aucun],
 'max_samples': [Aucun],
 'min_impurity_decrease': [0.0],
 'min_impurity_split': [Aucun],
 'min_samples_leaf': [1, 2, 4],
 'min_samples_split': [2, 4],
 'min_weight_fraction_leaf': [0.0],
 'n_estimators': [100, 125],
 #'n_jobs': [Aucun],
 'oob_score': [False],
 'random_state': [Aucun],
 #'verbose': 0,
 'warm_start': [False]
}#Ajuster par matrice de confusion
from sklearn.metrics import roc_curve, précision_recall_curve, auc, make_scorer, recall_score, accuracy_score, précision_score, confusion_matrix
scorers = {
    'recall_score': make_scorer(recall_score),
    'précision_score': make_scorer(précision_score),
    'accuracy_score': make_scorer(accuracy_score)
}# Créer un modèle de base
rfc = RandomForestClassifier()
# Instancier le modèle de quadrillage
grid_search = GridSearchCV(estimator = rfc, param_grid = param_grid,
                           scoring=scorers, refit='recall_score',
                           cv = 10, n_jobs = -1, verbose = 2)train_features = X_train_resgrid_search.fit(train_features, train_labels)
rf_best_grid = grid_search.best_estimator_rf_best_grid.fit(train_features, train_labels)
rf_predictions = rf_best_grid.predict(X3_test)
print(accuracy_score(y3_test, rf_predictions))  
print(confusion_matrix(y3_test, rf_predictions))
print(classification_report(y3_test, rf_predictions))

0.92
[[ 46  14]
 [  4 161]]
              précision    rappel f1 score   support
           0       0.92      0.77      0.84        60
           1       0.92      0.98      0.95       165
    exactitude                           0.92       225
   moyenne macro       0.92      0.87      0.89       225
moyenne pondérée       0.92      0.92      0.92       225

Le résultat a montré qu'un quadrillage a permis d'augmenter légèrement la précision globale, tout en maintenant le FN au même niveau. 

Traçons également les comparaisons avec l'ASC :

confusion_matrix4 = pd.crosstab(y3_test, rf_predictions, rownames=['Actual'], colnames=['Predicted'])
sns.heatmap(confusion_matrix4, annot=Vrai, fmt = 'g', cmap="YlOrBr")print("LABEL ENCODING + STRATIFY")
print(classification_report(y3_test, 1-y3_hat))
print("ASC = ",roc_auc_score(y3_test, 1-y3_hat),'\n\n')print("SMOTE")
print(classification_report(y3_test, 1-y_res_hat))
print("ASC = ",roc_auc_score(y3_test, 1-y_res_hat), '\n\n')print("SMOTE + LBG Selected Weights + RF Grid Search")
print(classification_report(y3_test, rf_predictions))
print("ASC = ",roc_auc_score(y3_test, rf_predictions), '\n\n\n')y_res_hat_probs = LR.predict_proba(X3_test)
y_res_hat_probs = y_res_hat_probs[:, 1]predictions_rf_probs = rf_best_grid.predict_proba(X3_test) #(X_validation)
predictions_rf_probs = predictions_rf_probs[:, 1]fpr_res, tpr_res, _ = roc_curve(y3_test, 1-y_res_hat_probs)
fpr_rf_res, tpr_rf_res, _ = roc_curve(y3_test, predictions_rf_probs)plt.figure(figsize=(10,10))
plt.plot([0, 1], [0, 1], 'k--')
plt.plot(fpr, tpr, label="Base")
plt.plot(fpr2,tpr2,label="Label Encoded")
plt.plot(fpr3,tpr3,label="Stratify")
plt.plot(fpr_res,tpr_res,label="SMOTE")
plt.plot(fpr_rf_res,tpr_rf_res,label="SMOTE + RF GRID")
plt.xlabel('False positive rate')
plt.ylabel('Vrai positive rate')
plt.title('ROC curve')
plt.legend(loc="best")
plt.show()

CODAGE D'ÉTIQUETTES + STRATIFICATION
              précision    rappel  f1 score   support
           0       0.95      0.58      0.72        60
           1       0.87      0.99      0.92       165
    exactitude                           0.88       225
   moyenne macro       0.91      0.79      0.82       225
moyenne pondérée       0.89      0.88      0.87       225
ASC =  0.7856060606060606

MODIFICATION
              précision    rappel  f1 score   support
           0       0.69      0.75      0.72        60
           1       0.91      0.88      0.89       165
    exactitude                           0.84       225
   moyenne macro       0.80      0.81      0.81       225
moyenne pondérée       0.85      0.84      0.85       225
ASC =  0.8143939393939394

MODIFICATION + LBG Pondérations sélectionnées + Quadrillage RF
              précision    rappel  f1 score   support
           0       0.92      0.77      0.84        60
           1       0.92      0.98      0.95       165
    exactitude                           0.92       225
   moyenne macro       0.92      0.87      0.89       225
moyenne pondérée       0.92      0.92      0.92       225
ASC =  0.8712121212121211

Le résultat a montré qu'après des comparaisons d'algorithmes et un quadrillage suivant, nous avons réussi à faire passer l'ASC de 78 % à 87 %, avec une précision globale de 92 % et un rappel de 77 %.

Récapitulatif de l'approche "ML traditionnelle"

Qu'en est-il réellement de ce résultat ? Il est correct pour un processus manuel de base avec des algorithmes ML traditionnels. Comment ce résultat apparaît-il dans les tableaux de compétition Kaggle ? Eh bien, il ne figurerait pas dans le tableau de classement. J'ai passé le jeu de données brutes par le service AutoML actuel de DataRobot, le meilleur résultat serait un ASC équivalent de ~90+% (à confirmer avec des données similaires) avec le modèle " Classificateur arborescent XGB avec fonctions d'apprentissage non supervisé " (XGB Trees Classifier with Unsupervised Learning Features), sur une comparaison des 43 meilleurs modèles. C'est peut-être le genre de modèle de base que nous devrions utiliser si nous voulons vraiment être compétitifs sur Kaggle. Je joindrai également la liste des meilleurs résultats par rapport aux modèles dans le github. Finalement, pour les cas réels spécifiques aux sites de soins, j'ai le sentiment que nous devons également intégrer un certain degré d'approches d'apprentissage profond personnalisées, comme mentionné dans la section "Données et tâches" de ce billet. Bien sûr, dans les cas réels, l'endroit où collecter des colonnes de données de qualité pourrait également être une question initiale.

L'approche IntegratedML?

Ce qui précède est un processus de ML dit traditionnel, qui comprend normalement l'EDA des données, l'ingénierie des caractéristiques, la sélection des caractéristiques, la sélection des modèles, et l'optimisation des performances par la quadrillage, etc. C'est l'approche la plus simple à laquelle j'ai pu penser jusqu'à présent pour cette tâche, et nous n'avons même pas encore abordé le déploiement du modèle et les cycles de vie de la gestion des services - nous le ferons dans le prochain article, en examinant comment nous pourrions tirer parti de Flask/FastAPI/IRIS et déployer ce modèle de ML de base dans une pile de services de démonstration de la radiographie de Covid-19.

IRIS dispose désormais d'IntegratedML, qui est une enveloppe SQL élégante d'options puissantes d'AutoMLs. Dans la deuxième partie, nous verrons comment accomplir la tâche susmentionnée dans le cadre d'un processus simplifié, de sorte que nous n'aurons plus à nous préoccuper de la sélection des caractéristiques, de la sélection des modèles, de l'optimisation des performances, etc.

Jusqu'ici, cet article pourrait être trop long pour une note de 10 minutes visant à intégrer rapidement les mêmes données, c'est pourquoi je le déplace vers l'article suivant, partie II. 

Salut les développeurs,

Nous aimerions vous inviter à participer à notre prochain concours dédié à la création des solutions d'IA/ML qui utilisent Cloud SQL pour travailler avec les données :

🏆 Concours InterSystems IRIS Cloud SQL et IntegratedML 🏆

Durée: du 3 avril au 23 avril 2023

Prix: $13,500!

Salut la Communauté !

Voici un deuxième article de la série et celui-ci est dédié à SQL. Donc, si vous voulez en savoir plus sur l'utilisation du modèle relationnel dans IRIS, consultez les articles mentionnés.

Je vous présente mon nouveau projet, qui est irissqlcli, REPL (Read-Eval-Print Loop) pour InterSystems IRIS SQL

• Mise en évidence de la syntaxe
• Suggestions (tableaux, fonctions)
• Plus de 20 formats de sortie
• Support de stdin
• Sortie vers des fichiers 

L'installez avec pip

pip install irissqlcli

Ou lancez avec docker

docker run -it caretdev/irissqlcli irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER

Connection à IRIS

$ irissqlcli iris://_SYSTEM@localhost:1972/USER -W
Password for _SYSTEM:
Server:  InterSystems IRIS Version 2022.3.0.606 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select $ZVERSION
+---------------------------------------------------------------------------------------------------------+
| Expression_1                                                                                            |
+---------------------------------------------------------------------------------------------------------+
| IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.3 (Build 606U) Mon Jan 30202309:05:12 EST |
+---------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.063s
[SQL]_SYSTEM@localhost:USER> help
+----------+-------------------+------------------------------------------------------------+
| Commande  | Raccourci          | Description                                                |
+----------+-------------------+------------------------------------------------------------+
| .exit    | \q                | Sortie.                                                      |
| .mode    | \T                | Modifier le format de tableau utilisé pour les résultats.            |
| .once    | \o [-o] filename  | Ajout du résultat suivant à un fichier de sortie (écraser en utilisant -o). |
| .schemas | \ds               | Liste des schémas.                                             |
| .tables  | \dt [schema]      | Liste des tableaux.                                               |
| \e       | \e                | Commande d'édition avec éditeur (utilise $EDITOR).                   |
| help     | \?                | Montre cette utilité.                                            |
| nopager  | \n                | Désactiver le pager, imprimer vers stdout.                            |
| notee    | notee             | Arrête l'écriture des résultats dans un fichier de sortie.                    |
| pager    | \P [command]      | Definition du PAGER. Impression des résultats de la requête via PAGER.              |
| prompt   | \R                | Modification du format de l'invite.                                      |
| quit     | \q                | Quit.                                                      |
| tee      | tee [-o] filename | Ajout de tous les résultats à un fichier de sortie (écraser en utilisant -o). |
+----------+-------------------+------------------------------------------------------------+
Time: 0.012s
[SQL]_SYSTEM@localhost:USER>

$ irissqlcli --help
Usage: irissqlcli [OPTIONS] [URI] [NOM D'UTILISATEUR]

Options:
  -h, --host TEXT         Adresse hôte de l'instance IRIS.
  -p, --port INTEGER      Numéro de port sur lequel l'instance IRIS està l'écoute.
  -U, --username TEXT     Nom d'utilisateur pour se connecter à l'instance IRIS.
  -u, --user TEXT         Nom d'utilisateur pour se connecter à l'instance IRIS.
  -W, --password          Invite de mot de passe forcée.
  -v, --version           Version de irissqlcli.
  -n, --nspace TEXT       nom de l'espace de nom auquel se connecter.
  -q, --quiet             Mode silencieux, saut de l'intro au démarrage etau revoir à la
                          sortie.
  -l, --logfile FILENAME  Enregistrez chaque requête etses résultats dans un fichier.
  --irissqlclirc FILE     L'emplacement du fichier irissqlclirc.
  --auto-vertical-output  Passage automatique en mode de sortie verticale sile résultat est plus large que la largeur du terminal. résultat estplus large que la largeur du terminal.
  --row-limit INTEGER     Définissez le seuil pour l'invite de limite de rangée de . Utilisez 0 pour désactiver
                          l'invite.
  -t, --table             Affichez la sortie du lot au format tableau .
  --csv                   Affichez la sortie du lot in au format CSV.
  --warn / --no-warn      Avertissement avant d'exécuter une requête destructive.
  -e, --execute TEXT      Exécutez la commande etquitter.
  --help                  Affichage de ce message et sortie.

ou en Python Embedded (nécessite de %Service_CallIn activé)

$ irissqlcli iris+emb:///USER
Server:  IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.2 (Build 368U) Fri Oct 21202216:39:41 EDT
Version: 0.1.0
[SQL]irisowner@/usr/irissys/:USER>

L'application supporte stdin, ce qui vous permet d'envoyer un fichier SQL avec un tas de requêtes SQL et de commandes irissqcli. Par exemple, cette commande produira 3 fichiers dans différents formats (parmi plus de 20 formats disponibles)

$ cat <select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

.mode latex;
tee -o test.tex;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

.mode html;
tee -o test.html;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;

EOF

De plus, il est possible d'exécuter un terminal web avec docker

docker run -d --name irissqlcli \
  --restart always \
  -p 7681:7681\
  caretdev/irissqlcli-web irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER

http://localhost:7681/

Et avec docker-compose

version: '3'
services:
  iris:
    image: intersystemsdc/iris-community
    ports:
      - 1972
      - 52773
    command:
      - -a
      - '##class(Security.Users).UnExpireUserPasswords("*")'
  cli:
    image: caretdev/irissqlcli-web
    ports:
      - 7681:7681
    environment:
      - IRIS_HOSTNAME:iris
      - IRIS_PORT=1972
      - IRIS_NAMESPACE=USER
      - IRIS_USERNAME=_SYSTEM
      - IRIS_PASSWORD=SYS

Avec InterSystems IRIS 2022.2, nous avons introduit le stockage en colonne comme une nouvelle option pour la persistance de vos tables IRIS SQL qui peut booster vos requêtes analytiques d'un ordre de grandeur. La capacité est marquée comme expérimentale dans les versions 2022.2 et 2022.3, mais passera à une capacité de production entièrement prise en charge dans la prochaine version 2023.1.

La documentation du produit et cette vidéo d'introduction, décrivent déjà les différences entre le stockage en ligne, toujours la valeur par défaut sur IRIS et utilisé dans l'ensemble de notre clientèle, et le stockage en table en colonnes et fournissent des conseils de haut niveau sur le choix de la disposition de stockage appropriée pour votre cas d'utilisation. Dans cet article, nous développerons ce sujet et partagerons quelques recommandations basées sur les principes de modélisation des pratiques de l'industrie, les tests internes et les commentaires des participants au Early Access Program. 

Comme vous vous en souvenez peut-être du Global Summit 2022 ou du webinaire de lancement 2022.2, nous lançons une nouvelle fonctionnalité passionnante à inclure dans vos solutions d'analyse sur InterSystems IRIS. Le stockage en colonnes introduit une autre façon de stocker vos données de table SQL qui offre une accélération d'ordre de grandeur pour les requêtes analytiques. Publié pour la première fois en tant que fonctionnalité expérimentale en 2022.2, le dernier Developer Preview 2022.3 comprend un tas de mises à jour qui, selon nous, valaient la peine d'être publiées ici.

J'essaie d'obtenir un compte de type de message spécifique avec une entrée spécifique et j'ai pensé que je pourrais construire la requête dans Message Viewer mais cela ne fournit pas de comptes (pour autant que je sache). Ainsi, lorsque je prends le SQL à partir de "Show Query", il omet les critères de segment comme le montre le code ci-dessous.

J'ai attaché les critères qui ont été exclus. Est-ce possible ?

Merci

Je veux faire une requête dans la base de données du Caché pour trouver les messages où un segment HL7 spécifique est égal à une valeur spécifique. Caché dispose-t-il d'une fonction d'interrogation de type "pipe to XML" ou "segment HL7" ?

Bonjour à tous, c'est avec grand plaisir que je vous annonce la V2 de mon application 'Contest-FHIR'.

Dans cette nouvelle version, j'ai utilisé de nouveaux outils et techniques que j'ai découverts lors de l'EUROPEAN HEALTHCARE HACKATHON auquel j'ai été invité par InterSystems en tant qu'invité et mentor pour présenter les multiples projets que j'ai réalisés lors de mon stage en avril 2022.

Aujourd'hui je vous présente la V2 de mon application, elle peut maintenant transformer un fichier CSV en FHIR en SQL en JUPYTER notebook.

C'est pour moi un grand pas en avant dans les technologies d'InterSystems et je pense que cet outil sera utilisé pour de nombreux autres usages et cas.

Visitez mon GitHub pour plus d'informations, et pour ceux d'entre vous qui ne veulent pas perdre de temps, consultez la partie 5. Walkthrough de mon GitHub.

Nous essayons de trouver la source des messages abandonnés et avons remarqué que nous sommes incapables d'interroger EnsLib.HL7.Message avec des clauses WHERE ou ORDER BY dans notre instruction SQL.

Je sais que EnsLib.HL7.Message est un tableau système, mais existe-t-il un moyen d'ajouter des index supplémentaires à ce tableau pour que la requête s'exécute mieux/plus rapidement sans affecter le système ?

Bonjour la communauté,

Nous sommes heureux d'annoncer que InterSystems IRIS, IRIS for Health, HealthShare Health Connect et InterSystems IRIS Studio 2022.2 sont maintenant disponibles !

Et pour discuter de toutes les fonctionnalités nouvelles et améliorées de celui-ci, nous aimerions vous inviter à notre webinaire Quoi de neuf dans InterSystems IRIS 2022.2.

Cette publication est le résultat direct d'une collaboration avec un client d'InterSystems qui est venu me consulter pour le problème suivant :

SELECT COUNT(*) FROM MyCustomTable

Cela prend 0,005 secondes, pour un total de 2300 lignes.  Cependant :

SELECT * FROM MyCustomTable

Prenait des minutes.  La raison en est subtile et suffisamment intéressante pour que j'écrive un article à ce sujet.  Cet article est long, mais si vous faites défiler la page jusqu'en bas, je vous donnerai un résumé rapide. Si vous êtes arrivé jusqu'ici et que vous pensez en avoir lu assez, faites défiler la page jusqu'à la fin pour connaître l'essentiel.  Vérifiez la phrase en gras.

Lors de la création de vos classes, il faut tenir compte de la question du stockage.  Comme beaucoup d'entre vous le savent, toutes les données dans Caché sont stockées dans des Globales.  

<Digression> 

Si vous ne le savez pas, je pense que cet article sera un peu trop long.  Je vous recommande de consulter un excellent tutoriel dans notre documentation :

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=TCOS

Si vous n'avez jamais utilisé Caché/Ensemble/HealthShare, le tutoriel ci-dessus est très utile, et même si vous l'avez fait, il vaut la peine de le consulter !  

Maintenant, comme toutes les données sont stockées dans des globales, il est important de comprendre comment les définitions de vos classes correspondent aux globales.  Construisons une application ensemble !  Nous allons examiner certains pièges courants et discuter de la façon dont le développement de vos classes affecte vos stratégies de stockage, avec un regard particulier sur les performances SQL.  

Imaginons que nous soyons le Bureau du recensement des États-Unis et que nous voulions disposer d'une base de données pour stocker les informations concernant tous les habitants des États-Unis.  Nous construisons donc une classe de la manière suivante :

Class USA.Person extends %Persistent
{
 Property Name as %String;
 Property SSN as %String;
 Property Address as %String;
 Property DateOfBirth as %Date;
}

SSN est l'abréviation de "Social Security Number" (numéro de sécurité sociale) qui, bien qu'il n'ait pas été conçu à l'origine pour être un numéro d'identification des personnes, est leur numéro d'identification de facto.  Cependant, comme nous sommes traditionalistes, nous ne l'utiliserons pas pour l'identification.  Cela dit, nous tenons à ce que cet élément soit indexé, car c'est un excellent moyen de rechercher une personne.  Nous savons que nous devrons parfois rechercher des personnes par le nom, c'est pourquoi nous voulons également un index des noms.  Et parce que notre patron aime ses rapports basés sur des tranches d'âge, nous pensons qu'un index des dates de naissance pourrait également être utile.  Ajoutons-les donc à notre classe

Class USA.Person extends %Persistent
{
 Property Name as %String;
 Property SSN as %String;
 Property Address as %String;
 Property DateOfBirth as %Date;

 Index NameIDX On Name;
 Index SSNIDX On SSN [Unique];
 Index DOBIDX on DateOfBirth;

}

Très bien.  Alors ajoutons une ligne et voyons à quoi ressemblent nos globales.  Notre instruction INSERT est la suivante :

INSERT INTO USA.Person (Name,SSN,Address,DateOfBirth) VALUES
   ('Baxter, Kyle','111-11-1111','1 Memorial Drive, Cambridge, MA 02142','1985-07-20')

Et la globale:

USER>zw ^USA.PersonD
^USA.PersonD=1
^USA.PersonD(1)=$lb("","Baxter, Kyle","111-11-1111","1 Memorial Drive, Cambridge, MA 02142",52796)

Le stockage par défaut d'une classe stocke vos données dans ^Package.ClassD.  Si le nom de la classe est trop long, il peut être haché, et vous pouvez le trouver dans la définition de stockage au bas de votre définition de classe.  Les index, à quoi ressemblent-ils ?

USER>zw ^USA.PersonI                      
^USA.PersonI("DOBIDX",52796,1)=""
^USA.PersonI("NameIDX"," BAXTER, KYLE",1)=""
^USA.PersonI("SSNIDX"," 111-11-1111",1)=""

Excellent, notre stockage est plutôt bon pour l'instant.  Donc on ajoute nos 320 millions de personnes et on peut trouver des gens assez rapidement.  Mais maintenant nous avons un problème, car nous voulons traiter le président et tous les ex-présidents avec une considération spéciale.  Nous ajoutons donc une classe spéciale pour le président :

Class USA.President extends USA.Person
{
Property PresNumber as %Integer;

Index PresNumberIDX on PresNumber;
}

Bien.  En raison de l'héritage, nous récupérons toutes les propriétés de USA.Person, et nous en ajoutons une pour nous permettre de savoir quel numéro de président il était.  Puisque je veux faire un peu de politique, je vais INSÉRER notre PROCHAIN président.  Voici l'instruction :

INSERT INTO USA.President (Name,SSN,DateOfBirth,Address,PresNumber) VALUES ('McDonald,Ronald','221-18-7518','01-01-1963','1600 Pennsylvania Ave NW, Washington, DC 20006',45)

Note : Son numéro de sécurité sociale s'écrit 'Burger'.  Désolé si c'est le vôtre.

Alors c'est génial !  Regardons votre Globale du Président :

USER>zw ^USA.PresidentD

Pas de données !  Et c'est là que nous arrivons à l'essentiel de cet article.  Parce que nous avons décidé d'hériter de USA.Person FIRST, nous avons hérité non seulement de ses propriétés et index, mais aussi de son stockage !  Donc pour localiser le président McDonald, nous devons regarder dans ^USA.PersonD.  Et nous pouvons voir ce qui suit :

^USA.PersonD(2)=$lb("~USA.President~","McDonald,Ronald","221-18-7518","1600 Pennsylvania Ave NW, Washington, DC 20006",44560)
^USA.PersonD(2,"President")=$lb(45&)

Deux choses à noter ici.  La première est que nous pouvons voir que le nœud (2) possède toutes les informations déjà stockées dans USA.Person.  Alors que le noeud (2, "President") ne contient que les informations spécifiques à la classe USA.President.  

Qu'est-ce que cela signifie en pratique ?  Eh bien, si nous voulons faire une opération de type : SELECT * FROM USA.President, nous aurons BESOIN de parcourir l'ensemble du tableau des personnes.  Si nous pensons que le tableau des personnes contient 320 000 000 lignes et que le tableau des présidents en contient 45, alors nous devons faire plus de 320 000 045 références globales pour extraire 45 lignes !  En effet, si l'on regarde le plan de requête :

• Lire la carte maîtresse USA.President.IDKEY, en bouclant sur ID.
• Pour chaque ligne:
•  Résultat de la ligne.

Nous observons ce que nous attendons.  Cependant, nous avons déjà vu que cela signifie qu'il faut nécessairement regarder dans la globale ^USA.PersonD.  Donc, cela va être une référence globale de 320 000 000+ car nous devons tester CHAQUE ^USA.PersonD pour vérifier s'il y a des données dans ^USA.PersonD(i, "Président") puisque nous ne savons pas quelles personnes seront présidents.  Eh bien, c'est mauvais ! Ce n'est pas du tout ce que nous voulions !  Que pouvons-nous faire ?  Eh bien, nous avons deux options :

Option 1

Ajouter un index d'éxtent.  Si nous faisons cela, nous obtenons une liste d'identifiants qui nous permet de savoir quelles personnes sont des présidents et nous pouvons utiliser cette information pour lire des nœuds spécifiques de la globale ^USA.Person.  Comme je dispose d'un stockage par défaut, je peux utiliser un index bitmap, ce qui rendra l'opération encore plus rapide.  Nous ajoutons l'index comme suit :

Index Extent [Type=Bitmap, Extent];

Et quand nous regardons notre plan de requête pour SELECT * FROM USA.President nous pouvons voir :

• Lecture de l'extent du bitmap USA.President.Extent, en bouclant sur l'ID.

• Pour chaque ligne :

•  Lecture de la carte maîtresse USA.President.IDKEY, en utilisant la valeur idkey donnée.
   Résultat de la ligne.

Ah, maintenant ça va être sympa et rapide.  Une référence globale pour lire l'Extent et ensuite 45 autres pour les présidents.  C'est plutôt efficace.  

Les inconvénients ?  La connexion à ce tableau devient un peu plus compliquée et peut impliquer un plus grand nombre de tableaux temporaires que vous ne le souhaiteriez.  

Option 2

Changement de la définition de la classe en ::

Class USA.President extends (%Persistent, USA.Person)

En faisant de %Persistent la première classe étendue, USA.President aura sa propre définition de stockage.  Ainsi, les présidents seront stockés de la manière suivante :

USER>zw ^USA.PresidentD
^USA.PresidentD=1
^USA.PresidentD(1)=$lb("","McDonald,Ronald","221-18-7518","1600 Pennsylvania Ave NW, Washington, DC 20006",44560,45)

C'est donc une bonne chose, car choisir USA.President signifie simplement lire les 45 membres de cette globale.  C'est facile et agréable, et le design est clair.

Les inconvénients ?  Eh bien maintenant, les présidents ne sont PAS dans le tableau des personnes Person.  Donc si vous voulez des informations sur les présidents ET les non-présidents, vous devez faire SELECT ... FROM USA.Person UNION ALL SELECT ... FROM USA.President

Si vous avez arrêté de lire au début, recommencez ici !

Lors de la création d'un héritage, nous avons deux options

Option 1: L'héritage de la superclasse est le premier.  Cela permet de stocker les données dans la même globale que la superclasse.  Utile si vous voulez avoir toutes les informations ensemble, et vous pouvez atténuer les problèmes de performance dans la sous-classe en ayant un index extent.

Option 2: Héritage de %Persistent first.  Cela permet de stocker les données dans une nouvelle globale.  C'est utile si vous interrogez beaucoup la sous-classe, mais si vous voulez voir les données de la super-classe et de la sous-classe, vous devez utiliser une requête UNION.

Laquelle de ces solutions est la meilleure ?  Cela dépend de la façon dont vous allez utiliser votre application.  Si vous souhaitez effectuer un grand nombre de requêtes sur l'ensemble des données, vous opterez probablement pour la première approche.  En revanche, si vous ne pensez pas interroger les données dans leur ensemble, vous opterez probablement pour la seconde approche.  Les deux approches sont tout à fait acceptables, à condition de ne pas oublier l'index extent de l'option 1.

Des questions ? Des commentaires ? De longues pensées contradictoires ?  Laissez-les ci-dessous !

Les champs peuvent être obtenu à l'aide du schéma INFORMATION_SCHEMA.

INFORMATION_SCHEMA est un schéma système et n'apparaît pas dans le menu SQL du Management Portal par défaut.

La méthode d'affichage est la suivante.

1. Ouvrez le Management Portal → System Explorer → SQL
2. Cochez "System" sur le côté gauche du menu déroulant du schéma.
3. Sélectionnez INFORMATION_SCHEMA dans le menu déroulant du schéma.

Le SQL pour obtenir l'ID, le nom du champ (COLUMN_NAME), le type de données (DATA_TYPE) et la description (DESCRIPTION) pour la table spécifiée (Test.Person) est le suivant.

Class Query dans InterSystems IRIS (et Cache, Ensemble, HealthShare) est un outil utile qui sépare les requêtes SQL du code Object Script. En principe, cela fonctionne comme suit : supposons que vous souhaitiez utiliser la même requête SQL avec différents arguments à plusieurs endroits différents. Dans ce cas, vous pouvez éviter la duplication du code en déclarant le corps de la requête comme une Class Query, puis en appelant cette requête par son nom. Cette approche est également pratique pour les requêtes personnalisées, dans lesquelles la tâche consistant à obtenir la ligne suivante est définie par un développeur. Cela vous intéresse ? Alors lisez la suite !

Class queries de base

Plus simplement, les Class Queries de base vous permettent de représenter des requêtes SQL SELECT. L'optimiseur et le compilateur SQL les traitent comme des requêtes SQL standards, mais elles sont plus pratiques lorsqu'il s'agit de les exécuter à partir du contexte Caché Object Script. Ils sont déclarés en tant qu'éléments de requête Query dans les définitions de classe (similaires aux méthodes ou aux propriétés) de la manière suivante :

• Type: %SQLQuery
• Tous les arguments de votre requête SQL doivent être énumérés dans la liste des arguments
• Type de requête: SELECT
• Utiliser les deux-points pour accéder à chaque argument (similaire au SQL statique)
• Définissez le paramètre ROWSPEC qui contient des informations sur les noms et les types de données des résultats de sortie ainsi que l'ordre des champs
• (Facultatif) Définissez le paramètre CONTAINID qui correspond à l'ordre numérique si le champ contient l'ID. Si vous n'avez pas besoin de renvoyer l'ID, n'attribuez pas de valeur à CONTAINID
• (Facultatif) Définissez le paramètre COMPILEMODE qui correspond au paramètre similaire en SQL statique et spécifie quand l'expression SQL doit être compilée. Lorsque ce paramètre est défini sur IMMEDIATE (par défaut), la requête sera compilée en même temps que la classe. Lorsque ce paramètre a la valeur DYNAMIC, la requête sera compilée avant sa première exécution (similaire au SQL dynamique)
• (Facultatif) Définissez le paramètre SELECTMODE qui spécifie le format des résultats de la requête
• Ajoutez la propriété SqlProc, si vous voulez appeler cette requête comme une procédure SQL.
• Définissez la propriété SqlName, si vous souhaitez renommer la requête. Le nom par défaut d'une requête dans le contexte SQL est le suivant : PackageName.ClassName_QueryName
• Caché Studio fournit l'assistant intégré pour la création de Class Query

Exemple de définition de la classe Sample.Person avec la requête ByName qui renvoie tous les noms d'utilisateur qui commencent par une lettre spécifiée

Class Sample.Person Extends %Persistent
{
Property Name As %String;
Property DOB As %Date;
Property SSN As %String;
Query ByName(name As %String = "") As %SQLQuery
    (ROWSPEC="ID:%Integer,Name:%String,DOB:%Date,SSN:%String",
     CONTAINID = 1, SELECTMODE = "RUNTIME",
     COMPILEMODE = "IMMEDIATE") [ SqlName = SP_Sample_By_Name, SqlProc ]
{
SELECT ID, Name, DOB, SSN
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}
}

Vous pouvez appeler cette requête depuis Caché Object Script de la manière suivante : 

Set statement=##class(%SQL.Statement).%New()   
Set status=statement.%PrepareClassQuery("Sample.Person","ByName")   
If $$$ISERR(status) {
    Do $system.OBJ.DisplayError(status)
}   
Set resultset=statement.%Execute("A")   
While resultset.%Next() {
    Write !, resultset.%Get("Name")   
}

Vous pouvez également obtenir un ensemble de résultats en utilisant la méthode générée automatiquement queryNameFunc :

Set resultset = ##class(Sample.Person).ByNameFunc("A")    
While resultset.%Next() {
    Write !, resultset.%Get("Name")   
}

Cette requête peut également être appelée à partir du SQLcontext de ces deux manières :

Call Sample.SP_Sample_By_Name('A')
Select * from Sample.SP_Sample_By_Name('A')

Cette classe peut être trouvée dans l'espace de nom par défaut SAMPLES Caché. Et c'est tout pour les requêtes simples. Passons maintenant aux requêtes personnalisées

Class queries personnalisées

Bien que les Class Queries de base fonctionnent parfaitement dans la plupart des cas, il est parfois nécessaire d'exécuter un contrôle total sur le comportement des requêtes dans les applications, par exemple :

• Des critères de sélection sophistiqués. Puisque dans les requêtes personnalisées vous implémentez une méthode Caché Object Script qui renvoie la ligne suivante de façon autonome, ces critères peuvent être aussi sophistiqués que vous le souhaitez.
• Si les données sont accessibles uniquement via l'API dans un format que vous ne souhaitez pas utiliser
• Si les données sont stockées dans des globales (sans classes)
• Si vous avez besoin d'élever les droits afin d'accéder aux données
• Si vous devez appeler une API externe afin d'accéder à des données
• Si vous devez accéder au système de fichiers afin d'accéder aux données
• Vous devez effectuer des opérations supplémentaires avant d'exécuter la requête (par exemple, établir une connexion, vérifier les autorisations, etc.)

Alors, comment créer des requêtes de classes personnalisées ? Tout d'abord, vous devez définir 4 méthodes qui mettent en œuvre l'ensemble du flux de travail de votre requête, de l'initialisation à la destruction :

• queryName — fournit des informations sur une requête (similaire aux requêtes de classe de base)
• queryNameExecute — construit une requête
• queryNameFetch — obtient le résultat de la ligne suivante d'une requête
• queryNameClose — détruit une requête

Analysons maintenant ces méthodes plus en détail.

La méthode queryName

La méthode queryName représente des informations sur une requête

• Type: %Query
• Laissez le corps vide
• Définissez le paramètre ROWSPEC qui contient les informations sur les noms et les types de données des résultats de sortie ainsi que l'ordre des champs
• (Facultatif) Définissez le paramètre CONTAINID qui correspond à l'ordre numérique si le champ contient l'ID. Si vous ne renvoyez pas d'ID, n'attribuez pas de valeur à CONTAINID

Par exemple, créons la requête AllRecords (queryName = AllRecords, et la méthode est simplement appelée AllRecords) qui produira toutes les instances de la nouvelle classe persistante Utils.CustomQuery, une par une. Tout d'abord, créons une nouvelle classe persistante Utils.CustomQuery :

Class Utils.CustomQuery Extends (%Persistent, %Populate){
Property Prop1 As %String;
Property Prop2 As %Integer;
}

Maintenant, écrivons la requête AllRecords :

Query AllRecords() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllRecords, SqlProc ]
{
}

La méthode queryNameExecute
La méthode queryNameExecute initialise complètement une requête. La signature de cette méthode est la suivante :

ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status

où:

• qHandle est utilisé pour la communication avec les autres méthodes de l'implémentation de la requête
• Cette méthode doit mettre qHandle dans l'état qui sera ensuite transmis à la méthode queryNameFetch
• qHandle peut être défini comme OREF, une variable ou une variable multidimensionnelle
• Les args sont des paramètres supplémentaires transmis à la requête. Vous pouvez ajouter autant d'args que vous le souhaitez (ou ne pas les utiliser du tout)
• La méthode doit retourner le statut d'initialisation de la requête

Revenons à notre exemple. Vous pouvez itérer dans l'étendue de plusieurs façons (je décrirai plus loin les approches de travail de base pour les requêtes personnalisées), mais pour cet exemple, itérons dans la globale en utilisant la fonction $Order. Dans ce cas, qHandle stockera l'ID actuel, et puisque nous n'avons pas besoin d'arguments supplémentaires, l'argument arg n'est pas nécessaire. Le résultat est le suivant :

ClassMethod AllRecordsExecute(ByRef qHandle As %Binary) As %Status {  
    Set qHandle = ""    Quit $$$OK
}

La méthode queryNameFetch
La méthode queryNameFetch renvoie un seul résultat sous la forme $List. La signature de cette méthode est la suivante :

ClassMethod queryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ]

where:

• qHandle est utilisé pour la communication avec les autres méthodes de l'implémentation de la requête
• Lorsque la requête est exécutée, les valeurs spécifiées par queryNameExecute ou par un appel précédent de queryNameFetch sont attribuées à qHandle.
• Le rang sera défini soit par une valeur de %List, soit par une chaîne vide, si toutes les données ont été traitées
• AtEnd doit être mis à 1, une fois que la fin des données est atteinte.
• La méthode "Fetch" doit être positionnée après la méthode "Execute", mais cela n'est important que pour SQL statique, c'est-à-dire les curseurs à l'intérieur des requêtes.

En général, les opérations suivantes sont effectuées dans le cadre de cette méthode :

1. Vérifier si nous avons atteint la fin des données
2. S'il reste encore des données : Créez une nouvelle %List et attribuez une valeur à la variable Row
3. Sinon, mettez AtEnd à 1
4. Préparer qHandle pour la prochaine récupération de résultat
5. Retourner l'état

Voici comment cela se présente dans notre exemple :

ClassMethod AllRecordsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status {
    #; itérer dans ^Utils.CustomQueryD    
    #; ecrire le prochain id dans qHandle et écriture de la valeur de la globale avec le nouvel id dans val
    Set qHandle = $Order(^Utils.CustomQueryD(qHandle),1,val)
    #; Vérifier s'il reste des données
       If qHandle = "" {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK    
    }
    #; Si ce n'est pas le cas, créer %List
    #; val = $Lb("", Prop1, Prop2) voir définition de Storage
    #; Row =$lb(Id,Prop1, Prop2)  voir ROWSPEC pour la demande AllRecords
    Set Row = $Lb(qHandle, $Lg(val,2), $Lg(val,3))
    Quit $$$OK
}

La méthode queryNameClose
La méthode queryNameClose met fin à la requête, une fois toutes les données obtenues. La signature de cette méthode est la suivante :

ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ]

où :

• Caché exécute cette méthode après le dernier appel à la méthode queryNameFetch
• En d'autres termes, il s'agit d'un destructeur de requête
• Par conséquent, vous devez disposer de tous les curseurs SQL, des requêtes et des variables locales dans sa mise en œuvre
• Les méthodes renvoient l'état actuel

Dans notre exemple, nous devons supprimer la variable locale qHandle :

ClassMethod AllRecordsClose(ByRef qHandle As %Binary) As %Status {
    Kill qHandle
    Quit $$$OK
  }

Et voilà ! Une fois que vous aurez compilé la classe, vous serez en mesure d'utiliser la requête AllRecords à partir de %SQL.Statement - tout comme les requêtes de la classe de base.

Approches de la logique d'itération pour les requêtes personnalisées

Alors, quelles approches peuvent être utilisées pour les requêtes personnalisées ? En général, il existe 3 approches de base :

• Itération à travers une globale
• Static SQL
• Dynamic SQL

Itération à travers une globale
Cette approche est basée sur l'utilisation de $Order et de fonctions similaires pour l'itération à travers une globale. Elle peut être utilisée dans les cas suivants :

• Les données sont stockées dans des globales (sans classes)
• Vous voulez réduire le nombre de glorefs dans le code
• Les résultats doivent/peuvent être triés par l'indice de la globale

SQL statique
L'approche est basée sur les curseurs et le SQL statique. Elle est utilisée pour :

• Rendre le code int plus lisible
• Faciliter le travail avec les curseurs
• Accélération du processus de compilation (le SQL statique est inclus dans la requête de la classe et n'est donc compilé qu'une seule fois).

Remarque:

• Les curseurs générés à partir de requêtes du type %SQLQuery sont nommés automatiquement, par exemple Q14.
• Tous les curseurs utilisés dans une classe doivent avoir des noms différents
• Les messages d'erreur sont liés aux noms internes des curseurs qui comportent des caractères supplémentaires à la fin de leur nom. Par exemple, une erreur dans le curseur Q140 est en fait causée par le curseur Q14.
• Utilisez PlaceAfter et assurez-vous que les curseurs sont utilisés dans la même routine int où ils ont été déclarés.
• INTO doit être utilisé en conjonction avec FETCH, mais pas DECLARE.

Exemple de SQL statique pour Utils.CustomQuery :

Query AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllStatic, SqlProc ]
{
}

ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status
{
    &sql(DECLARE C CURSOR FOR
        SELECT Id, Prop1, Prop2
        FROM Utils.CustomQuery
     )
     &sql(OPEN C)
    Quit $$$OK
}

ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ]
{
    #; INTO doit être associé à FETCH
    &sql(FETCH C INTO :Id, :Prop1, :Prop2)
    #; Vérifier si la fin des données est atteinte
    If (SQLCODE'=0) {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    }
    Set Row = $Lb(Id, Prop1, Prop2)
    Quit $$$OK
}

ClassMethod AllStaticClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ]
{
    &sql(CLOSE C)
    Quit $$$OK
}

SQL dynamique
L'approche est basée sur les requêtes d'autres classes et le SQL dynamique. Cette approche est raisonnable lorsqu'en plus d'une requête SQL proprement dite, vous devez également effectuer certaines opérations supplémentaires, par exemple exécuter une requête SQL dans plusieurs espaces de noms ou escalader les permissions avant d'exécuter la requête.

Exemple de SQL dynamique pour Utils.CustomQuery :

Query AllDynamic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllDynamic, SqlProc ]
{
}

ClassMethod AllDynamicExecute(ByRef qHandle As %Binary) As %Status
{
    Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery")
    Quit $$$OK
}

ClassMethod AllDynamicFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
{
    If qHandle.%Next()=0 {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    }
    Set Row = $Lb(qHandle.%Get("Id"), qHandle.%Get("Prop1"), qHandle.%Get("Prop2"))
    Quit $$$OK
}

ClassMethod AllDynamicClose(ByRef qHandle As %Binary) As %Status
{
    Kill qHandle
    Quit $$$OK
}

Approche alternative : %SQL.CustomResultSet

Vous pouvez également créer une requête en sous-classant la classe %SQL.CustomResultSet. Les avantages de cette approche sont les suivants :

• Une légère augmentation de la vitesse
• ROWSPEC est inutile, puisque toutes les métadonnées sont obtenues à partir de la définition de la classe
• Respect des principes de conception orientée objet

Pour créer une requête à partir de la sous-classe de la classe %SQL.CustomResultSet, assurez-vous d'effectuer les étapes suivantes :

1. Définir les propriétés correspondant aux champs résultants
2. Définir les propriétés privées où le contexte de la requête sera stocké
3. Remplacer la méthode %OpenCursor (similaire à queryNameExecute) qui initie le contexte. En cas d'erreur, définissez également %SQLCODE et %Message
4. Remplacer la méthode %Next (similaire à queryNameFetch) qui obtient le résultat suivant. Remplacer les propriétés. La méthode renvoie 0 si toutes les données ont été traitées et 1 s'il reste des données
5. Remplacer la méthode %CloseCursor (similaire à queryNameClose) si nécessaire

Exemple de %SQL.CustomResultSet pour Utils.CustomQuery :

Class Utils.CustomQueryRS Extends %SQL.CustomResultSet
{
Property Id As %String;
Property Prop1 As %String;
Property Prop2 As %Integer;
Method %OpenCursor() As %Library.Status
{
    Set ..Id = ""
    Quit $$$OK
}

Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ]
{
    Set sc = $$$OK
    Set ..Id = $Order(^Utils.CustomQueryD(..Id),1,val)
    Quit:..Id="" 0
    Set ..Prop1 = $Lg(val,2)
    Set ..Prop2 = $Lg(val,3)
    Quit $$$OK
}
}

Vous pouvez l'appeler à partir de Caché Object Script code de la manière suivante :

Set resultset= ##class(Utils.CustomQueryRS).%New()
       While resultset.%Next() {
        Write resultset.Id,!
 }

Un autre exemple est disponible dans l'espace de noms SAMPLES - il s'agit de la classe Sample.CustomResultSet qui implémente une requête pour Samples.Person.

Résumé

Les requêtes personnalisées vous aideront à séparer les expressions SQL du code Caché Object Script et à mettre en œuvre un comportement sophistiqué qui peut être trop difficile pour le SQL pur.

Références

Class Queries

Itération à travers une globale

SQL statique

Dynamic SQL

%SQL.CustomResultSet

Classe Utils.CustomQuery

Classe Utils.CustomQueryRS

L'auteur tient à remercier [Alexander Koblov] (https://community.intersystems.com/user/alexander-koblov) pour son aide à la composition de cet article.

Étant donné que SELECT ... FOR UPDATE est implémenté dans de nombreux RDBMS en tant que méthode d'acquisition de lock de ligne explicite, vous utilisez probablement cette fonctionnalité dans de nombreux cas.

Cette syntaxe n'entraîne pas d'erreur dans les produits InterSystems, mais elle n'acquiert pas les locks de ligne attendus.

Cet article vous montrera comment obtenir la même fonctionnalité.

DECLARE CURSOR C1 IS
SELECT Name FROM Person WHERE Name LIKE 'A%' FOR UPDATE
OPEN C1
LOOP FETCH C1 INTO name　
...afficher le nom...
...sortir de loop lorsque vous avez terminé...
END LOOP
CLOSE C1

L'instruction SQL ci-dessus peut être remplacée par l'instruction SQL suivante.

 &SQL(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
 &SQL(UPDATE Person SET ID=ID Where Name like 'A%')
 &SQL(DECLARE C1 CURSOR FOR SELECT ID,Name into :id,:name FROM Person Where Name like 'A%')
 &SQL(OPEN C1)
 &SQL(FETCH C1)
 While (SQLCODE = 0) {
   Write id, ":  ", name,!  &SQL(FETCH C1)
 }
 &SQL(CLOSE C1)&SQL(COMMIT) 

Remarque : &SQL() est appelé Embedded SQL et est une méthode de description qui peut être utilisée lorsque vous souhaitez incorporer des instructions SQL dans la logique côté serveur. Veuillez vous référer au document pour plus de détails.

Un système de stockage global d'aspect plus industriel

Dans le premier article de cette série, nous avons étudié le modèle entité-attribut-valeur (EAV) dans les bases de données relationnelles, et nous avons examiné les avantages et les inconvénients du stockage de ces entités, attributs et valeurs dans des tables. Nous avons appris que, malgré les avantages de cette approche en termes de flexibilité, elle présente de réels inconvénients, notamment une inadéquation fondamentale entre la structure logique des données et leur stockage physique, qui entraîne diverses difficultés.

Pour résoudre ces problèmes, nous avons décidé de voir si l'utilisation de globales - qui sont optimisées pour le stockage d'informations hiérarchiques - serait efficace pour les tâches que l'approche EAV traite habituellement.

Dans la Partie 1, nous avons créé un catalogue pour une boutique en ligne, d'abord en utilisant des tables, puis en utilisant une seule globale. Maintenant, essayons d'implémenter la même structure pour quelques globales.

Dans la première globale, ^catalog, nous allons stocker la structure du répertoire. Dans la deuxième globale, ^good, nous allons stocker les marchandises. Et dans la globale ^index, nous allons stocker les index. Puisque nos propriétés sont liées à un catalogue hiérarchique, nous ne créerons pas de globale séparée pour elles.

Avec cette approche, pour chaque entité (à l'exception des propriétés), nous avons une globale séparée, ce qui est bon du point de vue de la logique. Voici la structure du catalogue global :

Set ^сatalog(root_id, "Properties", "capacity", "name") = "Capacity, GB"
Set ^сatalog(root_id, "Properties", "capacity", "sort") = 1

Set ^сatalog(root_id, sub1_id, "Properties", "endurance", "name") = "Endurance, TBW"
Set ^сatalog(root_id, sub1_id, "Properties", "endurance", "sort") = 2

Set ^сatalog(root_id, sub1_id, "goods", id_good1) = 1
Set ^сatalog(root_id, sub1_id, "goods", id_good2) = 1

Set ^сatalog(root_id, sub2_id, "Properties", "avg_seek_time", "name") = "Rotate speed, ms"
Set ^сatalog(root_id, sub2_id, "Properties", "avg_seek_time", "sort") = 3

Set ^сatalog(root_id, sub2_id, "goods", id_good3) = 1
Set ^сatalog(root_id, sub2_id, "goods", id_good4) = 1

Une globale avec des marchandises ressemblera à quelque chose comme ceci :

Set ^good(id_good, property1) = value1
Set ^good(id_good, property2) = value2
Set ^good(id_good, property3) = value3
Set ^good(id_good, "catalog") = catalog_id

Bien sûr, nous avons besoin d'index afin que pour toute section du catalogue contenant des marchandises, nous puissions trier par les propriétés dont nous avons besoin. Une globale d'index aura une structure semblable à quelque chose comme ceci :

Configurer ^index(id_catalog, property1, id_good) = 1
; Pour obtenir rapidement le chemin complet du sous-catalogue concret
Configurer ^index("path", id_catalog) = "^catalog(root_id, sub1_id)"

Ainsi, dans n'importe quelle section du catalogue, on peut obtenir une liste triée. Une globale d'index est facultative. Il n'est utile que si le nombre de produits dans cette section du catalogue est important.

Code ObjectScript pour travailler avec des données de démonstration Demo Data

Maintenant, nous allons utiliser ObjectScript pour travailler avec nos données. Pour commencer, nous allons obtenir les propriétés d'une marchandise spécifique. Nous avons l'ID d'une marchandise particulière et nous devons afficher ses propriétés dans l'ordre donné par la valeur de tri. Voici le code pour cela :

get_sorted_properties(path, boolTable)
{
  ; mémoriser toutes les propriétés dans la globale temporaire
  While $QLENGTH(@path) > 0 {
    if ($DATA(@path("Properties"))) {
      set ln=""
      for {
	    Set ln = $order(@path("Properties", ln))
	    Quit: ln = ""

        IF boolTable & @path("Properties", ln, "table_view") = 1 {
  	      Set ^tmp(@path("Properties", ln, "sort"), ln) = @path("Properties", ln, "name")
	    }
	  ELSE {
  	    Set ^tmp(@path("Properties", ln, "sort"), ln) = @path("Properties", ln, "name")
	  }
    }
  }
}

print_sorted_properties_of_good(id_good)
{
  Set id_catalog = ^good(id_good, "catalog")
  Set path = ^index("path", id_catalog)

  Do get_sorted_properties(path, 0)

  set ln =""
  for {
   Set ln = $order(^tmp(ln))
   Quit: ln = ""
   Set fn = ""
   for {
 	Set fn = $order(^tmp(ln, fn))
 	Quit: fn = ""
 	Write ^tmp(ln, fn), " ", ^good(id_good, fn),!
   }
  }
}

Ensuite, nous voulons récupérer les produits de la section catalogue sous la forme de la table, basé sur id_catalog :

print_goods_table_of_catalog(id_catalog)
{
  Set path = ^index("path", id_catalog)
  Do get_sorted_properties(path, 1)

  set id=""
  for {
    Set id = $order(@path("goods"), id)
    Quit: id = ""

    Write id," ", ^good(id, "price"), " "

    set ln =""
    for {
      Set ln = $order(^tmp(ln))
      Quit: ln = ""
      Set fn = ""
      for {
 	    Set fn = $order(^tmp(ln, fn))
 	    Quit: fn = ""
 	    Write ^tmp(ln, fn), " ", ^good(id, fn)
      }
      Write !
    }
  }
}

Lisibilité : EAV SQL contre les globales

Comparons maintenant l'utilisation d'EAV et de SQL par rapport à l'utilisation de globales. En ce qui concerne la clarté du code, il est évident qu'il s'agit d'un paramètre subjectif. Mais regardons, par exemple, la création d'un nouveau produit.

Nous allons commencer par l'approche EAV, en utilisant SQL. Tout d'abord, nous devons obtenir une liste des propriétés de l'objet. Il s'agit d'une tâche distincte qui prend beaucoup de temps. Supposons que nous connaissions déjà les IDs de ces trois propriétés : capacité, poids, et endurance.

START TRANSACTION
INSERT INTO good (name, price, item_count, catalog_id) VALUES ('F320 3.2TB AIC SSD', 700, 10, 15);

SET @last_id = LAST_INSERT_ID ();

INSERT INTO NumberValues ​​Values​​(@last_id, @id_capacity, 3200);
INSERT INTO NumberValues ​​Values​​(@last_id, @id_weight, 0.4);
INSERT INTO NumberValues ​​Values​​(@last_id, @id_endurance, 29000);
COMMIT

Dans cet exemple, nous n'avons que trois propriétés, et l'exemple ne semble donc pas si inquiétant. Dans le cas général, nous aurions toujours quelques insertions dans la table de texte à l'intérieur de la transaction :

INSERT INTO TextValues ​​Values​​(@last_id, @ id_text_prop1, 'Text value of property 1');
INSERT INTO TextValues ​​Values​​(@last_id, @ id_text_prop2, 'Text value of property 2');
...
INSERT INTO TextValues Values (@last_id, @id_text_propN, 'Text value of property N');

Bien sûr, nous pourrions simplifier un peu la version SQL si nous utilisions la notation textuelle à la place des propriétés ID, par exemple "capacité" au lieu d'un nombre. Mais dans le monde SQL, ce n'est pas acceptable. Il est plutôt d'usage d'utiliser un ID numérique pour énumérer les instances d'entités. Cela permet d'obtenir des index plus rapides (il faut indexer moins d'octets), il est plus facile de suivre l'unicité et il est plus facile de créer automatiquement un nouvel ID. Dans ce cas, le fragment d'insertion aurait l'apparence suivante :

INSERT INTO NumberValues ​​Values​​(@last_id, 'capacity', 3200);
INSERT INTO NumberValues ​​Values​​(@last_id, 'weight', 0.4);
INSERT INTO NumberValues ​​Values​​(@last_id, 'endurance', 29000);

Voici le même exemple en utilisant des globales :

TSTART
Set ^good(id, "name") = "F320 3.2TB AIC SSD"
Set ^("price") = 700, ^("item_count") = 10, ^("reserved_count") = 0, ^("catalog") = id_catalog
Set ^("capacity") = 3200, ^("weight") = 0.4, ^("endurance") = 29000
TCOMMIT

Supprimons maintenant une marchandise en utilisant l'approche EAV :

START TRANSACTION
DELETE FROM good WHERE id = @ good_id;
DELETE FROM NumberValues ​​WHERE good_id = @ good_id;
DELETE FROM TextValues ​​WHERE good_id = @ good_id;
COMMIT

Et ensuite, faisons la même chose avec les globales :

Kill ^good(id_good)

Nous pouvons également comparer les deux approches en termes de longueur de code. Comme vous pouvez le constater dans les exemples précédents, lorsque vous utilisez des globales, le code est plus court. C'est une bonne chose. Plus le code est court, moins il y a d'erreurs et plus il est facile à comprendre et à gérer.

En général, un code plus court est aussi plus rapide. Et, dans ce cas, c'est certainement vrai, puisque les globales constituent une structure de données de niveau inférieur aux tables relationnelles.

Mise à l'échelle des données avec EAV et Globales

Ensuite, examinons la mise à l'échelle horizontale. Avec l'approche EAV, nous devons au moins distribuer les trois plus grandes tables sur les serveurs : Good, NumberValues, et TextValues. Les tables contenant des entités et des attributs peuvent simplement être entièrement copiés sur tous les serveurs, car ils contiennent peu d'informations.

Dans chaque serveur, avec une mise à l'échelle horizontale, des produits différents seraient stockés dans les tables Good, NumberValues et TextValues. Nous devrions allouer certains blocs d'identification pour les produits sur chaque serveur afin d'éviter la duplication des identifiants pour des produits différents.

Pour une mise à l'échelle horizontale avec des globales, il faudrait configurer des plages d'ID dans la globale et attribuer une plage de globale à chaque serveur.

La complexité est à peu près la même pour EAV et pour les globales, sauf que pour l'approche EAV, nous devrions configurer des plages d'ID pour trois tables. Avec les globales, nous configurons les ID pour une seule globale. C'est-à-dire qu'il est plus facile d'organiser la mise à l'échelle horizontale pour les globales.

Perte de données avec EAV et avec Globales

Enfin, considérons le risque de perte de données dû à des fichiers de base de données corrompus. Où est-il plus facile de sauvegarder toutes les données : dans cinq tables ou dans trois globales ( y compris une globale d'index ) ?

Je pense que c'est plus facile dans trois globales. Avec l'approche EAV, les données des marchandises différentes sont mélangées dans des tables, alors que pour les globales, les informations sont stockées de manière plus holistique. Les branches sous-jacentes sont stockées et triées séquentiellement. Par conséquent, la corruption d'une partie de la globale est moins susceptible d'entraîner des dommages que la corruption de l'une des tables dans l'approche EAV, où les données sont stockées comme des pâtes entremêlées.

Un autre casse-tête dans la récupération des données est l'affichage des informations. Avec l'approche EAV, les informations sont réparties entre plusieures tables et des scripts spéciaux sont nécessaires pour les assembler en un seul ensemble. Dans le cas des globales, vous pouvez simplement utiliser la commande ZWRITE pour afficher toutes les valeurs et les branches sous-jacentes du nœud.

Les Globales d'InterSystems IRIS : Une meilleure approche ?

L'approche EAV est apparue comme une astuce pour stocker des données hiérarchiques. Les tables n'ont pas été conçus à l'origine pour stocker des données imbriquées. L'approche EAV de facto est l'émulation des globales dans les tables. Étant donné que les tables représentent une structure de stockage de données de plus haut niveau et plus lente que les globales, l'approche EAV échoue par rapport aux globales.

À mon avis, pour les structures de données hiérarchiques, les globales sont plus pratiques et plus compréhensibles en termes de programmation, tout en étant plus rapides.

Si vous avez prévu une approche EAV pour votre projet, je vous suggère d'envisager d'utiliser les globales d'InterSystems IRIS pour stocker les données hiérarchiques.

La cause de cette erreur est que la ressource locked est déjà locked par un autre processus dans l'application et que le lock n'est pas libéré pour une raison quelconque.

S'il n'y a aucun signe que d'autres processus avec le lock, il est possible que la table de locks manque d'espace libre. Dans ce cas, le message LOCK TABLE FULL est envoyé au Message Log

Si vous effectuez un traitement transactionnel, il est possible que le report du lock ait un effet.
Veuillez vous référer aux documents suivants pour la transaction et le report de lock.

Using LOCK in Transactions【IRIS】

Using LOCK in Transactions

De plus, s'il existe un grand nombre d'enregistrements mis à jour par des instructions SQL dans la même table au cours d'une transaction, le seuil de lock (la valeur par défaut est 1000) est atteint et une escalade de lock se produit, entraînant un état de lock de table.

Comme vous pouvez le voir, il existe plusieurs causes possibles pour l'erreur de délai d'attente de lock. Tout d'abord, vérifiez l'état actuel du lock dans le menu de locks de Management Portal.

【Version 2011.1 ou ultérieure】
Management Portal ： [System Operations]> [Lock]

【Version 2010.2 ou antérieure】
Management Portal ：[Operations]> [Lock]

Introduction

Dans le premier article de cette série, nous examinerons le modèle entité-attribut-valeur (EAV) dans les bases de données relationnelles pour voir comment il est utilisé et à quoi il sert. Ensuite, nous comparerons les concepts du modèle EAV aux globales.

Parfois, on dispose d'objets comportant un nombre inconnu de champs, ou peut-être des champs hiérarchiquement imbriqués, pour lesquels, en règle générale, il faut effectuer une recherche.

Par exemple, voici une boutique en ligne avec divers groupes de produits. Chaque groupe de produits a son propre ensemble de propriétés uniques et a également des propriétés communes. Par exemple, les disques SSD et les disques durs ont la propriété commune "capacité", mais tous deux ont également des propriétés uniques, "Endurance, TBW" pour les SSD et "temps moyen de positionnement de la tête" pour les disques durs.

Dans certaines situations, le même produit, fabriqué par différents fabricants, possède des propriétés uniques.

Ainsi, imaginons que nous ayons une boutique en ligne qui vend 50 groupes de marchandises différents. Chaque groupe de produits a ses cinq propriétés uniques, qui peuvent être numériques ou textuelles.

Si nous créons une table dans lequel chaque produit possède 250 propriétés, alors que seules cinq d'entre elles sont réellement utilisées, non seulement nous augmentons considérablement (50 fois !) les exigences en matière d'espace disque, mais nous réduisons aussi considérablement les caractéristiques de vitesse de la base de données, puisque le cache sera encombré de propriétés inutiles et vides.

Mais ce n'est pas tout. Chaque fois que nous ajoutons une nouvelle famille de produits avec ses propriétés propres, nous devons modifier la structure du tableau à l'aide de la commande ALTER TABLE. Sur les tables de grande taille, cette opération peut prendre des heures ou des jours, ce qui est inacceptable pour les entreprises.

"Oui", remarquera le lecteur attentif, "mais nous pouvons utiliser une table différente pour chaque groupe de produits." Bien sûr, vous avez raison, mais cette approche nous donne une base de données avec des dizaines de milliers de tables pour un grand magasin, ce qui est difficile à administrer. De plus, le code, qui doit être pris en charge, devient de plus en plus complexe.

D'autre part, il n'est pas nécessaire de modifier la structure de la base de données lors de l'ajout d'un nouveau groupe de produits. Il suffit d'ajouter une nouvelle table pour un nouveau groupe de produits.

Dans tous les cas, les utilisateurs doivent être capables de rechercher facilement les produits dans un magasin, d'obtenir une table pratique des marchandises indiquant leurs propriétés actuelles et de comparer les produits.

Comme vous pouvez l'imaginer, un formulaire de recherche comportant 250 champs serait extrêmement gênant pour l'utilisateur, tout comme le fait de voir 250 colonnes de propriétés diverses dans la table des produits alors que seulement cinq propriétés pour le groupe sont nécessaires. Il en va de même pour les comparaisons de produits.

Une base de données marketing pourrait également servir comme un autre exemple utile. Pour chaque personne stockée dans la base, de nombreuses propriétés (souvent imbriquées) doivent être ajoutées, modifiées ou supprimées en permanence. Dans le passé, une personne peut avoir acheté quelque chose pour un certain coût, ou avoir acheté certains groupes de produits, avoir participé à un événement, avoir travaillé quelque part, avoir de la famille, vivre dans une certaine ville, appartenir à une certaine classe sociale, et ainsi de suite. Il pourrait y avoir des milliers de champs possibles, en constante évolution. Les spécialistes du marketing réfléchissent sans cesse à la manière de distinguer différents groupes de clients et de leur proposer des offres spéciales convaincantes.

Pour résoudre ces problèmes et disposer en même temps d'une structure de base de données précise et définie, l'approche entité-attribut-valeur a été développée.

Approche EAV

L'essence de l'approche EAV est le stockage séparé des entités, des attributs et des valeurs d'attributs. En général, pour illustrer l'approche EAV, on utilise seulement trois tables, appelés Entité, Attribut et Valeur :

La structure des données de démonstration que nous allons stocker.

Implémentation de l'approche EAV à l'aide de tables

Considérons maintenant un exemple plus complexe utilisant cinq tables (quatre si vous choisissez de consolider les deux derniers tables pour en faire un seul).

La première table est Сatalog:

CREATE TABLE Catalog (
id INT,
name VARCHAR (128),
parent INT
);

Cette table correspond en fait à l'Entité dans l'approche EAV. Elle permettra de stocker les sections du catalogue hiérarchique des marchandises.

La deuxième table est ****Field :

CREATE TABLE Field (
id INT,
name VARCHAR (128),
typeOf INT,
searchable INT,
catalog_id INT,
table_view INT,
sort INT
);

Dans cette table, nous spécifions le nom de l'attribut, son type, et si l'attribut est recherchable. Nous indiquons également la section du catalogue qui contient les marchandises auxquelles ces propriétés appartiennent. Tous les produits de la section du catalogue de catalog_id ou inférieur peuvent avoir des propriétés différentes qui sont stockées dans cette table.

La troisième table est Good.EIle est conçue pour stocker les marchandises, avec leurs prix, la quantité totale des marchandises, la quantité réservée des marchandises, et le nom des marchandises. En principe, vous n'avez pas vraiment besoin de cette table mais, à mon avis, il est utile d'avoir une table séparée pour les marchandises.

CREATE TABLE Good (
id INT,
name VARCHAR (128),
price FLOAT,
item_count INT,
reserved_count,
catalog_id INT
);

La quatrième table (TextValues) et la cinquième table (NumberValues) sont conçues pour stocker les valeurs du texte et les attributs numériques des marchandises, et elles ont une structure similaire.

CREATE TABLE TextValues ​​(
good_id INT,
field_id INT,
fValue TEXT
);

CREATE TABLE NumberValues ​​(
good_id INT,
field_id INT,
fValue INT
);

Au lieu des tables de valeurs textuelles et numériques, vous pouvez utiliser une seule table CustomValues avec une structure de ce type :

CREATE TABLE CustomValues ​​(
good_id INT,
field_id INT,
text_value TEXT,
number_value INT
);

Je préfère stocker les différents types de données séparément car cela augmente la vitesse et économise de l'espace.

Accès aux données à l'aide de l'approche EAV

Commençons par afficher le mappage de la structure du catalogue à l'aide de SQL :

SELECT * FROM Catalog ORDER BY id;

Afin de former un arbre à partir de ces valeurs, un code distinct est nécessaire. En PHP, cela ressemblerait à quelque chose comme ceci :

$stmt = $ pdo-> query ('SELECT * FROM Catalog ORDER BY id');
$aTree = [];
$idRoot = NULL;

while ($row = $ stmt->fetch())
{
    $aTree [$row ['id']] = ['name' => $ row ['name']];

    if (! $row['parent'])
      $idRoot = $row ['id'];
    else
      $aTree [$row['parent']] ['sub'] [] = $row['id'];
}

À l'avenir, nous pourrons simplement dessiner l'arbre si nous partons du nœud racine $aTree[$ idRoot].

Maintenant, nous allons obtenir les propriétés d'un produit spécifique. 

Tout d'abord, nous allons obtenir une liste de propriétés spécifiques à ce produit, puis y attacher les propriétés qui sont dans la base de données. Dans la vie réelle, toutes les propriétés indiquées ne sont pas renseignées et nous sommes donc obligés d'utiliser LEFT JOIN :

SELECT * FROM
(
SELECT g. *, F.name, f.type_of, val.fValue, f.sort FROM Good as g
INNER JOIN Field as f ON f.catalog_id = g.catalog_id
LEFT JOIN TextValues ​​as val ON tv.good = g.id AND f.id = val.field_id
WHERE g.id = $ nGood AND f.type_of = 'text'
UNION
SELECT g. *, F.name, f.type_of, val.fValue, f.sort FROM Good as g
INNER JOIN Field as f ON f.catalog_id = g.catalog_id
LEFT JOIN NumberValues ​​as val ON val.good = g.id AND f.id = val.field_id
WHERE g.id = $nGood AND f.type_of = 'number'
) t
ORDER BY t.sort;

Si nous utilisons une seule table pour stocker les valeurs numériques et textuelles, la requête est considérablement simplifiée :

SELECT g. *, F.name, f.type_of, val.text_value, val.number_value, f.sort FROM Good as g
INNER JOIN Field as f ON f.catalog = g.catalog
LEFT JOIN CustomValues ​​as val ON tv.good = g.id AND f.id = val.field_id
WHERE g.id = $nGood
ORDER BY f.sort;

Maintenant, nous allons obtenir les produits sous la forme de table contenue dans la section du catalogue $nCatalog. Tout d'abord, nous obtenons une liste de propriétés qui doivent être reflétées dans la vue de la table pour cette section du catalogue :

SELECT f.id, f.name, f.type_of FROM Catalog as c
INNER JOIN Field as f ON f.catalog_id = c.id
WHERE c.id = $nCatalog AND f.table_view = 1
ORDER BY f.sort;

Ensuite, nous construisons la requête pour créer la table. Supposons que pour une vue tabulaire, nous ayons besoin de trois propriétés supplémentaires (sans compter celles de la table Good). Pour simplifier la requête, nous supposons que :

SELECT g.if, g.name, g.price,
            f1.fValue as f1_val,
            f2.fValue as f2_val,
            f3.fValue as f3_val,
FROM Good
LEFT JOIN TextValue as f1 ON f1.good_id = g.id
LEFT JOIN NumberValue as f2 ON f2.good_id = g.id
LEFT JOIN NumberValue as f3 ON f3.good_id = g.id
WHERE g.catalog_id = $nCatalog;

Les avantages et les inconvénients de l'approche EAV

L'avantage évident de l'approche EAV est sa flexibilité. Avec des structures de données fixes telles que les tables, nous pouvons nous permettre de stocker une grande variété d'ensembles de propriétés pour les objets. Et nous pouvons stocker différentes structures de données sans modifier le schéma de la base de données. 

Nous pouvons également utiliser SQL, qui est familier à un grand nombre de développeurs. 

Le défaut le plus évident est l'inadéquation entre la structure logique des données et leur stockage physique, qui entraîne diverses difficultés. 

En outre, la programmation implique souvent des requêtes SQL très complexes. Le débogage peut être difficile car vous devez créer des outils non-standards pour visualiser les données EAV. Enfin, vous pouvez être amené à utiliser des requêtes LEFT JOIN, qui ralentissent la base de données.

Globales : Une alternative à EAV

Comme je suis familier à la fois du monde SQL et du monde des globales, j'ai eu l'idée que l'utilisation des globales pour les tâches résolues par l'approche EAV serait beaucoup plus intéressante.

Les globales sont des structures de données qui vous permettent de stocker des informations dispersées et hiérarchiques. Un point très important est que les globales sont soigneusement optimisées pour le stockage d'informations hiérarchiques. Les globales sont elles-mêmes des structures de niveau inférieur aux tables, ce qui leur permet de travailler beaucoup plus rapidement que ces derniers.

Dans le même temps, la structure de globale elle-même peut être sélectionnée en fonction de la structure des données, ce qui rend le code très simple et clair.

Structure de globale pour le stockage des données démographiques

Une globale représente une structure tellement flexible et élégante pour le stockage des données que nous pourrions nous débrouiller avec une seule globale pour le stockage des données dans les sections du catalogue, les propriétés et les produits, par exemple, de la manière suivante :

Remarquez à quel point la structure de globale est similaire à la structure de données. Cette conformité simplifie grandement le codage et le débogage.

En pratique, il est préférable d'utiliser plusieurs globales, bien que la tentation de stocker toutes les informations dans une seule globale soit assez forte. Il est judicieux de créer des globales distinctes pour les indices. Vous pouvez également séparer le stockage de la structure de la partition du répertoire des marchandises.

Quelle est la suite ?

Dans le deuxième article de cette série, nous aborderons les détails et les avantages du stockage des données dans des globales InterSystems Iris au lieu de suivre le modèle EAV.

Il est possible de construire (reconstruire) l'index pendant que des données sont enregistrées/supprimées, mais si vous construisez l'index pendant ce processus, il sera référencé pendant sa mise à jour, utilisez donc l'utilitaire dédié et procédez à la construction de l'index.

La procédure est la suivante.

1. Masquez le nom d'index que vous prévoyez d'ajouter l'optimiseur de requête.
2. Ajoutez la définition de l'index et effectuez la construction de l'index.
3. Une fois la construction de l'index est terminée, publiez l'index ajouté dans l'optimiseur.

L'exemple d'exécution est le suivant.

* Dans l'exemple, l'index standard HomeStateIdx est défini pour la colonne Home_State (informations d'état de l'adresse de contact) de Sample.Person.

2.Après avoir ajouté la définition d'index, reconstruisez-la.
　　Exemple de définition: Index HomeStateIdx On Home.State;

3. Une fois la construction de l'index est terminée, publiez l'index ajouté dans l'optimiseur.

Reportez-vous au plan de requête pour voir si l'index a été utilisé/non utilisé.
Dans l'exemple suivant, le résultat de la confirmation du plan avec le terminal basculé sur l'environnement d'exécution SQL avec $system.SQL.Shell() s'affiche (lors du référencement dans le Management Portal, après avoir exécuté SQL sur l'écran d'exécution de la requête, cliquez sur le bouton "Affichage du plan").

Pour plus de détails, veuillez consulter les documents suivants.
Building Indices on a READ and WRITE Active System【IRIS】

Building Indices on a READ and WRITE Active System

La semaine dernière, nous avons annoncé la Plate-forme de données InterSystems IRIS, notre nouvelle plate-forme complète pour toutes vos activités liées aux données, qu'elles soient transactionnelles, analytiques ou les deux. Nous avons inclus un grand nombre des fonctionnalités que nos clients connaissent et apprécient de Caché et Ensemble, mais dans cet article, nous allons mettre un peu plus en lumière l'une des nouvelles capacités de la plate-forme : SQL Sharding, une nouvelle fonctionnalité puissante dans notre histoire de scalability.

Si vous avez exactement 4 minutes et 41 secondes, jetez un coup d'œil à cette vidéo astucieuse sur le scalability. Si vous ne trouvez pas vos écouteurs et ne croyez pas que notre voix apaisante plaira à vos collègues, continuez à lire !

Changement d'échelle

Qu'il s'agisse de traiter des millions d'opérations boursières par jour ou de soigner des dizaines de milliers de patients par jour, une plateforme de données prenant en charge ces activités doit être capable de faire face à ces grandes échelles de manière transparente. La manière transparente signifie que les développeurs et les utilisateurs ne doivent pas se préoccuper de ces chiffres et qu'ils peuvent se concentrer sur leurs activités et applications principales, la plate-forme se chargeant de l'aspect de l'échelle.

Depuis des années, Caché prend en charge le vertical scalability, où les progrès du matériel sont exploités de manière transparente par le logiciel, en exploitant efficacement un nombre très élevé de cœurs et de grandes quantités de RAM. C'est ce qu'on appelle la mise à l'échelle vers le haut, et bien qu'un bon effort de dimensionnement initial puisse vous permettre d'obtenir un système parfaitement équilibré, il y a une limite inhérente à ce que vous pouvez réaliser sur un seul système de manière rentable.

C'est là qu'intervient le horizontal scalability, qui consiste à répartir la charge de travail sur un certain nombre de serveurs distincts travaillant en cluster, plutôt que sur un seul. Caché prend en charge les serveurs d'application ECP comme moyen de horizontal scalability depuis un certain temps déjà, mais InterSystems IRIS ajoute désormais le sharding SQL.

Quelles sont les nouveautés ?

Quelle est donc la différence entre les serveurs d'application ECP et la nouvelle fonctionnalité de sharding ? Pour comprendre cette différence, examinons de plus près les charges de travail. Une charge de travail peut consister en des dizaines de milliers de petits dispositifs écrivant continuellement de petits lots de données dans la base de données, ou juste une poignée d'analystes émettant des requêtes analytiques couvrant chacune des Go de données à la fois. Lequel des deux a la plus grande échelle ? Difficile à dire, tout comme il est difficile de dire si une canne à pêche ou un tonneau de bière est le plus grand. Les charges de travail ont plus d'une dimension et, par conséquent, la mise à l'échelle pour les prendre en charge nécessite également un peu plus de subtilité.

En simplifiant grossièrement, considérons les composants suivants dans une charge de travail d'application : N représente la charge de travail de l'utilisateur et Q représente la taille de la requête. Dans nos exemples précédents, la première charge de travail a un N élevé mais un Q faible et la seconde est caractérisée par un N faible mais un Q élevé. Les serveurs d'application ECP sont très efficaces pour aider à supporter un grand N, car ils permettent de répartir les utilisateurs de l'application sur différents serveurs. Cependant, ils ne sont pas nécessairement aussi utiles si l'ensemble de données devient très grand et que l'ensemble de travail ne tient pas dans la mémoire d'une seule machine. Le sharding s'occupe des grands Q, en vous permettant de partitionner l'ensemble de données entre les serveurs, le travail étant également poussé vers ces serveurs shard autant que possible.

Sharding SQL

Alors, à quoi sert réellement le sharding ? Il s'agit d'une fonctionnalité SQL qui divise les données d'une table partagée en ensembles disjoints de lignes qui sont stockés sur les serveurs shard. Lorsque vous vous connectez au maître du shard, vous voyez toujours cette table comme s'il s'agissait d'une table unique contenant toutes les données, mais les requêtes sont divisées en requêtes locales au shard qui sont envoyées à tous les serveurs shard. Là, les serveurs shard calculent les résultats en fonction des données qu'ils ont stockées localement et renvoient leurs résultats au shard master. Ce dernier agrège ces résultats, exécute toute combinaison logique pertinente et renvoie les résultats à l'application. 

Bien que ce système soit trivial pour un simple SELECT * FROM table, il y a beaucoup de logique intelligente au niveau de l'interface qui garantit que vous pouvez utiliser (presque) n'importe quelle requête SQL et qu'un maximum de travail est poussé vers les shards pour maximiser le parallélisme. La "clé du shard", qui définit quelles lignes vont où, est l'endroit où vous anticipez les modèles de requête typiques. Plus important encore, si vous pouvez vous assurer que les tables souvent reliés entre eux sont répartis selon les mêmes clés, les jointures peuvent être entièrement résolues au niveau du shard, ce qui vous donne les hautes performances que vous recherchez.

Bien sûr, ce n'est qu'un avant-goût et il y a beaucoup plus à explorer, mais l'essentiel est ce qui est illustré ci-dessus : Le sharding SQL est une nouvelle recette dans le livre des plats hautement évolutifs que vous pouvez concocter avec InterSystems IRIS. Il s'agit d'un complément aux serveurs d'applications ECP, qui se concentre sur les ensembles de données de taille importante, ce qui en fait une solution idéale pour de nombreux cas d'utilisation analytique. Comme les serveurs d'applications ECP, il est entièrement transparent pour l'application et présente quelques variations architecturales plus créatives pour des scénarios très spécifiques. 

Où puis-je en savoir plus ?

Les enregistrements des sessions suivantes du Global Summit 2017 sur le sujet sont disponibles ici : http://learning.intersystems.com:

• Qu'est-ce qui est caché dans votre lac de données ?, un aperçu technique du scalability et du sharding en particulier
• Nous en voulons plus ! Résolution de la question du scalability, un aperçu des cas d'utilisation pertinents exigeant une plateforme hautement scalable

Consultez également le présent guide de ressources sur InterSystems IRIS sur learning.intersystems.com pour en savoir plus sur les autres capacités de la nouvelle plate-forme. Si vous souhaitez essayer le sharding sur votre cas d'utilisation particulier, consultez http://www.intersystems.com/iris et remplissez le formulaire en bas de page pour vous inscrire à notre programme d'adoption initiale, ou surveillez la version de test sur le terrain prévue plus tard cette année.

Le type DATE correspond au type de données du produit InterSystems %Date et le type TIME correspond à %Time.

%Date enregistre une date interne (premier élément séparé par une virgule de la variable spéciale $Horolog), et %Time enregistre l'heure interne (deuxième élément séparé par une virgule de la variable spéciale $Horolog). La logique côté serveur utilise donc la valeur au format (logique) interne, sauf si vous changez le mode d'affichage.
La méthode permettant de modifier le format d'affichage de la date et de l'heure internes dans la logique côté serveur dépend de la méthode d'exploitation.

Dans les exemples suivants, nous utiliserons le tableau Sample.Person.
（L'exemple d'exécution de la commande est présenté pour une instruction SELECT, mais il peut également être écrit pour une instruction de mise à jour.）

Pour essayer IRIS/IRIS for Health, téléchargez la documentation à partir de (Télécharger des échantillons à utiliser avec InterSystems IRIS),
ou à partir de Articles connexes (téléchargement de la définition de classe de l'échantillon (Sample.Person) et création de données d'échantillon), veuillez commencer par importer la classe Sample.Person et créer les données d'exemple.

Si vous essayez Caché/Ensemble, utilisez Sample.Person dans l'espace de noms SAMPLES.

(1) Si vous utilisez l'Embedded SQL

Pour changer le format d'affichage à l'aide d'Embedded SQL, utilisez #sqlcomple select.
Les valeurs suivantes peuvent être spécifiées.

• Logical (par défaut)
• Display
• ODBC
• Runtime

Documentation (IRIS) : Compilation du SQL intégré et du préprocesseur de macros【IRIS】
Documentation : Compilation du SQL intégré et du préprocesseur de macros

(2) Si vous utilisez le Dynamic SQL 

Pour changer le format d'affichage en SQL dynamique à l'aide de %SQL.Statement, utilisez la propriété %SelectMode.
Cette propriété doit être définie avant l'exécution de %Execute().

Les valeurs qui peuvent être définies sont les suivantes.

• 0: mode logique
• 1: mode ODBC
• 2: mode d'affichage

(3) Lorsque vous utilisez une requête de classe 

Pour changer le format d'affichage dans une requête de classe, utilisez le paramètre de définition de la requête : SELECTMODE.
Les valeurs qui peuvent être spécifiées sont les suivantes

• RUNTIME (par défaut)
• LOGICAL
• DISPLAY
• ODBC

Voici un exemple de définition.

(4) Comment changer le format d'affichage des processus en cours

L'objet système $SYSTEM.SQL.SetSelectMode() peut être utilisé pour modifier le format d'affichage du processus en cours.
Les arguments et les valeurs de retour sont les suivants.

• Spécifiez 0 (logique), 1 (ODBC) ou 2 (affichage) comme premier argument.
• Le second argument est un argument de type pass-by-reference dont le résultat d'exécution est défini par %Status.
• La valeur de retour est le numéro du mode d'affichage en cours.

Veuillez vous référer à la page du document ci-dessous pour plus de détails.

Les bases d'InterSystems SQL - Options d'affichage des données【IRIS】
Options d'affichage de CachéSQL Basics_Data

※Après avoir changé le format d'affichage d'un processus, si le format d'affichage est modifié pour chaque méthode d'exécution SQL, le dernier format d'affichage spécifié sera utilisé.

(5) Comment convertir le format d'affichage à l'aide de fonctions ObjectScript

Une autre méthode consiste à utiliser les fonctions de conversion d'affichage d'ObjectScript pour convertir le format interne en format d'affichage.

Pour les fonctions de datation,
　Affichage -> Format interne $ZDATEH(yyyymmdd,8) ou $ZDATE(yyyy-mm-dd,3)
　Interne -> Pour obtenir le résultat du format d'affichage YYYYYMMDD : $ZDATE(+$Horolog,8) Si vous voulez obtenir le résultat de YYYYY-MM-DD : $ZDATEH(+$H,3), dans la fonction horaire
　Affichage -> format interne $ZTIMEH("HH:MM:SS")
　Interne -> Si vous voulez obtenir le résultat au format d'affichage HH:MM:SS : $ZTIMEH($piece($Horolog,"",2)), il existe également les fonctions $ZDATETIME() et $ZDATETIMEH() pour manipuler la date et l'heure.

Vous trouverez plus de détails sur les fonctions de date dans des documents suivants.
ObjectScript fonction【IRIS】
ObjectScript fonction

Comme nous le savons tous, Caché est une excellente base de données qui accomplit de nombreuses tâches en son sein. Cependant, que faites-vous lorsque vous avez besoin d'accéder à une base de données externe ? Une façon de le faire est d'utiliser la passerelle Caché SQL Gateway via JDBC. Dans cet article, mon objectif est de répondre aux questions suivantes pour vous aider à vous familiariser avec cette technologie et à déboguer certains problèmes courants.

Plan de travail

• Quels sont les paramètres de connexion dont vous avez besoin pour vous connecter à une base de données distante ?
• Qu'est-ce que la passerelle JDBC Gateway et le service de passerelle Java Gateway Service dans Ensemble ?
• Quels outils et méthodes sont disponibles pour déboguer les problèmes ?
• Quels sont les types de problèmes courants et les approches pour les résoudre ?

Avant de se plonger dans ces questions, discutons rapidement de l'architecture de la passerelle JDBC SQL Gateway. Pour simplifier, vous pouvez considérer que l'architecture est la suivante : Cache établit une connexion TCP avec un processus Java, appelé processus de passerelle. Le processus de passerelle se connecte ensuite à une base de données distante, telle que Caché, Oracle ou SQL Server, en utilisant le pilote spécifié pour cette base de données. Pour plus d'informations sur l'architecture de la passerelle SQL Gateway, veuillez consulter la documentation sur Utilisation de la passerelle Caché SQL Gateway.

Paramètres de connexion

Lorsque vous vous connectez à une base de données distante, vous devez fournir les paramètres suivants :

• nom d'utilisateur
• mot de passe
• nom du pilote
• URL
• chemin de classe

Connexion à la base de données Caché

Par exemple, si vous avez besoin de vous connecter à une instance de Caché en utilisant la passerelle SQL Gateway via JDBC, vous devez naviguer vers [System Administration] -> [Configuration] -> [Connectivity] -> [SQL Gateway Connections] dans le portail de gestion du système (SMP). Cliquez ensuite sur "Créer une nouvelle connexion" et spécifiez "JDBC" comme type de connexion.

Lors de la connexion à un système Caché, le nom du pilote doit toujours être com.intersys.jdbc.CacheDriver, comme indiqué dans la capture d'écran. Si vous vous connectez à une base de données tierce, vous devrez utiliser un nom de pilote différent (voir Connexion à des bases de données tierces ci-dessous).

Lorsque vous vous connectez aux bases de données Caché, vous n'avez pas besoin de spécifier un chemin de classe car le fichier JAR est téléchargé automatiquement.

Le paramètre URL varie également en fonction de la base de données à laquelle vous vous connectez. Pour les bases de données Caché, vous devez utiliser une URL de la forme suivante

jdbc:Cache://[server_address]:[superserver_port]/[namespace]

Connexion à des bases de données tierces

Une base de données tierce courante est Oracle. Un exemple de configuration est présenté ci-dessous.

Comme vous pouvez le constater, le nom du pilote et l'URL ont des caractéristiques différentes de celles que nous avons utilisées pour la connexion précédente. En outre, j'ai spécifié un chemin de classe dans cet exemple, car je dois utiliser le pilote d'Oracle pour me connecter à leur base de données.

Comme vous pouvez l'imaginer, SQL Server utilise différents modèles d'URL et de noms de pilotes.

Vous pouvez tester si les valeurs sont valides en cliquant sur le bouton " Testez la connexion ". Pour créer la connexion, cliquez sur "Enregistrer".

JDBC Gateway vs le service Java Gateway Business Service

Tout d'abord, la passerelle JDBC et le service de passerelle Java sont complètement indépendants l'un de l'autre. La passerelle JDBC peut être utilisée sur tous les systèmes basés sur Caché, alors que le service de passerelle Java n'existe que dans le cadre d'Ensemble. En outre, le service de passerelle Java utilise un processus différent de celui utilisé par la passerelle JDBC. Pour plus de détails sur le service commercial de passerelle Java, veuillez consulter Le service commercial de passerelle Java.

Méthodes et outils

Vous trouverez ci-dessous 5 outils et méthodes couramment utilisés pour résoudre des problèmes avec la passerelle JDBC SQL Gateway. Je vais d'abord parler de ces outils et vous montrer quelques exemples de leur utilisation dans la section suivante.

1. Journaux

A. Journal du pilote et journal de la passerelle

Lorsque vous utilisez la passerelle JDBC, le journal correspondant est le journal de la passerelle JDBC SQL. Comme nous l'avons vu précédemment, la passerelle JDBC est utilisée lorsque Caché doit accéder à des bases de données externes, ce qui signifie que Caché est le client. Le journal du pilote, par contre, correspond à l'utilisation du pilote JDBC d'InterSystems pour accéder à une base de données Caché à partir d'une application externe, ce qui signifie que Caché est le serveur. Si vous avez une connexion d'une base de données Caché à une autre base de données Caché, les deux types de journaux peuvent être utiles.

Dans notre documentation la section relative à l'activation du journal du pilote est intitulée "Activation de la journalisation pour JDBC", et la section relative à l'activation du journal de la passerelle est intitulée "Activation de la journalisation pour la passerelle SQL JDBC".

Même si les deux journaux comportent le mot "JDBC", ils sont totalement indépendants. L'objet de cet article est la passerelle JDBC, c'est pourquoi j'aborderai plus en détail le journal de la passerelle. Pour plus d'informations sur le journal du pilote, veuillez vous reporter à la section Activation du journal du pilote.

B. Activation du journal du pilote

Si vous utilisez la passerelle Caché JDBC SQL Gateway, vous devez effectuer les opérations suivantes pour activer la journalisation : dans le portail de gestion, allez dans [System Administration] > [Configuration] > [Connectivity] > [JDBC Gateway Settings]. Indiquez une valeur pour le journal de la passerelle JDBC. Ce doit être le chemin complet et le nom d'un fichier journal (par exemple, /tmp/jdbcGateway.log). Le fichier sera automatiquement créé s'il n'existe pas, mais le répertoire ne le sera pas. Caché va démarrer la passerelle JDBC SQL Gateway avec journalisation pour vous.

Si vous utilisez le service commercial Java Gateway dans Ensemble, veuillez consulter Activation de la journalisation de la passerelle Java Gateway dans Ensemble pour savoir comment activer la journalisation.

C. Analyse du journal d'une passerelle

Maintenant que vous avez collecté un journal de passerelle, vous vous posez peut-être la question suivante : quelle est la structure du journal et comment le lire ? Bonne question ! Je vais vous fournir ici quelques informations de base pour vous aider à démarrer. Malheureusement, il n'est pas toujours possible d'interpréter complètement le journal sans avoir accès au code source. Pour les situations complexes, n'hésitez pas à contacter le WRC (Centre de réponse global d'InterSystems) !

Pour démystifier la structure du journal, rappelez-vous qu'il s'agit toujours d'un morceau de données suivi d'une description de ce qu'il fait. Par exemple, voyez cette image avec une coloration syntaxique de base :

Afin de comprendre ce que Received signifie ici, vous devez vous rappeler que le journal de la passerelle enregistre les interactions entre la passerelle et la base de données descendante. Ainsi, Received signifie que la passerelle a reçu l'information de Caché/Ensemble. Dans l'exemple ci-dessus, la passerelle a reçu le texte d'une requête SELECT. Les significations des différentes valeurs de msgId peuvent être trouvées dans le code interne. Le 33 que nous voyons ici signifie " Preparer l'instruction ".

Le journal lui-même fournit également des informations sur le pilote, ce qui est intéressant à vérifier lors du débogage des problèmes. Voici un exemple,

Comme nous pouvons le voir, le Driver Name est com.intersys.jdbc.CacheDriver, ce qui est le nom du pilote utilisé pour se connecter au processus de passerelle. Le Jar File Name est cachejdbc.jar, ce qui est le nom du fichier jar situé dans <cache_install_directory>\lib\.

2. Trouver le processus de passerelle

Pour trouver le processus de passerelle, vous pouvez exécuter la commande ps. Par exemple,

ps -ef | grep java

Cette commande ps affiche des informations sur le processus Java, notamment le numéro de port, le fichier jar, le fichier journal, l'ID du processus Java et la commande qui a lancé le processus Java.

Voici un exemple du résultat de la commande :

mlimbpr15:~ mli$ ps -ef | grep java
17182 45402 26852   0 12:12PM ??         0:00.00 sh -c java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log 2>&1
17182 45403 45402   0 12:12PM ??         0:00.22 /usr/bin/java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log
502 45412 45365   0 12:12PM ttys000    0:00.00 grep java

Dans Windows, vous pouvez consulter le gestionnaire des tâches pour trouver des informations sur le processus de passerelle.

3. Lancement et arrêt de la passerelle

Il y a deux façons de lancer et d'arrêter la passerelle :

1. Par le biais du SMP
2. Utilisation du terminal

A. Par le biais du SMP

Vous pouvez lancer et arrêter la passerelle dans le SMP en accédant à [System Administration] -> [Configuration] -> [Connectivity] -> [JDBC Gateway Server].

B. Utilisation du terminal

Sur les machines Unix, vous pouvez également démarrer la passerelle depuis le terminal. Comme nous l'avons vu dans la section précédente, le résultat de ps -ef | grep java contient la commande qui a démarré le processus Java, qui dans l'exemple ci-dessus est le suivant:

java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log

Pour arrêter la passerelle depuis le terminal, vous pouvez tuer le processus. L'ID du processus Java est le deuxième chiffre de la ligne qui contient la commande ci-dessus, dans l'exemple ci-dessus c'est 45402. Ainsi, pour arrêter la passerelle, vous pouvez exécuter :

kill 45402

4. Écrire un programme Java

Exécuter un programme Java pour se connecter à une base de données descendante est un excellent moyen de tester la connexion, de vérifier la requête et d'aider à isoler la cause d'un problème donné. Je joins un exemple de programme Java qui établit une connexion avec SQL Server et imprime une liste de tous les tableaux. J'expliquerai pourquoi cela peut être utile dans la section suivante.

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.lang.reflect.Method;
import java.io.InputStream;
import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import javax.sql.*;

// Auteur : Vicky Li
// Ce programme établit une connexion avec le serveur SQL et récupère tous les tableaux. Le résultat est une liste de tableaux.

public class TestConnection {
    public static void main(String[] args) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //please replace url, username, and password with the correct parameters
            Connection conn = DriverManager.getConnection(url,username,password);

            System.out.println("connected");

            DatabaseMetaData meta = conn.getMetaData();
            ResultSet res = meta.getTables(null, null, null, new String[] {"TABLE"});
            System.out.println("List of tables: ");
            while (res.next()) {
                System.out.println(
                    "   " + res.getString("TABLE_CAT") +
                    ", " + res.getString("TABLE_SCHEM") +
                    ", " + res.getString("TABLE_NAME") +
                    ", " + res.getString("TABLE_TYPE")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Pour exécuter ce programme Java (ou tout autre programme Java), vous devez d'abord compiler le fichier .java, qui dans notre cas s'appelle TestConnection.java. Ensuite, un nouveau fichier sera généré au même endroit, que vous pourrez ensuite exécuter avec la commande suivante sur un système UNIX :

java -cp "<path to driver>/sqljdbc4.jar:lib/*:." TestConnection

Dans Windows, vous pouvez exécuter la commande suivante :

java -cp "<path to driver>/sqljdbc4.jar;lib/*;." TestConnection

5. Suivi d'une trace de jstack

Comme son nom l'indique, jstack imprime l'arborescence des appels de procédure Java. Cet outil peut devenir pratique lorsque vous avez besoin de mieux comprendre ce que fait le processus Java. Par exemple, si vous voyez le processus de la passerelle s'accrocher à un certain message dans le journal des passerelles, vous pourriez vouloir recueillir une trace jstack. Je tiens à souligner que jstack est un outil de bas niveau qui ne devrait être utilisé que lorsque d'autres méthodes, comme l'analyse du journal des passerelles, ne résolvent pas le problème.

Avant de collecter une trace jstack, vous devez vous assurer que le JDK est installé. Voici la commande pour collecter une trace jstack :

jstack -F <pid> > /<path to file>/jstack.txt

où le pid est l'ID du processus de la passerelle, qui peut être obtenu en exécutant la commande ps, telle que ps -ef | grep java. Pour plus d'informations sur la façon de trouver le pid, veuillez consulter Lancement et arrêt de la passerelle.

Maintenant, voici quelques considérations spéciales pour les machines Red Hat. Dans le passé, il y a eu des problèmes pour attacher jstack au processus de la passerelle JDBC (ainsi qu'au processus du service métier de la passerelle Java lancé par Ensemble) sur certaines versions de Red Hat, donc la meilleure façon de collecter une trace jstack sur Red Hat est de lancer le processus de la passerelle manuellement. Pour les instructions, veuillez consulter Collecter une trace jstack sur Red Hat.

Types courants de problèmes et approches pour les résoudre

1. Problème : Java n'est pas installé correctement

Dans cette situation, vérifiez la version de Java et les variables d'environnement.

Pour vérifier la version de Java, vous pouvez exécuter la commande suivante à partir d'un terminal :

java -version

Si vous obtenez l'erreur java : Command not found, cela signifie que le processus Cache ne peut pas trouver l'emplacement des exécutables Java. Cela peut généralement être résolu en plaçant les exécutables Java dans le PATH. Si vous rencontrez des problèmes, n'hésitez pas à contacter le WRC (Centre de réponse global).

2. Problème : échec de la connexion

Un bon diagnostic des échecs de connexion est la vérification du lancement du processus de la passerelle. Vous pouvez le faire en vérifiant le journal de la passerelle ou le processus de la passerelle. Sur les versions modernes, vous pouvez également aller sur le SMP et visiter [System Administration] -> [Configuration] -> [Connectivity] -> [JDBC Gateway Server], et vérifier si la page affiche "JDBC Gateway is running".

Si le processus de passerelle ne s'exécute pas, il est probable que Java n'est pas installé correctement ou que vous utilisez le mauvais port ; si le processus de passerelle s'exécute, il est probable que les paramètres de connexion sont incorrects.

Dans le premier cas, veuillez vous reporter à la section précédente et vérifiez le numéro de port. Je discuterai plus en détail de la deuxième situation ici.

Il est de la responsabilité du client d'utiliser les paramètres de connexion corrects :

• nom d'utilisateur
• mot de passe
• nom du pilote
• URL
• chemin de classe

Vous pouvez vérifier si vous avez les bons paramètres de l'une des trois façons suivantes :

• Utilisez le bouton "Test Connection" après avoir sélectionné un nom de connexion dans [System Administration] -> [Configuration] -> [Connectivity] -> [SQL Gateway Connections]. Note : pour les systèmes modernes, "Test Connection" donne des messages d'erreur utiles ; pour les systèmes plus anciens, le JDBC gateway log est nécessaire pour trouver plus d'informations sur l'échec.

• Exécutez la ligne de commande suivante depuis un terminal Caché pour tester la connexion :

    d $SYSTEM.SQLGateway.TestConnection(<connection name>)
  

• Exécutez un programme Java pour établir une connexion. Le programme que vous écrivez peut être similaire à l' example dont nous avons parlé précédemment.

3. Problème : décalage entre la façon dont Caché comprend JDBC et la façon dont la base de données distante comprend JDBC, par exemple :

• problèmes de type de données
• procédure stockée avec des paramètres de sortie
• flux

Pour cette catégorie, il est souvent plus utile de travailler avec le WRC (Centre de réponse global). Voici ce que nous faisons souvent pour déterminer si le problème se situe dans notre code interne ou dans la base de données distante (ou dans le pilote) :

• regarder les journaux et analyser ce qui est envoyé
• reproduire le problème en dehors de Caché en écriture d'un programme java.

Remarque

Le service commercial de la passerelle Java

Le nom de la classe du Service Métier d' Ensemble est EnsLib.JavaGateway.Service, et la classe de l'adaptateur est EnsLib.JavaGateway.ServiceAdapter. La session Ensemble crée d'abord une connexion avec le serveur Java Gateway, qui est un processus Java. L'architecture est similaire à celle de la passerelle JDBC SQL, sauf que le processus Java est géré par l'opération commerciale. Pour plus de détails, veuillez consulter la documentation.

Activation du journal du pilote

Pour activer le journal du pilote, vous devez ajouter un nom de fichier journal à la fin de la chaîne de connexion JDBC. Par exemple, si la chaîne de connexion originale ressemble à czci :

jdbc:Cache://127.0.0.1:1972/USER

Pour activer la journalisation, ajoutez un fichier (jdbc.log) à la fin de la chaîne de connexion, de sorte qu'elle ressemble à ceci :

jdbc:Cache://127.0.0.1:1972/USER/jdbc.log

Le fichier journal sera enregistré dans le répertoire de travail de l'application Java.

Activation de la journalisation de la passerelle Java dans Ensemble

Si vous utilisez le service métier de la passerelle Java dans Ensemble pour accéder à une autre base de données, vous devez, pour activer la journalisation, spécifier le chemin et le nom d'un fichier journal (par exemple, /tmp/javaGateway.log) dans le champ "Log File" du service de la passerelle Java. Veuillez noter que le chemin d'accès doit exister.

N'oubliez pas que la connexion de la passerelle Java utilisée par la production Ensemble est distincte des connexions utilisées par les tableaux liés ou d'autres productions. Ainsi, si vous utilisez Ensemble, vous devez collecter le journal dans le service de passerelle Java. Le code qui démarre le service de passerelle Java utilise le paramètre "Log File" dans Ensemble, et n'utilise pas le paramètre dans la passerelle Caché SQL dans le SMP comme décrit précédemment.

Récupération d'une trace jstack sur Red Hat

La clé ici est de lancer le processus de la passerelle manuellement, et la commande pour lancer la passerelle peut être obtenue en exécutant ps -ef | grep java. Vous trouverez ci-dessous les étapes complètes à suivre pour collecter une trace jstack sur Red Hat lors de l'exécution de la passerelle JDBC ou du service métier de la passerelle Java.

1. Assurez-vous que le JDK est installé.

2. Dans un terminal, exécutez ps -ef | grep java. Obtenez les deux informations suivantes à partir du résultat :

   • a. Copiez la commande qui a lancé la passerelle. Cela devrait ressembler à quelque chose comme ça : java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC2.log

   • b. Obtenez l'ID du processus Java (pid), qui est le deuxième chiffre de la ligne qui contient la commande ci-dessus.

3. Arrêtez le processus avec kill <pid>.

4. Exécutez la commande que vous avez copiée à l'étape 2.a. pour lancer manuellement un processus de passerelle.

5. Jetez un coup d'oeil au journal de la passerelle (dans notre exemple, il est situé dans /Applications/Cache20151/mgr/JDBC2.log) et assurez-vous que vous voyez des entrées comme >> LOAD_JAVA_CLASS: com.intersys.jdbc.CacheDriver. Cette étape est juste pour vérifier qu'un appel à la passerelle est effectué avec succès.

6. Dans un nouveau terminal, exécutez ps -ef | grep java pour obtenir le pid du processus de la passerelle.

7. Rassemblez une trace jstack : jstack -F <pid> > /tmp/jstack.txt

Pour SQL, null et la chaîne vide ('') sont distinguées. Chaque méthode de définition/réception est la suivante.

(1) NULL

【SQL】

【InterSystems ObjectScript】

(2) Chaîne vide ('')

【SQL】

【InterSystems ObjectScript】

Pour plus de détails, veuillez consulter les documents suivants.

NULL and the Empty String (IRIS)
NULL and the Empty String (Caché)

La recherche d'images comme celle de Google est une fonctionnalité intéressante qui m'émerveille - comme presque tout ce qui est lié au traitement des images.

Il y a quelques mois, InterSystems a publié un aperçu de Python Embedded. Comme Python dispose de nombreuses librairies pour le traitement d'images, j'ai décidé de lancer ma propre tentative pour jouer avec une sorte de recherche d'images - une version beaucoup plus modeste en fait :-)

Pour réaliser un système de recherche d'images, il faut d'abord sélectionner un ensemble de caractéristiques à extraire des images - ces caractéristiques sont également appelées descripteurs. L'étendue de chaque composante de ces descripteurs crée ce qu'on appelle un espace de caractéristiques, et chaque instance de cet espace est appelée un vecteur. Le nombre d de composantes nécessaires pour décrire les vecteurs, définit la dimension de l'espace des caractéristiques et des vecteurs, appelé d-dimensionnel.

Figure 1 - Un espace caractéristique tridimensionnel et un vecteur descripteur dans cet espace.

Credits: https://tinyurl.com/ddd76dln

Une fois l'ensemble des descripteurs définis, tout ce que vous avez à faire pour rechercher une image dans la base de données est d'extraire les mêmes descripteurs d'une image à rechercher et de les comparer aux descripteurs des images de la base de données - qui ont été précédemment extraits.

Dans ce travail, on a simplement utilisé la couleur dominante de l'image comme descripteur (j'ai dit que c'était une version modeste...). Comme une représentation RVB des couleurs a été utilisée, l'espace caractéristique est un espace tridimensionnel - 3d en abrégé. Chaque vecteur dans un tel espace a 3 composantes - (r,g,b), dans la gamme [0, 255].

Figure 2 - L'espace tridimensionnel des caractéristiques RVB

Credits: https://www.baslerweb.com/fp-1485687434/media/editorial/content_images/faqs/faq_RGB_1.gif

En traitement du signal, il est très fréquent d'avoir des espaces à n dimensions avec des valeurs de n bien supérieures à 3. En fait, vous pouvez combiner un grand nombre de descripteurs dans un même vecteur afin d'obtenir une meilleure précision. C'est ce qu'on appelle la sélection de caractéristiques et c'est une étape très importante dans les tâches de classification/reconnaissance.

Il est également courant de normaliser la plage de dimensions en [0, 1], mais pour des raisons de simplicité, ce travail utilise la plage par défaut [0, 255].

L'avantage de modéliser des caractéristiques sous forme de vecteurs est la possibilité de les comparer à travers des métriques de distance. Il existe de nombreuses distances, chacune ayant ses avantages et ses inconvénients, selon que l'on recherche la performance ou la précision. Dans ce travail, j'ai choisi des distances faciles à calculer - manhattan et chebyshev, qui sont essentiellement des différences absolues avec une précision raisonnable.

Figure 3 - Représentation de certains paramètres de distance

Credits: https://i0.wp.com/dataaspirant.com/wp-content/uploads/2015/04/cover_post_final.png

Index fonctionnel

Mais il ne s'agit que des outils nécessaires pour comparer les images en fonction de leur contenu. Si vous ne disposez pas d'un langage de requête comme SQL, vous vous retrouverez avec des méthodes et des paramètres de recherche fastidieux... De plus, en utilisant SQL, vous pouvez combiner cet index avec d'autres opérateurs bien connus, créant ainsi des requêtes complexes.

C'est ici où Functional Index d'InterSystems est très utile.

Un index fonctionnel est une classe qui implémente la classe abstraite %Library.FunctionalIndex qui implémente certaines méthodes afin de gérer la tâche d'indexation dans une instruction SQL. Ces méthodes traitent essentiellement les insertions, les suppressions et les mises à jour.

/// Indexation fonctionnelle permettant d'optimiser les requêtes sur les données d'image
Class dc.multimodel.ImageIndex.Index Extends %Library.FunctionalIndex [ System = 3 ]
{

/// Cardinalité de l'espace des caractéristiques
/// Comme cette classe est destinée à indexer l'image dans l'espace RVB, sa cardinalité est de 3
Paramètre Cardinalité = 3 ;

/// Cette méthode est invoquée lorsqu'une instance existante d'une classe est supprimée.
ClassMethod DeleteIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
	If (%mode '= "method") {
		$$$GENERATE("Set indexer = ##class(dc.multimodel.ImageIndex.Indexer).GetInstance("""_%class_""", """_%property_""")")
		$$$GENERATE("Set indexer.Cardinality = "_..#Cardinality)
		$$$GENERATE("Do indexer.Delete(pID, pArg...)")
	}
	Return $$$OK
}

ClassMethod Find(pSearch As %Binary) As %Library.Binary [ CodeMode = generator, ServerOnly = 1, SqlProc ]
{
	If (%mode '= "method") {
		$$$GENERATE("Set result = """"")
		$$$GENERATE("Set result = ##class(dc.multimodel.ImageIndex.SQLFind).%New()")
		$$$GENERATE("Set indexer = ##class(dc.multimodel.ImageIndex.Indexer).GetInstance("""_%class_""", """_%property_""")")
		$$$GENERATE("Set indexer.Cardinality = "_..#Cardinality)
		$$$GENERATE("Set result.Indexer = indexer")
		$$$GENERATE("Do result.PrepareFind(pSearch)")
		$$$GENERATE("Return result")
	}
	Return $$$OK
}

/// Cette méthode est invoquée lorsqu'une nouvelle instance d'une classe est insérée dans la base de données.
ClassMethod InsertIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
	If (%mode '= "method") {
		$$$GENERATE("Set indexer = ##class(dc.multimodel.ImageIndex.Indexer).GetInstance("""_%class_""", """_%property_""")")
		$$$GENERATE("Set indexer.Cardinality = "_..#Cardinality)
		$$$GENERATE("Do indexer.Insert(pID, pArg...)")
	}
	Return $$$OK
}

ClassMethod PurgeIndex() [ CodeMode = generator, ServerOnly = 1 ]
{
	If (%mode '= "method") {
		$$$GENERATE("Set indexer = ##class(dc.multimodel.ImageIndex.Indexer).GetInstance("""_%class_""", """_%property_""")")
		$$$GENERATE("Set indexer.Cardinality = "_..#Cardinality)
		$$$GENERATE("Set indexGbl = indexer.GetIndexLocation()")
		$$$GENERATE("Do indexer.Purge()")
	}
	Return $$$OK
}

/// Cette méthode est invoquée lorsqu'une instance existante d'une classe est mise à jour.
ClassMethod UpdateIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
	If (%mode '= "method") {
		$$$GENERATE("Set indexer = ##class(dc.multimodel.ImageIndex.Indexer).GetInstance("""_%class_""", """_%property_""")")
		$$$GENERATE("Set indexer.Cardinality = "_..#Cardinality)
		$$$GENERATE("Do indexer.Update(pID, pArg...)")
	}
	Return $$$OK
}

}

J'ai caché une partie du code d'implémentation pour des raisons de lisibilité ; vous pouvez consulter le code dans le lien OpenExchange.

Une autre classe abstraite doit être implémentée, c'est %SQL.AbstractFind, afin de rendre disponible l'utilisation de l'opérateur %FIND pour demander au moteur SQL d'utiliser votre index personnalisé.

Une explication beaucoup plus détaillée et conviviale des index fonctionnels est donnée par @alexander-koblov qui constitue également un excellent exemple d'index fonctionnel. Je vous recommande vivement de le lire.

Si vous souhaitez aller plus loin, vous pouvez jouer avec le code source des index %iFind et %UIMA index.

Dans ce travail, j'ai configuré une classe de test de persistance simple, où le chemin des images est stocké, et un index personnalisé pour la recherche d'images est défini pour ce champ.

Class dc.multimodel.ImageIndex.Test Extends %Persistent
{

Property Name As %String;

Property ImageFile As %String(MAXLEN = 1024);

Index idxName On Name [ Type = bitmap ];

Index idxImageFile On (ImageFile) As dc.multimodel.ImageIndex.Index;

Notez que idxImageFile est un index personnalisé (dc.multimodel.ImageIndex.Index) pour le champ Image (qui stocke le chemin de l'image).

Le tour de Python (et COS) !

Ainsi, les classes abstraites d'index fonctionnel vous donneront les points d'entrée où vous pourrez effectuer l'extraction de caractéristiques et la recherche lors de l'exécution des instructions SQL. Maintenant, c'est au tour de Python !

Vous pouvez importer et exécuter le code Python dans un contexte COS en utilisant Python intégré. Par exemple, pour extraire la couleur dominante d'une image :

Method GetDominantColorRGB(pFile As %String, ByRef pVector) As %Status
{
  Set sc = $$$OK
  Try {
    Set json = ##class(%SYS.Python).Import("json")
    Set fastcolorthief = ##class(%SYS.Python).Import("fast_colorthief")
    Set imagepath = pFile
    Set dominantcolor = fastcolorthief."get_dominant_color"(imagepath, 1)
    Set vector = {}.%FromJSON(json.dumps(dominantcolor))
    Set n = ..Cardinality - 1
    For i = 0:1:n {
      Set pVector(i) = vector.%Get(i)
    }
  } Catch(e) {
    Set sc = e.AsStatus()
  }
  Return sc
}

Dans cette méthode, deux librairies Python sont importées (json et fast_colorthief). La librairie fast_colorthief renvoie une représentation Python de type tableau 3-d avec les valeurs de RGB ; l'autre librairie - json, sérialise ce tableau dans un %DynamicArray.

La couleur dominante est extraite pour chaque enregistrement qui est inséré ou mis à jour - une fois que l'index fonctionnel lève les appels aux méthodes InsertIndex et UpdateIndex en réponse aux insertions et mises à jour dans le tableau. Ces caractéristiques sont stockées dans l'index global du tableau :

Method Insert(pID As %CacheString, pArgs... As %Binary)
{
	// pArgs(1) has the image path
	$$$ThrowOnError(..GetDominantColor(pArgs(1), .rgb))
	Set idxGbl = ..GetIndexLocation()
	Set @idxGbl@("model", pID) = ""
  	Merge @idxGbl@("model", pID, "rgb") = rgb
  	Set @idxGbl@("last-modification") = $ZTIMESTAMP
}

Method Update(pID As %CacheString, pArg... As %Binary)
{
	// pArgs(1) has the image path
  	Set idxGbl = ..GetIndexLocation()
  	Do ..GetDominantColor(pArg(1), .rgb)
  	Kill @idxGbl@("model", pID)
  	Set @idxGbl@("model", pID) = ""
  	Merge @idxGbl@("model", pID, "rgb") = rgb
  	Set @idxGbl@("last-modification") = $ZTIMESTAMP
}

De la même manière, lorsque des enregistrements sont supprimés, l'index fonctionnel lance des appels aux méthodes DeleteIndex et PurgeIndex. À leur tour, les fonctionnalités doivent être supprimées de l'index global du tableau :

Method Delete(pID As %CacheString, pArg... As %Binary)
{
  	Set idxGbl = ..GetIndexLocation()
  	Kill @idxGbl@("model", pID)
  	Set @idxGbl@("last-modification") = $ZTIMESTAMP
}

Method Purge(pID As %CacheString, pArg... As %Binary)
{
  	Set idxGbl = ..GetIndexLocation()
  	Kill @idxGbl
  	Set @idxGbl@("last-modification") = $ZTIMESTAMP
}

L'index global est récupéré par introspection dans la classe persistante :

Method GetIndexLocation() As %String
{
	Set storage = ##class(%Dictionary.ClassDefinition).%OpenId(..ClassName).Storages.GetAt(1).IndexLocation
	Return $NAME(@storage@(..IndexName))
}

Lorsque les utilisateurs utilisent l'index dans les clauses WHERE, la méthode Find() est activée par l'index de la fonction. Les instructions de la requête sont transmises afin que vous puissiez les analyser et décider de ce qu'il faut faire. Dans ce travail, les paramètres sont sérialisés en JSON afin de faciliter leur analyse. Les paramètres de la requête ont la structure suivante :

SELECT ImageFile
FROM dc_multimodel_ImageIndex.Test
WHERE ID %FIND search_index(idxImageFile, '{"color_similarity":{"image":"/data/img/test/161074693598711.jpg","first":5,"strategy":"knn"}}')

Dans cette instruction, vous pouvez voir l'utilisation de l'opérateur %FIND et de la fonction search_index. C'est ainsi que SQL accède à notre index personnalisé.

Les paramètres de search_index définissent l'index à rechercher - idxImageFile, dans ce cas ; et la valeur à envoyer à l'index. Ici, l'index attend un objet JSON, avec une configuration d'objet définissant : (i) le chemin de l'image, (ii) une limite pour les résultats, et (iii) une stratégie de recherche.

Une stratégie de recherche est simplement l'algorithme à utiliser pour effectuer la recherche. Actuellement, deux stratégies sont mises en œuvre : (i) fullscan et (ii) knn, qui correspond à k-proches voisins.

La stratégie fullscan consiste simplement en une recherche exhaustive mesurant la distance entre l'image recherchée et chaque image stockée dans la base de données.

Method FullScanFindStrategy(ByRef pSearchVector, ByRef pResult) As %Status
{
	Set sc = $$$OK
	Try {
		Set idxGbl = ..Indexer.GetIndexLocation()
		Set rankGbl = ..Indexer.GetRankLocation()

		Set id = $ORDER(@idxGbl@("model", ""))
		While (id '= "") {
			If ($ISVALIDNUM(id)) {
				Merge vector = @idxGbl@("model", id, "rgb")
				Set distance = ..Indexer.GetL1Distance(.pSearchVector, .vector)
				Set result(distance, id) = ""
			}
			Set id = $ORDER(@idxGbl@("model", id))
		}

		Kill @rankGbl@(..ImagePath, ..FindStrategy)
		If (..First '= "") {
			Set c = 0
			Set distance = $ORDER(result(""))
			While (distance '= "") && (c < ..First) {
				Merge resultTmp(distance) = result(distance)

				Set id = $ORDER(result(distance, ""))
				While (id '= "") {
					Set @rankGbl@(..ImagePath, ..FindStrategy, id) = distance
					Set id = $ORDER(result(distance, id))
				}

				Set c = c + 1
				Set distance = $ORDER(result(distance))
			}
			Kill result
			Merge result = resultTmp
		}

		Merge pResult = result
	}
	Catch ex {
		Set sc = ex.AsStatus()
	}
	Return sc
}

La stratégie KNN utilise une approche plus sophistiquée. Elle utilise une librairie Python pour créer une structure arborescente appelée Ball Tree. Une telle arborescence convient à une recherche efficace dans un espace à n dimensions.

Method KNNFindStrategy(ByRef pSearchVector, ByRef pResult) As %Status
{
	Do ..Log(" ------ KNNFindStrategy ------ ")
	Set sc = $$$OK
	Try {
		Set idxGbl = ..Indexer.GetIndexLocation()
		Set rankGbl = ..Indexer.GetRankLocation()

		Set json = ##class(%SYS.Python).Import("json")
		Set knn = ##class(%SYS.Python).Import("knn")

		Set first = ..First
		Set k = $GET(first, 5)

		Set n = ..Indexer.Cardinality - 1
		Set x = ""
		For i = 0:1:n {
			Set $LIST(x, * + 1) = pSearchVector(i)
		}
		Set x = "[["_$LISTTOSTRING(x, ",")_"]]"

		$$$ThrowOnError(..CreateOrUpdateKNNIndex())
		Set ind = knn.query(x, k, idxGbl)
		Set ind = {}.%FromJSON(json.dumps(ind.tolist()))
		Set ind = ind.%Get(0)

		Kill result
		Kill @rankGbl@(..ImagePath, ..FindStrategy)
		Set n = k - 1
		For i=0:1:n {
			Set id = ind.%Get(i)
			Set result(i, id) = ""
			Set @rankGbl@(..ImagePath, ..FindStrategy, id) = i
		}
		Merge pResult = result
	}
	Catch ex {
		Set sc = ex.AsStatus()
	}
	Return sc
}

Le code Python pour générer une arborescence Ball Tree est présenté ci-dessous :

from sklearn.neighbors import BallTree
import numpy as np
import pickle
import base64
import irisnative

def get_iris():
  ip = "127.0.0.1"
  port = 1972
  namespace = "USER"
  username = "superuser"
  password = "SYS"

  connection = irisnative.createConnection(ip,port,namespace,username,password)
  dbnative = irisnative.createIris(connection)

  return (connection, dbnative)

def release_iris(connection):
  connection.close()

def normalize_filename(filename):
  filename = filename.encode('UTF-8')
  return base64.urlsafe_b64encode(filename).decode('UTF-8')

def create_index(index_global, cardinality):
  connection, dbnative = get_iris()
  X = get_data(dbnative, index_global, cardinality)
  tree = BallTree(X, metric = "chebyshev")
  filename = f"/tmp/${normalize_filename(index_global)}.p"
  pickle.dump(tree, open(filename, "wb"))
  release_iris(connection)
  return tree

def get_data(dbnative, index_global, cardinality):
  X = []
  iter_ = dbnative.iterator(index_global, "model")
  for subscript, value in iter_.items():
    id_ = subscript
    v = []
    for i in range(cardinality):
      v.append(
        dbnative.get(index_global, "model", id_, "rgb", i) / 255
      )
    X.append(v)
  return X

def query(x, k, index_global):
  filename = f"/tmp/${normalize_filename(index_global)}.p"
  tree = pickle.load(open(filename, "rb"))
  x = eval(x)
  x_ = [xi / 255 for xi in x[0]]
  dist, ind = tree.query([x_], k)
  return ind

Lorsqu'une image est recherchée, l'index personnalisé appelle la méthode de requête de l'objet Ball Tree en Python. Vous pouvez également noter l'utilisation de l'API native d'IRIS afin d'accéder aux valeurs RVB globales de l'index pour la construction de l'arborescence Ball Tree.

Pour ordonner les images par similarité, il a été développé une procédure SQL qui traverse une globale stockant les distances précédemment calculées pour chaque image recherchée :

Method DiffRank(pSearch As %Binary, pId As %String) As %Float
{
	Set search = {}.%FromJSON(pSearch)
	If (search.%IsDefined("color_similarity")) {
		Set config = search.%Get("color_similarity")
		Set imagePath = config.%Get("image")
		If (config.%IsDefined("strategy")) {
			Set findStrategy = config.%Get("strategy")
		}
		Set rankGbl = ..Indexer.GetRankLocation()
		Set rank = $GET(@rankGbl@(imagePath, findStrategy, pId))
		Return rank
	}
	Return ""
}

Vous pouvez donc modifier l'instruction SQL pour classer les résultats par similarité :

SELECT ImageFile, dc_multimodel_ImageIndex.Test_idxImageFileDiffRank('{"color_similarity":{"image":"/data/img/test/161074693598711.jpg","first":5,"strategy":"knn"}}', id) AS DiffRank
FROM dc_multimodel_ImageIndex.Test
WHERE ID %FIND search_index(idxImageFile, '{"color_similarity":{"image":"/data/img/test/161074693598711.jpg","first":5,"strategy":"knn"}}')
ORDER BY DiffRank

Conclusion

L'objectif de ce travail était de montrer comment combiner la définition d'index fonctionnels dans COS avec des appels au code Python utilisant leurs étonnantes bibliothèques. De plus, en utilisant cette technique, vous pouvez accéder à des fonctionnalités complexes fournies par les librairies Python dans des instructions SQL, ce qui vous permet d'ajouter de nouvelles fonctionnalités à vos applications.

Les modèles de données objet et relationnel de la base de données Caché supportent trois types d'index, à savoir standard, bitmap et bitslice. En plus de ces trois types natifs, les développeurs peuvent déclarer leurs propres types d'index personnalisés et les utiliser dans toutes les classes depuis la version 2013.1. Par exemple, les index de texte iFind utilisent ce mécanisme.

Un Custom Index Type est une classe qui implémente les méthodes de l'interface %Library.FunctionalIndex pour effectuer des insertions, des mises à jour et des suppressions. Vous pouvez spécifier une telle classe comme type d'index lorsque vous déclarez un nouvel index.

Exemple:

Property A As %String;
Property B As %String;
Index someind On (A,B) As CustomPackage.CustomIndex;

La classe CustomPackage.CustomIndex est la classe même qui implémente les index personnalisés.

Par exemple, analysons le petit prototype d'un index à base de quadtrees pour les données spatiales qui a été développé pendant le Hackathon par notre équipe : Andrey Rechitsky, Aleksander Pogrebnikov et moi-même. (Le Hackathon a été organisé dans le cadre de la formation annuelle de l'école d'innovation d'InterSystems Russie, et nous remercions tout particulièrement le principal inspirateur du Hackathon, Timur Safin.)

Dans cet article, je ne vais pas parler des [quadtrees] (https://en.wikipedia.org/wiki/Quadtree) et de la façon de les utiliser. Nous allons plutôt examiner comment créer une nouvelle classe qui implémente l'interface %Library.FunctionalIndex pour l'implémentation de l'algorithme quadtree existant. Dans notre équipe, cette tâche a été confiée à Andrey. Andrey a créé la classe SpatialIndex.Indexer avec deux méthodes :

• Insert(x, y, id)
• Delete(x, y, id)

Lors de la création d'une nouvelle instance de la classe SpatialIndex.Indexer, il était nécessaire de définir un nom de nœud global dans lequel nous stockons les données d'index. Tout ce que j'avais à faire était de créer la classe SpatialIndex.Index avec les méthodes InsertIndex, UpdateIndex, DeleteIndex et PurgeIndex. Les trois premières méthodes acceptent l'Id de la chaîne à modifier et les valeurs indexées exactement dans le même ordre que celui dans lequel elles ont été définies dans la déclaration de l'index au sein de la classe correspondante. Dans notre exemple, les arguments d'entrée sont pArg(1) — A and pArg(2) — B.

Class SpatialIndex.Index Extends %Library.FunctionalIndex [ System = 3 ]
{

ClassMethod InsertIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
    if %mode'="method" {
        set IndexGlobal = ..IndexLocation(%class,%property)
        $$$GENERATE($C(9)_"set indexer = ##class(SpatialIndex.Indexer).%New($Name("_IndexGlobal_"))")
        $$$GENERATE($C(9)_"do indexer.Insert(pArg(1),pArg(2),pID)")
    }
}

ClassMethod UpdateIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
    if %mode'="method" {
        set IndexGlobal = ..IndexLocation(%class,%property)
        $$$GENERATE($C(9)_"set indexer = ##class(SpatialIndex.Indexer).%New($Name("_IndexGlobal_"))")
        $$$GENERATE($C(9)_"do indexer.Delete(pArg(3),pArg(4),pID)")
        $$$GENERATE($C(9)_"do indexer.Insert(pArg(1),pArg(2),pID)")
    }
}
ClassMethod DeleteIndex(pID As %CacheString, pArg... As %Binary) [ CodeMode = generator, ServerOnly = 1 ]
{
    if %mode'="method" {
        set IndexGlobal = ..IndexLocation(%class,%property)
        $$$GENERATE($C(9)_"set indexer = ##class(SpatialIndex.Indexer).%New($Name("_IndexGlobal_"))")
        $$$GENERATE($C(9)_"do indexer.Delete(pArg(1),pArg(2),pID)")
    }
}

ClassMethod PurgeIndex() [ CodeMode = generator, ServerOnly = 1 ]
{
    if %mode'="method" {
        set IndexGlobal = ..IndexLocation(%class,%property)
        $$$GENERATE($C(9)_"kill " _ IndexGlobal)
    }
}

ClassMethod IndexLocation(className As %String, indexName As %String) As %String
{
    set storage = ##class(%Dictionary.ClassDefinition).%OpenId(className).Storages.GetAt(1).IndexLocation
    quit $Name(@storage@(indexName))
}

}

IndexLocation est une méthode supplémentaire qui renvoie le nom du nœud dans le global où la valeur de l'index est enregistrée.

Analysons maintenant la classe de test dans laquelle l'index du type SpatialIndex.Index est utilisé :

Class SpatialIndex.Test Extends %Persistent
{
  Property Name As %String(MAXLEN = 300);
  Property Latitude As %String;
  Property Longitude As %String;
  Index coord On (Latitude, Longitude) As SpatialIndex.Index;
}

Lorsque la classe SpatialIndex.Test est compilée, le système génère les méthodes suivantes dans le code INT pour chaque index du type SpatialIndex.Index :

zcoordInsertIndex(pID,pArg...) public {
    set indexer = ##class(SpatialIndex.Indexer).%New($Name(^SpatialIndex.TestI("coord")))
    do indexer.Insert(pArg(1),pArg(2),pID) }
zcoordPurgeIndex() public {
    kill ^SpatialIndex.TestI("coord") }
zcoordSegmentInsert(pIndexBuffer,pID,pArg...) public {
    do ..coordInsertIndex(pID, pArg...) }
zcoordUpdateIndex(pID,pArg...) public {
    set indexer = ##class(SpatialIndex.Indexer).%New($Name(^SpatialIndex.TestI("coord")))
    do indexer.Delete(pArg(3),pArg(4),pID)
    do indexer.Insert(pArg(1),pArg(2),pID)
}

Les méthodes %SaveData, %DeleteData, %SQLInsert, %SQLUpdate et %SQLDelete appellent les méthodes de notre index. Par exemple, le code suivant fait partie de la méthode %SaveData :

if insert {
     ...
     do ..coordInsertIndex(id,i%Latitude,i%Longitude,"")
      ...
 } else {
      ...
     do ..coordUpdateIndex(id,i%Latitude,i%Longitude,zzc27v3,zzc27v2,"")
      ...
 }

Un exemple pratique est toujours mieux que la théorie, vous pouvez donc télécharger les fichiers depuis notre entrepôt : https://github.com/intersystems-ru/spatialindex/tree/no-web-interface. Ceci est un lien vers une branche sans l'interface web. Pour utiliser ce code :

1. Importez les classes
2. Décompresser RuCut.zip
3. Importez les données en utilisant les appels suivants :

   do $system.OBJ.LoadDir("c:\temp\spatialindex","ck")
   do ##class(SpatialIndex.Test).load("c:\temp\rucut.txt")
   

Le fichier rucut.txt contient des données sur 100 000 villes et villages de Russie, avec leur nom et leurs coordonnées. La méthode Load lit chaque chaîne de caractères du fichier, puis l'enregistre comme une instance distincte de la classe SpatialIndex.Test. Une fois la méthode Load exécutée, le fichier global ^SpatialIndex.TestI("coord") contient un quadtree avec les coordonnées de latitude et de longitude.

Et maintenant, exécutons des requêtes !

La construction des index n'est pas la partie la plus intéressante. Nous voulons utiliser notre index dans diverses requêtes. Dans Caché, il existe une syntaxe standard pour les index non standard :

SELECT *
FROM SpatialIndex.Test
WHERE %ID %FIND search_index(coord, 'window', 'minx=56,miny=56,maxx=57,maxy=57')

%ID %FIND search_index est la partie fixe de la syntaxe. Ensuite, il y a le nom de l'index, coord - et notez qu'aucun guillemet n'est nécessaire. Tous les autres paramètres ('window', 'minx=56,miny=56,maxx=57,maxy=57') sont transmis à la méthode Find, qui doit également être définie dans la classe du type d'index (qui, dans notre exemple, est SpatialIndex.Index) :

ClassMethod Find(queryType As %Binary, queryParams As %String) As %Library.Binary [ CodeMode = generator, ServerOnly = 1, SqlProc ]
{
    if %mode'="method" {
        set IndexGlobal = ..IndexLocation(%class,%property)
        set IndexGlobalQ = $$$QUOTE(IndexGlobal)
        $$$GENERATE($C(9)_"set result = ##class(SpatialIndex.SQLResult).%New()")
        $$$GENERATE($C(9)_"do result.PrepareFind($Name("_IndexGlobal_"), queryType, queryParams)")
        $$$GENERATE($C(9)_"quit result")
    }
}

Dans cet exemple de code, nous avons seulement deux paramètres - queryType et queryParams, mais vous pouvez ajouter autant de paramètres que vous le souhaitez.

Lorsque vous compilez une classe dans laquelle la méthode SpatialIndex.Index est utilisée, la méthode Find génère une méthode supplémentaire appelée z<IndexName>Find, qui est ensuite utilisée pour exécuter des requêtes SQL :

zcoordFind(queryType,queryParams) public { Set:'$isobject($get(%sqlcontext)) %sqlcontext=##class(%Library.ProcedureContext).%New()
    set result = ##class(SpatialIndex.SQLResult).%New()
    do result.PrepareFind($Name(^SpatialIndex.TestI("coord")), queryType, queryParams)
    quit result }

La méthode Find doit retourner une instance de la classe qui implémente l'interface %SQL.AbstractFind. Les méthodes de cette interface, NextChunk et PreviousChunk, renvoient des chaînes de bits par tranches de 64 000 bits chacune. Lorsqu'un enregistrement avec un certain ID répond aux critères de sélection, le bit correspondant (chunk_number * 64000 + position_number_within_chunk) est mis à 1.

Class SpatialIndex.SQLResult Extends %SQL.AbstractFind
{

Property ResultBits [ MultiDimensional, Private ];

Method %OnNew() As %Status [ Private, ServerOnly = 1 ]
{
    kill i%ResultBits
    kill qHandle
    quit $$$OK
}


Method PrepareFind(indexGlobal As %String, queryType As %String, queryParams As %Binary) As %Status
{
    if queryType = "window" {
        for i = 1:1:4 {
            set item = $Piece(queryParams, ",", i)
            set IndexGlobal = ..IndexLocation(%class,%property)
            $$$GENERATE($C(9)_"kill " _ IndexGlobal)   set param = $Piece(item, "=", 1)
            set value = $Piece(item, "=" ,2)
            set arg(param) = value
        }
        set qHandle("indexGlobal") = indexGlobal
        do ##class(SpatialIndex.QueryExecutor).InternalFindWindow(.qHandle,arg("minx"),arg("miny"),arg("maxx"),arg("maxy"))
        set id = ""
        for  {
            set id = $O(qHandle("data", id),1,idd)
            quit:id=""
            set tChunk = (idd\64000)+1, tPos=(idd#64000)+1
            set $BIT(i%ResultBits(tChunk),tPos) = 1
        }
    }
    quit $$$OK
}

Method ContainsItem(pItem As %String) As %Boolean
{
    set tChunk = (pItem\64000)+1, tPos=(pItem#64000)+1
    quit $bit($get(i%ResultBits(tChunk)),tPos)
}

Method GetChunk(pChunk As %Integer) As %Binary
{
    quit $get(i%ResultBits(pChunk))
}

Method NextChunk(ByRef pChunk As %Integer = "") As %Binary
{
    set pChunk = $order(i%ResultBits(pChunk),1,tBits)
    quit:pChunk="" ""
    quit tBits
}

Method PreviousChunk(ByRef pChunk As %Integer = "") As %Binary
{
    set pChunk = $order(i%ResultBits(pChunk),-1,tBits)
    quit:pChunk="" ""
    quit tBits
}
}

Comme le montre l'exemple de code ci-dessus, la méthode InternalFindWindow de la classe SpatialIndex.QueryExecutor recherche les points situés dans le rectangle spécifié. Ensuite, les ID des lignes correspondantes sont écrits dans les bitsets dans la boucle FOR.

Dans notre projet Hackathon, Andrey a également implémenté la fonctionnalité de recherche pour les ellipses :

SELECT *
FROM SpatialIndex.Test
WHERE %ID %FIND search_index(coord,'radius','x=55,y=55,radiusX=2,radiusY=2')
and name %StartsWith 'Z'

Un peu plus à propos de %FIND

Le prédicat %FIND possède un paramètre supplémentaire, SIZE, qui aide le moteur SQL à estimer le nombre de lignes correspondantes. En fonction de ce paramètre, le moteur SQL décide d'utiliser ou non l'index spécifié dans le prédicat %FIND.

Par exemple, ajoutons l'index suivant dans la classe SpatialIndex.Test :

Index ByName on Name;

Maintenant, recompilons la classe et construisons cet index :

write ##class(SpatialIndex.Test).%BuildIndices($LB("ByName"))

Et enfin, lancez TuneTable :

do $system.SQL.TuneTable("SpatialIndex.Test", 1)

Voici le plan de la requête :

SELECT *
FROM SpatialIndex.Test
WHERE name %startswith 'za'
and %ID %FIND search_index(coord,'radius','x=55,y=55,radiusX=2,radiusY=2') size ((10))

Comme l'index coord est susceptible de retourner peu de lignes, le moteur SQL n'utilise pas l'index sur la propriété Name.

Il y a un plan différent pour la requête suivante :

SELECT *
FROM SpatialIndex.Test
WHERE name %startswith 'za'
and %ID %FIND search_index(coord,'radius','x=55,y=55,radiusX=2,radiusY=2') size ((1000))

Le moteur SQL utilise les deux index pour exécuter cette requête.

Et, comme dernier exemple, créons une requête qui utilise uniquement l'index sur le champ Name, puisque l'index coord renverra probablement environ 100 000 lignes et sera donc très peu utilisable :

SELECT *
FROM SpatialIndex.Test
WHERE name %startswith 'za'
and %ID %FIND search_index(coord,'radius','x=55,y=55,radiusX=2,radiusY=2') size ((100000))

Merci à tous ceux qui ont lu ou au moins parcouru cet article.

Outre les liens de documentation ci-dessous, vous pouvez également trouver utile d'examiner les implémentations alternatives des interfaces %Library.FunctionalIndex et %SQL.AbstractFind. Pour visualiser ces implémentations, ouvrez l'une de ces classes dans Caché Studio et choisissez Class > Inherited Classes dans le menu.

Liens:

• %FIND
• search_index
• %SQL.AbstractFind
• %Library.FunctionalIndex

IRIS External Table est un projet Open Source de la communauté InterSystems, qui vous permet d'utiliser des fichiers stockés dans le système de fichiers local et le stockage d'objets en nuage comme AWS S3, en tant que tables SQL.

Il peut être trouvé sur Github https://github.com/intersystems-community/IRIS-ExternalTable Open Exchange https://openexchange.intersystems.com/package/IRIS-External-Table et est inclus dans InterSystems Package Manager ZPM.

Pour installer External Table depuis GitHub, utilisez :

git clone https://github.com/antonum/IRIS-ExternalTable.git
iris session iris
USER>set sc = ##class(%SYSTEM.OBJ).LoadDir("<path-to>/IRIS-ExternalTable/src", "ck",,1)

Pour installer avec ZPM Package Manager, utilisez :

USER>zpm "install external-table"

Travailler avec des fichiers locaux

Créons un fichier simple qui ressemble à ceci :

a1,b1
a2,b2

Ouvrez votre éditeur préféré et créez le fichier ou utilisez simplement une ligne de commande sous linux/mac :

echo $'a1,b1\na2,b2' > /tmp/test.txt

Dans IRIS SQL, créez une table pour représenter ce fichier :

create table test (col1 char(10),col2 char(10))

Convertissez la table pour utiliser le stockage externe :

CALL EXT.ConvertToExternal(
    'test',
    '{
        "adapter":"EXT.LocalFile",
        "location":"/tmp/test.txt",
        "delimiter": ","
    }')

Et enfin - interrogez la table :

select * from test

Si tout fonctionne comme prévu, vous devriez voir un résultat comme celui-ci :

col1	col2
a1	b1
a2	b2

Retournez maintenant dans l'éditeur, modifiez le contenu du fichier et réexécutez la requête SQL. Ta-Da !!! Vous lisez les nouvelles valeurs de votre fichier local en SQL.

col1	col2
a1	b1
a2	b99

Lecture de données à partir de S3

Sur <https://covid19-lake.s3.amazonaws.com/index.html >, vous pouvez avoir accès à des données constamment mises à jour sur le COVID, stockées par AWS dans la réserve publique de données.

Essayons d'accéder à l'une des sources de données de cette réserve de données : s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states

Si vous avez installé l'outil de ligne de commande AWS, vous pouvez répéter les étapes ci-dessous. Sinon, passez directement à la partie SQL. Vous n'avez pas besoin d'installer quoi que ce soit de spécifique à AWS sur votre machine pour suivre la partie SQL.

$ aws s3 ls s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/
2020-12-04 17:19:10     510572 us-states.csv

$ aws s3 cp s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv .
download: s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv to ./us-states.csv

$ head us-states.csv
date,state,fips,cases,deaths
2020-01-21,Washington,53,1,0
2020-01-22,Washington,53,1,0
2020-01-23,Washington,53,1,0
2020-01-24,Illinois,17,1,0
2020-01-24,Washington,53,1,0
2020-01-25,California,06,1,0
2020-01-25,Illinois,17,1,0
2020-01-25,Washington,53,1,0
2020-01-26,Arizona,04,1,0

Nous avons donc un fichier avec une structure assez simple. Cinq champs délimités.

Pour exposer ce dossier S3 en tant que table externe - d'abord, nous devons créer une table "normal" avec la structure désirée :

-- create external table
create table covid_by_state (
    "date" DATE,
    "state" VARCHAR(20),
    fips INT,
    cases INT,
    deaths INT
)


Notez que certains noms de champs comme "Date" sont des mots réservés dans IRIS SQL et doivent être entourés de guillemets doubles.
Ensuite - nous devons convertir cette table "régulière" en table "externe", basé sur le godet AWS S3 et le type de CSV.

 -- convertissez le tableau en stockage externe
call EXT.ConvertToExternal(
    'covid_by_state',
    '{
    "adapter":"EXT.AWSS3",
    "location":"s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/",
    "type": "csv",
    "delimiter": ",",
    "skipHeaders": 1
    }'
)

Si vous regardez de près - les arguments des procédures EXT.ExternalTable sont le nom de la table et ensuite une chaîne JSON, contenant plusieurs paramètres, tels que l'emplacement pour rechercher les fichiers, l'adaptateur à utiliser, le délimiteur, etc. En plus de AWS S3 External Table supporte le stockage Azure BLOB, Google Cloud Buckets et le système de fichiers local. GitHub Repo contient des références pour la syntaxe et les options supportées pour tous les formats.

Et enfin - faites une requête sur le tableau :

-- faites une requête sur le tableau :
select top 10 * from covid_by_state order by "date" desc

[SQL]USER>>select top 10 * from covid_by_state order by "date" desc
2.	select top 10 * from covid_by_state order by "date" desc

date	état	fips	cas	morts
2020-12-06	Alabama	01	269877	3889
2020-12-06	Alaska	02	36847	136
2020-12-06	Arizona	04	364276	6950
2020-12-06	Arkansas	05	170924	2660
2020-12-06	California	06	1371940	19937
2020-12-06	Colorado	08	262460	3437
2020-12-06	Connecticut	09	127715	5146
2020-12-06	Delaware	10	39912	793
2020-12-06	District of Columbia	11	23136	697
2020-12-06	Florida	12	1058066	19176

L'interrogation des données de la tableau distant prend naturellement plus de temps que celle de la table "IRIS natif" ou global, mais ces données sont entièrement stockées et mises à jour sur le nuage et sont introduites dans IRIS en coulisse.

Explorons quelques autres caractéristiques de la table externe.

%PATH et tables, sur la base de plusieurs fichiers

Dans notre exemple, le dossier du godet ne contient qu'un seul fichier. Le plus souvent, il contient plusieurs fichiers de la même structure, où le nom de fichier identifie soit l'horodatage, soit le numéro de périphérique, soit un autre attribut que nous voulons utiliser dans nos requêtes.

Le champ %PATH est automatiquement ajouté à chaque table externe et contient le chemin d'accès complet au fichier à partir duquel la ligne a été récupérée.

select top 5 %PATH, * from covid_by_state

%PATH	date	state	fips	cases	deaths
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv	2020-01-21	Washington	53	1	0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv	2020-01-22	Washington	53	1	0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv	2020-01-23	Washington	53	1	0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv	2020-01-24	Illinois	17	1	0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv	2020-01-24	Washington	53	1	0

Vous pouvez utiliser ce champ %PATH dans vos requêtes SQL comme n'importe quel autre champ.

Données ETL vers des " tables réguliers "

Si votre tâche consiste à charger des données de S3 dans une table IRIS - vous pouvez utiliser l'External Table comme un outil ETL. Il suffit de le faire :

INSERT INTO internal_table SELECT * FROM external_table

Dans notre cas, si nous voulons copier les données COVID de S3 dans la table local :

--create local table
create table covid_by_state_local (
    "date" DATE,
    "state" VARCHAR(100),
    fips INT,
    cases INT,
    deaths INT
)
--ETL from External to Local table
INSERT INTO covid_by_state_local SELECT TO_DATE("date",'YYYY-MM-DD'),state,fips,cases,deaths FROM covid_by_state

JOIN entre IRIS tables natif et External Table. Requêtes fédérées

External Table est une table SQL. Il peut être joint à d'autres tables, utilisé dans des sous-sélections et des UNIONs. Vous pouvez même combiner la table IRIS "Régulier" et deux ou plusieurs tables externes provenant de sources différentes dans la même requête SQL.

Essayez de créer une table régulier, par exemple en faisant correspondre les noms d'État aux codes d'État, comme Washington - WA. Et joignez-la à notre table basé sur S3.

create table state_codes (name varchar(100), code char(2))
insert into state_codes values ('Washington','WA')
insert into state_codes values ('Illinois','IL')

select top 10 "date", state, code, cases from covid_by_state join state_codes on state=name

Remplacez 'join' par 'left join' pour inclure les lignes pour lesquelles le code d'état n'est pas défini. Comme vous pouvez le constater, le résultat est une combinaison de données provenant de S3 et de votre table IRIS natif.

Accès sécurisé aux données

La réserve de données AWS Covid est publique. N'importe qui peut y lire des données sans aucune authentification ou autorisation. Dans la vie réelle, vous souhaitez accéder à vos données de manière sécurisée, afin d'empêcher les étrangers de jeter un coup d'œil à vos fichiers. Les détails complets de la gestion des identités et des accès (IAM) d'AWS n'entrent pas dans le cadre de cet article. Mais le minimum, vous devez savoir que vous avez besoin au moins de la clé d'accès au compte AWS et du secret afin d'accéder aux données privées de votre compte. <https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys >

AWS utilise l'authentification par clé de compte/secrète autentification pour signer les demandes. https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys

Si vous exécutez IRIS External Table sur une instance EC2, la méthode recommandée pour gérer l'authentification est d'utiliser les rôles d'instance EC2 https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html IRIS External Table sera en mesure d'utiliser les permissions de ce rôle. Aucune configuration supplémentaire n'est requise.

Sur une instance locale/non EC2, vous devez spécifier AWS_ACCESS_KEY_ID et AWS_SECRET_ACCESS_KEY en spécifiant des variables d'environnement ou en installant et en configurant le client AWS CLI.

export AWS_ACCESS_KEY_ID=AKIAEXAMPLEKEY
export AWS_SECRET_ACCESS_KEY=111222333abcdefghigklmnopqrst

Assurez-vous que la variable d'environnement est visible dans votre processus IRIS. Vous pouvez le vérifier en exécutant :

USER>write $system.Util.GetEnviron("AWS_ACCESS_KEY_ID")

Il doit renvoi la valeur de clé.

ou installer AWS CLI, en suivant les instructions ici https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2-linux.html et exécuter :

aws configure

External Table sera alors en mesure de lire les informations d'identification à partir des fichiers de configuration aws cli. Votre shell interactif et votre processus IRIS peuvent être exécutés sous différents comptes - assurez-vous d'exécuter aws configure sous le même compte que votre processus IRIS.

Partie 2 : Gestion des index

Vous avez maintenant une bonne idée du type d'index dont vous avez besoin pour votre classe et de la manière de les définir. Ensuite, comment les gérer ?

Plan de requête

Cet article est le premier d'une série d'articles sur les indexes SQL.

Partie 1 - Découvrez vos indexes

Qu'est-ce qu'un index, en fait ?

Imaginez la dernière fois où vous êtes allé à la bibliothèque. En général, les livres y sont classés par sujet (puis par auteur et par titre), et chaque étagère comporte une étiquette avec un code décrivant le sujet de ses livres. Si vous voulez collectionner des livres d'un certain sujet, au lieu de traverser chaque allée et de lire la couverture intérieure de chaque livre, vous pouvez vous diriger directement vers l'étagère étiquetée avec le sujet désiré et choisir vos livres.

Un index SQL a la même fonction générale : améliorer les performances en donnant une référence rapide à la valeur des champs pour chaque ligne de la table.

La mise en place d'index est l'une des principales étapes de la préparation de vos classes pour une performance SQL optimale.

Dans cet article, nous allons examiner les questions suivantes :

1. Qu'est-ce qu'un index et pourquoi/quand dois-je l'utiliser ?
2. Quels types d'indexes existent et pour quels scénarios sont-ils parfaitement adaptés ?
3. Qu'est-ce qu'un index ?
4. Comment le créer ?

• Et si j'ai des index, qu'est-ce que j'en fais ?

Je vais me référer aux classes de notre schéma Sample. Celles-ci sont disponibles dans le stockage Github suivant, et elles sont également fournies dans l'espace de noms Samples dans les installations de Caché et Ensemble :

https://github.com/intersystems/Samples-Data

Les principes de base

Vous pouvez indexer chaque propriété persistante et chaque  propriété qui peut être calculée de manière fiable à partir de données persistantes.

Disons que nous voulons indexer la propriété TaxID dans Sample.Company. Dans Studio ou Atelier, nous ajouterions ce qui suit à la définition de la classe :

                Index TaxIDIdx On TaxID;

L'instruction SQL DDL équivalente ressemblerait à ceci :

                CREATE INDEX TaxIDIdx ON Sample.Company (TaxID);

La structure globale de l'index par défaut est la suivante :

                ^Sample.CompanyI("TaxIDIdx",<TaxIDValueAtRowID>,<RowID>) = ""

Notez qu'il y a moins d'index inférieurs à lire que de champs dans une globale de données typique.

Considérons la requête

SELECT Name,TaxID FROM Sample.Company WHERE TaxID = 'J7349'

C'est logiquement simple et le plan de requête pour l'exécution de cette requête le reflète :

Ce plan indique essentiellement que nous vérifions l'index global pour les lignes avec la valeur TaxID donnée, puis nous nous référons à la globale de données ("carte principale") pour récupérer la ligne correspondante.

Considérons maintenant la même requête sans index sur TaxIDX. Le plan de requête résultant est, comme prévu, moins efficace :

Sans index, l'exécution de la requête sous-jacente d'IRIS repose sur la lecture en mémoire et l'application de la condition de la clause WHERE à chaque ligne de la table. Et comme nous ne nous attendons logiquement pas à ce qu'une société partage TaxID, nous faisons tout ce travail pour une seule ligne !

Bien sûr, avoir des indexes signifie avoir des données d'index et de ligne sur le disque. En fonction de ce sur quoi nous avons une condition et de la quantité de données que notre table contient, cela peut s'avérer avoir ses propres défis lorsque nous créons et alimentons un index.

Alors, quand ajoutons-nous un index à une propriété ?

Dans le cas général, nous avons fréquemment à remettre une propriété en état. Des exemples sont des informations d'identification telles que le SSN d'une personne ou un numéro de compte bancaire. Vous pouvez également considérer les dates de naissance ou les fonds d'un compte.  Pour en revenir à Sample.Company, la classe bénéficierait peut-être de l'indexation de la propriété Revenue si nous voulions collecter des données sur les organisations à hauts revenus. À l'inverse, les propriétés sur lesquelles il est peu probable que nous remettions des conditions sont moins appropriées pour être indexées : disons un slogan ou une description d'entreprise.

Facile - sauf qu'il faut aussi considérer quel type d'index est le meilleur !

Types d'indexes

Il existe six principaux types d'index que je vais aborder ici : standard, bitmap, compound, collection, bitslice et data. Je vais également aborder brièvement les index iFind, qui sont basés sur les flux. Il y a des chevauchements possibles ici et nous avons déjà abordé les indexes standards avec l'exemple ci-dessus.

Je vais présenter des exemples sur la façon de créer des indexes dans votre définition de classe, mais l'ajout de nouveaux index à une classe est plus complexe que le simple ajout d'une ligne dans votre définition de classe. Nous aborderons des considérations supplémentaires dans la partie suivante.

Prenons l'exemple de Sample.Person. Notez que Person a une sous-classe Employee, ce qui sera utile pour comprendre certains exemples. Employee partage son stockage global de données avec Person, et tous les indexes de Person sont hérités par Employee - ce qui signifie qu'Employee utilise l'index global de Person pour ces indexes hérités.

Si vous n'êtes pas familier avec ces classes, voici un aperçu général de celles-ci : Person a les propriétés SSN, DOB, Name, Home (un objet d'adresse intégré contenant l'état et la ville), Office (également une adresse), et la collection de listes FavoriteColors. Employee a une propriété supplémentaire Salary (que j'ai moi-même définie).

Standard

Index DateIDX On DOB;

J'utilise ici le terme "standard" pour désigner les indexes qui stockent la valeur brute d'une propriété (par opposition à une représentation binaire). Si la valeur est une chaîne de caractères, elle sera stockée sous une certaine collation - celle de SQLUPPER par défaut.

Par rapport aux index bitmap ou bitslice, les indexes standard sont plus compréhensibles pour les humains et relativement faciles à maintenir. Nous avons un nœud global pour chaque ligne de la table.

Voici comment DateIDX est stocké au niveau global.

^Sample.PersonI("DateIDX",51274,100115)="~Sample.Employee~" ; Date is 05/20/81

Notez que le premier index inférieur après le nom de l'index est la valeur de la date, le dernier index inférieur est l'ID de la personne ayant cette date de naissance, et la valeur stockée sur ce noeud global indique que cette personne est également membre de la sous-classe Sample.Employee. Si cette personne n'était membre d'aucune sous-classe, la valeur du noeud serait une chaîne vide.

Cette structure de base sera cohérente avec la plupart des indexes non binaires, où les indexes sur plus d'une propriété créent plus d'indexes inférieurs dans la globale, et où le fait d'avoir plus d'une valeur stockée au nœud produit un objet $listbuild, par exemple :

                ^Package.ClassI(IndexName,IndexValue1,IndexValue2,IndexValue3,RowID) = $lb(SubClass,DataValue1,DataValue2)

Bitmap - Une représentation binaire de l'ensemble des ID-codes correspondant à une valeur de propriété.

Index HomeStateIDX On Home.State [ Type = bitmap];

Les indexes bitmap sont stockés par valeur unique, contrairement aux indexes standard, qui sont stockés par ligne.

Pour aller plus loin dans l'exemple ci-dessus, disons que la personne avec l'ID 1 vit dans le Massachusetts, avec l'ID 2 à New York, avec l'ID 3 dans le Massachusetts et avec l'ID 4 à Rhode Island. HomeStateIDX est essentiellement stocké comme suit :

Si nous voulions qu'une requête renvoie les données des personnes vivant en Nouvelle-Angleterre, le système effectue un bitwise OR sur les lignes pertinentes de l'index bitmap. On voit rapidement que nous devons charger en mémoire des objets Personne avec les ID 1, 3 et 4 au minimum.

Les bitmaps peuvent être efficaces pour les opérateurs AND, RANGE et OR dans vos clauses WHERE. 

Bien qu'il n'y ait pas de limite officielle au nombre de valeurs uniques que vous pouvez avoir pour une propriété avant qu'un index bitmap soit moins efficace qu'un index standard, la règle générale est d'environ 10 000 valeurs distinctes. Ainsi, si un index bitmap peut être efficace pour un état des États-Unis, un index bitmap pour une ville ou un comté des États-Unis ne serait pas aussi utile.

Un autre concept à prendre en compte est l'efficacité du stockage. Si vous prévoyez d'ajouter et de supprimer fréquemment des lignes de votre table, le stockage de votre index bitmap peut devenir moins efficace. Prenons l'exemple ci-dessus : supposons que nous ayons supprimé de nombreuses lignes pour une raison quelconque et que notre table ne contienne plus de personnes vivant dans des états moins peuplés tels que le Wyoming ou le Dakota du Nord. Le bitmap comporte donc plusieurs lignes contenant uniquement des zéros. D'un autre côté, la création de nouvelles lignes dans les grandes tables peut finir par devenir plus lente, car le stockage bitmap doit accueillir un plus grand nombre de valeurs uniques.

Dans ces exemples, j'ai environ 150 000 lignes dans Sample.Person. Chaque nœud global stocke jusqu'à 64 000 ID, de sorte que l'index bitmap global à la valeur MA est divisé en trois parties :

      ^Sample.PersonI("HomeStateIDX"," MA",1)=$zwc(135,7992)_$c(0,(...))

^Sample.PersonI("HomeStateIDX"," MA",2)=$zwc(404,7990,(…))

^Sample.PersonI("HomeStateIDX"," MA",3)=$zwc(132,2744)_$c(0,(…))

Cas particulier : Bitmap étendu 

Un bitmap étendue, souvent appelé $<ClassName>, est un index bitmap sur les ID d'une classe - cela donne à IRIS un moyen rapide de savoir si une ligne existe et peut être utile pour les requêtes COUNT ou les requêtes sur les sous-classes. Ces indexes sont générés automatiquement lorsqu'un index bitmap est ajouté à la classe ; vous pouvez également créer manuellement un index bitmap d'étendue dans une définition de classe comme suit :

Index Company [ Extent, SqlName = "$Company", Type = bitmap ];

Ou via le mot-clé DDL appelé BITMAPEXTENT :

CREATE BITMAPEXTENT INDEX "$Company" ON TABLE Sample.Company

Composés - Les indexes basés sur deux ou plusieurs propriétés

Index OfficeAddrIDX On (Office.City, Office.State);

Le cas général d'utilisation des index composés est le conditionnement de requêtes fréquentes sur deux propriétés ou plus.

L'ordre des propriétés dans un index composé est important en raison de la manière dont l'index est stocké au niveau global. Le fait d'avoir la propriété la plus sélective en premier est plus efficace en termes de performances car cela permet d'économiser les lectures initiales du disque de l'index global ; dans cet exemple, Office.City est en premier car il y a plus de villes uniques que d'états aux États-Unis.

Le fait d'avoir une propriété moins sélective en premier est plus efficace en termes d'espace. En termes de structure globale, l'arbre d'indexation serait plus équilibré si State était placé en premier. Pensez-y : chaque état contient de nombreuses villes, mais certains noms de ville n'appartiennent qu'à un seul état.

Vous pouvez également vous demander si vous vous attendez à exécuter des requêtes fréquentes ne conditionnant qu'une seule de ces propriétés - cela peut vous éviter de définir un autre index.

Voici un exemple de la structure globale des indexes composés :

^Sample.PersonI("OfficeAddrIDX"," BOSTON"," MA",100115)="~Sample.Employee~"

Commentaires : Index composé ou index bitmap ?

Pour les requêtes comportant des conditions sur plusieurs propriétés, vous pouvez également vous demander si des indexes bitmap séparés seraient plus efficaces qu'un seul index composé.

Les opérations par bit sur deux indexes différents peuvent être plus efficaces à condition que les indexes bitmap conviennent à chaque propriété.

Il est également possible d'avoir des indexes bitmap composés, c'est-à-dire des indexes bitmap dont la valeur unique est l'intersection de plusieurs propriétés sur lesquelles vous effectuez l'indexation. Considérez la table donnée dans la section précédente, mais au lieu des états, nous avons toutes les paires possibles d'un état et d'une ville (par exemple, Boston, MA, Cambridge, MA, même Los Angeles, MA, etc.), et les cellules obtiennent des 1 pour les lignes qui adhèrent aux deux valeurs.

Collection - Les index basés sur les propriétés de la collection

Nous avons ici la propriété FavoriteColors définie comme suit :

Property FavoriteColors As list Of %String;

Avec chacun des indexes suivants définis à titre de démonstration :

Index fcIDX1 On FavoriteColors(ELEMENTS);
Index fcIDX2 On FavoriteColors(KEYS);

J'utilise ici le terme "collection" pour désigner plus largement les propriétés à cellule unique contenant plus d'une valeur. Les propriétés List Of et Array Of sont pertinentes ici, et si vous le souhaitez, même les chaînes de caractères délimitées.

Les propriétés de la collection sont automatiquement analysées pour construire leurs indexes. Pour les propriétés délimitées, comme un numéro de téléphone, vous devez définir cette méthode, <PropertyName>BuildValueArray(value, .valueArray), explicitement.

Compte tenu de l'exemple ci-dessus pour FavoriteColors, fcIDX1 ressemblerait à ceci pour une personne dont les couleurs préférées sont le bleu et le blanc :

^Sample.PersonI("fcIDX1"," BLUE",100115)="~Sample.Employee~"

(…)

^Sample.PersonI("fcIDX1"," WHITE",100115)="~Sample.Employee~"

fcIDX2 ressemblerait à :

         ^Sample.PersonI("fcIDX2",1,100115)="~Sample.Employee~"      

^Sample.PersonI("fcIDX2",2,100115)="~Sample.Employee~"

Dans ce cas, puisque FavoriteColors est une collection de listes, un index basé sur ses clés est moins utile qu'un index basé sur ses éléments.

Veuillez vous référer à notre documentation pour des considérations plus approfondies sur la création et la gestion des indexes sur les propriétés des collections.

Bitslice - Représentation en bitmap de la représentation en chaîne de bits des données numériques

Index SalaryIDX On Salary [ Type = bitslice ]; //In Sample.Employee

Contrairement aux indexes bitmap, qui contiennent des balises indiquant quelles lignes contiennent une valeur spécifique, les indexes bitslice convertissent d'abord les valeurs numériques de la décimale à la binaire, puis créent un bitmap sur chaque chiffre de la valeur binaire.

Reprenons l'exemple ci-dessus et, par souci de réalisme, simplifions le salaire en unités de 1 000 dollars. Ainsi, si le salaire d'un employé est enregistré sous la forme 65, il est compris comme représentant 65 000 dollars.

Disons que nous avons un employé avec l'ID 1 qui a un salaire de 15, l'ID 2 un salaire de 40, l'ID 3 un salaire de 64 et l'ID 4 un salaire de 130. Les valeurs binaires correspondantes sont :

Notre chaîne de bits s'étend sur 8 chiffres. La représentation bitmap correspondante - les valeurs d'indexes bitslice - est essentiellement stockée comme suit :

^Sample.PersonI("SalaryIDX",1,1) = "1000" ; La ligne 1 a une valeur à la place 1

^Sample.PersonI("SalaryIDX",2,1) = "1001" ; Les lignes 1 et 4 ont des valeurs à la place 2

^Sample.PersonI("SalaryIDX",3,1) = "1000" ; La ligne 1 a une valeur à la place 4

^Sample.PersonI("SalaryIDX",4,1) = "1100" ; Les lignes 1 et 2 ont des valeurs à la place 8

^Sample.PersonI("SalaryIDX",5,1) = "0000" ; etc…

^Sample.PersonI("SalaryIDX",6,1) = "0100"

^Sample.PersonI("SalaryIDX",7,1) = "0010"

^Sample.PersonI("SalaryIDX",8,1) = "0001"

Notez que les opérations modifiant Sample.Employee ou les salaires dans ses lignes, c'est-à-dire les INSERTs, UPDATESs et DELETEs, nécessitent maintenant la mise à jour de chacun de ces nœuds globaux, ou bitslices. L'ajout d'un index bitslice à plusieurs propriétés d'une table ou à une propriété fréquemment modifiée peut présenter des risques pour les performances. En général, la maintenance d'un index bitslice est plus coûteuse que celle des indexes standard ou bitmap.

Les indexes Bitslice sont hautement spécialisés et ont donc des cas d'utilisation spécifiques : les requêtes qui doivent effectuer des calculs agrégés, par exemple SUM, COUNT ou AVG.

En outre, ils ne peuvent être utilisés efficacement que sur des valeurs numériques - les chaînes de caractères sont converties en un 0 binaire.

Notez que si la table de données, et non les index, doit être lu pour vérifier la condition d'une requête, les indexes bitslice ne seront pas choisis pour exécuter la requête. Supposons que Sample.Person ne possède pas d'index sur Name. Si nous calculions le salaire moyen des employés portant le nom de famille Smith :

SELECT AVG(Salary) FROM Sample.Employee WHERE Name %STARTSWITH 'Smith,'

nous aurions besoin de lire des lignes de données pour appliquer la condition WHERE, et donc l'index bitslice ne serait pas utilisé en pratique.

Des problèmes de stockage similaires se posent pour les indexes bitslice et bitmap sur les tables où des lignes sont fréquemment créées ou supprimées.

Data - Index dont les données sont stockées dans leurs nœuds globaux.

Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];

Dans plusieurs des exemples précédents, vous avez peut-être observé la chaîne “~Sample.Employee~” stockée comme valeur au niveau du noeud lui-même. Rappelez-vous que Sample.Employee hérite des indexes de Sample.Person. Lorsque nous effectuons une requête sur les employés en particulier, nous lisons la valeur aux nœuds d'index correspondant à notre condition de propriété pour vérifier que ladite personne est également un employé.

On peut aussi définir explicitement les valeurs à stocker. Le fait d'avoir des données définies au niveau des nœuds globaux de l'index permet d'éviter la lecture de l'ensemble des données globales ; cela peut être utile pour les requêtes sélectives ou les requêtes ordonnées fréquentes.

Considérons l'index ci-dessus comme un exemple. Si nous voulions extraire des informations d'identification sur une personne à partir de tout ou une partie de son nom (par exemple, pour rechercher des informations sur les clients dans une application de réception), nous pourrions avoir une requête telle que 

SELECT SSN, Name, DOB FROM Sample.Person WHERE Name %STARTSWITH 'Smith,J' ORDER BY Name

Puisque les conditions de notre requête sur le nom et les valeurs que nous récupérons sont toutes contenues dans les nœuds globaux QuickSearchIDX, il nous suffit de lire notre I globale pour exécuter cette requête.

Notez que les valeurs de données ne peuvent pas être stockées avec des indexes de bitmap ou de bitslice.

^Sample.PersonI("QuickSearchIDX"," LARSON,KIRSTEN A.",100115)=$lb("~Sample.Employee~","555-55-5555",51274,"Larson,Kirsten A.")

iFind Indexes

Vous en avez déjà entendu parler ? Moi non plus. Les indexes iFind sont utilisés sur les propriétés des flux, mais pour les utiliser vous devez spécifier leurs noms avec des mots-clés dans la requête.

Je pourrais vous en dire plus, mais Kyle Baxter a déjà rédigé un article utile à ce sujet.