DEV Community

Jordi Escudé Gòdia 🎗️
Jordi Escudé Gòdia 🎗️

Posted on

7 1

Load a JSON file to Google BigQuery using Python

I'm starting to learn Python to update a data pipeline and had to upload some JSON files to Google BigQuery. Hope this helps people in need!

See GCP documentation (for a CSV example).

Steps before running the script:

  • Create a Google service account with BigQuery permissions.
  • Download the json key. Do not commit into git! Use .gitignore if needed.
  • Add the key to your .env variable. This will get load via load_dotenv library. Again, do not commit .env into git!

Example of your .env

GOOGLE_APPLICATION_CREDENTIALS=your-gcp-project-name-aaa333111aaa.json
  • Create the dataset via GCP Console, in the BigQuery section.
  • Run the script!

upload_json_to_bq.py script:

from google.cloud import bigquery

from google.oauth2 import service_account
from dotenv import load_dotenv
load_dotenv()

client = bigquery.Client()
filename = '/path/to/file/in/nd-format.json'
dataset_id = 'DatasetName'
table_id = 'TableName'

dataset_ref = client.dataset(dataset_id)
table_ref = dataset_ref.table(table_id)
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
job_config.autodetect = True

with open(filename, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="europe-west1",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.

print("Loaded {} rows into {}:{}.".format(job.output_rows, dataset_id, table_id))

Note: the file must be a JSON newline delimited file.

Tiugo image

Modular, Fast, and Built for Developers

CKEditor 5 gives you full control over your editing experience. A modular architecture means you get high performance, fewer re-renders and a setup that scales with your needs.

Start now

Top comments (0)

Image of Stellar post

🚀 Stellar Dev Diaries Series: Episode 1 is LIVE!

Ever wondered what it takes to build a web3 startup from scratch? In the Stellar Dev Diaries series, we follow the journey of a team of developers building on the Stellar Network as they go from hackathon win to getting funded and launching on mainnet.

Read more

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay