When using Kafka to import data into ByteHouse, if nested JSON is encountered in the source data, virtual columns and parsing functions can be used to help parse and import the source data. This article will provide a detailed description of the import methods for such scenarios.
Kafka table includes a virtual column (Virtual Column) _content (String). The contents of _content are the JSON strings of each row. The parsing approach is to use the JSONExtract function to extract individual columns from the complete _content string using JSON path.

JSON Data Example #

{  
        "npc_info": {       
                "npc":  "bedok"
        },
        "case_info": {    
                "time_info": {  
                      "year":"2021"
                      },
                        "offence":  "stealing",  
                        "case_no":  123       
             }
}

Import Requirements Description #

Plan to import data into the target table using the following format

Import Interface Configuration #

1. Data Loading -> Create New Import Task -> Select "Kafka Data Stream"
   
2. Select Kafka Datasource, subject (topic), set consumption group, and offset configuration. Click "Next"
   
3. Select the target table.
4. Define schema mapping. Click "Edit Target Table" to add a new column. Since the final column name is different from the JSON first layer format, if "analyze from datasource", some changes need to be made.
   
5. Create the target data table. Expression reference "Import Table" Expression content. Select "Create data table and proceed to the next step".
6. Set the data import task name. Select "Complete".
7. After creating the data import mission, click "Start".
8. Wait for the mission to run, then search the target table.

About the JSONExtract function #

The usage of the JSONExtract function is consistent with the ClickHouse community

JSONExtract(json[, indices_or_keys…], Return_type)
JSONExtractRaw(json[, indices_or_keys…])