You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by Charles Wiese <cw...@gmail.com> on 2019/11/09 14:56:13 UTC
Nested Record
I was very excited about the use case of creating Patient Records with 1:M records nested (such as Diagnosis, Procedures etc). We have done this in MongoDB and could use a SQL driver to get the nested “rows”. I understand that the idea of nested “rows” ate actually columns with “column_name:id” for each “row value”. Seems a bit odd compare to a document / JSON layout, but okay. However, how does one use a SQL driver for reporting and “unnesting” this data?
Is that even possible … I want to “select count(nested_procedures), patient_type from patient group by patient_type)”….. in with Mongo driver it is a virtual table like "select count(patient_procedures \.nested_procedures), patient_type from patient, patient_procedures group by patient_type)”
Is this possible?
Re: Nested Record
Posted by "larsh@apache.org" <la...@apache.org>.
The slightly longer answer is that HBase is very well equipped to handle this case.Row for inner/nested relationships can be stored inline with the parent relation, by extending the key by one more part indicating the identity of inner rows.The trick is to teach Phoenix to understand this - such as executing joins for such 1:many relationships as single scans, etc.
There's an old Jira: PHOENIX-150... Perhaps try to warm that one up again?
-- Lars
On Sunday, November 10, 2019, 02:53:07 AM PST, James Taylor <ja...@apache.org> wrote:
Hi Charles,
Phoenix doesn’t support nested data. I suggest you take a look at other
open source projects such as Apache Drill or Presto.
Thanks,
James
On Sat, Nov 9, 2019 at 8:16 AM Charles Wiese <cw...@gmail.com> wrote:
> I was very excited about the use case of creating Patient Records with 1:M
> records nested (such as Diagnosis, Procedures etc). We have done this in
> MongoDB and could use a SQL driver to get the nested “rows”. I understand
> that the idea of nested “rows” ate actually columns with “column_name:id”
> for each “row value”. Seems a bit odd compare to a document / JSON
> layout, but okay. However, how does one use a SQL driver for reporting and
> “unnesting” this data?
>
> Is that even possible … I want to “select count(nested_procedures),
> patient_type from patient group by patient_type)”….. in with Mongo driver
> it is a virtual table like "select count(patient_procedures
> \.nested_procedures), patient_type from patient, patient_procedures group
> by patient_type)”
>
> Is this possible?
Re: Nested Record
Posted by James Taylor <ja...@apache.org>.
Hi Charles,
Phoenix doesn’t support nested data. I suggest you take a look at other
open source projects such as Apache Drill or Presto.
Thanks,
James
On Sat, Nov 9, 2019 at 8:16 AM Charles Wiese <cw...@gmail.com> wrote:
> I was very excited about the use case of creating Patient Records with 1:M
> records nested (such as Diagnosis, Procedures etc). We have done this in
> MongoDB and could use a SQL driver to get the nested “rows”. I understand
> that the idea of nested “rows” ate actually columns with “column_name:id”
> for each “row value”. Seems a bit odd compare to a document / JSON
> layout, but okay. However, how does one use a SQL driver for reporting and
> “unnesting” this data?
>
> Is that even possible … I want to “select count(nested_procedures),
> patient_type from patient group by patient_type)”….. in with Mongo driver
> it is a virtual table like "select count(patient_procedures
> \.nested_procedures), patient_type from patient, patient_procedures group
> by patient_type)”
>
> Is this possible?