I have built a pipeline while migrate more than 100 mysql tables to big query by uploading mysql csv data to biq query using the similar command below. All tables csv files are getting uploaded on gcs bucket and migrated to biq query without any issues except the file below which is just 334 MB . I am able to upload other files even more than 3GB files on gcs bucket created through same code pipeline flow but gcs upload is not working with this file using same gsutil command.
File Name and Size:
-rw-rw-r-- 1 ubuntu ubuntu 334M Jan 30 11:06 trailStatementsV2.csv
gsutil command to upload(have added -d flat to debug the command error):
gsutil -m -d cp mysql-data/trailStatementsV2.csv gs://analyticsdump/mysql_data
***************************** WARNING *****************************
*** You are running gsutil with debug output enabled.
*** Be aware that debug output includes authentication credentials.
*** Make sure to remove the value of the Authorization header for
*** each HTTP request printed to the console prior to posting to
*** a public medium such as a forum post or Stack Overflow.
***************************** WARNING *****************************
gsutil version: 4.53
checksum: 8f91848c753a09585745117ecf306e9f (OK)
boto version: 2.49.0
python version: 3.7.5 (default, Nov 7 2019, 10:50:52) [GCC 8.3.0]
OS: Linux 5.3.0-1033-aws
multiprocessing available: True
using cloud sdk: True
pass cloud sdk credentials to gsutil: True
config path(s): /home/ubuntu/.boto, /home/ubuntu/.config/gcloud/legacy_credentials/asssbhasjsjjsklslsy@tsrelsssasel.in/.boto
gsutil path: /usr/lib/google-cloud-sdk/bin/gsutil
compiled crcmod: False
installed via package manager: False
editable install: False
Command being run: /usr/lib/google-cloud-sdk/platform/gsutil/gsutil -o GSUtil:default_project_id=tressllatassssale -m -d cp mysql-data/trailStatementsV2.csv gs://analyticsdump/mysql_data
config_file_list: ['/home/ubuntu/.boto', '/home/ubuntu/.config/gcloud/legacy_credentials/abssshay@tresssall.in/.boto']
config: [('working_dir', '/mnt/pyami'), ('debug', '0'), ('https_validate_certificates', 'True'), ('working_dir', '/mnt/pyami'), ('debug', '0'), ('content_language', 'en'), ('default_api_version', '2'), ('default_project_id', 'trelssssassslatale')]
DEBUG 0130 15:14:58.882112 multiprocess_file_storage.py] Read credential file
INFO 0130 15:14:58.883168 base_api.py] Calling method storage.objects.list with StorageObjectsListRequest: <StorageObjectsListRequest
bucket: 'analyticsdump'
delimiter: '/'
maxResults: 1000
prefix: 'mysql_data'
projection: ProjectionValueValuesEnum(noAcl, 1)>
INFO 0130 15:14:58.883990 base_api.py] Making http GET to https://storage.googleapis.com/storage/v1/b/analyticsdump/o?alt=json&fields=prefixes%2CnextPageToken%2Citems%2Fname&delimiter=%2F&maxResults=1000&prefix=mysql_data&projection=noAcl
INFO 0130 15:14:58.884292 base_api.py] Headers: {'accept': 'application/json',
'accept-encoding': 'gzip, deflate',
'content-length': '0',
'user-agent': 'apitools Python/3.7.5 gsutil/4.53 (linux) analytics/disabled '
'interactive/True command/cp google-cloud-sdk/310.0.0'}
INFO 0130 15:14:58.884488 base_api.py] Body: (none)
connect: (storage.googleapis.com, 443)
send: b'GET /storage/v1/b/analyticsdump/o?alt=json&fields=prefixes%2CnextPageToken%2Citems%2Fname&delimiter=%2F&maxResults=1000&prefix=mysql_data&projection=noAcl HTTP/1.1\r\nHost: storage.googleapis.com\r\ncontent-length: 0\r\nuser-agent: apitools Python/3.7.5 gsutil/4.53 (linux) analytics/disabled interactive/True command/cp google-cloud-sdk/310.0.0\r\naccept: application/json\r\naccept-encoding: gzip, deflate\r\nauthorization: Bearer ya29.a0AfH6SMDws2dSPCESElUtzB2SbUKaHB-1nMKXy8rvdMy9i1XI4ywrPttwtcPiY_uUhIPE7OxdUot7inlEZy7FFt8nDGrY8hC6jUicnYYcEteU-EN32AbxUePMNPZ2QH46lwoO4T1AbegUSeglg6jXg8HMZHWr4lb27DQINTCAMmEvGkDsMbp-yw\r\n\r\n'
Zero length chunk ignored
reply: 'HTTP/1.1 200 OK\r\n'
header: X-GUploader-UploadID: ABg5-Uy-p955_vOtrx66ZIxXJjcK0imQ6aVCiJEc2lIqH5oHrGKQtJXXyUIrnnw8tq4qs6yELMCVq6x8RUnRaui2hiY
header: Content-Type: application/json; charset=UTF-8
header: Date: Sat, 30 Jan 2021 09:44:59 GMT
header: Vary: Origin
header: Vary: X-Origin
header: Cache-Control: private, max-age=0, must-revalidate, no-transform
header: Expires: Sat, 30 Jan 2021 09:44:59 GMT
header: Content-Length: 42
header: Server: UploadServer
header: Alt-Svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
INFO 0130 15:14:59.246608 base_api.py] Response of type Objects: <Objects
items: []
prefixes: ['mysql_data/']>
process count: 16
thread count: 5
Copying file://mysql-data/trailStatementsV2.csv [Content-Type=text/csv]...
==> NOTE: You are uploading one or more large file(s), which would run
significantly faster if you enable parallel composite uploads. This
feature can be enabled by editing the
"parallel_composite_upload_threshold" value in your .boto
configuration file. However, note that if you do this large files will
be uploaded as `composite objects
<https://cloud.google.com/storage/docs/composite-objects>`_,which
means that any user who downloads such objects will need to have a
compiled crcmod installed (see "gsutil help crcmod"). This is because
without a compiled crcmod, computing checksums on composite objects is
so slow that gsutil disables downloads of composite objects.
Resuming upload for file://mysql-data/trailStatementsV2.csv
DEBUG 0130 15:14:59.595271 multiprocess_file_storage.py] Read credential file
connect: (storage.googleapis.com, 443)
send: b'PUT /resumable/upload/storage/v1/b/analyticsdump/o?alt=json&fields=crc32c%2Cetag%2Csize%2Cgeneration%2Cmd5Hash%2CcustomerEncryption&uploadType=resumable&upload_id=ABg5-UzThF83Nkcc-6FoOAudG7hRLo06trFaBzV0GB7Iiqr92uU9TkxoXReQlwyqmzsnDWTiePIgWP7S4WvWG90ULdI HTTP/1.1\r\nHost: storage.googleapis.com\r\ncontent-range: bytes */*\r\ncontent-length: 0\r\nauthorization: Bearer ya29.a0AfH6SMDws2dSPCESElUtzB2SbUKaHB-1nMKXy8rvdMy9i1XI4ywrPttwtcPiY_uUhIPE7OxdUot7inlEZy7FFt8nDGrY8hC6jUicnYYcEteU-EN32AbxUePMNPZ2QH46lwoO4T1AbegUSeglg6jXg8HMZHWr4lb27DQINTCAMmEvGkDsMbp-yw\r\nuser-agent: Python-httplib2/0.18.1 (gzip)\r\naccept-encoding: gzip, deflate\r\n\r\n'
Zero length chunk ignored
reply: 'HTTP/1.1 308 Resume Incomplete\r\n'
header: Content-Type: text/plain; charset=utf-8
header: X-GUploader-UploadID: ABg5-UzThF83Nkcc-6FoOAudG7hRLo06trFaBzV0GB7Iiqr92uU9TkxoXReQlwyqmzsnDWTiePIgWP7S4WvWG90ULdI
header: Range: bytes=0-4463525887
header: X-Range-MD5: 706e964f433468d613c4e7a706ff41be
header: Content-Length: 0
header: Date: Sat, 30 Jan 2021 09:44:59 GMT
header: Server: UploadServer
header: Alt-Svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
Catching up md5 for file://mysql-data/trailStatementsV2.csv...
send: b'PUT /resumable/upload/storage/v1/b/analyticsdump/o?alt=json&fields=crc32c%2Cetag%2Csize%2Cgeneration%2Cmd5Hash%2CcustomerEncryption&uploadType=resumable&upload_id=ABg5-UzThF83Nkcc-6FoOAudG7hRLo06trFaBzV0GB7Iiqr92uU9TkxoXReQlwyqmzsnDWTiePIgWP7S4WvWG90ULdI HTTP/1.1\r\nHost: storage.googleapis.com\r\ncontent-length: 1265662718\r\ncontent-type: text/csv\r\ncontent-range: bytes 4463525888-5729188605/5729188606\r\nuser-agent: apitools Python/3.7.5 gsutil/4.53 (linux) analytics/disabled interactive/True command/cp google-cloud-sdk/310.0.0\r\nauthorization: Bearer ya29.a0AfH6SMDws2dSPCESElUtzB2SbUKaHB-1nMKXy8rvdMy9i1XI4ywrPttwtcPiY_uUhIPE7OxdUot7inlEZy7FFt8nDGrY8hC6jUicnYYcEteU-EN32AbxUePMNPZ2QH46lwoO4T1AbegUSeglg6jXg8HMZHWr4lb27DQINTCAMmEvGkDsMbp-yw\r\naccept-encoding: gzip, deflate\r\n\r\n'
'StreamExhausted' object has no attribute 'message'
Encountered exception while copying:
Traceback (most recent call last):
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_api.py", line 1592, in _PerformResumableUpload
additional_headers=addl_headers)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/transfer.py", line 1013, in StreamMedia
additional_headers=additional_headers, use_chunks=False)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/transfer.py", line 957, in __StreamMedia
response = send_func(self.stream.tell())
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/transfer.py", line 947, in CallSendMediaBody
start, additional_headers=additional_headers)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/transfer.py", line 1064, in __SendMediaBody
return self.__SendMediaRequest(request, self.total_size)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/transfer.py", line 1033, in __SendMediaRequest
retries=self.num_retries, check_response_func=CheckResponse)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/http_wrapper.py", line 360, in MakeRequest
max_retry_wait, total_wait_sec))
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/retry_util.py", line 84, in RetriesInDataTransferHandler
http_wrapper.RethrowExceptionHandler(retry_args)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/http_wrapper.py", line 350, in MakeRequest
check_response_func=check_response_func)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/http_wrapper.py", line 400, in _MakeRequestNoRetry
redirections=redirections, connection_type=connection_type)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_media.py", line 313, in NewRequest
connection_type=override_connection_type)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/vendored/oauth2client/oauth2client/transport.py", line 175, in new_request
redirections, connection_type)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/vendored/oauth2client/oauth2client/transport.py", line 282, in request
connection_type=connection_type)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/httplib2/python3/httplib2/__init__.py", line 1994, in request
cachekey,
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/httplib2/python3/httplib2/__init__.py", line 1651, in _request
conn, request_uri, method, body, headers
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_media.py", line 574, in _conn_request
conn.request(method, request_uri, body, headers)
File "/usr/lib/python3.7/http/client.py", line 1252, in request
self._send_request(method, url, body, headers, encode_chunked)
File "/usr/lib/python3.7/http/client.py", line 1298, in _send_request
self.endheaders(body, encode_chunked=encode_chunked)
File "/usr/lib/python3.7/http/client.py", line 1247, in endheaders
self._send_output(message_body, encode_chunked=encode_chunked)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_media.py", line 166, in _send_output
self.send(message_body)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_media.py", line 254, in send
partial_buffer = full_buffer.read(self.GCS_JSON_BUFFER_SIZE)
File "/usr/lib/google-cloud-sdk/platform/gsutil/third_party/apitools/apitools/base/py/stream_slice.py", line 77, in read
self.__max_bytes - self.__remaining_bytes))
apitools.base.py.exceptions.StreamExhausted: Not enough bytes in stream; expected 1265662718, exhausted after 0
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/command.py", line 2306, in PerformTask
results = task.func(cls, task.args, thread_state=self.thread_gsutil_api)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/commands/cp.py", line 780, in _CopyFuncWrapper
preserve_posix=cls.preserve_posix_attrs)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/commands/cp.py", line 995, in CopyFunc
preserve_posix=preserve_posix)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/copy_helper.py", line 3934, in PerformCopy
gzip_encoded=gzip_encoded)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/copy_helper.py", line 2220, in _UploadFileToObject
parallel_composite_upload, logger)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/copy_helper.py", line 2036, in _DelegateUploadFileToObject
elapsed_time, uploaded_object = upload_delegate()
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/copy_helper.py", line 2209, in CallResumableUpload
gzip_encoded=gzip_encoded_file)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/utils/copy_helper.py", line 1837, in _UploadFileToObjectResumable
gzip_encoded=gzip_encoded)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/cloud_api_delegator.py", line 421, in UploadObjectResumable
gzip_encoded=gzip_encoded)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_api.py", line 1734, in UploadObjectResumable
gzip_encoded=gzip_encoded)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_api.py", line 1517, in _UploadObject
additional_headers, progress_callback, gzip_encoded)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_api.py", line 1653, in _PerformResumableUpload
resumable_ex = self._TranslateApitoolsResumableUploadException(e)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/gcs_json_api.py", line 2268, in _TranslateApitoolsResumableUploadException
return ResumableUploadAbortException(e.message)
AttributeError: 'StreamExhausted' object has no attribute 'message'
total_bytes_transferred: 0
DEBUG: Exception stack trace:
Traceback (most recent call last):
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/__main__.py", line 633, in _RunNamedCommandAndHandleExceptions
user_project=user_project)
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/command_runner.py", line 411, in RunNamedCommand
return_code = command_inst.RunCommand()
File "/usr/lib/google-cloud-sdk/platform/gsutil/gslib/commands/cp.py", line 1226, in RunCommand
count=self.op_failure_count, pl=plural_str))
gslib.exception.CommandException: CommandException: 1 file/object could not be transferred.
CommandException: 1 file/object could not be transferred.
Sample code generating those csv files is below:
with open("mysql-data/" + tableName + ".csv", 'r', encoding='utf-8') as file:
with open("mysql-data/"+tableName+"_temp.csv", mode='w', encoding='utf-8') as csv_writer:
my_writer = csv.writer(csv_writer, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
# my_reader = csv.reader(file, delimiter=',')
my_reader = csv.reader((line.replace('\0', '') for line in file))
for row in my_reader:
# print(row)
for i, cell in enumerate(row):
row[i] = cell.replace('\n', ' ') # removing multiple lines
my_writer.writerow(row)
As far as I know opening file using with will close the file after writing but still I tried closing and flushing after writing the file using flush() and close() still file is not getting uploaded to gcs bucket using same command above though file size is 334MB.
Is there anything I missing here? Any lead is highly appreciated. Thanks