DataStream CloudSQL Postgres to BigQuery: Numeric synced as String

[jairoperezaquin]

Seems to be happening with this specific data type, I have the following fields in my source.

Datastream->Objetcs->View Details:

But BigQuery shows the column as strings, so I can't perform math operations

Is there anything in the stream I should look into? Thanks!!

[-Rhett · Accepted solution]

Hi @jairoperezaquin,

Welcome to the Google Cloud Community!

This type of data discrepancy during streaming from Datastream to BigQuery can occur because of several reasons such as:

• Differences in write behavior configuration of Datastream
• Known limitations of Datastream
• Known limitations of BigQuery

In our case, when PostgreSQL NUMERIC columns are set with undefined precision (indicated by a precision value of -1), Datastream maps them to BigQuery STRING columns. Refer to the data type conversion table under PostgreSQL NUMERIC for more details.

To resolve this, we can either change the arbitrary precision to a positive value, or use a data type other than NUMERIC. You can also consider mapping PostgreSQL data types to Datastream unified types instead to make them more generally supported if ever you decide to change data destinations or want to have multiple data destinations in the future.

I hope this helps!

[jairoperezaquin]

Thanks a lot!