Add custom fields into Json

[srikanth flink]

Hi there,

I'm querying json data and is working fine. I would like to add custom fields including the query result.

My query looks like: select ROW(`source`),  ROW(`destination`), ROW(`dns`), organization, cnt from (select (source.`ip`,source.`isInternalIP`) as source, (destination.`ip`,destination.`isInternalIP`) as destination,  (dns.`query`, dns.`answers`.`data`) as dns, organization as organization from dnsTableS) tab1;

While I would like to add "'dns' as `agg.type`, 'dns' as `agg.name`" to the same output, but the query is throw exceptions:

[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "." at line 1, column 278.
Was expecting one of:
    "EXCEPT" ...
    "FETCH" ...
    "FROM" ...
    "INTERSECT" ...
    "LIMIT" ...
    "OFFSET" ...
    "ORDER" ...
    "MINUS" ...
    "UNION" ...
    ")" ...
    "," ...

Could someone help me with this? Thanks

Srikanth

[vino yang]

Hi,

The exception shows your SQL statement has grammatical errors. Please check it again or provide the whole SQL statement here.

Best,

Vino

srikanth flink <[hidden email]> 于2019年10月29日周二 下午2:51写道：

Hi there,

I'm querying json data and is working fine. I would like to add custom fields including the query result.

My query looks like: select ROW(`source`),  ROW(`destination`), ROW(`dns`), organization, cnt from (select (source.`ip`,source.`isInternalIP`) as source, (destination.`ip`,destination.`isInternalIP`) as destination,  (dns.`query`, dns.`answers`.`data`) as dns, organization as organization from dnsTableS) tab1;

While I would like to add "'dns' as `agg.type`, 'dns' as `agg.name`" to the same output, but the query is throw exceptions:

[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "." at line 1, column 278.
Was expecting one of:
    "EXCEPT" ...
    "FETCH" ...
    "FROM" ...
    "INTERSECT" ...
    "LIMIT" ...
    "OFFSET" ...
    "ORDER" ...
    "MINUS" ...
    "UNION" ...
    ")" ...
    "," ...

Could someone help me with this? Thanks

Srikanth

[Jingsong Li]

Hi Srikanth,

1.Can you share complete sql? And schema of input table?

2.What schema you want to select? I didn't understand what is "agg" field mean.

On Tue, Oct 29, 2019 at 3:28 PM Jingsong Li <[hidden email]> wrote:

Hi Srikanth,

1.Can you share complete sql? And schema of input table?

2.What schema you want to select? I didn't understand what is "agg" field mean.

On Tue, Oct 29, 2019 at 2:51 PM srikanth flink <[hidden email]> wrote:

Hi there,

I'm querying json data and is working fine. I would like to add custom fields including the query result.

My query looks like: select ROW(`source`),  ROW(`destination`), ROW(`dns`), organization, cnt from (select (source.`ip`,source.`isInternalIP`) as source, (destination.`ip`,destination.`isInternalIP`) as destination,  (dns.`query`, dns.`answers`.`data`) as dns, organization as organization from dnsTableS) tab1;

While I would like to add "'dns' as `agg.type`, 'dns' as `agg.name`" to the same output, but the query is throw exceptions:

[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "." at line 1, column 278.
Was expecting one of:
    "EXCEPT" ...
    "FETCH" ...
    "FROM" ...
    "INTERSECT" ...
    "LIMIT" ...
    "OFFSET" ...
    "ORDER" ...
    "MINUS" ...
    "UNION" ...
    ")" ...
    "," ...

Could someone help me with this? Thanks

Srikanth

--

Best, Jingsong Lee

--

[Jingsong Li]

Hi Srikanth,

Which sql throws exception?

On Tue, Oct 29, 2019 at 3:41 PM vino yang <[hidden email]> wrote:

Hi,

The exception shows your SQL statement has grammatical errors. Please check it again or provide the whole SQL statement here.

Best,

Vino

srikanth flink <[hidden email]> 于2019年10月29日周二 下午2:51写道：

Hi there,

I'm querying json data and is working fine. I would like to add custom fields including the query result.

My query looks like: select ROW(`source`),  ROW(`destination`), ROW(`dns`), organization, cnt from (select (source.`ip`,source.`isInternalIP`) as source, (destination.`ip`,destination.`isInternalIP`) as destination,  (dns.`query`, dns.`answers`.`data`) as dns, organization as organization from dnsTableS) tab1;

While I would like to add "'dns' as `agg.type`, 'dns' as `agg.name`" to the same output, but the query is throw exceptions:

[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.sql.parser.impl.ParseException: Encountered "." at line 1, column 278.
Was expecting one of:
    "EXCEPT" ...
    "FETCH" ...
    "FROM" ...
    "INTERSECT" ...
    "LIMIT" ...
    "OFFSET" ...
    "ORDER" ...
    "MINUS" ...
    "UNION" ...
    ")" ...
    "," ...

Could someone help me with this? Thanks

Srikanth

--