BookmarkSubscribeRSS Feed
TeraGrapher
Fluorite | Level 6

I am trying to assist a customer to improve some very large extracts from Teradata to SAS (9.4) by using TPT/Export.

 

They need to use LDAP (and are successfully using AuthDomain) and SQL pass-through to use specific Teradata SQL (and currently using).

 

I believe that the syntax would be similar to the following (I am just learning SAS and pulled this out from the various manuals), but I am being told that this is not correct:

 

LIBNAME MyTd Teradata TDPID=QA1 AUTHDOMAIN=TeradataAuth FastExport=Yes DBSliceParm=(THREADED_APPS,2) Mode=Teradata Database=QA1_PL_SAS_VIEWS LogDb=SB_PRICING;
PROC SQL;
CONNECT USING MyTd as MyTd_1;
CREATE TABLE work.term AS
SELECT *
FROM CONNECTION TO MyTd_1
(
    SELECT  PolicyNo,
            QuoteNo,
            NumTerm,
            GwTermNo,
            ModelNo,
            curmodel,
            MIN(CAST(PeriodStartDate AS date)) AS date_inc,
            MAX(CAST(PeriodEndDate AS date)) AS date_exp
    FROM policy
    WHERE    numterm IS NOT NULL
    AND      ModelNo IS NOT NULL
    GROUP BY PolicyNo, QuoteNo, NumTerm, GWTermNo, ModelNo, curmodel
    ORDER BY PolicyNo, NumTerm, GwTermNo, ModelNo;
);
DISCONNECT FROM MyTd_1;
QUIT;

 

 

Any ideas as to if this is actually possible and how to get this to work?

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, not used teradata, however the syntax shoud be similar to how we used to connect to Oracle, something along the lines of:

proc sql;
  connect to db (<connection details eg username/password);
  create table WORK.TERM as
  select * from connection to db (

<your database query here>

  );
  disconnect from db;
quit;

So I don't see anything majorly out, other than the connect to db.  

TeraGrapher
Fluorite | Level 6

Unfortunately that's been tried and SAS just uses ODBC for accessing Teradata and TPT is not used. Extracting millions and in some cases billions of rows using ODBC is just too slow.

 

Thanks anyway.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Ah, ok.  Your maybe best of speaking with your SAS rep then, TPT (Terradata Parallel Transporter) seems to be a Terradata specific function, so might need some specific software/drivers.  Can't help any further I am afraid.

 

TeraGrapher
Fluorite | Level 6

For information:

 

Teradata Parallel Transporter (TPT) is a Teradata supplied utility for loading data into and exporting data from Teradata quickly. It is available for use from SAS although the SAS documentation is somewhat lacking in detail.

 

TPT is already installed on the SAS server in question and tested outside of SAS to prove that it as indeed been installed correctly. Just can't get the syntax correct to use it with SAS, LDAP and SQL pass-through.

 

Already following up via the SAS adminstrators and I thought I'd try here as well.

LinusH
Tourmaline | Level 20

Well, I guess that your libname syntax is sufficient. But it requires that the global option metaserver is set, and of course that you have sufficient registrations in metadata.

Data never sleeps
TeraGrapher
Fluorite | Level 6

@LinusH, thanks for responding. Being a SAS newbie I am not sure what you are referring to when you mention "global option metaserver".

 

However if this is in relation to the use of LDAP and/or LIBNAME, then this is already in use successfully and so should be O.K.

LinusH
Tourmaline | Level 20

If you are not aware of any metadata server, you probably don't have one. So if that's the case, you can't use AUTHDOMAIN as I understand it, hence the login errors...

 

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0aiq25zc8u8u6n1i8...

Data never sleeps
TeraGrapher
Fluorite | Level 6

@LinusH - no login errors, not mentioned that - LDAP is working perfectly fine in general use.

TeraGrapher
Fluorite | Level 6

@SASKiwi, yes this community has only been tried after exausting the documentation. What is available is incomplete or not very explicit about TPT/API and the Export operator.

 

JBailey
Barite | Level 11

Hi @TeraGrapher

 

I think the use of explicit pass-through is messing up the code. TPT functionality must be invoked by SAS. This code is going to have problems, but it should give you an idea.

 

LIBNAME MyTd Teradata TDPID=QA1 AUTHDOMAIN=TeradataAuth FastExport=Yes DBSliceParm=(THREADED_APPS,2) Mode=Teradata Database=QA1_PL_SAS_VIEWS LogDb=SB_PRICING;
PROC SQL;
CONNECT USING MyTd as MyTd_1;
CREATE TABLE work.term AS
SELECT  PolicyNo,
            QuoteNo,
            NumTerm,
            GwTermNo,
            ModelNo,
            curmodel,
            MIN(CAST(PeriodStartDate AS date)) AS date_inc,
            MAX(CAST(PeriodEndDate AS date)) AS date_exp
    FROM mytd.policy 
    WHERE    numterm IS NOT NULL
    AND      ModelNo IS NOT NULL
    GROUP BY PolicyNo, QuoteNo, NumTerm, GWTermNo, ModelNo, curmodel
    ORDER BY PolicyNo, NumTerm, GwTermNo, ModelNo;

QUIT;

There is an old'ish paper, which needs to be updated, that may help you with this. It shows many examples of TPT.

https://support.sas.com/resources/papers/proceedings11/142-2011.pdf

 

 

TeraGrapher
Fluorite | Level 6

@JBailey, thanks for this. I will read and apply if I can.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 3702 views
  • 1 like
  • 5 in conversation