How to export a csv text file from Vertica using vsql

Posted on by
Reply

This vsql script shows how to create a text file which has no header, is delimitted by a comma, and has each line ending with a Carriage Return + Line Feed which is the norm for Windows.

\pset border 0
\pset format unaligned
\pset footer
\pset recordsep '\r\n'
\pset fieldsep ','
\t
\C
\o /tmp/MyOutPutFile.txt
select table_schema, table_id, table_name from tables limit 3;
\o

If you want to include the column names as a header row, omit the “\t”
If you omit the “\pset recordsep” option Vertica will use a default value of ‘\n’. This Line Feed character is the norm for Linux.
If you omit the “\pset fieldsep” option Vertica will use a default value of ‘|’ , the pipe symbol.
If you want to create a tab delimited file, use:

\pset fieldsep '\t'

Useful Fonts for Programmers

Posted on by
Reply

Some fonts are more comfortable on the eyes than others. For example, if you want to drive a graphics designer crazy, use Comic Sans. If you write screen plays you will want to use Courier Prime.

Programmers will will want to consider Source Code Pro and Source Sans Pro.

What is the difference between these two fonts? According to Adobe:

Source Sans Pro was designed by Paul D. Hunt as Adobe’s first open source typeface family, conceived primarily as a typeface for user interfaces. Source Sans Pro draws inspiration from the clarity and legibility of twentieth-century American gothic typeface designs. Distilling the best archetypical qualities of these models, Paul followed a rational design approach by simplifying glyph shapes by paring them to their essential form. However, in order to more easily differentiate similar letter shapes (such as uppercase I and lowercase L), some additional details have been added. Besides providing such explicitly clarity in short text strings, another fundamental design consideration was to create a typeface that reads well in extended settings. This can be seen in the general proportions: Source Sans Pro has been designed with a more generous width than many other comparable gothics, and its shorter majuscule letters, combined with minuscule letters with longer extenders, create a more pleasant reading texture in longer text passages.

 

Source Code Pro was designed by Paul D. Hunt as a companion to Source Sans. This complementary family was adapted from the Source design due to a request to create a monospaced version for coding applications. Source Code preserves the design features and vertical proportions of Source Sans, but alters the glyph widths so that they are uniform across all glyphs and weights. Although this family was designed specifically for coding environments, for which a regular weight will typically suffice, Source Code has been made available in the same weight range as the corresponding Source Sans design.

How to load a file into Vertica using DbVisualizer

Posted on by
Reply

Vertica v6 provides the capability to upload a file from your local computer into Vertica using the COPY… FROM LOCAL syntax.

Step 1: Create the file C:\Temp\TextFile.txt

Position, WordString
1, The
2, fox
3, is
4, red

Step 2: Open DbVisualizer and create a table in Vertica

CREATE TABLE SentenceStructure 
( Position INT
 ,WordString VARCHAR(100) );

Step 3: Load the file into the table by running this SQL in dbVisualizer

COPY  SentenceStructure 
( Position
 ,WordString
)
FROM LOCAL 'C:\Temp\TextFile.txt'
WITH DELIMITER E','    -- comma delimited
SKIP 1    -- skip header row
;

Step 4: Validate

SELECT * FROM SentenceStructure ORDER BY 1;

Top Users in Vertica

Posted on by
Reply

Here’s a helpful view that will show you the top users of your Vertica database over the past 24 hours. You can tweak it to sort by the number of queries, query duration, or memory usage.

CREATE VIEW etl.TopUsersPast24Hours AS
 SELECT user_name,
        sum(1) AS NumQueries,
        sum(error_count) AS NumErrors,
        sum(request_duration_ms) AS request_duration_ms_Total,
        (sum(memory_acquired_mb))::int AS memory_acquired_mb_Total,
        min(FirstQueryDateTime) AS FirstQueryDateTime,
        max(LastQueryDateTime) AS LastQueryDateTime,
        min(request_duration_ms) AS request_duration_ms_Min,
        (min(request_duration_ms_25thPct))::int AS request_duration_ms_25thPct,
        (avg(request_duration_ms))::int AS request_duration_ms_Avg,
        (min(request_duration_ms_Median))::int AS request_duration_ms_Median,
        (min(request_duration_ms_75thPct))::int AS request_duration_ms_75thPct,
        max(request_duration_ms) AS request_duration_ms_Max,
        (min(memory_acquired_mb))::int AS memory_acquired_mb_Min,
        (min(memory_acquired_mb_25thPct))::int AS memory_acquired_mb_25thPct,
        (avg(memory_acquired_mb))::int AS memory_acquired_mb_Avg,
        (min(memory_acquired_mb_Median))::int AS memory_acquired_mb_Median,
        (min(memory_acquired_mb_75thPct))::int AS memory_acquired_mb_75thPct,
        (max(memory_acquired_mb))::int AS memory_acquired_mb_Max
 FROM ( SELECT query_requests.user_name,
        query_requests.request_duration_ms,
        query_requests.memory_acquired_mb,
        coalesce(query_requests.error_count, 0) AS error_count,
        query_requests.start_timestamp AS FirstQueryDateTime,
        query_requests.end_timestamp AS LastQueryDateTime,
        percentile_cont(0.25::float) WITHIN GROUP(ORDER BY query_requests.request_duration_ms) OVER (PARTITION BY query_requests.user_name) AS request_duration_ms_25thPct,
        percentile_cont(0.5::float) WITHIN GROUP(ORDER BY query_requests.request_duration_ms) OVER (PARTITION BY query_requests.user_name) AS request_duration_ms_Median,
        percentile_cont(0.75::float) WITHIN GROUP(ORDER BY query_requests.request_duration_ms) OVER (PARTITION BY query_requests.user_name) AS request_duration_ms_75thPct,
        percentile_cont(0.25::float) WITHIN GROUP(ORDER BY query_requests.memory_acquired_mb) OVER (PARTITION BY query_requests.user_name) AS memory_acquired_mb_25thPct,
        percentile_cont(0.5::float) WITHIN GROUP(ORDER BY query_requests.memory_acquired_mb) OVER (PARTITION BY query_requests.user_name) AS memory_acquired_mb_Median,
        percentile_cont(0.75::float) WITHIN GROUP(ORDER BY query_requests.memory_acquired_mb) OVER (PARTITION BY query_requests.user_name) AS memory_acquired_mb_75thPct
 FROM v_monitor.query_requests
 WHERE ((query_requests.request_type ~~ 'QUERY'::varchar(5)) AND (query_requests.start_timestamp >= "timestampadd"('hour'::varchar(4), (-24), now())))
 ORDER BY query_requests.user_name) T1
 GROUP BY user_name
 ORDER BY sum(1) DESC;

SQL Server Agent Job Dependencies

Posted on by

Here are two different ways to create dependencies between jobs in SQL Server Agent.

Scenario 1: Have Job #1 call Job #2.
Create a TSQL step in Job #1 that will execute this SQL in the msdb database.


EXEC msdb.dbo.sp_start_job 'Job Name Goes Here'

Scenario 2: Have Job #2 wait for Job #1 to finish.
Make the first job step Job #2 a TSQL step that executes in the msdb database.
This WHILE loop will check every 15 minutes to see if Job #1 is finished.
It assumes that Job #1 runs once a day and finishes in under 12 hours.


 
WHILE (
SELECT COUNT(*) AS NumJobsRunning
  FROM  msdb.dbo.sysjobactivity A
  JOIN msdb.dbo.sysjobs B
    ON A.job_id = B.job_id
  WHERE 1=1
    AND A.[run_requested_date] >= dateadd(hh,-12,getdate())
    AND A.[stop_execution_date] is null
    AND (  B.[name] LIKE 'Job #1%' )
) > 0
BEGIN
    PRINT 'Waiting 15 minutes starting at ' + CONVERT(VARCHAR,GETDATE(),120)
    WAITFOR DELAY '00:15:00'
END      
    PRINT 'Started at ' + CONVERT(VARCHAR,GETDATE(),120)
;

Setting CPU Scaling to Performance on a Vertica Linux Cluster

Posted on by
Reply

Vertica’s documentation for v6.0.1 recommends that you disable CPU frequency scaling, and have the CPUs run in performance mode.

You can find out what speed your CPUs are designed to run at by checking the host_resources table:


SELECT
        host_name
       ,processor_count
       ,processor_core_count
       ,processor_description        
   FROM
        v_monitor.host_resources
ORDER BY host_name;

host_name      processor_count  processor_core_count  processor_description                            
-------------  ---------------  --------------------  -----------------------------------------------  
10.139.120.61  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz  
10.139.120.62  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz  
10.139.120.63  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz  
10.139.120.64  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz  
10.139.120.65  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz  
10.139.120.66  2                24                    Intel(R) Xeon(R) CPU           X5690  @ 3.47GHz

You can find out what speed the CPUs are actually running at by running this query. Since this query is based on one of the data collector tables there is no guarantee that this query will work in other versions of Vertica.


SELECT *
  FROM (
         SELECT *
               ,ROW_NUMBER() OVER (PARTITION BY node_name
                                       ORDER BY start_time DESC) AS RowNum
         FROM dc_cpu_aggregate_by_hour ) ALIAS
WHERE RowNum < 10 
ORDER BY node_name, RowNum;

If the CPUs are not running in performance mode you can use a bash script similar to this one to set them to performance mode. This script will need to be run whenever the box reboots.


#!/bin/bash
for i in {0..23}
do
    cd /sys/devices/system/cpu/cpu${i}/cpufreq
    echo "performance" >  /sys/devices/system/cpu/cpu${i}/cpufreq/scaling_governor
    pwd
    cat  /sys/devices/system/cpu/cpu${i}/cpufreq/scaling_governor
done

The usual caveats about tweaking for your distro of linux and the number of CPUs on your box apply, and I'm not responsible for any damage caused to your linux boxes.

How to script out users, roles, and permissions in Vertica

Posted on by
Reply

A common DBA task is to make a copy of the structure of a production database and then restore it on a development server.

You can script out the table, view, and function structures using the EXPORT_CATALOG command

 SELECT EXPORT_CATALOG('','DESIGN_ALL');

However, when you export the catalog you it will not include information on resource pools, roles, users, and permissions. Here are some code snips you can use to script out this additional information:

 
------------------------------------------------------------
-- Create Resource Pools
------------------------------------------------------------
SELECT    'CREATE RESOURCE POOL ' || name         
        || CASE WHEN memorysize                IS NULL THEN ' ' ELSE ' MEMORYSIZE '                 || '''' || memorysize               || '''' END      
        || CASE WHEN maxmemorysize = ''                THEN ' ' ELSE ' MAXMEMORYSIZE '              || '''' || maxmemorysize            || '''' END 
        || CASE WHEN executionparallelism     = 'AUTO' THEN ' ' ELSE ' EXECUTIONPARALLELISM '       || '''' || executionparallelism     || '''' END 
        || CASE WHEN NULLIFZERO(priority)      IS NULL THEN ' ' ELSE ' PRIORITY '                   || '''' || priority                 || '''' END 
        || CASE WHEN runtimepriority           IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY '            ||         runtimepriority                  END 
        || CASE WHEN runtimeprioritythreshold  IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD '   ||         runtimeprioritythreshold         END 
        || CASE WHEN queuetimeout              IS NULL THEN ' ' ELSE ' QUEUETIMEOUT '               ||         queuetimeout                     END   
        || CASE WHEN maxconcurrency            IS NULL THEN ' ' ELSE ' MAXCONCURRENCY '             ||         maxconcurrency                   END 
        || CASE WHEN runtimecap                IS NULL THEN ' ' ELSE ' RUNTIMECAP '                 || '''' || runtimecap               || '''' END       
        || ' ; '       
FROM v_catalog.resource_pools
WHERE NOT is_internal
ORDER BY name;

------------------------------------------------------------
-- Roles
------------------------------------------------------------
SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CR 
FROM v_catalog.roles
WHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser')
ORDER BY 1;

------------------------------------------------------------
-- Users
------------------------------------------------------------
SELECT 'CREATE USER ' || user_name  || ' RESOURCE POOL ' || resource_pool ||  ' ;'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

------------------------------------------------------------
-- Add users to Roles
------------------------------------------------------------
SELECT 'GRANT ROLE ' || all_roles || ' TO ' || user_name || ';'
FROM v_catalog.users
ORDER BY 1;

------------------------------------------------------------
-- Grant permissions to Users/Roles
------------------------------------------------------------
SELECT 'GRANT ' || REPLACE(privileges_description,'*','')  || 
       '   ON ' || COALESCE(object_schema,'') || '.' || object_name ||
       '   TO ' || grantee       || ';'
FROM v_catalog.grants
WHERE COALESCE(privileges_description,'') > ''
ORDER BY 1;

How to use the COPY command to load a file in Vertica

Posted on by
Reply

Our objective is to take a file that looks like this…


$ cat flatfile.txt
AcctID, IsValid, IsLate, StartDate , NextDate           , EndDate           , Name  , Amount
1     , true   , true  , 2012-10-05, 2012-12-25 15:55:21 , 1/1/2013 2:54AM   , NULL  , 10.00
2     , true   , false , 2012-10-06, 2012-12-25 17:24    , 2013-01-01 1:44PM ,       ,  5.12
3     ,        , false , 2011-10-05, 2012-01-25 2:53 PM  , 01/01/2013        , Rocky ,  6.70
4     , false  , true  , 2010-10-01, 2012-12-25 01:24    , 2013-01-01        ,       , 15.30

…and to load it into a table…


COPY  MyTable
(
    AcctID
   ,IsValidSource             FILLER VARCHAR(5)
   ,ISValid                   AS CASE WHEN IsValidSource iLIKE '%TRUE%' THEN 1 ELSE 0 END
   ,IsLateSource              FILLER VARCHAR(5)
   ,IsLate                    AS CASE WHEN IsLateSource  iLIKE '%TRUE%' THEN 1 ELSE 0 END
   ,StartDateSource           FILLER DATE
   ,StartDate                 AS COALESCE(StartDateSource,'9999-12-31')
   ,NextDateSource            FILLER DATETIME
   ,NextDate                  AS COALESCE(NextDateSource,'9999-12-31')
   ,EndDateSource             FILLER DATETIME
   ,EndDate                   AS COALESCE(EndDateSource,'9999-12-31')
   ,EndDateYYYYMMDD           AS TO_CHAR(COALESCE(EndDateSource,'9999-12-31'),'YYYYMMDD')::INT
   ,EndDateDateYYYYMMDDHHMMSS AS TO_CHAR(COALESCE(EndDateSource,'9999-12-31'),'YYYYMMDDHH24MISS')::INT
   ,NameSource                FILLER VARCHAR(100)
   ,Name                      AS CASE WHEN NameSource ilike '%NULL%' THEN '' ELSE TRIM(NameSource) END
   ,AmountSource              FILLER MONEY
   ,Amount                    AS COALESCE(AmountSource,0)
   ,WeirdNumber               AS (AcctID*1000000000 + COALESCE(AmountSource,0)*100)::INT
)
FROM :SourceFileName
WITH DELIMITER ','
SKIP 1
STREAM NAME :SourceFileName
;

…so that it looks like this:


-[ RECORD 1 ]-------------+--------------------------
RowID                     | 1
AcctID                    | 1
IsValid                   | 1
IsLate                    | 1
StartDate                 | 2012-10-05
NextDate                  | 2012-12-25 15:55:21
EndDate                   | 2013-01-01 02:54:00
EndDateYYYYMMDD           | 20130101
EndDateDateYYYYMMDDHHMMSS | 20130101025400
Name                      |
Amount                    | 10.0000
InsertDate                | 2012-10-21 22:47:30.84269
RandomUniform0To1         | 0.922055047936738
RandomInt100              | 0
WeirdNumber               | 1000001000
-[ RECORD 2 ]-------------+--------------------------
RowID                     | 2
AcctID                    | 2
IsValid                   | 1
IsLate                    | 0
StartDate                 | 2012-10-06
NextDate                  | 2012-12-25 17:24:00
EndDate                   | 2013-01-01 13:44:00
EndDateYYYYMMDD           | 20130101
EndDateDateYYYYMMDDHHMMSS | 20130101134400
Name                      |
Amount                    | 5.1200
InsertDate                | 2012-10-21 22:47:30.84269
RandomUniform0To1         | 0.856217089109123
RandomInt100              | 32
WeirdNumber               | 2000000512
-[ RECORD 3 ]-------------+--------------------------
RowID                     | 3
AcctID                    | 3
IsValid                   | 0
IsLate                    | 0
StartDate                 | 2011-10-05
NextDate                  | 2012-01-25 14:53:00
EndDate                   | 2013-01-01 00:00:00
EndDateYYYYMMDD           | 20130101
EndDateDateYYYYMMDDHHMMSS | 20130101000000
Name                      | Rocky
Amount                    | 6.7000
InsertDate                | 2012-10-21 22:47:30.84269
RandomUniform0To1         | 0.982697854051366
RandomInt100              | 90
WeirdNumber               | 3000000670
-[ RECORD 4 ]-------------+--------------------------
RowID                     | 4
AcctID                    | 4
IsValid                   | 0
IsLate                    | 1
StartDate                 | 2010-10-01
NextDate                  | 2012-12-25 01:24:00
EndDate                   | 2013-01-01 00:00:00
EndDateYYYYMMDD           | 20130101
EndDateDateYYYYMMDDHHMMSS | 20130101000000
Name                      |
Amount                    | 15.3000
InsertDate                | 2012-10-21 22:47:30.84269
RandomUniform0To1         | 0.425485474290326
RandomInt100              | 55
WeirdNumber               | 4000001530

Here is the SQL code. Pay attention to the commentary, it’s really important.


-----------------------------------------------------------------------------------
-- How to Load in a File and do some basic QA.
-- 
-- Note: We want the transaction to roll back if there is an error.
-- Consequently, AUTOCOMMIT is set to OFF
--           and ON_ERROR_STOP is set to ON
-- Otherwise, when an error is encountered Vertica would 
-- move on to the next statement instead of crashing and rolling back.
-- \x transposes the output.
-----------------------------------------------------------------------------------
\set AUTOCOMMIT off
\set ON_ERROR_STOP on
\timing
\x

-----------------------------------------------------------------------------------
-- Capture the Start Time
-----------------------------------------------------------------------------------
\set StartDateTime      `date "+%Y%m%d%H%M%S"`
\set StepStartDateTime  `date "+%Y%m%d%H%M%S"`


-----------------------------------------------------------------------------------
-- Once you've created the table and loaded in some sample data
-- then you need to RUN DATABASE DESIGNER.
-- Look at the DD output and follow it's guidance for the
-- encoding type to use for each column.
-- The easiest way to do this is to create another super projection
-- and drop the original super projection if you need to keep the data in 
-- the table.  If you don't specify an encoding type for each column then your
-- data will not be compressed, wasting disk space and processing time.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- TRANSACTION_TIMESTAMP()
-- This function returns the start time of the current transaction;
-- the value does not change during the transaction. The intent is
-- to allow a single transaction to have a consistent notion of
-- the "current" time, so that multiple modifications within
-- the same transaction bear the same timestamp.
-----------------------------------------------------------------------------------

DROP TABLE IF EXISTS MyTable ;

CREATE TABLE MyTable
(
    RowID                     AUTO_INCREMENT NOT NULL ENCODING COMMONDELTA_COMP
   ,AcctID                    INT            NOT NULL ENCODING AUTO
   -- Prefer INT to Binary because it is easier to SUM them up.
   ,IsValid                   INT            NOT NULL ENCODING RLE
   ,IsLate                    INT            NOT NULL ENCODING RLE
   ,StartDate                 DATE           NOT NULL
   ,NextDate                  DATETIME       NOT NULL
   ,EndDate                   DATETIME
   ,EndDateYYYYMMDD           INT            NOT NULL
   ,EndDateDateYYYYMMDDHHMMSS INT            NOT NULL
   ,Name                      VARCHAR(100)
   ,Amount                    MONEY          NOT NULL
   ,InsertDate                DATETIME       NOT NULL DEFAULT TRANSACTION_TIMESTAMP()
   ,RandomUniform0To1         FLOAT          NOT NULL DEFAULT RANDOM()
   ,RandomInt100              INT            NOT NULL DEFAULT RANDOMINT(100)
   ,WeirdNumber               INT            NOT NULL
   ,PRIMARY KEY (AcctID)
)
ORDER BY IsValid, IsLate, Amount, Name, StartDate, NextDate, EndDate, AcctID
-- UNSEGMENTED ALL NODES  -- Use UNSEGMENTED for Dimension Tables
SEGMENTED BY MODULARHASH(AcctID) ALL NODES -- Use SEGMENTED for Fact Tables
KSAFE 1 -- Put desired K-safety factor here.
;


-----------------------------------------------------------------------------------
-- Log Time
-----------------------------------------------------------------------------------
\set EndDateTime      `date "+%Y%m%d%H%M%S"`
SELECT  TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS') AS StartDateTime
       ,TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS') AS StepStartDateTime
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS IncrementalSeconds
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS TotalSeconds	   
;
\set :StepStartDateTime  `date "+%Y%m%d%H%M%S"`



-----------------------------------------------------------------------------------
-- I like to use variables for my input files
-- Note, you could also use wildcards such as
-- /home/dbadmin/playground/flat*.txt
-----------------------------------------------------------------------------------
\set SourceFileName '''' /home/dbadmin/playground/flatfile.txt ''''

-----------------------------------------------------------------------------------
-- Read in the file
-- iLIKE is a case insenstive comparison
-- Expressions can only contain source columns, not derived columns
-- StreamName makes it easy to find stats in
-- v_monitor.load_status table
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- I like qecho because it logs the comments in the log file, making it easy to
-- trace exactly where the program hit an Error.
\qecho =============================================================================
\qecho  Load the file
\qecho =============================================================================

COPY  MyTable
(
    AcctID
   ,IsValidSource             FILLER VARCHAR(5)
   ,ISValid                   AS CASE WHEN IsValidSource iLIKE '%TRUE%' THEN 1 ELSE 0 END
   ,IsLateSource              FILLER VARCHAR(5)
   ,IsLate                    AS CASE WHEN IsLateSource  iLIKE '%TRUE%' THEN 1 ELSE 0 END
   ,StartDateSource           FILLER DATE
   ,StartDate                 AS COALESCE(StartDateSource,'9999-12-31')
   ,NextDateSource            FILLER DATETIME
   ,NextDate                  AS COALESCE(NextDateSource,'9999-12-31')
   ,EndDateSource             FILLER DATETIME
   ,EndDate                   AS COALESCE(EndDateSource,'9999-12-31')
   ,EndDateYYYYMMDD           AS TO_CHAR(COALESCE(EndDateSource,'9999-12-31'),'YYYYMMDD')::INT
   ,EndDateDateYYYYMMDDHHMMSS AS TO_CHAR(COALESCE(EndDateSource,'9999-12-31'),'YYYYMMDDHH24MISS')::INT
   ,NameSource                FILLER VARCHAR(100)
   ,Name                      AS CASE WHEN NameSource ilike '%NULL%' THEN '' ELSE TRIM(NameSource) END
   ,AmountSource              FILLER MONEY
   ,Amount                    AS COALESCE(AmountSource,0)
   ,WeirdNumber               AS (AcctID*1000000000 + COALESCE(AmountSource,0)*100)::INT
)
FROM :SourceFileName
WITH DELIMITER ','
SKIP 1
STREAM NAME :SourceFileName
;


-----------------------------------------------------------------------------------
-- Log Time
-----------------------------------------------------------------------------------
\set EndDateTime      `date "+%Y%m%d%H%M%S"`
SELECT  TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS') AS StartDateTime
       ,TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS') AS StepStartDateTime
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS IncrementalSeconds
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS TotalSeconds	   
;
\set :StepStartDateTime  `date "+%Y%m%d%H%M%S"`


-----------------------------------------------------------------------------------
-- QA: Make sure all rows loaded and rollback if there was
--     an error.
-----------------------------------------------------------------------------------

\qecho =============================================================================
\qecho Raise an Error condition by selecting 1/0 if any rows were rejected
\qecho IF GET_NUM_REJECTED_ROWS()<>0 THEN 1-SIGN(GET_NUM_REJECTED_ROWS())) = 0
\qecho =============================================================================
SELECT
		 GET_NUM_REJECTED_ROWS() AS NumRejectedRows
		,GET_NUM_ACCEPTED_ROWS() AS NumAcceptedRows
;

SELECT 1 / (1-SIGN(GET_NUM_REJECTED_ROWS()));

\qecho =============================================================================
\qecho  Look at Load Streams Table
\qecho =============================================================================
SELECT * FROM v_monitor.load_streams
WHERE stream_name like :SourceFileName ;

\qecho =============================================================================
\qecho  QA: IsValid,IsLate contain only binary values.
\qecho  COUNT(*)=0 if no records returned
\qecho =============================================================================
SELECT 1/(1-SIGN(COUNT(*)))
FROM (SELECT * 
        FROM MyTable
      WHERE IsValid > 1
	     OR IsValid < 0
		 OR IsLate  > 1
		 OR IsLate  < 0
) AS T1;	


\qecho ============================================================================
\qecho Since Vertica does not check constraints when loading data,
\qecho raise an error and rollback if primary key constraint is violated.
\qecho ============================================================================
SELECT 1/(1-SIGN(COUNT(*)))
FROM (SELECT  ANALYZE_CONSTRAINTS ('MyTable')) AS T1;


-----------------------------------------------------------------------------------
-- Log Time
-----------------------------------------------------------------------------------
\set EndDateTime      `date "+%Y%m%d%H%M%S"`
SELECT  TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS') AS StartDateTime
       ,TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS') AS StepStartDateTime
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS IncrementalSeconds
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS TotalSeconds	   
;
\set :StepStartDateTime  `date "+%Y%m%d%H%M%S"`


-- SELECT 1/0 ;   -- Uncomment this line to Raise an Error 

\qecho ============================================================================
\qecho Run ANALYZE_STATISTICS 
\qecho Note:
\qecho THIS IS THE SAME AS A COMMIT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
\qecho ============================================================================
SELECT ANALYZE_STATISTICS('MyTable');


-----------------------------------------------------------------------------------
-- Log Time
-----------------------------------------------------------------------------------
\set EndDateTime      `date "+%Y%m%d%H%M%S"`
SELECT  TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS') AS StartDateTime
       ,TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS') AS StepStartDateTime
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StepStartDateTime , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS IncrementalSeconds
       ,DATEDIFF(SECOND, TO_TIMESTAMP( :StartDateTime     , 'YYYYMMDDHH24MISS')
	                   , TO_TIMESTAMP( :EndDateTime , 'YYYYMMDDHH24MISS') 
		) AS TotalSeconds	   
;
\set :StepStartDateTime  `date "+%Y%m%d%H%M%S"`


-----------------------------------------------------------------------------------
-- Did it Work?
-----------------------------------------------------------------------------------
SELECT * FROM MyTable ORDER BY AcctID;

-----------------------------------------------------------------------------------
-- Cleanup (don't do this in production code).
-----------------------------------------------------------------------------------
DROP TABLE IF EXISTS MyTable ;

\q

Power Pivot for Excel 2010 — Great FREE Tool

Posted on by
Reply

If you’re an analyst who uses Excel 2010 and Pivot Tables then download and install Power Pivot right now! You’ll think Pivot tables are 20th century anachronisms after using Power Pivot. If you’re using Excel 2013 then you already own this great tool.

PowerPivot is a data mashup and data exploration tool that allows you to integrate and analyze smallish (<1mm record) data sets using an in memory column store on your computer.  It links together data from different sources and uses a language call DAX to present information in a Pivot Table / Pivot Chart interface. Useful tutorials can be found at http://powerpivot-info.com/

Excel analysts familiar with Pivot Tables and Excel functions will be able to quickly climb the Power Pivot learning curve. Once you switch to Power Pivot you don’t have to deal with trying to mash all of your data into one tab for Pivot Table Analysis.  And you’ll be able to quickly analyze much larger data sets.