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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >
