[jira] [Commented] (DRILL-3698) Expose Show Files Command As SQL for sorting/filtering
[ https://issues.apache.org/jira/browse/DRILL-3698?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14709394#comment-14709394 ] Paul Mogren commented on DRILL-3698: +1 for providing table-like access via INFORMATION_SCHEMA as mentioned on the mailing list. Expose Show Files Command As SQL for sorting/filtering -- Key: DRILL-3698 URL: https://issues.apache.org/jira/browse/DRILL-3698 Project: Apache Drill Issue Type: Improvement Components: SQL Parser Affects Versions: Future Environment: All Reporter: John Omernik Assignee: Aman Sinha Labels: features Fix For: Future When using drill, 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. Based on a mailing list discussion there is no way to do that currently in Drill, hence this JIRA 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 SQL order by would be a huge win. I suppose one could potentially ask for WHERE clause (filtering)too, and maybe a select (which fieldsto display) however I am more concerned with the order, but if I had to implement all there I could see examples below: (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 (i.e. aggregates), just the ability to display various fields, filter on criteria, and ordering. 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 on it. Lots of options. I just wanted to get this down in an email/JIRA as it was something I found myself wishing I had over and over during data exploration. Fields Currently Returned: |name| isDirectory|isFile|length|owner group|permissions|accessTime|modificationTime| -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DRILL-3698) Expose Show Files Command As SQL for sorting/filtering
[ https://issues.apache.org/jira/browse/DRILL-3698?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14708633#comment-14708633 ] Ted Dunning commented on DRILL-3698: I think that making [show tables] into the equivalent of a select query is the way to go with this. If that can work syntactically, then everything you want just falls out directly. Expose Show Files Command As SQL for sorting/filtering -- Key: DRILL-3698 URL: https://issues.apache.org/jira/browse/DRILL-3698 Project: Apache Drill Issue Type: Improvement Components: SQL Parser Affects Versions: Future Environment: All Reporter: John Omernik Assignee: Aman Sinha Labels: features Fix For: Future When using drill, 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. Based on a mailing list discussion there is no way to do that currently in Drill, hence this JIRA 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 SQL order by would be a huge win. I suppose one could potentially ask for WHERE clause (filtering)too, and maybe a select (which fieldsto display) however I am more concerned with the order, but if I had to implement all there I could see examples below: (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 (i.e. aggregates), just the ability to display various fields, filter on criteria, and ordering. 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 on it. Lots of options. I just wanted to get this down in an email/JIRA as it was something I found myself wishing I had over and over during data exploration. Fields Currently Returned: |name| isDirectory|isFile|length|owner group|permissions|accessTime|modificationTime| -- This message was sent by Atlassian JIRA (v6.3.4#6332)
