Looker 3 — Explores & joins

Mở đầu: Explore là "mặt tiền" của mô hình

Ở bài LookML cơ bản chúng ta đã dựng các view — mỗi view mô tả một bảng, với các dimension và measure của nó. Nhưng một view đơn lẻ hiếm khi trả lời được câu hỏi nghiệp vụ thật. "Doanh thu theo quốc gia của khách hàng" cần bảng đơn hàng nối với bảng khách hàng. "Số lượng bán theo danh mục sản phẩm" cần đơn hàng nối với chi tiết đơn và sản phẩm. Nơi ghép những mảnh đó lại và trao cho người dùng cuối để họ tự khám phá, chính là Explore.

Explore là "khung khám phá" mà người phân tích thấy khi mở Looker: một danh sách dimension và measure ở panel trái, kéo thả sang phải để chọn cột, thêm filter, bật pivot — và Looker sinh SQL đẩy xuống warehouse. Người dùng không viết JOIN; JOIN đã được lập trình viên khai báo sẵn trong LookML. Đây là điểm khiến Looker vừa mạnh vừa nguy hiểm: một join sai relationship có thể làm doanh thu bị thổi phồng gấp đôi mà người dùng cuối không hề hay biết.

Quy ước xuyên suốt loạt bài giữ nguyên: đây là công cụ BI, không phải SQL sandbox. Mọi đoạn LookML trong bài chỉ là minh hoạ khái niệm.

Explore là gì và khai báo ở đâu

Một Explore được khai báo trong file model (.model.lkml), không phải trong view. Cấu trúc tối giản gồm: một base view (bảng gốc, còn gọi là from của explore) và các join kéo thêm view khác vào.

# file: ecommerce.model.lkml   (minh hoạ)
connection: "bigquery_prod"

include: "/views/*.view.lkml"

explore: orders {
  label: "Đơn hàng"
  description: "Phân tích đơn hàng, khách hàng và sản phẩm"

  # ... các join sẽ nằm ở đây
}

Tên explore: orders mặc định trỏ tới view orders cùng tên làm base view. Từ base view này, Looker luôn bắt đầu câu FROM, rồi nối các view khác vào bằng LEFT JOIN (mặc định). Người dùng cuối chọn từ menu Explore của model, và chỉ thấy những view đã được join vào — không thấy được toàn bộ warehouse.

Vài tham số hay dùng ở cấp explore:

• label / description: tên và mô tả hiển thị cho người dùng cuối (nên viết bằng ngôn ngữ nghiệp vụ, không phải tên bảng kỹ thuật).
• hidden: yes: ẩn explore khỏi menu (dùng cho explore trung gian, chỉ để extends).
• always_filter / sql_always_where: ràng buộc filter (nói ở phần sau).
• access_filter: lọc theo người dùng, thuộc chủ đề governance.

Join trong LookML: bốn thứ phải nhớ

Mỗi join bên trong explore trả lời bốn câu hỏi: nối view nào, nối bằng điều kiện gì, dùng kiểu SQL join nào, và quan hệ số lượng (cardinality) ra sao.

explore: orders {
  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
}

sql_on — điều kiện nối, viết như mệnh đề ON của SQL nhưng dùng cú pháp tham chiếu ${view.field} của LookML. Đây là cách nối phổ biến và linh hoạt nhất; bạn có thể ghép nhiều điều kiện bằng AND. (Có một cách viết tắt cũ là foreign_key, dùng khoá chính của view được join, nhưng sql_on rõ ràng hơn và được khuyến nghị.)

type — kiểu JOIN theo SQL. Các giá trị: left_outer (mặc định), inner, full_outer, cross. Trong BI, left_outer gần như luôn là lựa chọn đúng: giữ mọi dòng của base view kể cả khi không khớp bên phải, tránh vô tình đánh rơi đơn hàng chỉ vì thiếu bản ghi khách hàng. Chỉ dùng inner khi bạn chủ đích loại các dòng không khớp.

relationship — quan hệ số lượng giữa base (bên trái) và view được join (bên phải), nhìn từ góc độ "một dòng bên trái ứng với bao nhiêu dòng bên phải". Các giá trị: one_to_one, many_to_one (mặc định), one_to_many, many_to_many.

Đây là tham số quan trọng nhất và dễ sai nhất. Theo tài liệu Google Cloud, nếu bạn quên khai báo relationship, Looker mặc định hiểu là many_to_one. Vì sao điều này nguy hiểm đến vậy? Vì relationship quyết định Looker có cần dùng symmetric aggregates hay không — cơ chế bảo vệ measure khỏi bị tính sai. Đặt sai relationship = tính sai số tiền.

Cách xác định relationship cho join: customers ở trên: đứng từ orders, mỗi đơn hàng thuộc về một khách hàng, nhưng một khách có nhiều đơn. Nhìn từ base orders sang customers: nhiều-về-một → many_to_one. Đúng như mặc định. Nhưng khi ta nối orders xuống order_items (một đơn có nhiều dòng chi tiết) thì lại là one_to_many — và đây chính là nơi rắc rối bắt đầu.

Fan-out: khi tổng bị nhân lên

Hãy hình dung điều gì xảy ra ở tầng SQL khi nối orders (một dòng/đơn, có cột total là tổng tiền đơn) với order_items (nhiều dòng/đơn) bằng one_to_many:

Đơn hàng số 1 trị giá 100. Nó có 3 dòng chi tiết. Sau khi LEFT JOIN, đơn hàng số 1 xuất hiện trên 3 dòng kết quả, mỗi dòng mang theo total = 100. Nếu Looker sinh câu SUM(orders.total) ngây thơ, kết quả là 100 × 3 = 300. Sai gấp ba. Hiện tượng bảng bên trái bị "nhân bản dòng" do join tới bảng nhiều-hơn được gọi là fan-out.

Tài liệu Looker minh hoạ đúng vấn đề này: tổng đơn hàng đúng là $124.84, nhưng sau khi join với order_items mà tính SUM thô, con số nhảy lên $223.44 — sai lệch rõ rệt. Đây không phải lỗi của người dùng cuối; họ chỉ kéo thả một measure "Tổng doanh thu". Lỗi nằm ở cách tổng hợp trên dữ liệu đã fan-out.

Symmetric aggregates: vũ khí đặc trưng của Looker

Đây là điểm khác biệt lớn khiến Looker nổi bật. Thay vì bắt lập trình viên phải né mọi join one_to_many (như nhiều công cụ khác buộc phải làm), Looker tự phát hiện fan-out và sinh SQL "đối xứng" để tính đúng SUM và AVG bất kể dòng bị nhân bản. Cơ chế này gọi là symmetric aggregates.

Ý tưởng cốt lõi: để cộng total của mỗi đơn đúng một lần dù đơn đó xuất hiện trên nhiều dòng, Looker cần một cách nhận ra "đây vẫn là đơn số 1 mà tôi đã cộng rồi". Nó làm điều đó bằng khoá chính (primary key) của view: dựa vào primary_key, Looker dùng kỹ thuật kiểu SUM(DISTINCT ...) — cộng gộp theo khoá chính, các dòng trùng khoá chỉ được đếm một lần. Tài liệu Google Cloud diễn đạt: "Nếu nhiều dòng có cùng primary key, hàm symmetric aggregate chỉ đếm chúng một lần."

Về mặt SQL, Looker sinh ra biểu thức khá phức tạp: nó băm (hash) giá trị primary key, CAST() số cần cộng sang kiểu chính xác cao, kết hợp lại để đảm bảo mỗi khoá chỉ góp phần một lần, rồi trừ đi phần trùng. Người dùng cuối không thấy sự phức tạp này — họ chỉ thấy con số đúng. Với ví dụ ở trên, SUM(total) qua symmetric aggregate cho lại đúng 100 (và $124.84 với dữ liệu thật), trong khi vẫn cộng đúng các đại lượng ở cấp item (ví dụ SUM(order_items.quantity)) vì chúng vốn là một-dòng-một-giá-trị.

Hai điều kiện bắt buộc

Symmetric aggregates chỉ hoạt động khi:

1. View có primary_key khai báo đúng — một dimension duy nhất, không trùng, không null. Đây là lý do bài LookML cơ bản nhấn mạnh phải đặt primary_key: yes. Không có primary key, Looker không biết dòng nào là "trùng".
2. relationship khai báo đúng — Looker dựa vào relationship để biết nhánh nào có thể fan-out và cần bảo vệ. Đặt many_to_one cho một join thực chất là one_to_many sẽ khiến Looker không bật symmetric aggregate ở nơi cần, và tổng lại sai.

Giới hạn cần biết

• count_distinct và một số phép đếm distinct qua bảng join thường không được symmetric aggregate xử lý trọn vẹn và có thể cần tính thủ công (ví dụ đưa về derived table đã tổng hợp sẵn). SUM và AVG là nơi symmetric aggregates mạnh nhất.
• Chi phí SQL: biểu thức hashing + CAST làm câu SQL nặng hơn. Điểm hay là Looker tự phát hiện khi không có fan-out thì không dùng cơ chế này — nó chỉ bật khi thực sự có join nhiều-nhánh, nên không phải lo mọi query đều chậm.
• Warehouse phải hỗ trợ: hầu hết warehouse hiện đại (BigQuery, Snowflake, Redshift...) đều chạy tốt. Với những dialect không hỗ trợ, tài liệu cho biết Looker chỉ đưa measure type: count (dưới dạng COUNT DISTINCT) từ view được join vào explore trừ khi relationship là one_to_one; khi đó khuyến nghị tổng hợp trước bằng derived table để đưa về quan hệ one_to_one rồi mới join.

Filter ở cấp explore

Explore có thể áp ràng buộc lọc mặc định, hữu ích để giới hạn phạm vi và chi phí quét:

explore: orders {
  # Người dùng buộc phải chọn khoảng ngày (mặc định 90 ngày gần nhất)
  always_filter: {
    filters: [orders.created_date: "90 days"]
  }

  # Ràng buộc cứng, không hiện ra cho người dùng, luôn được AND vào WHERE
  sql_always_where: ${orders.status} != 'test' ;;

  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
}

Phân biệt hai loại: always_filter đặt filter mặc định nhưng người dùng vẫn chỉnh được (tốt cho hiệu năng, tránh ai đó quét cả 5 năm dữ liệu). sql_always_where là điều kiện cứng, luôn được nối vào WHERE và người dùng không tắt được (tốt để loại dữ liệu rác/test). Ngoài ra còn access_filter để lọc dữ liệu theo danh tính người dùng (mỗi vùng chỉ thấy đơn hàng vùng mình) — đây là công cụ trọng yếu cho phân quyền dữ liệu và sẽ được nói kỹ ở bài governance.

Derived table làm base cho explore

Base view của explore không nhất thiết là một bảng vật lý. Nó có thể là một derived table — một view mà sql_table_name được thay bằng một truy vấn SQL (hoặc một khối derived_table). Đây là lối thoát cho những trường hợp symmetric aggregates không đủ, hoặc khi bạn cần tổng hợp trước để tránh fan-out ngay từ gốc.

Ví dụ: thay vì join orders → order_items rồi phụ thuộc symmetric aggregate để cộng đúng, bạn có thể tạo một derived table order_summary đã GROUP BY order_id sẵn (mỗi đơn một dòng, đã có tổng số lượng và doanh thu), rồi join orders với nó theo one_to_one. Khi đó không còn fan-out để lo. Derived table cũng là nền tảng cho PDT (Persistent Derived Tables) — bảng dẫn xuất được vật chất hoá và lưu trong warehouse để tăng tốc. Chủ đề này được trình bày chi tiết ở bài Derived tables & PDT; ở đây chỉ cần nhớ: explore có thể đứng trên một derived table y như trên một bảng thật.

Nhiều explore từ cùng một view, và extends

Một view có thể là base của nhiều explore khác nhau, mỗi explore trả lời một câu hỏi. Từ view orders, bạn có thể có explore: orders (phân tích vận hành, join nhiều bảng) và explore: orders_finance (chỉ join những gì kế toán cần). Đừng cố nhồi mọi join vào một explore khổng lồ.

Để tránh lặp code, LookML có extends: một explore kế thừa cấu hình của explore khác rồi bổ sung/ghi đè.

explore: orders_base {
  hidden: yes                     # explore nền, không hiện cho người dùng
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
}

explore: orders {
  extends: [orders_base]          # kế thừa join customers ở trên
  label: "Đơn hàng (đầy đủ)"
  join: order_items {
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }
}

orders thừa hưởng join customers từ orders_base và thêm join order_items. Cách này giữ các định nghĩa join dùng chung ở một nơi, giảm sai lệch giữa các explore.

Use case thực tế: explore đơn hàng, tính doanh thu đúng

Giả sử phòng kinh doanh cần một explore trả lời: "Doanh thu và số lượng bán theo quốc gia khách hàng và danh mục sản phẩm, trong quý này." Ta cần ba bảng: orders (đơn), customers (khách, có country), order_items (chi tiết, nối tới products để lấy category).

explore: orders {
  label: "Đơn hàng theo khách & sản phẩm"

  always_filter: {
    filters: [orders.created_date: "this quarter"]
  }

  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }

  join: order_items {
    type: left_outer
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many          # <-- nhánh gây fan-out
  }

  join: products {
    type: left_outer
    sql_on: ${order_items.product_id} = ${products.id} ;;
    relationship: many_to_one
  }
}

Điều tinh tế ở đây: khi người dùng kéo dimension products.category (thuộc nhánh order_items → products) cùng với measure orders.total_revenue (thuộc orders), dữ liệu bị fan-out — mỗi đơn hiện trên nhiều dòng item. Nhưng vì orders có primary_key: id và join order_items khai báo đúng one_to_many, Looker tự bật symmetric aggregate cho orders.total_revenue: tổng doanh thu theo quốc gia vẫn đúng, không bị nhân theo số item.

Đồng thời, measure ở cấp item như order_items.total_quantity = SUM(quantity) vẫn được cộng bình thường (mỗi item một dòng, không có gì để nhân). Người dùng cuối có được một bảng: country × category với doanh thu đúng và số lượng đúng — mà không ai phải viết một dòng SQL nào. Đó là lời hứa của Looker được thực hiện, và symmetric aggregates là thứ khiến nó không sụp đổ khi gặp fan-out.

Ngược lại, nếu lập trình viên lười khai báo và để order_items mặc định many_to_one (sai), Looker sẽ không bảo vệ và total_revenue bị thổi phồng — một lỗi âm thầm, khó phát hiện vì con số vẫn "trông hợp lý". Đây là lý do relationship phải được review kỹ như review code tính tiền.

Best practice

• Một explore = một câu hỏi nghiệp vụ. Đừng nhồi 15 join vào một explore để "ai cần gì cũng có". Explore càng rộng, người dùng càng dễ lạc và SQL sinh ra càng đắt. Tách explore theo chủ đề (vận hành, tài chính, marketing).
• Luôn khai báo relationship một cách có chủ đích. Đừng dựa vào mặc định many_to_one. Với mỗi join, tự hỏi: "một dòng bên trái ứng với mấy dòng bên phải?" và ghi đúng. Đây là dòng LookML đáng review nhất.
• Bảo đảm mọi view có primary_key đúng. Không có nó, symmetric aggregates không hoạt động và tổng sẽ sai âm thầm.
• Ưu tiên left_outer. Chỉ dùng inner/cross khi thực sự có lý do; inner vô tình đánh rơi dòng base là bug phổ biến.
• Hạn chế join thừa. Mỗi join làm câu SQL nặng hơn trên warehouse — và trên BigQuery hay Snowflake, câu SQL nặng nghĩa là tốn tiền và thời gian quét thật. Chỉ join những view mà explore đó cần. Chi tiết tối ưu chi phí ở loạt bài BigQuery.
• Khi symmetric aggregates không đủ (count distinct phức tạp, logic tổng hợp lồng nhau), hãy tổng hợp trước bằng derived table về quan hệ one_to_one rồi mới join.
• Dùng extends để chia sẻ các join dùng chung giữa nhiều explore, giữ định nghĩa nhất quán.

Ghi nhớ

• Explore khai báo trong model = base view + các join; là mặt tiền để người dùng cuối kéo thả, Looker sinh SQL đẩy xuống warehouse.
• Join cần bốn thứ: sql_on (điều kiện), type (mặc định left_outer), relationship (mặc định many_to_one), và primary key ở view.
• relationship quyết định độ đúng của measure, không chỉ là kiểu SQL. Đặt sai = tính sai tiền.
• Fan-out: join one_to_many làm base view nhân bản dòng → SUM ngây thơ bị thổi phồng (ví dụ $124.84 → $223.44).
• Symmetric aggregates: Looker tự phát hiện fan-out, dùng primary key + kỹ thuật SUM/AVG "đối xứng" (hash + cast) để cộng mỗi khoá đúng một lần. Cần primary key đúng + relationship đúng. Giới hạn ở count distinct; tự tắt khi không có fan-out.
• Derived table có thể làm base cho explore, dùng để tổng hợp trước, tránh fan-out — nền tảng cho PDT.
• Best practice: một explore một câu hỏi; relationship có chủ đích; hạn chế join thừa để tiết kiệm chi phí warehouse.

Bài liên quan trong loạt: LookML cơ bản · Dimensions & measures · Derived tables & PDT