'How can I access Schema from the QueryResponse while calling getQueryResults method from my Java application?

I am using google.cloud.bigquery library to execute and create query using bigquery.query() method. I want to fetch the Schema details from the response but whenever the query returns no result, I am getting EmptyTableResult instead of the response which should have Schema and Fields listed inside it. I used another approach which creates job and then using the query job, I am calling bigquery.getQueryResults which should return QueryResponse object. Below is the code snippet.

QueryJobConfiguration queryConfig = 
QueryJobConfiguration.newBuilder(queryString)

.setDefaultDataset(bqDatasetId).setUseLegacySql(false)

 .setFlattenResults(true).build();

JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = bigQuery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

        // Wait for the query to complete.
        queryJob = queryJob.waitFor();

        // Check for errors
        if (queryJob == null) {
          throw new RuntimeException("Job no longer exists");
        } else if (queryJob.getStatus().getError() != null) {
          throw new RuntimeException(queryJob.getStatus().getError().toString());
        }

        // Get the results.
        QueryResponse response = bigQuery.getQueryResults(queryJob.getJobId());
        System.out.println(response);

Here, in sysout statement, I am getting the proper response, but whenever I am trying to use response.getSchema(), it is giving me compilation error saying getSchema() is not visible. Can anyone help me with this? Is this approach correct or there is any other approach which can do the same thing?

javagoogle-cloud-platformgoogle-bigquery


Solution 1:[1]

After searching a lot, I came to a conclusion that it is better to use reflections to invoke the invisible QueryRespons.getSchema() method and it worked like a charm. Though, reflection is not an ideal solution for this but it has resolved my problem.

Solution 2:[2]

You need to call getQueryResults() on the Job object instead. This will give you back a TableResult object. You can then call getSchema() to get the schema of the query/table/job. So, putting it all together:

QueryJobConfiguration queryConfig =
                QueryJobConfiguration.newBuilder(
                        "SELECT "
                                + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
                                + "view_count "
                                + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
                                + "WHERE tags like '%google-bigquery%' "
                                + "ORDER BY favorite_count DESC LIMIT 10")
                        .setUseLegacySql(false)
                        .build();

        JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = BIGQUERY.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
        queryJob = queryJob.waitFor();

        TableResult result = queryJob.getQueryResults(); //<--you need this
        Schema schema = result.getSchema(); //<--..and this

        System.out.println(schema);

Which yields:

Connected to the target VM, address: '127.0.0.1:64695', transport: 'socket'
Schema{fields=[Field{name=url, type=STRING, mode=NULLABLE, description=null}, Field{name=view_count, type=INTEGER, mode=NULLABLE, description=null}]}

Solution 3:[3]

public static JSONArray convert(TableResult resultSet) throws Exception {

    JSONArray jsonArray = new JSONArray();
    Schema schema = resultSet.getSchema();
    FieldList MyFields = schema.getFields();
    Field MyField2 = null;
    for (FieldValueList row : resultSet.iterateAll()) {

        JSONObject obj = new JSONObject();
        for (int i = 0; i < MyFields.size(); i++) {
            MyField2 = MyFields.get(i);
            obj.put(MyField2.getName().toLowerCase(), row.get(i).getValue());
        }
        jsonArray.put(obj);
    }
    return jsonArray;

}

Solution 4:[4]

Another approach is to use the solution in this ticket Querying Column Headers in GBQ

This is running a query and not using the api

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Manushi
Solution 2 Graham Polley
Solution 3 olivier
Solution 4 Tamir Klein

Related Questions