Snowflake: the snowsql option OUTPUT_FORMAT to output results delimited and formatted

Refer:

• Snowflake: Using SnowSQL
• Snowflake: snowsql !options

The list of available options output_format= :

snowsql -q 'select...' -o output_format=psql
snowsql -q 'select...' -o output_format=plain
snowsql -q 'select...' -o output_format=simple
snowsql -q 'select...' -o output_format=grid
snowsql -q 'select...' -o output_format=fancy_grid
snowsql -q 'select...' -o output_format=orgtbl
snowsql -q 'select...' -o output_format=rst
snowsql -q 'select...' -o output_format=mediawiki
snowsql -q 'select...' -o output_format=html
snowsql -q 'select...' -o output_format=latex
snowsql -q 'select...' -o output_format=latex_booktabs
snowsql -q 'select...' -o output_format=pipe
snowsql -q 'select...' -o output_format=tsv 
snowsql -q 'select...' -o output_format=csv 

Examples:

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=psql
+---------------+--------------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|---------------+--------------+-------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=plain
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=simple
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
-------------  ------------  -----------
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=grid
+---------------+--------------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
+===============+==============+=============+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=fancy_grid
╒═══════════════╤══════════════╤═════════════╕
│ TABLE_CATALOG │ TABLE_SCHEMA │ TABLE_OWNER │
╞═══════════════╪══════════════╪═════════════╡
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
╘═══════════════╧══════════════╧═════════════╛

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=orgtbl
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|---------------+--------------+-------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=rst
=============  ============  ===========
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
=============  ============  ===========
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
=============  ============  ===========

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=mediawiki
{| class="wikitable" style="text-align: left;"
|+ <!-- caption -->
|-
! TABLE_CATALOG !! TABLE_SCHEMA !! TABLE_OWNER
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=html
<table>
<tr><th>TABLE_CATALOG</th><th>TABLE_SCHEMA</th><th>TABLE_OWNER</th></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
</table>

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=latex
\begin{tabular}{lll}
\hline
 TABLE\_CATALOG & TABLE\_SCHEMA & TABLE\_OWNER \\
\hline
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
\hline
\end{tabular}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=latex_booktabs
\begin{tabular}{lll}
\toprule
 TABLE\_CATALOG & TABLE\_SCHEMA & TABLE\_OWNER \\
\midrule
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
\bottomrule
\end{tabular}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=pipe
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|:--------------|:-------------|:------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=tsv
TABLE_CATALOG   TABLE_SCHEMA    TABLE_OWNER
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=csv
"TABLE_CATALOG","TABLE_SCHEMA","TABLE_OWNER"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"


USER@SERVER$

Snowflake: create OVERLAPS function

Create OVERLAPS (t1,t2,t3,t4) function at Snowflake database

Related links:

• Postgres SQL OVERLAPS
• Intersection of 1D segments
• Snowflake: split string into array, sort it, and return string.

Creating OVERLAPS  UDF function at Snowflake database:

CREATE OR REPLACE FUNCTION  overlaps(V_A_START FLOAT           , V_A_END FLOAT           , V_B_START FLOAT           , V_B_END FLOAT           )
 --create or replace FUNCTION overlaps(V_A_START FLOAT4          , V_A_END FLOAT4          , V_B_START FLOAT4          , V_B_END FLOAT4          )
 --create or replace FUNCTION overlaps(V_A_START FLOAT8          , V_A_END FLOAT8          , V_B_START FLOAT8          , V_B_END FLOAT8          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE          , V_A_END DOUBLE          , V_B_START DOUBLE          , V_B_END DOUBLE          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE PRECISION, V_A_END DOUBLE PRECISION, V_B_START DOUBLE PRECISION, V_B_END DOUBLE PRECISION)
 --create or replace FUNCTION overlaps(V_A_START REAL            , V_A_END REAL            , V_B_START REAL            , V_B_END REAL            )
 --create or replace FUNCTION overlaps(V_A_START DATE            , V_A_END DATE            , V_B_START DATE            , V_B_END DATE            )
 --create or replace FUNCTION overlaps(V_A_START DATETIME        , V_A_END DATETIME        , V_B_START DATETIME        , V_B_END DATETIME        )
 --create or replace FUNCTION overlaps(V_A_START TIMESTAMP       , V_A_END TIMESTAMP       , V_B_START TIMESTAMP       , V_B_END TIMESTAMP       )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_LTZ   , V_A_END TIMESTAMP_LTZ   , V_B_START TIMESTAMP_LTZ   , V_B_END TIMESTAMP_LTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_NTZ   , V_A_END TIMESTAMP_NTZ   , V_B_START TIMESTAMP_NTZ   , V_B_END TIMESTAMP_NTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_TZ    , V_A_END TIMESTAMP_TZ    , V_B_START TIMESTAMP_TZ    , V_B_END TIMESTAMP_TZ    )
RETURNS BOOLEAN NOT NULL
LANGUAGE javascript
AS     
$$ 
    RETURN (Math.max(V_A_START,V_A_END) >= Math.min(V_B_START,V_B_END) && Math.max(V_B_START,V_B_END) >= Math.min(V_A_START,V_A_END)) ; 
$$
;

These overloads will NOT work and fail rendering the following error: 

                 Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type.

-- create or replace FUNCTION overlaps(V_A_START NUMBER          , V_A_END NUMBER          , V_B_START NUMBER          , V_B_END NUMBER          )   
-- create or replace FUNCTION overlaps(V_A_START DECIMAL         , V_A_END DECIMAL         , V_B_START DECIMAL         , V_B_END DECIMAL         )
-- create or replace FUNCTION overlaps(V_A_START NUMERIC         , V_A_END NUMERIC         , V_B_START NUMERIC         , V_B_END NUMERIC         )
-- create or replace FUNCTION overlaps(V_A_START INT             , V_A_END INT             , V_B_START INT             , V_B_END INT             )
-- create or replace FUNCTION overlaps(V_A_START INTEGER         , V_A_END INTEGER         , V_B_START INTEGER         , V_B_END INTEGER         )
-- create or replace FUNCTION overlaps(V_A_START BIGINT          , V_A_END BIGINT          , V_B_START BIGINT          , V_B_END BIGINT          )
-- create or replace FUNCTION overlaps(V_A_START SMALLINT        , V_A_END SMALLINT        , V_B_START SMALLINT        , V_B_END SMALLINT        )
-- create or replace FUNCTION overlaps(V_A_START TINYINT         , V_A_END TINYINT         , V_B_START TINYINT         , V_B_END TINYINT         )
-- create or replace FUNCTION overlaps(V_A_START BYTEINT         , V_A_END BYTEINT         , V_B_START BYTEINT         , V_B_END BYTEINT         )

Verify the working logic:

SELECT overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:01', TIMESTAMP'2019-01-02 23:59:59') AS OVERLAPS_INISDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_OUTSIDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-01 23:59:59') AS NOT_OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-03 00:00:01', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',NULL, NULL) AS NOT_OVERLAPS_NULLS
      ,overlaps( NULL, TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_NULLS
      ,overlaps(TIMESTAMP'2019-01-03 00:00:00', TIMESTAMP'2019-01-02 00:00:00' ,TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_reverseda
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedb
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedboth
;

Finally, check the all existent overloads of the function:

show functions like 'overlaps';

Hive: derive timezone from local timestamp and UTC(GMT) timestamp

PROBLEM

Derive time zone of the record. Calculate the hour difference (aka offset) between UTC and Local time stamps.

A table contains two column: 

• UTC unix timestamp
• Local time zone human readable time

See timezone offset mapping for the United States:  Time Zones in the United States

SOLUTION

In Hive 2.1.1-amzn-0 the following SQL will make a cut. The SQL statement converts to timestamp and extracts an HOUR interval difference.

SELECT cast(from_unixtime(cast(log_time_utc_sec AS bigint)) AS TIMESTAMP)

      -cast(log_time,                                       AS TIMESTAMP) AS HR_DIFF

FROM mybigtable

LIMIT 10

;

Elaborating the example: 

SELECT log_time_utc_sec

      ,log_time

     ,hour(cast(from_unixtime(cast(log_time_utc_sec AS bigint)) AS TIMESTAMP)-cast(regexp_replace(log_time, '^((.*?-){2}.*?)-', '$1 ') AS TIMESTAMP)) AS HR_DIFF

,case hour(cast(from_unixtime(cast(log_time_utc_sec AS bigint)) AS TIMESTAMP)-cast(regexp_replace(log_time, '^((.*?-){2}.*?)-', '$1 ') AS TIMESTAMP))

       when 10 then 'HST'

       when  9 then 'AKST'

       when  8 then 'PST'

       when  7 then 'MST'

       when  6 then 'CST'

       when  5 then 'EST' 

else CONCAT('UTC-',hour(cast(from_unixtime(cast(log_time_utc_sec AS bigint)) AS TIMESTAMP)-cast(regexp_replace(log_time, '^((.*?-){2}.*?)-', '$1 ') AS TIMESTAMP)))

end TIMEZONE

FROM mybigtable

LIMIT 10

;