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> 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>
> *Date: *Tuesday, May 11, 2021 at 11:51 AM
> *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
>
>
>
> 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> wrote:
>
> @Andrew Pilloud <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>
> *Reply-To: *"user@beam.apache.org" <user@beam.apache.org>
> *Date: *Monday, May 10, 2021 at 7:46 PM
> *To: *user <user@beam.apache.org>
> *Cc: *Yuan Feng <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%7C2c8c6047a05842fde53008d914adce2d%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637563559001417793%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=C4bGHyhOjB%2Bhh2HPUAAhOKICQfTisBJ1FrE4AWah1QQ%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%7C2c8c6047a05842fde53008d914adce2d%7C033464830d1840e7a5883784ac50e16f%7C0%7C0%7C637563559001427748%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=QIRqCuy9IBCEjPWWcwmIW1TR%2BB5LjrJAMTK3VzP%2F93s%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> 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>
> *Reply-To: *"user@beam.apache.org" <user@beam.apache.org>
> *Date: *Monday, May 10, 2021 at 7:19 PM
> *To: *"user@beam.apache.org" <user@beam.apache.org>
> *Cc: *Yuan Feng <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>
> *Reply-To: *"user@beam.apache.org" <user@beam.apache.org>
> *Date: *Monday, May 10, 2021 at 7:10 PM
> *To: *"user@beam.apache.org" <user@beam.apache.org>
> *Cc: *Yuan Feng <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" ...
>
>
