drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mufy <mufeed.us...@gmail.com>
Subject Re: Drilling JSON
Date Fri, 10 Oct 2014 08:11:16 GMT
I did go through the tutorials and when I saw the complexity of the data
structure in my file I knew for sure the tutorial wouldn't be of much help
:-).

@Bob - Nevertheless, it has helped to get some sort of primer. Thank you.

@Steve - Your inputs were valuable. Will try to work on from there. Thank
you.


---
Mufeed Usman
My LinkedIn <http://www.linkedin.com/pub/mufeed-usman/28/254/400> | My
Social Cause <http://www.vision2016.org.in/> | My Blogs : LiveJournal
<http://mufeed.livejournal.com>




On Fri, Oct 10, 2014 at 12:28 PM, Steven Phillips <sphillips@maprtech.com>
wrote:

> The problem with those queries is that "InstanceId" and "OwnerId" or not
> top-level fields. You are dealing with a nested data structure.
>
> I would recommend, as Bob suggested, trying out the tutorial first. That
> has some simpler json data to play with.
>
> For the data you have, there is a single record, which contains a single
> field called Reservations, which is a list objects (a.k.a. maps), which
> each contain a field called Instances, which is also a list of objects,
> which contain a field called InstanceId.
>
> Unfortunately, we don't currently have a flatten operator, which would
> allow you to break out each item in a list into its own record. The best
> you can do right now is select an index in the list:
>
> 0: jdbc:drill:> select Reservations[0].Instances[0].InstanceId from
> `/tmp/res.json`;
> +------------+
> |   EXPR$0   |
> +------------+
> | i-85d5e28e |
> +------------+
> 1 row selected (0.491 seconds)
> 0: jdbc:drill:> select Reservations[1].Instances[0].InstanceId from
> `/tmp/res.json`;
> +------------+
> |   EXPR$0   |
> +------------+
> | i-a5e9deae |
> +------------+
> 1 row selected (0.445 seconds)
>
> The reason the ouput is truncated when you select * is because in this
> case, the entire document is only a single record with a single column ,
> and sqlline has a limit to how much text it displays. You can increase this
> by running
>
> !set maxWidth 10000
>
> On Thu, Oct 9, 2014 at 11:26 PM, Bob Rumsby <brumsby@maprtech.com> wrote:
>
> > Have you done the tutorial?
> > https://cwiki.apache.org/confluence/display/DRILL/Apache+Drill+Tutorial
> > Some of these queries might help you.
> >
> > Bob
> >
> > On Thu, Oct 9, 2014 at 11:23 PM, mufy <mufeed.usman@gmail.com> wrote:
> >
> > > I am not from a database background and as expected am having teething
> > > problems playing around querying data using Drill. I was wondering if
> > > someone could point me with a few example queries based on the data
> > below.
> > > For instance, how will my query be if I wanted to retrieve
> "InstanceId":
> > > "i-85d5e28e" from the data below?
> > >
> > > I went through the examples in Wiki, but they were simple and I could
> not
> > > map the structure to formulate my queries against this. One or 2 that I
> > > tried were returning NULL.
> > >
> > > 0: jdbc:drill:zk=n67:5181> select InstanceId from dfs.`aws.json`;
> > > +------------+
> > > | InstanceId |
> > > +------------+
> > > | null       |
> > > +------------+
> > > 1 row selected (0.228 seconds)
> > >
> > >
> > > 0: jdbc:drill:zk=n67:5181> select OwnerId from dfs.`aws.json`;
> > > +------------+
> > > |  OwnerId   |
> > > +------------+
> > > | null       |
> > > +------------+
> > > 1 row selected (0.08 seconds)
> > >
> > > And a select * gave a truncated view of the content,
> > >
> > > 0: jdbc:drill:zk=n67:5181> select * from dfs.`aws.json`;
> > > +--------------+
> > > | Reservations |
> > > +--------------+
> > > |
> > >
> > >
> >
> [{"OwnerId":"898107336781","ReservationId":"r-48a03243","Groups":[{"GroupName":"ElasticMapReduce-master","GroupId":"sg-8e5681bd"}],"RequesterId":"110610769928","Instances":[{"Monitoring":
> > > |
> > > +--------------+
> > > 1 row selected (0.285 seconds)
> > >
> > >
> > >
> > > {
> > >     "Reservations": [
> > >         {
> > >             "OwnerId": "898107336781",
> > >             "ReservationId": "r-48a03243",
> > >             "Groups": [
> > >                 {
> > >                     "GroupName": "ElasticMapReduce-master",
> > >                     "GroupId": "sg-8e5681bd"
> > >                 }
> > >             ],
> > >             "RequesterId": "110610769928",
> > >             "Instances": [
> > >                 {
> > >                     "Monitoring": {
> > >                         "State": "disabled"
> > >                     },
> > >                     "PublicDnsName": "
> > > ec2-54-184-72-220.us-west-2.compute.amazonaws.com",
> > >                     "RootDeviceType": "instance-store",
> > >                     "State": {
> > >                         "Code": 16,
> > >                         "Name": "running"
> > >                     },
> > >                     "EbsOptimized": false,
> > >                     "LaunchTime": "2014-09-10T17:21:28.000Z",
> > >                     "PublicIpAddress": "54.184.72.220",
> > >                     "PrivateIpAddress": "10.253.11.180",
> > >                     "ProductCodes": [],
> > >                     "StateTransitionReason": null,
> > >                     "InstanceId": "i-85d5e28e",
> > >                     "ImageId": "ami-18a73928",
> > >                     "PrivateDnsName":
> > > "ip-10-253-11-180.us-west-2.compute.internal",
> > >                     "SecurityGroups": [
> > >                         {
> > >                             "GroupName": "ElasticMapReduce-master",
> > >                             "GroupId": "sg-8e5681bd"
> > >                         }
> > >                     ],
> > >                     "ClientToken":
> > "60708f00-5169-4647-9e9e-f83ce038d890",
> > >                     "InstanceType": "m1.large",
> > >                     "NetworkInterfaces": [],
> > >                     "Placement": {
> > >                         "Tenancy": "default",
> > >                         "GroupName": null,
> > >                         "AvailabilityZone": "us-west-2a"
> > >                     },
> > >                     "Hypervisor": "xen",
> > >                     "BlockDeviceMappings": [],
> > >                     "Architecture": "x86_64",
> > >                     "KernelId": "aki-ace26f9c",
> > >                     "VirtualizationType": "paravirtual",
> > >                     "Tags": [
> > >                         {
> > >                             "Value": "j-79I8KKKUTOCB",
> > >                             "Key": "aws:elasticmapreduce:job-flow-id"
> > >                         },
> > >                         {
> > >                             "Value": "MASTER",
> > >                             "Key":
> > > "aws:elasticmapreduce:instance-group-role"
> > >                         }
> > >                     ],
> > >                     "AmiLaunchIndex": 0
> > >                 }
> > >             ]
> > >         },
> > >         {
> > >             "OwnerId": "898107336781",
> > >             "ReservationId": "r-52a13359",
> > >             "Groups": [
> > >                 {
> > >                     "GroupName": "ElasticMapReduce-slave",
> > >                     "GroupId": "sg-8c5681bf"
> > >                 }
> > >             ],
> > >             "RequesterId": "110610769928",
> > >             "Instances": [
> > >                 {
> > >                     "Monitoring": {
> > >                         "State": "disabled"
> > >                     },
> > >                     "PublicDnsName": "
> > > ec2-54-214-173-57.us-west-2.compute.amazonaws.com",
> > >                     "RootDeviceType": "instance-store",
> > >                     "State": {
> > >                         "Code": 16,
> > >                         "Name": "running"
> > >                     },
> > >                     "EbsOptimized": false,
> > >                     "LaunchTime": "2014-09-10T17:21:20.000Z",
> > >                     "PublicIpAddress": "54.214.173.57",
> > >                     "PrivateIpAddress": "10.252.43.248",
> > >                     "ProductCodes": [],
> > >                     "StateTransitionReason": null,
> > >                     "InstanceId": "i-a5e9deae",
> > >                     "ImageId": "ami-18a73928",
> > >                     "PrivateDnsName":
> > > "ip-10-252-43-248.us-west-2.compute.internal",
> > >                     "SecurityGroups": [
> > >                         {
> > >                             "GroupName": "ElasticMapReduce-slave",
> > >                             "GroupId": "sg-8c5681bf"
> > >                         }
> > >                     ],
> > >                     "ClientToken":
> > "7b294f6f-3bae-430e-a3f7-f5413a1df65a",
> > >                     "InstanceType": "m1.large",
> > >                     "NetworkInterfaces": [],
> > >                     "Placement": {
> > >                         "Tenancy": "default",
> > >                         "GroupName": null,
> > >                         "AvailabilityZone": "us-west-2a"
> > >                     },
> > >                     "Hypervisor": "xen",
> > >                     "BlockDeviceMappings": [],
> > >                     "Architecture": "x86_64",
> > >                     "KernelId": "aki-ace26f9c",
> > >                     "VirtualizationType": "paravirtual",
> > >                     "Tags": [
> > >                         {
> > >                             "Value": "CORE",
> > >                             "Key":
> > > "aws:elasticmapreduce:instance-group-role"
> > >                         },
> > >                         {
> > >                             "Value": "j-79I8KKKUTOCB",
> > >                             "Key": "aws:elasticmapreduce:job-flow-id"
> > >                         }
> > >                     ],
> > >                     "AmiLaunchIndex": 1
> > >                 },
> > >                 {
> > >                     "Monitoring": {
> > >                         "State": "disabled"
> > >                     },
> > >                     "PublicDnsName": "
> > > ec2-54-203-24-144.us-west-2.compute.amazonaws.com",
> > >                     "RootDeviceType": "instance-store",
> > >                     "State": {
> > >                         "Code": 16,
> > >                         "Name": "running"
> > >                     },
> > >                     "EbsOptimized": false,
> > >                     "LaunchTime": "2014-09-10T17:21:20.000Z",
> > >                     "PublicIpAddress": "54.203.24.144",
> > >                     "PrivateIpAddress": "10.252.36.114",
> > >                     "ProductCodes": [],
> > >                     "StateTransitionReason": null,
> > >                     "InstanceId": "i-a4e9deaf",
> > >                     "ImageId": "ami-18a73928",
> > >                     "PrivateDnsName":
> > > "ip-10-252-36-114.us-west-2.compute.internal",
> > >                     "SecurityGroups": [
> > >                         {
> > >                             "GroupName": "ElasticMapReduce-slave",
> > >                             "GroupId": "sg-8c5681bf"
> > >                         }
> > >                     ],
> > >                     "ClientToken":
> > "7b294f6f-3bae-430e-a3f7-f5413a1df65a",
> > >                     "InstanceType": "m1.large",
> > >                     "NetworkInterfaces": [],
> > >                     "Placement": {
> > >                         "Tenancy": "default",
> > >                         "GroupName": null,
> > >                         "AvailabilityZone": "us-west-2a"
> > >                     },
> > >                     "Hypervisor": "xen",
> > >                     "BlockDeviceMappings": [],
> > >                     "Architecture": "x86_64",
> > >                     "KernelId": "aki-ace26f9c",
> > >                     "VirtualizationType": "paravirtual",
> > >                     "Tags": [
> > >                         {
> > >                             "Value": "j-79I8KKKUTOCB",
> > >                             "Key": "aws:elasticmapreduce:job-flow-id"
> > >                         },
> > >                         {
> > >                             "Value": "CORE",
> > >                             "Key":
> > > "aws:elasticmapreduce:instance-group-role"
> > >                         }
> > >                     ],
> > >                     "AmiLaunchIndex": 0
> > >                 }
> > >             ]
> > >         },
> > >         {
> > >             "OwnerId": "898107336781",
> > >             "ReservationId": "r-1a52b32d",
> > >             "Groups": [
> > >                 {
> > >                     "GroupName": "Amazon Linux
> > > AMI-2013-09-AutogenByAWSMP-",
> > >                     "GroupId": "sg-5e45646e"
> > >                 }
> > >             ],
> > >             "Instances": [
> > >                 {
> > >                     "Monitoring": {
> > >                         "State": "disabled"
> > >                     },
> > >                     "PublicDnsName": null,
> > >                     "KernelId": "aki-fc37bacc",
> > >                     "State": {
> > >                         "Code": 80,
> > >                         "Name": "stopped"
> > >                     },
> > >                     "EbsOptimized": false,
> > >                     "LaunchTime": "2013-10-08T17:17:58.000Z",
> > >                     "ProductCodes": [],
> > >                     "Tags": [
> > >                         {
> > >                             "Value": null,
> > >                             "Key": "Name"
> > >                         }
> > >                     ],
> > >                     "InstanceId": "i-7491f240",
> > >                     "ImageId": "ami-d03ea1e0",
> > >                     "PrivateDnsName": null,
> > >                     "KeyName": "abhinav",
> > >                     "SecurityGroups": [
> > >                         {
> > >                             "GroupName": "Amazon Linux
> > > AMI-2013-09-AutogenByAWSMP-",
> > >                             "GroupId": "sg-5e45646e"
> > >                         }
> > >                     ],
> > >                     "ClientToken": "yvTUN1381251474395",
> > >                     "InstanceType": "m1.large",
> > >                     "NetworkInterfaces": [],
> > >                     "Placement": {
> > >                         "Tenancy": "default",
> > >                         "GroupName": null,
> > >                         "AvailabilityZone": "us-west-2a"
> > >                     },
> > >                     "Hypervisor": "xen",
> > >                     "BlockDeviceMappings": [
> > >                         {
> > >                             "DeviceName": "/dev/sda1",
> > >                             "Ebs": {
> > >                                 "Status": "attached",
> > >                                 "DeleteOnTermination": true,
> > >                                 "VolumeId": "vol-f6d3b59f",
> > >                                 "AttachTime":
> "2013-10-08T16:57:58.000Z"
> > >                             }
> > >                         },
> > >                         {
> > >                             "DeviceName": "/dev/sdf",
> > >                             "Ebs": {
> > >                                 "Status": "attached",
> > >                                 "DeleteOnTermination": false,
> > >                                 "VolumeId": "vol-ddd3b5b4",
> > >                                 "AttachTime":
> "2013-10-08T17:17:49.000Z"
> > >                             }
> > >                         }
> > >                     ],
> > >                     "Architecture": "x86_64",
> > >                     "StateReason": {
> > >                         "Message": "Client.UserInitiatedShutdown: User
> > > initiated shutdown",
> > >                         "Code": "Client.UserInitiatedShutdown"
> > >                     },
> > >                     "RootDeviceName": "/dev/sda1",
> > >                     "VirtualizationType": "paravirtual",
> > >                     "RootDeviceType": "ebs",
> > >                     "StateTransitionReason": "User initiated
> (2013-10-08
> > > 18:10:13 GMT)",
> > >                     "AmiLaunchIndex": 0
> > >                 }
> > >             ]
> > >         }
> > >     ]
> > > }
> > >
> > >
> > > ---
> > > Mufeed Usman
> > > My LinkedIn <http://www.linkedin.com/pub/mufeed-usman/28/254/400> | My
> > > Social Cause <http://www.vision2016.org.in/> | My Blogs : LiveJournal
> > > <http://mufeed.livejournal.com>
> > >
> >
>
>
>
> --
>  Steven Phillips
>  Software Engineer
>
>  mapr.com
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message