Redshift - dataypes

Amazon Redshift Data Types

---

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. See below for a list of the different data type mappings applicable when working with an Amazon Redshift database.

Amazon Redshift Database Type	Spotfire Data Type	Supported
SMALLINT	Integer/Int	Yes
INTEGER	Integer/Int	Yes
BIGINT	LongInteger/Long	Yes
DECIMAL(p,s)	When p <= 9 and s = 0: Integer When 9 < p <= 18 and s = 0: LongInteger Else: Real	Yes
REAL	SingleReal/Float	Yes
DOUBLE PRECISION	Real/Double	Yes
FLOAT	Real	Yes
BOOLEAN	Boolean/Bool	Yes
CHAR	String	Yes
VARCHAR	String	Yes
DATE	Date	Yes
TIMESTAMP	DateTime	Yes

Kafka - Overview

Producer  --->>  Kakfa server (Kafka Broker), cluster is a group of broker which has  a toic - ---> consumer (request back for more data)

Topic is a unique name given to a kafka steam.  Break a topic in multiple partitions.

Partition - one partition per cluster computer .

Offset - a unique number given to a message in a specific partition .
No global offset for all partitions.

Consumer Group  - is a single logical unit of consumer

To identify message - need Topic name, Partition name and offset.

Replication factor (is set at topic level)  to decide how many partitions can copy a topic in an event of a node failure.

Kafka implements - Leader and follower model -
Leader partitions responsibility to get messages from Producer and sent to a consumer.

kafka-topic.sh zookeeper  localhost:/8888 --describe --topic xz

ISR - in sync replicas

Oracle DB Growth

SET SERVEROUT ON

 

SET VERIFY OFF

 

SET LINES 200

 

SET PAGES 2000

 

DECLARE

    v_ts_id                      NUMBER;

    not_in_awr EXCEPTION;

    v_ts_block_size              NUMBER;

    v_begin_snap_id              NUMBER;

    v_end_snap_id                NUMBER;

    v_begin_snap_date            DATE;

    v_end_snap_date              DATE;

    v_numdays                    NUMBER;

    v_count                      NUMBER;

    v_ts_begin_size              NUMBER;

    v_ts_end_size                NUMBER;

    v_ts_growth                  NUMBER;

    v_ts_begin_allocated_space   NUMBER;

    v_ts_end_allocated_space     NUMBER;

    v_db_begin_size              NUMBER := 0;

    v_db_end_size                NUMBER := 0;

    v_db_begin_allocated_space   NUMBER := 0;

    v_db_end_allocated_space     NUMBER := 0;

    v_db_growth                  NUMBER := 0;

    CURSOR v_cur IS

        SELECT

            tablespace_name

        FROM

            dba_tablespaces

        WHERE

                contents = 'PERMANENT';

 

BEGIN

    FOR v_rec IN v_cur LOOP

        BEGIN

            v_ts_begin_allocated_space := 0;

            v_ts_end_allocated_space := 0;

            v_ts_begin_size := 0;

            v_ts_end_size := 0;

            SELECT

                ts#

            INTO

                v_ts_id

            FROM

                v$tablespace

            WHERE

                name = v_rec.tablespace_name;

 

            SELECT

                block_size

            INTO

                v_ts_block_size

            FROM

                dba_tablespaces

            WHERE

                tablespace_name = v_rec.tablespace_name;

 

            SELECT

                COUNT(*)

            INTO

                v_count

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                tablespace_id = v_ts_id;

 

            IF

                v_count = 0

            THEN

                RAISE not_in_awr;

            END IF;

            SELECT

                MIN(snap_id),

                MAX(snap_id),

                MIN(trunc(TO_DATE(

                    rtime,

                    'MM/DD/YYYY HH24:MI:SS'

                ) ) ),

                MAX(trunc(TO_DATE(

                    rtime,

                    'MM/DD/YYYY HH24:MI:SS'

                ) ) )

            INTO

                v_begin_snap_id,v_end_snap_id,v_begin_snap_date,v_end_snap_date

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                tablespace_id = v_ts_id;

 

            IF

                upper(v_rec.tablespace_name) = 'SYSTEM'

            THEN

                v_numdays := v_end_snap_date - v_begin_snap_date;

            END IF;

 

            SELECT

                round(

                    MAX(tablespace_size) * v_ts_block_size / 1024 / 1024,

                    2

                )

            INTO

                v_ts_begin_allocated_space

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                    tablespace_id = v_ts_id

                AND

                    snap_id = v_begin_snap_id;

 

            SELECT

                round(

                    MAX(tablespace_size) * v_ts_block_size / 1024 / 1024,

                    2

                )

            INTO

                v_ts_end_allocated_space

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                    tablespace_id = v_ts_id

                AND

                    snap_id = v_end_snap_id;

 

            SELECT

                round(

                    MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024,

                    2

                )

            INTO

                v_ts_begin_size

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                    tablespace_id = v_ts_id

                AND

                    snap_id = v_begin_snap_id;

 

            SELECT

                round(

                    MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024,

                    2

                )

            INTO

                v_ts_end_size

            FROM

                dba_hist_tbspc_space_usage

            WHERE

                    tablespace_id = v_ts_id

                AND

                    snap_id = v_end_snap_id;

 

            v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;

            v_db_end_allocated_space := v_db_end_allocated_space + v_ts_end_allocated_space;

            v_db_begin_size := v_db_begin_size + v_ts_begin_size;

            v_db_end_size := v_db_end_size + v_ts_end_size;

            v_db_growth := v_db_end_size - v_db_begin_size;

        END;

    END LOOP;

 

    dbms_output.put_line(chr(10) );

    dbms_output.put_line('Summary');

    dbms_output.put_line('========');

    dbms_output.put_line('1) Allocated Space: '

     || v_db_end_allocated_space

     || ' MB'

     || ' ('

     || round(

        v_db_end_allocated_space / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('2) Used Space: '

     || v_db_end_size

     || ' MB'

     || ' ('

     || round(

        v_db_end_size / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('3) Used Space Percentage: '

     || round(

        v_db_end_size / v_db_end_allocated_space * 100,

        2

    )

     || ' %');

 

    dbms_output.put_line(chr(10) );

    dbms_output.put_line('History');

    dbms_output.put_line('========');

    dbms_output.put_line('1) Allocated Space on '

     || v_begin_snap_date

     || ': '

     || v_db_begin_allocated_space

     || ' MB'

     || ' ('

     || round(

        v_db_begin_allocated_space / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('2) Current Allocated Space on '

     || v_end_snap_date

     || ': '

     || v_db_end_allocated_space

     || ' MB'

     || ' ('

     || round(

        v_db_end_allocated_space / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('3) Used Space on '

     || v_begin_snap_date

     || ': '

     || v_db_begin_size

     || ' MB'

     || ' ('

     || round(

        v_db_begin_size / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('4) Current Used Space on '

     || v_end_snap_date

     || ': '

     || v_db_end_size

     || ' MB'

     || ' ('

     || round(

        v_db_end_size / 1024,

        2

    )

     || ' GB)');

 

    dbms_output.put_line('5) Total growth during last '

     || v_numdays

     || ' days between '

     || v_begin_snap_date

     || ' and '

     || v_end_snap_date

     || ': '

     || v_db_growth

     || ' MB'

     || ' ('

     || round(

        v_db_growth / 1024,

        2

    )

     || ' GB)');

 

    IF

        ( v_db_growth <= 0 OR v_numdays <= 0 )

    THEN

        dbms_output.put_line(chr(10) );

        dbms_output.put_line('No data growth was found for the Database');

    ELSE

        dbms_output.put_line('6) Per day growth during last '

         || v_numdays

         || ' days: '

         || round(

            v_db_growth / v_numdays,

            2

        )

         || ' MB'

         || ' ('

         || round(

            (v_db_growth / v_numdays) / 1024,

            2

        )

         || ' GB)');

 

        dbms_output.put_line(chr(10) );

        dbms_output.put_line('Expected Growth');

        dbms_output.put_line('===============');

        dbms_output.put_line('1) Expected growth for next 30 days: '

         || round(

            (v_db_growth / v_numdays) * 30,

            2

        )

         || ' MB'

         || ' ('

         || round(

            ( (v_db_growth / v_numdays) * 30) / 1024,

            2

        )

         || ' GB)');

 

        dbms_output.put_line('2) Expected growth for next 60 days: '

         || round(

            (v_db_growth / v_numdays) * 60,

            2

        )

         || ' MB'

         || ' ('

         || round(

            ( (v_db_growth / v_numdays) * 60) / 1024,

            2

        )

         || ' GB)');

 

        dbms_output.put_line('3) Expected growth for next 90 days: '

         || round(

            (v_db_growth / v_numdays) * 90,

            2

        )

         || ' MB'

         || ' ('

         || round(

            ( (v_db_growth / v_numdays) * 90) / 1024,

            2

        )

         || ' GB)');

 

        dbms_output.put_line(chr(10) );

        dbms_output.put_line('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');

    END IF;

 

EXCEPTION

    WHEN not_in_awr THEN

        dbms_output.put_line(chr(10) );

        dbms_output.put_line('===================================================================================================================='

);

        dbms_output.put_line('!!! ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR !!!');

        dbms_output.put_line('Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT,or wait for next AWR snapshot capture before executing this script'

);

        dbms_output.put_line('===================================================================================================================='

);

END;

/