Losing Data while uploding CSV to Bucket

Nelson Romero 0
Viewed 76

To put it in context, I have a bucket where I storage CSV files and a function that works to put that Data into a Database when you load new CSV into the bucket. I try to upload 100 CSV at the same time, in all, 581.100 records (70 MB) All of those files appears in my bucket and a new table is created. But when I do a “select count” I only found 267306 records (46 % of the total) I try to do it again, different bucket, function, and table, I try to upload another 100 files, 4.779.100 records this time (312 MB) When I check the table in big query I realize that only 2.293.920 records exist (47,9%) of the one that supposedly exist. So my question is, is there a way in which I can upload all the CSV that I want without losing data? Or does GCP have some restriction for that task? Thank you.

enter image description here

bucket function csv google-cloud-platform
1 Answers

As pointed out in your last comment:

google.api_core.exceptions.Forbidden: 403 Exceeded rate limits: too many table update operations for this table

This error shows that you have reached the limit for maximum rate of table metadata update operations per table for Standard tables, according to the documentation. You can review the limits that may apply here. Note that this quota cannot be increased.

In the diagnosis section, it says:

Metadata table updates can originate from API calls that modify a table's metadata or from jobs that modify a table's content.

As a resolution, you can do the following:

  • Reduce the update rate for the table metadata.
  • Add a delay between jobs or table operations to make sure that the update rate is within the limit.
  • For data inserts or modification, consider using DML operations. DML operations are not affected by the Maximum rate of table metadata update operations per table rate limit. DML operations have other limits and quotas. For more information, see Using data manipulation language (DML).
  • If you frequently load data from multiple small files stored in Cloud Storage that uses a job per file, then combine multiple load jobs into a single job. You can load from multiple Cloud Storage URIs with a comma-separated list (for example, gs://my_path/file_1,gs://my_path/file_2), or by using wildcards (for example, gs://my_path/*). For more information, see Batch loading data.
  • If you use single-row queries (that is, INSERT statements) to write data to a table, consider batching multiple queries into one to reduce the number of jobs. BigQuery doesn't perform well when used as a relational database, so single-row INSERT statements executed at a high speed is not a recommended best practice.
  • If you intend to insert data at a high rate, consider using BigQuery Storage Write API. It is a recommended solution for high-performance data ingestion. The BigQuery Storage Write API has robust features, including exactly-once delivery semantics. To learn about limits and quotas, see Storage Write API and to see costs of using this API, see BigQuery data ingestion pricing.
Rogelio Monter0
Related