Plugin Info in INFORMATION_SCHEMA
Hello Drillers, I am working on integrating Drill with a BI tool, and I’m wondering if there is any way to get information about a storage plugin via the INFORMATION_SCHEMA or other query? Specifically, I want to be able to determine whether a given storage plugin is a ‘file’ plugin or not. Thanks! — C
Re: Documentation Issue
Very good point on the 'Added in Drill Version 1.XX' , and I think Bridget's already on it ! However, for sys.functions, it doesn't make sense to carry a min version. The Drill server basically scans all the available functions and exposes them in the sys.functions table. If a function has been added in that Drill version, it will show up.. and dropped, it will not. Tracking the recently added/removed functions to a release is a bit trickier, but we should be able to do that between releases. If there were in-built support for function documentation, that would vastly simplify the problem, but there has not been consensus on this and the value it brings for the effort involved in updating all the functions. (last call, I saw nearly 3K functions, when accounting for all the signatures as well). And don't forget, UDFs would need to do this too. On 1/3/2019 7:28:50 AM, John Omernik wrote: Hey all, I was looking for some tools to help me add/subtract dates, and I went to: https://drill.apache.org/docs/date-time-functions-and-arithmetic/ TIMESTAMPDIFF and TIMESTAMPADD where there, and I got excited, I organized my query, and tried to run them and got the error below. So I tried troubleshooting. 30 minutes later, after reviewing my query, reviewing the docs, reviewing all the things, I came here, as I did that, I see there is a new Drill Release... hmmm. On a hunch, I command+F and sure enough, the function I was trying to use was just added in 1.15, (I am running 1.14) . So, A. I think the anytime a doc is updated to reflect a new function, we should add "Added in Drill version 1.15" The error I got didn't tell me the function didn't exist, and was not helpful. This caused me to go down a 30 minute rabbit hole. This is a bad user experience. B. I see that sys.functions is in the 1.15 release. This is awesome. I think we should add a min_drill_version column to this though. For those functions that are added beyond 1.15, we should know where they sit. This will help us create a process that can auto update Documentation as well. Anytime a new function is added, we can run a query that shows the those not in the docs. Thoughts? SYSTEM ERROR: AssertionError: todo: implement syntax SPECIAL(Reinterpret(-($0, ITEM($2, 'charge_state_ts'
Re: Nested Window Queries
John, what's the full SQL query that you submitted ? On Thu, Jan 3, 2019 at 6:45 AM John Omernik wrote: > Is there a limitation on nesting of of Window Queries? I have a query > where I am using an event stream, and the changing of a value to indicate > an event. (The state goes from disconnected, to charging, to complete, it > reports many times in each of those states, but I am using lag(state, 1) > over (order by event_ts) to find those records where current record is say > complete and previous record was charging to indicate that the state has > changed. > > This works well. > > Now I am trying to take that result set, and do another window to find the > delta time between when the charging started and when the charging > completed, you would think that a nested query showing the state change > times, and then a select lag(event_ts, 1) over (order by event_ts) would be > able to get me both the current time (when the charging was complete) and > the previous record event_ts (when the charging started as outputted by the > subquery). However, I am getting a verbose confusing error that perhaps we > can help figure out... > > The first part is: > > SYSTEM ERROR: CannotPlanException: Node > [rel#92358:Subset#11.LOGICAL.ANY([]).[3]] could not be implemented; > planner state: > > Root: rel#92358:Subset#11.LOGICAL.ANY([]).[3] > Original rel: > > > The rest is pages of information related to the query, the data etc. I > can send that separately if you are interested. > > > I guess, before sending all of that, are nested window queries just > not alloweable? > > > If that is the case, what are some alternative approaches? > > > Thanks, > > John >
Documentation Issue
Hey all, I was looking for some tools to help me add/subtract dates, and I went to: https://drill.apache.org/docs/date-time-functions-and-arithmetic/ TIMESTAMPDIFF and TIMESTAMPADD where there, and I got excited, I organized my query, and tried to run them and got the error below. So I tried troubleshooting. 30 minutes later, after reviewing my query, reviewing the docs, reviewing all the things, I came here, as I did that, I see there is a new Drill Release... hmmm. On a hunch, I command+F and sure enough, the function I was trying to use was just added in 1.15, (I am running 1.14) . So, A. I think the anytime a doc is updated to reflect a new function, we should add "Added in Drill version 1.15" The error I got didn't tell me the function didn't exist, and was not helpful. This caused me to go down a 30 minute rabbit hole. This is a bad user experience. B. I see that sys.functions is in the 1.15 release. This is awesome. I think we should add a min_drill_version column to this though. For those functions that are added beyond 1.15, we should know where they sit. This will help us create a process that can auto update Documentation as well. Anytime a new function is added, we can run a query that shows the those not in the docs. Thoughts? SYSTEM ERROR: AssertionError: todo: implement syntax SPECIAL(Reinterpret(-($0, ITEM($2, 'charge_state_ts'
Nested Window Queries
Is there a limitation on nesting of of Window Queries? I have a query where I am using an event stream, and the changing of a value to indicate an event. (The state goes from disconnected, to charging, to complete, it reports many times in each of those states, but I am using lag(state, 1) over (order by event_ts) to find those records where current record is say complete and previous record was charging to indicate that the state has changed. This works well. Now I am trying to take that result set, and do another window to find the delta time between when the charging started and when the charging completed, you would think that a nested query showing the state change times, and then a select lag(event_ts, 1) over (order by event_ts) would be able to get me both the current time (when the charging was complete) and the previous record event_ts (when the charging started as outputted by the subquery). However, I am getting a verbose confusing error that perhaps we can help figure out... The first part is: SYSTEM ERROR: CannotPlanException: Node [rel#92358:Subset#11.LOGICAL.ANY([]).[3]] could not be implemented; planner state: Root: rel#92358:Subset#11.LOGICAL.ANY([]).[3] Original rel: The rest is pages of information related to the query, the data etc. I can send that separately if you are interested. I guess, before sending all of that, are nested window queries just not alloweable? If that is the case, what are some alternative approaches? Thanks, John
