Snippet Redshift

With snippet Redshift, you can free your mind

  1. Avoid the pide by Zero error

    Using NULLIF() to prevent pide by Zero error

    SELECT pidend / NULLIF(pisor, 0) FROM tbl1; -- result NULL if pisor is NULL
    SELECT COALESCE(pidend / NULLIF(pisor, 0), 0) FROM tbl2 -- result 0 if pisor is NULL
    
  2. Alter table to change column data type

    Currently, there is no way to change column data type in redshift database. Here is a trick

    ALTER TABLE tbl1 ADD COLUMN created_at_new TIMESTAMP NOT NULL DEFAULT GETDATE();
    UPDATE tbl1 SET created_at_new = created_at;
    ALTER TABLE tbl1 DROP COLUMN created_at;
    ALTER TABLE tbl1 RENAME COLUMN created_at_new TO created_at;
    
  3. System Tables Reference

    -- displays the records of all Amazon Redshift load errors
    SELECT convert_timezone('Asia/Saigon', starttime), * FROM stl_load_errors ORDER BY query DESC;
    
    -- returns information to track or troubleshoot a data load
    SELECT * FROM stl_load_commits where filename like '%tickit%' ORDER BY query DESC;
    
    -- view any current updates on tables in the database
    -- Amazon Redshift locks tables to prevent two users FROM updating the same table at the same time
    SELECT convert_timezone('Asia/Saigon', last_update), * FROM stv_locks ORDER BY last_update DESC;
    
  4. First or Last Value

    -- lower performance if large data set
    SELECT DISTINCT a,
           FIRST_VALUE(b) OVER (PARTITION BY a ORDER BY updated_at DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS b
           FROM tbl
    WHERE updated_at BETWEEN '2016-12-01' AND '2017-01-01';
    
    -- balance performance for small or large data set
    SELECT t.a,
           t.b
    FROM (SELECT a,
                 b,
                 ROW_NUMBER() OVER (PARTITION BY a ORDER BY updated_at DESC) AS rk
          FROM tbl
          WHERE updated_at BETWEEN '2016-12-01' AND '2017-01-01') t
    WHERE t.rk = 1;
    

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections