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 analíticos solo son compatibles con las conexiones de BigQuery y Snowflake.

Definición

En el caso de las conexiones de BigQuery y Snowflake, el parámetro derived_analytic_model define un modelo analítico integrado en la base de datos (un gráfico de BigQuery o una vista semántica en Snowflake) que Looker administra. En este caso, Looker genera el modelo analítico dentro de tu base de datos ejecutando las sentencias SQL lenguaje de definición de datos (DDL) adecuadas que especificas en la definición del parámetro derived_analytic_model de LookML. La sintaxis de SQL que defines en el parámetro derived_analytic_model debe ser compatible con tu base de datos.

A diferencia de las tablas derivadas, los objetos del modelo analítico administrado por Looker no conservan ningún dato en la base de datos y no se actualizan de forma incremental. En cambio, representan modelos semánticos que definen relaciones y medidas directamente en la base de datos.

Para definir un modelo analítico, usa uno de los siguientes parámetros secundarios del parámetro derived_analytic_model:

Además, si defines tu vista analítica 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 analítico estable que se pueda consultar fuera de Looker.

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

sql

Usa el parámetro sql si deseas proporcionar el código SQL solo para la definición del modelo analítico y que Looker administre la creación del modelo analítico. Cuando uses el parámetro secundario 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 analítico en el lado de la base de datos.

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

sql_create

Usa el parámetro sql_create para definir una instrucción de SQL completa que permita crear un modelo analítico. 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 parámetro secundario sql_create:

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

Consulta Cómo crear un modelo analítico derivado con sql_create para ver un ejemplo del uso del parámetro sql_create para crear un modelo analítico 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 analítico. 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 una a la vez, en el orden en que las especificaste. Looker emite las instrucciones SQL en los subparámetros sql_step a medida que los defines, sin ningún 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 analítico derivado con create_process para ver un ejemplo del uso del parámetro create_process para crear un modelo analítico en tu base de datos.

publish_as_db_analytic_model

En el caso de los modelos analíticos derivados que se crean con el parámetro sql, puedes definir tu modelo analítico derivado con publish_as_db_analytic_model: yes para solicitar a Looker que cree un modelo analítico estable al que se pueda consultar fuera de Looker.

El modelo analítico estable se publicará (creará) en el próximo ciclo del regenerador de Looker después de que el LookML del modelo analítico derivado se implemente en producción con publish_as_db_analytic_model: yes.

Consulta la sección Cómo acceder al modelo analítico estable para obtener información sobre cómo obtener el nombre del modelo analítico estable y, así, poder usarlo para consultar el modelo analítico estable fuera de Looker.

Crea dimensiones y medidas de LookML basadas en tu vista analítica

Después de definir tu modelo analítico, en el mismo archivo de vista, puedes definir dimensiones y mediciones de LookML que se basen en el modelo analítico.

Consulta la documentación de tu dialecto para obtener información sobre la sintaxis adecuada que debes usar para definir tu modelo analítico y para hacer referencia a los elementos de tu modelo analítico. 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 analítica con los diferentes subparámetros de derived_analytic_model:

Cómo crear un modelo analítico derivado con sql

A continuación, se muestra un ejemplo de un archivo de vistas de LookML que define un modelo analítico basado en SQL para una base de datos de BigQuery con el subparámetro sql de derived_analytic_model. Looker creará el modelo analítico dentro de la base de datos ejecutando 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 analítico en sí. No hay ninguna instrucción CREATE, ya que, con sql, Looker controla automáticamente los comandos CREATE para el modelo analítico.
  • El modelo analítico se define con publish_as_db_analytic_model: yes, por lo que Looker creará un modelo analítico estable que se puede 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 analítico derivado con create_process

A continuación, se muestra un ejemplo de un archivo de vistas de LookML que define un modelo analítico 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 analítico. El primer paso descarta el modelo analítico si ya existe, y el segundo paso crea el 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 ;;
  }
  
  ...
}

Cómo crear un modelo analítico derivado con sql_create

A continuación, se muestra un ejemplo de un archivo de vistas de LookML que define un modelo analítico 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 analítico 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 analítico estable

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

Cuando se publique el modelo analítico 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 Explorar del modelo analítico. Sigue estos pasos para obtener el nombre estable de un modelo analítico:

  1. Abre Explorar para ver el modelo analítico.

  2. En Explorar, selecciona cualquier dimensión o métrica 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, haz lo siguiente:
      • 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 temporal y que apunta a la tabla ofuscada real que se muestra en la pestaña SQL. Para obtener el nombre estable de la vista analítica, 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 que sigue a la instrucción CREATE o SELECT, antes del ".".
    • CONNECTION_REGISTRATION_KEY: La clave de registro de la conexión tiene dos caracteres. Según el dialecto de la 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: Es el nombre del modelo de LookML.
    • VIEW_NAME: Es el nombre de la vista en la que se define el modelo analítico.

Por ejemplo, aquí se muestra el texto de la pestaña SQL de una consulta de Explorar para una conexión de BigQuery. El modelo analítico 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 analítico, por lo que no hay ninguna declaració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 analítico:

  • 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 analítico es el siguiente:

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Una vez que tengas el nombre estable del modelo analítico, puedes consultarlo directamente.

Aspectos para tener en cuenta

Cuando uses modelos analíticos integrados en la base de datos, ten en cuenta las siguientes consideraciones y limitaciones:

  • Tipos de datos: Solo se admiten los siguientes tipos de datos para las dimensiones y las métricas con los modelos analíticos:

    • Se admiten las siguientes dimensiones y medidas:
      • string
      • number
      • date
      • yesno
    • Solo se admite para las dimensiones:
      • time
      • date_time

  • Mediciones:

    • Las medidas básicas deben estar predefinidas: Las medidas básicas deben estar predefinidas en el modelo analítico de la base de datos subyacente. Looker no puede definir una nueva medida base realizando una agregación (como type: sum o type: count) en una dimensión de un modelo analítico.

    • Se admiten las medidas basadas en otras medidas: Puedes usar el parámetro sql de una medida de LookML para realizar cálculos no agregados que usen medidas básicas predefinidas del modelo analítico. Cuando creas una medida basada en otras medidas, no puedes definir la nueva medida como un tipo de medida agregada, como sum o count. Debes definir la nueva métrica como un tipo de métrica 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 analítico no puede incluir ninguna unión. Del mismo modo, una vista basada en un modelo analítico 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 analíticos. Algunos ejemplos de funciones que se basan en uniones implícitas son los calendarios personalizados y los campos que se definen con type: location, type: distance o type: zipcode.

  • Las siguientes funciones no son compatibles con los modelos analíticos: