drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mufy <mufeed.us...@gmail.com>
Subject Re: Drilling JSON
Date Fri, 10 Oct 2014 10:54:56 GMT
Is !set maxWidth 10000 a session change or system change?

How do I get a list of these properties? And also the current values of
those properties.


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




On Fri, Oct 10, 2014 at 1:41 PM, mufy <mufeed.usman@gmail.com> wrote:

> I did go through the tutorials and when I saw the complexity of the data
> structure in my file I knew for sure the tutorial wouldn't be of much help
> :-).
>
> @Bob - Nevertheless, it has helped to get some sort of primer. Thank you.
>
> @Steve - Your inputs were valuable. Will try to work on from there. Thank
> you.
>
>
> ---
> 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>
>
>
>
>
> On Fri, Oct 10, 2014 at 12:28 PM, Steven Phillips <sphillips@maprtech.com>
> wrote:
>
>> 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