01-11-2017 08:16 AM
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?
01-11-2017 08:26 AM
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.
01-11-2017 08:31 AM
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.
01-11-2017 08:57 AM
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.
01-11-2017 09:13 AM - edited 01-11-2017 09:36 AM
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.
01-11-2017 10:22 AM
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.
01-11-2017 10:36 AM
@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.
01-12-2017 08:14 AM
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...
01-12-2017 05:37 PM
So have you been through the Teradata-specific documentation like this?
01-16-2017 06:04 AM
@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.
01-17-2017 11:54 AM
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.