derived_analytic_model

Uso

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}
Jerarquía
view
derived_analytic_model
Valor predeterminado
Ninguno

Reglas especiales
Los modelos de análisis solo son compatibles con las conexiones de BigQuery y Snowflake.

Definición

Para las conexiones de BigQuery y Snowflake, el parámetro derived_analytic_model define un modelo de análisis en la base de datos (un BigQuery Graph o una vista semántica en Snowflake) que Looker administra. En esta situación, Looker genera el modelo de análisis dentro de tu base de datos mediante la ejecución de las instrucciones de lenguaje de definición de datos (DDL) de SQL adecuadas que especificas en la definición del parámetro de LookML derived_analytic_model. Tu base de datos debe admitir la sintaxis de SQL que definas en el parámetro derived_analytic_model.

A diferencia de las tablas derivadas, los objetos de modelo de análisis administrados por Looker no conservan ningún dato dentro de la base de datos y no se actualizan de forma incremental. En cambio, representan modelos semánticos que definen relaciones y mediciones directamente en la base de datos.

Para definir un modelo de análisis, usa uno de los siguientes subparámetros del parámetro derived_analytic_model:

Además, si defines tu vista de análisis con el subparámetro sql, puedes usar el subparámetro publish_as_db_analytic_model del parámetro derived_analytic_model para crear un modelo de análisis estable que se pueda consultar fuera de Looker.

Después de definir el modelo de análisis dentro del parámetro derived_analytic_model, puedes definir dimensiones y mediciones de LookML que se asignen a tu modelo de análisis. Consulta la sección Ejemplos para ver ejemplos.

sql

Usa el parámetro sql si deseas proporcionar el SQL solo para la definición del modelo de análisis y que Looker administre la creación del modelo de análisis. Cuando usas el subparámetro sql, no incluyas una instrucción CREATE ni CREATE OR REPLACE, ya que Looker generará automáticamente la instrucción DDL para crear el modelo de análisis en el lado de la base de datos.

Consulta Cómo crear un modelo de análisis derivado con sql para ver un ejemplo del uso del parámetro sql para crear un modelo de análisis en tu base de datos.

sql_create

Usa el parámetro sql_create para definir una instrucción de SQL completa para crear un modelo de análisis. Cuando usas el parámetro sql_create, debes incluir una instrucción CREATE OR REPLACE (o una instrucción CREATE, si tu dialecto no admite CREATE OR REPLACE).

Ten en cuenta lo siguiente cuando uses el subparámetro sql_create:

  • Para las conexiones de BigQuery, usa una instrucción CREATE OR REPLACE para crear el modelo de análisis.
  • Usa ${SQL_TABLE_NAME} para sustituir el nombre calculado del modelo de análisis que se está creando. Esto garantiza que la instrucción de SQL incluya correctamente el nombre del modelo de análisis que proporcionas en el parámetro view de LookML.

Consulta Cómo crear un modelo de análisis derivado con sql_create para ver un ejemplo del uso del parámetro sql_create para crear un modelo de análisis en tu base de datos.

create_process

Usa el parámetro create_process cuando necesites definir varias instrucciones de SQL secuenciales para definir el modelo de análisis. En el parámetro create_process, usa el subparámetro sql_step para especificar las instrucciones de SQL individuales. Tu base de datos ejecutará las instrucciones sql_step de una en una, en el orden en que las especificaste. Looker emite las instrucciones de SQL en los subparámetros sql_step a medida que las defines, sin wrapper, lo que significa que debes incluir un paso con una instrucción CREATE OR REPLACE (o una instrucción CREATE, si tu dialecto no admite CREATE OR REPLACE).

Consulta Cómo crear un modelo de análisis derivado con create_process para ver un ejemplo del uso del parámetro create_process para crear un modelo de análisis en tu base de datos.

publish_as_db_analytic_model

Para los modelos de análisis derivados que se crean con el parámetro sql, puedes definir tu modelo de análisis derivado con publish_as_db_analytic_model: yes para solicitar a Looker que cree un modelo de análisis estable que se pueda consultar fuera de Looker.

El modelo de análisis estable se publicará (creará) en el siguiente ciclo del regenerador de Looker después de que el LookML del modelo de análisis derivado se implemente en producción con publish_as_db_analytic_model: yes.

Consulta la sección Cómo acceder al modelo de análisis estable para obtener información sobre cómo obtener el nombre del modelo de análisis estable para que puedas usarlo para consultar el modelo de análisis estable fuera de Looker.

Crea dimensiones y mediciones de LookML basadas en tu vista de análisis

Después de definir tu modelo de análisis, en el mismo archivo de vista, puedes definir dimensiones y mediciones de LookML basadas en el modelo de análisis.

Consulta la documentación de tu dialecto para obtener información sobre la sintaxis adecuada que se debe usar para definir tu modelo de análisis y para hacer referencia a los elementos de tu modelo de análisis. Por ejemplo, para crear una dimensión de LookML a partir de una entidad de BigQuery Graph, debes usar guiones bajos para separar los elementos cuando definas el alcance. Por ejemplo, para BigQuery Graph, esta dimensión de LookML se basa en la propiedad location_id de la tabla de nodos Stores:

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

Sin embargo, para crear una dimensión de LookML basada en una vista semántica de Snowflake, debes usar el nombre no calificado de una métrica o dimensión.

Ejemplos

En las siguientes secciones, se proporcionan ejemplos de cómo crear una vista de análisis con los diferentes subparámetros de derived_analytic_model:

Cómo crear un modelo de análisis derivado con sql

El siguiente es un ejemplo de un archivo de vista de LookML que define un modelo de análisis basado en SQL para una base de datos de BigQuery con el subparámetro sql de derived_analytic_model. Looker creará el modelo de análisis dentro de la base de datos mediante la ejecución de los comandos DDL de SQL que se proporcionan en el parámetro sql.

Ten en cuenta lo siguiente en el ejemplo:

  • El subparámetro sql solo contiene la definición del modelo de análisis. No hay ninguna instrucción CREATE, ya que, con sql, Looker controla automáticamente los comandos CREATE para el modelo de análisis.
  • El modelo de análisis se define con publish_as_db_analytic_model: yes, por lo que Looker creará un modelo de análisis estable que se pueda consultar fuera de 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 ;; 
  }
}

Cómo crear un modelo de análisis derivado con create_process

El siguiente es un ejemplo de un archivo de vista de LookML que define un modelo de análisis basado en SQL para una base de datos de BigQuery con el subparámetro create_process de derived_analytic_model. En este ejemplo, debes definir varias instrucciones de SQL secuenciales para definir el modelo de análisis. En el primer paso, se quita el modelo de análisis si ya existe, y en el segundo paso, se crea el modelo de análisis.

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

Cómo crear un modelo de análisis derivado con sql_create

El siguiente es un ejemplo de un archivo de vista de LookML que define un modelo de análisis basado en SQL para una base de datos de BigQuery con el subparámetro sql_create de derived_analytic_model. En este ejemplo, el parámetro sql_create define la instrucción CREATE OR REPLACE completa que se ejecutará para crear el modelo de análisis en un solo paso.

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

  ...

}

Cómo acceder al modelo de análisis estable

Si creaste tu modelo de análisis derivado con el subparámetro sql y, además, incluiste la instrucción publish_as_db_analytic_model: yes en el parámetro derived_analytic_model, Looker publicará (creará) el modelo de análisis estable en el siguiente ciclo del regenerador de Looker después de que el LookML del modelo de análisis derivado se implemente en producción con publish_as_db_analytic_model: yes.

Cuando se publique el modelo de análisis estable, podrás consultarlo directamente con su nombre estable. Puedes determinar el nombre estable a partir de la información que se incluye en la pestaña SQL de la sección Datos de una consulta de exploración del modelo de análisis. Sigue estos pasos para obtener el nombre estable de un modelo de análisis:

  1. Abre la exploración de la vista de tu modelo de análisis.

  2. En la exploración, selecciona cualquier dimensión o medición del selector de campos.

  3. Haz clic en la pestaña SQL de la sección Datos.

  4. En la pestaña SQL, busca una de las siguientes instrucciones de SQL:

    • Para BigQuery Graph:
      • CREATE PROPERTY GRAPH
      • SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
    • Para la vista semántica de Snowflake:
      • CREATE SEMANTIC VIEW
      • SELECT ... FROM SEMANTIC_VIEW_NAME

  5. El nombre estable es una vista que Looker crea en el esquema de borrador, que apunta a la tabla ofuscada real que se muestra en la pestaña SQL. Para obtener el nombre estable de la vista de análisis, completa la siguiente información de la instrucción de SQL:

    SCRATCH_SCHEMA_NAME.CONNECTION_REGISTRATION_KEY_MODEL_NAME_VIEW_NAME
    • SCRATCH_SCHEMA_NAME: El nombre del esquema de borrador es el comienzo de la cadena después de la instrucción CREATE o SELECT, antes de "."
    • CONNECTION_REGISTRATION_KEY: La clave de registro de conexión tiene dos caracteres; según el dialecto de tu base de datos, seguirá un signo de dólar o el primer guion bajo en el nombre de la tabla en la instrucción CREATE o SELECT.
    • MODEL_NAME: El nombre del modelo de LookML.
    • VIEW_NAME: El nombre de la vista en la que se define el modelo de análisis.

Por ejemplo, este es el texto de la pestaña SQL de una consulta de exploración para una conexión de BigQuery. El modelo de análisis se define en la vista llamada sales_analytic_model, y el nombre del modelo de LookML es thelook. En este caso, Looker ya creó el modelo de análisis, por lo que no hay ninguna instrucción CREATE. Sin embargo, la instrucción SELECT ... FROM GRAPH_EXPAND contiene la información del nombre de la tabla:

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

Estos son los valores que necesitas para derivar el nombre estable del modelo de análisis:

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

Por lo tanto, el nombre estable del modelo de análisis es el siguiente:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Una vez que tengas el nombre estable del modelo de análisis, podrás consultarlo directamente.

Aspectos para tener en cuenta

Cuando uses modelos de análisis en la base de datos, ten en cuenta las siguientes consideraciones y limitaciones:

  • Tipos de datos: Los modelos de análisis solo admiten los siguientes tipos de datos para dimensiones y mediciones:

    • Admitidos para dimensiones y mediciones:
      • string
      • number
      • date
      • yesno
    • Admitidos solo para dimensiones:
      • time
      • date_time

  • Mediciones:

    • Las mediciones base deben estar predefinidas: Las mediciones base deben estar predefinidas en el modelo de análisis de la base de datos subyacente. Looker no puede definir una nueva medición base realizando una agregación (como type: sum o type: count) en una dimensión de un modelo de análisis.

    • Se admiten las mediciones basadas en otras mediciones: Puedes usar el parámetro sql de una medición de LookML para realizar cálculos no agregados que usen mediciones base predefinidas del modelo de análisis. Cuando creas una medición basada en otras mediciones, no puedes definir la nueva medición como un tipo de medición agregada, como sum o count. Debes definir la nueva medición como un tipo de medición no agregada, como string, number, date o yesno. Consulta el siguiente ejemplo:

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

  • Uniones: Una exploración cuya vista base se basa en un modelo de análisis no puede incluir ninguna unión. Del mismo modo, una vista basada en un modelo de análisis no se puede unir a una exploración que tenga una vista base de LookML estándar.

  • Uniones implícitas: Las funciones que dependen de uniones implícitas no son compatibles con los modelos de análisis. Algunos ejemplos de funciones que dependen de uniones implícitas son los calendarios personalizados y los campos definidos con type: location, type: distance o type: zipcode.

  • Las siguientes funciones no son compatibles con los modelos de análisis: