0

I am testing Google Cloud SQL and I have to test the instructions LOAD DATA INFILE and SELECT...INTO OUTFILE.

I understood that the instruction LOAD DATA LOCAL INFILE could be used instead of LOAD DATA INFILE, but how can it be used in Cloud SQL?

Can the instruction SELECT...INTO OUTFILE be used as is?

Thank you Renzo for your answer but when I try to execute the instruction SELECT.. INTO OUTFILE when I am connected to the Cloud SQL instance, I get the following error message:

ERROR 1045 (28000) at line 1: Access denied for user 'root'@'%'

I tried to used the instruction LOAD DATA LOCAL INFILE from my computer connected to Cloud SQL instance on MySQL and it worked. I used a file located on my computer to import CSV data into a table successfully. But I am wondering if I can use a file present in a bucket to do the same...

I try to explain what I need to do:

We are migrating our Website to GCP App Engine/Cloud SQL. We extensively use both instructions LOAD DATA INFILE which select files from a folder "ftp" to load them into the databases. As well, we use the SELECT INTO OUTFILE to export data to CSV files into a folder of our Website. So my concern is to be able to use the same process on App Engine/Cloud SQL.

The difference is that the Website instance and database instance are separated on GCP. Should we use buckets on GCP to replace our folders ? Or should we create folders on App Engine / Cloud SQL instance instead? What is the best solution according to you?

Thanks in advance

Patulacci
  • 5
  • 5

1 Answers1

1

According to the page about Importing data into Cloud SQL,

you can also use the LOAD DATA LOCAL INFILE statement in the mysql client, which loads a local file to the database

You can follow a sample steps in this link to start the mysql client by connecting to your Cloud SQL instance first through Cloud Shell. After connecting, you will be able to execute the import statement: LOAD DATA LOCAL INFILE.

The same steps can be applied for exporting and the syntax SELECT...INTO OUTFILE can be used as is. Together with other alternatives, this can be also found on Exporting data from Cloud SQL :

Exporting in CSV format is equivalent to running the following SQL statement:

SELECT <query> INTO OUTFILE ... CHARACTER SET 'utf8mb4'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
        ESCAPED BY '\\' LINES TERMINATED BY '\n'

Once you have executed the operations, it is also advisable to check their status especially if it is long running or an error has occurred.

Justin
  • 176
  • 7
  • I have tried SELECT INTO OUTFILE while connected to Cloud SQL instance but it does not work, I get the error "Access denied for user". – Patulacci May 16 '19 at 13:08
  • Since I cannot use the instruction SELECT...INTO OUTFILE on Cloud SQL, I decided to copy the file to GCP bucket via gcloud instruction or Google API. Regarding LOAD DATA LOCAL INFILE, I decided to copy the file from GCP bucket to App Engine temp folder, then use LOAD DATA LOCAL INFILE to import data into the database. I guess it should work, I need to test though... Thanks – Patulacci May 21 '19 at 09:43