derived_analytic_model

Uso

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}
Hierarquia
view
derived_analytic_model
Valor padrão
Nenhum

Regras especiais
Os modelos analíticos são compatíveis apenas com conexões do BigQuery e do Snowflake.

Definição

Para conexões do BigQuery e do Snowflake, o parâmetro derived_analytic_model define um modelo analítico no banco de dados (um gráfico do BigQuery ou uma visualização semântica no Snowflake) gerenciado pelo Looker. Nesse cenário, o Looker gera o modelo analítico no banco de dados executando as instruções SQL linguagem de definição de dados (DDL) apropriadas especificadas na definição do parâmetro LookML derived_analytic_model. A sintaxe SQL definida no parâmetro derived_analytic_model precisa ser compatível com o banco de dados.

Ao contrário das tabelas derivadas, os objetos de modelo analítico gerenciados pelo Looker não persistem dados no banco de dados e não são atualizados de forma incremental. Em vez disso, eles representam modelos semânticos que definem relações e medições diretamente no banco de dados.

Para definir um modelo analítico, use um dos seguintes subparâmetros do parâmetro derived_analytic_model:

Além disso, se você definir a visualização analítica com o subparâmetro sql, use o subparâmetro publish_as_db_analytic_model do parâmetro derived_analytic_model para criar um modelo analítico estável que pode ser consultado fora do Looker.

Depois de definir o modelo analítico dentro do parâmetro derived_analytic_model, você pode definir dimensões e medições do LookML que são mapeadas para o modelo analítico. Consulte a seção Exemplos para ver exemplos.

sql

Use o parâmetro sql se quiser fornecer o SQL apenas para a definição do modelo analítico e fazer com que o Looker gerencie a criação do modelo analítico. Ao usar o subparâmetro sql, não inclua uma instrução CREATE ou CREATE OR REPLACE, porque o Looker vai gerar automaticamente a instrução DDL para criar o modelo analítico no lado do banco de dados.

Consulte Criar um modelo analítico derivado com sql para ver um exemplo de como usar o parâmetro sql para criar um modelo analítico no banco de dados.

sql_create

Use o parâmetro sql_create para definir uma instrução SQL completa para criar um modelo analítico. Ao usar o parâmetro sql_create, é necessário incluir uma instrução CREATE OR REPLACE (ou uma instrução CREATE, se o dialeto não oferecer suporte a CREATE OR REPLACE).

Observe o seguinte ao usar o subparâmetro sql_create:

  • Para conexões do BigQuery, use uma instrução CREATE OR REPLACE para criar o modelo analítico.
  • Use ${SQL_TABLE_NAME} para substituir o nome calculado do modelo analítico que está sendo criado. Isso garante que a instrução SQL inclua corretamente o nome do modelo analítico fornecido no parâmetro view do LookML.

Consulte Criar um modelo analítico derivado com sql_create para ver um exemplo de como usar o parâmetro sql_create para criar um modelo analítico no banco de dados.

create_process

Use o parâmetro create_process quando precisar definir várias instruções SQL sequenciais para definir o modelo analítico. No parâmetro create_process, use o subparâmetro sql_step para especificar as instruções SQL individuais. O banco de dados vai executar as instruções sql_step uma de cada vez, na ordem especificada. O Looker emite as instruções SQL nos subparâmetros sql_step conforme você as define, sem wrapper, o que significa que é necessário incluir uma etapa com uma instrução CREATE OR REPLACE (ou uma instrução CREATE, se o dialeto não oferecer suporte a CREATE OR REPLACE).

Consulte Criar um modelo analítico derivado com create_process para ver um exemplo de como usar o parâmetro create_process para criar um modelo analítico no banco de dados.

publish_as_db_analytic_model

Para modelos analíticos derivados criados com o parâmetro sql, você pode definir o modelo analítico derivado com publish_as_db_analytic_model: yes para solicitar que o Looker crie um modelo analítico estável que possa ser consultado fora do Looker.

O modelo analítico estável será publicado (criado) no próximo ciclo do regenerador do Looker depois que o LookML do modelo analítico derivado for implantado na produção com publish_as_db_analytic_model: yes.

Consulte a seção Acessar o modelo analítico estável para saber como receber o nome do modelo analítico estável para que você possa usar o nome para consultar o modelo analítico estável fora do Looker.

Criar dimensões e medições do LookML com base na visualização analítica

Depois de definir o modelo analítico, no mesmo arquivo de visualização, você pode definir dimensões e medições do LookML com base no modelo analítico.

Consulte a documentação do dialeto para saber mais sobre a sintaxe adequada a ser usada para definir o modelo analítico e para se referir a elementos no modelo analítico. Por exemplo, para criar uma dimensão do LookML de uma entidade do gráfico do BigQuery, é necessário usar sublinhados para separar elementos ao definir o escopo. Por exemplo, para o gráfico do BigQuery, essa dimensão do LookML é baseada na propriedade location_id na tabela de nós Stores:

  dimension: location_id {
    type: number
    sql: Stores_location_id ;;
  }

No entanto, para criar uma dimensão do LookML com base em uma visualização semântica do Snowflake, é necessário usar o nome não qualificado de uma métrica ou dimensão.

Exemplos

As seções a seguir fornecem exemplos de como criar uma visualização analítica usando os diferentes subparâmetros de derived_analytic_model:

Criar um modelo analítico derivado com sql

A seguir, há um exemplo de arquivo de visualização do LookML que define um modelo analítico baseado em SQL para um banco de dados do BigQuery usando o subparâmetro sql de derived_analytic_model. O Looker vai criar o modelo analítico no banco de dados executando os comandos SQL DDL fornecidos no parâmetro sql.

Observe o seguinte no exemplo:

  • O subparâmetro sql contém apenas a definição do modelo analítico. Não há instrução CREATE, porque, com sql, o Looker processa automaticamente os comandos CREATE para o modelo analítico.
  • O modelo analítico é definido com publish_as_db_analytic_model: yes, então o Looker vai criar um modelo analítico estável que pode ser consultado fora do Looker.
view: MyWarehouseOrdersView {
  derived_analytic_model: {
    publish_as_db_analytic_model: yes

    # Defining the analytic model
    
    sql:
      NODE TABLES (
        Customers
        KEY(customer_id)
        PROPERTIES(
          country_code,
          concat(first_name, ' ', last_name) AS name,
          age,
          MEASURE(AVG(age)) AS AvgAge 
        ),
      Orders
        KEY(order_id)
        PROPERTIES (
          customer_id,
          employee_id,
          date,
          discount,
          MEASURE(AVG(discount)) AS AvgDiscount
        )

      EDGE TABLES (
        -- Relationship: Orders -> Customers
        looker_test.orders AS orders_to_users
          KEY(id)
          SOURCE KEY (order_id) REFERENCES orders (order_id)
          DESTINATION KEY (customer_id) REFERENCES Customers (customer_id)
          NO PROPERTIES
      ) ;;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: customer_id {
    type: number
    sql: Customers_customer_id ;;
  }

  dimension: customer_age {
    type: number
    sql: Customers_age ;;
  }

  measure: orders_avg_discount {
    type: number
    sql: Orders_AvgDiscount ;; 
  }
}

Criar um modelo analítico derivado com create_process

A seguir, há um exemplo de arquivo de visualização do LookML que define um modelo analítico baseado em SQL para um banco de dados do BigQuery usando o subparâmetro create_process de derived_analytic_model. Neste exemplo, é necessário definir várias instruções SQL sequenciais para definir o modelo analítico. A primeira etapa descarta o modelo analítico se ele já existir, e a segunda etapa cria o modelo analítico.

view: university_statistics {
  derived_analytic_model: {
    create_process: {
      sql_step: 
        DROP PROPERTY GRAPH IF EXISTS ${SQL_TABLE_NAME} ;;
      sql_step: 
        CREATE PROPERTY GRAPH ${SQL_TABLE_NAME}
         NODE TABLES (
          university.College
            KEY(college_id)
            PROPERTIES(college_id, college_name),
          university.Department
            KEY(dept_id)
            PROPERTIES(dept_id, dept_name, college_id,
              budget OPTIONS(description="Department budget in USD"),
              MEASURE(SUM(budget)) AS total_budget),
          university.Course
            KEY(course_id)
            PROPERTIES(
              course_id,
              course_name,
              credits,
              dept_id,
              MEASURE(AVG(credits)) AS avg_credits,
              MEASURE(SUM(credits)) AS total_credits,
              MEASURE(COUNT(course_id)) AS course_count)
        )
        EDGE TABLES (
          university.Department AS CollegeDept
            SOURCE KEY (college_id) REFERENCES College (college_id)
            DESTINATION KEY (dept_id) REFERENCES Department (dept_id),
          university.Course AS DeptCourse
            SOURCE KEY (dept_id) REFERENCES Department (dept_id)
            DESTINATION KEY (course_id) REFERENCES Course (course_id)
        );;
    }
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: college_id {
    type: number
    sql: College_college_id ;;
  }

  dimension: course_name {
    type: string
    sql: Course_course_name ;;
  }
  
  ...
}

Criar um modelo analítico derivado com sql_create

A seguir, há um exemplo de arquivo de visualização do LookML que define um modelo analítico baseado em SQL para um banco de dados do BigQuery usando o subparâmetro sql_create de derived_analytic_model. Neste exemplo, o parâmetro sql_create define a instrução CREATE OR REPLACE completa a ser executada para criar o modelo analítico em uma única etapa.

view: MyWarehouseOrdersView {
  derived_analytic_model: {
    sql_create:
      CREATE OR REPLACE PROPERTY GRAPH ${SQL_TABLE_NAME}
        NODE TABLES(
          accounting.Loan AS Loan
            KEY(loanId)
            LABEL Loan PROPERTIES(
              loanId,
              loanAmount,
              balance,
              createTime,
              interestRate,
              accountId,
              balance + 100 AS derived_balance,
              CASE WHEN balance > 1000 THEN "High" ELSE "Low" END AS risk_level,
              CONCAT("ID-", CAST(loanId AS STRING)) AS full_id,
              DATE(2024, 1, 1) AS fixed_date,
              MEASURE(AVG(interestRate)) AS avg_interest_rate
            ),
          accounting.AccountView AS Account
            KEY(accountId)
            LABEL Account PROPERTIES(
              accountId,
              createTime,
              isBlocked,
              accountType,
              amount,
              ownerId,
              MEASURE(MIN(createTime)) AS oldest_account_create_time,
              MEASURE(MAX(createTime)) AS newest_account_create_time,
              MEASURE(AVG(amount)) AS avg_account_amount,
              MEASURE(SUM(amount)) AS total_account_amount,
              MEASURE(COUNT(DISTINCT accountType)) AS account_type_count
            ),
          accounting.PersonMV AS Person
            KEY(personId)
            LABEL Person PROPERTIES(
              personId,
              personName,
              age,
              age_tier,
              MEASURE(AVG(age)) AS avg_age,
              MEASURE(COUNT(DISTINCT age_tier)) AS age_tier_count
            )
        )
        EDGE TABLES(
          accounting.Loan AS Account_Repay_Loan
            KEY(loanId)
            SOURCE KEY(loanId) REFERENCES Loan(loanId)
            DESTINATION KEY(accountId) REFERENCES Account(accountId)
            LABEL Repay NO PROPERTIES,
          accounting.Account AS Person_Own_Account
            KEY(accountId)
            SOURCE KEY(accountId) REFERENCES Account(accountId)
            DESTINATION KEY(ownerId) REFERENCES Person(personId)
            LABEL Own NO PROPERTIES
        );;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: loan_id {
    type: number
    sql: Loan_loanId ;;
  }

  dimension: account_ID {
    type: number
    sql: Account_accountID ;;
  }

  ...

}

Acessar o modelo analítico estável

Se você criou o modelo analítico derivado usando o subparâmetro sql e incluiu a instrução publish_as_db_analytic_model: yes no parâmetro derived_analytic_model, o Looker vai publicar (criar) o modelo analítico estável no próximo ciclo do regenerador do Looker depois que o LookML do modelo analítico derivado for implantado na produção com publish_as_db_analytic_model: yes.

Quando o modelo analítico estável é publicado, é possível consultá-lo diretamente usando o nome estável. Há duas maneiras de receber o nome estável de um modelo analítico:

Modal de detalhes da PDT

Se você for um admin ou um usuário com a permissão see_pdts, siga estas etapas para usar a página Tabelas derivadas persistentes na seção Admin do Looker para receber o nome do modelo analítico estável de um modelo analítico:

  1. Clique no ícone do menu principal do Looker e selecione Admin, se o menu Admin ainda não estiver aberto. Se você estiver na seção Análise ou Desenvolver do menu principal do Looker, talvez seja necessário clicar na seta para voltar e acessar o menu Admin.
  2. No menu Admin, selecione Tabelas derivadas persistentes.
  3. Na página Tabelas derivadas persistentes, pesquise o nome do modelo analítico.
  4. Clique no menu de três pontos do modelo analítico e selecione Detalhes da PDT.
  5. No modal Detalhes da PDT, procure o campo Nome estável.

Para consultar o modelo analítico estável diretamente, adicione o nome do esquema de rascunho antes do nome do modelo. Por exemplo, se o nome do esquema de rascunho for tmp, você poderá consultar o modelo analítico estável com um comando como este:

SELECT * from tmp.PA_bq_analytic_model_sales_semantic_view

Guia SQL de uma análise

Se você não tiver acesso à página de admin Tabelas derivadas persistentes, poderá determinar o nome estável das informações incluídas na guia SQL na seção Dados de uma consulta de análise do modelo analítico. Siga estas etapas para receber o nome estável de um modelo analítico:

  1. Abra a análise da visualização do modelo analítico.

  2. Na análise, selecione qualquer dimensão ou medição no seletor de campos.

  3. Clique na guia SQL da seção Dados.

  4. Na guia SQL, localize uma das seguintes instruções SQL:

    • Para o gráfico do BigQuery:
      • CREATE PROPERTY GRAPH
      • SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
    • Para a visualização semântica do Snowflake:
      • CREATE SEMANTIC VIEW
      • SELECT ... FROM SEMANTIC_VIEW_NAME

  5. O nome estável é uma visualização que o Looker cria no esquema de rascunho, que aponta para a tabela ofuscada real mostrada na guia SQL. Para receber o nome estável da visualização analítica, preencha as seguintes informações da instrução SQL:

    SCRATCH_SCHEMA_NAME.CONNECTION_REGISTRATION_KEY_MODEL_NAME_VIEW_NAME
    • SCRATCH_SCHEMA_NAME: o nome do esquema de rascunho é o início da string após a instrução CREATE ou SELECT, antes do "."
    • CONNECTION_REGISTRATION_KEY: a chave de registro de conexão tem dois caracteres. Dependendo do dialeto do banco de dados, ela vai seguir um cifrão ou o primeiro sublinhado no nome da tabela na instrução CREATE ou SELECT.
    • MODEL_NAME: o nome do modelo do LookML.
    • VIEW_NAME: o nome da visualização em que o modelo analítico é definido.

Por exemplo, aqui está o texto da guia SQL de uma consulta de análise para uma conexão do BigQuery. O modelo analítico é definido na visualização chamada sales_analytic_model, e o nome do modelo do LookML é thelook. Nesse caso, o Looker já criou o modelo analítico, então não há instrução CREATE. Mas a instrução SELECT ... FROM GRAPH_EXPAND contém as informações do nome da tabela:

-- use existing sales_analytic_model in `looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model`
SELECT
    sales_analytic_model.orders_id  AS sales_analytic_model_orders_id,
    AGG(sales_analytic_model.orders_count_orders ) AS sales_analytic_model_count_orders
FROM GRAPH_EXPAND("looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model") AS sales_analytic_model
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

Estes são os valores necessários para derivar o nome estável do modelo analítico:

  • SCRATCH_SCHEMA_NAME é looker-test-db.looker_scratch
  • CONNECTION_REGISTRATION_KEY é J7
  • MODEL_NAME é thelook
  • VIEW_NAME é sales_analytic_model

Portanto, o nome estável do modelo analítico é o seguinte:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Depois de receber o nome estável do modelo analítico, você pode consultar o modelo analítico diretamente.

Informações importantes

Ao usar modelos analíticos no banco de dados, lembre-se das seguintes considerações e limitações:

  • Tipos de dados: apenas os seguintes tipos de dados para dimensões e medições são compatíveis com modelos analíticos:

    • Compatível com dimensões e medições:
      • string
      • number
      • date
      • yesno
    • Compatível apenas com dimensões:
      • time
      • date_time

  • Medições :

    • As medições básicas precisam ser predefinidas:as medições básicas precisam ser predefinidas no modelo analítico do banco de dados subjacente. O Looker não pode definir uma nova medição básica executando uma agregação (como type: sum ou type: count) em uma dimensão de um modelo analítico.

    • As medições baseadas em outras medições são compatíveis:é possível usar o parâmetro sql de uma medição do LookML para executar cálculos não agregados que usam medições básicas predefinidas do modelo analítico. Ao criar uma medição baseada em outras medições, não é possível definir a nova medição como um tipo de medição agregada, como sum ou count. É necessário definir a nova medição como um tipo de medição não agregada, como string, number, date ou yesno. Confira o exemplo a seguir:

      measure: average_order_amount {
  type: number
  sql: ROUND(${total_order_amount} / NULLIF(${count_orders}, 0), 2) ;;
}

  • Mesclas:uma análise cuja visualização básica é baseada em um modelo analítico não pode incluir mesclas. Da mesma forma, uma visualização baseada em um modelo analítico não pode ser mesclada em uma análise que tenha uma visualização básica padrão do LookML.

  • Mesclas implícitas:os recursos que dependem de mesclas implícitas não são compatíveis com modelos analíticos. Alguns exemplos de recursos que dependem de mesclas implícitas são calendários personalizados e campos definidos com type: location, type: distance ou type: zipcode.

  • Os seguintes recursos não são compatíveis com modelos analíticos: