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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
