jackghm

import java.io._
/*
  Argument 0 is the output file from the following
  vsql -h hostNme -U jackg -w <redacted> -d pstl -At -c "select get_projection_segments('jackg.testHashNodes_p1_b0');" -o /tmp/get_projection_segments.out

  Argument 1 is the output file from the following
  run vSQL to create an output file of the node names and Private IP address used to ssh too.
  typically we run on an Edge Node, then ssh to private Vertica host IP Address to execute local vsql commands
  vsql -h hostNme -U jackg -w <redacted> -d pstl -At -c "select b.node_name, a.ip_address from v_monitor.network_interfaces a join v_catalog.nodes b on (a.node_name = b.node_name) where b.node_state = 'UP' and a.interface = 'eno16777736' and a.ip_address_family = 'ipv4' order by a.node_name asc;" -o /tmp/NodeIP.out
 */

jackghm

/*
#1 always write datetime values into the database as UTC!!!
-- Reread that last statement
Vertica will store all datetime values as UTC values 
*/
-- I highly recommend storing an Integer column of the Day Date
select TO_CHAR(<% DATA_END_TIME %>::DATE - INTEGER '1', 'YYYYMMDD')::INTEGER AS date_id

SELECT CLOCK_TIMESTAMP() "Current Time";	-- return current time
SELECT NOW();   -- time since last session connection (or commit;) which may be an older time than now

jackghm

-- convert a date_id (e.g., DIM_DATE_GID) in a format like YYYYMMDD::INT to a timestamp
select TO_DATE(20141102::varchar, 'YYYYMMDD')::timestamp
select TO_DATE(DIM_DATE_GID::varchar, 'YYYYMMDD')::timestamp
-- convert date type to an int
select to_char(current_date(), 'YYYYMMDD')::int
-- Using Date math to find the number of days to use in DATEs as INT values.
-- This is helpful since months can have a varying number of days (e.g., Feb 28th or 29th, Sept. 30th or Dec 31st)
select 
 current_date() as Current_Date_as_Date
, to_char(current_date(),'YYYYMMDD')::integer as Current_Date_as_Integer

jackghm

-- Query request times over time by user
select distinct TheDay, user_name
, (min_dat / 1000) as min_sec, (max_dat / 1000) as max_sec
, (avg_dat / 1000) as avg_sec, (median_dat / 1000) as median_sec
, query_cnt
from (
select DATE(end_timestamp::timestamp) as TheDay, user_name
, min(request_duration_ms) over(partition by DATE(end_timestamp::timestamp), user_name ) min_dat 
, max(request_duration_ms) over(partition by DATE(end_timestamp::timestamp), user_name ) max_dat
, avg(request_duration_ms) over(partition by DATE(end_timestamp::timestamp), user_name ) avg_dat