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 de 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 à Looker, qu'elles proviennent de l'interface Explorer de Looker dans l'UI Looker, d'une visualisation intégrée au 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 d'accéder aux modèles LookML à toute application tierce compatible avec la connectivité 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 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 sont les plus à l'aise.

Comment l'interface Open SQL présente les éléments d'un projet LookML

Pour comprendre comment l'interface Open SQL 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 de l'interface utilisateur utilisés pour exécuter des requêtes SQL dans Looker (pour en savoir plus, consultez la section Termes et concepts LookML). Les concepts de projet LookML suivants sont liés à l'interface Open SQL:

  • 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 sous forme de schémas de base de données.
  • Une exploration est un regroupement logique d'une ou de plusieurs vues et des relations de jointure entre ces vues. L'interface Open SQL présente les explorations sous la forme de tables de base de données.
  • Une vue définit une collection 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 requises par les utilisateurs finaux. L'interface Open SQL présente 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 Open SQL en tant que 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 du référencement d'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 joint une vue appelée customer à une vue appelée product et que ces deux vues ont une dimension id, vous devez désigner les deux champs id par `customer.id` et `product.id`, respectivement. Pour utiliser également le nom complet avec le nom de l'exploration, vous devez faire référence aux deux champs comme suit : `order_items`.`customer.id` et `order_items`.`product.id`. Pour savoir où placer des accents graves dans les identifiants de base de données, consultez Utiliser des accents graves 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 les exigences sont remplies.
  2. Activez l'interface SQL ouverte sur votre instance Looker.
  3. 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:

Activer l'interface SQL ouverte sur votre instance Looker

Pour activer l'interface Open SQL sur votre instance, procédez comme suit:

Télécharger le pilote JDBC Open SQL Interface

Le pilote JDBC de l'interface Open SQL 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 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 ci-dessous 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 la section Enregistrer une application cliente OAuth.
  2. Connectez-vous à Looker avec OAuth pour demander un jeton d'accès. Pour obtenir un exemple, consultez Se connecter aux utilisateurs avec OAuth.
  3. Utilisez un objet Properties (Propriétés) pour transmettre les identifiants OAuth lors de l'ouverture de 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 utiliser l'API Looker afin de générer un jeton d'accès qui peut être transmis au pilote JDBC de l'interface Open SQL:

  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 permettant d'effectuer 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" (Propriétés) 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 qu'OAuth et peuvent uniquement être disponibles en preview de l'interface Open SQL. Consultez Clés API pour 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 comme 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 de LookML

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

  • L'interface Open SQL n'est compatible qu'avec les dimensions et les mesures Looker. L'interface Open SQL n'est pas compatible avec les paramètres LookML filter ou parameter.
  • L'interface Open SQL ne peut pas être utilisée pour remplacer les valeurs de always_filter et conditionally_filter définies dans le modèle LookML.

Limites de SQL

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

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

Lorsque vous envoyez des requêtes à l'interface Open SQL, 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>`
  • 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 décrivant un seul attribut au sujet d'une ligne de la table. Les projets LookML, en revanche, peuvent définir à la fois des dimensions et des mesures. Une mesure est une agrégation de données sur plusieurs lignes, par exemple 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 accents graves) dans la fonction spéciale AGGREGATE(). Par exemple, utilisez ce code 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 et city de la vue customers, et la mesure du montant total de la vue orders. Ces deux vues sont jointes dans 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 réservés au filtrage avec JSON_OBJECT.

L'Open SQL Interface accepte les paramètres et les champs de filtre uniquement.

Lorsque vous exécutez des requêtes avec l'interface SQL ouverte, 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, une ou plusieurs paires clé-valeur de filtre et des 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 parameter.

Consultez les sections suivantes pour obtenir des exemples d'utilisation des paramètres et des champs de type "filtrer uniquement" avec l'interface SQL ouverte.

Exemple de paramètre

Voici un exemple d'utilisation d'un parameter avec une interface Open SQL si un paramètre de la vue customers est défini dans Looker:

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 lui appliquer la valeur du paramètre segment de medium_customers:

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 l'interface SQL ouverte. Par exemple, si une vue products comporte une dimension et un champ de filtre 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 l'interface SQL ouverte en envoyant une requête comme celle-ci:

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 filter pour en savoir plus sur le fonctionnement des champs réservés au filtrage 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.

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 de la norme 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 de l'exploration
LABEL Explorer le libellé
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 de la norme 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 de Looker correspondent au code de type SQL 16 (BOOLEAN).
ORDINAL_POSITION Ordinal en base 1 du champ dans l'exploration (combinaison de dimensions et de 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 "null".
DRILL_FIELDS Liste des champs d'entraînement définis pour la dimension ou la mesure, le cas échéant
FIELD_ALIAS Alias du champ, le cas échéant
FIELD_CATEGORY Indique s'il s'agit d'un champ dimension ou measure
FIELD_DESCRIPTION Description du champ
FIELD_GROUP_VARIANT Si le champ se présente sous un libellé de groupe de champ, FIELD_GROUP_VARIANT spécifie le nom plus court du champ qui s'affiche 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 dans les explorations (TRUE) ou s'il est visible dans le sélecteur de champs dans les 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 pouvoir trier à nouveau les valeurs du champ (TRUE) ou si les valeurs du champ peuvent être triées de nouveau sans qu'il soit nécessaire d'actualiser la requête SQL (FALSE).
SORTABLE Indique si le champ peut être trié (TRUE) ou ne peut pas l'être (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 de format de la 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 Open SQL dans l'UI Looker

Les administrateurs Looker peuvent utiliser l'interface utilisateur de 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 est définie sur "Interface SQL". La valeur User (Utilisateur) indique le nom de l'utilisateur Looker qui a exécuté la requête.

  • Dans l'exploration de l'historique de l'activité système, les requêtes de l'interface Open SQL ont la valeur Source "sql_interface". La valeur Adresse e-mail de l'utilisateur indique l'adresse e-mail de l'utilisateur Looker ayant exécuté la requête. Vous pouvez accéder directement à l'exploration de l'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

Contactez looker-sql-interface@google.com si vous avez des questions ou des demandes de fonctionnalités concernant l'interface Open SQL.