Kafka streaming to Flex table with columns

mdkarkeys

Hi,
we are on vertica 8.1.1. I'm streaming clickstream data (JSON format) from a Kafka topic into vertica flex table using vkconfig.

1) I created the flex table with default timestamp column as follows and data is loaded to raw column from the input stream. But the insert_timestamp column has all NULL values. With vkconfig, we can't do ETL while streaming. But this is a default timestamp column added to FLEX table. Is there a way to get this column populated with the insert time, while streaming data from Kafka? appreciate any help on this.

create flex table topic_1_tgt (
INSERT_TIMESTAMP timestamp(0) DEFAULT getdate() );

2) Also stream_microbatch_history has a lot of history data. How can we cleanup this table and delete old data?

SruthiA

For the first question, try creating TABLE as follows
create flex table topic_1_tgt (
ts timestamptz DEFAULT current_timestamp) and I think this will help you populating timestamp

For the second question, if you want to get rid of complete data just drop the schema associated with that table and it should help

mdkarkeys

following is the new DDL used. While row_id column is populated with data, ts (timestamp) column still has NULL values only.

CREATE FLEX TABLE public.topic_1_tgt 
( 
    row_id  IDENTITY , 
    ts timestamptz DEFAULT (now())::timestamptz(6) 
); 


CREATE PROJECTION public.topic_1_tgt /*+createtype(P)*/ 
( 
 __raw__, 
 row_id, 
 ts 
) 
AS 
 SELECT topic_1_tgt.__raw__, 
        topic_1_tgt.row_id, 
        topic_1_tgt.ts 
 FROM public.topic_1_tgt 
 ORDER BY topic_1_tgt.row_id, 
          topic_1_tgt.ts 
SEGMENTED BY hash(topic_1_tgt.row_id, topic_1_tgt.ts) ALL NODES KSAFE 1; 


SELECT MARK_DESIGN_KSAFE(1);