drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mufy <mufeed.us...@gmail.com>
Subject Drilling JSON
Date Fri, 10 Oct 2014 06:23:15 GMT
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>

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