Abrir interface SQL

A camada de modelagem semântica do LookML do Looker permite a um analista de dados definir dimensões, agregados, cálculos e relações de dados numa base de dados SQL. Os modelos da LookML oferecem reutilização de código e integração do Git. Um modelo LookML bem estruturado permite aos utilizadores fazer a sua própria exploração de dados e relatórios self-service.

O modelo LookML é a base de todos os dados pedidos ao Looker, quer esse pedido seja feito a partir da interface de exploração do Looker na IU do Looker, de uma visualização incorporada no portal da sua empresa ou noutra aplicação de terceiros, ou de uma aplicação personalizada que foi desenvolvida com a API do Looker. A interface SQL aberta fornece acesso aos modelos do LookML a qualquer aplicação de terceiros que suporte a conectividade de base de dados Java (JDBC). As aplicações podem estabelecer ligação a um modelo do LookML como se fosse uma base de dados, o que permite aos utilizadores tirar partido de todo o trabalho realizado pelos respetivos analistas de dados no modelo do LookML, ao mesmo tempo que usam as ferramentas com as quais se sentem mais confortáveis.

Como a interface SQL aberta apresenta elementos do projeto LookML

Para compreender como a interface SQL aberta apresenta os elementos de um projeto LookML, é importante compreender como os projetos LookML estão estruturados.

Um projeto do LookML é uma coleção de ficheiros que descrevem os objetos, as ligações da base de dados e os elementos da interface do utilizador que são usados para fazer consultas SQL no Looker (consulte os termos e conceitos do LookML para mais informações). Os seguintes conceitos do projeto LookML estão relacionados com a interface SQL aberta:

  • Um modelo do LookML especifica uma ligação da base de dados e uma ou mais explorações. A interface SQL aberta apresenta os modelos como esquemas de base de dados.
  • Uma exploração é um agrupamento lógico de uma ou mais visualizações de propriedades e as relações de junção entre essas visualizações de propriedades. A interface SQL aberta apresenta as explorações como tabelas de base de dados.
  • Uma vista define uma coleção de campos (dimensões e medidas). Geralmente, uma vista baseia-se numa tabela na sua base de dados ou numa tabela derivada. As vistas podem conter as colunas da tabela da base de dados subjacente, bem como quaisquer dimensões ou medidas personalizadas que os utilizadores finais possam precisar. A interface SQL aberta apresenta a combinação de um nome de vista e um nome de campo como um nome de coluna da base de dados. Por exemplo, a dimensão id na vista order_items é apresentada pela interface SQL aberta como uma coluna da base de dados denominada order_items.id.

Uma exploração do Looker pode definir relações de junção entre várias visualizações. Uma vez que é possível que uma vista tenha um campo com o mesmo nome que um campo numa vista diferente, a interface SQL aberta inclui o nome da vista e o nome do campo quando faz referência a uma coluna. Por conseguinte, use este formato para fazer referência a um nome de coluna quando enviar consultas para a interface SQL aberta:

`<view_name>.<field_name>`

Por exemplo, se existir uma exploração denominada order_items que junta uma vista denominada customer com uma vista denominada product e ambas as vistas tiverem uma dimensão id, refere-se aos dois campos id como `customer.id` e `product.id`, respetivamente. Para usar o nome completo com o nome do Explorar também, refere-se aos dois campos como `order_items`.`customer.id` e `order_items`.`product.id`. (Consulte o artigo Use acentos graves em torno dos identificadores de bases de dados para obter informações sobre onde colocar os acentos graves quando se refere a identificadores de bases de dados.)

Configurar a interface SQL aberta

Para usar a interface SQL aberta, siga estes passos:

  1. Verifique se os requisitos estão cumpridos.
  2. Transfira o ficheiro do controlador JDBC da interface SQL aberta.

As secções seguintes descrevem estes passos.

Requisitos

Os seguintes componentes são necessários para usar a interface SQL aberta:

Transfira o controlador JDBC da interface SQL aberta

O controlador JDBC da interface SQL aberta do Looker chama-se avatica-<release_number>-looker.jar. Transfira a versão mais recente do GitHub em https://github.com/looker-open-source/calcite-avatica/releases.

O controlador JDBC espera o seguinte formato de URL:

jdbc:looker:url=https://Looker instance URL

Por exemplo:

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

A classe do controlador JDBC é:

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

Autenticação na interface SQL aberta

A interface SQL aberta suporta três métodos de autenticação:

OAuth

Os clientes JDBC que suportam o OAuth podem ser configurados para usar o servidor OAuth de uma instância do Looker. Siga os passos para configurar a autenticação OAuth:

  1. Use a extensão API Explorer para registar o cliente OAuth JDBC na sua instância do Looker, para que a instância do Looker possa reconhecer pedidos OAuth. Consulte o artigo Registar uma aplicação cliente OAuth para ver instruções.
  2. Inicie sessão no Looker com o OAuth para pedir uma chave de acesso. Consulte o artigo Realizar o início de sessão do utilizador através do OAuth para ver um exemplo.
  3. Use um objeto Properties para transmitir as credenciais OAuth quando abrir a ligação JDBC à interface Open SQL.

Segue-se um exemplo que 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);

Gerar um token de acesso através de chaves da API

Em vez de usar o fluxo OAuth padrão para gerar um token de acesso, pode seguir estes passos para usar a API Looker para gerar um token de acesso que pode ser transmitido ao controlador JDBC da interface SQL aberta:

  1. Gere chaves de API para o seu utilizador do Looker, conforme descrito na página Definições de administração – Utilizadores.

  2. Use o login ponto final da API para a sua instância do Looker. A resposta inclui um token de acesso no formato Authorization: token <access_token>. Segue-se um exemplo do comando curl que pode usar para fazer este pedido:

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Transmita o valor <access_token> da resposta como o token no objeto Properties para transmitir as credenciais OAuth ao abrir a ligação JDBC à interface SQL aberta.

Chaves da API

Também pode usar chaves da API para fazer a autenticação em vez de um nome de utilizador e uma palavra-passe. As chaves da API são consideradas menos seguras do que o OAuth e podem só estar disponíveis durante a pré-visualização da interface SQL aberta. Consulte o artigo Chaves da API para obter informações sobre como criar chaves da API para a sua instância do Looker.

Use a parte do ID de cliente da chave da API Looker como nome de utilizador. Use a parte Client Secret para a palavra-passe.

Executar consultas com a interface SQL aberta

Tenha em atenção as seguintes diretrizes quando executar consultas com a interface SQL aberta:

Limitações de SQL

Tenha em atenção as seguintes limitações de SQL ao enviar consultas para a interface Open SQL:

Use acentos graves em torno dos identificadores de bases de dados

Quando envia consultas para a interface SQL aberta, use acentos graves à volta dos identificadores de esquemas, tabelas e colunas. Veja como especificar elementos da base de dados usando acentos graves com termos do Looker:

  • esquema: `<model_name>`
  • tabela: `<explore_name>`

  • coluna: `<view_name>.<field_name>`

Segue-se um exemplo do formato de declaração SELECT com estes elementos:

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

Especifique medidas do LookML com AGGREGATE()

Normalmente, as tabelas de base 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 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 suportados. Consulte a página Tipos de medidas para ver a lista completa dos tipos de medidas do LookML suportados.)

Com a interface SQL aberta, tem de designar todas as medidas do LookML incluídas numa consulta ao envolver a medida (incluindo acentos graves) na função especial AGGREGATE(). Por exemplo, use isto para especificar a medida count da vista orders:

AGGREGATE(`orders.count`)

Tem de incluir as medidas do LookML na função AGGREGATE(), quer a medida esteja numa cláusula SELECT, numa cláusula HAVING ou numa cláusula ORDER BY.

Se não tiver a certeza de que um campo é uma medida do LookML, pode usar o método DatabaseMetaData.getColumns para aceder aos metadados do projeto do LookML. A coluna IS_GENERATEDCOLUMN indica YES para quaisquer medidas do LookML e NO para dimensões do LookML. Consulte a secção Aceder aos metadados da base de dados para mais informações.

Especifique parâmetros e campos apenas de filtragem com JSON_OBJECT

A interface SQL aberta suporta parâmetros e campos apenas de filtragem.

Quando executa consultas com a interface SQL aberta, pode aplicar parâmetros e campos apenas de filtragem à 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 tem de ser o nome de um campo ou um parâmetro apenas de filtragem.
  • Para campos apenas de filtragem, o valor de cada chave tem de ser uma expressão de filtro de string do Looker.
  • Para os parâmetros, o valor de cada chave tem de ser um valor simples definido na definição parameter.

Consulte as secções seguintes para ver exemplos de utilização de parâmetros e campos apenas de filtragem com a interface SQL aberta.

Exemplo de parâmetro

Como exemplo de utilização de um parameter com a interface SQL aberta, se a vista customers tiver um parâmetro definido no Looker da seguinte forma:

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"
  }
}

Pode enviar esta consulta para a interface SQL aberta 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