Teradata Cheatsheet

1. check index

SELECT
databasename, tablename, columnname, indextype, indexnumber, indexname
FROM  
dbc.indices
where tablename='xxx'
    and databasename='xxx'
ORDER BY
databasename,  tablename, indexnumber;


2.de-duplicate

drop table temp1

create volatile table temp1 (
    month_date date,
    record int
)on commit preserve rows


insert temp1
select '2017-02-05',1

insert temp1
select '2017-02-07',10


insert temp1
select '2017-03-07',7

select* from temp1


select * from
(
    select tt.*,month_date - extract(day from month_date)+1 as month_first from temp1 tt
)subq1
QUALIFY RANK() OVER (PARTITION BY month_first ORDER BY month_date DESC) <=1;

3.date
date: get current date

4.BTEQ Log on

bteq >> $BTEQ_OUT 2>> $BTEQ_ERR <<EOF
.logon $TDPID/$USERID, $PASSWORD;
.set width 2000;
.export report file=$GCFR_OUT;
$BT;
.SET RETRY ON;
.SET ERRORLEVEL 2639 SEVERITY 0;
call $GCFR_DATABASE_NAME.$GCFR_API_CALL;
.export reset;
.quit
EOF

 

 

5.show table Space 

SELECT
SUM(CURRENTPERM)/1024/1024 ACTUALSPACE,
MAX(CURRENTPERM)*(HASHAMP()+1)/1024/1024 EFFECTIVESPACE
FROM
DBC.TABLESIZE
WHERE DATABASENAME = 'xxx' AND TABLENAME = 'xxx'

 

 

 

 

6.database space

SELECT
DatabaseName
,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB
,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB
FROM DBC.DiskSpace
WHERE DatabaseName = 'xxx'
GROUP BY DatabaseName;

 

 

7. table space

SELECT TABLENAME,SUM(CURRENTPERM)/1024/1024/1024 ACTUALSPACE_GB, MAX(CURRENTPERM)*(HASHAMP()+1)/1024/1024/1024 EFFECTIVESPACE_GB
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'xxx' 
GROUP BY TABLENAME
ORDER BY 2 DESC


8.space type

Perm Space 使用空间
Spool Space 临时空间
Temp Space 临时表空间



9. Large count

select CAST(COUNT(*) AS FLOAT) from t



10.varchar 和 int 比较
会把varchar 转化为 int， 如果varchar是列名, col=123 col 是varchar, 所有varchar会转化，这样很慢. 如果varchar 是条件, col='123' 只有'123'会变成int.


11.

HELP CONSTRAINT HELP VOLATILE TABLE HELP STATISTICS HELP INDEX SHOW TABLE .SHOW CONTROL NO LOG WITH NO DATA WITH DATA SESSION SQLFLAG SESSION TRANSACTION ANSI MACROS

---------MERGE: UPDATEA ... ELSE INSERT MERGE INTO UPDATE FROM

---------TEMP TABLE: DERIVED VOLATILE GLOBAL TEMPORARY TABLE WITH

---------RECURSIVE: CREATE RECURSIVE VIEW WITH RECURSIVE

---------AGGREGATION: COUNT(1) OVER (PARTITION BY) OVER (ORDER BY) OVER (PARTITION BY prodid ORDER BY sales DESC) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING accumulative: ROWS UNBOUNDED PRECEDING ROWS 2 PRECEDING BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING RESET WHEN sales IS NOT NULL (reset upper bound) ROW_NUMBER() WITH BY ROLLUP CUBE GROUPING SETS NULL LAST

---------RANK: RANK() OVER (ORDER BY column1 DESC) RANK() OVER (PARTITION BY column1, column2 ORDER BY column3 DESC) QUALIFY RANK() OVER (ORDER BY sumsales ASC) <= 3 ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) PERCENT_RANK() DENSE_RANK() RANK() OVER (ORDER BY sales WITH TIES LOW/HIGH/AVG/DENSE)

---------Quantile: QUANTILE (3,column1 DESC) QUALIFY QUANTILE (100, column1) >= 75

---------SAMPLE: SAMPLE SAMPLEID TOP WITH TIES TOP PERCENT WITH TIES

---------NULL: NULLIF COALESCE

---------STRING: || SUBSTRING / SUBSTR POSITION* / INDEX* TRIM* / RTRIM / LTRIM UPPER* LOWER* OTRANSLATE OREPLACE FORMAT

---------TIME: EXTRACT(YEAR FROM ..) ADD_MONTHS

 

12.pivot

CREATE TABLE xxx.TBL_TEST(
    COL_TYPE VARCHAR(100) NOT NULL,
    COL_DATE DATE NOT NULL,
    COL_VOLUME INT,
    PRIMARY KEY(COL_TYPE,COL_DATE)
) 

CREATE TABLE xxx.TBL_PIVOT_TEST(
    COL_TYPE VARCHAR(100) NOT NULL,
    COL_DATE_20170501 INT,
    COL_DATE_20170502 INT,
    PRIMARY KEY(COL_TYPE)
) 

INSERT INTO xxx.TBL_PIVOT_TEST
SELECT T1.COL_TYPE,COL_DATE_20170501,COL_DATE_20170502 FROM 
(
SELECT 
    COL_TYPE,
    SUM(COL_VOLUME) OVER (PARTITION BY COL_TYPE) AS COL_DATE_20170501
FROM 
    xxx.TBL_TEST
WHERE     
    COL_DATE='2017-05-01') T1
LEFT JOIN
(
SELECT 
    COL_TYPE,
    SUM(COL_VOLUME) OVER (PARTITION BY COL_TYPE) AS COL_DATE_20170502
FROM 
    xxx.TBL_TEST
WHERE     
    COL_DATE='2017-05-02'    ) T2
ON T1.COL_TYPE=T2.COL_TYPE


SELECT * FROM xxx.TBL_PIVOT_TEST

13 sp

REPLACE PROCEDURE xxx.Test (OUT output VARCHAR(3000))
BEGIN
    DECLARE VAR_DATE DATE;
    DECLARE VAR_I INT;
    DECLARE VAR_NEW_OUTPUT VARCHAR(100);
    
    SET VAR_DATE='2017-05-01';
    SET VAR_NEW_OUTPUT =  '';
    
    SET VAR_I =  1;
    WHILE (VAR_I<=3) DO
    BEGIN
        IF(VAR_I<>2) THEN
        BEGIN
            SET VAR_NEW_OUTPUT = VAR_NEW_OUTPUT || cast(VAR_DATE as varchar(10)) || VAR_I || ' ';
        END;
        END IF;
        SET VAR_I =  VAR_I + 1;
    END;
    END WHILE;
    SET OUTPUT=VAR_NEW_OUTPUT;
    
END

call xxx.Test(output)

14.

cmd:
C:\folder>tbuild -f tptSample.tpt -j trans

tptSample.tpt:

DEFINE JOB TRANS_DATA_PROD_DEV
DESCRIPTION 'Transform data between Prod and Dev'
(
DEFINE SCHEMA TPT_SCHEMA
DESCRIPTION 'TPT SCHEMA'
(
col INTDATE,

);
DEFINE OPERATOR READ_OPERATOR
DESCRIPTION 'TPT SELECT Operator'
TYPE SELECTOR
SCHEMA TPT_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId = 'server....',
VARCHAR UserName = 'YourLanID',
VARCHAR LogonMech = 'LDAP',
VARCHAR UserPassword = 'YourLanPassword',
VARCHAR SelectStmt = 'SELECT * FROM table...'
);

DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TPT Load Operator'
TYPE LOAD
SCHEMA TPT_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId = 'server....',
VARCHAR UserName = 'YourLanID',
VARCHAR WorkingDatabase = 'db',
VARCHAR UserPassword = 'YourTeradataPassword',
VARCHAR TargetTable = ' table...',
VARCHAR LogTable = ' table...'
);

APPLY (
'INSERT INTO  table... (col)
VALUES (:col);'
) TO OPERATOR (LOAD_OPERATOR)
SELECT * FROM OPERATOR ( READ_OPERATOR);

);

15.

Teradata Parallel Transporter User Guide.pdf:
https://developer.teradata.com/sites/all/files/documentation/linked_docs/2445020A_TPT-User-Guide-13.10.pdf

16.

HELP CONSTRAINT
HELP VOLATILE TABLE
HELP STATISTICS
HELP INDEX
SHOW TABLE
.SHOW CONTROL
NO LOG
WITH NO DATA
WITH DATA
SESSION SQLFLAG
SESSION TRANSACTION ANSI
MACROS

---------MERGE:
UPDATEA ... ELSE INSERT
MERGE INTO
UPDATE FROM

---------TEMP TABLE:
DERIVED
VOLATILE
GLOBAL TEMPORARY TABLE
WITH

---------RECURSIVE:
CREATE RECURSIVE VIEW
WITH RECURSIVE

---------AGGREGATION:
COUNT(1) OVER (PARTITION BY)
OVER (ORDER BY)
OVER (PARTITION BY prodid ORDER BY sales DESC)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
accumulative: ROWS UNBOUNDED PRECEDING
ROWS 2 PRECEDING
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RESET WHEN sales IS NOT NULL (reset upper bound)
ROW_NUMBER()
WITH BY
ROLLUP
CUBE
GROUPING SETS
NULL LAST

---------RANK:
RANK() OVER (ORDER BY column1 DESC)
RANK() OVER (PARTITION BY column1, column2 ORDER BY column3 DESC)
QUALIFY RANK() OVER (ORDER BY sumsales ASC) <= 3
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC)
PERCENT_RANK()
DENSE_RANK()
RANK() OVER (ORDER BY sales WITH TIES LOW/HIGH/AVG/DENSE)

---------Quantile:
QUANTILE (3,column1 DESC)
QUALIFY QUANTILE (100, column1) >= 75

---------SAMPLE:
SAMPLE
SAMPLEID
TOP WITH TIES
TOP PERCENT WITH TIES

---------NULL:
NULLIF
COALESCE

---------STRING:
||
SUBSTRING / SUBSTR
POSITION* / INDEX*
TRIM* / RTRIM / LTRIM
UPPER*
LOWER*
OTRANSLATE
OREPLACE
FORMAT

---------TIME:
EXTRACT(YEAR FROM ..)
ADD_MONTHS

 

17.