8000 Support ARRAY data type · Issue #10742 · lightdash/lightdash · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Support ARRAY data type #10742

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
yu-iskw opened this issue Jul 17, 2024 · 6 comments
Open

Support ARRAY data type #10742

yu-iskw opened this issue Jul 17, 2024 · 6 comments
Labels
✨ feature-request Request for a new feature or functionality

Comments

@yu-iskw
Copy link
Contributor
yu-iskw commented Jul 17, 2024

Description

I would like Lightdash to support ARRAY data type.
That would help us not to create additional models and can calculate some types of metrics.

What problem does this solve?

Duplication of rows prevents us from calculating some types of metrics on Lightdash.
If Lightdash supports ARRAY data type, we don't need to additional models and can calculate some types of metrics.

As far as I know, there are two main approaches to get ARRAY data available in Lightdash.
However, in either case, we can't calculate the sum of a column in one model.

  1. Expand ARRAY data into multiple rows
  2. Create another model only with unique key and ARRAY data and join it with the original model

Let's consider if we have a BigQuery table which has a ARRAY field.
We call it the original_table source.

  • id: INT (unique key)
  • name: STRING
  • amount: INT
  • items: ARRAY of STRING

In the first approach, we can expand the items ARRAY into multiple rows.
The expanded model has duplicated rows on id columns.
If we want to calculate the sum of amount column over unique ids on Lightdash, we can't do it because the id column are duplicated.

-- expanded_model.sql
SELECT
  id,
  name,
  amount,
  item,
FROM
  original_table,
  UNNEST(items) AS item

In the second approach, we can create another model only with unique id and items ARRAY.
Then, we join the original_table with the expanded_tags model on id column with the
We can calculate the sum of amount column over unique ids on Lightdash, if we don't use any field of items ARRAY.
However, if we want to filter rows by element of items ARRAY, we can't do it because the joined data has duplicated rows.
That might be confusing for users.

-- expanded_tags.sql
SELECT
  id,
  items,
FROM
  original_table
version: 2
models:
  - name: original_table
    meta:
      joins:
        - join: expanded_tags
          sql_on: ${expanded_tags.id} = ${original_table.id}
          type: left
@yu-iskw yu-iskw added the ✨ feature-request Request for a new feature or functionality label Jul 17, 2024
Closed
@yu-iskw
Copy link
Contributor Author
yu-iskw commented Jul 30, 2024

< 8000 a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/TuringLovesDeathMetal/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/TuringLovesDeathMetal">@TuringLovesDeathMetal Let me share another possible idea inspired by Looker instead of supporting the ARRAY data type. It seems that Looker allows to join ARRAY field by using the UNNEST function in the case of BigQuery. If the subsequent issues are addressed, we can also join with repeated fields as the ARRAY data type of BigQuery.

https://cloud.google.com/looker/docs/best-practices/how-to-model-nested-bigquery-data-in-looker

-- model file

connection: "bigquery_publicdata_standard_sql"

explore: persons {

  # Repeated nested object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
    relationship: one_to_many
  }

  # Non repeated nested object
  join: persons_phone_number {
    view_label: "Persons: Phone:"
    sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
    relationship: one_to_one
  }

}

-- view files

view: persons {

 sql_table_name: bigquery-samples.nested.persons_living ;;

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.fullName ;;
  }

  dimension: fullName {label: "Full Name"}

  dimension: kind {}

  dimension: age {type:number}

  dimension: citiesLived {hidden:yes}

  dimension: phoneNumber {hidden:yes}

  measure: average_age {
    type: average
    sql: ${age} ;;
    drill_fields: [fullName,age]
  }

  measure: count {
    type: count
    drill_fields: [fullName, cities_lived.place_count, age]
  }
}

view: persons_phone_number {

  dimension: areaCode {label: "Area Code"}

  dimension: number {}
}

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
  }

  dimension: place {}

  dimension: numberOfYears {
    label: "Number Of Years"
    type: number
  }

  measure: place_count {
    type: count
    drill_fields: [place, persons.count]
  }

  measure: total_years {
    type: sum
    sql: ${numberOfYears} ;;
    drill_fields: [persons.fullName, persons.age, place, numberOfYears]
  }

}

@lightdash-bot
Copy link
Collaborator

This issue was mentioned by a user in slack: https://lightdash-community.slack.com/archives/C03MG2VTR08/p1732728331734719?thread_ts=1732728331.734719&cid=C03MG2VTR08

@lightdash-bot
Copy link
Collaborator

This issue was mentioned by a user in slack: https://lightdash.slack.com/archives/C05UPNC0LGP/p1734374612987889?thread_ts=1734374612.987889&cid=C05UPNC0LGP

@lightdash-bot
Copy link
Collaborator

This issue was mentioned by a user in slack: https://lightdash.slack.com/archives/C07UA0AC2G2/p1736508486487049?thread_ts=1736508486.487049&cid=C07UA0AC2G2

@lightdash-bot
Copy link
Collaborator

This issue was mentioned by a user in slack: https://lightdash.slack.com/archives/C07UA0AC2G2/p1732536712367539?thread_ts=1732536712.367539&cid=C07UA0AC2G2

@12ian34
Copy link
Contributor
12ian34 commented Mar 20, 2025

requested by a prospect

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
✨ feature-request Request for a new feature or functionality
Projects
Selected for Development
Status: 📝 To-Do
Milestone
No milestone
Development

No branches or pull requests
3 participants
@yu-iskw @12ian34 @lightdash-bot
0