5

I am in current need of Airflow db connection credentials for my Airflow instance in Cloud Composer.
All I see on Airflow connection UI is airflow_db mysql airflow-sqlproxy-service.

I would like to connect to it via DataGrip.
Another thing is if I want to change the [core] sql_alchemy_conn override environmental variable, how do I do it as it is restrictedw hen I add it on my env variable on Cloud Composer environments.

Theo
  • 57,719
  • 8
  • 24
  • 41
Plengo
  • 97
  • 1
  • 10

2 Answers2

2

Cloud Composer isn't designed to give external access to the database. However you can connect to the GKE cluster and then access it from within the cluster. This doc shows how to do that with SQLAlchemy, but you can also get direct MySQL CLI access by running mysql -h $AIRFLOW_SQLPROXY_SERVICE_SERVICE_HOST -u root airflow-db instead of sqlalchemy in step 6.

David
  • 9,288
  • 1
  • 20
  • 52
  • I want to clean my xcoms via a dag tasks when my workflow is finished via a code like this, and it is somehow prevented ``` clean_xcoms = PostgresOperator( task_id='clean_xcoms', postgres_conn_id='airflow_db', sql="delete from xcom where dag_id='{{ dag.dag_id }}'", dag=dag ) ``` Error: ``` could not connect to server: Connection timed out Is the server running on host "airflow-sqlproxy-service" (ip address) and accepting TCP/IP connections on port 5432? ``` – Plengo Dec 06 '18 at 12:46
  • 1
    @Plengo Cloud Composer uses MySQL as Airflow DB, but you are using the `PostgresOperator`. – jcdenton Dec 07 '18 at 13:07
0

Adding to David's answer:

After following directions to connect to the GKE cluster worker the $AIRFLOW_SQLPROXY_SERVICE_SERVICE_HOST env variable did not exist for me . Instead, I parsed the connection details from the SQLAlchemy connection string env variable that Composer makes when SQLAlchemy is installed. 

echo $AIRFLOW__CORE__SQL_ALCHEMY_CONN

Should return a connection string of the form: mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>.

Parsing the host, user and dbname from this i'm able to connect with: 

mysql -h <host> -u <user> <dbname>
gavinest
  • 308
  • 2
  • 12