Transfer from MySQL to BigQuery without exceeding limits?

2
votes

I have a large data set of millions of records across 5 tables. I am flattening the tables and trying to upload them in bigquery as one batch job. I will be using a ruby script to connect to mysql make the query and batch upload them into bigquery.

I will use this wrapper to connect to BigQuery https://github.com/abronte/BigQuery

This wrapper to connect to MySQL https://rubygems.org/gems/mysql

The idea is that I will query 100k records from mysql at a time and upload these, but I don't want to hit these limits... The following limits apply for streaming data into BigQuery.

Maximum row size: 1 MB
HTTP request size limit: 10 MB
Maximum rows per second: 100,000 rows per second, per table. Exceeding this amount will cause quota_exceeded errors. Maximum rows per request: 500
Maximum bytes per second: 100 MB per second, per table. Exceeding this amount will cause quota_exceeded errors.

Source: https://cloud.google.com/bigquery/streaming-data-into-bigquery

Questions:
(1) Am I re-inventing the wheel and there is something out-there that will do this already?
(2) Is there an easy way to mark what was uploaded in bigquery to prevent duplicates?
(3) Any way to avoid hitting these limits?

2
google-bigquery

2 Answers

5
votes

There are different ways to put data in BigQuery:

  • load jobs
  • streaming inserts

So what you are trying to do here is to load the data, and not stream it. Streaming is good from a live data source, but when you migrate a project it's easier to dump data out as CSV or JSON, then use the batch loading into BigQuery where you can load files big as 5TB (JSON).

If you do this way, all your hassle with the limits is gone, and you don't reinvent the wheel.

Streaming insert will be used later by your app to add on the go data your tables.

0
votes

Note that if you're reading from mysql, you can use mysqldump to dump to CSV. If bigquery is giving you problems with the CSV formatting, you may need to play with the formatting options (try --fields-terminated-by=\t --lines-terminated-by=\n, since tab separated formatting is a little bit more robust than comma separated formatting.

Once you've dumped the data to csv, you can upload it to google cloud storage then run a bigquery load job to import the entire thing at once.