Database field types
Hi -- I notice that when the database tables get created all the fields are 'text' type. That is okay. However, it might in some cases be convenient for us to have different field types. Is there any problem with that? If we create the table with the desired field types, will that take care of it (assuming the data actually matches the type)? Liam -- Liam Kirsher PGP: http://liam.numenet.com/pgp/
On Fri, 2009-03-06 at 19:08 -0800, Liam Kirsher wrote:
Hi --
I notice that when the database tables get created all the fields are 'text' type. That is okay. However, it might in some cases be convenient for us to have different field types. Is there any problem with that? If we create the table with the desired field types, will that take care of it (assuming the data actually matches the type)?
you can specify the field type, like this: sql(columns("date datetime")); e.g. separate the type with a space from the column name. -- Bazsi
This worked fine for the timestamps, but there was a problem with other types. Some of the numeric fields can have null values. It looks like syslog-ng doesn't translate empty strings into nulls when doing the insert. Thus, the insert fails because the numeric field types won't accept a string of any kind. For now, I can just let them be text strings, but I'd prefer to be able to use numeric types and allow null values. For example, in the destination below, lat and long could be float8 and pid and art_id could be integers.
destination d_ut_access { sql( type(pgsql) host("localhost") username("postgres") password("xxxxxxx") database("prodlogs") table("ut_access_log") columns("time_stamp timestamp with time zone", "query_time timestamp", "host", "program", "pid", "request_id", "level", "ip", "phone_id", "phone_type", "software_version", "client_version", "query_string", "art_id", "session_id", "lat", "lng") values("$R_ISODATE", "${UT.QTIME}", "$HOST_FROM", "$PROGRAM", "$PID", "${UT.REQUEST_ID}", "$LEVEL", "${UT.IP}", "${UT.PHONE_ID}", "${UT.PHONE_TYPE}", "${UT.SOFTWARE_VERSION}", "${UT.CLIENT_VERSION}", "${UT.QUERY_STRING}", "${UT.ART_ID}", "${UT.SESSION_ID}", "${UT.LAT}", "${UT.LNG}") indexes("time_stamp", "query_time", "ip", "phone_id") ); };
Balazs Scheidler wrote:
On Fri, 2009-03-06 at 19:08 -0800, Liam Kirsher wrote:
Hi --
I notice that when the database tables get created all the fields are 'text' type. That is okay. However, it might in some cases be convenient for us to have different field types. Is there any problem with that? If we create the table with the desired field types, will that take care of it (assuming the data actually matches the type)?
you can specify the field type, like this:
sql(columns("date datetime"));
e.g. separate the type with a space from the column name.
-- Liam Kirsher PGP: http://liam.numenet.com/pgp/
On Sun, 2009-03-15 at 14:40 -0700, Liam Kirsher wrote:
This worked fine for the timestamps, but there was a problem with other types.
Some of the numeric fields can have null values.
It looks like syslog-ng doesn't translate empty strings into nulls when doing the insert. Thus, the insert fails because the numeric field types won't accept a string of any kind.
For now, I can just let them be text strings, but I'd prefer to be able to use numeric types and allow null values. For example, in the destination below, lat and long could be float8 and pid and art_id could be integers.
true enough, but you could perhaps use the "default value" feature of macro expansion, like this: ${art_id:-0} this means that if art_id is unset it'll expand to 0. Is this a solution to your problem? -- Bazsi
Balazs, Well, can a default value be NULL? Would it be possible to say something like ${lat:-NULL} For lat/lng it definitely won't work, since 0/0 is a valid value -- but probably one we don't want -- someplace out in the Atlantic Ocean. Liam Balazs Scheidler wrote:
On Sun, 2009-03-15 at 14:40 -0700, Liam Kirsher wrote:
This worked fine for the timestamps, but there was a problem with other types.
Some of the numeric fields can have null values.
It looks like syslog-ng doesn't translate empty strings into nulls when doing the insert. Thus, the insert fails because the numeric field types won't accept a string of any kind.
For now, I can just let them be text strings, but I'd prefer to be able to use numeric types and allow null values. For example, in the destination below, lat and long could be float8 and pid and art_id could be integers.
true enough, but you could perhaps use the "default value" feature of macro expansion, like this:
${art_id:-0}
this means that if art_id is unset it'll expand to 0. Is this a solution to your problem?
-- Liam Kirsher PGP: http://liam.numenet.com/pgp/
On Tue, 2009-03-17 at 11:06 -0700, Liam Kirsher wrote:
Balazs,
Well, can a default value be NULL? Would it be possible to say something like ${lat:-NULL}
For lat/lng it definitely won't work, since 0/0 is a valid value -- but probably one we don't want -- someplace out in the Atlantic Ocean.
Please try the attached patch, it adds a new option to the sql() destination driver: null(). Whenever syslog-ng enounters a value that matches the string specified by null(), it will insert the NULL value. This patch has not yet been committed anywhere, but it seems to do the trick for me. The question is if it works for you? With this you could do something like: sql(... columns(..., "art_id", ...) values(..., "${UT.ART_ID:-@@NULL@@}", ...) null("@@NULL@@") Of course the NULL value must be something that cannot happen in your dataset, you could perhaps use the null string, but in that case every empty string would be INSERTed as NULLs which I'm not sure is what you want. The problem is that within the SQL driver, every column is a string and the value you specify is a template possible containing several macros. Syslog-ng currently has no way of knowing that the given field had no value, it only knows that the given field expanded to the zero string. With the sample above (and the patch below), if any other field would by accident contain the string @@NULL@@ it'd be emitted as a NULL value. I don't see too much of a risk here, but you never know... If this works out for you, I'm willing to integrate this patch, but possibly only after the 3.0.2 release was made. Which you can help by the way by testing the binaries that I've published recently. -- Bazsi
Balazs, Thanks, I will test it out. Liam Balazs Scheidler wrote:
On Tue, 2009-03-17 at 11:06 -0700, Liam Kirsher wrote:
Balazs,
Well, can a default value be NULL? Would it be possible to say something like ${lat:-NULL}
For lat/lng it definitely won't work, since 0/0 is a valid value -- but probably one we don't want -- someplace out in the Atlantic Ocean.
Please try the attached patch, it adds a new option to the sql() destination driver: null().
Whenever syslog-ng enounters a value that matches the string specified by null(), it will insert the NULL value.
This patch has not yet been committed anywhere, but it seems to do the trick for me. The question is if it works for you?
With this you could do something like:
sql(... columns(..., "art_id", ...) values(..., "${UT.ART_ID:-@@NULL@@}", ...) null("@@NULL@@")
Of course the NULL value must be something that cannot happen in your dataset, you could perhaps use the null string, but in that case every empty string would be INSERTed as NULLs which I'm not sure is what you want.
The problem is that within the SQL driver, every column is a string and the value you specify is a template possible containing several macros. Syslog-ng currently has no way of knowing that the given field had no value, it only knows that the given field expanded to the zero string.
With the sample above (and the patch below), if any other field would by accident contain the string @@NULL@@ it'd be emitted as a NULL value. I don't see too much of a risk here, but you never know...
If this works out for you, I'm willing to integrate this patch, but possibly only after the 3.0.2 release was made. Which you can help by the way by testing the binaries that I've published recently.
------------------------------------------------------------------------
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- Liam Kirsher PGP: http://liam.numenet.com/pgp/
Hi Liam, On Mon, 2009-03-23 at 14:59 -0700, Liam Kirsher wrote:
Balazs,
Thanks, I will test it out.
In the meanwhile I've pushed this patchset in a topic branch, named "sql-null-value" in the public git repostory. I'd still appreciate testing. Thanks in advance. -- Bazsi
participants (2)
-
Balazs Scheidler
-
Liam Kirsher