IBM Streams 4.3.0

Examples

SPL standard and specialized toolkits > com.ibm.streams.db 2.2.0 > com.ibm.streams.db.netezza > NetezzaPrepareLoad > Examples

These examples demonstrate how to use the NetezzaPrepareLoad operator.

Example 1: NetezzaPrepareLoad with default parameters

This example shows how the NetezzaPrepareLoad operator prepares a delimited string when no optional parameters are specified. Note the following parameter specifications:

The pipe character (|) is used for delimiting fields.
No characters in the string field f2 are escaped with a backslash.

The streams processing application contains the following SPL code:

use com.ibm.streams.db.netezza::*;
composite loadSample1_1 {
type testSchema = int32 f1, rstring f2;
graph
	stream <testSchema> mySrc = FileSource() {
	param
		file   :  "input.csv";
		format : csv;
	}
	stream <rstring buf> preparedData = NetezzaPrepareLoad(mySrc) {
	param
		access             : "myLoad";
		connectionDocument : "connections.xml";
	}
() as mySink = FileSink(preparedData) {
param
	file : "output.csv";
	quoteStrings : false;
}
}

The connection.xml contains the following elements:

<?xml version="1.0" encoding="UTF-8"?>
<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<connection_specifications>
	<connection_specification name="conn1" >
		<ODBC database="myDatabase" user="myUser" password="myPassword" />
</connection_specification>
</connection_specifications>
<access_specifications>
<access_specification name="myLoad">
	<table tablename="table1" transaction_batchsize="500000"/>
	<uses_connection connection="conn1" />
	<native_schema>
		<column name="f1" type="INTEGER"/>
		<column name="f2" type="CHAR" length="32"/>
	</native_schema>
</access_specification>
</access_specifications>
</st:connections>

The example uses the following input:

bsmoth@host1:~/work/netezza/dev/samples/sample1.1/data> cat input.csv 
1,Bob Jones
2,Joe Smith
3,Jane Doe

This example generates the following output:

cat output.csv
1|Bob Jones
2|Joe Smith
3|Jane Doe

Example 2: Specifying a non-default delimiter

This example shows how the NetezzaPrepareLoad operator prepares a delimited string when the delimiter parameter is used to specify a delimiter character.

Note the following parameter specifications:

The comma is now being used to delimit fields.

The streams processing application contains the following SPL code:

use com.ibm.streams.db.netezza::*;
composite loadSample1_2 {
type testSchema = int32 f1, rstring f2;
graph
stream <testSchema> mySrc = FileSource() {
	param
		file   :  "input.csv";
		format : csv;
}
stream <rstring buf> preparedData = NetezzaPrepareLoad(mySrc) {
	param
		access             : "myLoad";
		connectionDocument : "connections.xml";
		delimiter          : �,�;
}
() as mySink = FileSink(preparedData) {
	param
	file : "output.csv";
	quoteStrings : false;
}
}

The connection.xml file contains the following elements:

<?xml version="1.0" encoding="UTF-8"?>
<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection_specifications>
<connection_specification name="conn1" >
	<ODBC database="myDatabase" user="myUser" password="myPassword" />
</connection_specification>
</connection_specifications>
<access_specifications>
<access_specification name="myLoad">
	<table tablename="table1" transaction_batchsize="500000"/>
	<uses_connection connection="conn1" />
	<native_schema>
	<column name="f1" type="int32"/>
	<column name="f2" type="rstring" length="32"/>
	</native_schema>
</access_specification>
</access_specifications>
</st:connections>

This example uses the following input:

bsmoth@host1:~/work/netezza/dev/samples/sample1.1/data> cat input.csv 
1,Bob Jones
2,Joe Smith
3,Jane Doe

This example generates the following output:

cat output.csv
1,Bob Jones
2,Joe Smith
3,Jane Doe

Example 3: Specifying a string for escapeCharList

This example shows how a value for the escapeCharList parameter can cause different characters to be escaped.

Note the following parameter specifications:

The number sign (#) is now used to delimit fields.
The pipe character is not being escaped.
The escapeCharList parameter contains a value of #% which causes the # and the % character to each be escaped in the f2 field.

The streams processing application contains the following SPL code:

use com.ibm.streams.db.netezza::*;
composite loadSample1_3 {
type testSchema = int32 f1, rstring f2;
graph
stream <testSchema> mySrc = FileSource() {
	param
		file   :  "input.csv";
		format : csv;
}
stream <rstring buf> preparedData = NetezzaPrepareLoad(mySrc) {
	param
		access             : "myLoad";
		connectionDocument : "connections.xml";
		delimiter          : �#�;
		escapeCharList     : [�#%�];
}
() as mySink = FileSink(preparedData) {
	param
		file : "output.csv";
		quoteStrings : false;
}
}

The connection.xml contains the following elements:

<?xml version="1.0" encoding="UTF-8"?>
<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
				xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection_specifications>
<connection_specification name="conn1" >
	<ODBC database="myDatabase" user="myUser" password="myPassword" />
</connection_specification>
</connection_specifications>
<access_specifications>
<access_specification name="myLoad">
	<table tablename="table1" transaction_batchsize="500000"/>
	<uses_connection connection="conn1" />
	<native_schema>
	<column name="f1" type="INTEGER"/>
	<column name="f2" type="CHAR" length="32"/>
	</native_schema>
</access_specification>
</access_specifications>
</st:connections>

This example uses the following input:

bsmith@host6:~/work/netezza/dev/samples/sample1.1/data> cat input.csv 
1,Bob|Jones
2,Joe|Smith%
3,Jane|Doe#

This example generates the following output:

cat output.csv
1#Bob|Jones
2#Joe|Smith\%
3#Jane|Doe\#

Example 4: Specifying an empty string for escapeCharList

This example shows how to specify an empty string value for the escapeCharList parameter to result in no escaped characters. Note the following parameter specifications:

The default pipe character is being used to delimit fields.
The input data does not contain any pipe characters that would need to be escaped.

The streams processing application contains the following SPL code:

use com.ibm.streams.db.netezza::*;
composite loadSample1_4 {
type testSchema = int32 f1, rstring f2;
graph
stream <testSchema> mySrc = FileSource() {
	param
	file   :  "input.csv";
	format : csv;
}
stream <rstring buf> preparedData = NetezzaPrepareLoad(mySrc) {
	param
	access             : "myLoad";
	connectionDocument : "connections.xml";
	escapeCharList     : [""];
}
() as mySink = FileSink(preparedData) {
	param
	file : "output.csv";
	quoteStrings : false;
}
}

The connection.xml contains the following elements:

<?xml version="1.0" encoding="UTF-8"?>
<st:connections xmlns:st="http://www.ibm.com/xmlns/prod/streams/adapters"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection_specifications>
<connection_specification name="conn1" >
	<ODBC database="myDatabase" user="myUser" password="myPassword" />
</connection_specification>
</connection_specifications>
<access_specifications>
<access_specification name="myLoad">
	<table tablename="table1" transaction_batchsize="500000"/>
	<uses_connection connection="conn1" />
	<native_schema>
	<column name="f1" type="INTEGER"/>
	<column name="f2" type="CHAR" length="32"/>
	</native_schema>
</access_specification>
</access_specifications>
</st:connections>

This example uses the following input:

bsmith@host6:~/work/netezza/dev/samples/sample1.1/data> cat input.csv 
1,Bob Jones
2,Joe Smith
3,Jane Doe

This example uses the following output:

cat output.csv
1|Bob Jones
2|Joe Smith
3|Jane Doe