Using Time Series Functions for Vertica Data Ship Targets
There are a few special steps to consider when defining views that include Vertica time series functions. The following steps are guidelines for how you can make use of the following Vertica time series functions within a data ship target view where the SQL will be run entirely on the Vertica database:
• TIMESERIES SELECT clause which names a time slice
• TS_FIRST_VALUE
• TS_LAST_VALUE
Restrictions
• Running the Vertica time series functions in any data source besides Vertica results in processing errors.
• Time functions with time zone and timestamp functions with time zone, do not have micro- and nanosecond precision, nor time zone information. A workaround for that limitation includes using the CAST function to have the time zone value converted to a VARCHAR value.
To use Vertica time series functions
1. Create or identify a view from any data source for which you want to use the time series functions.
3. Enable data ship with Vertica as a source.
4. Manipulate your view code to make use of the Vertica time series function. Refer to Vertica Programmers Guide and Vertica Reference Guide for details of the time series syntax. The following view SQL is included as a sample of one way to write a data ship query that uses Vertica time series functions:
SELECT
store_key, cc_class, case when store_key is not null then store_key end mycase
FROM
/users/composite/test/sources/vertica50/store/store_dimension
inner join
/users/composite/test/sources/netezza50/TPCDS100_2/TPCDS100/PROD/CALL_CENTER
ON cc_call_center_sk = store_key
timeseries ts as '3 hour'
over (partition by store_key, first_open_date, cc_class
order by cast(cc_rec_start_date as timestamp))
order by case when ts is not null then ts end , store_key
5. Test the view to make sure that all the SQL is pushed and processed on the Vertica database.