A camada de modelagem semântica LookML do Looker permite que o analista de dados defina dimensões, agregados, cálculos e relações entre dados em um banco de dados SQL. Os modelos LookML oferecem reutilização de código e integração do Git. Um modelo LookML bem estruturado permite que os usuários façam a própria exploração e geração de relatórios de dados por autoatendimento.
O modelo LookML é a base de todos os dados solicitados ao Looker, seja da interface da Análise do Looker na interface do Looker, de uma visualização incorporada no portal da sua empresa ou de outro aplicativo de terceiros, ou de um aplicativo personalizado desenvolvido com a API Looker. A Interface Open SQL fornece acesso aos modelos LookML para qualquer aplicativo de terceiros que ofereça suporte ao Java Database Connectivity (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 realizado por seus analistas de dados no modelo LookML, enquanto usam as ferramentas com as quais eles estão mais confortáveis.
Como a interface aberta SQL mostra elementos do projeto do LookML
Para entender como a interface aberta SQL apresenta os elementos de um projeto do LookML, é importante compreender 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 os Termos e conceitos do LookML para mais informações). Os seguintes conceitos de projeto do LookML estão relacionados à Interface Open SQL:
- Um modelo do LookML especifica uma conexão de banco de dados e uma ou mais Análises. A interface do 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 "Explores" como tabelas de banco de dados.
- Uma visualização define uma coleção de campos (dimensões e medidas). 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 quaisquer dimensões ou medições personalizadas que os usuários finais possam precisar. 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 do banco de dados. Por exemplo, a dimensão
id
na visualizaçãoorder_items
é mostrada pela interface SQL aberta como uma coluna de banco de dados chamadaorder_items.id
.
Uma Análise do Looker pode definir relações de mesclagem entre várias visualizações. Como é possível que uma visualização tenha um campo com o mesmo nome de um campo em outra visualização, a interface SQL aberta 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 Open SQL:
`<view_name>.<field_name>`
Por exemplo, se houver uma Análise chamada order_items
que mescla uma visualização chamada customer
com uma visualização chamada product
e ambas tiverem uma dimensão id
, você vai se referir aos dois campos id
como `customer.id`
e `product.id`
, respectivamente. Para usar o nome totalmente qualificado com o nome "Análise", você consulta os dois campos como `order_items`.`customer.id`
e `order_items`.`product.id`
. Consulte Usar acentos graves em torno dos identificadores do banco de dados para saber onde os acentos graves devem ser inseridos ao se referir a identificadores de banco de dados.
Configuração da interface aberta SQL
Para usar a interface SQL aberta, siga estas etapas:
- Verifique se os requisitos foram atendidos.
- Faça o download do arquivo do driver do JDBC da interface SQL aberta.
As seções abaixo descrevem essas etapas.
Requisitos
Os seguintes componentes são necessários para usar a Interface SQL aberta:
- Uma instância hospedada pelo Looker com a versão 23.18 ou mais recente
- Um projeto do LookML que usa dados de uma conexão do Google BigQuery. O projeto do LookML precisa ter um arquivo de modelo que especifique uma conexão do Google BigQuery no parâmetro
connection
. - Uma função do usuário do Looker que inclua a permissão
explore
no modelo LookML que você quer acessar com a interface aberta SQL.
Fazer o download do driver JDBC da interface SQL aberta
O driver JDBC da interface SQL aberta do Looker tem o nome 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 autenticar na interface Open SQL
A Interface Open SQL é compatível com 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 API Explorer para registrar o cliente OAuth do JDBC na sua instância do Looker e permitir que ela reconheça as solicitações do OAuth. Consulte as instruções em Como registrar um aplicativo cliente OAuth.
- Faça login no Looker com o OAuth para solicitar um token de acesso. Consulte Como fazer login de usuário usando o OAuth para ver um exemplo.
- Use um objeto de propriedades para transmitir as credenciais do OAuth ao abrir a conexão do JDBC para a interface Open SQL.
Veja a seguir um exemplo usando 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);
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 pode ser transmitido para o driver JDBC da interface SQL aberta:
- Gere chaves de API para o usuário do Looker conforme descrito na página Configurações de administrador – Usuários.
Use o endpoint de API
login
na sua instância do Looker. A resposta inclui um token de acesso no formatoAuthorization: token <access_token>
. Veja a seguir um exemplo do comando curl que pode ser usado para fazer esta 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 de propriedades para transmitir as credenciais do OAuth ao abrir a conexão JDBC para a Open SQL Interface.
Chaves de API
Você também pode usar chaves de API para autenticar 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 somente durante a visualização da Interface SQL aberta. Acesse Chaves de API e saiba como criar chaves para sua instância do Looker.
Use a parte do ID do cliente da chave de API Looker como o nome de usuário. Use a parte da Chave secreta do cliente para a senha.
Como executar consultas com a interface SQL aberta
Observe as seguintes diretrizes ao executar consultas com a interface Open SQL:
- A interface Open SQL aceita consultas SQL que aderem à sintaxe GoogleSQL.
- A Interface Open SQL exige acentos graves (`) ao redor do modelo, da Análise e dos identificadores de campo. Consulte Usar acentos graves em torno dos identificadores do banco de dados para mais informações e exemplos.
- A interface do Open SQL é compatível com a maioria dos operadores do BigQuery. Se você precisar de um operador incompatível, envie uma solicitação por e-mail para looker-sql-interface@google.com.
- Com a interface Open SQL, é necessário designar todas as medidas do LookML incluídas em uma consulta unindo a medida (incluindo acentos graves) na função especial
AGGREGATE()
. Consulte a seção Especificar medidas do LookML comAGGREGATE()
.
Limitações do LookML
Ao enviar consultas para a interface SQL aberta, tenha em mente o seguinte:
- É possível usar uma cláusula
WHERE
em uma consulta de interface SQL aberta para transmitir os valoresalways_filter
econditionally_filter
para o modelo LookML.
Limitações do SQL
Observe as seguintes limitações de SQL ao enviar consultas para a Interface Open SQL:
- A interface SQL aberta é compatível apenas com consultas
SELECT
. A interface aberta SQL não oferece suporte para instruçõesUPDATE
eDELETE
ou 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, na sigla em inglês). - A interface SQL aberta não é compatível com o operador
JOIN
.- Não é possível enviar uma consulta com o operador
JOIN
à interface SQL aberta para criar mesclagens na mesma Análise ou em duas Análises diferentes. - Para mesclar duas tabelas no banco de dados, use o modelo do LookML criando mesclagens para uma ou mais visualizações em uma definição de Análise em um arquivo de modelo no projeto do LookML.
- Não é possível enviar uma consulta com o operador
- A interface aberta SQL não é compatível com chamadas de função de janela.
- A interface SQL aberta não é compatível com subconsultas.
- A interface do Open SQL não é compatível com a conversão de fuso horário. As datas e horas no modelo LookML terão o tipo
DATETIME
no fuso horário definido nas configurações (configurações de fuso horário do usuário, fuso horário do app ou fuso horário do banco de dados). - A interface do Open SQL não aceita os tipos de dados do BigQuery geografia, JSON e tempo.
Usar acentos graves em identificadores de banco de dados
Ao enviar consultas para a Open SQL Interface, use crases ao redor dos identificadores de esquema, tabela e coluna. Confira como especificar elementos de banco de dados usando acentos graves 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()
Em geral, as tabelas de banco de dados 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 medições. 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 compatíveis. Consulte a página Tipos de medidas para conferir a lista completa de opções compatíveis.
Com a interface Open SQL, é necessário designar todas as medidas do LookML incluídas em uma consulta unindo a medida (incluindo acentos graves) na função especial AGGREGATE()
. Por exemplo, use-o para especificar a medida count na visualização orders:
AGGREGATE(`orders.count`)
É necessário unir as medidas do LookML na função AGGREGATE()
, independente de elas estarem 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 qualquer medida do LookML e NO
para dimensões do LookML. Consulte a seção Como acessar metadados do banco de dados para mais informações.
Exemplo
Este é um exemplo de consulta que usa dimensões e medições. Essa consulta recupera as dimensões state e city da visualização customers e a medida total amount da visualização orders. Essas duas visualizações são unidas à Análise de orders no modelo de e-commerce. Para as cidades com mais de 10 pedidos, essa resposta de consulta mostra as cinco principais cidades por valor do 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;
Especifique campos e parâmetros somente para filtros com JSON_OBJECT
A interface SQL aberta oferece suporte a parâmetros e campos somente filtro.
Ao executar consultas com a interface SQL aberta, é possível aplicar parâmetros e campos somente de 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 campo ou parâmetro somente para filtros. - Para campos somente para filtro, o valor de cada chave precisa ser uma expressão de filtro da string do Looker.
- Para parâmetros, o valor de cada chave precisa ser um valor simples definido na definição de
parameter
.
Consulte as seções a seguir para ver exemplos de uso de parâmetros e campos somente para filtros com a interface aberta do SQL.
Exemplo de parâmetro
Como exemplo de uso de parameter
com interface SQL aberta, 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"
}
}
É possível enviar essa consulta à interface SQL aberta para aplicar o valor de 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 SQL aberta vai transmitir esse valor de parâmetro à consulta no Looker, e o Looker aplicará o valor de medium_customers
a todos os campos da Análise configurados para usar o parâmetro segment
. Consulte a documentação de 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 SQL aberta. Por exemplo, se uma visualização products
tiver uma dimensão e um campo somente 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 SQL aberta 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 SQL aberta vai aplicar a expressão de filtro de string do Looker %Santa Cruz%
à consulta no Looker. Consulte a documentação de filter
para saber como os campos somente de filtro funcionam no Looker.
Como acessar metadados do banco de dados
A interface Open SQL é compatível com um subconjunto da interface DatabaseMetaData padrão do JDBC, usada para receber informações sobre o banco de dados subjacente. Use os seguintes métodos da interface DatabaseMetaData para receber informações sobre o modelo LookML:
DatabaseMetadata.getSchemas
A tabela a seguir descreve como um modelo do LookML se relaciona com as estruturas de banco de dados padrão na resposta do método da interface DatabaseMetadata.getSchemas
.
getSchemas coluna de resposta |
Descrição |
---|---|
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_CATALOG |
(null) |
DatabaseMetadata.getTables
A tabela a seguir descreve como um modelo do LookML se relaciona com as estruturas do banco de dados na resposta do método da interface DatabaseMetaData.getTables
. A resposta inclui metadados JDBC padrão, bem como metadados específicos do Looker:
getTables coluna de resposta |
Descrição |
---|---|
Metadados padrão do JDBC | |
TABLE_CAT |
(null) |
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_NAME |
Nome da Análise do LookML |
TABLE_TYPE |
Sempre retorna o valor TABLE_TYPE |
Metadados específicos do Looker | |
DESCRIPTION |
Explorar description |
LABEL |
Explorar marcador |
TAGS |
Explorar tags |
DatabaseMetadata.getColumns
A tabela a seguir descreve como um modelo do LookML se relaciona com as estruturas do banco de dados na resposta do método da interface DatabaseMetaData.getColumns
. A resposta inclui metadados JDBC padrão, bem como metadados específicos do Looker:
getColumns coluna de resposta |
Descrição |
---|---|
Metadados padrão do JDBC | |
TABLE_CAT |
(null) |
TABLE_SCHEM |
Nome do modelo do LookML |
TABLE_NAME |
Nome da Análise do LookML |
COLUMN_NAME |
Nome do campo do 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 (BOOLEAN). |
ORDINAL_POSITION |
O ordinal com base em 1 do campo em "Explorar" (combinando dimensões e medidas em ordem alfabética por nome da visualização e depois do nome do campo) |
IS_NULLABLE |
Sempre retorna o valor YES |
IS_GENERATEDCOLUMN |
YES para medições, NO para dimensões |
Metadados específicos do Looker | |
DIMENSION_GROUP |
Nome do grupo de dimensão, se o campo fizer 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 medida, se houver |
FIELD_ALIAS |
Alias para o 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 campo identificador do grupo, o FIELD_GROUP_VARIANT especificará o nome mais curto do campo exibido no marcador do grupo. |
FIELD_LABEL |
Rótulo do campo |
FIELD_NAME |
Nome da dimensão ou medição |
HIDDEN |
Indica se o campo está oculto no seletor em "Análises" (TRUE ) ou se está visível no seletor em "Análises" (FALSE ). |
LOOKER_TYPE |
Tipo de campo do LookML para a dimensão ou a medida |
REQUIRES_REFRESH_ON_SORT |
Indica se a consulta SQL precisa ser atualizada para reclassificar os valores do campo (TRUE ) ou se os valores do campo podem ser reclassificados sem exigir uma atualização da consulta SQL (FALSE ). |
SORTABLE |
Se o campo pode ser classificado (TRUE ) ou não pode ser classificado (FALSE ) |
TAGS |
Tags de campo |
USE_STRICT_VALUE_FORMAT |
Indica se o campo usa formato de valor estrito (TRUE ) ou não (FALSE ). |
VALUE_FORMAT |
String de formato de valor para o campo |
VIEW_LABEL |
Ver marcador do campo |
VIEW_NAME |
Nome da visualização em que o campo é definido no projeto do LookML. |
Identificar consultas da interface SQL aberta na interface do Looker
Os administradores do Looker podem usar a interface do Looker para identificar quais consultas são originadas da interface aberta SQL:
- Na página de administração Consultas, as consultas da interface SQL aberta têm o valor "Interface SQL" de Origem. O valor User mostra o nome do usuário do Looker que executou a consulta. É possível clicar no botão Detalhes em uma consulta para exibir informações adicionais sobre ela. Na caixa de diálogo Detalhes, clique em Consulta SQL da interface para conferir a consulta SQL que foi enviada ao Looker pela interface SQL aberta.
Em Análise do histórico de atividades do sistema, as consultas da interface SQL aberta têm o valor "sql_interface" de Source. 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 do Histórico filtrada por "sql_interface". inserindo o endereço da instância do Looker no início do URL:
https://Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface
Repositório para dependências de terceiros
O link a seguir dá 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/
Feedback sobre a interface SQL aberta
Entre em contato com looker-sql-interface@google.com se tiver dúvidas ou solicitações de recursos da Open SQL Interface.