Thanks, Jeremy! That was a lot easier than I expected.
If you'd care to take a look, my changes are up
here:
https://github.com/nexiahome/sequel-vertica/commit/832442163a45b797a328a208529ce7d8563eb746
I think it looks pretty good, but there's probably a better way to do the
quoting around the AS clause.
-=Eric
On Wednesday, December 9, 2015 at 9:01:28 PM UTC-7, Jeremy Evans wrote:
>
> On Wednesday, December 9, 2015 at 7:48:53 PM UTC-8, Eric Schwartz wrote:
>>
>> HPE's Vertica database has a proprietary extension used for generating
>> timeseries data. I am trying to figure out how to invoke it from Sequel
>> with minimal hullabaloo.
>>
>> Here's an example of the kind of query I'm trying to build:
>>
>> SELECT slice_time,
>> round(ts_last_value(a_column IGNORE NULLS, 'linear')) AS a_col
>> FROM(
>> select occurred_at,
>> a_column
>> FROM a_table
>> WHERE something = 'something else'
>> ) subq -- this is not used, but the parser requires a name here
>> TIMESERIES slice_time AS '1 second' OVER(ORDER BY occurred_at)
>>
>> I can build the subquery with:
>>
>> subquery = DB.from(:a_table).select(:occurred_at,
>> :a_column).where(something: 'something_else')
>>
>> I can then compose it into the outer query with
>>
>> DB.from(subquery).select { |o| [o.slice_time,
>> o.round(o.ts_last_value(Sequel.lit("a_column IGNORE NULLS"),
>> 'linear')).as(:a_col)] }
>>
>> but I can't figure out how to add the TIMESERIES clause to the query. I
>> don't mind dropping into Sequel.lit if required (as you can see), but I
>> need to get a dataset back, since this query will itself be included into
>> another query as a subquery. Any suggestions are greatly appreciated.
>>
>
> This is really something that should be added to the vertica adapter. You
> want to add the ability to call a method to append an additional clause.
> Look at the Dataset.def_sql_method calls in the shared adapters that ship
> with Sequel for an example of how to do this. It should be possible to
> hack it in:
>
> DB.extend_datasets do
> Dataset.def_sql_method(self, :select, %w'with select distinct columns
> from join timeseries where group having compounds order limit lock'
>
> def timeseries(s)
> clone(:timeseries => s)
> end
>
> private
>
> def select_timeseries_sql(sql)
> if opts[:timeseries]
> sql << 'TIMESERIES ' << opts[:timeseries]
> end
> end
> end
>
> That's not necessarily a good way to do it, as you have to specify the
> timeseries as a string, and I didn't even try it, but it should give you
> the basic idea for how to implement it.
>
> BTW, if there's a better way to do the round(ts_last_value(...)) bit, I'm
>> not super-thrilled with how that looks (it works fine, though).
>>
>
> That doesn't seem like a bad way to do it. The only faster way would be
> not having the virtual row block accept an argument.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
