Tutoriel : Comment définir des fonctions SQL avec Presto sur tous les connecteurs

Presto est le moteur de requête SQL open source pour les lacs de données. Il prend en charge de nombreuses fonctions natives qui sont généralement suffisantes pour la plupart des cas d’utilisation. Cependant, il existe peut-être un cas particulier où vous devez implémenter votre propre fonction. Pour simplifier cela, Presto permet aux utilisateurs de définir des expressions en tant que fonctions SQL. Ce sont des fonctions dynamiques séparées du code source Presto, gérées par un gestionnaire d’espaces de noms de fonctions que vous pouvez configurer avec une base de données MySQL. En fait, c’est l’une des fonctionnalités les plus utilisées de Presto sur Facebook, avec plus de 1000 fonctions définies.

Gestionnaire d’espace de noms de fonction

Un espace de noms de fonction est un catalog.schema qui stocke les fonctions dans le format comme mysql.test. Chaque catalog.schema peut être un espace de noms de fonction. Un gestionnaire d’espace de noms de fonction est un plugin qui gère un ensemble de ces schémas de catalogue de fonctions. Le catalogue peut être mappé à des connecteurs dans Presto (un connecteur pour les fonctions, pas de tableaux ni de vue) et permet au moteur Presto d’effectuer des actions telles que la création, la modification et la suppression de fonctions.

Cette gestion de fonction définie par l’utilisateur est séparée de l’API du connecteur pour plus de flexibilité, ces fonctions SQL peuvent donc être utilisées sur tous les connecteurs. De plus, la requête est garantie d’utiliser la même version de la fonction tout au long de l’exécution et toute modification des fonctions est versionnée.

Mise en œuvre

Aujourd’hui, le gestionnaire d’espace de noms de fonction est implémenté à l’aide de MySQL, les utilisateurs doivent donc disposer d’un service MySQL en cours d’exécution pour initialiser le gestionnaire d’espace de nom de fonction basé sur MySQL.

Étape 1 : Fournissez le serveur MySQL et générez l’URL JDBC pour un accès supplémentaire

Supposons que le serveur MySQL soit accessible à l’adresse localhost:3306, exemple d’URL de base de données –

jdbc:mysql://localhost:3306/presto?user=root&password=password

Étape 2 : Créer une base de données et des tables dans la base de données MySQL pour stocker les données liées au gestionnaire d’espace de noms de fonction

 CREATE DATABASE presto;
 USE presto;

Étape 3 : Configurer chez Presto [2]

Créez la configuration du gestionnaire d’espace de noms de fonction sous etc/function-namespace/mysql.properties :

function-namespace-manager.name=mysql database-url=jdbc:mysql://localhost:3306/presto?user=root&password=password
function-namespaces-table-name=function_namespaces
functions-table-name=sql_functions

Et redémarrez le service Presto.

Étape 4 : Créer un nouveau nom de fonction

Maintenant, une fois le serveur Presto démarré, nous verrons ci-dessous les tableaux sous la base de données presto (qui est utilisée pour gérer l’espace de noms des fonctions) dans Mysql –

mysql> show tables;
+---------------------+
| Tables_in_presto    |
+---------------------+
| enum_types          |
| function_namespaces |
| sql_functions       |
+---------------------+
93 rows in set (0.00 sec)

Pour créer un nouvel espace de noms de fonction « ahana.default », insérez dans la table function_namespaces :

INSERT INTO function_namespaces (catalog_name, schema_name)
    VALUES('ahana', 'default');

Étape 5 : Créer une fonction et une requête à partir de Presto [1]

Voici un exemple simple de fonction SQL pour COSECANT :

presto>CREATE OR REPLACE FUNCTION ahana.default.cosec(x double)
RETURNS double
COMMENT ‘Cosecant trigonometric function'
LANGUAGE SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
RETURN 1 / sin(x);

D’autres exemples sont disponibles sur https://prestodb.io/docs/current/sql/create-function.html#examples [1]

Étape 6 : appliquer la fonction nouvellement créée et la requête SQL

Les utilisateurs doivent utiliser un nom de fonction complet lors de l’utilisation dans une requête SQL.

En suivant l’exemple de l’utilisation de la fonction SQL cosec dans la requête.

Voici un autre exemple simple de création d’un EpochTimeToLocalDate fonction pour convertir l’heure Unix en fuseau horaire local sous ahana.default espace de noms de fonction.

presto> sélectionnez ahana.default.cosec (50) comme Cosec_value;  Cosec_value --------------------- -3.8113408578721053 (1 ligne) Requête 20211103_211533_00002_ajuyv, FINISHED, 1 node Splits : 33 total, 33 done (100.00%) 0:00 [0 rows, 0B] [0 rows/s, 0B/s

presto> CREATE FUNCTION ahana.default. EpochTimeToLocalDate (x bigint) 
     -> RETURNS timestamp 
     -> LANGUAGE SQL 
     -> DETERMINISTIC RETURNS NULL ON NULL INPUT 
     -> RETURN from_unixtime (x);
CREATE FUNCTION

presto> select ahana.default.EpochTimeToLocalDate(1629837828000) as date;
           date            
---------------------------
 +53617-07-03 11:00:00.000 
(1 row)

Query 20211101_230315_00043_ajuyv, FINISHED, 1 node
Splits: 33 total, 33 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]

Noter

function-namespaces-table-name La propriété peut être utilisée s’il existe un cas d’utilisation pour instancier plusieurs gestionnaires d’espaces de noms de fonctions, sinon si nous pouvons créer des fonctions dans un seul gestionnaire d’espaces de noms de fonctions, cela peut être utilisé dans toutes les différentes bases de données ou connecteurs. [2]

Feuille de route future

Fonction à distance Prise en charge de l’API d’économie UDF à distance

Vous permet d’exécuter des fonctions arbitraires qui ne sont pas sûres ou impossibles à exécuter dans la JVM de travail : fonctions Java peu fiables, C++, Python, etc.

Les références

[1] DDL Syntax to use FUNCTIONS

[2] Function Namespace Manager Documentation