Kafka streaming to Flex table with columns

mdkarkeysmdkarkeys Registered User

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?

Answers

  • SruthiASruthiA Employee, Registered User, VerticaExpert

    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

  • mdkarkeysmdkarkeys Registered User

    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);
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file