Search code examples
mysqlgoogle-cloud-sqlload-data-infileselect-into-outfile

Use LOAD DATA INFILE and SELECT INTO OUTFILE in Cloud SQL

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

Solution

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