How To Download A BigQuery Result Into a CSV File Using Kotlin
In this tutorial we are going to see how we can download the result of a BigQuery query into a CSV file in our computer by using Kotlin, the Java BigQuery API Client Library provided by Google and the OpenCSV Library.
Let’s get it Started!
Set Up
You should start by setting up authentication to run the client library. You can do it by following the official documentation.
For examples purposes, I will start a new Kotlin project using the Gradle Build Tool and add the BigQuery and the OpenCSV dependencies:
implementation("com.google.cloud:google-cloud-bigquery:1.117.1")
implementation("com.opencsv:opencsv:5.2")Function To Fetch The Result
We will use the Shakespeare sample data table provided by Google in our example, which has four columns:
Now let’s create our function to fetch the result. Taking the official documentation as example, we will have something like:
This function will run the query defined in the 7th line:
"SELECT * FROM `bigquery-public-data.samples.shakespeare`LIMIT 10;"And then return the result, which is a TableResult object.
Creating Our Data Class
Now, we need to create the data class that will hold the information of each row from our result and that will be used to write all the information into our csv file:
Function To Convert Results
Let’s implement a function that will convert our TableResult into a list of Shakespeare objects, the class defined in the previous part of this tutorial:
Simple function, it will just iterate over all the results in the tableResult object, create Shakespeare objects and then add then to the list of Shakespeare objects.
Beware that in line #3 I added the not-null assertion operator (
!!) because I knew my result was not going to be null. Unless you are a NPE lover, you should implement some logic to make sure your result is not null.
Function To Write Into CSV File
Now that we already have our list of Shakespeare objects, we can proceed and write them down in a CSV file. To do so, we are going to use the OpenCSV library added as a dependency on Gradle in the beginning of this tutorial.
In this function, we define a path where the file will be saved to, we then instantiate a file writer that will be used by the SatefulBeanToCsv object to write the file.
Results
After running our methods, we will have something like this:
Pretty easy, isn’t it? 🕺
I’m very happy you got to this part of the story, I‘m truly thankful for this.
Support my work: follow me and clap to this story.
Conclusion
In this tutorial you learned how to connect your Spring Boot Application to Google Big Query, query a dataset, convert the data and then save it to a CSV file.
I hope you have enjoyed it and see you next time!
Contribute
Writing takes time and effort. I love writing and sharing knowledge, but I also have bills to pay. If you like my work, please, consider donating through Buy Me a Coffee: https://www.buymeacoffee.com/RaphaelDeLio
Or by sending me BitCoin: 1HjG7pmghg3Z8RATH4aiUWr156BGafJ6Zw
Follow Me on Social Media
Stay connected and dive deeper into the world of Kotlin with me! Follow my journey across all major social platforms for exclusive content, tips, and discussions.
