Interface aberta do SQL

A camada de modelagem semântica LookML do Looker permite que um analista de dados defina dimensões, agregados, 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 do Git. Um modelo LookML bem estruturado permite que os usuários façam análises e relatórios de dados autônomos.

O modelo LookML é a base de todos os dados solicitados ao Looker, sejam eles da interface "Explore" do Looker na interface do Looker, de uma visualização incorporada no portal da empresa ou de outro aplicativo de terceiros ou de um aplicativo personalizado desenvolvido com a API Looker. Ela dá acesso aos modelos LookML a qualquer aplicativo de terceiros compatível com Java Database Connectivity (JDBC). Os aplicativos podem se conectar a um modelo LookML como se fosse um banco de dados. Assim, os usuários aproveitam todo o trabalho feito pelos analistas de dados no modelo e usam as ferramentas que preferirem.

Como a interface Open SQL mostra elementos do projeto LookML

Para entender como a interface aberta do 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 descreve os objetos, as conexões do 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 do projeto LookML estão relacionados à Interface Open SQL:

  • Um model do LookML especifica uma conexão de banco de dados e uma ou mais Análises. A Open SQL Interface 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 essas visualizações. A interface Open SQL mostra "Explores" como tabelas de banco de dados.
  • Uma visualização define um conjunto de campos (dimensões e medidas). Uma visualização geralmente é baseada em uma tabela do seu banco de dados ou em uma tabela derivada. As visualizações podem ter as colunas da tabela de banco de dados, além de dimensões ou medidas personalizadas que os usuários finais possam precisar. A interface 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ção order_items é exibida pela Open SQL Interface como uma coluna do banco de dados chamada order_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 Open SQL Interface 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 Open SQL Interface:

`<view_name>.<field_name>`

Por exemplo, se houvesse uma Análise chamada order_items que mesclasse uma visualização chamada customer com uma visualização chamada product e as duas visualizações 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 da Análise, faça referência aos dois campos como `order_items`.`customer.id` e `order_items`.`product.id`. (Consulte Usar acentos graves em torno de identificadores de banco de dados para informações sobre onde colocá-los ao se referir a identificadores de banco de dados.)

Como configurar a Open SQL Interface

Para usar a Open SQL Interface, execute as seguintes etapas:

  1. Verifique se os requisitos foram atendidos.
  2. Ative a interface Open SQL na instância do Looker.
  3. Faça o download do arquivo de driver JDBC da Open SQL Interface (em inglês).

As seções a seguir descrevem essas etapas.

Requisitos

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

Ativar a interface Open SQL na sua instância do Looker

Execute as etapas a seguir para ativar a interface Open SQL na instância:

Fazer o download do driver JDBC da Open SQL Interface

O driver JDBC da interface aberta SQL 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://your Looker instance URL

Exemplo:

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

A classe do driver do JDBC é:

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

Como autenticar na Open SQL Interface

A Open SQL Interface é compatível com três métodos de autenticação:

OAuth

Os clientes JDBC compatíveis com o OAuth podem ser configurados para usar um servidor OAuth da instância do Looker. Siga as etapas para configurar a autenticação OAuth:

  1. Use a extensão do API Explorer para registrar o cliente OAuth do JDBC na sua instância do Looker. Assim, ela reconhece as solicitações OAuth. Consulte Como registrar um aplicativo cliente OAuth para ver instruções.
  2. 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.
  3. Use um objeto de propriedades para transmitir as credenciais do OAuth ao abrir a conexão JDBC com o Open SQL Interface.

Veja a seguir 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 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 Open SQL Interface:

  1. Gere chaves de API para o usuário do Looker, conforme descrito na página Configurações do administrador: usuários.

  2. Use o endpoint da API login para sua instância do Looker. A resposta inclui um token de acesso no formato: Authorization: token <access_token>. Confira um exemplo de comando curl para fazer a solicitação:

      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 do OAuth ao abrir a conexão JDBC com o Open SQL Interface.

Chaves de API

Também é possível usar chaves de API para autenticação no lugar de nome de usuário e senha. As chaves de API são consideradas menos seguras do que o OAuth e só podem estar disponíveis durante o pré-lançamento da Open SQL Interface. Consulte informações sobre como criar chaves de API para sua instância do Looker em Chaves de API.

Use a parte ID do cliente da chave da API Looker como o nome de usuário. Use a parte Chave secreta do cliente da senha.

Como executar consultas com a Open SQL Interface

Observe as seguintes diretrizes ao executar consultas com a Open SQL Interface:

Limitações do LookML

Observe as seguintes limitações do LookML ao enviar consultas para a Open SQL Interface:

Limitações de SQL

Observe as seguintes limitações de SQL ao enviar consultas para a Open SQL Interface:

Usar acento grave entre identificadores de banco de dados

Ao enviar consultas para a Open SQL Interface, use acentos graves em torno de identificadores de esquema, tabela e coluna. Confira como especificar elementos do 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()

As tabelas de banco de dados normalmente contêm somente dimensões, dados que descrevem um único atributo sobre uma linha na tabela. Já nos projetos do LookML, é possível 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 compatíveis. Consulte a página Tipos de medidas para conferir a lista completa de tipos compatíveis do LookML.

Com a Open SQL Interface, é 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(), independentemente de a medida estar 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 medidas. Esta consulta recupera as dimensões state e state da visualização de state, e a medida state da visualização de state. Essas duas visualizações são unidas nas Análises de pedidos no modelo de e-commerce. Para cidades que têm mais de 10 pedidos, essa resposta da consulta mostra as 5 principais cidades pelo 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

O Open SQL Interface é compatível com parâmetros e campos somente de filtro.

Ao executar consultas com o Open SQL Interface, você pode 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 de 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 de parameter.

Consulte as seções a seguir para ver exemplos de como usar parâmetros e campos somente filtro com o Open SQL Interface.

Exemplo de parâmetro

Como exemplo de uso de um parameter com interface Open SQL, se a visualização customers tivesse 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 esta consulta à Open SQL Interface 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 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 filtro

Você pode usar um campo filter com Open SQL Interface. Por exemplo, se uma visualização products tiver uma dimensão e um campo somente filtro definidos 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 ;;
    }

Você pode usar o filtro brand_select com Open SQL Interface 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 vai aplicar a expressão de filtro de string do Looker %Santa Cruz% à consulta no Looker. Consulte a documentação do filter para mais informações sobre como os campos somente filtros funcionam no Looker.

Como acessar metadados de banco de dados

A Open SQL Interface é compatível com um subconjunto da interface DatabaseMetaData padrão do JDBC, que é usada para coletar informações sobre o banco de dados subjacente. É possível usar os seguintes métodos da interface DatabaseMetaData para conseguir informações sobre o modelo LookML:

DatabaseMetadata.getSchemas

A tabela a seguir descreve como um modelo LookML se relaciona com as estruturas padrão de banco de dados na resposta do método de interface DatabaseMetadata.getSchemas.

Coluna de resposta getSchemas Descrição
TABLE_SCHEM Nome do modelo do LookML
TABLE_CATALOG (null)

DatabaseMetadata.getTables

A tabela a seguir descreve como um modelo LookML se relaciona com as 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 (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 Explore a descrição
LABEL Explorar rótulo
TAGS Conheça as tags

DatabaseMetadata.getColumns

A tabela a seguir descreve como um modelo LookML se relaciona com as estruturas de banco de dados na resposta do método de interface DatabaseMetaData.getColumns. A resposta inclui metadados JDBC padrão e específicos do Looker:

Coluna de resposta getColumns 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 o código de tipo SQL 16 (BOOLEAN).
ORDINAL_POSITION O ordinal baseado em 1 do campo em "Explore" (combinando 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ão se o campo fizer parte de um grupo. Se o campo não fizer parte de um grupo de dimensão, será nulo.
DRILL_FIELDS Lista de campos de detalhamento definidos para a dimensão ou medida, se houver
FIELD_ALIAS Alias do campo, se houver.
FIELD_CATEGORY Se o campo é dimension ou measure.
FIELD_DESCRIPTION Campo description
FIELD_GROUP_VARIANT Se o campo aparecer em um marcador de grupo, o FIELD_GROUP_VARIANT especificará o nome mais curto do campo que aparece abaixo do marcador.
FIELD_LABEL Campo label
FIELD_NAME Nome da dimensão ou medida
HIDDEN Se o campo está oculto no seletor em "Explores" (TRUE) ou se ele está visível no seletor em "Explores" (FALSE).
LOOKER_TYPE Tipo de campo do LookML para a dimensão ou a medida
REQUIRES_REFRESH_ON_SORT Define 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 (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 do valor do campo
VIEW_LABEL Ver marcador do campo
VIEW_NAME Nome da visualização em que o campo está definido no projeto do LookML

Identificar consultas de Open SQL Interface na IU do Looker

Os administradores do Looker podem usar a interface do Looker para identificar quais consultas foram originadas da Interface Open SQL:

  • Na página de administração Queries, as consultas da Open SQL Interface têm um valor Source de "SQL Interface". O valor User vai mostrar o nome do usuário do Looker que executou a consulta.

  • Em Análise do histórico de atividades do sistema, as consultas da Open SQL Interface têm o valor "sql_interface" em Source. O valor User Email mostrará o endereço de e-mail do usuário do Looker que executou a consulta. Acesse diretamente a Análise do Histórico filtrada por "sql_interface" inserindo o endereço da instância do Looker no início deste URL:

    https://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Feedback para Interface SQL aberta

Entre em contato com looker-sql-interface@google.com se tiver dúvidas ou solicitações de recursos para o Open SQL Interface.