A camada de modelagem semântica do LookML do Looker permite que um analista de dados defina dimensões, agregações, cálculos e relações de dados em um banco de dados SQL. Os modelos do LookML oferecem reutilização de código e integração com o Git. Um modelo do LookML bem estruturado permite que os usuários façam a própria análise e geração de relatórios de dados de autoatendimento.
O modelo LookML é a base de todos os dados solicitados ao Looker, seja pela interface de análise do Looker na interface do Looker, uma visualização incorporada no portal da empresa ou outro aplicativo de terceiros ou um aplicativo personalizado desenvolvido com a API do Looker. A interface Open SQL oferece acesso aos modelos do LookML para qualquer aplicativo de terceiros que ofereça suporte à conectividade de banco de dados Java (JDBC). Os aplicativos podem se conectar a um modelo do LookML como se fosse um banco de dados, permitindo que os usuários aproveitem todo o trabalho feito pelos analistas de dados no modelo do LookML, usando as ferramentas com que se sentem mais confortáveis.
Como a interface do Open SQL mostra elementos do projeto do LookML
Para entender como a interface do Open SQL mostra os elementos de um projeto do LookML, é importante entender como os projetos do LookML são estruturados.
Um projeto do LookML é uma coleção de arquivos que descrevem os objetos, as conexões de banco de dados e os elementos da interface do usuário usados para realizar consultas SQL no Looker. Consulte Termos e conceitos do LookML para mais informações. Os seguintes conceitos do projeto do LookML estão relacionados à interface do Open SQL:
- Um modelo do LookML especifica uma conexão de banco de dados e uma ou mais análises. A interface Open SQL mostra modelos como esquemas de banco de dados.
- Uma análise é um agrupamento lógico de uma ou mais visualizações e as relações de mesclagem entre elas. A interface do Open SQL mostra as análises como tabelas de banco de dados.
- Uma visualização define uma coleção de campos (dimensões e medições). Uma visualização geralmente é baseada em uma tabela no seu banco de dados ou em uma tabela derivada. As visualizações podem conter as colunas da tabela de banco de dados subjacente, bem como qualquer dimensão ou medida personalizada que os usuários finais possam exigir. A interface do Open SQL mostra a combinação de um nome de visualização e um nome de campo como um nome de coluna de banco de dados. Por exemplo, a dimensão
id
na visualizaçãoorder_items
é exibida pela interface Open SQL como uma coluna de banco de dados chamadaorder_items.id
.
Uma Análise do Looker pode definir relacionamentos de mesclagem entre várias visualizações. Como é possível que uma visualização tenha um campo com o mesmo nome que um campo em outra visualização, a interface Open SQL inclui o nome da visualização e o nome do campo ao fazer referência a uma coluna. Portanto, use este formato para fazer referência a um nome de coluna ao enviar consultas para a interface do Open SQL:
`<view_name>.<field_name>`
Por exemplo, se houvesse uma Análise chamada order_items
que unisse uma visualização chamada customer
com uma chamada product
e ambas tivessem uma dimensão id
, você se referiria aos dois campos id
como `customer.id`
e `product.id`
, respectivamente. Para usar o nome totalmente qualificado com o nome da Análise, você pode se referir aos dois campos como `order_items`.`customer.id`
e `order_items`.`product.id`
. Consulte Usar acento grave em torno de identificadores de banco de dados para informações sobre onde colocar os acentos graves ao se referir a identificadores de banco de dados.
Como configurar a interface do Open SQL
Para usar a interface do Open SQL, siga estas etapas:
- Verifique se os requisitos foram atendidos.
- Faça o download do arquivo do driver JDBC da interface Open SQL.
As seções a seguir descrevem essas etapas.
Requisitos
Os seguintes componentes são necessários para usar a interface Open SQL:
- O aplicativo de terceiros que você quer usar (como o Tableau, o ThoughtSpot ou um aplicativo personalizado) precisa se conectar à sua instância do Looker. A interface Open SQL pode ser usada com instâncias do Looker hospedadas pelo cliente, desde que a instância do Looker esteja em rede de uma forma que permita que o aplicativo de terceiros acesse a instância do Looker.
- Um projeto do LookML que usa dados de uma conexão do Google BigQuery. O projeto do LookML precisa ter um arquivo modelo que especifique uma conexão do Google BigQuery no parâmetro
connection
. - Uma função de usuário do Looker que inclui a permissão
explore
no modelo LookML que você quer acessar com a interface Open SQL.
Fazer o download do driver JDBC da interface do Open SQL
O driver JDBC da interface SQL aberta do Looker é chamado de avatica-<release_number>-looker.jar
. Faça o download da versão mais recente no GitHub em https://github.com/looker-open-source/calcite-avatica/releases.
O driver JDBC espera o seguinte formato de URL:
jdbc:looker:url=https://Looker instance URL
Exemplo:
jdbc:looker:url=https://myInstance.cloud.looker.com
A classe do driver JDBC é:
org.apache.calcite.avatica.remote.looker.LookerDriver
Como fazer a autenticação na interface do Open SQL
A interface Open SQL oferece suporte a três métodos de autenticação:
OAuth
Os clientes JDBC compatíveis com OAuth podem ser configurados para usar o servidor OAuth de uma instância do Looker. Siga as etapas para configurar a autenticação OAuth:
- Use a extensão do API Explorer para registrar o cliente OAuth do JDBC na instância do Looker para que ela reconheça as solicitações do OAuth. Consulte Como registrar um aplicativo cliente OAuth para ver instruções.
- Faça login no Looker com o OAuth para solicitar um token de acesso. Consulte Fazer login do usuário usando o OAuth para conferir um exemplo.
- Use um objeto de propriedades para transmitir as credenciais do OAuth ao abrir a conexão JDBC para a interface SQL aberta.
Confira abaixo um exemplo de uso de 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);
Como gerar um token de acesso usando chaves de API
Em vez de usar o fluxo OAuth padrão para gerar um token de acesso, siga estas etapas para usar a API Looker e gerar um token de acesso que possa ser transmitido ao driver JDBC da interface Open SQL:
- Gere chaves de API para o usuário do Looker, conforme descrito na página Configurações do administrador: usuários.
Use o endpoint da API
login
para sua instância do Looker. A resposta inclui um token de acesso no formatoAuthorization: token <access_token>
. Confira abaixo um exemplo do comando curl que pode ser usado para fazer essa solicitação:curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\
Transmita o valor
<access_token>
da resposta como o token no objeto Properties para transmitir as credenciais do OAuth ao abrir a conexão JDBC para a interface Open SQL.
Chaves de API
Também é possível usar chaves de API para autenticação em vez de um nome de usuário e uma senha. As chaves de API são consideradas menos seguras do que o OAuth e podem estar disponíveis apenas durante a visualização da interface SQL aberta. Consulte Chaves de API para saber como criar chaves de API para sua instância do Looker.
Use a parte ID do cliente da chave da API do Looker como o nome de usuário. Use a parte Chave secreta do cliente para a senha.
Como executar consultas com a interface Open SQL
Observe as seguintes diretrizes ao executar consultas com a interface Open SQL:
- A interface Open SQL aceita consultas SQL que aderem à sintaxe do GoogleSQL.
- A interface do Open SQL exige crases (`) em torno de modelos, análises detalhadas e identificadores de campo. Consulte Usar colchetes em torno de identificadores de banco de dados para mais informações e exemplos.
- A interface Open SQL oferece suporte à maioria dos operadores do BigQuery.
- Com a interface Open SQL, é necessário designar todas as medidas do LookML incluídas em uma consulta, envolvendo a medida (incluindo as chaves invertidas) na função especial
AGGREGATE()
. Consulte a seção Especificar medidas do LookML comAGGREGATE()
.
Limitações do SQL
Observe as seguintes limitações do SQL ao enviar consultas para a interface do Open SQL:
- A interface do Open SQL oferece suporte apenas a consultas
SELECT
. A interface Open SQL não oferece suporte a instruçõesUPDATE
eDELETE
ou a qualquer outra instrução de linguagem de definição de dados (DDL), linguagem de manipulação de dados (DML) ou linguagem de controle de dados (DCL). - A interface Open SQL não é compatível com o operador
JOIN
.- Não é possível enviar uma consulta com o operador
JOIN
para a interface do Open SQL para criar mesclagens no mesmo Análise ou em duas Análises diferentes. - Se você quiser criar uma mesclagem entre duas tabelas no seu banco de dados, faça isso no modelo do LookML criando mesclagens com uma ou mais visualizações em uma definição de Análise em um arquivo de modelo no seu projeto do LookML.
- Não é possível enviar uma consulta com o operador
- A interface Open SQL não aceita chamadas de função de janela.
- A interface Open SQL não oferece suporte a subconsultas.
- A interface Open SQL não oferece suporte à conversão de fuso horário. As datas e horas no modelo do LookML terão o tipo
DATETIME
no fuso horário definido nas suas configurações (fuso horário do usuário, fuso horário do aplicativo ou fuso horário do banco de dados). - A interface Open SQL não oferece suporte aos tipos de dados do BigQuery geography, JSON e time.
Use chaves invertidas em torno de identificadores de banco de dados
Ao enviar consultas para a interface do Open SQL, use chaves invertidas em torno dos identificadores de esquema, tabela e coluna. Saiba como especificar elementos do banco de dados usando colchetes com termos do Looker:
- schema:
`<model_name>`
. - tabela:
`<explore_name>`
coluna:
`<view_name>.<field_name>`
Confira um exemplo de formato de instrução SELECT
usando estes elementos:
SELECT `view.field`
FROM `model`.`explore`
LIMIT 10;
Especificar medidas do LookML com AGGREGATE()
As tabelas de banco de dados geralmente contêm apenas dimensões, dados que descrevem um único atributo sobre uma linha na tabela. No entanto, os projetos do LookML podem definir dimensões e medidas. Uma medida é uma agregação de dados em várias linhas, como SUM
, AVG
, MIN
ou MAX
. Outros tipos de medidas também são aceitos. Consulte a página Tipos de medidas para ver a lista completa de tipos de medidas do LookML aceitos.
Com a interface Open SQL, é necessário designar todas as medidas do LookML incluídas em uma consulta, envolvendo a medida (incluindo as chaves invertidas) na função especial AGGREGATE()
. Por exemplo, use isso para especificar a medida contagem na visualização pedidos:
AGGREGATE(`orders.count`)
É necessário agrupar as medidas do LookML na função AGGREGATE()
, seja em uma cláusula SELECT
, HAVING
ou ORDER BY
.
Se você não tiver certeza se um campo é uma medida do LookML, use o método DatabaseMetaData.getColumns
para acessar os metadados do projeto do LookML. A coluna IS_GENERATEDCOLUMN
vai indicar YES
para todas as medições do LookML e NO
para as dimensões do LookML. Consulte a seção Como acessar metadados do banco de dados para mais informações.
Especifique campos e parâmetros somente para filtros com JSON_OBJECT
A interface aberta do SQL aceita parâmetros e campos somente para filtro.
Ao executar consultas com a interface Open SQL, é possível aplicar parâmetros e campos somente filtro à consulta incluindo uma chamada de construtor JSON_OBJECT
com o seguinte formato:
JSON_OBJECT(
'<view>.<parameter name>', '<parameter value>',
'<view>.<filter name>', '<Looker filter expression>'
)
O objeto JSON pode conter zero ou mais pares de chave-valor de filtro e zero ou mais pares de chave-valor de parâmetro.
- A chave no construtor
JSON_OBJECT
precisa ser o nome de um parâmetro ou campo somente para filtro. - Para campos somente de filtro, o valor de cada chave precisa ser uma expressão de filtro de string do Looker.
- Para parâmetros, o valor de cada chave precisa ser um valor simples definido na definição
parameter
.
Consulte as seções a seguir para conferir exemplos de uso de parâmetros e campos somente para filtro com a interface aberta do SQL.
Exemplo de parâmetro
Como exemplo de uso de uma parameter
com a interface Open SQL, se a visualização customers
tiver um parâmetro definido no Looker da seguinte maneira:
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"
}
}
Você pode enviar esta consulta à interface do Open SQL para aplicar o valor do parâmetro segment
de medium_customers
à 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;
A interface aberta do SQL vai transmitir esse valor de parâmetro para a consulta no Looker, que vai aplicar o valor medium_customers
a todos os campos na Análise que estiverem configurados para usar o parâmetro segment
. Consulte a documentação do parameter
para saber como os parâmetros funcionam no Looker.
Exemplo de campo somente para filtros
É possível usar um campo filter
com a interface Open SQL. Por exemplo, se uma visualização products
tiver uma dimensão e um campo somente para filtro definido no Looker da seguinte maneira:
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 ;;
}
É possível usar o filtro brand_select
com a interface Open SQL enviando uma consulta como esta:
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;
A interface aberta do SQL vai aplicar a expressão do filtro de string do Looker %Santa Cruz%
à consulta no Looker. Consulte a documentação filter
para saber como os campos somente para filtro funcionam no Looker.
Forneça valores de always_filter
ou conditionally_filter
em uma cláusula WHERE
ou HAVING
A interface do Open SQL pode oferecer suporte a uma Análise que tenha always_filter
ou conditionally_filter
, mas não as duas.
Se você tiver definido a Análise LookML com always_filter
ou conditionally_filter
, transmita os valores dos campos de filtro na consulta SQL para a interface aberta do SQL:
- Se a definição do filtro especificar uma ou mais dimensões, inclua uma cláusula
WHERE
na consulta SQL para cada uma das dimensões do filtro. - Se a definição do filtro especificar uma ou mais medidas, inclua uma cláusula
HAVING
na consulta SQL para cada uma das medidas do filtro.
Por exemplo, há um modelo faa
em que você definiu uma Análise do LookML flights
com um parâmetro always_filter
que especifica as dimensões country
e aircraft_category
e a métrica count
, conforme abaixo:
explore: flights {
view_name: flights
always_filter: {
filters: [country : "Peru" , aircraft_category : "Airplane", count : ">1"]
}
}
Na consulta à interface do Open SQL, use uma cláusula WHERE
para transmitir valores para as dimensões do filtro e uma cláusula HAVING
para transmitir um valor para o filtro de medida ao modelo do LookML, como este:
SELECT
`flights.make`
FROM
`faa`.`flights`
WHERE `flights.country` = 'Ecuador' AND `flights.aircraft_category` = 'Airplane'
GROUP BY
1
HAVING `flights.count` > 2)
LIMIT 5
Se você não transmitir valores de filtro para cada uma das dimensões e medidas especificadas no parâmetro always_filter
, a consulta vai retornar um erro. O mesmo vale para as dimensões e métricas especificadas em um parâmetro conditionally_filter
, exceto que você pode definir um parâmetro conditionally_filter
com um subparâmetro unless
, como este:
explore: flights {
view_name: flights
conditionally_filter: {
filters: [country : "Peru" , aircraft_category : "Airplane"]
unless: [count]
}
}
Nesse caso, é necessário transmitir um valor de filtro para cada uma das dimensões e medidas especificadas no subparâmetro filters
de conditionally_filter
, a menos que você especifique um filtro em um campo no subparâmetro unless
. Consulte a página de documentação do conditionally_filter
para saber mais sobre como usar o subparâmetro unless
.
Por exemplo, as seguintes consultas para a interface do Open SQL seriam aceitáveis. A primeira consulta fornece valores de filtro para os campos especificados no subparâmetro filters
, e a segunda consulta fornece um valor de filtro para o campo especificado no 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
Exemplo
Confira um exemplo de consulta que usa dimensões e métricas. Essa consulta recupera as dimensões estado e cidade da visualização clientes e a medida valor total da visualização pedidos. Ambas as visualizações são combinadas na análise detalhada pedidos no modelo e-commerce. Para as cidades com mais de 10 pedidos, esta resposta de consulta mostra as cinco cidades com maior quantidade 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;
Como acessar metadados do banco de dados
A interface Open SQL oferece suporte a um subconjunto da interface DatabaseMetaData (link em inglês) JDBC padrão, que é usada para coletar informações sobre o banco de dados subjacente. É possível usar os seguintes métodos da interface DatabaseMetaData para receber informações sobre seu modelo do LookML:
DatabaseMetadata.getSchemas
A tabela a seguir descreve como um modelo da LookML se relaciona às estruturas de banco de dados padrão na resposta do método de interface DatabaseMetadata.getSchemas
.
Coluna de resposta getSchemas |
Descrição |
---|---|
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_CATALOG |
(nulo) |
DatabaseMetadata.getTables
A tabela a seguir descreve como um modelo do LookML se relaciona às estruturas de banco de dados na resposta do método de interface DatabaseMetaData.getTables
. A resposta inclui metadados JDBC padrão e específicos do Looker:
Coluna de resposta getTables |
Descrição |
---|---|
Metadados padrão do JDBC | |
TABLE_CAT |
(nulo) |
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_NAME |
name da análise do LookML |
TABLE_TYPE |
Sempre retorna o valor TABLE_TYPE |
Metadados específicos do Looker | |
DESCRIPTION |
Conferir a descrição |
LABEL |
Rótulo do recurso "Explorar" |
TAGS |
Conheça as tags |
DatabaseMetadata.getColumns
A tabela a seguir descreve como um modelo do LookML se relaciona às estruturas de banco de dados na resposta do método de interface DatabaseMetaData.getColumns
. A resposta inclui metadados JDBC padrão e metadados específicos do Looker:
Coluna de resposta getColumns |
Descrição |
---|---|
Metadados padrão do JDBC | |
TABLE_CAT |
(nulo) |
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_NAME |
Nome da análise detalhada do LookML |
COLUMN_NAME |
Nome do campo LookML no formato `<view_name>.<field_name>` . Por exemplo, `orders.amount` . |
DATA_TYPE |
O código java.sql.Types da coluna. Por exemplo, os campos yesno do Looker são códigos de tipo SQL 16 (BOOLEANO). |
ORDINAL_POSITION |
O ordinal com base em 1 do campo na Análise (misturando dimensões e medidas em ordem alfabética pelo nome da visualização e depois pelo nome do campo) |
IS_NULLABLE |
Sempre retorna o valor YES |
IS_GENERATEDCOLUMN |
YES para medidas, NO para dimensões |
Metadados específicos do Looker | |
DIMENSION_GROUP |
Nome do grupo de dimensões se o campo faz parte de um grupo de dimensões. Se o campo não fizer parte de um grupo de dimensões, ele será nulo. |
DRILL_FIELDS |
Lista de campos de detalhamento definidos para a dimensão ou métrica, se houver |
FIELD_ALIAS |
Alias do campo, se houver |
FIELD_CATEGORY |
Se o campo é dimension ou measure |
FIELD_DESCRIPTION |
Descrição do campo |
FIELD_GROUP_VARIANT |
Se o campo for apresentado em um rótulo de grupo, o FIELD_GROUP_VARIANT vai especificar o nome mais curto do campo exibido no rótulo. |
FIELD_LABEL |
Identificador do campo |
FIELD_NAME |
Nome da dimensão ou medida |
HIDDEN |
Indica se o campo está oculto do seletor de campos nas Análises (TRUE ) ou se ele está visível no seletor de campos nas Análises (FALSE ). |
LOOKER_TYPE |
Tipo de campo do LookML para a dimensão ou métrica |
REQUIRES_REFRESH_ON_SORT |
Indica se a consulta SQL precisa ser atualizada para classificar os valores do campo (TRUE ) ou se os valores do campo podem ser classificados sem precisar atualizar a consulta SQL (FALSE ). |
SORTABLE |
Indica se o campo pode ser classificado (TRUE ) ou não (FALSE ). |
TAGS |
Tags do campo |
USE_STRICT_VALUE_FORMAT |
Indica se o campo usa o formato de valor restrito (TRUE ) ou não (FALSE ). |
VALUE_FORMAT |
String Formato de valor para o campo |
VIEW_LABEL |
Conferir marcador do campo |
VIEW_NAME |
Nome da visualização em que o campo é definido no projeto do LookML |
Como identificar consultas da interface do Open SQL na interface do Looker
Os administradores do Looker podem usar a interface do Looker para identificar quais consultas foram originadas pela interface do Open SQL:
- Na página de administrador Consultas, as consultas da interface SQL aberta têm um valor de Origem "Interface SQL". O valor Usuário mostra o nome do usuário do Looker que executou a consulta. Clique no botão Detalhes de uma consulta para ver mais informações sobre ela. Na caixa de diálogo Detalhes, clique em Consulta da interface SQL para conferir a consulta SQL que foi enviada ao Looker pela interface SQL aberta.
Na Análise detalhada do histórico de atividade do sistema, as consultas da interface aberta do SQL têm um valor de Origem "sql_interface". O valor E-mail do usuário mostra o endereço de e-mail do usuário do Looker que executou a consulta. Você pode acessar diretamente a Análise detalhada Histórico filtrada em "sql_interface" inserindo o endereço da instância do Looker no início deste URL:
https://Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=%22sql_interface%22
Repositório para dependências de terceiros
O link a seguir fornece acesso ao repositório hospedado pelo Google para dependências de terceiros usadas pelo driver JDBC do Looker:
https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/