BigQuery Subscription Error Incompatible schema type DOUBLE vs. FLOAT

[taufiqibrahim]

Hi All,

I am trying to create BigQuery Subscription from Pub/Sub topic to ingest data into BigQuery based on https://cloud.google.com/pubsub/docs/create-subscription#subscription

First, I created an empty table on BigQuery:

CREATE OR REPLACE TABLE tibrahim_debezium.mysql_inventory_products (
  id INT64 NOT NULL,
  name STRING,
  description STRING,
  weight FLOAT64,
  __op STRING,
  __table STRING,
  __source_ts_ms INT64,
  __deleted STRING
)

Then, I created a Pub/Sub schema using this command:

gcloud pubsub schemas create mysql.inventory.products-schema \
        --type=AVRO \
        --definition='
{
 "type" : "record",
 "name" : "MysqlInventoryProductsSchema",
 "fields" : [
    {
        "type": "int",
        "optional": false,
        "name": "id"
    },
    {
        "type": "string",
        "optional": false,
        "name": "name"
    },
    {
        "type": "string",
        "optional": true,
        "name": "description"
    },
    {
        "type": "float",
        "optional": true,
        "name": "weight"
    },
    {
        "type": "string",
        "optional": true,
        "name": "__op"
    },
    {
        "type": "string",
        "optional": true,
        "name": "__table"
    },
    {
        "type": "int",
        "optional": true,
        "name": "__source_ts_ms"
    },
    {
        "type": "string",
        "optional": true,
        "name": "__deleted"
    }
 ]
}'

The schema successfully created.

I continue created Pub/Sub topic with above schema.

gcloud pubsub topics create mysql.inventory.products --message-encoding=json --schema=mysql.inventory.products-schema

The topic successfully created.

Next, I created the BigQuery subscription using below command:

gcloud pubsub subscriptions create mysql.inventory.products-bq-sub --topic mysql.inventory.products --bigquery-table=<PROJECT_ID_REDACTED>.<DATASET_REDACTED>.mysql_inventory_products --use-topic-schema

It returned error: ERROR: Failed to create subscription [projects/<PROJECT_REDACTED>/subscriptions/mysql.inventory.products-bq-sub]: Incompatible schema type for field weight: DOUBLE vs. FLOAT.

The doc https://cloud.google.com/pubsub/docs/bigquery#avro-to-zetasql showing that float should be fine.

What could be the issue here?

[iamflimflam1]

Having the same problem. Simple examples without floats work fine.

I also tried it with protocol buffers as the schema and had the same problem.

[ugo-poool]

Yep I can confirm you're not alone.

Even protobuf float doesn't match bigquery float. 

[qiqiwu]

We are aware of the issue affecting schemas that use floats and doubles and have a fix in progress. Apologies for the inconvenience.

[taarak18]

Awaiting resolution to the issue. Also any roadmap for the following:-
1. Allowing external imports in the topic schema, like something equivalent to timestamp? As of now, the new BQ tables can only be partitioned by the ingestion_timestamp.
2. Allowing proto enums to be stored as String instead of int64 in BQ

[obrep]

Is there any timeline on the fix? 

[qiqiwu]

We are targeting the fix to be out by August 19th.

[bausoft]

@qiqiwu 

---

@taarak18 wrote:

Awaiting resolution to the issue. Also any roadmap for the following:-
1. Allowing external imports in the topic schema, like something equivalent to timestamp? As of now, the new BQ tables can only be partitioned by the ingestion_timestamp.
2. Allowing proto enums to be stored as String instead of int64 in BQ

---

[AlanZSS]

same here.
waiting for the support of Avro logicalType in BigQuery Subscription and the float/double issue

https://stackoverflow.com/questions/73210614/pubsub-bigquery-subscription-using-topic-schema

[qiqiwu]

Regarding the feature requests mentioned above, they are currently under consideration.

[cagriyonca]

Hello @qiqiwu ,
Is there any update about this problem? We've also stuck here, waiting for your solution.

[obrep]

@qiqiwu any news  on the fix? 

[cagriyonca]

It's been solved guys,

BQ subscriptions allow sending float now.

Thanks Google!

[qiqiwu · Accepted solution]

Yes, confirming this is now fixed.

[ponypony]

Hi,

Was searching and found this, any update on this one:

2. Allowing proto enums to be stored as String instead of int64 in BQ

Thanks