CODEWITHSUNDEEP
sqlapache-beamapache-calcite
artofdoe
artofdoe

Reputation: 197

Calcite SQL does not recognize CONVERT_TIMEZONE in Apache Beam

I'm running a simple Sql transform in my Beam pipeline:

SELECT CONVERT_TIMEZONE('America/New York', 'UTC',(TIMESTAMP '1970-01-01 00:00:00' + OriginalTZ * INTERVAL '1' SECOND)) as MyUTC FROM PCOLLECTION

But i get the error:

 No match found for function signature CONVERT_TIMEZONE(<CHARACTER>, <CHARACTER>, <TIMESTAMP>)

But according to the Calcite documentation, that's how the syntax is.

I'm using Apache Beam version 2.29. But it seems that the Calcite sql version within is an old version 2.20 from 2019.

How can I use the CONVERT_TIMEZONE in my Calcite sql?

Upvotes: 1

Views: 503

Answers (2)

Kenn Knowles
Kenn Knowles

Reputation: 6033

I confirmed that this doesn't work: https://github.com/apache/beam/pull/15010/files

However, you can use SqlTransform#registerUdf to add your own (and you can use Calcite's Apache 2.0 licensed code exactly if you want to).

Upvotes: 1

ningk
ningk

Reputation: 1383

Apache Beam is currently on Calcite SQL 1.20 while CONVERT_TIMEZONE was added to 1.21.

A workaround is to use a normal ParDo or Map to convert the timezone. If you are using Python, some examples: Python Timezone conversion.

Upvotes: 2

Related Questions