How does Cloud SQL (Postgres) import from CSV even work?

[Nate Finch]

I need to import approximately 152k rows from an external database. I have the info in a csv file exported from the other database.

I need to transform that csv so it matches the format my database expects. For my local postgres, this is easy, I can format it and use the COPY command in postgres, like this:

COPY users(email, password, pwd_kind, failed_auth_attempts, last_auth_attempt, locked_until, verified)

FROM '/Users/finchnat/import.csv'

DELIMITER ','

CSV HEADER

QUOTE '"';

But CloudSQL doesn't let you have superusers, so I can't use copy.  And the csv import feature doesn't seem to give you any options at all for mapping what's in the csv to the database table columns.

And as far as I can tell there's literally zero information about how to format your csv file so the import feature will know how to map the columns in the csv to the columns in the database table.

So... how do you use it?  And where's the documentation that fills in the gigantic missing piece of how to format your CSV so it works with this feature?

Any help would be appreciated.

-Nate

[Elliott (Google Cloud Platform Support)]

Hello Nate,

I understand that you would like to transform your CSV file to match database table columns. Also you mentioned that there is no documentation available to implement your workflow, namely, how it is used and missing documentation.

I did a check to find a way to do what you want in the documentation but you are right. This way does not currently exist and I apologize for this. I was able to confirm the commands you used but you are correct that they cannot be run as a regular user. The super user must be used and this does not work for you.

I think this is an opportunity to create a feature request to ask the Cloud SQL Specialists to introduce a feature that is needed to satisfy your use case. I was unable to find an existing feature request exactly but I would like to create one on your behalf. I can only imagine how frustrating it must be not to have all the tools available to do your work.

To add value to this feature request, can you please describe how you would like to have the mapping implemented in order for you to do your work?

If you have any other questions or follow ups, you may reply to this thread and we will assist you then.

Thank you for your patience and understanding.

I will wait for your response.

[Nate Finch]

For what it's worth, the way I figured out how to do it was to use cloud_sql_proxy and then I could use psql which has a \copy command that is basically just like COPY, but doesn't require superuser privileges. 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/R4RPqK_MQ0A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/a7665540-7acf-40b8-9c8e-0e10368fb18fn%40googlegroups.com.