You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Virilo Tejedor <vi...@gmail.com> on 2017/01/18 15:54:20 UTC
Array or list type attributes from MongoDB
Hi,
I’d like to use Apache Drill to feed a QlikSense application from a MongoDB
database.
I’m not very sure about how should I manage list type attributes.
I have a MongoDB collection containing entities like this example, where a
“response” contains a list of “docs”, and each doc has a list of “files”
response = { '_id': ObjectId('12346e41234b0dbfe5f5b4d38'),
'creationDate': '2016-11-02T17:33:58+01:00',
'docs': [ { 'files': [ {
'content': '...',
'name': None,
'type': 't1'},
{
'content': '...',
'name': None,
'type': 't31'},
{
'content': '...',
'name': 'ORIGINAL',
'type': 'tX'}],
'requestId': 39},
{ 'files': [ {
'content': '...',
'name': None,
'type': 'tG'},
{
'content': '...',
'name': None,
'type': 'tX'}],
'requestId': None}],
'entityCode': '360',
'entityType': '13',
'messageType': 'msgZ',
'processId': 'ID00000000001294',
'registryCode': '00015',
'registryType': '2',
'returnCode': '200',
'returnDescription': None}
It could be great if this entities could be offered to QlikSense as if it
were different tables:
Is it possible to configure Apache Drill on this way?
I know that it is possible using MongoDB BI Connector. But this connector
is an enterprise feature only.
https://docs.mongodb.com/bi-connector/master/schema-configuration/#arrays
Thanks in advance.
Best regards,
Virilo
Re: Array or list type attributes from MongoDB
Posted by rahul challapalli <ch...@gmail.com>.
I suggest that you try using "flatten" [1] along with the mongo db storage
plugin. I did not understand what you meant by "entities could be offered
to QlikSense as if it were different tables". Below is an example of
flatten usage
select d.id, flatten(d.doc.files), d.doc.requestId from (
select data.response._id id, flatten(data.response.docs) doc from
`input.json` data
) d;
[1] https://drill.apache.org/docs/flatten/
On Wed, Jan 18, 2017 at 7:54 AM, Virilo Tejedor <vi...@gmail.com>
wrote:
> Hi,
>
>
> I’d like to use Apache Drill to feed a QlikSense application from a MongoDB
> database.
>
> I’m not very sure about how should I manage list type attributes.
>
>
> I have a MongoDB collection containing entities like this example, where a
> “response” contains a list of “docs”, and each doc has a list of “files”
>
> response = { '_id': ObjectId('12346e41234b0dbfe5f5b4d38'),
>
> 'creationDate': '2016-11-02T17:33:58+01:00',
>
> 'docs': [ { 'files': [ {
>
> 'content': '...',
>
> 'name': None,
>
> 'type': 't1'},
>
> {
>
> 'content': '...',
>
> 'name': None,
>
> 'type': 't31'},
>
> {
>
> 'content': '...',
>
> 'name': 'ORIGINAL',
>
> 'type': 'tX'}],
>
> 'requestId': 39},
>
> { 'files': [ {
>
> 'content': '...',
>
> 'name': None,
>
> 'type': 'tG'},
>
> {
>
> 'content': '...',
>
> 'name': None,
>
> 'type': 'tX'}],
>
> 'requestId': None}],
>
> 'entityCode': '360',
>
> 'entityType': '13',
>
> 'messageType': 'msgZ',
>
> 'processId': 'ID00000000001294',
>
> 'registryCode': '00015',
>
> 'registryType': '2',
>
> 'returnCode': '200',
>
> 'returnDescription': None}
>
>
>
> It could be great if this entities could be offered to QlikSense as if it
> were different tables:
>
>
>
> Is it possible to configure Apache Drill on this way?
>
>
>
> I know that it is possible using MongoDB BI Connector. But this connector
> is an enterprise feature only.
>
> https://docs.mongodb.com/bi-connector/master/schema-configuration/#arrays
>
>
>
>
> Thanks in advance.
>
>
>
>
> Best regards,
>
>
> Virilo
>