CODEWITHSUNDEEP
google-bigquerygoogle-cloud-dataflow
nxverma
nxverma

Reputation: 3

BigQuery Table Column

I have below json 

{attribute_values=[{key=PO, values=[234234, 21]}, {key=POReceipt, values=[ABC]}]}

How i will define attribute_values Column?

currently I defined as String and getting error while inserting via Dataflow

"errorMessage": "{\n  \"errors\" : [ {\n    \"debugInfo\" : \"\",\n    \"location\" : \"attribute_values\",\n    \"message\" : \"Array specified for non-repeated field.\",\n    \"reason\" : \"invalid\"\n  } ],\n  \"index\" : 0\n}",
    "stacktrace": null
  }

Upvotes: 0

Views: 477

Answers (2)

Christopher
Christopher

Reputation: 941

You have a BQ table with column attribute_value with datatype string and now you want to change the datatype of that column. There are 2 options you can choose from

  1. Using a query, selecting all the column in the table then cast the column you want to change then write it to a destination table (although the cast doesn't allow you to cast from string to array/struct Casting)
  2. Export your data (to GCS) then loading it to a new table

Please check this documentation Changing a column data type for more details

Upvotes: 0

Tamir Klein
Tamir Klein

Reputation: 3632

How I will define attribute_values Column?

You can create your table as follow:

-- create table
 CREATE TABLE IF NOT EXISTS  `projectId.datasetId.tableName`
 (   
  attribute_values STRUCT <key String, values ARRAY<STRING>>
 )

You can use the below INSERT sql example to test how to populate your table

INSERT INTO 
    `projectId.datasetId.tableName` (attribute_values)
values (
STRUCT <key String, values ARRAY<STRING>>('PO', ['234234', '21'])
);

INSERT INTO 
    `projectId.datasetId.tableName` (attribute_values)
values (
STRUCT <key String, values ARRAY<STRING>>('POReceipt', ['ABC'])
);

enter image description here

To do this via code you can check this answer for more details.

Upvotes: 1

Related Questions