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
