R: Zugriff auf Teradata via RODBC

Veröffentlicht am von

RODBC ermöglicht es, von R aus Teradata SQL Anweisungen durchzuführen. Abfrageergebnisse werden als Data Frames repräsentiert und können nahtlos in R weiterverwendet werden.

Zunächst importieren wir das Paket RODBC.

 library(RODBC)

Danach öffnen wir eine ODBC Verbindung zum Teradata RDBMS. Die Option USEREGIONALSETTINGS verhindert, daß RODBC – aufgrund der Ländereinstellung Österreich und des damit verbundenen Dezimalkommas – DECIMAL Spalten als Text interpretiert.

td <- odbcDriverConnect(connection="DRIVER=Teradata;DBCNAME=127.0.0.1;DATABASE=theDatabasename;UID=theUsername;PWD=thePassword;USEREGIONALSETTINGS=N")

SQL SELECT Anweisungen werden mit der Funktion sqlQuery() ausgeführt; diese liefert einen Data Frame zurück. Klarerweise achten wir in diesem Schritt auf die zu erwartende Datenmenge.

x<-sqlQuery(td,
"SELECT
    tablename                    AS name
    ,createtimestamp (DATE)      AS createdate
    ,characters(TRIM(Tablename)) AS namelength
FROM
    dbc.tables
WHERE
    tablekind='T'
    AND createtimestamp IS NOT NULL
    AND databasename='theDatabasename'
;")

Zuletzt schließen wir die nicht mehr benötigte Datenbankverbindung.

 odbcClose(td)

Der Data Frame steht weiterhin zur Verfügung:

# display the first lines
head(x)
                            name createdate namelength
1 SPxxxxxxxxxxxxxxxxxxxxxxxxxxxx 2011-11-09         30
2 MCxxxxxxxxxxxxxxxxxxxxxxxxxxx  2013-06-10         29
3 MCxxxxxxxxxxxxxxxxxxxxxxx      2013-03-12         25
4 Inxxxxxxxxxxxxxxxxxxxxx        2002-12-27         23
5 Inxxxxxxxxxxxxxxxxx            2002-02-28         19
6 BFxxxxxxxxxxxxxxxxxxxxxx       2005-03-16         24

# show column information
summary(x)
                             name       createdate           namelength   
 Abxxxxxxx                     :  1   Min.   :2000-05-16   Min.   : 3.00  
 Adxxxxx                       :  1   1st Qu.:2006-09-04   1st Qu.:14.00  
 Adxxxxxxxxxxxxxxxxxxxxx       :  1   Median :2010-07-02   Median :19.00  
 Adxxxxxxxxxxx                 :  1   Mean   :2009-05-30   Mean   :18.87  
 Adxxxxxxxxxxxxxx              :  1   3rd Qu.:2012-12-12   3rd Qu.:24.00  
 Agxxxxxxxxxxxxxxxxxx          :  1   Max.   :2014-02-07   Max.   :30.00  
 (Other)                       :543      

# plot density of table age
tableage=as.numeric(x[,2] - Sys.Date())
plot(density(tableage))

R_tableage_densityDamit ist die grundsätzliche Vorgangsweise auch schon beschrieben.
Die folgende Abfrage ist hilfreich um zu überprüfen, ob die Datentypumwandlungen zwischen RDBMS, ODBC und R funktionieren:

x<-sqlQuery(td,
"SELECT
    -128 (BYTEINT)                  AS vminByteint
    ,127 (BYTEINT)                  AS vmaxByteint
    ,-32768 (SMALLINT)              AS vminSmallint
    , 32767 (SMALLINT)              AS vmaxSmallint
    ,-2147483648 (INTEGER)          AS vminInteger
    , 2147483647 (INTEGER)          AS vmaxInteger
    ,-9223372036854775808 (BIGINT)  AS vminBigint 
    , 9223372036854775807 (BIGINT)  AS vmaxBigint
    ,-9.9 (DECIMAL(2,1))            AS vminDecimal1B
    , 9.9 (DECIMAL(2,1))            AS vmaxDecimal1B
    ,-9.999 (DECIMAL(4,3))          AS vminDecimal2B
    , 9.999 (DECIMAL(4,3))          AS vmaxDecimal2B
    ,-9.99999999 (DECIMAL(9,8))     AS vminDecimal4B
    , 9.99999999 (DECIMAL(9,8))     AS vmaxDecimal4B
    ,-9.99999999999999999 (DECIMAL(18,17))  AS vminDecimal8B
    , 9.99999999999999999 (DECIMAL(18,17))  AS vmaxDecimal8B
    ,-9.9999999999999999999999999999999999999 (DECIMAL(38,37))  AS vminDecimal16B
    , 9.9999999999999999999999999999999999999 (DECIMAL(38,37))  AS vmaxDecimal16B
    ,123.456789 (FLOAT)             AS vFloat 
    ,CURRENT_DATE                   AS vDate
    ,CURRENT_TIME                   AS vTime
    ,CURRENT_TIMESTAMP              AS vTimestamp
    ,'aZöäüÖÄÜß?!*' (VARCHAR(25))   AS vVarchar
    ,'aZöäüÖÄÜß?!*' (CHAR(25))      AS vChar
 ;")

Als Merkhilfe einige nützliche RODBC bzw. R Funktionen:

# regarding the ODBC connection ("channel")
odbcGetErrMsg(td)
odbcGetInfo(td)
sqlTypeInfo(td)

# regarding data types ("modes")
class(x)
[1] "data.frame"
mode(x)
[1] "list"
mode(x[,3])
[1] "numeric"