CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-pubsub
Sridhar Srinivasan
Sridhar Srinivasan

Reputation: 69

Pub/Sub Topic to BigQuery Table Subscription

Pub/Sub topic schema

syntax = "proto3";

message UserProfile {
    string name= 1;
    string phone= 2;
    map<string, string> address= 3;
}

Topic data

{"name":"name","phone":"phone","address":{"streetname":"streetnamee","zipcode":"000000"}}

This would be consumed by a Big Query Subscription "Write To BigQuery"

I have selected :

  1. Use Topic Schema
  2. Drop Unknown fields

Unable to process the address data to the bq table, having tried 2 datatypes ARRAY and JSON

Big Query Table Schema

CREATE TABLE `dataset.tablename`
(
  name STRING OPTIONS(description=""),
  phone STRING OPTIONS(description=""),
  address ARRAY<STRUCT<a STRING, b STRING>>
)

or

    CREATE TABLE `dataset.tablename`
    (
      name STRING OPTIONS(description=""),
      phone STRING OPTIONS(description=""),
      address JSON
    );

when commented the address field its working

Upvotes: 1

Views: 707

Answers (1)

Kamal Aboul-Hosn
Kamal Aboul-Hosn

Reputation: 17271

For Cloud Pub/Sub to BigQuery subscriptions with a map type in the schema, you need to have table with an ARRAY of a STRUCT with fields named key and value:

CREATE TABLE `dataset.tablename`
(
  name STRING,
  phone STRING,
  address ARRAY<STRUCT<key STRING, value STRING>>
);

Upvotes: 2

Related Questions