Support the official google cloud sql connector

[red8888]

Description

Python is one of the few languages gcp supports with an official library for making cloud sql connections:
https://cloud.google.com/sql/docs/mysql/connect-connectors#python

The package:
https://pypi.org/project/cloud-sql-python-connector/

I couldnt find examples of this being supported for the airflow DB connection. Is it supported?

By using this library you don't need direct network access or a separate proxy process to connect to a cloud sql DB. Also if you are using postgres you can use the iam integration so there is no static password.

Supporting this would make deployments way simpler and more secure.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template!

[potiuk]

It is not a "feature" as such (and does not require "feature" issue). This is merely one of the Google Provider's operators or extending the current cloud sql operator with the connector. We have > 3000 operators in Airlfow and there are plenty of changes to them without the need to have "feature" entry.

I converted it into discussion - likely it is a good idea (though it would have to be implemented by someone as another Hook/Operator to do so.

Feel free to add a PR converting it if you want @red8888. Also @lwyszomi @bhirsz @bkossakowska - maybe this is something that can be addressed by the Google team ?

[red8888]

@potiuk Just to make sure I'm clear on the feature I'm wondering about. This would be for connecting a DB backend: https://airflow.apache.org/docs/apache-airflow/stable/howto/set-up-database.html

So the DB airflow uses for its backend, that connection would be through the SQL connector lib for python- which means we wouldn't need private network access, or running the proxy as a separate process, and could also use integrated IAM auth, etc.

So like not for a specific DAG connecting to a DB, it would be used by airflow to connect to the airflow DB.

[potiuk]

Then it should be done as sqlalchemy engine and contributed to SQLAlchemy - not to Airflow.

I don't even think it is possible to do it in Airflow, because it's sqlalchemy is the one who sets up and establishes connections to the DB - not airflow. SQLAlchemy has already a number of engines - and actually multiple of them for same database:

https://docs.sqlalchemy.org/en/14/core/engines.html

Those are currently supported engines for mysql/postgres. You likely need to add `mysql+cloud_sql' and 'postger+cloud_sql'.

# default
engine = create_engine("postgresql://scott:tiger@localhost/mydatabase")

# psycopg2
engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/mydatabase")

# pg8000
engine = create_engine("postgresql+pg8000://scott:tiger@localhost/mydatabase")

# default
engine = create_engine("mysql://scott:tiger@localhost/foo")

# mysqlclient (a maintained fork of MySQL-Python)
engine = create_engine("mysql+mysqldb://scott:tiger@localhost/foo")

# PyMySQL
engine = create_engine("mysql+pymysql://scott:tiger@localhost/foo")