(DEPRECATED) Apache Flink User Mailing List archive.

FlinkSQL query error when specify json-schema.

classic Classic list List threaded Threaded
♦
♦
 4 messages Options
wanglei2@geekplus.com.cn
Reply | Threaded
Open this post in threaded view
♦
♦
|

FlinkSQL query error when specify json-schema.

wanglei2@geekplus.com.cn

CREATE TABLE user_log(
    `id` INT,
    `timestamp`  BIGINT
) WITH (
    'connector.type' = 'kafka',
    'connector.version' = 'universal',
    'connector.topic' = 'wanglei_jsontest',
    'connector.startup-mode' = 'latest-offset',
    'connector.properties.0.key' = 'zookeeper.connect',
    'connector.properties.0.value' = '172.19.78.32:2181',
    'connector.properties.1.key' = 'bootstrap.servers',
    'connector.properties.1.value' = '172.19.78.32:9092',
    'update-mode' = 'append',
    'format.type' = 'json',
    'format.json-schema' = '{
        "type": "object",
        "properties": {
           "id": {"type": "integer"},
           "timestamp": {"type": "number"}
        }
    }'
);

Then select * from user_log;

org.apache.flink.table.api.ValidationException: Type INT of table field 'id' does not match with the physical type LEGACY('DECIMAL', 'DECIMAL') of the 'id' field of the TableSource return type.

Seems the specified type "integer", "number" can not be mapped to  INT, BIGINT 

How can i solve this problem?

Thanks,
Lei
Benchao Li
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: FlinkSQL query error when specify json-schema.

Benchao Li
Hi wanglei,

You don't need to specify 'format.json-schema', the format can derive schema from the DDL.
Your exception above means the schema in 'format.json-schema' and DDL are not match.

[hidden email] <[hidden email]> 于2020年4月16日周四 下午4:21写道：

CREATE TABLE user_log(
    `id` INT,
    `timestamp`  BIGINT
) WITH (
    'connector.type' = 'kafka',
    'connector.version' = 'universal',
    'connector.topic' = 'wanglei_jsontest',
    'connector.startup-mode' = 'latest-offset',
    'connector.properties.0.key' = 'zookeeper.connect',
    'connector.properties.0.value' = '172.19.78.32:2181',
    'connector.properties.1.key' = 'bootstrap.servers',
    'connector.properties.1.value' = '172.19.78.32:9092',
    'update-mode' = 'append',
    'format.type' = 'json',
    'format.json-schema' = '{
        "type": "object",
        "properties": {
           "id": {"type": "integer"},
           "timestamp": {"type": "number"}
        }
    }'
);

Then select * from user_log;

org.apache.flink.table.api.ValidationException: Type INT of table field 'id' does not match with the physical type LEGACY('DECIMAL', 'DECIMAL') of the 'id' field of the TableSource return type.

Seems the specified type "integer", "number" can not be mapped to  INT, BIGINT 

How can i solve this problem?

Thanks,
Lei



-- 
Benchao Li
School of Electronics Engineering and Computer Science, Peking University
Tel:+86-15650713730
Email: [hidden email]; [hidden email]
wanglei2@geekplus.com.cn
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Re: FlinkSQL query error when specify json-schema.

wanglei2@geekplus.com.cn

Thanks, I have tried. 

 'format.derive-schema' = 'true' will work.

But if i insist to use format.json-schema,  the CREATE TABLE must be writtten as: 

    `id` DECIMAL(38,18),
    `timestamp` DECIMAL(38,18)

 
Date: 2020-04-16 16:56
Subject: Re: FlinkSQL query error when specify json-schema.
Hi wanglei,

You don't need to specify 'format.json-schema', the format can derive schema from the DDL.
Your exception above means the schema in 'format.json-schema' and DDL are not match.

[hidden email] <[hidden email]> 于2020年4月16日周四 下午4:21写道：

CREATE TABLE user_log(
    `id` INT,
    `timestamp`  BIGINT
) WITH (
    'connector.type' = 'kafka',
    'connector.version' = 'universal',
    'connector.topic' = 'wanglei_jsontest',
    'connector.startup-mode' = 'latest-offset',
    'connector.properties.0.key' = 'zookeeper.connect',
    'connector.properties.0.value' = '172.19.78.32:2181',
    'connector.properties.1.key' = 'bootstrap.servers',
    'connector.properties.1.value' = '172.19.78.32:9092',
    'update-mode' = 'append',
    'format.type' = 'json',
    'format.json-schema' = '{
        "type": "object",
        "properties": {
           "id": {"type": "integer"},
           "timestamp": {"type": "number"}
        }
    }'
);

Then select * from user_log;

org.apache.flink.table.api.ValidationException: Type INT of table field 'id' does not match with the physical type LEGACY('DECIMAL', 'DECIMAL') of the 'id' field of the TableSource return type.

Seems the specified type "integer", "number" can not be mapped to  INT, BIGINT 

How can i solve this problem?

Thanks,
Lei



-- 
Benchao Li
School of Electronics Engineering and Computer Science, Peking University
Tel:+86-15650713730
Email: [hidden email]; [hidden email]
Benchao Li
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Re: FlinkSQL query error when specify json-schema.

Benchao Li
Hi wanglei,

Yes, your observation is correct. Currently the type derivation relies on legacy types, which only support (38, 18) as decimal precisions.

[hidden email] <[hidden email]> 于2020年4月16日周四 下午6:54写道：

Thanks, I have tried. 

 'format.derive-schema' = 'true' will work.

But if i insist to use format.json-schema,  the CREATE TABLE must be writtten as: 

    `id` DECIMAL(38,18),
    `timestamp` DECIMAL(38,18)

 
Date: 2020-04-16 16:56
Subject: Re: FlinkSQL query error when specify json-schema.
Hi wanglei,

You don't need to specify 'format.json-schema', the format can derive schema from the DDL.
Your exception above means the schema in 'format.json-schema' and DDL are not match.

[hidden email] <[hidden email]> 于2020年4月16日周四 下午4:21写道：

CREATE TABLE user_log(
    `id` INT,
    `timestamp`  BIGINT
) WITH (
    'connector.type' = 'kafka',
    'connector.version' = 'universal',
    'connector.topic' = 'wanglei_jsontest',
    'connector.startup-mode' = 'latest-offset',
    'connector.properties.0.key' = 'zookeeper.connect',
    'connector.properties.0.value' = '172.19.78.32:2181',
    'connector.properties.1.key' = 'bootstrap.servers',
    'connector.properties.1.value' = '172.19.78.32:9092',
    'update-mode' = 'append',
    'format.type' = 'json',
    'format.json-schema' = '{
        "type": "object",
        "properties": {
           "id": {"type": "integer"},
           "timestamp": {"type": "number"}
        }
    }'
);

Then select * from user_log;

org.apache.flink.table.api.ValidationException: Type INT of table field 'id' does not match with the physical type LEGACY('DECIMAL', 'DECIMAL') of the 'id' field of the TableSource return type.

Seems the specified type "integer", "number" can not be mapped to  INT, BIGINT 

How can i solve this problem?

Thanks,
Lei



-- 
Benchao Li
School of Electronics Engineering and Computer Science, Peking University
Tel:+86-15650713730
Email: [hidden email]; [hidden email]


-- 
Benchao Li
School of Electronics Engineering and Computer Science, Peking University
Tel:+86-15650713730
Email: [hidden email]; [hidden email]
Free forum by Nabble Edit this page