Junções em views de métricas
Junções em views de métricas enriquecem seus dados de origem com atributos de tabelas relacionadas. Eles suportam joins diretos de uma tabela de fatos para tabelas de dimensão (esquema estrela), joins multi-hop em tabelas de dimensão normalizadas (esquema floco de neve) e joins um-para-muitos que agregam fatos de tabelas relacionadas. Por default, todas as junções são muitos-para-um, portanto, cada linha de origem corresponde a no máximo uma linha na tabela associada.
Joins de Esquema em Estrela
Em um esquema em estrela, o(a) source é a tabela de fatos e une-se a uma ou mais tabelas de dimensões usando um(a) LEFT OUTER JOIN. As views de métricas join as tabelas de fatos e de dimensões necessárias para a query específica, com base nos campos e medidas selecionados.
Especifique as colunas de join usando uma cláusula on (expressão Boolean) ou uma cláusula using (nomes de coluna compartilhados). O join deve seguir um relacionamento de muitos para um. Em casos de muitos para muitos, o mecanismo seleciona a primeira linha correspondente da tabela de dimensões associada.
O exemplo a seguir une a tabela de fatos orders à tabela de dimensões customer com uma cláusula on, que aceita 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 join tiverem o mesmo nome em ambas as tabelas, use uma cláusula using em vez de uma cláusula on. A cláusula using usa uma matriz de nomes de coluna que existem tanto na tabela de origem quanto na tabela associada. Nenhum dataset no catálogo samples tem tabelas que compartilham um nome de coluna de join, então o exemplo a seguir usa nomes de tabela e coluna de placeholder 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 o join name refere-se às colunas da tabela que faz a junção. Por exemplo, source.o_custkey = customer.c_custkey faz a junção da coluna o_custkey da tabela de origem à coluna c_custkey da tabela customer. Se nenhum prefixo for fornecido, a referência é por default para a tabela que fez a junção.
Joins de esquema Snowflake
Um esquema Snowflake estende um esquema em estrela ao normalizar tabelas de dimensão e conectá-las a subdimensões. Isso cria uma estrutura de join multinível.
Para definir um esquema em floco de neve:
- Crie uma view de métricas.
- Adicionar joins de primeiro nível (esquema estrela).
- Join com outras tabelas de dimensão.
- Disponibilize atributos aninhados adicionando campos na sua view.
O exemplo a seguir usa o dataset TPC-H para ilustrar um esquema snowflake que mostra a hierarquia geográfica de pedidos. O exemplo une a tabela de pedidos aos clientes, depois às suas nações (países ou regiões) 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
Cardinalidade de join
O campo cardinality em um join controla o relacionamento 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 unida.
A tabela a seguir compara as duas cardinalidades suportadas:
Propriedade |
|
|
|---|---|---|
Linhas correspondentes por linha de origem | No máximo um | Zero ou mais |
Uso típico | Pesquisa de dimensão | Expansão de Fato |
Permitido em | Sim | Não |
Permitido em | Sim | Sim |
Joins muitos para um
Muitos-para-um é a cardinalidade default. Cada linha na origem corresponde a no máximo uma linha na tabela unida, portanto, a tabela unida atua como uma pesquisa de dimensão. É possível omitir o campo cardinality para junções muitos-para-um, ou especificar cardinality: many_to_one explicitamente.
Tanto campos quanto medidas podem referenciar colunas de um join muitos para um usando notação de ponto (por exemplo, customer.c_name).
Declare restrições de join com rely
A definição rely.at_most_one_match: true declara que o join não tem expansão no lado "um":
- Em um join muitos-para-um, cada linha de origem corresponde a, no máximo, uma linha na tabela combinada.
- Em um join um-para-muitos, cada linha combinada corresponde a, no máximo, uma linha de origem.
Essa declaração permite que o motor ignore joins desnecessárias e reduza os dados escaneados, especialmente para queries que filtram em campos da tabela unida. A 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 verdadeiramente válido. Esta propriedade não é validada em runtime. Se o lado asserido produzir um fan-out, as medidas retornarão resultados incorretos.
O exemplo a seguir associa orders a customer com rely habilitado:
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 Otimizar joins com rely para a referência completa do campo rely.
Junções um-para-muitos
Defina cardinality: one_to_many para permitir que uma única linha de origem corresponda a várias linhas na tabela associada. Isso transforma essa tabela em uma fonte de fato que o mecanismo agrega independentemente no grão de origem.
Junções um-para-muitos exigem Databricks Runtime 18.1 ou acima 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 granularidades, como pedidos por cliente ou eventos por account, sem duplicar as linhas de origem nos resultados da query. A origem atua como a espinha dimensional: cada entidade aparece exatamente uma vez, independentemente de quantas linhas correspondentes existam na tabela unida.
Exemplo de join um-para-muitos
O exemplo a seguir usa customer como a origem e une orders com cardinality: one_to_many. Uma many_to_one join para nation fornece o campo nation_name. Qualifique o lado da 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. Ambas as junções definem rely.at_most_one_match: true: na join nation afirma que cada cliente tem no máximo uma nação, e na 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 conta linhas na tabela de origem customer, enquanto order_count e total_order_revenue agregam linhas da 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 origem, 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 pelos 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 fazem referência a colunas aninhadas com seu caminho de pontos completo por meio dos nomes de join, como orders.lineitem.l_extendedprice, porque lineitem é acessível somente por orders. Use count(distinct orders.o_orderkey) em vez de uma count simples para a contagem de pedidos: cada pedido se desdobra em vários itens de linha, então uma contagem simples contaria um pedido uma vez por item de linha.
Junções um-para-muitos irmãs
Defina múltiplos joins um-para-muitos no mesmo nível para medir fontes de fato independentes de uma única view. O motor agrega os joins irmãos separadamente e depois os combina, de modo que suas linhas nunca se multiplicam. Os irmãos de nível superior podem misturar cardinalidades livremente, de modo que um join de dimensão many_to_one e um join de fato one_to_many podem coexistir no mesmo nível.
O exemplo a seguir usa nation como origem e adiciona duas branches independentes 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 depois que o mecanismo combina cada uma com a granularidade da query. É possível combinar medidas de diferentes fontes com aritmética, mas uma única função de agregação deve fazer referência a colunas de apenas uma fonte.
Conectar várias tabelas de fatos com uma tabela de ponte
Uma view de métricas modela uma única tabela de fatos unida a tabelas de dimensões. Para combinar medidas de duas ou mais tabelas de fatos que estão em grãos diferentes, defina uma ponte que enumere as combinações válidas das dimensões que os fatos compartilham, diretamente no source da view de métricas. Por exemplo, o fato de remessa samples.tpch lineitem (granularidade: linha de pedido) e o fato de suprimento partsupp (granularidade: peça e fornecedor) ambos 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 da query permaneçam previsíveis. A view de métricas retorna apenas as combinações que o usuário declara válidas, em vez de inferi-las para cada query. Defina cardinality: one_to_many em cada join de fato para que o mecanismo agregue cada fato independentemente em relação à ponte compartilhada sem duplicação de dados e dupla contagem.
Para construir a ponte, defina-a como uma query SQL na view de métrica source, faça o join de cada tabela de fatos a ela em suas colunas compartilhadas e, em seguida, declare campos nas colunas de dimensão compartilhadas e medidas em cada fato. Use um(a) 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 conta 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 realmente ocorrem, faça o swap de source por um UNION (ou FULL OUTER JOIN) dos pares distintos de cada fato, de modo que cada fato contribua com seus membros. 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 referenciar um join um-para-muitos : um campo deve ser resolvido para exatamente um valor por linha de origem. Como uma coluna um-para-muitos pode ter vários valores por linha de origem, não é possível usá-la em uma definição
fields. Para usar essa coluna como um campo, faça dessa tabela a origem e faça o join da origem original como um joinmany_to_oneem vez disso. - **Uma única agregação não pode abranger fontes**: Cada função de agregação deve fazer referência a 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 join não pode misturar cardinalidades : Todos os descendentes de um join de um para muitos também devem ser de um para muitos, e todos os descendentes de um join de muitos para um devem ser de muitos para um. Apenas elementos de nível superior podem misturar cardinalidades.