Interface SQL aberta

A camada de modelagem semântica 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 da LookML oferecem reutilização de código e integração com o 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 de autoatendimento.

O modelo LookML é a base de todos os dados solicitados ao Looker, seja pela interface de visualização do Looker na interface do usuário do Looker, uma visualização incorporada no portal da sua empresa ou outro aplicativo de terceiros, ou um aplicativo personalizado desenvolvido com a API do Looker. A interface Open SQL oferece acesso aos modelos da LookML para qualquer aplicativo de terceiros que seja compatível com a conectividade de banco de dados Java (JDBC). Os aplicativos podem se conectar a um modelo do LookML como se fosse um banco de dados. Assim, os usuários aproveitam todo o trabalho feito pelos analistas de dados no modelo do LookML, usando as ferramentas com que têm mais familiaridade.

Como a interface Open SQL mostra elementos do projeto LookML

Para entender como a interface 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 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 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 junção entre elas. A interface Open SQL mostra as análises detalhadas como tabelas de banco de dados.
  • Uma visualização define uma coleção de campos (dimensões e métricas). 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 as dimensões ou métricas personalizadas que os usuários finais podem 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 de banco de dados. Por exemplo, a dimensão id na visualização order_items é apresentada pela interface Open SQL como uma coluna de banco de dados chamada order_items.id.

Uma Análise do Looker pode definir relações de junção 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 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 se referir 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 une uma visualização chamada customer com uma chamada product, e ambas tiverem uma dimensão id, você se referirá aos dois campos id como `customer.id` e `product.id`, respectivamente. Para usar o nome totalmente qualificado com o nome da Análise, consulte os 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 saber onde colocar os acentos graves ao se referir a identificadores de banco de dados.

Como configurar a interface Open SQL

Para usar a interface Open SQL, siga estas etapas:

  1. Verifique se os requisitos foram atendidos.
  2. Faça o download do arquivo de 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:

Baixar o driver JDBC da interface 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 autenticar na interface Open SQL

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

OAuth

Os clientes JDBC que oferecem suporte ao OAuth podem ser configurados para usar o servidor OAuth de uma instância do Looker. Siga as etapas para configurar a autenticação OAuth:

  1. Use a extensão da API Explorer para registrar o cliente OAuth do JDBC com sua instância do Looker. Assim, ela poderá reconhecer solicitações do OAuth. Consulte Como registrar um aplicativo cliente OAuth para instruções.
  2. Faça login no Looker com o OAuth para solicitar um token de acesso. Consulte Como fazer login do usuário usando o OAuth para ver um exemplo.
  3. Use um objeto Properties para transmitir as credenciais do OAuth ao abrir a conexão JDBC com a interface Open SQL.

Confira 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 ao driver JDBC da interface Open SQL:

  1. Gere chaves de API para seu usuário do Looker, conforme descrito na página Configurações de 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 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\

  3. 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 com a interface Open SQL.

Chaves de API

Também é possível usar chaves de API para autenticar em vez de um nome de usuário e senha. As chaves de API são consideradas menos seguras do que o OAuth e podem estar disponíveis apenas durante a prévia da interface Open SQL. Consulte Chaves de API para informações sobre como criar chaves de API para sua instância do Looker.

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

Executar consultas com a interface Open SQL

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

Limitações do SQL

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

Use crase em identificadores de banco de dados

Ao enviar consultas para a interface Open SQL, use crases em torno dos identificadores de esquema, tabela e coluna. Veja como especificar elementos de banco de dados usando crases com termos do Looker:

  • schema: `<model_name>`.
  • table: `<explore_name>`

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

Confira um exemplo de formato de instrução SELECT usando esses 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 compatíveis.

Com a interface Open SQL, é necessário designar todas as medidas da LookML incluídas em uma consulta envolvendo a medida (incluindo crases) na função especial AGGREGATE(). Por exemplo, use isso para especificar a métrica count da visualização orders:

AGGREGATE(`orders.count`)

É necessário incluir as medidas do LookML na função AGGREGATE(), seja na cláusula SELECT, HAVING ou ORDER BY.

Se você não tiver certeza se um campo é uma métrica 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 medidas 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 SQL aberta é compatível com parâmetros e campos somente para filtros.

Ao executar consultas com a interface Open SQL, é 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 filtros, 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 ver exemplos de como usar parâmetros e campos somente para filtro com a interface Open SQL.

Exemplo de parâmetro

Como exemplo de uso de um parameter com a interface Open SQL, se a visualização customers tivesse 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"
  }
}

É possível enviar esta consulta à interface 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 SQL vai transmitir esse valor de parâmetro para a consulta no Looker, e o Looker 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 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 filtros 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 ;;
    }

É 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 SQL vai aplicar a expressão de 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 always_filter ou conditionally_filter em uma cláusula WHERE ou HAVING

A interface Open SQL pode ser compatível com uma análise detalhada que tenha always_filter ou conditionally_filter, mas não os dois.

Se você definiu a análise do LookML com always_filter ou conditionally_filter, transmita os valores dos campos de filtro na sua consulta SQL para a interface do SQL aberto:

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

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, da seguinte forma: 

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

Na sua consulta à interface Open SQL, use 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 métrica ao seu 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 métricas especificadas no parâmetro always_filter, a consulta vai retornar um erro. O mesmo vale para dimensões e medidas especificadas em um parâmetro conditionally_filter, exceto que você pode definir um parâmetro conditionally_filter com um subparâmetro unless, assim:

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

Nesse caso, transmita um valor de filtro para cada uma das dimensões e métricas 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 como usar o subparâmetro unless.

Por exemplo, qualquer uma das seguintes consultas à interface Open SQL seria aceita. 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 usando dimensões e métricas. Essa consulta recupera as dimensões estado e cidade da visualização clientes e a métrica valor total da visualização pedidos. As duas visualizações são unidas na análise detalhada pedidos do modelo 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;

Como acessar metadados do banco de dados

A interface Open SQL é compatível com um subconjunto da interface DatabaseMetaData padrão do JDBC, que é usada para receber 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 LookML:

A interface SQL retorna resultados apenas para modelos, análises detalhadas e campos a que você tem acesso.

DatabaseMetadata.getSchemas

A tabela a seguir descreve como um modelo da LookML se relaciona com as estruturas de banco de dados padrão na resposta do método de 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 da 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 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 detalhada do LookML
TABLE_TYPE Sempre retorna 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, SYNONYM.
SELF_REFERENCING_COL_NAME (null)
REF_GENERATION (null)
Metadados específicos do Looker
DESCRIPTION Confira a descrição
LABEL Conheça o rótulo
TAGS Conheça as tags
CONDITIONALLY_FILTER_UNLESS A lista de campos no subparâmetro unless do parâmetro conditionally_filter da Análise detalhada. Se não houver campos especificados no subparâmetro unless ou se não houver um parâmetro conditionally_filter definido para a análise detalhada, esse valor será nulo.

DatabaseMetadata.getColumns

A tabela a seguir descreve como um modelo da 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 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 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).
TYPE_NAME String que representa o tipo de dados da coluna. Para um tipo definido pelo usuário (UDT), o nome do tipo é totalmente qualificado.
COLUMN_SIZE Número inteiro que representa o número máximo de caracteres 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 do decimal, para tipos de dados aplicáveis, ou o número de dígitos fracionários. Nulo é retornado para tipos de dados em que DECIMAL_DIGITS não é aplicável.
NUM_PREC_RADIX Número inteiro que representa a base ou raiz (normalmente 10 ou 2) dos dados.
NULLABLE

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

  • 0: columnNoNulls - talvez não permita valores NULL
  • 1: columnNullable - permite valores NULL
  • 2: columnNullableUnknown - anulabilidade desconhecida
REMARKS (null)
COLUMN_DEF (null)
SQL_DATA_TYPE (null)
SQL_DATETIME_SUB (null)
CHAR_OCTET_LENGTH Para tipos de dados de caracteres, um número inteiro que representa o número máximo de bytes na coluna.
ORDINAL_POSITION O ordinal de base 1 do campo na Análise (misturando dimensões e medidas em ordem alfabética por nome da visualização e, em seguida, nome do campo)
IS_NULLABLE Sempre retorna 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, esse valor 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 é um dimension ou um measure
FIELD_DESCRIPTION Campo description
FIELD_GROUP_VARIANT Se o campo for apresentado em um rótulo do grupo, o FIELD_GROUP_VARIANT vai especificar o nome mais curto do campo que aparece no rótulo do grupo.
FIELD_LABEL Campo label
FIELD_NAME Nome da dimensão ou métrica
LOOKER_TYPE Tipo de campo do LookML para a dimensão ou medida
REQUIRES_REFRESH_ON_SORT Se a consulta SQL precisa ser atualizada para reordenar os valores do campo (TRUE) ou se os valores do campo podem ser reordenados sem precisar de uma atualização da consulta SQL (FALSE).
SORTABLE Se o campo pode ser classificado (TRUE) ou não (FALSE)
TAGS Campo tags
USE_STRICT_VALUE_FORMAT Se o campo usa formato de valor estrito (TRUE) ou não (FALSE)
VALUE_FORMAT String formato do 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
HIDDEN Se o campo está oculto do seletor de campo nas Análises (TRUE) ou se ele está visível no seletor de campo nas Análises (FALSE).
ALWAYS_FILTER O valor padrão do parâmetro always_filter definido no campo. Se o campo não fizer parte de um parâmetro always_filter, esse valor será nulo.
CONDITIONALLY_FILTER O valor padrão do parâmetro conditionally_filter definido no campo. Se o campo não fizer parte de um parâmetro conditionally_filter, esse valor será nulo.

Identificar consultas da interface Open SQL na interface 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 Consultas, as consultas da interface SQL aberta têm um valor Origem de "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 acessar mais informações sobre ela. Na caixa de diálogo Detalhes, clique em Consulta da interface SQL para ver a consulta SQL enviada ao Looker pela interface SQL aberta.

  • Na Análise detalhada do histórico de atividades do sistema, as consultas da interface SQL aberta têm um valor Origem de "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. Acesse diretamente a análise detalhada do Histórico 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 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/