@Andrew Pilloud<mailto:[email protected]> thanks for your suggestions. I
tried CAST and TRIM but it did not work:
Sql Stmt I am using: SELECT 'CAST(id AS VARCHAR)' FROM PCOLLECTION
Logs:
[main] INFO org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner - SQL:
SELECT 'CAST(id AS VARCHAR)'
FROM `beam`.`PCOLLECTION` AS `PCOLLECTION`
[main] INFO org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner -
SQLPlan>
LogicalProject(EXPR$0=['CAST(id AS VARCHAR)'])
BeamIOSourceRel(table=[[beam, PCOLLECTION]])
[main] INFO org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner -
BEAMPlan>
BeamCalcRel(expr#0..44=[{inputs}], expr#45=['CAST(id AS VARCHAR)'],
EXPR$0=[$t45])
BeamIOSourceRel(table=[[beam, PCOLLECTION]])
Exception in thread "main" java.lang.RuntimeException: Unhandled logical type
SqlCharType
at
org.apache.beam.sdk.schemas.utils.AvroUtils.getFieldSchema(AvroUtils.java:911)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroField(AvroUtils.java:306)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroSchema(AvroUtils.java:341)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroSchema(AvroUtils.java:348)
From: Andrew Pilloud <[email protected]>
Reply-To: "[email protected]" <[email protected]>
Date: Monday, May 10, 2021 at 7:46 PM
To: user <[email protected]>
Cc: Yuan Feng <[email protected]>
Subject: Re: A problem with calcite sql
For the first one you have
https://issues.apache.org/jira/browse/BEAM-5251<https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FBEAM-5251&data=04%7C01%7Ctaol%40zillow.com%7Cd0d024cf363d4a0df9a708d914270a8b%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637562980181312070%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=QGT5TNbR17Bb2T2MjBVxD7mfxzIe4XG9%2Bfgr%2BjBvRzY%3D&reserved=0>
For the second, I opened a new issue for you:
https://issues.apache.org/jira/browse/BEAM-12323<https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FBEAM-12323&data=04%7C01%7Ctaol%40zillow.com%7Cd0d024cf363d4a0df9a708d914270a8b%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637562980181322031%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=BZ1y7IQcb5Hw93b%2BcrZpe6G%2BDw3E0KcPUDuxjWuV%2BDw%3D&reserved=0>
Your second issue is because our Avro conversion library doesn't know how to
handle fixed length strings. These normally show up in SQL when you are
outputting a constant. I'm not sure exactly how to work around it, if you can
get the output type to be a VARCHAR (instead of CHAR) this problem will go
away. You might be able to do something like 'CAST("Your String Literal" AS
VARCHAR)' , 'TRIM("Your String Literal")' or ' "Your String Literal" || "" '.
On Mon, May 10, 2021 at 7:25 PM Tao Li
<[email protected]<mailto:[email protected]>> wrote:
Sorry to bug with another question. I was saving a data set with below schema
(this dataset comes from sql query). Saw the SqlCharType issue. Did anyone see
this issue before?
[main] INFO com.zillow.pipeler.core.transform.DatasetFlattenerCore - Fields:
Field{name=id, description=, type=LOGICAL_TYPE NOT NULL, options={{}}}
Field{name=user_tmp, description=, type=LOGICAL_TYPE NOT NULL, options={{}}}
Field{name=market_name, description=, type=LOGICAL_TYPE NOT NULL, options={{}}}
Field{name=market_transactionManagement_transactionManagers_email,
description=, type=LOGICAL_TYPE NOT NULL, options={{}}}
Field{name=market_transactionManagement_transactionManagers_name, description=,
type=LOGICAL_TYPE NOT NULL, options={{}}}
Field{name=market_transactionManagement_transactionProfileId, description=,
type=LOGICAL_TYPE NOT NULL, options={{}}}
Options:{{}}
Exception in thread "main" java.lang.RuntimeException: Unhandled logical type
SqlCharType
at
org.apache.beam.sdk.schemas.utils.AvroUtils.getFieldSchema(AvroUtils.java:911)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroField(AvroUtils.java:306)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroSchema(AvroUtils.java:341)
at
org.apache.beam.sdk.schemas.utils.AvroUtils.toAvroSchema(AvroUtils.java:348)
From: Tao Li <[email protected]<mailto:[email protected]>>
Reply-To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Date: Monday, May 10, 2021 at 7:19 PM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Cc: Yuan Feng <[email protected]<mailto:[email protected]>>
Subject: Re: A problem with calcite sql
Never mind. Looks like “user” is a reserved name.
From: Tao Li <[email protected]<mailto:[email protected]>>
Reply-To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Date: Monday, May 10, 2021 at 7:10 PM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Cc: Yuan Feng <[email protected]<mailto:[email protected]>>
Subject: A problem with calcite sql
Hi Beam community,
I am seeing a weird issue by using calcite sql. I don’t understand why it’s
complaining my query is not valid. Once I removed “user AS user”, it worked
fine. Please advise. Thanks.
Exception in thread "main"
org.apache.beam.sdk.extensions.sql.impl.ParseException: Unable to parse query
SELECT id AS id, user AS user, market_name AS market_name,
market_transactionManagement_transactionManagers.email AS
market_transactionManagement_transactionManagers_email,
market_transactionManagement_transactionManagers.name AS
market_transactionManagement_transactionManagers_name,
market_transactionManagement_transactionProfileId AS
market_transactionManagement_transactionProfileId FROM PCOLLECTION
at
org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner.convertToBeamRel(CalciteQueryPlanner.java:214)
at
org.apache.beam.sdk.extensions.sql.impl.BeamSqlEnv.parseQuery(BeamSqlEnv.java:111)
at
org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:171)
at
org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:109)
at org.apache.beam.sdk.Pipeline.applyInternal(Pipeline.java:547)
at
org.apache.beam.sdk.Pipeline.applyTransform(Pipeline.java:498)
at
org.apache.beam.sdk.values.PCollection.apply(PCollection.java:370)
at
com.zillow.pipeler.core.transform.DatasetFlattenerCore.updateSchemaBasedOnAvroSchema(DatasetFlattenerCore.java:85)
at
com.zillow.pipeler.core.transform.DatasetFlattenerCore.execute(DatasetFlattenerCore.java:61)
at
com.zillow.pipeler.core.transform.DatasetFlattenerCore.execute(DatasetFlattenerCore.java:29)
at
com.zillow.pipeler.orchestrator.BaseOrchestrator.run(BaseOrchestrator.java:61)
at
com.zillow.pipeler.orchestrator.transform.DatasetFlattenerOrchestrator.main(DatasetFlattenerOrchestrator.java:71)
Caused by:
org.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.sql.parser.SqlParseException:
Encountered "AS user" at line 1, column 23.
Was expecting one of:
<EOF>
"ORDER" ...
"LIMIT" ...
