Importing “|” Delimited CSV Data from Bucket to MySQL on Cloud SQL

Google Cloud Database
1

Hello,

I am trying to import data from a CSV file that is stored in a Bucket. The CSV file uses the “|” delimiter. I would like to import this data into a MySQL database on Cloud SQL.

However, it seems that the root user does not have the “File” privilege, which means I cannot perform this import via MySQL Workbench.

Could anyone guide me on how I can carry out this task? Any help or direction would be greatly appreciated.

Thank you!

I hope this helps! If you need further assistance, feel free to ask.

2

Importing CSV data into Cloud SQL when you don’t have root user FILE privileges can be done in a few ways. Here are common approaches:

Method 1: Using the Cloud Console Import Feature

  1. Navigate to your Cloud SQL instance:

    • In the Google Cloud Console, go to the SQL section.
    • Select the specific Cloud SQL instance where you want to import the data.

  2. Access the Import option:

    • Click on the Import button (often found on the instance’s overview page).

  3. Configure the import:

    • Choose file: Browse and select your CSV file from the Cloud Storage bucket or provide the bucket path where it is located.
    • Format: Select “CSV.”
    • Database: Select the target database within your Cloud SQL instance.
    • Table:
      • If the table already exists, choose it from the list.
      • If you want to create a new table, provide the desired table name.
    • Import Options (important):
      • In the “Column delimiter” field, input “|” to specify the pipe delimiter.

  4. Initiate the import: Click the Import button to start the process.

Method 2: Using the gcloud sql import csv Command

  1. Install the Google Cloud SDK: Follow the instructions from Google Cloud SDK Installation.

  2. Run the gcloud sql import csv command:

    gcloud sql import csv [YOUR_INSTANCE_NAME] gs://[YOUR_BUCKET_NAME]/[YOUR_CSV_FILE.csv] \ --database=[YOUR_DATABASE_NAME] \ --table=[YOUR_TABLE_NAME] \ --fields-terminated-by=|

    • Replace the placeholders with your actual instance, bucket, file, database, and table names.
    • The --fields-terminated-by=| part specifies the pipe delimiter.

Method 3: Using Client-side Tools (Note: Limited Applicability in Cloud SQL)

  • If you have the CSV file locally and can connect to your Cloud SQL instance using tools like MySQL command-line client or MySQL Workbench, you might consider using LOAD DATA LOCAL INFILE. However, this method typically requires the FILE privilege, which might not be available in Google Cloud SQL due to managed service constraints.

Additional Considerations:

  • CSV Formatting: Ensure your CSV file adheres to standard CSV formatting, using the pipe delimiter in your case.
  • Error Handling: If the import encounters errors (e.g., data type mismatches), Cloud SQL import logs can provide details for troubleshooting.
3

The first option you mentioned is not possible because the console does not have the option to choose the import delimiter. Is it a cons version issue?

I have already tried the other options with the exception of installing the SDK, but as I use the company’s notebook this installation is not allowed.