1

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?

artofdoe
  • 167
  • 2
  • 14

2 Answers2

2

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.

ningk
  • 1,298
  • 1
  • 7
  • 7
1

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).

Kenn Knowles
  • 5,838
  • 18
  • 22
  • I ended up using a UDF. Any reason why the latest Beam is using such an old version of Calcite? – artofdoe Jun 15 '21 at 22:18
  • Yes. The reason is that Calcite makes *lots* of breaking changes with every release. There have been a few attempts at upgrades that never completed. There is a current effort to reach 1.26 at https://github.com/apache/beam/pull/14729 – Kenn Knowles Jun 16 '21 at 16:40