CODEWITHSUNDEEP
google-bigquery
vidhya sagar
vidhya sagar

Reputation: 87

How do i create partitioned table from non partitioned table in BigQuery using query?

I have tried this solution Migrating from non-partitioned to Partitioned tables, but i get this error. "Error: Cannot query rows larger than 100MB limit."

Job ID: sandbox-kiana-analytics:bquijob_4a1b2032_15d2c7d17f3.

Upvotes: 1

Views: 563

Answers (1)

Omid Fatemieh
Omid Fatemieh

Reputation: 66

Vidhya,

I internally looked at the query you sent to BigQuery, and can see that as part of your query, you are using ARRAY_AGG() to put all data for a day in one row. This results in very large rows, which ultimately exceed Big Query's 100MB per-row limit. This is a rather complex and inefficient way of partitioning the data. Instead, I suggest using the built-in support for data partitioning provided by BigQuery (example here). In this approach, you can create an empty date-partitioned table, and add day-partition data to it for each day.

Upvotes: 1

Related Questions