将TSV文件从存储桶移动到Cloud MySql [英] Moving TSV files from Storage Bucket to Cloud MySql

问题描述

我在存储桶中有TSV文件，我想将文件移到GCP MySQL中.我已使用以下脚本将文件从存储桶导出到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;

但是我遇到了错误

错误1045(28000):用户'root'@'％'的访问被拒绝(使用 密码:是)"

" 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.

推荐答案

由于无法直接从存储桶中读取源文件，因此出现此错误.

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

LOAD DATA INFILE不能用于指向存储桶，因为它希望文件存储在本地.因此，您需要存储在用于连接到CloudSQL实例的计算机的文件系统上的TXT副本.

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.

首先需要使用gsutil工具下载blob :

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

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

然后根据文档将文件上传到实例

为此，您必须:

以编写者身份将服务帐户添加到存储桶ACL:

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]

导入文件:

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

此后，您可以使用LOAD DATA INFILE引用Cloud SQL实例中的.txt文件，而不是使用外部引用.

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.

这篇关于将TSV文件从存储桶移动到Cloud MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！