Interface SQL ouverte

La couche de modélisation sémantique LookML de Looker permet à un analyste de données de définir des dimensions, des agrégations, des calculs et des relations entre les données dans une base de données SQL. Les modèles LookML permettent de réutiliser le code et d'intégrer Git. Un modèle LookML bien structuré permet aux utilisateurs d'explorer les données et de créer des rapports en libre-service.

Le modèle LookML constitue la base de toutes les données demandées par Looker, qu'elles proviennent de l'interface Exploration de Looker dans l'interface utilisateur de Looker, d'une visualisation intégrée dans le portail de votre entreprise ou d'une autre application tierce, ou encore d'une application personnalisée développée avec l'API Looker. L'interface Open SQL permet à toute application tierce compatible avec Java Database Connectivity (JDBC) d'accéder aux modèles LookML. Les applications peuvent se connecter à un modèle LookML comme s'il s'agissait d'une base de données. Les utilisateurs peuvent ainsi profiter de tout le travail effectué par leurs analystes de données dans le modèle LookML, tout en utilisant les outils avec lesquels ils se sentent le plus à l'aise.

Comment l'interface SQL ouverte présente les éléments du projet LookML

Pour comprendre comment l'interface Open SQL présente les éléments d'un projet LookML, il est important de comprendre comment les projets LookML sont structurés.

Un projet LookML est un ensemble de fichiers qui décrivent les objets, les connexions de base de données et les éléments d'interface utilisateur utilisés pour exécuter des requêtes SQL dans Looker (pour en savoir plus, consultez les termes et concepts LookML). Les concepts de projet LookML suivants sont liés à l'interface SQL ouverte:

  • Un modèle LookML spécifie une connexion de base de données et une ou plusieurs explorations. L'interface Open SQL présente les modèles sous forme de schémas de base de données.
  • Une exploration est un regroupement logique d'une ou plusieurs vues et des relations de jointure entre ces vues. L'interface SQL ouverte affiche les explorations en tant que tables de base de données.
  • Une vue définit un ensemble de champs (dimensions et mesures). Une vue est généralement basée sur une table de votre base de données ou une table dérivée. Les vues peuvent contenir les colonnes du tableau de base de données sous-jacent, ainsi que les dimensions ou mesures personnalisées que vos utilisateurs finaux peuvent exiger. L'interface Open SQL affiche la combinaison d'un nom de vue et d'un nom de champ sous la forme d'un nom de colonne de base de données. Par exemple, la dimension id de la vue order_items est affichée par l'interface SQL ouverte sous la forme d'une colonne de base de données appelée order_items.id.

Une exploration Looker peut définir des relations de jointure entre plusieurs vues. Étant donné qu'une vue peut avoir un champ ayant le même nom qu'un champ d'une autre vue, l'interface SQL ouverte inclut à la fois le nom de la vue et le nom du champ lorsqu'il fait référence à une colonne. Par conséquent, utilisez ce format pour référencer un nom de colonne lorsque vous envoyez des requêtes à l'interface SQL ouverte:

`<view_name>.<field_name>`

Par exemple, s'il existe une exploration nommée order_items qui joint une vue appelée customer à une vue nommée product et que ces deux vues ont une dimension id, vous appelleriez les deux champs id respectivement `customer.id` et `product.id`. Pour utiliser le nom complet avec le nom de l'exploration, vous devez faire référence aux deux champs sous la forme `order_items`.`customer.id` et `order_items`.`product.id`. (Pour savoir où placer les guillemets, consultez Utiliser des guillemets autour des identifiants de base de données.)

Configurer l'interface Open SQL

Pour utiliser l'interface Open SQL, procédez comme suit :

  1. Vérifiez que vous remplissez les conditions requises.
  2. Téléchargez le fichier du pilote JDBC Open SQL Interface.

Les sections suivantes décrivent ces étapes.

Conditions requises

Les composants suivants sont requis pour utiliser l'interface SQL ouverte:

Télécharger le pilote JDBC de l'interface SQL ouverte

Le pilote JDBC de l'interface SQL ouverte de Looker s'appelle avatica-<release_number>-looker.jar. Téléchargez la dernière version sur GitHub à l'adresse https://github.com/looker-open-source/calcite-avatica/releases.

Le pilote JDBC attend le format d'URL suivant:

jdbc:looker:url=https://Looker instance URL

Exemple :

jdbc:looker:url=https://myInstance.cloud.looker.com

La classe de pilote JDBC est la suivante:

org.apache.calcite.avatica.remote.looker.LookerDriver

S'authentifier auprès de l'interface Open SQL

L'interface Open SQL prend en charge trois méthodes d'authentification :

OAuth

Les clients JDBC compatibles avec OAuth peuvent être configurés pour utiliser le serveur OAuth d'une instance Looker. Suivez la procédure pour configurer l'authentification OAuth:

  1. Utilisez l'extension APIs Explorer pour enregistrer le client OAuth JDBC auprès de votre instance Looker afin que celle-ci puisse reconnaître les requêtes OAuth. Pour obtenir des instructions, consultez Enregistrer une application cliente OAuth.
  2. Connectez-vous à Looker avec OAuth pour demander un jeton d'accès. Vous trouverez un exemple dans la section Connexion utilisateur à l'aide du protocole OAuth.
  3. Utilisez un objet Propriétés pour transmettre les identifiants OAuth lors de l'ouverture de la connexion JDBC à une interface SQL ouverte.

Voici un exemple utilisant DriverManager#getConnection(<String>, <Properties>`):

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

Générer un jeton d'accès à l'aide de clés API

Au lieu d'utiliser le flux OAuth standard pour générer un jeton d'accès, vous pouvez suivre ces étapes pour utiliser l'API Looker afin de générer un jeton d'accès qui peut être transmis au pilote JDBC de l'interface SQL ouverte:

  1. Générez des clés API pour votre utilisateur Looker, comme indiqué sur la page Paramètres administrateur - Utilisateurs.

  2. Utilisez le point de terminaison de l'API login pour votre instance Looker. La réponse inclut un jeton d'accès au format Authorization: token <access_token>. Voici un exemple de commande curl que vous pouvez utiliser pour envoyer cette requête:

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Transmettez la valeur <access_token> de la réponse en tant que jeton dans l'objet Properties pour transmettre les identifiants OAuth lors de l'ouverture de la connexion JDBC à l'interface Open SQL.

Clés API

Vous pouvez également utiliser des clés API pour vous authentifier à la place d'un nom d'utilisateur et d'un mot de passe. Les clés API sont considérées comme moins sécurisées que OAuth et ne peuvent être disponibles que pendant la phase de prévisualisation de l'interface Open SQL. Pour savoir comment créer des clés API pour votre instance Looker, consultez la section Clés API.

Utilisez la partie ID client de la clé API Looker comme nom d'utilisateur. Utilisez la partie Code secret du client pour le mot de passe.

Exécuter des requêtes avec l'interface Open SQL

Tenez compte des consignes suivantes lorsque vous exécutez des requêtes avec l'interface SQL ouverte:

Limites de LookML

Tenez compte des points suivants lorsque vous envoyez des requêtes à l'interface SQL ouverte:

  • Vous pouvez utiliser une clause WHERE dans une requête Open SQL Interface pour transmettre des valeurs always_filter et conditionally_filter à votre modèle LookML.

Limites SQL

Notez les limites SQL suivantes lorsque vous envoyez des requêtes à l'interface SQL ouverte:

Utiliser des accents graves autour des identifiants de base de données

Lorsque vous envoyez des requêtes à l'interface SQL ouverte, utilisez des accents graves autour des identifiants de schéma, de table et de colonne. Voici comment spécifier des éléments de base de données à l'aide d'accents graves avec les termes Looker:

  • schema : `<model_name>`
  • tableau: `<explore_name>`

  • colonne : `<view_name>.<field_name>`

Voici un exemple de format d'instruction SELECT utilisant ces éléments :

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

Spécifier les mesures LookML avec AGGREGATE()

Les tables de base de données ne contiennent généralement que des dimensions, des données qui décrivent un seul attribut d'une ligne de la table. En revanche, les projets LookML peuvent définir à la fois des dimensions et des mesures. Une mesure est un regroupement de données sur plusieurs lignes, par exemple SUM, AVG, MIN ou MAX. D'autres types de mesures sont également acceptés. Consultez la page Types de mesures pour obtenir la liste complète des types de mesures LookML compatibles.

Avec l'interface Open SQL, vous devez désigner toutes les mesures LookML incluses dans une requête en encapsulant la mesure (y compris les guillemets inversés) dans la fonction spéciale AGGREGATE(). Par exemple, utilisez ceci pour spécifier la mesure count à partir de la vue orders:

AGGREGATE(`orders.count`)

Vous devez encapsuler les mesures LookML dans la fonction AGGREGATE(), que la mesure se trouve dans une clause SELECT, HAVING ou ORDER BY.

Si vous ne savez pas si un champ est une mesure LookML, vous pouvez utiliser la méthode DatabaseMetaData.getColumns pour accéder aux métadonnées du projet LookML. La colonne IS_GENERATEDCOLUMN indique YES pour toutes les mesures LookML et NO pour les dimensions LookML. Pour en savoir plus, consultez la section Accéder aux métadonnées de la base de données.

Exemple

Voici un exemple de requête utilisant à la fois des dimensions et des mesures. Cette requête récupère les dimensions État et Ville de la vue Clients, ainsi que la métrique Montant total de la vue Commandes. Ces deux vues sont jointes à l'exploration orders dans le modèle ecommerce. Pour les villes qui ont plus de 10 commandes, cette réponse à la requête affiche les 5 premières villes par montant de commande:

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Spécifier des champs et des paramètres de filtre uniquement avec JSON_OBJECT

L'interface SQL ouverte est compatible avec les paramètres et les champs de filtre uniquement.

Lorsque vous exécutez des requêtes avec une interface SQL ouverte, vous pouvez y appliquer des paramètres et des champs de filtre uniquement en incluant un appel de constructeur JSON_OBJECT au format suivant:

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

L'objet JSON peut contenir zéro, une ou plusieurs paires clé-valeur de filtre, ainsi que zéro, une ou plusieurs paires clé-valeur de paramètres.

  • La clé du constructeur JSON_OBJECT doit être le nom d'un champ ou d'un paramètre de filtre uniquement.
  • Pour les champs de filtre uniquement, la valeur de chaque clé doit être une expression de filtre de chaîne Looker.
  • Pour les paramètres, la valeur de chaque clé doit être une valeur simple définie dans la définition de parameter.

Consultez les sections suivantes pour découvrir des exemples d'utilisation des paramètres et des champs de filtre uniquement avec l'interface Open SQL.

Exemple de paramètre

Par exemple, si la vue customers avait un paramètre défini dans Looker comme suit :

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

Vous pouvez envoyer cette requête à l'interface Open SQL pour appliquer la valeur du paramètre segment (medium_customers) à la requête :

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Open SQL Interface transmettra cette valeur de paramètre à la requête dans Looker, et Looker appliquera la valeur medium_customers à tous les champs de l'exploration configurés pour utiliser le paramètre segment. Consultez la documentation parameter pour en savoir plus sur le fonctionnement des paramètres dans Looker.

Exemple de champ de filtre uniquement

Vous pouvez utiliser un champ filter avec l'interface Open SQL. Par exemple, si une vue products comporte une dimension et un champ de filtre uniquement défini dans Looker comme suit :

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

Vous pouvez utiliser le filtre brand_select avec une interface SQL ouverte en envoyant une requête de ce type:

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

L'interface SQL ouverte appliquera l'expression de filtre de chaîne Looker %Santa Cruz% à la requête dans Looker. Consultez la documentation sur filter pour en savoir plus sur le fonctionnement des champs de filtre uniquement dans Looker.

Accéder aux métadonnées de la base de données

L'interface Open SQL est compatible avec un sous-ensemble de l'interface DatabaseMetaData standard de JDBC, qui permet d'obtenir des informations sur la base de données sous-jacente. Vous pouvez utiliser les méthodes suivantes de l'interface DatabaseMetaData pour obtenir des informations sur votre modèle LookML :

DatabaseMetadata.getSchemas

Le tableau suivant décrit comment un modèle LookML se rapporte aux structures de base de données standards dans la réponse de la méthode d'interface DatabaseMetadata.getSchemas.

Colonne de réponse getSchemas Description
TABLE_SCHEM Nom du modèle LookML
TABLE_CATALOG (nul)

DatabaseMetadata.getTables

Le tableau suivant décrit la relation entre un modèle LookML et les structures de base de données dans la réponse de la méthode d'interface DatabaseMetaData.getTables. La réponse inclut des métadonnées JDBC standards ainsi que des métadonnées spécifiques à Looker :

Colonne de réponse getTables Description
Métadonnées standards JDBC
TABLE_CAT (nul)
TABLE_SCHEM Nom du modèle LookML
TABLE_NAME Nom de l'exploration LookML
TABLE_TYPE Renvoie toujours la valeur TABLE_TYPE
Métadonnées spécifiques à Looker
DESCRIPTION Description
LABEL Explorer label
TAGS Découvrir les tags

DatabaseMetadata.getColumns

Le tableau suivant décrit la relation entre un modèle LookML et les structures de base de données dans la réponse de la méthode d'interface DatabaseMetaData.getColumns. La réponse inclut des métadonnées JDBC standards ainsi que des métadonnées spécifiques à Looker:

Colonne de réponse getColumns Description
Métadonnées standards JDBC
TABLE_CAT (nul)
TABLE_SCHEM Nom du modèle LookML
TABLE_NAME Nom de l'exploration LookML
COLUMN_NAME Nom du champ LookML au format `<view_name>.<field_name>`. Exemple :`orders.amount`
DATA_TYPE Code java.sql.Types de la colonne. Par exemple, les champs yesno Looker sont du code de type SQL 16 (BOOLEAN).
ORDINAL_POSITION Ordinal du champ dans l'exploration (mélangeant les dimensions et les mesures par ordre alphabétique, d'abord par nom de la vue, puis par nom du champ)
IS_NULLABLE Renvoie toujours la valeur YES
IS_GENERATEDCOLUMN YES pour les mesures, NO pour les dimensions
Métadonnées spécifiques à Looker
DIMENSION_GROUP Nom du groupe de dimensions si le champ fait partie d'un groupe de dimensions. Si le champ ne fait pas partie d'un groupe de dimensions, la valeur sera nulle.
DRILL_FIELDS Liste des champs d'analyse définis pour la dimension ou la mesure, le cas échéant
FIELD_ALIAS Alias du champ, le cas échéant
FIELD_CATEGORY Indique si le champ est de type dimension ou measure
FIELD_DESCRIPTION Champ description
FIELD_GROUP_VARIANT Si le champ est présenté sous le libellé group label (étiquette de groupe), FIELD_GROUP_VARIANT spécifie le nom plus court du champ affiché sous le libellé de groupe.
FIELD_LABEL Champ label
FIELD_NAME Nom de la dimension ou de la mesure
HIDDEN Indique si le champ est masqué dans le sélecteur de champs des explorations (TRUE) ou s'il est visible dans le sélecteur de champs des explorations (FALSE).
LOOKER_TYPE Type de champ LookML pour la dimension ou la mesure
REQUIRES_REFRESH_ON_SORT Indique si la requête SQL doit être actualisée pour réorganiser les valeurs du champ (TRUE) ou si les valeurs du champ peuvent être réorganisées sans nécessiter d'actualisation de la requête SQL (FALSE).
SORTABLE Indique si le champ peut être trié (TRUE) ou non (FALSE).
TAGS Tags de champ
USE_STRICT_VALUE_FORMAT Indique si le champ utilise un format de valeur strict (TRUE) ou non (FALSE)
VALUE_FORMAT Chaîne du format de valeur du champ
VIEW_LABEL Afficher le libellé du champ
VIEW_NAME Nom de la vue dans laquelle le champ est défini dans le projet LookML

Identifier les requêtes de l'interface SQL ouverte dans l'interface utilisateur de Looker

Les administrateurs Looker peuvent utiliser l'interface utilisateur de Looker pour identifier les requêtes provenant de l'interface SQL ouverte:

  • Sur la page d'administration Requêtes, les requêtes de l'interface SQL ouverte ont la valeur Source "Interface SQL". La valeur Utilisateur indique le nom de l'utilisateur Looker qui a exécuté la requête. Vous pouvez cliquer sur le bouton Détails d'une requête pour afficher des informations supplémentaires la concernant. Dans la boîte de dialogue Détails, vous pouvez cliquer sur Requête de l'interface SQL pour afficher la requête SQL envoyée à Looker depuis l'interface SQL ouverte.

  • Dans l'exploration de l'historique de l'activité du système, la valeur Source des requêtes de l'interface Open SQL est "sql_interface". La valeur Adresse e-mail de l'utilisateur affiche l'adresse e-mail de l'utilisateur Looker qui a exécuté la requête. Vous pouvez accéder directement à l'exploration History (Historique) filtrée sur "sql_interface". en insérant l'adresse de votre instance Looker au début de cette URL:

    https://Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Dépôt pour les dépendances tierces

Le lien suivant permet d'accéder au dépôt hébergé par Google pour les dépendances tierces utilisées par le pilote JDBC Looker:

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/