Skip to main content
Question

Kafka streaming to Flex table with columns

  • March 1, 2019
  • 2 replies
  • 16 views
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?

    2 replies

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • March 4, 2019

    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
    • mdkarkeys
    • Author
    • New Participant
    • March 4, 2019

    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);
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement