2

In my Kinesis Analytics application I want to add a constant string to my output stream.

For example:

CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" (
            "constant_column"  varchar(100),
            "feature" varchar(246)
            );


CREATE OR REPLACE PUMP "OutputPump" AS 
   INSERT INTO "DESTINATION_SQL_STREAM" 
      SELECT STREAM   "MY_CONSTANT_STRING", "SOURCE_SQL_STREAM_001"."feature"
      FROM   "SOURCE_SQL_STREAM_001";

I would like to replace "MY_CONSTANT_STRING" with some string value. The reason for this is that I have many pumps writing to my output stream, so I want some kind of indicator what pump is used. Is there a way to do this?

Farseer
  • 4,036
  • 3
  • 42
  • 61

1 Answers1

0

Whatever you replace "MY_CONSTANT_STRING" with should be a column name in "SOURCE_SQL_STREAM_001". It's hard to tell from your question, but this seems like it should be "constant_column".

You also can replace "SOURCE_SQL_STREAM_001"."feature" with just "feature".

So, your final two lines should be:

SELECT STREAM   "constant_column", "feature"
      FROM   "SOURCE_SQL_STREAM_001";

If you want to add a custom string that is not sourced from "SOURCE_SQL_STREAM_001", you will need to add this before/after those two lines, and separate the custom string and the select statement with parentheses. This string should not be put in place of "MY_CONSTANT_STRING" because that field is part of the select statement, which is purely for pulling columns from "SOURCE_SQL_STREAM_001". To find out more about how to achieve this, look into documentation on multiple value insert statements.

ketcham
  • 922
  • 4
  • 15
  • Sorry for unclear question. What I really need is a way to write a string to column, where string will represent name of the pump/stream. First approach with column names will work, but I will need several columns (kinda like bitmap) and write some data only to single column that identify what pump I am using. Second approach is problematic, because I need to take data from stream, as far as I know and not just some values. What I tried is selecting from `VALUES` and writing to stream, but it failed. – Farseer Jul 23 '19 at 07:17
  • So you will have multiple columns, each titled by the name of a source pump? If so, will these columns only contain a single value, feature? – ketcham Jul 23 '19 at 14:00
  • Yes, that is the plan. Not optimal but It will work. If there is a way to write costume string to STREAM, I can solve it, so every PUMP will write its name. – Farseer Jul 23 '19 at 18:42