Abrir interfaz SQL

La capa de modelado semántico LookML de Looker permite a los analistas de datos definir dimensiones, agregaciones, cálculos y relaciones de datos en una base de datos SQL. Los modelos de LookML ofrecen reutilización de código e integración con Git. Un modelo de LookML bien estructurado permite a los usuarios explorar los datos y generar informes por su cuenta.

El modelo LookML es la base de todos los datos que se solicitan a Looker, ya sea a través de la interfaz Explorar de Looker en la interfaz de usuario de Looker, de una visualización insertada en el portal de tu empresa o en otra aplicación de terceros, o bien de una aplicación personalizada desarrollada con la API de Looker. La interfaz Open SQL proporciona acceso a los modelos de LookML a cualquier aplicación de terceros que admita Java Database Connectivity (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 han realizado sus analistas de datos en el modelo de LookML y, al mismo tiempo, usar las herramientas con las que se sientan más cómodos.

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

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

Un proyecto de LookML es un conjunto de archivos que describen los objetos, las conexiones de bases de datos y los elementos de la interfaz de usuario que se utilizan para llevar a cabo 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 están relacionados con la interfaz Open SQL:

  • Un modelo de LookML especifica una conexión de base de datos y una o varias Exploraciones. La interfaz Open SQL muestra los modelos como esquemas de bases de datos.
  • Una Exploración es una agrupación lógica de una o varias vistas y las relaciones de unión entre ellas. La interfaz Open SQL muestra los Exploraciones como tablas de bases de datos.
  • Una vista define un conjunto de campos (dimensiones y medidas). Una vista se basa generalmente en una tabla de su 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 métricas personalizadas que necesiten los usuarios finales. La interfaz 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 de la vista order_items se muestra en Open SQL Interface 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 de otra vista, la interfaz Open SQL incluye tanto el nombre de la vista como el del campo al hacer referencia a una columna. Por lo tanto, usa este formato para hacer referencia a un nombre de columna al enviar consultas a la interfaz Open SQL:

`<view_name>.<field_name>`

Por ejemplo, si hay una Exploración llamada order_items que combina una vista llamada customer con otra llamada product y ambas vistas tienen una dimensión id, los dos campos id se denominarán `customer.id` y `product.id`, respectivamente. Para usar el nombre completo con el nombre de la exploración, harías referencia a los dos campos como `order_items`.`customer.id` y `order_items`.`product.id`. Consulta Usar comillas inversas alrededor de los identificadores de bases de datos para obtener información sobre dónde colocar las comillas inversas al hacer referencia a identificadores de bases de datos.

Configurar la interfaz Open SQL

Para usar la interfaz Open SQL, sigue estos pasos:

  1. Comprueba que se cumplen los requisitos.
  2. Descarga el archivo del controlador JDBC de la interfaz Open SQL.

En las siguientes secciones se describen estos pasos.

Requisitos

Para usar la interfaz Open SQL, se necesitan los siguientes componentes:

Descargar el controlador JDBC de la interfaz Open SQL

El controlador JDBC de la interfaz 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:

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

Autenticación en la interfaz Open SQL

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

OAuth

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

  1. Usa la extensión Explorador de APIs para registrar el cliente de OAuth de JDBC en tu instancia de Looker, de modo que la instancia pueda reconocer las solicitudes de OAuth. Para obtener instrucciones, consulta Registrar una aplicación cliente de OAuth.
  2. Inicia sesión en Looker con OAuth para solicitar un token de acceso. Consulta un ejemplo en Iniciar sesión de usuario con OAuth.
  3. Usa un objeto Properties para transferir las credenciales de OAuth al abrir la conexión JDBC a la interfaz Open SQL.

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

Generar 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 transferir al controlador JDBC de la interfaz SQL abierta:

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

  2. Usa el login endpoint de la API de tu instancia de Looker. La respuesta incluye un token de acceso con el formato Authorization: token <access_token>. A continuación, se muestra 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. Transfiere el valor de <access_token> de la respuesta como token en el objeto Properties para transferir las credenciales de OAuth al abrir la conexión 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 solo pueden estar disponibles durante la vista previa de la interfaz 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 ID de cliente de la clave de API de Looker como nombre de usuario. Usa la parte Secreto de cliente como contraseña.

Ejecutar consultas con la interfaz Open SQL

Ten en cuenta las siguientes directrices al ejecutar consultas con la interfaz Open SQL:

Limitaciones de SQL

Ten en cuenta las siguientes limitaciones de SQL al enviar consultas a la interfaz Open SQL:

Usar comillas inversas alrededor de los identificadores de bases de datos

Cuando envíes consultas a la interfaz Open SQL, usa comillas inversas alrededor de los identificadores de esquemas, tablas y columnas. A continuación, se explica cómo especificar elementos de la base de datos usando comillas inversas con 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;

Especificar medidas de LookML con AGGREGATE()

Las tablas de bases de datos suelen contener solo dimensiones, es decir, datos que describen un solo atributo de una fila de la tabla. Sin embargo, los proyectos de LookML pueden definir tanto dimensiones como medidas. Una métrica es una agregación de datos de varias filas, como SUM, AVG, MIN o MAX. También se admiten otros tipos de medidas. Consulta la lista completa de tipos de medidas de LookML admitidos en la página Tipos de medidas.

Con la interfaz Open SQL, debes designar las medidas de LookML que se incluyan en una consulta envolviendo la medida (incluidas las comillas inversas) en la función especial AGGREGATE(). Por ejemplo, puedes usarlo para especificar la medida count de la vista orders:

AGGREGATE(`orders.count`)

Debes envolver las medidas de LookML en la función AGGREGATE(), tanto si la medida está en una cláusula SELECT, HAVING o 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 en las medidas de LookML y NO en las dimensiones de LookML. Para obtener más información, consulta la sección Acceder a los metadatos de la base de datos.

Especificar campos y parámetros solo para filtros con JSON_OBJECT

Open SQL Interface admite parámetros y campos solo para filtros.

Cuando ejecutes consultas con la interfaz Open SQL, puedes aplicar parámetros y campos solo para filtros a la consulta incluyendo 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 del constructor JSON_OBJECT debe ser el nombre de un campo o parámetro solo para filtros.
  • En el caso de los campos solo para filtros, 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 definido en la definición de parameter.

En las siguientes secciones se muestran ejemplos de cómo usar parámetros y campos solo para filtros con la interfaz SQL abierta.

Ejemplo de parámetro

Por ejemplo, si la vista customers tuviera un parámetro definido en Looker de la siguiente manera:parameter

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 Open SQL para aplicar el valor del parámetro segment, que es 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;

Open SQL Interface transferirá este valor de parámetro a la consulta en Looker, y Looker aplicará el valor medium_customers a los campos de 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 solo para filtros

Puede usar un campo filter con la interfaz Open SQL. Por ejemplo, si una productsvista tiene una dimensión y un campo solo para filtros 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 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;

Abrir interfaz 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 solo para filtros en Looker.

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

La interfaz Open SQL puede admitir Exploraciones que tengan always_filter o conditionally_filter, pero no ambos.

Si has definido tu exploración de LookML con always_filter o conditionally_filter, debes pasar valores para los campos de filtro en tu consulta SQL a la interfaz de SQL abierto:

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

Por ejemplo, supongamos que tienes un faa modelo en el que has definido una Exploración de LookML flights con un parámetro always_filter que especifica las dimensiones country y aircraft_category, así como 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 enviar valores de las dimensiones de filtro y una cláusula HAVING para enviar un valor del filtro de medida a tu modelo de LookML, como en el siguiente ejemplo:

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 envía valores de filtro para cada una de las dimensiones y medidas especificadas en el parámetro always_filter, la consulta devolverá un error. Lo mismo ocurre con las dimensiones y las medidas especificadas en un parámetro conditionally_filter, con la diferencia de que puede definir un parámetro conditionally_filter con un subparámetro unless, como se muestra a continuación:

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

En este caso, debe proporcionar un valor de filtro para cada una de las dimensiones y métricas especificadas en el subparámetro filters de conditionally_filter, a menos que especifique un filtro en un campo del subparámetro unless. Para obtener más información sobre el uso del subparámetro unless, consulta la página de documentación de conditionally_filter.

Por ejemplo, se aceptaría cualquiera de las siguientes consultas a la interfaz Open SQL. La primera consulta proporciona valores de filtro para los campos especificados en el subparámetro filters, y la segunda consulta 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 una consulta de ejemplo que usa dimensiones y medidas. Esta consulta obtiene las dimensiones state y city de la vista customers y la métrica total amount de la vista orders. Ambas vistas se combinan en la exploración pedidos del modelo comercio electrónico. En el caso de las ciudades que tienen más de 10 pedidos, esta respuesta de consulta muestra las 5 ciudades con mayor 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;

Acceder a los metadatos de la base de datos

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

La interfaz SQL abierta solo devuelve resultados de 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 base de datos estándar en la respuesta del método de interfaz DatabaseMetadata.getSchemas.

Columna de respuesta de 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, así como metadatos específicos de Looker:

Columna de respuesta de getTables Descripción
Metadatos estándar de JDBC
TABLE_CAT (nulo)
TABLE_SCHEM Nombre del modelo de LookML
TABLE_NAME Nombre de la instancia de Explore de LookML
TABLE_TYPE Siempre devuelve el valor TABLE_TYPE.
REMARKS (nulo)
TYPE_CAT (nulo)
TYPE_SCHEM (nulo)
TYPE_NAME 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 Consulta la descripción.
LABEL Explorar etiquetas
TAGS Consultar etiquetas
CONDITIONALLY_FILTER_UNLESS Lista de campos del subparámetro unless del parámetro conditionally_filter de Explorar. Si no se especifica ningún campo en el subparámetro unless o no se define ningún parámetro conditionally_filter en el Explorar, este valor será 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 interfaz DatabaseMetaData.getColumns. La respuesta incluye metadatos JDBC estándar, así como metadatos específicos de Looker:

Columna de respuesta de getColumns Descripción
Metadatos estándar de JDBC
TABLE_CAT (nulo)
TABLE_SCHEM Nombre del modelo de LookML
TABLE_NAME Nombre de la instancia de Explore 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 de Looker yesno son de tipo de código SQL 16 (BOOLEAN).
TYPE_NAME Cadena que representa el tipo de datos de la columna. En el caso de un tipo definido por el usuario (TDU), el nombre del tipo está completo.
COLUMN_SIZE Número entero que representa el número máximo de caracteres o bytes que se pueden almacenar en la columna.
BUFFER_LENGTH (nulo)
DECIMAL_DIGITS Número entero que representa la escala de los datos: el número de dígitos a la derecha del decimal, en el caso de los tipos de datos aplicables, o el número de dígitos fraccionarios. Se devuelve un valor nulo para los tipos de datos en los que DECIMAL_DIGITS no es aplicable.
NUM_PREC_RADIX Número entero que representa la base o la raíz (normalmente 10 o 2) de los datos.
NULLABLE

Número entero que indica si se permiten valores nulos:

  • 0: columnNoNulls puede que no permita valores NULL
  • 1: columnNullable: permite valores NULL
  • 2: columnNullableUnknown - no se conoce la anulabilidad
REMARKS (nulo)
COLUMN_DEF (nulo)
SQL_DATA_TYPE (nulo)
SQL_DATETIME_SUB (nulo)
CHAR_OCTET_LENGTH En el caso de los tipos de datos de caracteres, un número entero que representa el número máximo de bytes de la columna.
ORDINAL_POSITION Número ordinal del campo en la Exploración (se mezclan las dimensiones y las medidas alfabéticamente por nombre de vista y, después, por nombre de 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 medidas 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 definidos para la dimensión o la medida (si los hay)
FIELD_ALIAS Alias del campo (si lo tiene)
FIELD_CATEGORY Si el campo es dimension o measure
FIELD_DESCRIPTION Descripción del campo
FIELD_GROUP_VARIANT Si el campo se presenta en un grupo de campos, el FIELD_GROUP_VARIANT especificará el nombre más corto del campo que se muestra en el grupo de campos.
FIELD_LABEL Campo label
FIELD_NAME Nombre de la dimensión o la medida
LOOKER_TYPE Tipo de campo de LookML de la dimensión o la medida
REQUIRES_REFRESH_ON_SORT Indica si la consulta de SQL se debe actualizar 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 Indica si el campo se puede ordenar (TRUE) o no (FALSE).
TAGS Campo tags
USE_STRICT_VALUE_FORMAT Indica si el campo usa el formato de valor estricto (TRUE) o no (FALSE).
VALUE_FORMAT Cadena Value format del campo
VIEW_LABEL Ver etiqueta 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 Exploraciones (TRUE) o si está visible en el selector de campos de Exploraciones (FALSE).
ALWAYS_FILTER El valor predeterminado del parámetro always_filter que se ha definido en el campo. Si el campo no forma parte de un parámetro always_filter, este valor es nulo.
CONDITIONALLY_FILTER El valor predeterminado del parámetro conditionally_filter que se ha definido en el campo. Si el campo no forma parte de un parámetro conditionally_filter, este valor es nulo.

Identificar consultas de interfaz Open SQL en la interfaz de usuario de Looker

Los administradores de Looker pueden usar la interfaz de usuario de Looker para identificar qué consultas proceden de la interfaz SQL abierta:

  • En la página de administración Consultas, las consultas de la interfaz de Open SQL tienen el valor "Interfaz SQL" en el campo Fuente. El valor Usuario mostrará el nombre del usuario de Looker que ha ejecutado la consulta. Puede hacer clic en el botón Detalles de una consulta para obtener información adicional sobre ella. En el cuadro de diálogo Detalles, puedes hacer clic en Consulta de interfaz SQL para ver la consulta de SQL que se envió a Looker desde la interfaz SQL abierta.

  • En el Explorador del historial de actividad del sistema, las consultas de la interfaz SQL abierta tienen el valor "sql_interface" en el campo Fuente. El valor Correo electrónico del usuario mostrará la dirección de correo del usuario de Looker que ha ejecutado la consulta. Puedes ir directamente al Historial Explorar filtrado por "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 de dependencias de terceros

El siguiente enlace proporciona acceso al repositorio alojado en Google de 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/