drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steven Phillips <sphill...@maprtech.com>
Subject Re: Drilling JSON
Date Fri, 10 Oct 2014 06:58:40 GMT
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 <brumsby@maprtech.com> 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 <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>
> >
>



-- 
 Steven Phillips
 Software Engineer

 mapr.com

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