How to Fetch the Data from Bigquery with Java

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.
the bigquery client library converts queries to jobs and returns result obejcts

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:
service account page
  • Now we can create a key by going into the “Keys” tab of the service account we created:
create key page
  • 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


Knoldus-blog-footer-image

Written by 

Prajjawal is a QA Consultant having experience of more than 1.4 year. He is familiar with core concepts of manual & automation testing using tools like Contract test, Selenium, and Postman Also having knowledge of Core Java, Python and Data Science. He is always eager to learn new and advanced concepts in order to improve himself. He likes to watch web series and play cricket.

Leave a Reply