Skip to content

Instantly share code, notes, and snippets.

@jackghm
jackghm / Vertica_Query_Times
Created February 25, 2015 18:26
Vertica Query request times over time by user
-- 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
@jackghm
jackghm / Vertica_Dates
Created February 25, 2015 18:39
Vertica Dates
-- 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
jackghm / HP-Vertica-Date-Time.sql
Created August 2, 2015 22:54
Working with HP-Vertica date and time
/*
#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
jackghm / nodeSegUDFgen.scala
Created September 11, 2016 06:46
nodeSegUDFgen.scala
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
*/