derived_analytic_model

Utilizzo

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}
Gerarchia
view
derived_analytic_model
Valore predefinito
Nessuno

Regole speciali
I modelli analitici sono supportati solo per le connessioni BigQuery e Snowflake.

Definizione

Per le connessioni BigQuery e Snowflake, il parametro derived_analytic_model definisce un modello analitico in-database (un grafico BigQuery o una visualizzazione semantica in Snowflake) gestito da Looker. In questo scenario, Looker genera il modello analitico all'interno del database eseguendo le istruzioni SQL Data Definition Language (DDL) appropriate specificate nella definizione del parametro LookML derived_analytic_model. La sintassi SQL definita nel parametro derived_analytic_model deve essere supportata dal database.

A differenza delle tabelle derivate, gli oggetti del modello analitico gestito da Looker non conservano i dati all'interno del database e non vengono aggiornati in modo incrementale. Rappresentano invece modelli semantici che definiscono relazioni e misure direttamente nel database.

Per definire un modello analitico, utilizza uno dei seguenti sottoparametri del parametro derived_analytic_model:

Inoltre, se definisci la visualizzazione analitica con il sottoparametro sql, puoi utilizzare il sottoparametro publish_as_db_analytic_model del parametro derived_analytic_model per creare un modello analitico stabile su cui è possibile eseguire query al di fuori di Looker.

Dopo aver definito il modello analitico all'interno del parametro derived_analytic_model, puoi definire dimensioni e misure LookML che vengono mappate al modello analitico. Per alcuni esempi, consulta la sezione Esempi.

sql

Utilizza il parametro sql se vuoi fornire il codice SQL solo per la definizione del modello analitico e lasciare che Looker gestisca la creazione del modello analitico. Quando utilizzi il sottoparametro sql, non includere un'istruzione CREATE o CREATE OR REPLACE, perché Looker genererà automaticamente l'istruzione DDL per creare il modello analitico sul lato database.

Consulta la sezione Creazione di un modello analitico derivato con sql per un esempio di utilizzo del parametro sql per creare un modello analitico nel tuo database.

sql_create

Utilizza il parametro sql_create per definire un'istruzione SQL completa per creare un modello analitico. Quando utilizzi il parametro sql_create, devi includere un'istruzione CREATE OR REPLACE (o un'istruzione CREATE, se il dialetto non supporta CREATE OR REPLACE).

Tieni presente quanto segue quando utilizzi il sottoparametro sql_create:

  • Per le connessioni BigQuery, utilizza un'istruzione CREATE OR REPLACE per creare il modello analitico.
  • Utilizza ${SQL_TABLE_NAME} per sostituire il nome calcolato del modello analitico in fase di creazione. In questo modo, l'istruzione SQL includerà correttamente il nome del modello analitico che fornisci nel parametro LookML view.

Consulta la sezione Creazione di un modello analitico derivato con sql_create per un esempio di utilizzo del parametro sql_create per creare un modello analitico nel tuo database.

create_process

Utilizza il parametro create_process quando devi definire più istruzioni SQL sequenziali per definire il modello analitico. Nel parametro create_process, utilizza il sottoparametro sql_step per specificare le singole istruzioni SQL. Il database eseguirà le istruzioni sql_step una alla volta, nell'ordine in cui le hai specificate. Looker esegue le istruzioni SQL nei sottoparametri sql_step così come le definisci, senza wrapper, il che significa che devi includere un passaggio con un'istruzione CREATE OR REPLACE (o un'istruzione CREATE, se il dialetto non supporta CREATE OR REPLACE).

Consulta la sezione Creazione di un modello analitico derivato con create_process per un esempio di utilizzo del parametro create_process per creare un modello analitico nel tuo database.

publish_as_db_analytic_model

Per i modelli analitici derivati creati con il parametro sql, puoi definire il modello analitico derivato con publish_as_db_analytic_model: yes per chiedere a Looker di creare un modello analitico stabile su cui è possibile eseguire query al di fuori di Looker.

Il modello analitico stabile verrà pubblicato (creato) nel ciclo successivo del rigeneratore Looker dopo che il LookML del modello analitico derivato è stato implementato in produzione con publish_as_db_analytic_model: yes.

Consulta la sezione Accesso al modello analitico stabile per informazioni su come ottenere il nome del modello analitico stabile in modo da poterlo utilizzare per eseguire query al di fuori di Looker.

Creare dimensioni e misure LookML in base alla visualizzazione analitica

Dopo aver definito il modello analitico, nello stesso file di vista puoi definire le dimensioni e le misure LookML basate sul modello analitico.

Consulta la documentazione del tuo dialetto per informazioni sulla sintassi corretta da utilizzare per definire il modello analitico e per fare riferimento agli elementi del modello analitico. Ad esempio, per creare una dimensione LookML da un'entità BigQuery Graph, devi utilizzare i trattini bassi per separare gli elementi durante la definizione dell'ambito. Ad esempio, per BigQuery Graph, questa dimensione LookML si basa sulla proprietà location_id nella tabella dei nodi Stores:

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

Tuttavia, per creare una dimensione LookML basata su una vista semantica Snowflake, devi utilizzare il nome non qualificato di una metrica o di una dimensione.

Esempi

Le sezioni seguenti forniscono esempi di creazione di una visualizzazione analitica utilizzando i diversi sottoparametri di derived_analytic_model:

Creazione di un modello analitico derivato con sql

Di seguito è riportato un esempio di file di visualizzazione LookML che definisce un modello analitico basato su SQL per un database BigQuery utilizzando il parametro secondario sql di derived_analytic_model. Looker creerà il modello analitico all'interno del database eseguendo i comandi DDL SQL forniti nel parametro sql.

Nell'esempio, nota quanto segue:

  • Il sottoparametro sql contiene solo la definizione del modello analitico stesso. Non è presente alcuna istruzione CREATE perché con sql Looker gestisce automaticamente i comandi CREATE per il modello analitico.
  • Il modello analitico è definito con publish_as_db_analytic_model: yes, quindi Looker creerà un modello analitico stabile su cui è possibile eseguire query al di fuori di 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 ;; 
  }
}

Creazione di un modello analitico derivato con create_process

Di seguito è riportato un esempio di file di vista LookML che definisce un modello analitico basato su SQL per un database BigQuery utilizzando il sottoparametro create_process di derived_analytic_model. In questo esempio, devi definire più istruzioni SQL sequenziali per definire il modello analitico. Il primo passaggio elimina il modello analitico, se esiste già, mentre il secondo passaggio lo crea.

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

Creazione di un modello analitico derivato con sql_create

Di seguito è riportato un esempio di file di vista LookML che definisce un modello analitico basato su SQL per un database BigQuery utilizzando il sottoparametro sql_create di derived_analytic_model. In questo esempio, il parametro sql_create definisce l'intera istruzione CREATE OR REPLACE da eseguire per creare il modello analitico in un unico passaggio.

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

  ...

}

Accesso al modello analitico stabile

Se hai creato il modello analitico derivato utilizzando il sottoparametro sql e hai incluso l'istruzione publish_as_db_analytic_model: yes nel parametro derived_analytic_model, Looker pubblicherà (creerà) il modello analitico stabile nel ciclo successivo del rigeneratore di Looker dopo che il LookML del modello analitico derivato è stato implementato in produzione con publish_as_db_analytic_model: yes.

Quando il modello analitico stabile viene pubblicato, puoi interrogarlo direttamente utilizzando il suo nome stabile. Puoi determinare il nome stabile dalle informazioni incluse nella scheda SQL della sezione Dati di una query di esplorazione del modello analitico. Per ottenere il nome stabile di un modello analitico:

  1. Apri Esplora per la visualizzazione del modello analitico.

  2. In Esplora, seleziona le dimensioni o le misure dal selettore campi.

  3. Fai clic sulla scheda SQL della sezione Dati.

  4. Nella scheda SQL, individua una delle seguenti istruzioni SQL:

    • Per BigQuery Graph:
      • CREATE PROPERTY GRAPH
      • SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
    • Per la visualizzazione semantica di Snowflake:
      • CREATE SEMANTIC VIEW
      • SELECT ... FROM SEMANTIC_VIEW_NAME

  5. Il nome stabile è una vista che Looker crea nello schema temporaneo e che punta alla tabella offuscata effettiva mostrata nella scheda SQL. Per ottenere il nome stabile della visualizzazione analitica, compila le seguenti informazioni dall'istruzione SQL:

    SCRATCH_SCHEMA_NAME.CONNECTION_REGISTRATION_KEY_MODEL_NAME_VIEW_NAME
    • SCRATCH_SCHEMA_NAME: il nome dello schema temporaneo è l'inizio della stringa dopo l'istruzione CREATE o SELECT, prima di "."
    • CONNECTION_REGISTRATION_KEY: la chiave di registrazione della connessione è composta da due caratteri; a seconda del dialetto del database, seguirà un segno del dollaro o il primo trattino basso nel nome della tabella nell'istruzione CREATE o SELECT.
    • MODEL_NAME: il nome del modello LookML.
    • VIEW_NAME: il nome della vista in cui è definito il modello analitico.

Ad esempio, ecco il testo della scheda SQL di una query di esplorazione per una connessione BigQuery. Il modello analitico è definito nella vista denominata sales_analytic_model e il nome del modello LookML è thelook. In questo caso, Looker ha già creato il modello analitico, quindi non è presente alcuna istruzione CREATE. Tuttavia, l'istruzione SELECT ... FROM GRAPH_EXPAND contiene le informazioni sul nome della tabella:

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

Di seguito sono riportati i valori necessari per derivare il nome stabile del modello analitico:

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

Pertanto, il nome stabile del modello analitico è il seguente:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Una volta ottenuto il nome stabile del modello analitico, puoi eseguire query direttamente sul modello.

Aspetti da considerare

Quando utilizzi modelli di analisi in-database, tieni presente le seguenti considerazioni e limitazioni:

  • Tipi di dati:con i modelli analitici sono supportati solo i seguenti tipi di dati per dimensioni e metriche:

    • Supportato per dimensioni e misure:
      • string
      • number
      • date
      • yesno
    • Supportato solo per le dimensioni:
      • time
      • date_time

  • Misure:

    • Le misure di base devono essere predefinite:le misure di base devono essere predefinite nel modello analitico del database sottostante. Looker non può definire una nuova misura di base eseguendo un'aggregazione (ad esempio type: sum o type: count) su una dimensione di un modello analitico.

    • Sono supportate le misure basate su altre misure:puoi utilizzare il parametro sql di una misura LookML per eseguire calcoli non aggregati che utilizzano misure di base predefinite del modello analitico. Quando crei una misura basata su altre misure, non puoi definirla come tipo di misura aggregata, ad esempio sum o count. Devi definire la nuova metrica come tipo di metrica non aggregata, ad esempio string, number, date o yesno. Vedi il seguente esempio:

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

  • Join:un'esplorazione la cui visualizzazione di base si basa su un modello analitico non può includere join. Allo stesso modo, una vista basata su un modello analitico non può essere unita a un'esplorazione con una vista di base LookML standard.

  • Join impliciti:le funzionalità che si basano su join impliciti non sono supportate per i modelli analitici. Alcuni esempi di funzionalità che si basano sui join impliciti sono i calendari personalizzati e i campi definiti con type: location, type: distance o type: zipcode.

  • Le seguenti funzionalità non sono supportate con i modelli analitici: