Does Timestream have a function related to decoding base64 values?

Question

We have an `id` that's encoded in base64 and needs to be converted to `bigint` afterwards, and one of our Clients would like to see the real values.

So, we are trying to use `UNLOAD` in Timestream with the following query, but it doesn't seem to work:

```
UNLOAD(
	select cast(decode(id, 'base64') as bigint) id, time, measure_name, measure_value
	from "{tableName}"
	where time BETWEEN ago(7d) AND now()
)
TO 's3://{path}' WITH (format='CSV', compression='GZIP', include_header='true')
```

How do we achieve what we are trying to do?

Please advise 🙇‍♂️

Thank you.

Accepted Answer

It turned-out, AWS Timestream actually have functions in the query language that are not listed in any of their own Documentation sites.

As we can see on their [Developer Guide](https://docs.aws.amazon.com/timestream/latest/developerguide/reference.html), the **Note** section tells us that the query engine that they are using is/includes Trino. And if we go to the Documentation site of Trino, we can see that [Base64 encoding functions](https://trino.io/docs/current/functions/binary.html#base64-encoding-functions) are there just fine.

So, in the end, our query looked something like below:
```
UNLOAD(
	select   cast(from_big_endian_64(from_base64(id)) as bigint) id,
			date_format(time, '%Y-%m-%d %H:%i:%s') time,
  			case
  				when measure_name = '{f}' then '{foo}'
  				when measure_name = '{b}' then '{bar}'
  				else measure_name
			end as measure_name,
  			cast(measure_value::bigint as bigint) measure_value
	from "{db}"."{tableName}"
	where measure_name = ANY(values 'f','b','{etc}')
	and time between ago(4 * 30d) and now()
)
TO 's3://{path}' WITH (format='CSV', compression='GZIP', include_header='true')
```

I wish we could have shared this answer earlier.

Thank you all for sharing your findings. 🙇‍♂️

I hope this cleared things up for everybody.

Answer

Amazon Timestream does not have built-in functions for decoding base64 values directly within SQL queries. However, you can achieve the desired functionality by performing the base64 decoding outside of Timestream and then loading the decoded data into Timestream : -

Decode Base64 Outside of Timestream:
Use a programming language eg python  to decode the base64 values and convert them to the desired data type (e.g., bigint). This step can be performed before loading the data into Timestream.
here is an example of python to do it

```
import base64
# Sample base64-encoded ID
encoded_id = "your_base64_encoded_id_here"
# Decode base64 and convert to bigint
decoded_id = int.from_bytes(base64.b64decode(encoded_id), byteorder='big')
print(decoded_id)

```
Once you have decoded and converted the base64 values to bigint, you can proceed with loading the data into Timestream and querying it as needed.
Remember to handle errors and edge cases appropriately when decoding base64 values and converting them to the desired data type. Additionally, ensure that the data loading process into Timestream follows best practices and meets your performance and scalability requirements.

You can refer to this AWS documentation https://docs.aws.amazon.com/it_it/iot/latest/developerguide/timestream-rule-action.html  but i didn't see where it was written that you can decode the base 64 directly within SQL queries. I advice you to reach out to AWS Support team incase if you get any error

Hope it clarifies and if does I would appreciate answer to be accepted so that community can benefit for clarity, thanks ;)

Does Timestream have a function related to decoding base64 values?

Relevant content