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 do 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 e relatórios de dados 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 Java Database Connectivity (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 os conceitos do LookML para mais informações). Os seguintes conceitos de projetos do 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 visualização 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 Open SQL

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 ponto final da API login 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 quando abrir a ligação JDBC à interface SQL aberta.

Chaves da API

Também pode usar chaves da API para autenticar 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 SQL aberta:

Use acentos graves em torno dos identificadores da base de dados

Quando enviar 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. (Também são suportados outros tipos de medidas. Consulte a página Tipos de medidas para ver a lista completa dos tipos de medidas 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âmetros.

  • 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

Por exemplo, para usar 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 5;

A interface SQL aberta transmite este valor do parâmetro para a consulta no Looker, e o Looker aplica o valor medium_customers a todos os campos na análise detalhada configurados para usar o parâmetro segment. Consulte a documentação parameter para obter informações sobre como os parâmetros funcionam no Looker.

Exemplo de campo apenas de filtro

Pode usar um campo filter com a interface SQL aberta. Por exemplo, se uma visualização de propriedade products tiver uma dimensão e um campo apenas de filtragem definidos no Looker da seguinte forma:

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

Pode usar o filtro brand_select com a interface SQL aberta enviando uma consulta como a seguinte:

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 aplica a expressão de filtro de string do Looker %Santa Cruz% à consulta no Looker. Consulte a documentação filter para ver informações sobre o funcionamento dos campos apenas de filtragem no Looker.

Indique valores always_filter ou conditionally_filter numa cláusula WHERE ou HAVING

A interface SQL aberta pode suportar uma exploração que tenha always_filter ou conditionally_filter, mas não ambas.

Se tiver definido a sua análise detalhada do LookML com always_filter ou conditionally_filter, tem de transmitir valores para os campos de filtro na sua consulta SQL à interface SQL aberta:

  • Se a definição do filtro especificar uma ou mais dimensões, tem de incluir uma cláusula WHERE na sua consulta SQL para cada uma das dimensões do filtro.
  • Se a definição do filtro especificar uma ou mais métricas, tem de incluir uma cláusula HAVING na sua consulta SQL para cada uma das métricas do filtro.

Por exemplo, existe um faa modelo no qual definiu uma exploração do LookML flights com um parâmetro always_filter que especifica as dimensões country e aircraft_category e a medida count, da seguinte forma: 

explore: flights {
  view_name: flights
  always_filter: {
    filters: [country : "Peru" , aircraft_category : "Airplane", count : ">1"]
  }
}

Na sua consulta à interface SQL aberta, tem de usar uma cláusula WHERE para transmitir valores para as dimensões de filtro e uma cláusula HAVING para transmitir um valor para o filtro de medida ao seu modelo LookML, como o seguinte:

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 não transmitir valores de filtro para cada uma das dimensões e medidas especificadas no parâmetro always_filter, a consulta devolve um erro. O mesmo se aplica às dimensões e medidas especificadas num parâmetro conditionally_filter, exceto que pode definir um parâmetro conditionally_filter com um subparâmetro unless, da seguinte forma:

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

Neste caso, tem de transmitir um valor de filtro para cada uma das dimensões e medidas especificadas no subparâmetro filters de conditionally_filter, a menos que especifique um filtro num campo no subparâmetro unless. (Consulte a página de documentação conditionally_filter para ver detalhes sobre a utilização do subparâmetro unless.)

Por exemplo, qualquer uma das seguintes consultas à interface SQL aberta seria aceitável. 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

Segue-se um exemplo de consulta que usa dimensões e medidas. Esta consulta extrai as dimensões state e city da vista customers e a medida total amount da vista orders. Ambas as vistas são unidas na exploração orders no modelo de comércio eletrónico. Para as cidades com mais de 10 encomendas, esta resposta à consulta mostra as 5 principais cidades por valor da encomenda:

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;

Aceder aos metadados da base de dados

A interface Open SQL suporta um subconjunto da interface DatabaseMetaData JDBC padrão, que é usada para obter informações sobre a base de dados subjacente. Pode usar os seguintes métodos da interface DatabaseMetaData para obter informações sobre o seu modelo LookML:

A interface SQL aberta devolve resultados apenas para modelos, explorações e campos aos quais tem acesso.

DatabaseMetadata.getSchemas

A tabela seguinte descreve a relação entre um modelo do LookML e as estruturas de base 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 seguinte descreve a relação entre um modelo do LookML e as estruturas da base 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 JDBC
TABLE_CAT (null)
TABLE_SCHEM Nome do modelo do LookML
TABLE_NAME Nome da análise detalhada do LookML
TABLE_TYPE Devolve sempre o valor TABLE_TYPE
REMARKS (null)
TYPE_CAT (null)
TYPE_SCHEM (null)
TYPE_NAME String que representa o tipo de tabela. Os tipos possíveis são TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS e SYNONYM.
SELF_REFERENCING_COL_NAME (null)
REF_GENERATION (null)
Metadados específicos do Looker
DESCRIPTION Explore a descrição
LABEL Explore a etiqueta
TAGS Explore etiquetas
CONDITIONALLY_FILTER_UNLESS A lista de campos no subparâmetro unless do parâmetro conditionally_filter da funcionalidade Explorar. Se não forem especificados campos no subparâmetro unless ou se não for definido nenhum parâmetro conditionally_filter para a análise detalhada, este valor é nulo.

DatabaseMetadata.getColumns

A tabela seguinte descreve a relação entre um modelo do LookML e as estruturas da base 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 JDBC
TABLE_CAT (null)
TABLE_SCHEM Nome do modelo do LookML
TABLE_NAME Nome da exploração 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 do Looker são do tipo de código SQL 16 (BOOLEAN).yesno
TYPE_NAME String que representa o tipo de dados da coluna. Para um tipo definido pelo utilizador (UDT), o nome do tipo é totalmente qualificado.
COLUMN_SIZE Número inteiro que representa o número máximo de carateres ou bytes que podem ser armazenados na coluna.
BUFFER_LENGTH (null)
DECIMAL_DIGITS Número inteiro que representa a escala dos dados: o número de dígitos à direita da vírgula decimal, para tipos de dados aplicáveis, ou o número de dígitos fracionários. É devolvido um valor nulo para tipos de dados em que DECIMAL_DIGITS não é aplicável.
NUM_PREC_RADIX Número inteiro que representa a base ou a base de numeração (normalmente, 10 ou 2) dos dados.
NULLABLE

Número inteiro que indica se são permitidos valores nulos:

  • 0: columnNoNulls – pode não permitir valores NULL
  • 1: columnNullable - permite definitivamente valores NULL
  • 2: columnNullableUnknown – nulidade desconhecida
REMARKS (null)
COLUMN_DEF (null)
SQL_DATA_TYPE (null)
SQL_DATETIME_SUB (null)
CHAR_OCTET_LENGTH Para tipos de dados de carateres, um número inteiro que representa o número máximo de bytes na coluna.
ORDINAL_POSITION O ordinal baseado em 1 do campo na análise detalhada (misturando dimensões e medidas por ordem alfabética pelo nome da vista e, em seguida, pelo nome do campo)
IS_NULLABLE Devolve sempre o valor YES
SCOPE_CATALOG (null)
SCOPE_SCHEMA (null)
SCOPE_TABLE (null)
SOURCE_DATA_TYPE (null)
IS_AUTOINCREMENT (null)
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 fizer parte de um grupo de dimensões. Se o campo não fizer parte de um grupo de dimensões, este valor é nulo.
DRILL_FIELDS Lista de campos de detalhe definidos para a dimensão ou a medida, se existirem
FIELD_ALIAS Alias do campo, se existir
FIELD_CATEGORY Se o campo é dimension ou measure
FIELD_DESCRIPTION Campo description
FIELD_GROUP_VARIANT Se o campo for apresentado sob uma etiqueta de grupo, o elemento FIELD_GROUP_VARIANT especifica o nome mais curto do campo que é apresentado sob a etiqueta de grupo.
FIELD_LABEL Campo label
FIELD_NAME Nome da dimensão ou da medida
LOOKER_TYPE Tipo de campo do LookML para a dimensão ou a medida
REQUIRES_REFRESH_ON_SORT Se a consulta SQL tem de ser atualizada para reordenar os valores do campo (TRUE) ou se os valores do campo podem ser reordenados sem necessitar de uma atualização da consulta SQL (FALSE).
SORTABLE Se é possível ordenar o campo (TRUE) ou não (FALSE)
TAGS Campo tags
USE_STRICT_VALUE_FORMAT Se o campo usa o formato de valor rigoroso (TRUE) ou não (FALSE)
VALUE_FORMAT String formato do valor para o campo
VIEW_LABEL Ver etiqueta para o campo
VIEW_NAME Nome da vista na qual o campo está definido no projeto LookML
HIDDEN Se o campo está oculto no selecionador de campos em Explorar (TRUE) ou se o campo está visível no selecionador de campos em Explorar (FALSE).
ALWAYS_FILTER O valor predefinido do parâmetro always_filter definido no campo. Se o campo não fizer parte de um parâmetro always_filter, este valor é nulo.
CONDITIONALLY_FILTER O valor predefinido do parâmetro conditionally_filter definido no campo. Se o campo não fizer parte de um parâmetro conditionally_filter, este valor é nulo.

Identificar consultas de interface SQL aberta na IU do Looker

Os administradores do Looker podem usar a IU do Looker para identificar as consultas que tiveram origem na interface SQL aberta:

  • Na página de administração Consultas, as consultas da interface SQL aberta têm um valor Origem de "Interface SQL". O valor User mostra o nome do utilizador do Looker que executou a consulta. Pode clicar no botão Detalhes de uma consulta para apresentar informações adicionais sobre a consulta. Na caixa de diálogo Detalhes, pode clicar em Consulta da interface SQL para ver a consulta SQL que foi enviada para o Looker a partir da interface SQL aberta.

  • No Histórico de atividade do sistema do Explore, as consultas da interface SQL aberta têm um valor de Origem de "sql_interface". O valor Email do utilizador mostra o endereço de email do utilizador do Looker que executou a consulta. Pode aceder diretamente à página Histórico do Explore filtrada em "sql_interface" inserindo o endereço da sua 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 seguinte fornece acesso ao repositório alojado pela Google para dependências de terceiros usadas pelo controlador JDBC do Looker:

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/