IssueTracker

Hello,

Thank you for contacting the Google Cloud support team.

I have gone through your reported issue, however it seems like this is an issue observed specifically at your end within your project environment. It would need more specific debugging and analysis. To ensure a faster resolution and dedicated support for your issue, I kindly request you to file a support ticket by clicking here. Our support team will prioritize your request and provide you with the assistance you need.

Please note that the Issue Tracker is primarily meant for reporting commonly observed issues and requesting new features. For individual support issues, it is best to utilize the support ticketing system. I'm going to close this issue which will no longer be monitored. If you have any additional issues or concerns, please don’t hesitate to create a new issue on the Issue Tracker.

Thank you, we appreciate your cooperation!

There is no specific ETA at this time, though we are working on it. Hopefully by the end of the quarter.

We're just getting started with the bigQuery subscriptions and ran into this and are currently using the workaround.

defined our type for BigQuery as type = "INTEGER" and our type for avro as type = "long", logicalType = "timestamp-micros"

I was hoping to report whether the workaround worked for us; however, we're also impacted by the lack of support for nullable fields when using schemas defined as avro with json encoding so, dead in the water on that part at the moment.

Is there issue created for nullable fields problem?

yes. https://issuetracker.google.com/issues/242757468

This week, we have released support for strings matching the time-based types in BigQuery. However, we are still working on support for Avro logical types for time, which are based on integers. We are hoping to have support completed by the end of October.

Are your messages encoded in JSON or in Avro binary format?

Support for timestamp-micros, date, and time-micros is rolling out this week. Other types will be added in 2023Q3, though I expect we probably won't support the local variants of timestamp at any point. The documentation has been updated: https://cloud.google.com/pubsub/docs/bigquery#avro-logical-to-zetasql. The feature should be available in all regions by the end of the week.

The aforementioned support has rolled out.

local-timestamp-micros is not a supported logical type, so the only way to represent this in BQ would be as a long. From that point on, I believe it is just up to the application to treat it as a local timestamp when querying in BigQuery.

apratiksha@, I suspect they are not specifying the type correctly. One does not put "timestamp-micros" in the "type" field, but in the "logicalType" field. See https://avro.apache.org/docs/1.10.2/spec.html#Logical+Types.

I have this on my Avro schema:

{
  "name" : "timestamp",
  "type" : "long",
  "logicalType" : "timestamp-micros"
}

But I'm still getting a Incompatible schema type for field timestamp: INT64 vs. TIMESTAMP error when creating the PubSub subscription. What am I doing wrong?

Is that type within a record type or the entire schema? What is the definition for the column in BigQuery? I have a schema defined as follows:

{
  "type": "record",
  "namespace": "unit.test",
  "name": "Message",
  "fields": [
    { "name": "time_micros_field", "type": { "type": "long", "logicalType": "timestamp-micros" }}
  ]
}

and a BigQuery table with the schema

[{
	"name": "time_micros_field",
	"type": "TIMESTAMP",
	"mode": "NULLABLE"
}]

and the table creation works as expected.

What I sent was the definition of just one the record fields. Looking at your example, I realized that the syntax on my Avro schema was wrong. So by replacing:

{
  "name" : "timestamp",
  "type" : "long",
  "logicalType" : "timestamp-micros"
}

with

{
  "name" : "timestamp",
  "type" : { "type": "long", "logicalType": "timestamp-micros" }
}

the issue was fixed.

Thanks a lot for you help!

Weird, I still cannot get this one working. I have defined entire schema like this:

{"name": "created_at", "type": {"type": "long", "logicalType": "timestamp-micros"}}

And bigquery is:

[{
	"name": "created_at",
	"type": "TIMESTAMP",
	"mode": "NULLABLE"
}]

But I'm still getting this:

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

You need to define it as a record schema:

{
  "type": "record",
  "name": "Message",
  "fields": [
    { "name": "created_at", "type": { "type": "long", "logicalType": "timestamp-micros" }}
  ]
}

You are showing two different fields in your example: The field name in the topic schema is "time" and in the table is "UpdatedAt."

The addition of decimal time was more difficult than anticipated. We are now hoping to launch in 2023Q4, though that may still slip. Once it is available, we will update this ticket.

We now have support for the decimal and duration types. The only remaining types that we may support at some point are time-millis and timestamp-millis. We don't plan to support the local variations of timestamp as they don't really make sense to translate to a TIMESTAMP type since there is no notion of "local" time for the timestamp to which we'd convert.