Geekly articles weekly

Apprentissage automatique pour Vertica

Annotation


Dans cet article, je veux partager ma propre expérience avec l'apprentissage automatique dans un entrepôt de données sur Vertica.

Pour être honnête, je ne suis pas un analyste expert qui peut décrire en détail toute la variété des méthodes de recherche et des algorithmes de prédiction de données. Mais malgré tout, étant un expert de Vertica et ayant une expérience de base avec ML, j'essaierai de parler des façons de travailler avec l'analyse prédictive dans Vertica en utilisant la fonctionnalité de serveur intégrée et le langage R.

Bibliothèque d'apprentissage automatique Vertica


À partir de la version 7, Vertica a été étendu avec la bibliothèque Machine Learning, avec laquelle vous pouvez:

  • Préparer des exemples de données pour l'apprentissage automatique
  • former des modèles d'apprentissage automatique sur des données préparées;
  • effectuer une analyse prédictive des données de stockage sur des modèles d'apprentissage automatique enregistrés.

La bibliothèque est livrée immédiatement complète avec l'installation de Vertica pour toutes les versions, y compris la communauté gratuite. Travailler avec est encadré sous la forme d'un appel à des fonctions sous SQL, qui sont décrites en détail dans la documentation avec des exemples d'utilisation sur des données de démonstration préparées.

Un exemple de travail avec ML dans Vertica


Comme exemple simple du fonctionnement de ML, j'ai pris les données de démonstration de mtcars qui font partie de l'exemple de données ML pour Vertica. Ces données comprennent deux tableaux:

  • mtcars_train - données préparées pour la formation de modèles d'apprentissage automatique
  • mtcars - données pour l'analyse

Regardons les données pour la formation:

=>SELECT * FROM mtcars_train;



Dans l'ensemble de données sur les modèles de voitures, leurs caractéristiques sont décrites. Essayons de former le machine learning afin que, selon les caractéristiques des voitures, il soit possible de prédire quel type de boîte de vitesses est impliqué dans la voiture - une boîte manuelle ou une boîte de vitesses automatique. Pour ce faire, nous devrons construire un modèle de régression logistique sur les données préparées, en trouvant la dépendance du type de boîte du champ "am" et des champs de poids du véhicule "wt", du nombre de cylindres "cyl" et du nombre de vitesses dans la boîte "gear":

 =>SELECT LOGISTIC_REG('logistic_reg_mtcars', 'mtcars_train', 'am', 'cyl, wt, gear'); Finished in 19 iterations

La fonction appelée a analysé la relation entre am et les champs cyl, wt, gear, a révélé la formule de dépendance et a écrit le résultat de la simulation de dépendance dans la base de données Vertica dans le modèle «logistic_reg_mtcars». En utilisant ce modèle enregistré, vous pouvez désormais analyser les données sur les voitures et prédire la disponibilité des boîtes de vitesses automatiques.

Les informations sur le modèle peuvent être consultées:

 =>SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='logistic_reg_mtcars');



Maintenant, nous utilisons le modèle sur les données pour les voitures, enregistrant le résultat dans un nouveau tableau:

 =>CREATE TABLE mtcars_predict_results AS ( SELECT car_model, am, PREDICT_LOGISTIC_REG(cyl, wt, gear USING PARAMETERS model_name='logistic_reg_mtcars') AS prediction FROM mtcars );

Et en comparant les valeurs réelles de am avec celles obtenues dans la prédiction de prédiction:

 =>SELECT * FROM mtcars_predict_results;



Dans ce cas, la prévision de 100% coïncidait avec le type réel de boîte dans les modèles présentés. Dans le cas de la préparation de nouvelles données pour la formation, vous devrez supprimer et réenregistrer le modèle.

Fonctionnalité ML dans Vertica


La bibliothèque Vertica ML prend en charge les types d'analyse prédictive suivants:

  • Prévision:
    • Régression linéaire
    • Forêt aléatoire pour la régression
    • SVM (Support Vector Machine) pour la régression
  • Classification:
    • Régression logistique
    • Bayes naïfs
    • Forêt aléatoire pour la classification
    • SVM (Support Vector Machine) pour la classification
  • Regroupement:
    • k-means

Pour préparer les données pour la formation, les fonctionnalités suivantes sont présentées:

  • Équilibrage des données
  • Nettoyage des émissions
  • Encodage des valeurs de colonne catégorielles (textuelles)
  • Remplacement des données manquantes
  • Normalisation des données
  • Analyse en composantes principales
  • Échantillonnage des données
  • Décomposition en valeurs singulières

Compte tenu de la fonctionnalité ML dans Vertica, nous pouvons dire que la bibliothèque intégrée nous permet de résoudre un assez large éventail de problèmes, mais n'a pas le retard nécessaire pour étudier les modèles et les dépendances dans les données. Il existe des fonctions pour préparer les données pour l'apprentissage automatique, mais sans visualiser la distribution des données sous forme de graphiques, seuls les gourous de l'analyse ayant une connaissance approfondie des données analysées seront en mesure de «préparer» ces données et de former des modèles d'apprentissage sur celles-ci.

R Studio avec Vertica


Pour une analyse prédictive des données plus approfondie et interactive, le langage R est idéalement adapté, qui dispose d'un environnement visuel pour travailler avec les données R Studio. Les avantages tangibles de l'utilisation de R avec Vertica seront:

  • l'interactivité de l'environnement avec la possibilité d'enregistrer l'état pour une analyse plus approfondie après la prochaine analyse;
  • visualisation visuelle des données sous forme de tableaux et de graphiques;
  • Puissance du langage R pour travailler avec des ensembles de données;
  • une variété d'algorithmes d'analyse prédictive similaires à ceux présentés dans Vertica ML.

Les inconvénients de travailler avec R avec des données volumineuses sont les besoins en RAM, la vitesse de travail avec des tableaux de données volumineux et la nécessité d'importer et d'exporter des données Vertica. Ces lacunes sont couvertes par la possibilité d'incorporer des fonctions R écrites pour une exécution directe sur un cluster dans Vertica, qui sera décrite ci-dessous.

Une petite introduction à R


Nous reproduirons les prévisions de boîtes automatiques sur les données Vertica en utilisant R. Afin de ne pas effrayer les programmeurs peu familiers avec ce langage, je conduirai un petit cours d'un jeune combattant R.

Ainsi, le langage R est le même langage procédural qui a des objets, des classes et des fonctions.
Un objet peut être un ensemble de données (vecteur, liste, jeu de données ...), une valeur (texte, nombre, date, heure ...) ou une fonction. Pour les valeurs, les types numérique, chaîne, booléen et date-heure sont pris en charge. Pour les jeux de données, la numérotation des tableaux commence à 1, pas à 0.

Classiquement, au lieu de "=" dans R, l'opérateur d'affectation "<-" est utilisé. Bien qu'il ne soit pas interdit d'utiliser l'affectation de l'autre côté "->" et même l'habituel "=". L'opérateur "=" lui-même est utilisé lors de l'appel de fonctions pour spécifier des paramètres nommés.

Au lieu de "." "$" est utilisé pour accéder aux champs des ensembles de données. Un point n'est pas un mot clé et est utilisé dans les noms d'objets pour augmenter leur lisibilité. Ainsi, «my.data $ field» sera décrypté sous la forme d'un tableau d'enregistrements du champ «field» de l'ensemble de données «my.data».

Vous pouvez utiliser des guillemets simples ou doubles pour encadrer des textes.

Plus important encore: R est conçu pour travailler avec des ensembles de données. Même si le code dit «a <-1», alors assurez-vous que R à l'intérieur de lui-même croit que «a» est un tableau de 1 élément. La conception du langage vous permet de travailler avec des ensembles de données comme avec des variables ordinaires: ajouter et soustraire, connecter et déconnecter, filtrer par mesures. La façon la plus simple de créer un tableau listant ses éléments est d'appeler la fonction "c (éléments du tableau séparés par des virgules)". Le nom "c" est apparemment pris comme abréviation courte pour Collection, mais je ne le dirai pas avec certitude.

Chargement des données d'un SGBD dans R


Pour utiliser RDBMS via ODBC pour R, vous devez installer le package RODBC. Il peut être installé dans R Studio sur l'onglet packages ou en utilisant la commande R:

 install.packages('RODBC') library('RODBC')


Maintenant, nous pouvons travailler avec Vertica. Nous créons un alias ODBC sur le serveur et obtenons les données de test et l'ensemble de données complet pour la voiture:

 #    Vertica con <- odbcConnect(dsn='VerticaDSN') #    mtcars_train mtcars.train <- sqlQuery(con, "SELECT * FROM public.mtcars_train") #    mtcars</b> mtcars.data <- sqlQuery(con, "SELECT * FROM public.mtcars") #   odbcClose(con)

Lors du chargement de données à partir de sources R pour des champs de types de texte et de date-heure, leur appartenance à des facteurs est automatiquement établie. Le champ «am» est de type numérique et R est perçu comme un indicateur numérique, et non un facteur, qui ne permettra pas une régression logistique. Par conséquent, nous convertissons ce champ en un facteur numérique:

 mtcars.data$am = factor(mtcars.data$am) mtcars.train$am = factor(mtcars.train$am)

Dans R Studio, il est pratique de regarder les données de manière interactive, de créer des graphiques d'analyse prédictive et d'écrire du code dans R avec des conseils:



Construire un modèle en R


Nous allons construire un modèle de régression logistique sur l'ensemble de données préparé pour les mêmes dimensions que dans Vertica:

 mtcars.model <- glm(formula = am ~ cyl + wt + gear, family = binomial(), data = mtcars.train)

Explication: en langage R, la formule d'analyse prédictive est indiquée comme suit:

 <  >~<   >

Analyse des données du modèle dans R


Nous initialisons l'ensemble de données résultant, en prenant de mtcars tous les enregistrements pour les champs obligatoires:

 mtcars.result <- data.frame(car_model = mtcars.data$car_model, am = mtcars.data$am, predict = 0)

Maintenant, sur la base du modèle construit, vous pouvez effectuer une analyse sur les données elles-mêmes:

 mtcars.result$predict <- predict.glm(mtcars.model, newdata = subset(mtcars.data, select = c('cyl', 'wt', 'gear')), type = 'response' )

Le résultat de l'analyse est renvoyé au champ de prédiction sous forme de pourcentage de la probabilité de la prévision. Simplifiez par analogie avec Vertica aux valeurs 0 ou 1, en considérant la prévision positive avec une probabilité de plus de 50%:

 mtcars.result$predict <- ifelse(mtcars.result$predict > 0.5, 1, 0)

Nous calculons le nombre total d'enregistrements pour lesquels le champ de prédiction prévu ne correspond pas à la valeur réelle en am:

 nrow(mtcars[mtcars.result$am != mtcars.result$predict, ])

R a renvoyé zéro. Ainsi, les prévisions ont convergé sur tous les modèles de voitures, comme dans le ML de Vertica.

Remarque: les enregistrements de mtcars ont été renvoyés par le filtre (le premier paramètre entre crochets) avec toutes les colonnes (le deuxième paramètre a été omis après la virgule entre crochets).

Enregistrement et chargement de données localement dans R


A la sortie de R, le studio propose de sauvegarder l'état de tous les objets afin de continuer à travailler après un redémarrage. Si, pour une raison quelconque, vous devez enregistrer, puis restaurer l'état des objets individuels, pour cela, des fonctions spéciales sont fournies dans R:

 #      save(mtcars.model, file = 'mtcars.model') #      load('mtcars.model')

Enregistrement de données de R vers Vertica


Si R Studio a été utilisé pour préparer des données pour la formation des modèles ML Vertica, ou si l'analyse a été effectuée directement dans celui-ci, qui doit ensuite être utilisée dans la base de données Vertica, les ensembles de données R peuvent être écrits dans la table Vertica.

Étant donné que la bibliothèque ODBC pour R est conçue pour les SGBDR OLTP, elle ne peut pas générer correctement des requêtes de création de table pour Vertica. Par conséquent, pour réussir l'enregistrement des données, vous devrez créer manuellement la table nécessaire dans Vertica à l'aide de SQL, dont l'ensemble de champs et les types coïncident avec l'ensemble de données enregistrables R.

De plus, le processus d'enregistrement lui-même semble simple (n'oubliez pas d'ouvrir puis de fermer la connexion con):

 sqlSave(con, mtcars.result, tablename = 'public.mtcars_result', append = TRUE, rownames = FALSE, colnames = FALSE)

Utilisation de Vertica avec R


Le travail interactif avec des données dans R Studio est bien adapté au mode de recherche et de préparation de données. Mais il est totalement inadapté à l'analyse de flux de données et de grands tableaux en mode automatique. L'une des options du schéma d'analyse prédictive hybride R avec Vertica est la préparation de données pour l'apprentissage de R et l'identification des dépendances pour la construction de modèles. Ensuite, en utilisant les fonctions ML intégrées à Vertica, les modèles de prévision des données préparées sur R sont formés en tenant compte des dépendances identifiées des variables.

Il existe une option plus flexible lorsque toute la puissance du langage R est utilisée directement sous Vertica. Pour cela, Vertica a développé la distribution R sous la forme d'une bibliothèque de plug-ins qui vous permet d'utiliser des fonctions de transformation écrites directement dans le langage R dans les requêtes SQL. La documentation décrit en détail l'installation du support R pour Vertica et les packages R supplémentaires nécessaires au fonctionnement, le cas échéant.

Enregistrement du modèle R dans Vertica


Pour utiliser le modèle d'analyse précédemment préparé par R Studio dans les fonctions R exécutées sous Vertica, vous devez les enregistrer sur les serveurs Vertica. L'enregistrement local sur chaque serveur du cluster avec un fichier n'est ni pratique ni fiable, de nouveaux serveurs peuvent être ajoutés au cluster, et lors du changement de modèle, vous devrez vous rappeler de réécrire tous les fichiers.

Le moyen le plus pratique consiste à sérialiser le modèle R en texte et à enregistrer la fonction Vertica en tant qu'UDF, qui renverra ce texte sous forme de constante (n'oubliez pas d'ouvrir puis de fermer la connexion con):

 #     mtcars.model.text <- rawToChar( serialize(mtcars.model, connection = NULL, ascii = TRUE)) #       Vertica # (     ) mtcars.func <- paste0( "CREATE OR REPLACE FUNCTION public.MtCarsAnalizeModel() RETURN varchar(65000) AS BEGIN RETURN '", gsub("'", "''", mtcars.model.text), "'; END; GRANT EXECUTE ON FUNCTION public.MtCarsAnalizeModel() TO public;" ) #    Vertica sqlQuery(con, mtcars.func)

La méthode proposée permet de contourner la restriction de Vertica sur les paramètres transmis dans la fonction de transformation, où seul le transfert de constantes ou d'expressions de constantes est requis. Vertica UDF SQL compile non pas comme des fonctions, mais comme des expressions calculées, c'est-à-dire que lors du passage d'un paramètre, au lieu d'appeler la fonction, son texte (dans ce cas une constante) sera transféré, ce qui a été enregistré dans le code ci-dessus.

Si vous modifiez le modèle, vous devrez recréer sa fonction dans Vertica. Il est logique d'encapsuler ce code dans une fonction universelle qui génère une fonction dans Vertica avec le nom spécifié à partir du modèle passé.

Fonctions R pour Vertica


Pour connecter les fonctions R à Vertica, vous devez écrire des fonctions d'analyse et d'enregistrement des données dans Vertica.

La fonction de travailler avec des données sous Vertica devrait avoir deux paramètres: l'ensemble de données résultant (comme data.frame) et les paramètres de travail (comme list):

 MtCarsAnalize <- function(data, parameters) { if ( is.null(parameters[['model']]) ) { stop("NULL value for model! Model cannot be NULL.") } else { model <- unserialize(charToRaw(parameters[['model']])) } names(data) <- c('car_model', 'cyl', 'wt', 'gear') result <- data.frame(car_model = data$car_model, predict = 0) result$predict <- predict.glm(model, newdata = subset(data, select = c('cyl', 'wt', 'gear')), type = 'response' ) result$predict <- ifelse(result$predict > 0.5, TRUE, FALSE) return(result) }

Dans le corps de la fonction, on vérifie que le paramètre du modèle est passé, dont le texte est traduit en forme binaire et désérialisé en l'objet du modèle d'analyse. Étant donné que Vertica transfère ses propres noms de champ à l'ensemble de données pour la fonction, les noms de champ explicites sont définis dans l'ensemble de données. Sur la base des données obtenues, un ensemble de résultats est construit avec le nom du modèle de machine et zéro prédire. Ensuite, une prévision est construite en utilisant uniquement les champs nécessaires à l'analyse à partir de l'ensemble de données obtenu. Le champ de prédiction de l'ensemble de résultats est défini sur des valeurs booléennes (pour une modification au lieu de numériques) et le résultat est renvoyé par la fonction.

Reste maintenant à décrire l'enregistrement de cette fonction dans Vertica:

 MtCarsAnalizeFactory <- function() { list(name = MtCarsAnalize, udxtype = c("transform"), intype = c("varchar", "int", "float", "int"), outtype = c("varchar", "boolean"), outnames = c("car_model", "predict"), parametertypecallback=MtCarsAnalizeParameters) } MtCarsAnalizeParameters <- function() { parameters <- list(datatype = c("varchar"), length = 65000, scale = c("NA"), name = c("model")) return(parameters) }

La fonction MtCarsAnalizeFactory décrit le nom de la fonction utilisée pour l'opération, le champ pour l'ensemble de données entrant et sortant, et la deuxième fonction décrit le paramètre passé «modèle». Les types de champs sont des types de données Vertica. Lors du transfert et du retour de données, Vertica convertit automatiquement les valeurs dans les types de données requis pour le langage R. Vous pouvez voir le tableau de compatibilité des types dans la documentation de Vertica.

Vous pouvez tester le fonctionnement de la fonction écrite pour Vertica sur les données téléchargées vers R studio:

 test.data = subset(mtcars.data, select = c('car_model', 'cyl', 'wt', 'gear')) test.params = list(model = mtcars.model.text) test.result = MtCarsAnalize(test.data, test.params)

Connectez la bibliothèque de fonctionnalités à Vertica


Nous enregistrons toutes les fonctions ci-dessus dans un seul fichier "mtcars_func.r" et téléchargeons ce fichier sur l'un des serveurs du cluster Vertica dans "/ home / dbadmin".

Un point important: dans R Studio, vous devez définir l'option pour enregistrer la traduction des lignes dans des fichiers en mode Posix (LF). Cela peut être fait dans les options globales, section Code, onglet Enregistrement. Si vous travaillez sous Windows, par défaut, le fichier sera enregistré avec un retour chariot et ne pourra pas être téléchargé sur Vertica.

Nous nous connectons au serveur à partir du cluster Vertica, sur lequel nous avons enregistré le fichier et chargé la bibliothèque:

 CREATE LIBRARY MtCarsLibs AS '/home/dbadmin/mtcars_func.r' LANGUAGE 'R';

À partir de cette bibliothèque, vous pouvez enregistrer la fonction R:

 CREATE TRANSFORM FUNCTION public.MtCarsAnalize AS LANGUAGE 'R' NAME 'MtCarsAnalizeFactory' LIBRARY MtCarsLibs; GRANT EXECUTE ON TRANSFORM FUNCTION public.MtCarsAnalize(varchar, int, float, int) TO public;

Fonctions d'appel R dans Vertica


Nous appelons la fonction R, en lui passant le texte du modèle, qui était précédemment enregistré en tant que fonction UDF:

 SELECT MtCarsAnalize(car_model, cyl, wt, gear USING PARAMETERS model = public.MtCarsAnalizeModel()) OVER() FROM public.mtcars;



On peut vérifier que, comme dans les cas précédents, la prévision est 100% cohérente avec la situation réelle:

 SELECT c.*, p.predict, p.predict = c.am::int AS valid FROM public.mtcars c INNER JOIN ( SELECT MtCarsAnalize(car_model, cyl, wt, gear USING PARAMETERS model = public.MtCarsAnalizeModel()) OVER() FROM public.mtcars ) p ON c.car_model = p.car_model

Remarque: les fonctions de transformation dans Vertica renvoient leur propre ensemble de données à partir des champs et des enregistrements définis dans les fonctions, mais elles peuvent être utilisées dans les requêtes si elles sont encapsulées dans une sous-requête.

Lorsque les fonctions R sont connectées, Vertica copie le code source dans son installation, qu'il compile en code machine. Le fichier source R téléchargé sur le serveur après la connexion à la bibliothèque n'est pas requis pour les travaux ultérieurs. La vitesse des fonctions prenant en compte la compilation binaire est suffisamment élevée pour fonctionner avec des tableaux de données volumineux, cependant, il convient de rappeler que toutes les opérations R sont effectuées en mémoire et qu'il y a un risque d'aller à l'échange s'il y a un manque de mémoire du système d'exploitation pour répondre aux besoins de Vertica et R travaillant ensemble .

Si la fonction est appelée sur la partition des données spécifiées dans PARTITION BY pour OVER, Vertica parallélise l'exécution de chaque partition sur les serveurs de cluster. Ainsi, si un fabricant était toujours présent dans l'ensemble de données en plus du modèle de machine, vous pouvez le spécifier dans PARTITION BY et paralléliser l'analyse pour chaque fabricant.

Autres opportunités d'apprentissage machine Vertica


En plus de R, Vertica peut développer ses propres fonctions de transformation en C, Java et Python. Chaque langue a ses propres nuances et caractéristiques d'écriture et de connexion à Vertica. Avec son propre ML, tout cela donne à Vertica une bonne réserve pour l'analyse prédictive des données.

Remerciements et liens


Je tiens à remercier sincèrement mon ami et collègue Vlad Malofeev de Perm, qui m'a présenté R et m'a aidé à le découvrir dans l'un de nos projets communs.

Initialement, dans un projet où une prévision a été faite sur des conditions difficiles pour l'avenir en utilisant les données de l'année dernière, les développeurs ont essayé d'utiliser SQL et Java. Cela a engendré de grandes difficultés de prise en compte de la qualité de ces sources et a fortement ralenti le développement du projet. Vlad est venu avec le projet avec R, nous avons connecté R à Vertica, il a conduit les données au studio et tout a tourné et tourné magnifiquement tout de suite. Littéralement en quelques semaines, tout ce qui a duré des mois a été ratissé, sauvant le projet d'un code complexe.

Les exemples de données avec des voitures peuvent être téléchargés à partir du référentiel GIT:

 git clone https://github.com/vertica/Machine-Learning-Examples

et téléchargez sur Vertica:

 /opt/vertica/bin/vsql -d <name of your database> -f load_ml_data.sql

Si vous voulez approfondir le ML et apprendre à travailler avec R, je vous recommande un livre en russe «R en action». Analyse et visualisation des données en langage R. » Il est écrit dans un langage humain simple et accessible et convient aux débutants qui n'ont jamais rencontré d'apprentissage automatique.

Vous pouvez voir ici des informations sur la connexion de la bibliothèque R à Vertica.

Pour ceux qui ont déjà commencé à apprendre et à utiliser ML en Python, il convient de prêter attention à l'IDE Rodeo, c'est un analogue de R Studio, car sans analyse de qualité interactive, c'est impossible. Je pense que tout ce qui est décrit dans cet article sous R de manière similaire peut être développé en Python, y compris l'enregistrement du modèle dans les fonctions UDF et le développement de fonctions d'analyse pour Vertica. Si vous cochez, n'oubliez pas de vous désabonner des résultats dans les commentaires, je serai reconnaissant pour les informations.

Merci pour votre temps et j'espère avoir pu démontrer la simplicité et les capacités incroyables de la symbiose de R et Vertica.

Source: https://habr.com/ru/post/fr436306/

More articles:


All Articles