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>
