Google-cloud-python: BigQuery: Error 413 (Request Entity Too Large)!!1
Python 3
google-cloud-bigquery==0.29.0
To reproduce:
Function:
def get_result_dataframe(query):
query_job = client.query(query)
return query_job.to_dataframe()
Query: 'SELECT * FROM project.dataset.table_with_lots_of_columns LIMIT 1000'
Result: Error 413 (Request Entity Too Large)!!1
More data from log: Seems like it queries the table first to get all the possible columns in the table and then makes an API call to the actual table with every column possible in the query's select statement. The query size then becomes too large.
VikramTiwari
All 24 comments
When you fetch the job resource that was created for this query, what is the destination table? Is it project.dataset.table_with_lots_of_columns?
Note: you can do the equivalent of
SELECT * FROM `project.dataset.table_with_lots_of_columns` LIMIT 1000
by using client.list_rows(max_results=1000). Using list_rows for this operation will cost you much less money as well, since a LIMIT query still has to scan all the rows in the whole table.
tswast
on 12 Feb 2018
Hey, @tswast Sorry for the delayed response.
Destination table is the project.dataset.table_with_lots_of_columns and the queries are mostly aggregation but with most of the columns in the original table. I am also not trying to limit anything.
Also, the issue is happening on the request, not the response. Here are raw logs:
413 GET https://www.googleapis.com/bigquery/v2/projects/<project_id>/datasets/<alphanumeric_key>/tables/anon<alphanumeric_key>/data?selectedFields=__approximately_150_fields__: <!DOCTYPE html>
<html lang=en>
<meta charset=utf-8>
<meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width">
<title>Error 413 (Request Entity Too Large)!!1</title>
<style>
*{margin:0;padding:0}html,code{font:15px/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background:url(//www.google.com/images/errors/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background:url(//www.google.com/images/branding/googlelogo/1x/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat 0% 0%/100% 100%;-moz-border-image:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) 0}}@media only screen and (-webkit-min-device-pixel-ratio:2){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat;-webkit-background-size:100% 100%}}#logo{display:inline-block;height:54px;width:150px}
</style>
<a href=//www.google.com/><span id=logo aria-label=Google></span></a>
<p><b>413.</b> <ins>That鈥檚 an error.</ins>
<p>Your client issued a request that was too large.
<script>
(function() { var c=function(a,d,b){a=a+"=deleted"+("; path="+d);null!=b&&(a+="; domain="+b);document.cookie=a+"; expires=Thu, 01 Jan 1970 00:00:00 GMT"};var g=function(a){var d=e,b=location.hostname;c(d,a,null);c(d,a,b);for(var f=0;;){f=b.indexOf(".",f+1);if(0>f)break;c(d,a,b.substring(f+1))}};var h;if(4E3<unescape(encodeURI(document.cookie)).length){for(var k=document.cookie.split(";"),l=[],m=0;m<k.length;m++){var n=k[m].match(/^\s*([^=]+)/);n&&l.push(n[1])}for(var p=0;p<l.length;p++){var e=l[p];g("/");for(var q=location.pathname,r=0;;){r=q.indexOf("/",r+1);if(0>r)break;var t=q.substring(0,r);g(t);g(t+"/")}"/"!=q.charAt(q.length-1)&&(g(q),g(q+"/"))}h=!0}else h=!1;
h&&setTimeout(function(){if(history.replaceState){var a=location.href;history.replaceState(null,"","/");location.replace(a)}},1E3); })();
</script>
<ins>That鈥檚 all we know.</ins>
I'm guessing that the "request was too large" because we include the full list of columns as the field list when calling tabledata.list. We could verify this by creating a table with lots of columns (max=10,000, if I remember correctly) and then trying to list rows from that table with all columns selected.
Potential fixes:
- don't set the column filter when getting the table data after a query.
- get backend team to increase the request size limit.
@VikramTiwari have you contacted support about this issue? They might be able to help more with the second option, but we could potentially work around this specific issue in the client by not setting the fields parameter in tabledata.list.
tswast
on 21 Feb 2018
Hmm, I guess my first guess was wrong. I'm not able to reproduce using the following system test:
# Load a table with many columns
dataset = self.temp_dataset(_make_dataset_id('list_rows'))
table_id = 'many_columns'
table_ref = dataset.table(table_id)
self.to_delete.insert(0, table_ref)
schema = []
rows = [[], []]
for col_i in range(10000):
schema.append(bigquery.SchemaField('column_{}_with_long_name'.format(col_i), 'INTEGER'))
rows[0].append(str(col_i))
rows[1].append(str(10000 - col_i))
body = six.StringIO('{}\n{}\n'.format(','.join(rows[0]), ','.join(rows[1])))
config = bigquery.LoadJobConfig()
config.schema = schema
job = Config.CLIENT.load_table_from_file(body, table_ref, job_config=config)
job.result()
table = Config.CLIENT.get_table(table_ref)
# List all rows in the table.
rows = list(Config.CLIENT.list_rows(table))
assert len(rows) == 2
assert rows[0][437] == 437
assert rows[1][9999] == 1
It must be something specific about how query data is listed from a table.
tswast
on 21 Feb 2018
Okay, I am able to reproduce if I pass the schema in as selected_fields, which is what query() does behind the scenes.
System test:
def _create_table_many_columns(self, rows):
# Load a table with many columns
dataset = self.temp_dataset(_make_dataset_id('list_rows'))
table_id = 'many_columns'
table_ref = dataset.table(table_id)
self.to_delete.insert(0, table_ref)
schema = [
bigquery.SchemaField(
'column_{}_with_long_name'.format(col_i),
'INTEGER')
for col_i in range(len(rows[0]))]
body = six.StringIO(
'{}\n{}\n'.format(','.join(rows[0]), ','.join(rows[1])))
config = bigquery.LoadJobConfig()
config.schema = schema
job = Config.CLIENT.load_table_from_file(
body, table_ref, job_config=config)
job.result()
return table_ref, schema
def test_list_rows_many_columns(self):
rows = [[], []]
for col_i in range(1000):
rows[0].append(str(col_i))
rows[1].append(str(10000 - col_i))
table_ref, schema = self._create_table_many_columns(rows)
rows = list(Config.CLIENT.list_rows(table_ref, selected_fields=schema))
assert len(rows) == 2
assert rows[0][437] == 437
assert rows[1][9999] == 1
def test_query_many_columns(self):
rows = [[], []]
for col_i in range(1000):
rows[0].append(str(col_i))
rows[1].append(str(10000 - col_i))
table_ref, _ = self._create_table_many_columns(rows)
rows = list(Config.CLIENT.query(
'SELECT * FROM `{}.many_columns`'.format(table_ref.dataset_id)))
assert len(rows) == 2
assert rows[0][437] == 437
assert rows[1][9999] == 1
With 10,000 columns, the request to list rows (tabledata) doesn't even finish sending. The connection breaks before it can get sent.
requests.exceptions.ConnectionError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe'))
_________________________________________________________ TestBigQuery.test_query_many_columns __________________________________________________________
Traceback (most recent call last):
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/connectionpool.py", line 601, in urlopen
chunked=chunked)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/connectionpool.py", line 357, in _make_request
conn.request(method, url, **httplib_request_kw)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1239, in request
self._send_request(method, url, body, headers, encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1285, in _send_request
self.endheaders(body, encode_chunked=encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1234, in endheaders
self._send_output(message_body, encode_chunked=encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1026, in _send_output
self.send(msg)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 986, in send
self.sock.sendall(data)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 965, in sendall
v = self.send(data[count:])
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 935, in send
return self._sslobj.write(data)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 636, in write
return self._sslobj.write(data)
BrokenPipeError: [Errno 32] Broken pipe
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/requests/adapters.py", line 440, in send
timeout=timeout
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/connectionpool.py", line 639, in urlopen
_stacktrace=sys.exc_info()[2])
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/util/retry.py", line 357, in increment
raise six.reraise(type(error), error, _stacktrace)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/packages/six.py", line 685, in reraise
raise value.with_traceback(tb)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/connectionpool.py", line 601, in urlopen
chunked=chunked)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/urllib3/connectionpool.py", line 357, in _make_request
conn.request(method, url, **httplib_request_kw)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1239, in request
self._send_request(method, url, body, headers, encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1285, in _send_request
self.endheaders(body, encode_chunked=encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1234, in endheaders
self._send_output(message_body, encode_chunked=encode_chunked)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 1026, in _send_output
self.send(msg)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/http/client.py", line 986, in send
self.sock.sendall(data)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 965, in sendall
v = self.send(data[count:])
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 935, in send
return self._sslobj.write(data)
File "/Users/swast/.pyenv/versions/3.6.2/lib/python3.6/ssl.py", line 636, in write
return self._sslobj.write(data)
urllib3.exceptions.ProtocolError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe'))
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/Users/swast/src/google-cloud-python/bigquery/tests/system.py", line 397, in test_query_many_columns
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 186, in _items_iter
for page in self._page_iter(increment=False):
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 217, in _page_iter
page = self._next_page()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 336, in _next_page
response = self._get_next_page_response()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 388, in _get_next_page_response
query_params=params)
File "/Users/swast/src/google-cloud-python/bigquery/google/cloud/bigquery/client.py", line 283, in _call_api
return call()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/retry.py", line 260, in retry_wrapped_func
on_error=on_error,
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/retry.py", line 177, in retry_target
return target()
File "/Users/swast/src/google-cloud-python/core/google/cloud/_http.py", line 290, in api_request
headers=headers, target_object=_target_object)
File "/Users/swast/src/google-cloud-python/core/google/cloud/_http.py", line 183, in _make_request
return self._do_request(method, url, headers, data, target_object)
File "/Users/swast/src/google-cloud-python/core/google/cloud/_http.py", line 212, in _do_request
url=url, method=method, headers=headers, data=data)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/google/auth/transport/requests.py", line 186, in request
method, url, data=data, headers=request_headers, **kwargs)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/requests/sessions.py", line 508, in request
resp = self.send(prep, **send_kwargs)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/requests/sessions.py", line 618, in send
r = adapter.send(request, **kwargs)
File "/Users/swast/.pyenv/versions/3.6.2/envs/google-cloud-python/lib/python3.6/site-packages/requests/adapters.py", line 490, in send
raise ConnectionError(err, request=request)
requests.exceptions.ConnectionError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe'))
With 1,000 columns the request goes through but fails with the described error 413.
Error 413 (Request Entity Too Large)!!1
_______________________________________________________ TestBigQuery.test_list_rows_many_columns ________________________________________________________
Traceback (most recent call last):
File "/Users/swast/src/google-cloud-python/bigquery/tests/system.py", line 382, in test_list_rows_many_columns
rows = list(Config.CLIENT.list_rows(table_ref, selected_fields=schema))
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 186, in _items_iter
for page in self._page_iter(increment=False):
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 217, in _page_iter
page = self._next_page()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 336, in _next_page
response = self._get_next_page_response()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/page_iterator.py", line 388, in _get_next_page_response
query_params=params)
File "/Users/swast/src/google-cloud-python/bigquery/google/cloud/bigquery/client.py", line 283, in _call_api
return call()
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/retry.py", line 260, in retry_wrapped_func
on_error=on_error,
File "/Users/swast/src/google-cloud-python/api_core/google/api_core/retry.py", line 177, in retry_target
return target()
File "/Users/swast/src/google-cloud-python/core/google/cloud/_http.py", line 293, in api_request
raise exceptions.from_http_response(response)
google.api_core.exceptions.GoogleAPICallError: 413 GET https://www.googleapis.com/bigquery/v2/projects/swast-scratch/datasets/list_rows_1519325955869/tables/many_columns/data?selectedFields=column_0_with_long_name%2Ccolumn_1_with_long_name%2Ccolumn_2_with_long_name%2Ccolumn_3_with_long_name%2Ccolumn_4_with_long_name%2Ccolumn_5_with_long_name%2Ccolumn_6_with_long_name%2Ccolumn_7_with_long_name%2Ccolumn_8_with_long_name%2Ccolumn_9_with_long_name%2Ccolumn_10_with_long_name%2Ccolumn_11_with_long_name%2Ccolumn_12_with_long_name%2Ccolumn_13_with_long_name%2Ccolumn_14_with_long_name%2Ccolumn_15_with_long_name%2Ccolumn_16_with_long_name%2Ccolumn_17_with_long_name%2Ccolumn_18_with_long_name%2Ccolumn_19_with_long_name%2Ccolumn_20_with_long_name%2Ccolumn_21_with_long_name%2Ccolumn_22_with_long_name%2Ccolumn_23_with_long_name%2Ccolumn_24_with_long_name%2Ccolumn_25_with_long_name%2Ccolumn_26_with_long_name%2Ccolumn_27_with_long_name%2Ccolumn_28_with_long_name%2Ccolumn_29_with_long_name%2Ccolumn_30_with_long_name%2Ccolumn_31_with_long_name%2Ccolumn_32_with_long_name%2Ccolumn_33_with_long_name%2Ccolumn_34_with_long_name%2Ccolumn_35_with_long_name%2Ccolumn_36_with_long_name%2Ccolumn_37_with_long_name%2Ccolumn_38_with_long_name%2Ccolumn_39_with_long_name%2Ccolumn_40_with_long_name%2Ccolumn_41_with_long_name%2Ccolumn_42_with_long_name%2Ccolumn_43_with_long_name%2Ccolumn_44_with_long_name%2Ccolumn_45_with_long_name%2Ccolumn_46_with_long_name%2Ccolumn_47_with_long_name%2Ccolumn_48_with_long_name%2Ccolumn_49_with_long_name%2Ccolumn_50_with_long_name%2Ccolumn_51_with_long_name%2Ccolumn_52_with_long_name%2Ccolumn_53_with_long_name%2Ccolumn_54_with_long_name%2Ccolumn_55_with_long_name%2Ccolumn_56_with_long_name%2Ccolumn_57_with_long_name%2Ccolumn_58_with_long_name%2Ccolumn_59_with_long_name%2Ccolumn_60_with_long_name%2Ccolumn_61_with_long_name%2Ccolumn_62_with_long_name%2Ccolumn_63_with_long_name%2Ccolumn_64_with_long_name%2Ccolumn_65_with_long_name%2Ccolumn_66_with_long_name%2Ccolumn_67_with_long_name%2Ccolumn_68_with_long_name%2Ccolumn_69_with_long_name%2Ccolumn_70_with_long_name%2Ccolumn_71_with_long_name%2Ccolumn_72_with_long_name%2Ccolumn_73_with_long_name%2Ccolumn_74_with_long_name%2Ccolumn_75_with_long_name%2Ccolumn_76_with_long_name%2Ccolumn_77_with_long_name%2Ccolumn_78_with_long_name%2Ccolumn_79_with_long_name%2Ccolumn_80_with_long_name%2Ccolumn_81_with_long_name%2Ccolumn_82_with_long_name%2Ccolumn_83_with_long_name%2Ccolumn_84_with_long_name%2Ccolumn_85_with_long_name%2Ccolumn_86_with_long_name%2Ccolumn_87_with_long_name%2Ccolumn_88_with_long_name%2Ccolumn_89_with_long_name%2Ccolumn_90_with_long_name%2Ccolumn_91_with_long_name%2Ccolumn_92_with_long_name%2Ccolumn_93_with_long_name%2Ccolumn_94_with_long_name%2Ccolumn_95_with_long_name%2Ccolumn_96_with_long_name%2Ccolumn_97_with_long_name%2Ccolumn_98_with_long_name%2Ccolumn_99_with_long_name%2Ccolumn_100_with_long_name%2Ccolumn_101_with_long_name%2Ccolumn_102_with_long_name%2Ccolumn_103_with_long_name%2Ccolumn_104_with_long_name%2Ccolumn_105_with_long_name%2Ccolumn_106_with_long_name%2Ccolumn_107_with_long_name%2Ccolumn_108_with_long_name%2Ccolumn_109_with_long_name%2Ccolumn_110_with_long_name%2Ccolumn_111_with_long_name%2Ccolumn_112_with_long_name%2Ccolumn_113_with_long_name%2Ccolumn_114_with_long_name%2Ccolumn_115_with_long_name%2Ccolumn_116_with_long_name%2Ccolumn_117_with_long_name%2Ccolumn_118_with_long_name%2Ccolumn_119_with_long_name%2Ccolumn_120_with_long_name%2Ccolumn_121_with_long_name%2Ccolumn_122_with_long_name%2Ccolumn_123_with_long_name%2Ccolumn_124_with_long_name%2Ccolumn_125_with_long_name%2Ccolumn_126_with_long_name%2Ccolumn_127_with_long_name%2Ccolumn_128_with_long_name%2Ccolumn_129_with_long_name%2Ccolumn_130_with_long_name%2Ccolumn_131_with_long_name%2Ccolumn_132_with_long_name%2Ccolumn_133_with_long_name%2Ccolumn_134_with_long_name%2Ccolumn_135_with_long_name%2Ccolumn_136_with_long_name%2Ccolumn_137_with_long_name%2Ccolumn_138_with_long_name%2Ccolumn_139_with_long_name%2Ccolumn_140_with_long_name%2Ccolumn_141_with_long_name%2Ccolumn_142_with_long_name%2Ccolumn_143_with_long_name%2Ccolumn_144_with_long_name%2Ccolumn_145_with_long_name%2Ccolumn_146_with_long_name%2Ccolumn_147_with_long_name%2Ccolumn_148_with_long_name%2Ccolumn_149_with_long_name%2Ccolumn_150_with_long_name%2Ccolumn_151_with_long_name%2Ccolumn_152_with_long_name%2Ccolumn_153_with_long_name%2Ccolumn_154_with_long_name%2Ccolumn_155_with_long_name%2Ccolumn_156_with_long_name%2Ccolumn_157_with_long_name%2Ccolumn_158_with_long_name%2Ccolumn_159_with_long_name%2Ccolumn_160_with_long_name%2Ccolumn_161_with_long_name%2Ccolumn_162_with_long_name%2Ccolumn_163_with_long_name%2Ccolumn_164_with_long_name%2Ccolumn_165_with_long_name%2Ccolumn_166_with_long_name%2Ccolumn_167_with_long_name%2Ccolumn_168_with_long_name%2Ccolumn_169_with_long_name%2Ccolumn_170_with_long_name%2Ccolumn_171_with_long_name%2Ccolumn_172_with_long_name%2Ccolumn_173_with_long_name%2Ccolumn_174_with_long_name%2Ccolumn_175_with_long_name%2Ccolumn_176_with_long_name%2Ccolumn_177_with_long_name%2Ccolumn_178_with_long_name%2Ccolumn_179_with_long_name%2Ccolumn_180_with_long_name%2Ccolumn_181_with_long_name%2Ccolumn_182_with_long_name%2Ccolumn_183_with_long_name%2Ccolumn_184_with_long_name%2Ccolumn_185_with_long_name%2Ccolumn_186_with_long_name%2Ccolumn_187_with_long_name%2Ccolumn_188_with_long_name%2Ccolumn_189_with_long_name%2Ccolumn_190_with_long_name%2Ccolumn_191_with_long_name%2Ccolumn_192_with_long_name%2Ccolumn_193_with_long_name%2Ccolumn_194_with_long_name%2Ccolumn_195_with_long_name%2Ccolumn_196_with_long_name%2Ccolumn_197_with_long_name%2Ccolumn_198_with_long_name%2Ccolumn_199_with_long_name%2Ccolumn_200_with_long_name%2Ccolumn_201_with_long_name%2Ccolumn_202_with_long_name%2Ccolumn_203_with_long_name%2Ccolumn_204_with_long_name%2Ccolumn_205_with_long_name%2Ccolumn_206_with_long_name%2Ccolumn_207_with_long_name%2Ccolumn_208_with_long_name%2Ccolumn_209_with_long_name%2Ccolumn_210_with_long_name%2Ccolumn_211_with_long_name%2Ccolumn_212_with_long_name%2Ccolumn_213_with_long_name%2Ccolumn_214_with_long_name%2Ccolumn_215_with_long_name%2Ccolumn_216_with_long_name%2Ccolumn_217_with_long_name%2Ccolumn_218_with_long_name%2Ccolumn_219_with_long_name%2Ccolumn_220_with_long_name%2Ccolumn_221_with_long_name%2Ccolumn_222_with_long_name%2Ccolumn_223_with_long_name%2Ccolumn_224_with_long_name%2Ccolumn_225_with_long_name%2Ccolumn_226_with_long_name%2Ccolumn_227_with_long_name%2Ccolumn_228_with_long_name%2Ccolumn_229_with_long_name%2Ccolumn_230_with_long_name%2Ccolumn_231_with_long_name%2Ccolumn_232_with_long_name%2Ccolumn_233_with_long_name%2Ccolumn_234_with_long_name%2Ccolumn_235_with_long_name%2Ccolumn_236_with_long_name%2Ccolumn_237_with_long_name%2Ccolumn_238_with_long_name%2Ccolumn_239_with_long_name%2Ccolumn_240_with_long_name%2Ccolumn_241_with_long_name%2Ccolumn_242_with_long_name%2Ccolumn_243_with_long_name%2Ccolumn_244_with_long_name%2Ccolumn_245_with_long_name%2Ccolumn_246_with_long_name%2Ccolumn_247_with_long_name%2Ccolumn_248_with_long_name%2Ccolumn_249_with_long_name%2Ccolumn_250_with_long_name%2Ccolumn_251_with_long_name%2Ccolumn_252_with_long_name%2Ccolumn_253_with_long_name%2Ccolumn_254_with_long_name%2Ccolumn_255_with_long_name%2Ccolumn_256_with_long_name%2Ccolumn_257_with_long_name%2Ccolumn_258_with_long_name%2Ccolumn_259_with_long_name%2Ccolumn_260_with_long_name%2Ccolumn_261_with_long_name%2Ccolumn_262_with_long_name%2Ccolumn_263_with_long_name%2Ccolumn_264_with_long_name%2Ccolumn_265_with_long_name%2Ccolumn_266_with_long_name%2Ccolumn_267_with_long_name%2Ccolumn_268_with_long_name%2Ccolumn_269_with_long_name%2Ccolumn_270_with_long_name%2Ccolumn_271_with_long_name%2Ccolumn_272_with_long_name%2Ccolumn_273_with_long_name%2Ccolumn_274_with_long_name%2Ccolumn_275_with_long_name%2Ccolumn_276_with_long_name%2Ccolumn_277_with_long_name%2Ccolumn_278_with_long_name%2Ccolumn_279_with_long_name%2Ccolumn_280_with_long_name%2Ccolumn_281_with_long_name%2Ccolumn_282_with_long_name%2Ccolumn_283_with_long_name%2Ccolumn_284_with_long_name%2Ccolumn_285_with_long_name%2Ccolumn_286_with_long_name%2Ccolumn_287_with_long_name%2Ccolumn_288_with_long_name%2Ccolumn_289_with_long_name%2Ccolumn_290_with_long_name%2Ccolumn_291_with_long_name%2Ccolumn_292_with_long_name%2Ccolumn_293_with_long_name%2Ccolumn_294_with_long_name%2Ccolumn_295_with_long_name%2Ccolumn_296_with_long_name%2Ccolumn_297_with_long_name%2Ccolumn_298_with_long_name%2Ccolumn_299_with_long_name%2Ccolumn_300_with_long_name%2Ccolumn_301_with_long_name%2Ccolumn_302_with_long_name%2Ccolumn_303_with_long_name%2Ccolumn_304_with_long_name%2Ccolumn_305_with_long_name%2Ccolumn_306_with_long_name%2Ccolumn_307_with_long_name%2Ccolumn_308_with_long_name%2Ccolumn_309_with_long_name%2Ccolumn_310_with_long_name%2Ccolumn_311_with_long_name%2Ccolumn_312_with_long_name%2Ccolumn_313_with_long_name%2Ccolumn_314_with_long_name%2Ccolumn_315_with_long_name%2Ccolumn_316_with_long_name%2Ccolumn_317_with_long_name%2Ccolumn_318_with_long_name%2Ccolumn_319_with_long_name%2Ccolumn_320_with_long_name%2Ccolumn_321_with_long_name%2Ccolumn_322_with_long_name%2Ccolumn_323_with_long_name%2Ccolumn_324_with_long_name%2Ccolumn_325_with_long_name%2Ccolumn_326_with_long_name%2Ccolumn_327_with_long_name%2Ccolumn_328_with_long_name%2Ccolumn_329_with_long_name%2Ccolumn_330_with_long_name%2Ccolumn_331_with_long_name%2Ccolumn_332_with_long_name%2Ccolumn_333_with_long_name%2Ccolumn_334_with_long_name%2Ccolumn_335_with_long_name%2Ccolumn_336_with_long_name%2Ccolumn_337_with_long_name%2Ccolumn_338_with_long_name%2Ccolumn_339_with_long_name%2Ccolumn_340_with_long_name%2Ccolumn_341_with_long_name%2Ccolumn_342_with_long_name%2Ccolumn_343_with_long_name%2Ccolumn_344_with_long_name%2Ccolumn_345_with_long_name%2Ccolumn_346_with_long_name%2Ccolumn_347_with_long_name%2Ccolumn_348_with_long_name%2Ccolumn_349_with_long_name%2Ccolumn_350_with_long_name%2Ccolumn_351_with_long_name%2Ccolumn_352_with_long_name%2Ccolumn_353_with_long_name%2Ccolumn_354_with_long_name%2Ccolumn_355_with_long_name%2Ccolumn_356_with_long_name%2Ccolumn_357_with_long_name%2Ccolumn_358_with_long_name%2Ccolumn_359_with_long_name%2Ccolumn_360_with_long_name%2Ccolumn_361_with_long_name%2Ccolumn_362_with_long_name%2Ccolumn_363_with_long_name%2Ccolumn_364_with_long_name%2Ccolumn_365_with_long_name%2Ccolumn_366_with_long_name%2Ccolumn_367_with_long_name%2Ccolumn_368_with_long_name%2Ccolumn_369_with_long_name%2Ccolumn_370_with_long_name%2Ccolumn_371_with_long_name%2Ccolumn_372_with_long_name%2Ccolumn_373_with_long_name%2Ccolumn_374_with_long_name%2Ccolumn_375_with_long_name%2Ccolumn_376_with_long_name%2Ccolumn_377_with_long_name%2Ccolumn_378_with_long_name%2Ccolumn_379_with_long_name%2Ccolumn_380_with_long_name%2Ccolumn_381_with_long_name%2Ccolumn_382_with_long_name%2Ccolumn_383_with_long_name%2Ccolumn_384_with_long_name%2Ccolumn_385_with_long_name%2Ccolumn_386_with_long_name%2Ccolumn_387_with_long_name%2Ccolumn_388_with_long_name%2Ccolumn_389_with_long_name%2Ccolumn_390_with_long_name%2Ccolumn_391_with_long_name%2Ccolumn_392_with_long_name%2Ccolumn_393_with_long_name%2Ccolumn_394_with_long_name%2Ccolumn_395_with_long_name%2Ccolumn_396_with_long_name%2Ccolumn_397_with_long_name%2Ccolumn_398_with_long_name%2Ccolumn_399_with_long_name%2Ccolumn_400_with_long_name%2Ccolumn_401_with_long_name%2Ccolumn_402_with_long_name%2Ccolumn_403_with_long_name%2Ccolumn_404_with_long_name%2Ccolumn_405_with_long_name%2Ccolumn_406_with_long_name%2Ccolumn_407_with_long_name%2Ccolumn_408_with_long_name%2Ccolumn_409_with_long_name%2Ccolumn_410_with_long_name%2Ccolumn_411_with_long_name%2Ccolumn_412_with_long_name%2Ccolumn_413_with_long_name%2Ccolumn_414_with_long_name%2Ccolumn_415_with_long_name%2Ccolumn_416_with_long_name%2Ccolumn_417_with_long_name%2Ccolumn_418_with_long_name%2Ccolumn_419_with_long_name%2Ccolumn_420_with_long_name%2Ccolumn_421_with_long_name%2Ccolumn_422_with_long_name%2Ccolumn_423_with_long_name%2Ccolumn_424_with_long_name%2Ccolumn_425_with_long_name%2Ccolumn_426_with_long_name%2Ccolumn_427_with_long_name%2Ccolumn_428_with_long_name%2Ccolumn_429_with_long_name%2Ccolumn_430_with_long_name%2Ccolumn_431_with_long_name%2Ccolumn_432_with_long_name%2Ccolumn_433_with_long_name%2Ccolumn_434_with_long_name%2Ccolumn_435_with_long_name%2Ccolumn_436_with_long_name%2Ccolumn_437_with_long_name%2Ccolumn_438_with_long_name%2Ccolumn_439_with_long_name%2Ccolumn_440_with_long_name%2Ccolumn_441_with_long_name%2Ccolumn_442_with_long_name%2Ccolumn_443_with_long_name%2Ccolumn_444_with_long_name%2Ccolumn_445_with_long_name%2Ccolumn_446_with_long_name%2Ccolumn_447_with_long_name%2Ccolumn_448_with_long_name%2Ccolumn_449_with_long_name%2Ccolumn_450_with_long_name%2Ccolumn_451_with_long_name%2Ccolumn_452_with_long_name%2Ccolumn_453_with_long_name%2Ccolumn_454_with_long_name%2Ccolumn_455_with_long_name%2Ccolumn_456_with_long_name%2Ccolumn_457_with_long_name%2Ccolumn_458_with_long_name%2Ccolumn_459_with_long_name%2Ccolumn_460_with_long_name%2Ccolumn_461_with_long_name%2Ccolumn_462_with_long_name%2Ccolumn_463_with_long_name%2Ccolumn_464_with_long_name%2Ccolumn_465_with_long_name%2Ccolumn_466_with_long_name%2Ccolumn_467_with_long_name%2Ccolumn_468_with_long_name%2Ccolumn_469_with_long_name%2Ccolumn_470_with_long_name%2Ccolumn_471_with_long_name%2Ccolumn_472_with_long_name%2Ccolumn_473_with_long_name%2Ccolumn_474_with_long_name%2Ccolumn_475_with_long_name%2Ccolumn_476_with_long_name%2Ccolumn_477_with_long_name%2Ccolumn_478_with_long_name%2Ccolumn_479_with_long_name%2Ccolumn_480_with_long_name%2Ccolumn_481_with_long_name%2Ccolumn_482_with_long_name%2Ccolumn_483_with_long_name%2Ccolumn_484_with_long_name%2Ccolumn_485_with_long_name%2Ccolumn_486_with_long_name%2Ccolumn_487_with_long_name%2Ccolumn_488_with_long_name%2Ccolumn_489_with_long_name%2Ccolumn_490_with_long_name%2Ccolumn_491_with_long_name%2Ccolumn_492_with_long_name%2Ccolumn_493_with_long_name%2Ccolumn_494_with_long_name%2Ccolumn_495_with_long_name%2Ccolumn_496_with_long_name%2Ccolumn_497_with_long_name%2Ccolumn_498_with_long_name%2Ccolumn_499_with_long_name%2Ccolumn_500_with_long_name%2Ccolumn_501_with_long_name%2Ccolumn_502_with_long_name%2Ccolumn_503_with_long_name%2Ccolumn_504_with_long_name%2Ccolumn_505_with_long_name%2Ccolumn_506_with_long_name%2Ccolumn_507_with_long_name%2Ccolumn_508_with_long_name%2Ccolumn_509_with_long_name%2Ccolumn_510_with_long_name%2Ccolumn_511_with_long_name%2Ccolumn_512_with_long_name%2Ccolumn_513_with_long_name%2Ccolumn_514_with_long_name%2Ccolumn_515_with_long_name%2Ccolumn_516_with_long_name%2Ccolumn_517_with_long_name%2Ccolumn_518_with_long_name%2Ccolumn_519_with_long_name%2Ccolumn_520_with_long_name%2Ccolumn_521_with_long_name%2Ccolumn_522_with_long_name%2Ccolumn_523_with_long_name%2Ccolumn_524_with_long_name%2Ccolumn_525_with_long_name%2Ccolumn_526_with_long_name%2Ccolumn_527_with_long_name%2Ccolumn_528_with_long_name%2Ccolumn_529_with_long_name%2Ccolumn_530_with_long_name%2Ccolumn_531_with_long_name%2Ccolumn_532_with_long_name%2Ccolumn_533_with_long_name%2Ccolumn_534_with_long_name%2Ccolumn_535_with_long_name%2Ccolumn_536_with_long_name%2Ccolumn_537_with_long_name%2Ccolumn_538_with_long_name%2Ccolumn_539_with_long_name%2Ccolumn_540_with_long_name%2Ccolumn_541_with_long_name%2Ccolumn_542_with_long_name%2Ccolumn_543_with_long_name%2Ccolumn_544_with_long_name%2Ccolumn_545_with_long_name%2Ccolumn_546_with_long_name%2Ccolumn_547_with_long_name%2Ccolumn_548_with_long_name%2Ccolumn_549_with_long_name%2Ccolumn_550_with_long_name%2Ccolumn_551_with_long_name%2Ccolumn_552_with_long_name%2Ccolumn_553_with_long_name%2Ccolumn_554_with_long_name%2Ccolumn_555_with_long_name%2Ccolumn_556_with_long_name%2Ccolumn_557_with_long_name%2Ccolumn_558_with_long_name%2Ccolumn_559_with_long_name%2Ccolumn_560_with_long_name%2Ccolumn_561_with_long_name%2Ccolumn_562_with_long_name%2Ccolumn_563_with_long_name%2Ccolumn_564_with_long_name%2Ccolumn_565_with_long_name%2Ccolumn_566_with_long_name%2Ccolumn_567_with_long_name%2Ccolumn_568_with_long_name%2Ccolumn_569_with_long_name%2Ccolumn_570_with_long_name%2Ccolumn_571_with_long_name%2Ccolumn_572_with_long_name%2Ccolumn_573_with_long_name%2Ccolumn_574_with_long_name%2Ccolumn_575_with_long_name%2Ccolumn_576_with_long_name%2Ccolumn_577_with_long_name%2Ccolumn_578_with_long_name%2Ccolumn_579_with_long_name%2Ccolumn_580_with_long_name%2Ccolumn_581_with_long_name%2Ccolumn_582_with_long_name%2Ccolumn_583_with_long_name%2Ccolumn_584_with_long_name%2Ccolumn_585_with_long_name%2Ccolumn_586_with_long_name%2Ccolumn_587_with_long_name%2Ccolumn_588_with_long_name%2Ccolumn_589_with_long_name%2Ccolumn_590_with_long_name%2Ccolumn_591_with_long_name%2Ccolumn_592_with_long_name%2Ccolumn_593_with_long_name%2Ccolumn_594_with_long_name%2Ccolumn_595_with_long_name%2Ccolumn_596_with_long_name%2Ccolumn_597_with_long_name%2Ccolumn_598_with_long_name%2Ccolumn_599_with_long_name%2Ccolumn_600_with_long_name%2Ccolumn_601_with_long_name%2Ccolumn_602_with_long_name%2Ccolumn_603_with_long_name%2Ccolumn_604_with_long_name%2Ccolumn_605_with_long_name%2Ccolumn_606_with_long_name%2Ccolumn_607_with_long_name%2Ccolumn_608_with_long_name%2Ccolumn_609_with_long_name%2Ccolumn_610_with_long_name%2Ccolumn_611_with_long_name%2Ccolumn_612_with_long_name%2Ccolumn_613_with_long_name%2Ccolumn_614_with_long_name%2Ccolumn_615_with_long_name%2Ccolumn_616_with_long_name%2Ccolumn_617_with_long_name%2Ccolumn_618_with_long_name%2Ccolumn_619_with_long_name%2Ccolumn_620_with_long_name%2Ccolumn_621_with_long_name%2Ccolumn_622_with_long_name%2Ccolumn_623_with_long_name%2Ccolumn_624_with_long_name%2Ccolumn_625_with_long_name%2Ccolumn_626_with_long_name%2Ccolumn_627_with_long_name%2Ccolumn_628_with_long_name%2Ccolumn_629_with_long_name%2Ccolumn_630_with_long_name%2Ccolumn_631_with_long_name%2Ccolumn_632_with_long_name%2Ccolumn_633_with_long_name%2Ccolumn_634_with_long_name%2Ccolumn_635_with_long_name%2Ccolumn_636_with_long_name%2Ccolumn_637_with_long_name%2Ccolumn_638_with_long_name%2Ccolumn_639_with_long_name%2Ccolumn_640_with_long_name%2Ccolumn_641_with_long_name%2Ccolumn_642_with_long_name%2Ccolumn_643_with_long_name%2Ccolumn_644_with_long_name%2Ccolumn_645_with_long_name%2Ccolumn_646_with_long_name%2Ccolumn_647_with_long_name%2Ccolumn_648_with_long_name%2Ccolumn_649_with_long_name%2Ccolumn_650_with_long_name%2Ccolumn_651_with_long_name%2Ccolumn_652_with_long_name%2Ccolumn_653_with_long_name%2Ccolumn_654_with_long_name%2Ccolumn_655_with_long_name%2Ccolumn_656_with_long_name%2Ccolumn_657_with_long_name%2Ccolumn_658_with_long_name%2Ccolumn_659_with_long_name%2Ccolumn_660_with_long_name%2Ccolumn_661_with_long_name%2Ccolumn_662_with_long_name%2Ccolumn_663_with_long_name%2Ccolumn_664_with_long_name%2Ccolumn_665_with_long_name%2Ccolumn_666_with_long_name%2Ccolumn_667_with_long_name%2Ccolumn_668_with_long_name%2Ccolumn_669_with_long_name%2Ccolumn_670_with_long_name%2Ccolumn_671_with_long_name%2Ccolumn_672_with_long_name%2Ccolumn_673_with_long_name%2Ccolumn_674_with_long_name%2Ccolumn_675_with_long_name%2Ccolumn_676_with_long_name%2Ccolumn_677_with_long_name%2Ccolumn_678_with_long_name%2Ccolumn_679_with_long_name%2Ccolumn_680_with_long_name%2Ccolumn_681_with_long_name%2Ccolumn_682_with_long_name%2Ccolumn_683_with_long_name%2Ccolumn_684_with_long_name%2Ccolumn_685_with_long_name%2Ccolumn_686_with_long_name%2Ccolumn_687_with_long_name%2Ccolumn_688_with_long_name%2Ccolumn_689_with_long_name%2Ccolumn_690_with_long_name%2Ccolumn_691_with_long_name%2Ccolumn_692_with_long_name%2Ccolumn_693_with_long_name%2Ccolumn_694_with_long_name%2Ccolumn_695_with_long_name%2Ccolumn_696_with_long_name%2Ccolumn_697_with_long_name%2Ccolumn_698_with_long_name%2Ccolumn_699_with_long_name%2Ccolumn_700_with_long_name%2Ccolumn_701_with_long_name%2Ccolumn_702_with_long_name%2Ccolumn_703_with_long_name%2Ccolumn_704_with_long_name%2Ccolumn_705_with_long_name%2Ccolumn_706_with_long_name%2Ccolumn_707_with_long_name%2Ccolumn_708_with_long_name%2Ccolumn_709_with_long_name%2Ccolumn_710_with_long_name%2Ccolumn_711_with_long_name%2Ccolumn_712_with_long_name%2Ccolumn_713_with_long_name%2Ccolumn_714_with_long_name%2Ccolumn_715_with_long_name%2Ccolumn_716_with_long_name%2Ccolumn_717_with_long_name%2Ccolumn_718_with_long_name%2Ccolumn_719_with_long_name%2Ccolumn_720_with_long_name%2Ccolumn_721_with_long_name%2Ccolumn_722_with_long_name%2Ccolumn_723_with_long_name%2Ccolumn_724_with_long_name%2Ccolumn_725_with_long_name%2Ccolumn_726_with_long_name%2Ccolumn_727_with_long_name%2Ccolumn_728_with_long_name%2Ccolumn_729_with_long_name%2Ccolumn_730_with_long_name%2Ccolumn_731_with_long_name%2Ccolumn_732_with_long_name%2Ccolumn_733_with_long_name%2Ccolumn_734_with_long_name%2Ccolumn_735_with_long_name%2Ccolumn_736_with_long_name%2Ccolumn_737_with_long_name%2Ccolumn_738_with_long_name%2Ccolumn_739_with_long_name%2Ccolumn_740_with_long_name%2Ccolumn_741_with_long_name%2Ccolumn_742_with_long_name%2Ccolumn_743_with_long_name%2Ccolumn_744_with_long_name%2Ccolumn_745_with_long_name%2Ccolumn_746_with_long_name%2Ccolumn_747_with_long_name%2Ccolumn_748_with_long_name%2Ccolumn_749_with_long_name%2Ccolumn_750_with_long_name%2Ccolumn_751_with_long_name%2Ccolumn_752_with_long_name%2Ccolumn_753_with_long_name%2Ccolumn_754_with_long_name%2Ccolumn_755_with_long_name%2Ccolumn_756_with_long_name%2Ccolumn_757_with_long_name%2Ccolumn_758_with_long_name%2Ccolumn_759_with_long_name%2Ccolumn_760_with_long_name%2Ccolumn_761_with_long_name%2Ccolumn_762_with_long_name%2Ccolumn_763_with_long_name%2Ccolumn_764_with_long_name%2Ccolumn_765_with_long_name%2Ccolumn_766_with_long_name%2Ccolumn_767_with_long_name%2Ccolumn_768_with_long_name%2Ccolumn_769_with_long_name%2Ccolumn_770_with_long_name%2Ccolumn_771_with_long_name%2Ccolumn_772_with_long_name%2Ccolumn_773_with_long_name%2Ccolumn_774_with_long_name%2Ccolumn_775_with_long_name%2Ccolumn_776_with_long_name%2Ccolumn_777_with_long_name%2Ccolumn_778_with_long_name%2Ccolumn_779_with_long_name%2Ccolumn_780_with_long_name%2Ccolumn_781_with_long_name%2Ccolumn_782_with_long_name%2Ccolumn_783_with_long_name%2Ccolumn_784_with_long_name%2Ccolumn_785_with_long_name%2Ccolumn_786_with_long_name%2Ccolumn_787_with_long_name%2Ccolumn_788_with_long_name%2Ccolumn_789_with_long_name%2Ccolumn_790_with_long_name%2Ccolumn_791_with_long_name%2Ccolumn_792_with_long_name%2Ccolumn_793_with_long_name%2Ccolumn_794_with_long_name%2Ccolumn_795_with_long_name%2Ccolumn_796_with_long_name%2Ccolumn_797_with_long_name%2Ccolumn_798_with_long_name%2Ccolumn_799_with_long_name%2Ccolumn_800_with_long_name%2Ccolumn_801_with_long_name%2Ccolumn_802_with_long_name%2Ccolumn_803_with_long_name%2Ccolumn_804_with_long_name%2Ccolumn_805_with_long_name%2Ccolumn_806_with_long_name%2Ccolumn_807_with_long_name%2Ccolumn_808_with_long_name%2Ccolumn_809_with_long_name%2Ccolumn_810_with_long_name%2Ccolumn_811_with_long_name%2Ccolumn_812_with_long_name%2Ccolumn_813_with_long_name%2Ccolumn_814_with_long_name%2Ccolumn_815_with_long_name%2Ccolumn_816_with_long_name%2Ccolumn_817_with_long_name%2Ccolumn_818_with_long_name%2Ccolumn_819_with_long_name%2Ccolumn_820_with_long_name%2Ccolumn_821_with_long_name%2Ccolumn_822_with_long_name%2Ccolumn_823_with_long_name%2Ccolumn_824_with_long_name%2Ccolumn_825_with_long_name%2Ccolumn_826_with_long_name%2Ccolumn_827_with_long_name%2Ccolumn_828_with_long_name%2Ccolumn_829_with_long_name%2Ccolumn_830_with_long_name%2Ccolumn_831_with_long_name%2Ccolumn_832_with_long_name%2Ccolumn_833_with_long_name%2Ccolumn_834_with_long_name%2Ccolumn_835_with_long_name%2Ccolumn_836_with_long_name%2Ccolumn_837_with_long_name%2Ccolumn_838_with_long_name%2Ccolumn_839_with_long_name%2Ccolumn_840_with_long_name%2Ccolumn_841_with_long_name%2Ccolumn_842_with_long_name%2Ccolumn_843_with_long_name%2Ccolumn_844_with_long_name%2Ccolumn_845_with_long_name%2Ccolumn_846_with_long_name%2Ccolumn_847_with_long_name%2Ccolumn_848_with_long_name%2Ccolumn_849_with_long_name%2Ccolumn_850_with_long_name%2Ccolumn_851_with_long_name%2Ccolumn_852_with_long_name%2Ccolumn_853_with_long_name%2Ccolumn_854_with_long_name%2Ccolumn_855_with_long_name%2Ccolumn_856_with_long_name%2Ccolumn_857_with_long_name%2Ccolumn_858_with_long_name%2Ccolumn_859_with_long_name%2Ccolumn_860_with_long_name%2Ccolumn_861_with_long_name%2Ccolumn_862_with_long_name%2Ccolumn_863_with_long_name%2Ccolumn_864_with_long_name%2Ccolumn_865_with_long_name%2Ccolumn_866_with_long_name%2Ccolumn_867_with_long_name%2Ccolumn_868_with_long_name%2Ccolumn_869_with_long_name%2Ccolumn_870_with_long_name%2Ccolumn_871_with_long_name%2Ccolumn_872_with_long_name%2Ccolumn_873_with_long_name%2Ccolumn_874_with_long_name%2Ccolumn_875_with_long_name%2Ccolumn_876_with_long_name%2Ccolumn_877_with_long_name%2Ccolumn_878_with_long_name%2Ccolumn_879_with_long_name%2Ccolumn_880_with_long_name%2Ccolumn_881_with_long_name%2Ccolumn_882_with_long_name%2Ccolumn_883_with_long_name%2Ccolumn_884_with_long_name%2Ccolumn_885_with_long_name%2Ccolumn_886_with_long_name%2Ccolumn_887_with_long_name%2Ccolumn_888_with_long_name%2Ccolumn_889_with_long_name%2Ccolumn_890_with_long_name%2Ccolumn_891_with_long_name%2Ccolumn_892_with_long_name%2Ccolumn_893_with_long_name%2Ccolumn_894_with_long_name%2Ccolumn_895_with_long_name%2Ccolumn_896_with_long_name%2Ccolumn_897_with_long_name%2Ccolumn_898_with_long_name%2Ccolumn_899_with_long_name%2Ccolumn_900_with_long_name%2Ccolumn_901_with_long_name%2Ccolumn_902_with_long_name%2Ccolumn_903_with_long_name%2Ccolumn_904_with_long_name%2Ccolumn_905_with_long_name%2Ccolumn_906_with_long_name%2Ccolumn_907_with_long_name%2Ccolumn_908_with_long_name%2Ccolumn_909_with_long_name%2Ccolumn_910_with_long_name%2Ccolumn_911_with_long_name%2Ccolumn_912_with_long_name%2Ccolumn_913_with_long_name%2Ccolumn_914_with_long_name%2Ccolumn_915_with_long_name%2Ccolumn_916_with_long_name%2Ccolumn_917_with_long_name%2Ccolumn_918_with_long_name%2Ccolumn_919_with_long_name%2Ccolumn_920_with_long_name%2Ccolumn_921_with_long_name%2Ccolumn_922_with_long_name%2Ccolumn_923_with_long_name%2Ccolumn_924_with_long_name%2Ccolumn_925_with_long_name%2Ccolumn_926_with_long_name%2Ccolumn_927_with_long_name%2Ccolumn_928_with_long_name%2Ccolumn_929_with_long_name%2Ccolumn_930_with_long_name%2Ccolumn_931_with_long_name%2Ccolumn_932_with_long_name%2Ccolumn_933_with_long_name%2Ccolumn_934_with_long_name%2Ccolumn_935_with_long_name%2Ccolumn_936_with_long_name%2Ccolumn_937_with_long_name%2Ccolumn_938_with_long_name%2Ccolumn_939_with_long_name%2Ccolumn_940_with_long_name%2Ccolumn_941_with_long_name%2Ccolumn_942_with_long_name%2Ccolumn_943_with_long_name%2Ccolumn_944_with_long_name%2Ccolumn_945_with_long_name%2Ccolumn_946_with_long_name%2Ccolumn_947_with_long_name%2Ccolumn_948_with_long_name%2Ccolumn_949_with_long_name%2Ccolumn_950_with_long_name%2Ccolumn_951_with_long_name%2Ccolumn_952_with_long_name%2Ccolumn_953_with_long_name%2Ccolumn_954_with_long_name%2Ccolumn_955_with_long_name%2Ccolumn_956_with_long_name%2Ccolumn_957_with_long_name%2Ccolumn_958_with_long_name%2Ccolumn_959_with_long_name%2Ccolumn_960_with_long_name%2Ccolumn_961_with_long_name%2Ccolumn_962_with_long_name%2Ccolumn_963_with_long_name%2Ccolumn_964_with_long_name%2Ccolumn_965_with_long_name%2Ccolumn_966_with_long_name%2Ccolumn_967_with_long_name%2Ccolumn_968_with_long_name%2Ccolumn_969_with_long_name%2Ccolumn_970_with_long_name%2Ccolumn_971_with_long_name%2Ccolumn_972_with_long_name%2Ccolumn_973_with_long_name%2Ccolumn_974_with_long_name%2Ccolumn_975_with_long_name%2Ccolumn_976_with_long_name%2Ccolumn_977_with_long_name%2Ccolumn_978_with_long_name%2Ccolumn_979_with_long_name%2Ccolumn_980_with_long_name%2Ccolumn_981_with_long_name%2Ccolumn_982_with_long_name%2Ccolumn_983_with_long_name%2Ccolumn_984_with_long_name%2Ccolumn_985_with_long_name%2Ccolumn_986_with_long_name%2Ccolumn_987_with_long_name%2Ccolumn_988_with_long_name%2Ccolumn_989_with_long_name%2Ccolumn_990_with_long_name%2Ccolumn_991_with_long_name%2Ccolumn_992_with_long_name%2Ccolumn_993_with_long_name%2Ccolumn_994_with_long_name%2Ccolumn_995_with_long_name%2Ccolumn_996_with_long_name%2Ccolumn_997_with_long_name%2Ccolumn_998_with_long_name%2Ccolumn_999_with_long_name:
Error 413 (Request Entity Too Large)!!1
413. That鈥檚 an error.
Your client issued a request that was too large.
That鈥檚 all we know.
I don't see a request size limit documented at https://cloud.google.com/bigquery/quotas#api_requests so I will file an issue internally about this.
The workaround we could possibly do in the client libraries is to not populate the selected_fields and pretend we have a complete Table object when fetching query results, but I worry this will not do the right thing if the query is written to an existing empty/non-empty destination table where the columns are not in the same order as the query.
tswast
on 22 Feb 2018
👍1
Glad that you could reproduce it too. Such queries used to work fine in version 0.27.
VikramTiwari
on 22 Feb 2018
Yeah, 0.27 and before got query results using https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/getQueryResults, which is slower but also easier to get right in the client.
I'll give the backend folks a little time to see if they can increase the request limit size. If not, we do have a workaround, which is to get the destination table object and then list rows (or we can pretend we have a full Table object by populating the reference and the schema).
tswast
on 22 Feb 2018
👍1
Is there any update from the backend team? I have opened an issue on public issuetracker. https://issuetracker.google.com/issues/73991955
VikramTiwari
on 28 Feb 2018
Yeah, it sounds like there is some infrastructure that limits the length which is slightly out of the BigQuery team's control. The client will have to workaround this by asking for all columns (empty selectedFields) when listing query results.
tswast
on 28 Feb 2018
Hey @tswast Is there any way to get around this in the current version? I tried following your advice but getting errors.
Code:
job = client.query(query)
job.result() # waits for the result to be ready
schema = job._query_results.schema
table = job.destination
result = client.list_rows(table, selected_fields=[], retry=True)
result.to_dataframe()
Error: 'bool' object is not callable
VikramTiwari
on 3 Mar 2018
job.destination is only a TableReference, so list_rows will still try to use selected_fields as the schema.
I believe the following should work:
job = client.query(query)
job.result() # waits for the result to be ready
schema = job._query_results.schema
table_ref = job.destination
table = bigquery.Table(table_ref, schema=schema)
result = client.list_rows(table)
result.to_dataframe()
Edit Removed the retry parameter as it is not a boolean and the default value should work fine. Per https://github.com/GoogleCloudPlatform/google-cloud-python/issues/4864#issuecomment-374363246
tswast
on 5 Mar 2018
@tswast Seems like result is an HTTPIterator
VikramTiwari
on 7 Mar 2018
In 0.29.0? It looks like a RowIterator to me. https://github.com/GoogleCloudPlatform/google-cloud-python/blob/bigquery-0.29.0/bigquery/google/cloud/bigquery/client.py#L1256-L1264
tswast
on 7 Mar 2018
@tswast Is this issue blocked on anything in the backend?
tseaver
on 16 Mar 2018
Nope, at least not anything that is feasible to get fixed in the backend. This is something we'll have to do ourselves. Basically in the function that gets the query results call list_rows in the way I do https://github.com/GoogleCloudPlatform/google-cloud-python/issues/4864#issuecomment-370553770 instead of using the selected_fields option. (Potentially only when there are a large number of columns)
tswast
on 16 Mar 2018
The code snippet provided by @tswast works with 0.31.0 with the slight modification that the retry parameter of list_rows should not be a bool, but rather a google.api_core.retry.Retry object. For example:
#...
result = client.list_rows(table, retry=google.api_core.retry.Retry())
#...
bmabey
on 19 Mar 2018
Thanks @bmabey, I've updated the snippet.
If anyone wants to tackle making the actual fix. All that needs to be done is to take the workaround and apply it in the result() function for QueryJob. Currently:
tswast
on 19 Mar 2018
Haven't tested but let me know if that works. @tswast @bmabey
VikramTiwari
on 20 Mar 2018
👍1
Fixed by https://github.com/GoogleCloudPlatform/google-cloud-python/pull/5072. Should appear in the next release.
tswast
on 22 Mar 2018
@tswast
Is there any work around for requests.exceptions.ConnectionError: ('Connection aborted.', BrokenPipeError(32, 'Broken pipe')) when the request or query is large?
In our queries, one of the condition in where clause checks a list of values. This list is huge, like 0.8 million elements.
I serialized the QueryJobConfig object and it came around 18MB. For this size, the BrokenPipeError was raised. I reduced the list to 0.4 million making the QueryJobConfig object size 12 MB and this time the query ran fine.
shivafractal
on 4 Feb 2019
@shivafractal Rather than passing an 800,000 item list as a query parameter, I would consider uploading those values into a separate (maybe temporary?) table and then building the query by joining with it.
tseaver
on 4 Feb 2019
👍1
+1 to Tres's workaround. This is a situation where the external data sources feature of BigQuery (such as a CSV file in GCS) can come in very handy.
tswast
on 4 Feb 2019
@tseaver
That list is a single column CSV file stored in GCS. The values in the list/file are a subset of a specific table's column. Using a custom ORM we dynamically generate a query containing the above list in one of its where conditions. This query is then executed on a different table.
Many such dynamic queries are generated and executed in parallel by Celery workers. These queries are part of our application's core workflow and are not one time activity.
I don't think creating separate tables and using joins will work for our use case.
Generalising my question, how can we send large no. of parameters to BigQuery without raising BrokenPipeError(32, 'Broken pipe')?
shivafractal
on 5 Feb 2019
@shivafractal Unfortunately there's not much we can do client-side to avoid this error. It is caused by one of the pieces of the API serving infrastructure hitting it's maximum request size limit. It may be possible for this limit to be increased.
If you have a support plan (https://cloud.google.com/support/), I recommend filing a support ticket for the fastest response.
Otherwise, you may file an issue against the BigQuery API at https://issuetracker.google.com/issues/new?component=187149&template=1162659
tswast
on 5 Feb 2019
Was this page helpful?
0 / 5 - 0 ratings
Related issues
blaflamme
路
3Comments
tkaymak
路
4Comments
stevenschlansker
路
3Comments
bmenasha
路
3Comments
ramuta
路
3Comments