1

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.

vidhya sagar
  • 87
  • 1
  • 10
  • Hello and welcome to StackOverflow. Please take some time to read the help page, especially the sections named ["What topics can I ask about here?"](http://stackoverflow.com/help/on-topic) and ["What types of questions should I avoid asking?"](http://stackoverflow.com/help/dont-ask). And more importantly, please read [the Stack Overflow question checklist](http://meta.stackexchange.com/q/156810/204922). You might also want to learn about [Minimal, Complete, and Verifiable Examples](http://stackoverflow.com/help/mcve). – herrbischoff Jul 10 '17 at 18:58

1 Answers1

1

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.

Omid Fatemieh
  • 66
  • 2
  • Thanks for your analysis! Currently, we have 2 TB of data, so if we want to do the query like this, then it will cost us more money.The cheapest way of doing this by exporting data to GCS, but the problem is that all dates can't be exported to GCS as partitioned. – vidhya sagar Jul 11 '17 at 08:29