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.

2.

If non-Vertica data sources are involved, enable data ship with Vertica as the target. For more information, see Finishing Data Ship Configuration for Data Sources.

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.