How to Fetch the Data from Bigquery with Java

Table of contents

Reading Time: 2 minutes

BigQuery is a managed data warehouse and is part of the Google Cloud Platform. so in this blog, we can learn how to fetch the data from bigquery with java.

BigQuery Jobs

Jobs are actions that BigQuery runs on your behalf to load data, export data, query data, or copy data.
When you want to run a query or transfer data from one dataset to another, you send a job description to your BigQuery instance, and it is executed as a job to get the final result.

Query And Code For Fetching data From an Existing Table

Fetching the Row count from Bigquery Table.

Query : SELECT Count(1) FROM tableName
Code : 
public static AtomicLong Count(String tableName, String topic) throws InterruptedException {
    AtomicLong countBeforeInsertion = new AtomicLong();
    TableResult beforeInsertionCountQuery =
        Utility.bqTableQuery(
                ConsumerContractConstants.queryForCount
                    .replace("tableName", tableName))
            .getQueryResults();
    beforeInsertionCountQuery
        .getValues()
        .forEach(fieldValueList -> countBeforeInsertion.set(fieldValueList.get(0).getLongValue()));
    return countBeforeInsertion;
  }

2. Fetching the Latest Row from Bigquery Table.

Query : SELECT * FROM tableName WHERE insertion_time_stamp=(SELECT max(insertion_time_stamp) FROM tableName)
Code : 
Job queryJob =
        Utility.bqTableQuery(
            ConsumerContractConstants.queryForInsertionTime.replace(
                "tableName", ConsumerContractConstants.dlqTableName));

3. Fetching the MetaData (Modification/Creation/Updation Time) from Bigquery Table.

Query : 
 "SELECT  TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time, FROM "
          + projectEnvironment
          + "."
          + cd_ds_datasetName
          + ".__TABLES__ where table_id = "
          + "'"
          + tableNameBq
          + "'";
Code : 
public static ZonedDateTime ModifiedTime(String tableName) throws InterruptedException {
    AtomicReference<String> timeBeforeInsertion = new AtomicReference<>();
    TableResult beforeInsertionTimeQuery =
        Utility.bqTableQuery(
                ConsumerContractConstants.modificationTimeQuery.replace("tableName", tableName))
            .getQueryResults();
    beforeInsertionTimeQuery
        .getValues()
        .forEach(fieldValueList -> timeBeforeInsertion.set(fieldValueList.get(0).getStringValue()));
    ZonedDateTime zonedBeforeDateTime = getDateTime(timeBeforeInsertion);
    return zonedBeforeDateTime;
  }

Steps for connection to Bigquery

Initialize the BigQuery service
Create a job to run our query
Run the job on BigQuery and Export the “GOOGLE CREDENTIALS”.
Run the Test cases ( there we write the code to fetch the data from bigquery)
Check the result

Creating a New Service Account

To enable your Java application to execute jobs on BigQuery, it requires a service account.
To create a service account, go to the service accounts page and click on “CREATE SERVICE ACCOUNT”.
In the creation page, we need to provide a name, and give the “BigQuery Admin” role: