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 est à 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 d'accéder aux modèles LookML à toute application tierce compatible avec JDBC (Java Database Connectivity). 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 d'exploiter tout le travail effectué par leurs analystes de données dans le modèle LookML, tout en utilisant les outils avec lesquels ils sont le plus à l'aise.

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

Pour comprendre comment l'interface SQL ouverte présente les éléments d'un projet LookML, il est important de comprendre la structure des projets LookML.

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

  • Un model LookML spécifie une connexion de base de données et une ou plusieurs explorations. L'interface Open SQL présente des modèles en tant que schémas de base de données.
  • Une exploration est un regroupement logique d'une ou plusieurs vues, ainsi que les 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 de la table de base de données sous-jacente, ainsi que les dimensions ou mesures personnalisées dont vos utilisateurs finaux peuvent avoir besoin. L'interface SQL ouverte affiche la combinaison d'un nom de vue et d'un nom de champ en tant que nom de colonne de base de données. Par exemple, la dimension id dans 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 également le nom complet avec le nom de l'exploration, vous devez faire référence aux deux champs sous `order_items`.`customer.id` et `order_items`.`product.id`. Pour en savoir plus sur l'emplacement des guillemets obliques autour des identifiants de base de données, consultez Utiliser des accents graves autour des identifiants de base de données.

Configurer l'interface SQL ouverte

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

  1. Vérifiez que les exigences sont remplies.
  2. Activez l'interface SQL ouverte sur votre instance Looker.
  3. Téléchargez le fichier du pilote JDBC de l'interface SQL ouverte.

Les sections suivantes décrivent ces étapes.

Conditions requises

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

Activer l'interface SQL ouverte sur votre instance Looker

Activez l'interface SQL ouverte sur votre instance en procédant comme suit:

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://your 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 sur l'interface SQL ouverte

L'interface SQL ouverte 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 l'article 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 avec 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 permettant d'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 Propriétés pour transmettre les identifiants OAuth lors de l'ouverture de la connexion JDBC à l'interface SQL ouverte.

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 qu'OAuth et ne sont disponibles que dans la version preview de l'interface SQL ouverte. Consultez Clés API afin d'en savoir plus sur la création de clés API pour votre instance Looker.

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 SQL ouverte

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

Limites de LookML

Notez les limites LookML suivantes lors de l'envoi de requêtes à l'interface SQL ouverte:

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 des 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 tableaux de bases de données ne contiennent généralement que des dimensions, c'est-à-dire des données décrivant un seul attribut associé à une ligne du tableau. 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 SQL ouverte, vous devez désigner toutes les mesures LookML incluses dans une requête en encapsulant la mesure (en intégrant les accents graves) 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 state (État) et city (ville) de la vue customers (clients) et total amount (montant total) de la vue orders (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écifiez 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, et 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 réservé au filtrage.
  • Pour les champs réservés au filtrage, 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 obtenir des exemples d'utilisation de paramètres et de champs de filtre uniquement avec l'interface SQL ouverte.

Exemple de paramètre

Voici un exemple d'utilisation d'un parameter avec une interface SQL ouverte, si le paramètre de la vue customers est 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 SQL ouverte pour appliquer la valeur du paramètre segment de 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;

L'interface SQL ouverte transmet cette valeur de paramètre à la requête dans Looker, et Looker applique 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 une interface SQL ouverte. Par exemple, si une vue products comporte une dimension et un champ de filtrage uniquement définis 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 de filter pour en savoir plus sur le fonctionnement des champs "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 la relation entre un modèle LookML et les structures de base de données standards dans la réponse de la méthode d'interface DatabaseMetadata.getSchemas.

getSchemas colonne de réponse 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:

getTables colonne de réponse 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 Explorer la 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:

getColumns colonne de réponse 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 en base 1 du champ dans l'exploration (combinaison des dimensions et des mesures par ordre alphabétique, en fonction du nom de la vue, puis du 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 Description du champ
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 Libellé du champ
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 trier à nouveau les valeurs du champ (TRUE) ou si les valeurs du champ peuvent être triées sans actualiser 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.

  • Dans l'exploration de l'historique des activités du système, les requêtes de l'interface SQL ouverte ont la valeur Source "sql_interface". La valeur Adresse e-mail de l'utilisateur indique 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://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Commentaires sur l'interface SQL ouverte

Si vous avez des questions ou des demandes de fonctionnalités concernant l'interface SQL ouverte, contactez looker-sql-interface@google.com.