Joins em views de métricas
Esta página descreve como usar joins em views de métricas para enriquecer seus dados de origem com atributos de tabelas relacionadas.
As junções em visualizações de métricas suportam junções diretas de uma tabela de fatos para tabelas de dimensões (esquema estrela), junções de múltiplos saltos entre tabelas de dimensões normalizadas (esquema floco de neve) e junções um-para-muitos que agregam fatos de tabelas relacionadas. Por padrão, todas as junções são de muitos para um, o que significa que cada linha de origem corresponde a no máximo uma linha na tabela unida.
Joins de esquema em estrela
Em um esquema em estrela, o source é a tabela de fatos e se une a uma ou mais tabelas de dimensão usando um LEFT OUTER JOIN join. As views de métricas join as tabelas de fatos e dimensões necessárias para a consulta específica, com base nos campos e medidas selecionados.
Especifique as colunas de join usando uma cláusula on (expressão Booleana) ou uma cláusula using (nomes de coluna compartilhados). O join deve seguir uma relação muitos-para-um. Em casos de muitos-para-muitos, a primeira linha correspondente da tabela de dimensão combinada é selecionada.
O exemplo a seguir join a tabela de fatos orders à tabela de dimensões customer com uma cláusula on, que usa uma expressão Boolean.
version: 1.1
source: samples.tpch.orders
joins:
# The on clause supports a Boolean expression
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
fields:
# Field referencing a join column using dot notation
- name: Customer name
expr: customer.c_name
- name: Customer market segment
expr: customer.c_mktsegment
measures:
# Measure referencing a join column
- name: Total revenue
expr: SUM(o_totalprice)
- name: Order count
expr: COUNT(1)
Quando as colunas de junção tiverem o mesmo nome em ambas as tabelas, use uma cláusula using em vez de uma cláusula on . A cláusula using aceita uma matriz de nomes de colunas que existem tanto na tabela de origem quanto na tabela unida. Nenhum dataset no catálogo samples possui tabelas que compartilham o nome de uma coluna de join, portanto, o exemplo a seguir usa nomes de tabela e coluna provisórios para ilustrar a sintaxe:
joins:
- name: customer
source: catalog.schema.customer
using:
- customer_id
Em uma cláusula on, source refere-se à tabela de origem da view de métricas e a join name refere-se a colunas da tabela unida. Por exemplo, source.o_custkey = customer.c_custkey une a coluna o_custkey da tabela de origem à coluna c_custkey da tabela customer. Se nenhum prefixo for fornecido, a referência, por padrão, é a tabela unida.
Junções de esquema Snowflake
Um esquema em floco de neve estende um esquema em estrela normalizando as tabelas de dimensão e conectando-as a subdimensões. Isso cria uma estrutura de join de vários níveis.
Para definir um esquema de floco de neve:
- Criar view de métricas.
- Adicionar joins de primeiro nível (esquema em estrela).
- join com outras tabelas de dimensões.
- Para expor atributos aninhados, adicione campos na sua view.
O exemplo a seguir utiliza o dataset TPC-H para ilustrar um esquema em floco de neve que mostra a hierarquia geográfica de pedidos. O exemplo une a tabela de pedidos aos clientes, em seguida, às suas nações (países) e, finalmente, às suas regiões (continentes). O dataset TPC-H está disponível no catálogo samples no seu workspace do Databricks.
source: samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
joins:
- name: nation
source: samples.tpch.nation
on: customer.c_nationkey = nation.n_nationkey
joins:
- name: region
source: samples.tpch.region
on: nation.n_regionkey = region.r_regionkey
fields:
- name: clerk
expr: o_clerk
- name: customer
expr: customer
comment: returns the full customer row as a struct
- name: customer_name
expr: customer.c_name
- name: nation
expr: customer.nation
- name: nation_name
expr: customer.nation.n_name
Join cardinality
O campo cardinality em um join controla a relação entre a tabela de origem e a tabela unida. Este campo determina como o mecanismo processa as medidas que fazem referência a colunas da tabela combinada.
A tabela a seguir compara as duas cardinalidades suportadas:
Propriedade |
|
|
|---|---|---|
Linhas correspondidas por linha de origem | No máximo um | Zero ou mais |
Uso típico | Pesquisa de dimensão | Expansão de fatos |
Permitido em | Sim | Não |
Permitido em | Sim | Sim |
Junções muitos-para-um
Muitos para um é a cardinalidade default. Cada linha na origem corresponde a, no máximo, uma linha na tabela associada, portanto, a tabela associada atua como uma pesquisa de dimensão. Você pode omitir o campo cardinality para joins de muitos para um, ou especificar cardinality: many_to_one explicitamente.
Campos e medidas podem referenciar colunas de um join muitos para um usando notação de ponto (por exemplo, customer.c_name).
Declarar restrições de join com rely
A configuração rely.at_most_one_match: true declara que a join não tem ramificação no lado "um":
- Em uma join muitos-para-um, cada linha de origem corresponde a, no máximo, uma linha na tabela associada.
- Em um join um-para-muitos, cada linha associada corresponde a, no máximo, uma linha de origem.
Esta declaração permite ao mecanismo ignorar junções desnecessárias e reduzir os dados analisados, especialmente para queries que filtram em campos da tabela unida. O Databricks recomenda definir rely em ambas as cardinalidades quando a restrição é válida.
Defina at_most_one_match: true somente quando o relacionamento for genuíno. Esta propriedade não é validada em tempo de execução. Se o lado declarado como único produzir um fan-out, as medidas retornarão resultados incorretos.
O exemplo a seguir associa orders a customer com rely ativado:
version: 1.1
source: samples.tpch.orders
joins:
- name: customer
source: samples.tpch.customer
on: source.o_custkey = customer.c_custkey
rely:
at_most_one_match: true
fields:
- name: Customer name
expr: customer.c_name
- name: Customer market segment
expr: customer.c_mktsegment
measures:
- name: Total revenue
expr: SUM(o_totalprice)
- name: Order count
expr: COUNT(1)
Consulte Otimize joins com rely para a referência completa do campo rely.
One-to-many join
Defina cardinality: one_to_many para permitir que uma única linha de origem corresponda a múltiplas linhas na tabela combinada. Isso transforma aquela tabela em uma fonte de fatos que o mecanismo agrega independentemente na granularidade da origem.
Joins de um para muitos exigem o Databricks Runtime 18.1 ou superior e a versão 1.1 da especificação YAML. Consulte Disponibilidade do recurso de view de métricas.
Um join um-para-muitos permite que uma única view de métricas meça fatos que residem em diferentes níveis, como pedidos por cliente ou eventos por account, sem duplicar as linhas de origem nos resultados da consulta. A origem atua como a espinha dimensional: cada entidade aparece exatamente uma vez, independentemente de quantas linhas correspondentes existam na tabela combinada.
Exemplo de join um para muitos
O exemplo a seguir usa customer como a origem e faz o join de orders com cardinality: one_to_many. Um many_to_one join para nation fornece o campo nation_name. Qualifique o lado de origem de cada condição de join com source. para que a referência se resolva para a tabela de origem da view de métricas. Ambos os joins definem rely.at_most_one_match: true: no join nation, afirma que cada cliente tem no máximo uma nação, e no join orders, afirma que cada pedido pertence a no máximo um cliente. Consulte Declarar restrições de join com rely.
version: 1.1
source: samples.tpch.customer
joins:
- name: nation
source: samples.tpch.nation
on: nation.n_nationkey = source.c_nationkey
rely:
at_most_one_match: true
- name: orders
source: samples.tpch.orders
on: orders.o_custkey = source.c_custkey
cardinality: one_to_many
rely:
at_most_one_match: true
fields:
- name: customer_name
expr: c_name
- name: nation_name
expr: nation.n_name
measures:
- name: customer_count
expr: count(*)
- name: order_count
expr: count(orders.o_orderkey)
- name: total_order_revenue
expr: sum(orders.o_totalprice)
Nesta view, customer_count contabiliza as linhas na tabela de origem customer, enquanto order_count e total_order_revenue agregam as linhas do branch orders. Um cliente com dois pedidos retorna um order_count de 2 enquanto customer_count permanece 1, o que confirma que as linhas de origem não são duplicadas. Um cliente sem pedidos ainda aparece nos resultados, com um order_count de 0 e um NULL total_order_revenue.
Junções aninhadas de um para muitos
Para medir fatos que estão dois ou mais níveis abaixo da fonte, aninhe junções um-para-muitos. Todas as junções em uma subárvore um-para-muitos devem compartilhar a mesma cardinalidade, portanto, um pai um-para-muitos não pode ter um filho muitos-para-um. Referencie uma coluna em um join aninhado com seu caminho completo de pontos, por meio dos nomes dos joins.
O exemplo a seguir aninha lineitem sob orders para que uma única view de granularidade de cliente possa contar tanto pedidos quanto itens de linha:
version: 1.1
source: samples.tpch.customer
joins:
- name: orders
source: samples.tpch.orders
on: orders.o_custkey = source.c_custkey
cardinality: one_to_many
joins:
- name: lineitem
source: samples.tpch.lineitem
on: lineitem.l_orderkey = orders.o_orderkey
cardinality: one_to_many
fields:
- name: customer_name
expr: c_name
measures:
- name: order_count
expr: count(distinct orders.o_orderkey)
- name: line_item_count
expr: count(orders.lineitem.l_linenumber)
- name: total_line_revenue
expr: sum(orders.lineitem.l_extendedprice)
As medidas referenciam colunas aninhadas com seu caminho de pontos completo através dos nomes de join, como orders.lineitem.l_extendedprice, porque lineitem é acessível somente através de orders. Utilize count(distinct orders.o_orderkey) em vez de um count simples para a contagem de pedidos: cada pedido se desdobra em vários itens de linha, portanto, uma contagem simples contaria um pedido uma vez por item de linha.
Sibling one-to-many join
Defina múltiplas junções um-para-muitos no mesmo nível para medir fontes de fatos independentes a partir de uma única view. Joins paralelos são agregados separadamente e depois mesclados, assim suas linhas nunca se multiplicam cruzadamente. Elementos irmãos de nível superior podem misturar cardinalidades livremente, portanto, um many_to_one join de dimensão e um one_to_many join de fato podem coexistir no mesmo nível.
O exemplo a seguir usa nation como a origem e adiciona dois ramos independentes de um-para-muitos, customer e supplier:
version: 1.1
source: samples.tpch.nation
joins:
- name: customer
source: samples.tpch.customer
on: customer.c_nationkey = source.n_nationkey
cardinality: one_to_many
- name: supplier
source: samples.tpch.supplier
on: supplier.s_nationkey = source.n_nationkey
cardinality: one_to_many
fields:
- name: nation_name
expr: n_name
measures:
- name: customer_count
expr: count(customer.c_custkey)
- name: supplier_count
expr: count(supplier.s_suppkey)
- name: customers_per_supplier
expr: count(customer.c_custkey) / count(supplier.s_suppkey)
A medida customers_per_supplier divide duas agregações independentes após o mecanismo mesclar cada uma ao grão da query. É possível combinar medidas de diferentes fontes com aritmética, mas uma única função de agregação deve referenciar colunas de apenas uma fonte.
Conectar múltiplas tabelas de fatos com uma tabela de ponte
Uma view de métricas modela uma única tabela de fatos associada a tabelas de dimensões. Para combinar medidas de duas ou mais tabelas de fatos que estão em diferentes granularidades, defina uma ponte que enumera as combinações válidas das dimensões que os fatos compartilham, diretamente na source da view de métricas. Por exemplo, o samples.tpch fato de remessa lineitem (granularidade: linha de pedido) e o fato de suprimento partsupp (granularidade: peça e fornecedor) compartilham as dimensões de peça e fornecedor.
Uma ponte torna explícito o conjunto de combinações de dimensões válidas, para que os resultados das queries permaneçam previsíveis. A view de métricas retorna apenas as combinações declaradas como válidas, em vez de inferi-las para cada consulta. Defina cardinality: one_to_many em cada join de fato para que o mecanismo agregue cada fato independentemente contra a ponte compartilhada, sem espalhar e contar em dobro.
Para construir a ponte, defina-a como uma consulta SQL na view de métrica source, una cada tabela de fatos a ela por suas colunas compartilhadas e, depois, declare campos nas colunas de dimensão compartilhadas e medidas em cada fato. Use um CROSS JOIN quando cada combinação das dimensões compartilhadas for válida:
version: 1.1
source: SELECT * FROM samples.tpch.part CROSS JOIN samples.tpch.supplier
filter: s_suppkey IN (11315, 42920) AND p_partkey IN (30419, 80418)
joins:
- name: lineitem
source: samples.tpch.lineitem
on: source.p_partkey = lineitem.l_partkey AND source.s_suppkey = lineitem.l_suppkey
cardinality: one_to_many
- name: partsupp
source: samples.tpch.partsupp
on: source.p_partkey = partsupp.ps_partkey AND source.s_suppkey = partsupp.ps_suppkey
cardinality: one_to_many
fields:
- name: part_name
expr: p_name
- name: part_brand
expr: p_brand
- name: part_type
expr: p_type
- name: part_size
expr: p_size
- name: manufacturer
expr: p_mfgr
- name: supplier_name
expr: s_name
measures:
- name: lineitem_count
expr: COUNT(lineitem.*)
- name: total_quantity_sold
expr: SUM(lineitem.l_quantity)
- name: gross_revenue
expr: SUM(lineitem.l_extendedprice)
- name: net_revenue
expr: SUM(lineitem.l_extendedprice * (1 - lineitem.l_discount))
- name: distinct_orders
expr: COUNT(DISTINCT lineitem.l_orderkey)
- name: available_quantity
expr: SUM(partsupp.ps_availqty)
- name: avg_supply_cost
expr: AVG(partsupp.ps_supplycost)
- name: total_supply_value
expr: SUM(partsupp.ps_availqty * partsupp.ps_supplycost)
Uma medida sobre uma tabela de fatos contabiliza apenas os registros cujos valores de dimensão compartilhada aparecem na ponte. Combinações que a ponte não inclui não contribuem para os resultados.
Quando se deseja apenas as combinações que de fato ocorrem, troque o source por um UNION (ou FULL OUTER JOIN) dos pares distintos de cada fato para que cada fato contribua com seus membros únicos. Os joins, fields e measures permanecem os mesmos:
source: |
SELECT DISTINCT l_partkey AS p_partkey, l_suppkey AS s_suppkey FROM samples.tpch.lineitem
UNION
SELECT DISTINCT ps_partkey AS p_partkey, ps_suppkey AS s_suppkey FROM samples.tpch.partsupp
Restrições de join um para muitos
- Campos não podem fazer referência a um one-to-many join : um campo deve resultar em exatamente um valor por linha de origem. Como uma coluna um-para-muitos pode ter múltiplos valores por linha de origem, ela não pode ser usada em uma definição de
fields. Para usar tal coluna como um campo, torne essa tabela a fonte e junte a fonte original como umamany_to_onejoin em vez disso. - Uma única agregação não pode abranger fontes : Cada função de agregação deve referenciar colunas de uma única fonte. A aritmética entre os resultados de duas agregações é permitida, como
count(orders.o_orderkey) / count(*), mas uma única função não pode combinar colunas de duas fontes. - Uma subárvore de junção não pode misturar cardinalidades : Todos os descendentes de uma junção de um para muitos devem ser também de um para muitos, e todos os descendentes de uma junção de muitos para um devem ser de muitos para um. Apenas irmãos de nível superior podem misturar cardinalidades.