I try to use FlinkSQL to create a table with Avro format, but the avro schema is a little bit complicated which is like this:
{
"type":"record",
"name":"Record",
"namespace":"example.avro",
"fields":[
{
"name":"market",
"type":"string"
},
{
"name":"fruits",
"type":[
"null",
{
"type":"array",
"items":[
"null",
{
"type":"record",
"name":"apple",
"fields":[
{
"name":"price",
"type":"int"
},
{
"name":"counts",
"type":"long"
}
]
},
{
"type":"record",
"name":"banana",
"fields":[
{
"name":"location",
"type":"string"
},
{
"name":"date",
"type":"string"
}
]
}
]
}
],
"default":null
},
{
"name":"new_fruits",
"type":[
"null",
{
"type":"array",
"items":[
"null",
"apple",
"banana"
]
}
],
"default":null
}
]
}
The problem is that the array type from avro schema contains two different types but the array type of flink can only contain one type, I tried to define the type: ARRAY<ROW,ROW> in FlinkSQL DDL, but got syntax error. The whole DDL is shown below:
CREATE TABLE kafka_avro_source(
`market` STRING NOT NULL,
`fruits` ARRAY<ROW<`price` INT NOT NULL, `counts` BIGINT NOT NULL>, ROW<`location` STRING NOT NULL, `date` STRING NOT NULL>>,
`new_fruits` ARRAY<ROW<`price` INT NOT NULL, `counts` BIGINT NOT NULL>, ROW<`location` STRING NOT NULL, `date` STRING NOT NULL>>
) WITH(
'connector' = 'kafka',
'topic' = 'avro_topic',
'properties.bootstrap.servers' = '192.168.1.1:9092',
'properties.group.id' = 'testGroup',
'scan.startup.mode' = 'earliest-offset',
'format' = 'avro',
'properties.sasl.kerberos.service.name' = 'kafka',
'properties.security.protocol' = 'SASL_PLAINTEXT',
'properties.kerberos.domain.name' = 'hadoop.com'
);
How could I use SQL to create a table with this avro schema, assume can not change the avro schema?
