Interfaz de SQL abierta

La capa de modelado semántico LookML de Looker permite a un analista de datos definir dimensiones, agregados, cálculos y relaciones de datos en una base de datos de SQL. Los modelos de LookML proporcionan integración de Git y reutilización de código. Un modelo de LookML bien estructurado empodera a los usuarios para que realicen sus propios informes y exploración de datos.

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 Explorar de Looker en la IU de Looker, una visualización incorporada en el portal de tu empresa o alguna otra aplicación de terceros, o una aplicación personalizada desarrollada 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 entender cómo la interfaz abierta de SQL muestra los elementos de un proyecto de LookML, es importante comprender 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 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 SQL abierta:

  • Un modelo de LookML especifica una conexión de base de datos y una o más exploraciones. La interfaz abierta de 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 las exploraciones como tablas de bases de datos.
  • Una vista define una colección de campos (tanto dimensiones como medidas). 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 las dimensiones o medidas personalizadas 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 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.

Una exploración de Looker puede definir relaciones de unión entre varias vistas. Debido a que 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>`

A modo de 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 también el nombre completamente calificado con el nombre de Explorar, consulta 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).

Configura la interfaz abierta de SQL

Para usar la interfaz Open SQL, sigue estos pasos:

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

En las siguientes secciones, se describen estos pasos.

Requisitos

Los siguientes componentes son necesarios para usar la interfaz abierta de SQL:

Descarga el controlador de JDBC de la interfaz de SQL abierta

El controlador de JDBC de la interfaz de SQL abierta de Looker se llama avatica-<release_number>-looker.jar. Descarga la versión más reciente desde 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

Autenticación en la interfaz abierta de 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 APIs para registrar el cliente de OAuth de JDBC con tu instancia de Looker, de modo que la instancia de Looker 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 Open SQL Interface.

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 hacer 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 abierta de 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 API de Looker como nombre de usuario. Usa la parte Secreto del cliente para la contraseña.

Ejecuta consultas con la interfaz Open SQL

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 en torno a 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 usando 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 mediciones. Consulta la página Tipos de mediciones para ver la lista completa de tipos de mediciones compatibles de LookML).

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, usa esto para especificar la medida count desde la vista orders:

AGGREGATE(`orders.count`)

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

Si no tienes certeza 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 medida de LookML y NO para dimensiones de LookML. Consulta la sección Accede 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 de solo filtro y parámetros con JSON_OBJECT

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

Cuando ejecutas consultas con la interfaz de Open SQL, puedes aplicar parámetros y campos de solo filtro a la consulta si incluyes una llamada de 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.
  • Para los parámetros, el valor de cada clave debe ser un valor sin formato que se define en la definición de parameter.

Consulta las siguientes secciones para ver ejemplos de uso de parámetros y campos de solo filtro con la interfaz abierta de 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 del 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 filtro

Puedes usar un campo filter con la interfaz abierta de SQL. Por ejemplo, si una vista products tuviera 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 ;;
    }

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 de SQL abierta aplicará la expresión de filtro de cadenas 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.

Accede 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 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 interfaz DatabaseMetaData.getTables. La respuesta incluye metadatos de JDBC estándar y metadatos específicos de Looker:

getTables columna de respuesta 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 Explorar la descripción
LABEL Explorar la etiqueta
TAGS Explorar 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:

getColumns columna de respuesta 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 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 (que combina dimensiones y medidas alfabéticamente por nombre de vista y, luego, nombre del 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 Lista de campos de desglose configurados para la dimensión o la medida, si corresponde.
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 Si el campo está oculto en el selector de campos de Exploraciones (TRUE) o si el campo está 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 de 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 de SQL (FALSE).
SORTABLE Si el campo se puede ordenar (TRUE) o no (FALSE)
TAGS Etiquetas de campo
USE_STRICT_VALUE_FORMAT Si el campo utiliza 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 Nombre de la vista en la que se define el campo en el proyecto de LookML

Identificar las 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 SQL abierta:

  • En la página del administrador Consultas, las consultas de la interfaz abierta de SQL 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 abierta de SQL.

  • En la Exploración del historial de actividad del sistema, las consultas de la interfaz abierta de SQL tienen un valor de Fuente de “sql_interface”. El valor Correo electrónico del usuario mostrará la dirección de correo electrónico del usuario de Looker que ejecutó la consulta. Puedes ir directamente a la página Explorar del historial con un filtro en “sql_interface”. Para ello, 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]=sql_interface

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

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

Comentarios sobre la interfaz de Open SQL

Si tienes preguntas o solicitudes de funciones para la interfaz de SQL abierta, escribe a looker-sql-interface@google.com.