Cloud SQL/NiFi: Connect to cloud sql database with python and NiFi

Question

So, I am doing a etl process in which I use Apache NiFi as an etl tool along with a postgresql database from google cloud sql to read csv file from GCS. As a part of the process, I need to write a query to transform data read from csv file and insert to the table in the cloud sql database. So, based on NIFi, I need to write a python to execute a sql queries automatically on a daily basis. But the question here is that how can I write a python to connect with the cloud sql database? What config that should be done? I have read something about cloud sql proxy but can I just use an cloud sql instance's internal ip address and put it in some config file and creating some dbconnector out of it?

Thank you

Edit: I can connect to cloud sql database from my vm using psql -h [CLOUD_SQL_PRIVATE_IP_ADDR] -U postgres but I need to run python script for the etl process and there's a part of the process that need to execute sql. What I am trying to ask is that how can I write a python file that use for executing the sql

e.g. In python, query = 'select * from table ....' and then run postgres.run_sql(query) which will execute the query. So how can I create this kind of executor?

Answer 1

I don't understand why you need to write any code in Python? I've done a similar process where I used GetFile (locally) to read a CSV file, parse and transform it, and then used ExecuteSQLRecord to insert the rows into a SQL server (running on a cloud provider). The DBCPConnectionPool needs to reference your cloud provider as per their connection instructions. This means the URL likely reference something.google.com and you may need to open firewall rules using your cloud provider administration.

Answer 2

You can connect directly to a Cloud SQL instance via a Public IP (public meaning accessible via the public internet) mostly the same as a local database. By default, connections via Public IP require some form of authorization. Here you have 3 (maybe 4*) options:

1. Cloud SQL Proxy - this is an executable that listens on a local port or unix socket and uses IAM permissions to authenticate, encrypt, and forward connections to the database.

2. Self-managed SSL/TLS - Create a SSL/TLS key pair, providing the client key to NiFi as proof of authentication.

3. Whitelisting an IP - Whitelist which IPs are allowed to connect (so the IP that NiFi publicly sits on). This is the least secure option for a variety of reasons.

Any of these options should work for you to connect directly to the database. If you still need to specifics for Python, I suggest looking into SQLAlchemy and use these snippets here as reference.

• Another possible option: It looks like NiFi is using Java and allows you to specify a jar as a driver, so you could potentially also provide a driver bundled with the Cloud SQL JDBC SocketFactory to authenticate the connection as well.

Answer 3

To connect to a Cloud SQL instance with Python you need the Cloud SQL Proxy. Also you have to set a configuration file.

In this tutorial you can find step by step how to achieve this. It is described how to set the configuration file needed for the connection (here you can find an example of this file as well).

Also in the tutorial there are some examples showing you how to interact with your database with Python.