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 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 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 vistaorder_items
como una columna de base de datos llamadaorder_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:
- Verifica que se cumplan los requisitos.
- 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:
- Una instancia de Looker alocada en Looker y que ejecute Looker 23.18 o una versión posterior.
- Un proyecto de LookML que usa datos de una conexión de Google BigQuery. (El proyecto de LookML debe tener un archivo de modelo que especifique una conexión de Google BigQuery en su parámetro
connection
). - Un rol del usuario de Looker que incluya el permiso
explore
en el modelo de LookML al que quieres acceder con la interfaz abierta de SQL
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://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:
- 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.
- 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.
- 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:
- Genera claves de API para tu usuario de Looker como se describe en la página Configuración del administrador: Usuarios.
Usa el extremo de la API de
login
para tu instancia de Looker. La respuesta incluye un token de acceso en el formatoAuthorization: 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\
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:
- La interfaz de Open SQL acepta consultas SQL que cumplen con la sintaxis GoogleSQL.
- La interfaz de Open SQL requiere acentos graves (`) en torno al modelo, la exploración y los identificadores de campo. Consulta Usa acentos graves en los identificadores de bases de datos para obtener información adicional y ejemplos.
- La interfaz de Open SQL admite la mayoría de los operadores de BigQuery. Si necesitas un operador que no es compatible, envía una solicitud por correo electrónico a looker-sql-interface@google.com.
- 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()
. Consulta la sección Especifica medidas de LookML conAGGREGATE()
.
Limitaciones de LookML
Ten en cuenta lo siguiente cuando envíes consultas a la interfaz abierta de SQL:
- Puedes usar una cláusula
WHERE
en una consulta de la interfaz de Open SQL para pasar los valoresalways_filter
yconditionally_filter
a tu modelo de LookML.
Limitaciones de SQL
Ten en cuenta las siguientes limitaciones de SQL cuando envíes consultas a la interfaz de Open SQL:
- La interfaz de Open SQL solo admite consultas
SELECT
. La interfaz de Open SQL no admite las declaracionesUPDATE
niDELETE
, ni ningún otro lenguaje de definición de datos (DDL), lenguaje de manipulación de datos (DML) ni lenguaje de control de datos (DCL). - La interfaz abierta de SQL no admite el operador
JOIN
.- No puedes enviar una consulta con el operador
JOIN
a la interfaz abierta de SQL para crear uniones dentro de la misma exploración o entre dos exploraciones diferentes. - Si quieres crear una unión entre dos tablas en tu base de datos, puedes hacerlo en el modelo de LookML creando uniones a una o más vistas en una definición de Explorar dentro de un archivo de modelo en tu proyecto de LookML.
- No puedes enviar una consulta con el operador
- La interfaz abierta de SQL no admite las llamadas a funciones analíticas.
- La interfaz de Open SQL no admite subconsultas.
- La interfaz de Open SQL no admite la conversión de zona horaria. Las fechas y horas del modelo de LookML tendrán el tipo
DATETIME
en la zona horaria que se define en tu configuración (zona horaria del usuario, zona horaria de la aplicación o zona horaria de la base de datos). - La interfaz de Open SQL no admite los tipos de datos de BigQuery geografía, JSON y tiempo.
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. 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
Escribe a looker-sql-interface@google.com si tienes alguna pregunta o deseas solicitar funciones para Open SQL Interface.