Sending data to Windows Named Pipe

Tools & Utilities
Enthusiast

Sending data to Windows Named Pipe

Hi:

I left this post on the main Teradata website forum, but I thought this might be more appropriate and perhaps get a response. Thanks for taking a look. I just found this little nook inside of Teradata and I'm glad it exists.

I've created a Windows named pipe in a vb.net application using the System.IO.Pipes namespace. I'm creating a pipe, my process appears to be connecting to the pipe block and waiting to read and now I would just like to feed the pipe from Teradata.

I'd like FastExport to write to the pipe, but is this possible without using the Named Pipe Access Module which appears to not support writing?

I'm looking to do something like this in my FastExport...

.EXPORT OUTFILE \.\pipe\testpipe
FORMAT TEXT
MODE RECORD;

**** 17:35:21 UTY4019 Access module error '34' received during 'File open' operation:
'pmUnxDskOpen: fopen error (Invalid argument)' File Name :
'\.\pipe\testpipe'.

It is very possible that I've not configured my pipe correctly. So if there is any one out there with any experience in this matter or if this has been done I would like some help. Thanks!
5 REPLIES
Teradata Employee

Re: Sending data to Windows Named Pipe

When you created the pipe and you began the FastExport job, did you already have a reader attempting to read from the pipe prior to starting FastExport?

I think that is the correct order.
I think you have to start the pipe-reader-process prior to starting FastExport.
-- SteveF
Enthusiast

Re: Sending data to Windows Named Pipe

Hello Steven:

Thank you for the reply. I am starting my reader first and it is connecting to the pipe successfully as the IsConnected property is returning true once the connection is made. The applicaiton I'm working on use the .NET 3.5 framework. I tried it on .NET 2.0 (before we upgraded frameworks). It was a lot more complex with Windows API calls but the results have been the same.

The error I usually get from FastExport depending on how I tweak it because I'm not sure what work is sometimes file not found, sometimes unable to open file and sometimes I get a permission error.

I've looked into threading issues. From what I understand the pipe server and the read from the pipe should be on a new thread and the write should be on the main thread once the reader has connected.

I've tried a lot of different configurations of the pipe server and I've always gotten the same result. Here is the pseudo code I'm using. If the configuration is an issue please, PLEASE let me know.

'Create the pipe server
Dim pipeServer As New NamedPipeServerStream("testpipe", PipeDirection.InOut)

' Wait for a client to connect
pipeServer.WaitForConnection()

pipeServer.IsConnected is returning true when I start the reader... and blocking waiting for the write, but the write to the pipe is returning errors in Fexp.

Thank you again for helping out!
Teradata Employee

Re: Sending data to Windows Named Pipe

I could inferred the following points from my understanding of the problem scenario:

1) The Named pipe was created using vb.net application (using some call within .NET framework) without using the Named pipes access module.  I think, "testpipe" should be the name of the named pipe that you have created using vb.net application.

2) You are trying to export the data using fastexport from Teradata database and write the same onto the named pipe (//./pipe/testpipe).  Please make sure that you are writing on to the same named pipe (name of the pipe) that was created using vb.net application.

3) You have raised the question,

I'd like FastExport to write to the pipe, but is this possible without using the Named Pipe Access Module which appears to not support writing?

The answer is YES.  In your case, the Named pipes access module was not being used while creating the named pipe.  You need not use the Named pipes access module for writing the data on to the Named pipe.

4) You are getting the following error while executing the fastexport script.

**** 17:35:21 UTY4019 Access module error '34' received during 'File open' operation:

'pmUnxDskOpen: fopen error (Invalid argument)' File Name :

         '\.\pipe\testpipe'.

This error is generated by an intermediate layer (libpm.dll) called 'Data connector'.  This is because the Data connector could not find the named pipe "testpipe", which you have created by the vb.net.

========================================

I have a suggestion, which might resolve your problem.

a) You modify the fastexport script, so that it exports the data to a file (and not to a pipe).  I mean to say,

change        .EXPORT OUTFILE \.\pipe\testpipe

to            .EXPORT OUTFILE testfile

b) After successful generation of the 'testfile' file by fastexport,  just start the reader process and leave it waiting for the data to be written to the named pipe "\.\pipe\testpipe".

c) Open the command prompt window. Go to the location where "testfile" was generated by the fastexport. Type the following command to write the data on to the named pipe.

(use this command if you have mks toolkit installed on your windows box)

     cat testfile > //./pipe/testpipe    

(use this command if you DO NOT have mks toolkit installed on your windows box. Just using DOS command.)

     type testfile > //./pipe/testpipe

I think this should transfer the data onto the pipe successfully.  BTW, this scenario has nothing to do with the Named pipes access module.

Good luck!!

Enthusiast

Re: Sending data to Windows Named Pipe

More information about that can be found in Teradata Tools and Utilities Access Module Reference. It was really helpful.

Enthusiast

Re: Sending data to Windows Named Pipe

Thanks to @Sastry for his tips. I want to give back and share a boilerplate to pipe data from Python to Fasltoad via a named pipe on Windows (tested with Windows7).

teradata_dn = 'your database tdpid or dns name'
database = 'put yours'
table = 'example_table' # as usual we have to define primary index as well
primary_index = '"game_id"' # double quotes to avoid parsing errors
pipe_name = r'\.\pipe\fastload' # pipe name, dot stands for localhost

#just an example dataframe
import pandas as pd
data = pd.DataFrame(
{
'game_id': [1,2,3,4,5,6,7,8],
'year': [2010, 2011, 2012, 2011, 2012, 2010, 2011, 2012],
'team': ['Bears', 'Bears', 'Bears', 'Packers', 'Packers'
, 'Lions', 'Lions', 'Lions'],
'wins': [11, 8, 10, 15, 11, 6, 10, 4],
'losses': [5, 8, 6, 1, 5, 10, 6, 12]
}
, columns=['game_id','year', 'team', 'wins', 'losses']
)

#convert it to csv that we will pipe to fastload
from StringIO import StringIO
sio = StringIO()
data.to_csv(sio, header=False, index=False)

# compose a config file for fastload using the example dataframe structure
from string import Template
s=Template("""
.LOGON $TDPID/$$tdwallet(u),$$tdwallet(db_name);
DROP TABLE $DATABASE.$TABLE; /* table to which you want to load data */
DROP TABLE $DATABASE.TEST_DUP; /* error table -1 */
DROP TABLE $DATABASE.TEST_ERR; /* error table - 2 */
CREATE TABLE $DATABASE.$TABLE /* Target table definition*/
(
$cr_t_columns
) PRIMARY INDEX ( $PRIMARY_INDEX );
BEGIN LOADING $DATABASE.$TABLE
ERRORFILES
$DATABASE.TEST_DUP,
$DATABASE.TEST_ERR;
SET RECORD VARTEXT "," ; /* flat file delimiter (","-comma,"\t"-tab) */
axsmod np_AXSMOD.dll 'fallback_directory=.';
DEFINE
$def_columns
FILE='$data_file'; /* specifies the location path for the flat file */
INSERT INTO $DATABASE.$TABLE
VALUES (
$ins_columns
);
.END LOADING;
.LOGOFF;
eof
""")
#$tdwallet(db_name) is a chained password shielding using tdwallet
#'$>tdwallet add your_db_name', when prompted add your password
#'$>tdwallet add db_name', copypaste this: $wallet(your_db_name)

script_text = s.substitute(
data_file=pipe_name
, TDPID=teradata_dn
, DATABASE=database
, TABLE=table
, PRIMARY_INDEX=primary_index
, cr_t_columns=',\n'.join(['"'+columnname+'" varchar(2000)'
for columnname in data.columns])
, def_columns=',\n'.join(['"'+columnname+'" (varchar(2000))'
for columnname in data.columns])
, ins_columns=',\n'.join([':'+'"'+columnname+'"'
for columnname in data.columns])
)

script = open('load_from_fifo.txt',"w")
script.writelines( "%s" % script_text )
script.close()

import subprocess, string
with open('load_from_fifo.txt', 'r') as configuration:
proc = subprocess.Popen(['fastload']
, stdin=configuration
, stdout=subprocess.PIPE)

for line in iter(proc.stdout.readline, b''):
print line
if string.find(line,'Starting to send to RDBMS') >0 :
"""
Teradata Tools and Utilities Access Module Reference
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/\
index.html#pageInterface_Tools/B035_2425_071A/2425title.html
"""
import win32file
h=win32file.CreateFile(
pipe_name, # pipe name, dot stands for localhost
win32file.GENERIC_WRITE, # write access
0, # no sharing
None, # no security attributes
win32file.OPEN_EXISTING, # opens existing pipe
0, # default attributes
None); # no template file

win32file.WriteFile(h, sio.getvalue())
h = None # Close the file by removing the last reference to the handle!