Rob, What errors are in your drillbit.log and drillbit.out files? This will help to understand why you are seeing the issues with getting Drill running.
To “spool” the query output you can user !record <filename> where it writes to the relative path from where sqlline was started I would recommend that you rather use CTAS (Create Tables AS) to get the output you want, writing a file(s) to the file system and defining the filesystem storage plug in. Also remember to set the storage format to the desired output format before running CTAS. alter session set `store.format` = ‘csv’; —Andries On Jun 9, 2015, at 6:09 AM, Neeraja Rentachintala <[email protected]> wrote: > Rob > Before answering specific questions, I just wanted to check how did you > setup Drill. > Did you just download the tar from the Drill site and launching in embedded > mode? > I just did the same and am able to query the file you attached. I just put > in my local file system under the directory and referenced in the query. > > 0: jdbc:drill:zk=local> select flatten(t.entries.entries) from > dfs.`/Users/nrentachintala/Downloads/yelp/sample_json.json` t limit 5; > > *+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+* > > *| ** > > EXPR$0 > > ** |* > > *+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+* > > *| > *{"@type":"activityLogEntry","id":"444312323","type":"BBBDDD","objectName":"lll","runId":3,"runtimeEnvironmentId":"333K2E24333333333332","startTime":"2344-34-13T33:33:41.333Z","endTime":"2344-34-13T33:33:11.333Z","state":4,"failedSourceRows":3,"successSourceRows":4,"failedTargetRows":3,"successTargetRows":4,"errorMsg":"No > errors encountered."}* |* > > *| > *{"@type":"activityLogEntry","id":"444312322","type":"BBBDDD","objectName":"mmm","runId":3,"runtimeEnvironmentId":"333K2E24333333333332","startTime":"2344-34-13T33:33:11.333Z","endTime":"2344-34-13T33:33:13.333Z","state":4,"failedSourceRows":3,"successSourceRows":4,"failedTargetRows":3,"successTargetRows":4,"errorMsg":"No > errors encountered."}* |* > > *| > *{"@type":"activityLogEntry","id":"444312322","type":"FFFGGG","objectName":"","runId":3,"runtimeEnvironmentId":"333K2E24333333333332","startTime":"2344-34-13T33:33:13.333Z","endTime":"2344-34-13T33:33:24.333Z","state":4,"failedSourceRows":3,"successSourceRows":4,"failedTargetRows":3,"successTargetRows":4,"errorMsg":"No > errors encountered."}* |* > > *| > *{"@type":"activityLogEntry","id":"444312322","type":"FFFGGG","objectName":"","runId":3,"runtimeEnvironmentId":"333K2E24333333333332","startTime":"2344-34-13T33:33:24.333Z","endTime":"2344-34-13T33:33:22.333Z","state":4,"failedSourceRows":3,"successSourceRows":3,"failedTargetRows":3,"successTargetRows":3,"errorMsg":"No > errors encountered."}* |* > > *| > *{"@type":"activityLogEntry","id":"444312323","type":"BBBDDD","objectName":"qqq","runId":3,"runtimeEnvironmentId":"333K2E24333333333332","startTime":"2344-34-13T33:33:22.333Z","endTime":"2344-34-13T33:31:14.333Z","state":4,"failedSourceRows":3,"successSourceRows":422,"failedTargetRows":3,"successTargetRows":422,"errorMsg":"No > errors encountered."}* |* > > *+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+* > > > 0: jdbc:drill:zk=local> select t1.entries.objectName, t1.entries.runId from > (select flatten(t.entries.entries) entries from `sample_json.json` t) t1; > > *+---------+---------+* > > *| **EXPR$0 ** | **EXPR$1 ** |* > > *+---------+---------+* > > *| *lll * | *3 * |* > > *| *mmm * | *3 * |* > > *| * * | *3 * |* > > *| * * | *3 * |* > > *| *qqq * | *3 * |* > > *| *ooo * | *3 * |* > > *| *ppp * | *3 * |* > > *| * * | *3 * |* > > *| * * | *3 * |* > > *| * * | *3 * |* > > *+---------+---------+* > > > On Tue, Jun 9, 2015 at 12:41 AM, Rob E <[email protected]> wrote: > >> Hello, >> >> We are trying to 'flatten' out a multi level (nested) json file that is >> the result of a REST API call. We were going to try flatten out this to a >> CSV, load it into Oracle tables and then do traditional SQL queries. The >> initial approach was going to be done using a combination of CSVKIT >> (in2csv) and Perl. Until I came across Apache Drill.... >> >> I have spent the past 4 days going through the setup and tutorials but >> have hit a roadlock on a number of different front which I have listed >> below and hope you can help. >> >> 1) Where does Drill get the employee.json file from? I have searched my >> whole computer after downloading the tar file and looked in the sample data >> directory and cannot find it anywhere. I have attached a screenshot after >> doing a find command which shows the content of the sample data directory. >> >> 2) I have the json file I wish to query in the bin directory but whenever >> I try to query it errors - based upon reading the tutes and also looking at >> the structure of the select statement agains the employee.json file I THINK >> it's right but it fails every time. I have attached a cut down version of >> the sample json i am trying to query. It is called sample_json.json >> >> 3) The aforementioned json file contains nested data and the problem is >> the nested level also contain similar names as some of the higher levels. >> This is the way the log information is returned in JSON format from the >> REST API call. How can one flatten this? The deepest level is 2 considering >> that leve one starts at 0. ie. there are 3 levels at most. In cases where >> there are 3 levels, we want to flatten it out, in cases where there are 2, >> then only 2 will be flattened out. >> >> 4) When trying to executed drill_embedded from the bin directory it starts >> up but then when trying to run the same query against employee.json that >> works from the UI, the drill_embedded command simply hangs until i have to >> kill the process. See the screenshot "drill embedded under cygwin hangs". >> Note JAVA_HOME has been set. >> >> 5)Then when trying sqlline from the bin directory it starts up, I run the >> same employee query, it hangs and then starts togive log4j errors. See >> attached screenshot "sqlline_hangs_then_gives_log4j_error" >> >> 6) Finally the sqlline bat file works under the Windows command prompt and >> returns results when querying employee.json (but no other file) but I >> cannot exit despite entering the !quit command. see attached screenshot: >> "Unable_to_exit_sqlline_when_running_from_Windows_command_promtp" >> >> Please note I've gone through the documentation and believe I've followed >> the instructions as best I can and managed to solve a few issues by >> searching online. However, even after trying towatch some Drill youtube >> videos and looking on forums like stackoverflow still can't it to work as >> nicely as the tutes say and videos demonstrated. >> >> Basically all we want to do is flatten out a nested JSON file and Drill >> seems far more suited to this task - if only we can get it working properly >> - than coding Python or Perl scripts. THe csvkit (in2csv) works fine but >> not for nested JSON data which is our use case. >> >> One final question - how can one 'spool' results to a file? >> >> We've tried giving it a go under Windows, Mac OS and also cygwin but each >> time have failed somewhere despite trying to follow the documentation and >> tutorial material to the best of our ability. >> >> It just looks soooooo much easier reaing the online doco then actually >> trying to implement. >> >> We'd gratefully welcome some feedback on the aforementioned teething >> issues we're experiencing as we'd certainly like to fly the Apache Drill >> flag. >> >> thanks and regards, >> Rob. >>
