Netezza – ERROR [08S02] Unexpected protocol character/message

Reading Time: < 1 minute

Issue:
When you are trying to create external table in Netezza appliance, If you get the error below

ERROR [08S02] Unexpected protocol character/message

CAUSE:
This error may occur when you try to create a external table.

CREATE EXTERNAL TABLE ‘c:\trainingtable.csv’ USING (DELIMITER ‘,’ REMOTESOURCE ‘odbc’ FILLRECORD ‘true’ DATEDELIM
‘/’ DATESTYLE ‘YMD’ QUOTEDVALUE ‘yes’ TIMEDELIM ‘:’ TIMESTYLE ’12HOUR’)

If you dig deeper in postgres logs, you will also find the below error:

26499] ERROR: found delim ‘,’ in a data field, specify escapeChar ‘\’ option in the external table definition

RESOLUTION:
Make sure to specify escapeChar ‘\’ option in the create external table syntax and rerun your query.

CREATE EXTERNAL TABLE ‘c:\trainingtable.csv’ USING (DELIMITER ‘,’ REMOTESOURCE ‘odbc’ FILLRECORD ‘true’ DATEDELIM
‘/’ DATESTYLE ‘YMD’ QUOTEDVALUE ‘yes’ TIMEDELIM ‘:’ TIMESTYLE ’12HOUR’ escapeChar ‘\’)