CODEWITHSUNDEEP
mysqlgoogle-cloud-platform
Shiva Krishna
Shiva Krishna

Reputation: 13

Moving TSV files from Storage Bucket to Cloud MySql

I have TSV files in Storage Bucket and i want to move files into GCP MySQL. I have used the below script to export files from storage bucket to MySQL:

LOAD DATA INFILE 'gs://Bucket_name/pre.txt' INTO TABLE Prep FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'   IGNORE 1 LINES;

But i am getting an error

" ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES) "

I have reset the password and checked the bucket level permissions and database connections, all are set up correctly, but still getting the access denied error.

Upvotes: 0

Views: 399

Answers (1)

Chris32
Chris32

Reputation: 4961

You are getting this error because you can't read the source file directly from the bucket.

LOAD DATA INFILE can't be used to point to a bucket because it expects the file to be stored locally. Therefore, you need a copy of the TXT stored on the filesystem of the machine you are using to connect to the CloudSQL instance.

You need to download the blob first using the gsutil tool:

gsutil cp gs://[BUCKET_NAME]/[OBJECT_NAME] [SAVE_TO_LOCATION]

And then upload the file to the instance following the documentation

For this you have to:

Add the service account to the bucket ACL as a writer:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]

Add the service account to the import file as a reader:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:R gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]

Import the file:

 gcloud sql import csv [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] --database=[DATABASE_NAME] --table=[TABLE_NAME]

After this you can use the LOAD DATA INFILE making reference to the .txt file in your Cloud SQL instance instead of an external reference.

Upvotes: 1

Related Questions