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 de Git. Un modelo de LookML bien estructurado permite a los usuarios realizar su propia exploración y generación de informes de datos de autoservicio.

El modelo de LookML es la base de cualquier dato que se solicite a Looker, ya sea que esa solicitud provenga de la interfaz de Looker Explore en la IU de Looker, una visualización incorporada en el portal de tu empresa o en otra aplicación de terceros, o una aplicación personalizada que se desarrolló con la API de Looker. La interfaz de SQL abierta 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 que les resulten más cómodas.

Cómo la interfaz de SQL abierta 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 comprender cómo están estructurados 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 SQL en Looker (consulta los términos y conceptos de LookML para obtener más información). Los siguientes conceptos de proyectos 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 muestra los modelos como esquemas de base 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 Explorar como tablas de base de datos.
  • Una vista define una colección de campos (dimensiones y 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 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 base de datos. Por ejemplo, la dimensión id en la vista order_items se muestra en la interfaz de Open SQL como una columna de base de datos llamada order_items.id.

Una exploración de Looker puede definir relaciones de unión entre varias vistas. Como es posible que una vista tenga un campo con el mismo nombre que un campo en una vista diferente, la interfaz de Open SQL incluye el nombre de la vista y el nombre del campo cuando 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 una exploración llamada 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 completamente calificado con el nombre de Explorar, debes hacer referencia a los dos campos como `order_items`.`customer.id` y `order_items`.`product.id`. (Consulta Cómo usar acentos graves alrededor de los identificadores de base de datos para obtener información sobre dónde colocar los acentos graves cuando te refieras a los identificadores de base 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 de JDBC de la interfaz de SQL abierta.

En las siguientes secciones, se describen estos pasos.

Requisitos

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

Descarga el controlador de JDBC de la interfaz de SQL abierta

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 de JDBC es:

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

Cómo autenticarse en la interfaz de Open SQL

La interfaz 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 los pasos para configurar la autenticación de OAuth:

  1. Usa la extensión del Explorador de API para registrar el cliente de OAuth de JDBC con 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 los usuarios con OAuth para ver un ejemplo.
  3. Usa un objeto Properties para pasar las credenciales de OAuth cuando abras la conexión de JDBC a la interfaz Open SQL.

A continuación, se muestra un ejemplo con 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);

Genera un token de acceso con claves de API

En lugar de usar el flujo de OAuth estándar 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 de 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>. El siguiente es 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 abras la conexión JDBC a la interfaz Open SQL.

Claves de API

También puedes usar claves de API para autenticar en lugar de un nombre de usuario y una contraseña. Las claves de API se consideran menos seguras que OAuth y es posible que solo estén disponibles durante la vista previa de la interfaz de SQL abierta. 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 API de Looker como nombre de usuario. Usa la parte Secreto del cliente para la contraseña.

Ejecuta consultas con la interfaz de SQL abierta

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

Limitaciones de LookML

Ten en cuenta lo siguiente cuando envíes consultas a la interfaz de SQL abierta:

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 base de datos con acentos graves con términos de Looker:

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

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

Este es un ejemplo de formato de sentencia SELECT que usa 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, datos que describen un solo atributo sobre una fila de la tabla. Sin embargo, los proyectos de LookML pueden definir dimensiones y mediciones. Una medida 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 compatibles).

Con la interfaz de Open SQL, debes designar las medidas de LookML que se incluyen en una consulta uniendo la medida (incluidos los acentos graves) en la función especial AGGREGATE(). Por ejemplo, úsalo para especificar la medida count de la vista orders:

AGGREGATE(`orders.count`)

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

Si no estás seguro de 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 medición 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.

Ejemplo

A continuación, se muestra un ejemplo de consulta que usa dimensiones y métricas. Esta consulta recupera las dimensiones estado y ciudad de la vista clientes y la medida importe total de la vista pedidos. Ambas vistas se unen en la exploración de pedidos en el modelo de comercio electrónico. En el caso de las ciudades que tienen más de 10 pedidos, esta respuesta de la consulta muestra las 5 ciudades principales por importe del 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;

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 Open SQL, puedes aplicar parámetros y campos de solo 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ámetros.

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

Puedes 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 abierta de SQL pasará este valor de parámetro a la consulta en Looker, y Looker aplicará el valor medium_customers a cualquier campo de 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 Open SQL. Por ejemplo, si una vista products tuviera una dimensión y un campo solo de filtro definido 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 ;;
    }

Para usar el filtro brand_select con la interfaz de Open SQL, envía 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 abierta de SQL 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.

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 DatabaseMetaData para obtener información sobre tu modelo de LookML:

DatabaseMetadata.getSchemas

En la siguiente tabla, se describe cómo se relaciona un modelo de LookML con las estructuras de base 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 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 función Explorar de LookML
TABLE_TYPE Siempre muestra el valor TABLE_TYPE
Metadatos específicos de Looker
DESCRIPTION Descripción de Explorar
LABEL Explora la etiqueta
TAGS Explora las etiquetas

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 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 Es el 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).
ORDINAL_POSITION El ordinal basado en 1 del campo dentro de Explorar (combina dimensiones y medidas alfabéticamente por nombre de vista y, luego, por nombre de campo)
IS_NULLABLE Siempre muestra el valor YES
IS_GENERATEDCOLUMN YES para las mediciones y NO para las dimensiones
Metadatos específicos de Looker
DIMENSION_GROUP Es el nombre del grupo de dimensiones si el campo forma parte de uno. Si el campo no forma parte de un grupo de dimensiones, este será nulo.
DRILL_FIELDS Es la lista de campos de desglose establecidos para la dimensión o la métrica, si los hay.
FIELD_ALIAS Alias del campo, si corresponde
FIELD_CATEGORY Si el campo es dimension o measure
FIELD_DESCRIPTION Descripción del campo
FIELD_GROUP_VARIANT Si el campo se presenta en una etiqueta de grupo, FIELD_GROUP_VARIANT especificará el nombre más corto del campo que se muestra en la etiqueta de grupo.
FIELD_LABEL Etiqueta del campo
FIELD_NAME Nombre de la dimensión o medición
HIDDEN Indica si el campo está oculto en el selector de campos de Exploraciones (TRUE) o si es visible en el selector de campos de Exploraciones (FALSE).
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 Etiquetas de campo
USE_STRICT_VALUE_FORMAT Indica si el campo usa formato de valor estricto (TRUE) o no (FALSE).
VALUE_FORMAT Cadena de formato de valor para el campo
VIEW_LABEL Etiqueta de vista del campo
VIEW_NAME Es el nombre de la vista en la que se define el campo en el proyecto de LookML.

Cómo identificar consultas de la interfaz abierta de SQL 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 SQL abierta:

  • En la página de administración Consultas, las consultas de la interfaz de SQL abierta tienen un valor de Fuente de "Interfaz de SQL". El valor User 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 la Exploración del historial de actividad del sistema, las consultas de la interfaz de SQL abierta tienen un valor de 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. Para ir directamente a la exploración Historial filtrada en "sql_interface", inserta la dirección de tu instancia de Looker al comienzo 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

El siguiente vínculo 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/