Related to this - I saw someone on stackoverflow asking if modified_time returned from show files could be treated relationally.
Can the show files result be queried or converted into query result from the sql command line, and if so how? On Sunday, August 23, 2015, Ted Dunning <ted.dunn...@gmail.com> wrote: > The cleanest fix would be to make the INFORMATION schema return information > about file system objects. Then you could do clean selects with whatever > you needed to do. > > https://drill.apache.org/docs/querying-the-information-schema/ > > > On Sun, Aug 23, 2015 at 8:31 AM, USC <hsua...@usc.edu <javascript:;>> > wrote: > > > Hi John, > > It is definitely a great idea to have SQL on show files. > > > > What we can do is open a JIRA issue. Usually, a interested person in > > community would pick up and work on :) > > > > > On Aug 23, 2015, at 4:07 AM, John Omernik <j...@omernik.com > <javascript:;>> wrote: > > > > > > Hey all, > > > > > > So while I've dabbled in drill, this past week I've really dug in, and > > > honestly, I think this project is a game changer, I was able to do some > > > amazing things with Drill kudos to all the hard work that has been done > > > with Drill. > > > > > > I had one question, and potential feature request: > > > > > > When using drill this weekend, I had a workspace setup, and I found > > myself > > > using the show files command often to find my directories etc. The > thing > > > is, the return of show files is not ordered. And when looking at file > > > system data there are many possible ways to order the results for > > > efficiency as a user. > > > > > > Consider the ls command in unix. The ability to specify different > > sorting > > > is built in there. I checked out > > > http://drill.apache.org/docs/show-files-command/ as well as tried the > > > "obvious" show files order by name and that didn't work nor did I see > > how I > > > could in the documentation. > > > > > > So, is there a way to order output? If there isn't now, could that be > > > added? I think just adding ORDER BY SQL methodology would be perfect > > here, > > > you have 8 fields (seen below) and ordering by any one of them, or > group > > of > > > them, with ASC/DESC just like standard order by would be a huge win. > > > > > > I suppose one could potentially ask for WHERE clause too, and maybe a > > > select (which fields) however I am more concerned with the order, but > if > > I > > > had to implement all there I could see: > > > > > > (All Three, select, where, and order) (I.e. after "Files" if the token > > > isn't WHERE or ORDER then check for the fields, if it's not a valid > > field > > > list error) > > > > > > SHOW FILES name, accessTime where name like '%.csv' order by name; > > > > > > (Where clause and order, note the token after FILES is WHERE) > > > SHOW FILES WHERE name like '%.csv' order by length ASC, name DESC; > > > > > > (Only Order, ORDER Is the first token after FILES) > > > SHOW FILES ORDER BY length ASC, name DESC > > > > > > I don't think we have to grant full SQL functionality here, just the > > > ability to display various fields, filter on criteria, and ordering.. > No > > > aggregates, etc. If you wanted to get fancy, I suppose you could take > the > > > table and make it a full on table, i.e. take the results make it a > quick > > > inmemory table and then utilize the whole drill stack (minus > aggregates) > > of > > > functions on it. Lots of options. I just wanted to get this down in > an > > > email as it was something I found myself wishing I had over and over > > during > > > data exploration. > > > > > > > > > |name| isDirectory | isFile | length | owner > > group|permissions|accessTime > > > | modificationTime | > > > > > > > > > > > > John > > >
