Ouvrir l'interface SQL

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'effectuer eux-mêmes leur propre exploration et création de rapports de données en libre-service.

Le modèle LookML est la base de toutes les données demandées à Looker, que cette requête provienne de l'interface d'exploration Looker dans l'UI Looker, d'une visualisation intégrée dans le portail de votre entreprise ou d'une autre application tierce, ou 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, ce qui permet aux utilisateurs de 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 Open SQL affiche 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 Open SQL:

  • 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 Open SQL affiche les explorations sous forme de 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 Open SQL 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 portant le même nom qu'un champ d'une autre vue, l'interface Open SQL inclut à la fois le nom de la vue et le nom du champ lors de la référence à une colonne. Par conséquent, utilisez ce format pour faire référence à un nom de colonne lorsque vous envoyez des requêtes à l'interface Open SQL:

`<view_name>.<field_name>`

Par exemple, si une exploration nommée order_items associe une vue nommée customer à une vue nommée product, et que ces deux vues comportent une dimension id, vous devez faire référence aux deux champs id sous les noms `customer.id` et `product.id`, respectivement. 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 Open SQL:

Télécharger le pilote JDBC Open SQL Interface

Le pilote JDBC de l'interface SQL ouverte 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 du pilote JDBC est:

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 les étapes ci-dessous pour configurer l'authentification OAuth:

  1. Utilisez l'extension de l'explorateur d'API pour enregistrer le client OAuth JDBC auprès de votre instance Looker afin qu'elle 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. Pour en savoir plus, consultez Effectuer la connexion des utilisateurs à l'aide d'OAuth.
  3. Utilisez un objet Properties pour transmettre les identifiants OAuth lorsque vous ouvrez la connexion JDBC à l'interface Open SQL.

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 générer un jeton d'accès à l'aide de l'API Looker, qui peut être transmis au pilote JDBC Open SQL Interface:

  1. Générez des clés API pour votre utilisateur Looker, comme décrit sur la page Paramètres d'administration - 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 sont peut-être disponibles que pendant la phase de prévisualisation de l'interface SQL ouverte. 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 Open SQL:

Limites SQL

Tenez compte des limitations SQL suivantes lorsque vous envoyez des requêtes à l'interface Open SQL:

Utiliser des accolades autour des identifiants de base de données

Lorsque vous envoyez des requêtes à l'interface Open SQL, utilisez des accolades 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 de crochets avec des termes Looker:

  • schema : `<model_name>`
  • table: `<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 des 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 une agrégation de données sur plusieurs lignes, telles que SUM, AVG, MIN ou MAX. (D'autres types de mesures sont également acceptés. Pour obtenir la liste complète des types de mesures LookML acceptés, consultez la page Types de mesures.)

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 cette option pour spécifier la métrique nombre à partir de la vue Commandes:

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.

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

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

Lorsque vous exécutez des requêtes avec l'interface Open SQL, vous pouvez appliquer des paramètres et des champs de filtre uniquement à la requête 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 ou plusieurs paires clé-valeur de filtre, ainsi que zéro ou plusieurs paires clé-valeur de paramètre.

  • 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 voir 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

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 sur 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 l'interface Open SQL en envoyant une requête telle que la suivante:

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. Pour en savoir plus sur le fonctionnement des champs de filtre uniquement dans Looker, consultez la documentation sur filter.

Fournir des valeurs always_filter ou conditionally_filter dans une clause WHERE ou HAVING

L'interface Open SQL est compatible avec une exploration qui contient always_filter ou conditionally_filter, mais pas les deux.

Si vous avez défini votre exploration LookML avec always_filter ou conditionally_filter, vous devez transmettre les valeurs des champs de filtre de votre requête SQL à l'interface Open SQL:

  • Si la définition du filtre spécifie une ou plusieurs dimensions, vous devez inclure une clause WHERE dans votre requête SQL pour chacune des dimensions du filtre.
  • Si la définition du filtre spécifie une ou plusieurs mesures, vous devez inclure une clause HAVING dans votre requête SQL pour chacune des mesures de filtre.

Par exemple, vous avez défini une exploration LookML flights avec un paramètre always_filter qui spécifie les dimensions country et aircraft_category, ainsi que la mesure count, comme suit :faa 

explore: flights {
  view_name: flights
  always_filter: {
    filters: [country : "Peru" , aircraft_category : "Airplane", count : ">1"]
  }
}

Dans votre requête à l'interface Open SQL, vous devez utiliser une clause WHERE pour transmettre des valeurs pour les dimensions de filtre et une clause HAVING pour transmettre une valeur pour le filtre de mesure à votre modèle LookML, comme suit:

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      WHERE `flights.country` = 'Ecuador' AND `flights.aircraft_category` = 'Airplane'
      GROUP BY
          1
      HAVING `flights.count` > 2) 
LIMIT 5

Si vous ne transmettez pas de valeurs de filtre pour chacune des dimensions et mesures spécifiées dans le paramètre always_filter, la requête renvoie une erreur. Il en va de même pour les dimensions et les mesures spécifiées dans un paramètre conditionally_filter, sauf que vous pouvez définir un paramètre conditionally_filter avec un sous-paramètre unless, comme suit:

explore: flights {
  view_name: flights
  conditionally_filter: {
    filters: [country : "Peru" , aircraft_category : "Airplane"]
    unless: [count]
  }
}

Dans ce cas, vous devez transmettre une valeur de filtre pour chacune des dimensions et mesures spécifiées dans le sous-paramètre filters de conditionally_filter, sauf si vous spécifiez plutôt un filtre sur un champ dans le sous-paramètre unless. (Pour en savoir plus sur l'utilisation du sous-paramètre unless, consultez la page de documentation conditionally_filter.)

Par exemple, l'une des requêtes suivantes à l'interface Open SQL est acceptable. La première requête fournit des valeurs de filtre pour les champs spécifiés dans le sous-paramètre filters, et la seconde requête fournit une valeur de filtre pour le champ spécifié dans le sous-paramètre unless:

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      WHERE `flights.country` = 'Ecuador' AND `flights.aircraft_category` = 'Airplane'
      
LIMIT 5

SELECT
    `flights.make`
FROM
    `faa`.`flights`
      GROUP BY
          1
      HAVING `flights.count` > 2

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 dans l'exploration Commandes du modèle e-commerce. Pour les villes ayant enregistré plus de 10 commandes, cette réponse à la requête affiche les cinq 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;

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 JDBC standard, 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 (vide)

DatabaseMetadata.getTables

Le tableau suivant décrit comment un modèle LookML se rapporte aux 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 (vide)
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 le libellé
TAGS Explorer les tags

DatabaseMetadata.getColumns

Le tableau suivant décrit comment un modèle LookML se rapporte aux 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 (vide)
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 de Looker sont du 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, cette valeur est 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 un dimension ou un measure
FIELD_DESCRIPTION Champ description
FIELD_GROUP_VARIANT Si le champ est présenté sous un libellé 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 Champ tags
USE_STRICT_VALUE_FORMAT Indique si le champ utilise le format de valeur strict (TRUE) ou non (FALSE).
VALUE_FORMAT Chaîne Format de valeur pour le 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 Looker pour identifier les requêtes provenant de l'interface Open SQL:

  • Sur la page d'administration Requêtes, la valeur Source des requêtes de l'interface SQL ouverte est "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 à son sujet. 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 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]=%22sql_interface%22

Dépôt de 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/