Andrew, I tried the last query you recommended, and seeing this error:
Caused by: org.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.tools.ValidationException: org.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.runtime.CalciteContextException: From line 1, column 34 to line 1, column 44: Table 'PCOLLECTION' not found From: Andrew Pilloud <apill...@google.com> Date: Tuesday, May 11, 2021 at 10:38 PM To: Tao Li <t...@zillow.com> Cc: "user@beam.apache.org" <user@beam.apache.org>, Yuan Feng <yua...@zillowgroup.com> Subject: Re: A problem with calcite sql If the type was just a nested row this should would work: SELECT `market_transactionManagement_transactionManagers`.`email` FROM PCOLLECTION or this: SELECT market_transactionManagement_transactionManagers.email FROM PCOLLECTION If you have exactly one element in the array something like this should work: SELECT market_transactionManagement_transactionManagers[1].email FROM PCOLLECTION If you want to extract the array, try something like this: SELECT manager.email FROM UNNEST(PCOLLECTION.market_transactionManagement_transactionManagers) AS manager On Tue, May 11, 2021 at 10:22 PM Tao Li <t...@zillow.com<mailto:t...@zillow.com>> wrote: Thanks Andrew. With `id` syntax I am not seeing “Unhandled logical type SqlCharType” error any more. This is great progress! However I am still seeing an issue by querying a composite field. Below is the schema of the array type field: Field{name=market_transactionManagement_transactionManagers, description=, type=ARRAY<ROW<email STRING, name STRING>>, options={{}}} My sql query is selecting a nested field: SELECT `market_transactionManagement_transactionManagers.email` FROM PCOLLECTION Error: Caused by: org.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.sql.validate.SqlValidatorException: Column 'market_transactionManagement_transactionManagers.email' not found in any table So what would be the right syntax? Thanks! From: Andrew Pilloud <apill...@google.com<mailto:apill...@google.com>> Date: Tuesday, May 11, 2021 at 11:51 AM To: Tao Li <t...@zillow.com<mailto:t...@zillow.com>> Cc: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>>, Yuan Feng <yua...@zillowgroup.com<mailto:yua...@zillowgroup.com>> Subject: Re: A problem with calcite sql SELECT CAST('CAST(id AS VARCHAR)' AS VARCHAR) FROM PCOLLECTION works for me, but I don't think that is what you wanted. Note that ' is for string literals and ` is for escaping names in Beam SQL's default dialect config. Try: SELECT `id` FROM PCOLLECTION On Tue, May 11, 2021 at 10:58 AM Tao Li <t...@zillow.com<mailto:t...@zillow.com>> wrote: @Andrew Pilloud<mailto:apill...@google.com> 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 <apill...@google.com<mailto:apill...@google.com>> Reply-To: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>> Date: Monday, May 10, 2021 at 7:46 PM To: user <user@beam.apache.org<mailto:user@beam.apache.org>> Cc: Yuan Feng <yua...@zillowgroup.com<mailto:yua...@zillowgroup.com>> 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%7Ce1a0e4eee6164a829fd508d915083af1%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637563947363358745%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Qsb45DB8XYfDorAY9PauHRexgHoUCGS0LNDXbaKU%2B9g%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%7Ce1a0e4eee6164a829fd508d915083af1%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637563947363368701%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=iQ2P%2BERX9%2BlrWi9xuUFecpJcGvWqxbn5BjiVhz%2F%2FHA4%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 <t...@zillow.com<mailto:t...@zillow.com>> 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 <t...@zillow.com<mailto:t...@zillow.com>> Reply-To: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>> Date: Monday, May 10, 2021 at 7:19 PM To: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>> Cc: Yuan Feng <yua...@zillowgroup.com<mailto:yua...@zillowgroup.com>> Subject: Re: A problem with calcite sql Never mind. Looks like “user” is a reserved name. From: Tao Li <t...@zillow.com<mailto:t...@zillow.com>> Reply-To: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>> Date: Monday, May 10, 2021 at 7:10 PM To: "user@beam.apache.org<mailto:user@beam.apache.org>" <user@beam.apache.org<mailto:user@beam.apache.org>> Cc: Yuan Feng <yua...@zillowgroup.com<mailto:yua...@zillowgroup.com>> 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" ...
