0

I have a syntax error in my bash command when I try to fill a Postgres table with data from Kafka using kafkacat:

[k ~]$ kafkacat -b XXX.XX.Y.Z:9092 -t test -o 20501  -f '%k|%s|%T|%p|%o|213\n' -e | psql -c "copy raw_from_kafka(key, value, timestamp, partition, offset, load_cycle) from stdin delimiter '|'" -d dwh_dev -h XXX.ZZ.Y.ZZ -U konstantin
% Auto-selecting Consumer mode (use -P or -C to override)
ERROR:  syntax error at or near "offset"
LINE 1: ... raw_from_kafka(key, value, timestamp, partition, offset, lo...
                                                             ^
% ERROR: Write error for message of 34 bytes in test [0] at offset 20567: Broken pipe

The syntax is simple, so I can't understand why the error appears.

Strangely, this error only appears when I specify a list of columns I want to fill. If I use the same command filling all columns in the table, everything goes nice and smooth, the data ends up in the table, just like I want:

[k ~]$ kafkacat -b XXX.XX.Y.Z:9092 -t test -o 20501  -f '%k|%s|%T|%p|%o|2020-08-05 22:33:44+03|213\n' -e | psql -c "copy raw_from_kafka from stdin delimiter '|'" -d dwh_dev -h XXX.ZZ.Y.ZZ -U konstantin
% Auto-selecting Consumer mode (use -P or -C to override)
% Reached end of topic test [0] at offset 23437: exiting

But I want to be able to fill only specific columns with the data from Kafka, while the rest of columns are filled with default values. Therefore, I need the first version of the command working.

Does anybody have any idea, why the error appears?

Postgres version: 12 kafkacat version: 1.6.0 System: CentOS 7

Konstantin Popov
  • 1,546
  • 4
  • 15
  • 19

2 Answers2

2

offset is a reserved word in SQL. If you have a column named offset you need to double-quote it, ie. "offset".

harmic
  • 28,606
  • 5
  • 67
  • 91
  • Just tried - got same error in the same place: ERROR: syntax error at or near "offset" LINE 1: ... raw_from_kafka(key, value, timestamp, partition, offset, lo... ^ I think it will also collide with quotation marks around the whole query. Now will try to rename all columns that may be reserved words... – Konstantin Popov Aug 06 '20 at 11:39
  • @KonstantinPopov " I think it will also collide with quotation marks around the whole query" Correct, which is why it didn't work. You would have had to escape the double quotes in order for them to be seen. – jjanes Aug 06 '20 at 21:35
0

Renaming offset to k_offset resolved the issue.

Konstantin Popov
  • 1,546
  • 4
  • 15
  • 19