Loading data to BigQuery using python API vs bq load

Question

Got a new requirement. In GCS bucket have around 130+ files and these files need to be loaded into different tables on BigQuery on daily basis.

After researching, I found two options.

1) Use "bq load" command to load (Shell Script/Python Script) 2) Create a Python API to load the data to BigQuery

Which option is best. If I go with Python API, I need use APPENGINE to schedule it.

is there any better option other than this?

Thanks,

Answer 1

There are a few differences: a) BQ Load:

-You can have some issues using special chars as delimiters, like ^ and |.

-You don't need a service account (You can use a user account)

-You can't use it on google cloud functions

b) API

-You don't have the special chars trouble.

-You can use it on google cloud functions

-And if you create a python script, you can schedule it on Scheduled Tasks (On Windows)

Answer 2

However you do it, you'll be creating load jobs. So from the BigQuery side of things, it doesn't really matter which option you choose.

As far as scheduling goes, you do have some options on Google Cloud Platform:

• App Engine standard environment cron service.
  • See this example for using this to reliably schedule tasks via Pub/Sub.
• Your operating system's cron or systemd timers on a Compute Engine instance.
• A cron job on a Kubernetes cluster, using Container Engine.