CODEWITHSUNDEEP
typesgoogle-bigquerygoogle-cloud-pubsub
mehere
mehere

Reputation: 1556

Map Pubsub topic Avro schema datatype to BigQuery

I have a pubsub topic with the below avro schema attached. I would like to map it to BigQuery table schema using push-to-bq subscription type with use_topic_schema as true.

    {
    "name" : "columnA",
    "type" : [ {
      "type" : "bytes",
      "logicalType" : "decimal",
      "precision" : 18,
      "scale" : 4
    }, "null" ]
  }, {
    "name" : "columnB",
    "type" : [ {
      "type" : "bytes",
      "logicalType" : "decimal",
      "precision" : 19,
      "scale" : 4
    }, "null" ]
  }, {
    "name" : "columnC",
    "type" : [ {
      "type" : "bytes",
      "logicalType" : "decimal",
      "precision" : 38,
      "scale" : 6
    }, "null" ]
  }

Tried to map it to below BigQuery schema,

{
    "mode": "NULLABLE",
    "name": "columnA",
    "type": "FLOAT"
  },
  {
    "mode": "NULLABLE",
    "name": "columnB",
    "type": "NUMERIC"
  },
  {
    "mode": "NULLABLE",
    "name": "columnC",
    "type": "FLOAT"
  }

But receiving the below incompatible error. This error is received for all 3 columns columnA, columnB, columnC.

│ Error: Error creating Subscription: googleapi: Error 400: Incompatible schema type for field NetSalesLocalCurrency: BYTES vs. FLOAT
│ Details:
│ [
│   {
│     "@type": "type.googleapis.com/google.rpc.ErrorInfo",
│     "domain": "pubsub.googleapis.com",
│     "metadata": {
│       "actual_value": "FLOAT",
│       "expected_value": "BYTES",
│       "field_name": "ColumnC",
│       "reason": "INCOMPATIBLE_TYPE"
│     },
│     "reason": "INCOMPATIBLE_SCHEMA"
│   }
│ ]

Upvotes: 0

Views: 673

Answers (1)

Kamal Aboul-Hosn
Kamal Aboul-Hosn

Reputation: 17216

Cloud Pub/Sub to BigQuery does not support converting the logical Avro type decimal into a numeric type in BigQuery. You need to use the BYTES column type in your BigQuery table schema for the three columns. See the BigQuery subscription schema compatibility documentation for what type conversions are supported.

Upvotes: 1

Related Questions