Log to redshift using log4net adonetappender
I am trying to log into my redshift server using the built-in adonetappender using log4net. When the timestamp field is removed, the log will be saved successfully. However, the timestamp format seems to be causing it. I have tried many different formats that match the redshift claims.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!-- This section contains the log4net configuration settings -->
<log4net>
<!-- Define some output appenders -->
<appender name="DebugAppender" type="log4net.Appender.DebugAppender">
<immediateFlush value="true" />
<layout type="log4net.Layout.SimpleLayout" />
</appender>
<appender name="RedshiftAppender" type="log4net.Appender.AdoNetAppender">
<bufferSize value="1" />
<connectionType value="System.Data.Odbc.OdbcConnection,System.Data,version=1.0.3300.0,publicKeyToken=b77a5c561934e089,culture=neutral" />
<connectionString value="Driver={Amazon Redshift (x86)};..." />
<commandText value="INSERT INTO hep_config.server_log (tm,src,lvl,msg) VALUES ($1,$2,$3,$4)" />
<parameter>
<parameterName value="$1" />
<dbType value="DateTime" />
<layout type="log4net.Layout.PatternLayout" value="%date{yyyyMMdd HH':'mm':'ss'.'fff}" />
</parameter>
<parameter>
<parameterName value="$2" />
<dbType value="String" />
<size value="128" />
<layout type="log4net.Layout.PatternLayout" value="%thread" />
</parameter>
<parameter>
<parameterName value="$3" />
<dbType value="String" />
<size value="64" />
<layout type="log4net.Layout.PatternLayout" value="%level" />
</parameter>
<parameter>
<parameterName value="$4" />
<dbType value="String" />
<size value="4000" />
<layout type="log4net.Layout.PatternLayout" value="%message" />
</parameter>
</appender>
<root>
<level value="ALL" />
<appender-ref ref="DebugAppender" />
<appender-ref ref="RedshiftAppender" />
</root>
</log4net>
</configuration>
source to share
I tried and couldn't get it to work with the Redshift driver. However, PostgreSQL ODBC Driver works. Try the following:
Change your connection string to use PostgreSQL ODBC Driver . Note. I am using windows 64 bit, so you will need to adjust the driver based on your version.
<connectionString value="Driver={PostgreSQL Unicode(x64)};..." />
Change the layout for DateTime to use RawUtcTimeStampLayout.
<layout type="log4net.Layout.RawUtcTimeStampLayout" />
NOTE: Timestamp values in Redshift UTC
UPDATE
I managed to get it to work with the Redshift driver. The MSDN documentation for OdbcCommand.Parameters states:
When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by OdbcCommand. Use a question mark (?) In any of these.
and
The order in which OdbcParameter objects are added to the OdbcParameterCollection must directly match the position of the question mark placeholder for the parameter in the command text.
With this information, you can try the following:
<commandText value="INSERT INTO log.log_test(tm,src,lvl,msg) VALUES (?, ?, ?, ?)" />
At this point, it doesn't matter what you call parameters, as the order of the parameters does matter. I kept on using RawUtcTimeStampLayout, but I changed the dbType to string.
source to share
You can define the timestamp format as part of your COPY command .
Use the following flag:
TIMEFORMAT [AS] {'timeformat_string' | "auto" | "epochal" | 'epochmillisecs'} If TIMEFORMAT is not specified, the default format is YYYY-MM-DD HH: MI: SS.
source to share