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 seu banco de dados executando as instruções SQL Linguagem de definição de dados (DDL) adequadas especificadas na definição do parâmetro derived_analytic_model LookML. A sintaxe SQL definida no parâmetro derived_analytic_model precisa ser compatível com seu banco de dados.

Ao contrário das tabelas derivadas, os objetos de modelo analítico gerenciados pelo Looker não mantêm 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 medidas 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 sua visualização de análise com o subparâmetro sql, poderá usar 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 no parâmetro derived_analytic_model, você pode definir dimensões e métricas do LookML que mapeiam seu 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 deixar que o Looker gerencie a criação dele. 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 Como criar um modelo analítico derivado com sql para ver um exemplo de uso do parâmetro sql para criar um modelo analítico no seu banco de dados.

sql_create

Use o parâmetro sql_create para definir uma instrução SQL completa e criar um modelo analítico. Ao usar o parâmetro sql_create, inclua uma instrução CREATE OR REPLACE (ou CREATE, se o dialeto não for compatível com 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 da LookML.

Consulte Como criar um modelo analítico derivado com sql_create para ver um exemplo de uso do parâmetro sql_create para criar um modelo analítico no seu 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. Seu 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ê os define, sem um wrapper. Isso significa que você precisa incluir uma etapa com uma instrução CREATE OR REPLACE (ou uma instrução CREATE, se o dialeto não for compatível com CREATE OR REPLACE).

Consulte Como criar um modelo analítico derivado com create_process para ver um exemplo de uso do parâmetro create_process para criar um modelo analítico no seu banco de dados.

publish_as_db_analytic_model

Para modelos analíticos derivados criados com o parâmetro sql, é possível 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 a LookML do modelo analítico derivado for implantada na produção com publish_as_db_analytic_model: yes.

Consulte a seção Como acessar o modelo analítico estável para saber como conseguir o nome dele e usá-lo para consultar o modelo fora do Looker.

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

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

Consulte a documentação do seu dialeto para saber mais sobre a sintaxe adequada a ser usada na definição e referência de elementos do modelo analítico. Por exemplo, para criar uma dimensão da LookML com base em uma entidade do BigQuery Graph, use sublinhados para separar os elementos ao definir o escopo. Por exemplo, para o BigQuery Graph, essa dimensão da 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 mostram exemplos de como criar uma visualização analítica usando os diferentes subparâmetros de derived_analytic_model:

Como criar um modelo analítico derivado com sql

Confira a seguir 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á uma instrução CREATE porque, com o 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. Assim, o Looker cria 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 ;; 
  }
}

Como criar um modelo analítico derivado com create_process

Confira a seguir 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, você precisa 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.

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

Como criar um modelo analítico derivado com sql_create

Confira a seguir 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 ;;
  }

  ...

}

Como acessar o modelo analítico estável

Se você criou seu 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 a LookML do modelo analítico derivado for implantada 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. É possível determinar o nome estável com base nas informações incluídas na guia SQL da 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 detalhada da visualização do modelo analítico.

  2. Na análise detalhada, selecione dimensões ou métricas no seletor de campo.

  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 real ofuscada mostrada na guia "SQL". Para receber o nome estável da visualização de análise, 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 de ".".
    • 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 está definido.

Por exemplo, este é o texto da guia SQL de uma consulta do recurso Detalhar 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á uma instrução CREATE. No entanto, 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 é:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Depois de ter o nome estável do modelo analítico, é possível consultá-lo 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:somente os seguintes tipos de dados para dimensões e métricas são compatíveis com modelos analíticos:

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

  • Medidas:

    • As medidas de base precisam ser predefinidas:no modelo analítico do banco de dados subjacente. O Looker não pode definir uma nova medida de base realizando uma agregação (como type: sum ou type: count) em uma dimensão de um modelo analítico.

    • Medidas baseadas em outras medidas são aceitas:use o parâmetro sql de uma medida do LookML para realizar cálculos não agregados que usam medidas básicas predefinidas do modelo analítico. Ao criar uma métrica com base em outras, não é possível definir a nova métrica como um tipo agregado, como sum ou count. Defina a nova métrica como um tipo não agregado, como string, number, date ou yesno. Veja o exemplo a seguir:

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

  • Mesclas:uma Análise própria cuja visualização de base é 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 unida a uma Análise detalhada que tenha uma visualização de base padrão do LookML.

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

  • Os seguintes recursos estão indisponíveis com modelos analíticos: