LookerでのRelation Ship指定と主キー

概要

• modelでのrelation shipの指定を間違えたので、備忘の為にメモします。
• 初歩の初歩なので、詳しい方はスルー して下さい。

間違えた

• 下記のようなモデルを指定するとき、relation shipには何が入るでしょう。
• 答えは、ManyTable( 多 )に対して、OneTable( １ )をJOINするので、 many_to_one です。
• 初歩の初歩ですが、これを、one_to_many に間違えると何が起きるか？という話です。

explore: ManyTable{
  join:  OneTable{
    relationship: ????★ここ★????
    sql_on: ${ManyTable.joinkey} = ${OneTable.joinkey} ;;
  }
}

Relation Shipって

many_to_one

関係性

• テーブルの関係性ですね。例えば以下のテーブルMany／Oneを考えると、
• Many（多）に対して、 One（1）の関係性なので、多：１（many_to_one）なわけです。

LEFT JOIN　と　件数カウント

• many_to_oneで、LEFT JOINすると、こんな感じですね。
• この結合時に、Many側の件数を数えるには、結合した表に対し、単に、count(*) してあげれば良いわけです。
• 多側が左表、かつ、LEFT JOINなので、count(*)で、A,B,C,D,E⇒5行と数えられます。

one_to_many

関係性

• さっきと逆ですね。一般的には、、
• many_to_oneの多（トランザクション）を左に、１（マスタ）を右に持ってくることが多い気がします。

LEFT JOIN　と　件数カウント

• one_to_manyで、LEFT JOINすると、こんな感じです。
• ONE側が左表なので、結合できるレコードに関しては、左側に何度も出現 します。（下記でいう A や B ）
• よって、One側のレコード件数を数えるには、 主キー（もしくは候補キー）でdistinct したくなります。
• 本件は、Looker関係なく様々議論されていますね。
  • Count one-to-many using left join gives wrong count on source table
  • Prevent duplicate record counts in one to many query

Relation Ship指定を間違えると

• 冒頭に記載したとおり、many_to_one（正）と指定すべきところを、one_to_many（誤）と指定すると、
• LEFT JOINでの件数カウントで、
  • 本当は、count(*) とpkを使わずに件数がカウントできる処理に対して、
  • Lookerは、count(distinct pk) とpkで重複排除をした上でのカウントを実行しようとします。
• そして、左表側に、主キー(pk) の指定がない場合、下記のようなエラーを出力します。
  • 左表に対して、pkを要求するわけですね。

Field 'ManyTbl Count' cannot be calculated because of a one_to_many or many_to_many join. 
Add a primary key to ManyTbl

よって

• 初歩の初歩ですが、（当たり前ですが、）
  • Relation Shipを正しく指定する。（備忘の為）、もう一度、model定義を記載します。下記です。

explore: ManyTable{
  join:  OneTable{
    relationship: many_to_one 　# Manyに対する、Oneなので、many_to_one
    sql_on: ${ManyTable.joinkey} = ${OneTable.joinkey} ;;
  }
}

• その上で、
  • 原則、主キーは指定する。
  • RDBMS上、主キーが無い場合、列の組合せで複合主キーが構成できる場合、Looker上でそれを指定。
    • 指定方法は、Dealing with Non-Unique Primary Keys、から抜粋しておきます。

dimension: compound_primary_key {
  primary_key: yes
  hidden: yes
  type: string
  sql: CONCAT(${TABLE}.primary_key_1, ' ', ${TABLE}.primary_key_2) ;;
}

最後に

• 初歩の初歩のミスでお恥ずかしいですが、どなたかの役に立てば、、と