Convert JSON to Parquet files on Google Cloud Storage using BigQuery

Search for a command to run...

No comments yet. Be the first to comment.
Background In today's data-driven world, the ability to efficiently query and manage large datasets is crucial for businesses. DuckDB, an in-process SQL OLAP database management system, offers a powerful solution for handling complex analytical queri...
I recently assisted a customer in troubleshooting an issue with their Google Cloud Composer environment and found myself delving deep to uncover the problem. They had 22 DAGs, but only 11 were visible in the UI. It turns out this is a known bug that ...
Background Recently, I assisted a customer who required protection for sensitive data in BigQuery, ensuring access was restricted to a small group of individuals. They wanted to prevent any accidental access by employees. To address this, I recommend...
Background IAM Authentication in Cloud SQL for PostgreSQL lets you manage database user access using Google Cloud Identity and Access Management (IAM) identities instead of traditional PostgreSQL usernames and passwords. This integrates database acce...
Background Directed Acyclic Graphs (DAGs) in Amazon Managed Workflows for Apache Airflow (MWAA) can be triggered in several ways, depending on how much automation and integration you need. While you can manually trigger DAGs using the Airflow UI, we ...
Do you have lots of files on Google Cloud Storage that you want to convert to a different format?
There are a few ways you can achieve it:
But what if there is a true serverless way? And you only need to write SQL to convert them?
Let me walk you through it.
I wrote a Python script to generate a gzipped JSONL file.
import gzip
import json
def main():
json_str = ''
for i in range(10):
json_str = json_str + json.dumps(dict(id=i, value=i * i)) + "\n"
json_bytes = json_str.encode("utf-8")
with gzip.open("test.jsonl.gz", "w") as fout:
fout.write(json_bytes)
if __name__ == "__main__":
main()
By following the Google document about BigQuery external table, I was able to query it:

To achieve this, I used BigQuery EXPORT DATA statement to "export" the data from BigQuery to Cloud Storage in PARQUET format:

After navigating to the Cloud Storage console, I can see the PARQUET file:

Finally, I'd like to verify the data is actually in the PARQUET file:

And yay!
Using BigQuery's native EXPORT DATA statement, I was able to convert gzipped JSONL files on Google Cloud Storage to PARQUET format.
You can also convert the files to other formats. It currently supports AVRO, CSV, JSON and PARQUET. It is straightforward and serverless.