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> >
