Abrir la interfaz de SQL

La capa de modelado semántico LookML de Looker permite que un analista de datos defina dimensiones, agregaciones, cálculos y relaciones de datos en una base de datos de SQL. Los modelos de LookML proporcionan reutilización de código y integración con Git. Un modelo de LookML bien estructurado permite a los usuarios realizar sus propias exploraciones de datos y generar informes de autoservicio.

El modelo de LookML es la base de todos los datos que se solicitan a Looker, ya sea que la solicitud provenga de la interfaz de Explorar de Looker en la IU de Looker, de una visualización incorporada en el portal de tu empresa o en otra aplicación de terceros, o de una aplicación personalizada que se desarrolló con la API de Looker. La interfaz de Open SQL proporciona acceso a los modelos de LookML a cualquier aplicación de terceros que admita la conectividad a bases de datos de Java (JDBC). Las aplicaciones pueden conectarse a un modelo de LookML como si fuera una base de datos, lo que permite a los usuarios aprovechar todo el trabajo que realizaron sus analistas de datos en el modelo de LookML, mientras usan las herramientas con las que se sienten más cómodos.

Cómo la interfaz de Open SQL muestra los elementos del proyecto de LookML

Para comprender cómo la interfaz de Open SQL muestra los elementos de un proyecto de LookML, es importante saber cómo se estructuran los proyectos de LookML.

Un proyecto de LookML es una colección de archivos que describen los objetos, las conexiones de bases de datos y los elementos de la interfaz de usuario que se usan para realizar consultas en SQL en Looker (consulta Términos y conceptos de LookML para obtener más información). Los siguientes conceptos del proyecto de LookML se relacionan con la interfaz de Open SQL:

  • Un modelo de LookML especifica una conexión de base de datos y una o más exploraciones. La interfaz de Open SQL expone los modelos como esquemas de bases de datos.
  • Una Exploración es una agrupación lógica de una o más vistas y las relaciones de unión entre ellas. La interfaz de Open SQL muestra los Explorar como tablas de bases de datos.
  • Una vista define una colección de campos (tanto dimensiones como mediciones). Por lo general, una vista se basa en una tabla de tu base de datos o en una tabla derivada. Las vistas pueden contener las columnas de la tabla de la base de datos subyacente, así como cualquier dimensión o medida personalizada que puedan requerir tus usuarios finales. La interfaz de Open SQL muestra la combinación de un nombre de vista y un nombre de campo como un nombre de columna de la base de datos. Por ejemplo, la interfaz de Open SQL muestra la dimensión id en la vista order_items como una columna de base de datos llamada order_items.id.

Un Explorar de Looker puede definir relaciones de unión entre varias vistas. Dado que es posible que una vista tenga un campo con el mismo nombre que un campo en otra vista, la interfaz de Open SQL incluye el nombre de la vista y el nombre del campo cuando se hace referencia a una columna. Por lo tanto, usa este formato para hacer referencia a un nombre de columna cuando envíes consultas a la interfaz de Open SQL:

`<view_name>.<field_name>`

Por ejemplo, si hubiera un Explore llamado order_items que une una vista llamada customer con una vista llamada product, y ambas vistas tuvieran una dimensión id, te referirías a los dos campos id como `customer.id` y `product.id`, respectivamente. Para usar el nombre completo con el nombre de la Exploración, también harías referencia a los dos campos como `order_items`.`customer.id` y `order_items`.`product.id`. (Consulta Usa acentos graves alrededor de los identificadores de bases de datos para obtener información sobre dónde colocar los acentos graves cuando hagas referencia a los identificadores de bases de datos).

Cómo configurar la interfaz de Open SQL

Para usar la interfaz de Open SQL, sigue estos pasos:

  1. Verifica que se cumplan los requisitos.
  2. Descarga el archivo del controlador JDBC de Open SQL Interface.

En las siguientes secciones, se describen estos pasos.

Requisitos

Se requieren los siguientes componentes para usar la interfaz de Open SQL:

Descarga el controlador JDBC de Open SQL Interface

El controlador JDBC de la interfaz de SQL abierta de Looker se llama avatica-<release_number>-looker.jar. Descarga la versión más reciente de GitHub en https://github.com/looker-open-source/calcite-avatica/releases.

El controlador JDBC espera el siguiente formato de URL:

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

Por ejemplo:

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

La clase del controlador JDBC es la siguiente:

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

Autenticación en la interfaz de Open SQL

La interfaz de Open SQL admite tres métodos de autenticación:

OAuth

Los clientes de JDBC que admiten OAuth se pueden configurar para usar el servidor de OAuth de una instancia de Looker. Sigue estos pasos para configurar la autenticación de OAuth:

  1. Usa la extensión del Explorador de APIs para registrar el cliente de OAuth de JDBC en tu instancia de Looker, de modo que esta pueda reconocer las solicitudes de OAuth. Consulta Cómo registrar una aplicación cliente de OAuth para obtener instrucciones.
  2. Accede a Looker con OAuth para solicitar un token de acceso. Consulta Cómo realizar el acceso de usuarios con OAuth para ver un ejemplo.
  3. Usa un objeto Properties para pasar las credenciales de OAuth cuando abras la conexión JDBC a Open SQL Interface.

A continuación, se muestra un ejemplo en el que se usa 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);

Cómo generar un token de acceso con claves de API

En lugar de usar el flujo estándar de OAuth para generar un token de acceso, puedes seguir estos pasos para usar la API de Looker y generar un token de acceso que se pueda pasar al controlador JDBC de la interfaz de SQL abierta:

  1. Genera claves de API para tu usuario de Looker, como se describe en la página Configuración del administrador: Usuarios.

  2. Usa el extremo de la API de login para tu instancia de Looker. La respuesta incluye un token de acceso en el formato Authorization: token <access_token>. A continuación, se muestra un ejemplo del comando curl que puedes usar para realizar esta solicitud:

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

  3. Pasa el valor <access_token> de la respuesta como el token en el objeto Properties para pasar las credenciales de OAuth cuando se abra la conexión JDBC a la interfaz de Open SQL.

Claves de API

También puedes usar claves de API para autenticarte en lugar de un nombre de usuario y una contraseña. Las claves de API se consideran menos seguras que OAuth y solo pueden estar disponibles durante la vista previa de la interfaz de Open SQL. Consulta Claves de API para obtener información sobre cómo crear claves de API para tu instancia de Looker.

Usa la parte del ID de cliente de la clave de la API de Looker como nombre de usuario. Usa la parte del secreto del cliente para la contraseña.

Cómo ejecutar consultas con la interfaz de Open SQL

Ten en cuenta los siguientes lineamientos cuando ejecutes consultas con la interfaz de Open SQL:

Limitaciones de SQL

Ten en cuenta las siguientes limitaciones de SQL cuando envíes consultas a la interfaz de Open SQL:

Usa acentos graves alrededor de los identificadores de la base de datos

Cuando envíes consultas a la interfaz de Open SQL, usa acentos graves alrededor de los identificadores de esquema, tabla y columna. A continuación, se muestra cómo especificar elementos de la base de datos con acentos graves y términos de Looker:

  • Esquema: `<model_name>`
  • tabla: `<explore_name>`

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

A continuación, se muestra un ejemplo del formato de la instrucción SELECT con estos elementos:

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

Especifica medidas de LookML con AGGREGATE()

Por lo general, las tablas de bases de datos solo contienen dimensiones, es decir, datos que describen un solo atributo sobre una fila de la tabla. Sin embargo, los proyectos de LookML pueden definir tanto dimensiones como mediciones. Una medición es una agregación de datos en varias filas, como SUM, AVG, MIN o MAX. (También se admiten otros tipos de medidas. Consulta la página Tipos de medidas para obtener la lista completa de los tipos de medidas de LookML admitidos).

Con la interfaz de Open SQL, debes designar cualquier medida de LookML que se incluya en una consulta. Para ello, debes encerrar la medida (incluidas las comillas inversas) en la función especial AGGREGATE(). Por ejemplo, usa esto para especificar la medida count de la vista orders:

AGGREGATE(`orders.count`)

Debes incluir las medidas de LookML en la función AGGREGATE(), ya sea que la medida se encuentre en una cláusula SELECT, una cláusula HAVING o una cláusula ORDER BY.

Si no sabes con certeza si un campo es una medida de LookML, puedes usar el método DatabaseMetaData.getColumns para acceder a los metadatos del proyecto de LookML. La columna IS_GENERATEDCOLUMN indicará YES para cualquier medida de LookML y NO para las dimensiones de LookML. Consulta la sección Cómo acceder a los metadatos de la base de datos para obtener más información.

Especifica campos y parámetros de solo filtrado con JSON_OBJECT

La interfaz de SQL abierta admite parámetros y campos de solo filtrado.

Cuando ejecutas consultas con la interfaz de Open SQL, puedes aplicar parámetros y campos solo de filtro a la consulta si incluyes una llamada al constructor JSON_OBJECT con el siguiente formato:

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

El objeto JSON puede contener cero o más pares clave-valor de filtro y cero o más pares clave-valor de parámetro.

  • La clave en el constructor JSON_OBJECT debe ser el nombre de un campo o parámetro solo de filtro.
  • En el caso de los campos de solo filtro, el valor de cada clave debe ser una expresión de filtro de cadena de Looker.
  • En el caso de los parámetros, el valor de cada clave debe ser un valor simple que se defina en la definición de parameter.

Consulta las siguientes secciones para ver ejemplos del uso de parámetros y campos de solo filtro con la interfaz de Open SQL.

Ejemplo de parámetro

Como ejemplo del uso de un parameter con la interfaz de Open SQL, si la vista customers tuviera un parámetro definido en Looker de la siguiente manera:

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"
  }
}

Podrías enviar esta consulta a la interfaz de Open SQL para aplicar el valor del parámetro segment de medium_customers a la consulta:

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;

La interfaz de SQL abierta pasará este valor de parámetro a la consulta en Looker, y Looker aplicará el valor medium_customers a cualquier campo del Explorar que esté configurado para usar el parámetro segment. Consulta la documentación de parameter para obtener información sobre cómo funcionan los parámetros en Looker.

Ejemplo de campo de solo filtrado

Puedes usar un campo filter con la interfaz de Open SQL. Por ejemplo, si una vista products tenía una dimensión y un campo solo de filtro definidos en Looker de la siguiente manera:

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 ;;
    }

Puedes usar el filtro brand_select con la interfaz de Open SQL enviando una consulta como la siguiente:

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;

La interfaz de SQL abierta aplicará la expresión de filtro de cadena de Looker %Santa Cruz% a la consulta en Looker. Consulta la documentación de filter para obtener información sobre cómo funcionan los campos de solo filtro en Looker.

Proporciona valores de always_filter o conditionally_filter en una cláusula WHERE o HAVING.

La interfaz de Open SQL puede admitir un Explore que tenga always_filter o conditionally_filter, pero no ambos.

Si definiste tu Explorar de LookML con always_filter o conditionally_filter, debes pasar valores para los campos de filtro en tu consulta en SQL a la interfaz de SQL abierta:

  • Si la definición del filtro especifica una o más dimensiones, debes incluir una cláusula WHERE en tu consulta en SQL para cada una de las dimensiones del filtro.
  • Si la definición del filtro especifica una o más medidas, debes incluir una cláusula HAVING en tu consulta en SQL para cada una de las medidas del filtro.

Por ejemplo, hay un modelo faa en el que definiste una exploración de LookML flights con un parámetro always_filter que especifica las dimensiones country y aircraft_category, y la medida count, de la siguiente manera: 

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

En tu consulta a la interfaz de Open SQL, debes usar una cláusula WHERE para pasar valores para las dimensiones de filtro y una cláusula HAVING para pasar un valor para el filtro de medición a tu modelo de LookML, como se muestra a continuación:

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 no pasas valores de filtro para cada una de las dimensiones y las métricas que se especifican en el parámetro always_filter, la consulta devolverá un error. Lo mismo sucede con las dimensiones y las medidas especificadas en un parámetro conditionally_filter, excepto que puedes definir un parámetro conditionally_filter con un subparámetro unless, de la siguiente manera:

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

En este caso, debes pasar un valor de filtro para cada una de las dimensiones y medidas que se especifican en el parámetro secundario filters de conditionally_filter, a menos que especifiques un filtro en un campo del parámetro secundario unless. (Consulta la página de documentación de conditionally_filter para obtener detalles sobre el uso del parámetro secundario unless).

Por ejemplo, cualquiera de las siguientes consultas a la interfaz de Open SQL sería aceptable. La primera búsqueda proporciona valores de filtro para los campos especificados en el subparámetro filters, y la segunda búsqueda proporciona un valor de filtro para el campo especificado en el subparámetro 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

Ejemplo

A continuación, se muestra un ejemplo de una consulta que usa tanto dimensiones como medidas. Esta consulta recupera las dimensiones estado y ciudad de la vista clientes y la métrica importe total de la vista pedidos. Ambas vistas se unen en el Explorar pedidos del modelo comercio electrónico. En el caso de las ciudades que tienen más de 10 pedidos, la respuesta de esta búsqueda muestra las 5 ciudades principales por importe de pedido:

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;

Cómo acceder a los metadatos de la base de datos

La interfaz de Open SQL admite un subconjunto de la interfaz DatabaseMetaData estándar de JDBC, que se usa para obtener información sobre la base de datos subyacente. Puedes usar los siguientes métodos de la interfaz de DatabaseMetaData para obtener información sobre tu modelo de LookML:

La interfaz de SQL abierta solo devuelve resultados para los modelos, las Exploraciones y los campos a los que tienes acceso.

DatabaseMetadata.getSchemas

En la siguiente tabla, se describe cómo se relaciona un modelo de LookML con las estructuras de bases de datos estándar en la respuesta del método de interfaz DatabaseMetadata.getSchemas.

Columna de respuesta getSchemas Descripción
TABLE_SCHEM Nombre del modelo de LookML
TABLE_CATALOG (nulo)

DatabaseMetadata.getTables

En la siguiente tabla, se describe cómo se relaciona un modelo de LookML con las estructuras de la base de datos en la respuesta del método de la interfaz DatabaseMetaData.getTables. La respuesta incluye metadatos JDBC estándar y metadatos específicos de Looker:

Columna de respuesta getTables Descripción
Metadatos estándar de JDBC
TABLE_CAT (nulo)
TABLE_SCHEM Nombre del modelo de LookML
TABLE_NAME Nombre de la exploración de LookML
TABLE_TYPE Siempre devuelve el valor TABLE_TYPE.
REMARKS (nulo)
TYPE_CAT (nulo)
TYPE_SCHEM (nulo)
TYPE_NAME Es una cadena que representa el tipo de tabla. Los tipos posibles son TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS y SYNONYM.
SELF_REFERENCING_COL_NAME (nulo)
REF_GENERATION (nulo)
Metadatos específicos de Looker
DESCRIPTION Explorar la descripción
LABEL Explorar etiqueta
TAGS Explorar etiquetas
CONDITIONALLY_FILTER_UNLESS Es la lista de campos en el subparámetro unless del parámetro conditionally_filter de Explorar. Si no se especifican campos en el subparámetro unless o si no se define ningún parámetro conditionally_filter para la Exploración, este valor es nulo.

DatabaseMetadata.getColumns

En la siguiente tabla, se describe cómo se relaciona un modelo de LookML con las estructuras de la base de datos en la respuesta del método de la interfaz DatabaseMetaData.getColumns. La respuesta incluye metadatos JDBC estándar y metadatos específicos de Looker:

Columna de respuesta getColumns Descripción
Metadatos estándar de JDBC
TABLE_CAT (nulo)
TABLE_SCHEM Nombre del modelo de LookML
TABLE_NAME Nombre de la exploración de LookML
COLUMN_NAME Nombre del campo de LookML en formato `<view_name>.<field_name>`. Por ejemplo, `orders.amount`
DATA_TYPE Es el código java.sql.Types de la columna. Por ejemplo, los campos yesno de Looker son el código de tipo SQL 16 (BOOLEAN).
TYPE_NAME Es una cadena que representa el tipo de datos de la columna. En el caso de un tipo definido por el usuario (UDT), el nombre del tipo está completamente calificado.
COLUMN_SIZE Es un número entero que representa la cantidad máxima de caracteres o bytes que se pueden almacenar en la columna.
BUFFER_LENGTH (nulo)
DECIMAL_DIGITS Es un número entero que representa la escala de los datos: la cantidad de dígitos a la derecha del decimal, para los tipos de datos aplicables, o la cantidad de dígitos fraccionarios. Se devuelve nulo para los tipos de datos en los que DECIMAL_DIGITS no es aplicable.
NUM_PREC_RADIX Es un número entero que representa la base o la raíz (por lo general, 10 o 2) de los datos.
NULLABLE

Es un número entero que indica si se permiten valores nulos:

  • 0: columnNoNulls (es posible que no permita valores NULL)
  • 1: columnNullable: Definitivamente permite valores NULL.
  • 2: columnNullableUnknown: Se desconoce la nulabilidad
REMARKS (nulo)
COLUMN_DEF (nulo)
SQL_DATA_TYPE (nulo)
SQL_DATETIME_SUB (nulo)
CHAR_OCTET_LENGTH Para los tipos de datos de caracteres, es un número entero que representa la cantidad máxima de bytes en la columna.
ORDINAL_POSITION Ordinal basado en 1 del campo dentro de Explorar (las dimensiones y las métricas se mezclan alfabéticamente por nombre de la vista y, luego, por nombre del campo)
IS_NULLABLE Siempre devuelve el valor YES.
SCOPE_CATALOG (nulo)
SCOPE_SCHEMA (nulo)
SCOPE_TABLE (nulo)
SOURCE_DATA_TYPE (nulo)
IS_AUTOINCREMENT (nulo)
IS_GENERATEDCOLUMN YES para las métricas y NO para las dimensiones
Metadatos específicos de Looker
DIMENSION_GROUP Nombre del grupo de dimensiones si el campo forma parte de un grupo de dimensiones. Si el campo no forma parte de un grupo de dimensiones, este valor es nulo.
DRILL_FIELDS Lista de campos de desglose establecidos para la dimensión o la métrica, si corresponde
FIELD_ALIAS Alias del campo, si corresponde
FIELD_CATEGORY Indica si el campo es dimension o measure.
FIELD_DESCRIPTION Campo description
FIELD_GROUP_VARIANT Si el campo se presenta bajo una etiqueta de grupo, el FIELD_GROUP_VARIANT especificará el nombre más corto del campo que se muestra bajo la etiqueta del grupo.
FIELD_LABEL Etiqueta del campo label
FIELD_NAME Nombre de la dimensión o la métrica
LOOKER_TYPE Tipo de campo de LookML para la dimensión o la medida
REQUIRES_REFRESH_ON_SORT Indica si se debe actualizar la consulta en SQL para volver a ordenar los valores del campo (TRUE) o si los valores del campo se pueden volver a ordenar sin necesidad de actualizar la consulta en SQL (FALSE).
SORTABLE Indica si el campo se puede ordenar (TRUE) o no (FALSE).
TAGS Campo tags
USE_STRICT_VALUE_FORMAT Indica si el campo usa un formato de valor estricto (TRUE) o no (FALSE).
VALUE_FORMAT Cadena de formato de valor para el campo
VIEW_LABEL Etiqueta de visualización del campo
VIEW_NAME Nombre de la vista en la que se define el campo en el proyecto de LookML
HIDDEN Indica si el campo está oculto en el selector de campos de las Exploraciones (TRUE) o si el campo está visible en el selector de campos de las Exploraciones (FALSE).
ALWAYS_FILTER Es el valor predeterminado del parámetro always_filter que se establece en el campo. Si el campo no forma parte de un parámetro always_filter, este valor es nulo.
CONDITIONALLY_FILTER Es el valor predeterminado del parámetro conditionally_filter que se establece en el campo. Si el campo no forma parte de un parámetro conditionally_filter, este valor es nulo.

Cómo identificar consultas de la interfaz de SQL abierta en la IU de Looker

Los administradores de Looker pueden usar la IU de Looker para identificar qué consultas se originaron en la interfaz de Open SQL:

  • En la página de administrador Queries, las consultas de la interfaz de SQL abierta tienen el valor "Interfaz de SQL" en Source. El valor de Usuario mostrará el nombre del usuario de Looker que ejecutó la consulta. Puedes hacer clic en el botón Detalles de una consulta para obtener información adicional sobre ella. En el diálogo Detalles, puedes hacer clic en Consulta de la interfaz de SQL para ver la consulta en SQL que se envió a Looker desde la interfaz de SQL abierta.

  • En el Explorar historial de actividad del sistema, las consultas de la interfaz de SQL abierta tienen el valor Fuente de "sql_interface". El valor de User Email mostrará la dirección de correo electrónico del usuario de Looker que ejecutó la consulta. Puedes ir directamente al Explorar Historial filtrado en "sql_interface" insertando la dirección de tu instancia de Looker al principio de esta URL:

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

Repositorio para dependencias de terceros

En el siguiente vínculo, se proporciona acceso al repositorio alojado en Google para las dependencias de terceros que usa el controlador JDBC de Looker:

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