How to Fetch the Data from Bigquery with Java

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

1. 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

1. Initialize the BigQuery service
2. Create a job to run our query
3. Run the job on BigQuery and Export the “GOOGLE CREDENTIALS”.
4. Run the Test cases ( there we write the code to fetch the data from bigquery)
5. 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:

• Now we can create a key by going into the “Keys” tab of the service account we created:

• You should be able to download this key as a JSON file. We will be using this in our Java application later.

Reference

https://cloud.google.com/bigquery/docs/jobs-overview