Flink SQL Row里嵌套Array＜Row＞该如何用DDL定义

最新推荐文章于 2023-07-20 22:37:43 发布

久昔

最新推荐文章于 2023-07-20 22:37:43 发布

阅读量370

点赞数

分类专栏： Flink 文章标签： flink sql kafka

本文链接：https://blog.csdn.net/weixin_927326/article/details/129009046

版权

Flink 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

相关proto定义

    CmDsOfdmChan ds_ofdm_channel = 10;
    message CmDsOfdmChan {
        int32 count = 1;
        repeated Entry entry = 2;
        message Entry {
            int32 channel_id = 1;
            int32 plc_total_codewords = 2;
            int32 plc_unreliable_codewords = 3;
            int32 ncp_field_crc_failures = 4;
        }
    }

    CmDsOfdmProfileStat ds_ofdm_profile_stat = 11;
    message CmDsOfdmProfileStat {
        int32 count = 1;
        repeated Entry entry = 2;
        message Entry {
            int32 channel_id = 1;
            repeated Profile profile = 2;
            message Profile{
                int32 profile_index = 1;
                int32 total_codewords = 2;
                int32 corrected_codewords = 3;
                int32 uncorrectable_codewords = 4;
            }
        }
    }

DDL定义

CREATE TABLE Docsis_test4(
`mac` STRING,
`timestamp`  INT,
`cm_status` row<resets INT,resets_delta INT,lost_syncs INT,lost_syncs_delta INT>,
`ds_channel` row<`count` INT,`entry` Array<ROW<channel_id INT,frequency INT,`power` INT>>>,
`us_channel` row<`count` INT,`entry` Array<ROW<channel_id INT,frequency INT,width INT>>>,
`signal_quality` row<`count` INT,`entry` Array<ROW<channel_id INT,microrelfections INT,ext_unerroreds INT,ext_correcteds INT,ext_uncorrectables INT>>>,
`us_status` row<`count` INT,`entry` Array<ROW<channel_id INT,tx_power INT,t3_timeouts INT,t4_timeouts INT,ranging_status INT>>>,
`signal_quality_ext` row<`count` INT,`entry` Array<ROW<channel_id INT,rx_mer INT>>>,
`ds_ofdm_rxmer` row<`count` INT,`entry` Array<Row<channel_id INT,percentile INT,mean INT,std_dev INT,thr_val INT>>>,
`ds_ofdm_channel` row<`count` INT,`entry` Array<Row<channel_id INT, plc_total_codewords INT,plc_unreliable_codewords INT,ncp_field_crc_failures INT>>>,
`ds_ofdm_profile_stat` row<`count` INT,`entry` Array<Row<channel_id INT,`profile` Array<Row<profile_index INT,total_codewords INT,corrected_codewords INT,uncorrectable_codewords INT>>>>>,
`ds_ofdm_channel_power` row<`count` INT,`entry` Array<ROW<channel_id INT,`bands` Array<ROW<band_index INT,center_frequency INT,rx_power INT>>>>>,
`us_ofdma_status` row<`count` INT,`entry` Array<ROW<channel_id INT,t3_timeouts INT,t4_timeouts INT>>>,
`us_ofdma_channel` row<`count` INT,`entry` Array<Row<channel_id INT,subcarrier_zero_freq INT, first_active_subcarrier_num INT,last_active_subcarrier_num INT,num_active_subcarriers INT,subcarrier_spacing INT,tx_power INT>>> 
) WITH (
 'connector' = 'kafka',
 'topic' = 'a2',
 'properties.bootstrap.servers' = 'kafka:9092',
 'scan.startup.mode' = 'latest-offset',
 'properties.group.id' = 'flink-job',
 'format' = 'protobuf',
 'protobuf.message-class-name' = 'com.hitrontech.protobuf.docsis.Docsis',
 'protobuf.ignore-parse-errors' = 'true'
);

插入数据

insert into Docsis_test4 values(
    '64:77:7d:e2:05:80',
    1673920260,
    row(0,0,0,0),
    row(25,Array[Row(1,507000000,-87),Row(25,0,0),Row(23,639000000,-92),Row(22,633000000,-92),Row(21,627000000,-95),Row(20,621000000,-95),Row(19,615000000,-95),Row(18,609000000,-97),Row(17,603000000,-97),Row(16,597000000,-100),Row(15,591000000,-100),Row(14,585000000,-100),Row(13,579000000,-100),Row(12,573000000,-104),Row(11,567000000,-100),Row(10,561000000,-97),Row(9,555000000,-95),Row(8,549000000,-97),Row(7,543000000,-95),Row(6,537000000,-94),Row(5,531000000,-92),Row(4,525000000,-92),Row(3,519000000,-92),Row(2,513000000,-90),Row(24,645000000,-90)]),
    row(9,Array[Row(2,13000000,1600000),Row(8,22600000,1600000),Row(7,21000000,1600000),Row(6,19400000,1600000),Row(5,17800000,1600000),Row(4,16200000,1600000),Row(3,14600000,1600000),Row(1,11400000,1600000),Row(9,0,0)]),
    row(24,Array[Row(1,29,2147483647,1562,7950),Row(23,36,2147483647,932,6408),Row(22,35,2147483647,1037,6362),Row(21,38,2147483647,833,6378),Row(20,38,2147483647,687,4201),Row(19,34,2147483647,938,3971),Row(18,37,2147483647,714,2685),Row(17,38,2147483647,757,5525),Row(16,37,2147483647,889,5916),Row(15,33,2147483647,938,6461),Row(14,34,2147483647,1952,9005),Row(13,35,2147483647,1778,9173),Row(12,35,2147483647,1290,11594),Row(11,37,2147483647,1707,11004),Row(10,35,2147483647,1920,10802),Row(9,34,2147483647,1676,7993),Row(8,34,2147483647,1762,7593),Row(7,37,2147483647,1153,7360),Row(6,33,2147483647,1461,5911),Row(5,37,2147483647,1407,829),Row(4,35,2147483647,1495,10229),Row(3,33,2147483647,1495,1020),Row(2,38,2147483647,1237,887),Row(24,33,2147483647,1210,5579)]),
    row(8,Array[Row(2,473,1,0,4),Row(8,495,2,0,4),Row(7,505,2,0,4),Row(6,493,1,0,4),Row(5,493,2,0,4),Row(4,473,1,0,4),Row(3,468,39,0,4),Row(1,468,1,0,4)]),
    row(24,Array[Row(1,403),Row(23,409),Row(22,403),Row(21,389),Row(20,403),Row(19,389),Row(18,403),Row(17,403),Row(16,386),Row(15,389),Row(14,389),Row(13,403),Row(12,389),Row(11,409),Row(10,389),Row(9,403),Row(8,403),Row(7,389),Row(6,382),Row(5,403),Row(4,403),Row(3,403),Row(2,409),Row(24,40)]),
    row(1,Array[Row(25,2,4268,109,161)]),
    row(1,Array[Row(25,414518127,0,7)]),
    row(1,Array[Row(25,Array[Row(0,20158985,13487761,20)])]),
    row(1,Array[Row(25,Array[Row(0,456000000,-8),Row(1,453000000,-119),Row(2,459000000,-8),Row(3,465000000,-85),Row(4,471000000,-8),Row(5,477000000,-8),Row(6,483000000,-9),Row(7,489000000,-9),Row(8,495000000,-9),Row(9,501000000,-112)])]),
    row(1,Array[Row(9,1,0)]),
    row(1,Array[Row(9,53350000,74,272,199,50,208)])
);

注意反引号！