drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bob Rumsby <brum...@maprtech.com>
Subject Re: Drilling JSON
Date Fri, 10 Oct 2014 06:26:10 GMT
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 <mufeed.usman@gmail.com> 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>
>

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