CODEWITHSUNDEEP
google-bigquery
figaro
figaro

Reputation: 2325

Streaming data into google bigquery template table with date partition

I am trying to stream data into Big Query using templateSuffix and a date partition appended to the table name using Java API But I am getting the below exception:

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
"code" : 400,
"errors" : [ {
"domain" : "global",
"location" : "suffix",
"locationType" : "other",
"message" : "Table name should only contain _, a-z, A-Z, or 0-9.",
"reason" : "invalid"
} ],
"message" : "Table name should only contain _, a-z, A-Z, or 0-9."
}

I am using the API as : 

String tableName = "testTable$201701"; // 201701 is partition_id
TableDataInsertAllRequest request = new TableDataInsertAllRequest()
                    .setIgnoreUnknownValues(true)
                    .setRows(rows);

// add a template suffix
request.setTemplateSuffix(templateSuffix);

return bigquery
 .tabledata()
 .insertAll(projectId, datasetId, tableName, request)
 .execute();

Only templateSuffix or only date partition on the table works fine. But not both together. Any ideas how to get this resolved?

Upvotes: 0

Views: 940

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

There are two separate use cases – streaming data to daily sharded tables and streaming data to daily partitioned tables

Daily date-sharded tables are those that have separate table for each day vs. day partitioned table which is one table but partitioned “internally”

You can stream in any of those.
For streaming to happen – table must exist. So to avoid creating new table for each and every new day – template table is used (which is not needed if you have partitioned table).
So, either you stream to specific partition of day partitioned table or you stream to separate daily sharded tables using template table.
Not the both at the same time!

Upvotes: 1

Related Questions