Get TableSchema from BigQuery result PCollection 2019-01-28 10:12发布 站内问答 / Java 468 2 5

Question

When I run a query in BigQuery Web UI, the results are displayed in a table where both name and type of each field are known (even when a field is a result of COUNT(), AVG(), ... operation, type of field is known, of course). The results can be then directly exported as a table/json/csv.

My question is, when I retrieve query results in my java project, e.g. with a query:

String query =  "SELECT nationality, COUNT(DISTINCT personID) AS population 
                 FROM Dataset.Table 
                 GROUP BY nationality";

PCollection<TableRow> result = p.apply(BigQueryIO.Read.fromQuery(query));

... is it possible to obtain the schema of TableRow in result PCollection, without explicitly defining it? I think it must be possible, since it's possible with the same query when using BigQuery Web UI. But I can't figure out how to do it ...

TableSchema schema =  // function of PCollection<TableRow> result ?

result.apply(BigQueryIO.Write
                .named("Write Results Table")
                .to(getTableReference(tableName))
                .withSchema(schema));

That way query results could be always automatically exported/saved into a new table (only the table name then needs to be explicitly provided).

Any ideas? Any help would be appreciated :)

Answer 1

Conceptually - you should write the function which will iterate thru all cells of given TableRow and for each - get name and type and while iterating you will create respective TableSchema.
For simple schemas, I would expect, it should be relatively easy.
For schemas with records, repeated, etc. this could be more complex

Answer 2

Unfortunately, Dataflow SDK doesn't expose a schema returned by BigQuery via Dataflow's BigQueryIO API. There's no "good" workaround within the Dataflow API alone.

Defining a schema manually is one workaround.

Alternatively, you could make a separate query to BigQuery directly via jobs: query at pipeline construction time, whose result can then be passed to BigQueryIO.Write transform. This may incur additional cost, but that can probably be mitigated by altering the query slightly to reduce the amount of data processed. Correctness of the output is not relevant, since you'd be storing the schema only.