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 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 los analistas de datos en el modelo de LookML, mientras usan las herramientas con las que se sientan más cómodos.

Cómo muestra la interfaz abierta de SQL los elementos de 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 en SQL en Looker (consulta Términos y conceptos de LookML para obtener más información). Los siguientes conceptos del proyecto de LookML están relacionados con la interfaz abierta de SQL:

  • Un model 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 esas vistas. 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 nombre de columna de base de datos. Por ejemplo, la interfaz abierta de 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 abierta de 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 al nombre de una columna cuando envíes consultas a la interfaz 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 tienen 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 Usa acentos graves en torno a los identificadores de bases de datos para obtener información sobre dónde colocarlos cuando se hace referencia a los identificadores de bases de datos).

Configura la interfaz abierta de SQL

Para utilizar la interfaz Open SQL, realiza los siguientes pasos:

  1. Verifica que se cumplan los requisitos.
  2. Habilita Open SQL Interface en tu instancia de Looker.
  3. 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:

Habilita Open SQL Interface en tu instancia de Looker

Para habilitar la interfaz Open SQL en tu instancia, realiza los siguientes pasos:

Descarga el controlador JDBC de la interfaz de Open SQL

El controlador JDBC de la interfaz Open SQL 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://your Looker instance URL

Por ejemplo:

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

La clase de controlador de JDBC es la siguiente:

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

Autenticación en la interfaz abierta de SQL

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

OAuth

Los clientes de JDBC compatibles con OAuth se pueden configurar para que usen 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 Registra 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 usuario 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.

El siguiente es 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);

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 JDBC de la interfaz abierta de SQL:

  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>. Este es un comando curl de ejemplo 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 de JDBC a la interfaz 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 es posible que solo estén disponibles durante la vista previa de la interfaz abierta de SQL. Consulta Claves de API si necesitas información para crear claves de API para tu instancia de Looker.

Usa la parte de ID de cliente de la clave de API de Looker como nombre de usuario. Usa la parte de Secreto del cliente (Client Secret) para la contraseña.

Ejecuta consultas con la interfaz abierta de SQL

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

Limitaciones de LookML

Ten en cuenta las siguientes limitaciones de LookML cuando envíes consultas a la interfaz abierta de 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 en torno a los identificadores de la base de datos.

Cuando envíes consultas a la interfaz Open SQL, usa acentos graves en torno a 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>`

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

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

Especifica las medidas de LookML con AGGREGATE()

Por lo general, las tablas de bases de datos solo contienen dimensiones, es decir, datos que describen un único atributo de 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 cualquier medida de LookML que se incluya en una consulta uniendo la medida (incluidos los acentos graves) a 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

Esta es una consulta de ejemplo que usa dimensiones y mediciones. Esta consulta recupera las dimensiones state y city de la vista customers, y la medida total amount de la vista orders. Ambas vistas están unidas a la función Explorar de pedidos en el modelo ecommerce. Para las ciudades que tienen más de 10 pedidos, esta respuesta de la consulta muestra las 5 ciudades principales por cantidad de pedidos:

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 de solo filtro.
  • Para 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

A modo de ejemplo para usar un parameter con la interfaz abierta de SQL, si la vista customers tenía 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 de SQL abierta pasará este valor del parámetro a la consulta en Looker, y Looker aplicará el valor medium_customers a todos los campos de la exploración que estén configurados 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 tenía una dimensión y un campo de solo 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 abierta de SQL si envías 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 de DatabaseMetaData de JDBC estándar, 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.

getSchemas columna de respuesta 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 las bases 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 exploración 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 las etiquetas

DatabaseMetadata.getColumns

En la siguiente tabla, se describe cómo se relaciona un modelo de LookML con las estructuras de las bases de datos en la respuesta del método de interfaz DatabaseMetaData.getColumns. La respuesta incluye metadatos de 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 de 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 dimensión si el campo forma parte de un grupo de dimensiones. Si el campo no forma parte de un grupo de dimensiones, el valor será nulo.
DRILL_FIELDS Lista de campos de desglose configurados para la dimensión o la medida, si corresponde.
FIELD_ALIAS Alias para el campo, si corresponde
FIELD_CATEGORY Si el campo es dimension o measure
FIELD_DESCRIPTION description del campo
FIELD_GROUP_VARIANT Si el campo se presenta bajo un campo group label, FIELD_GROUP_VARIANT especificará el nombre más corto del campo que se muestra debajo de la etiqueta de grupo.
FIELD_LABEL Etiqueta del campo
FIELD_NAME Nombre de la dimensión o medida
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 medición
REQUIRES_REFRESH_ON_SORT Indica si la consulta en SQL debe actualizarse para volver a ordenar los valores del campo (TRUE) o si los valores del campo se pueden volver a ordenar sin requerir una actualización de la consulta en 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 Es la cadena de formato del valor para el campo.
VIEW_LABEL Ver etiqueta para el 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 abierta de SQL:

  • En la página del administrador de Consultas, las consultas de la interfaz abierta de SQL tienen un valor de Fuente de "Interfaz de SQL". El valor Usuario mostrará el nombre del usuario de Looker que ejecutó la consulta.

  • 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. Para ir directamente a la exploración del historial, filtrada en “sql_interface”, inserta la dirección de tu instancia de Looker al comienzo de esta URL:

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

Comentarios sobre la interfaz de Open SQL

Escribe a looker-sql-interface@google.com si tienes alguna pregunta o deseas solicitar funciones para Open SQL Interface.