Default Airflow DB and how to change it

[manel....@liqid.de]

Hello,

I am pretty new in using Composer, I am wondering how to find the default configuration of Airflow specially "core-sql_alchemy_conn" it is not mentionned properly in the documentation. and if I want to change Airflow DB How should I change it? throught the environment I created ?

Best,

Manel

[Cheng Liu]

Hi Manel,

Hope this is sth that you are looking for: from the airflow web UI, you can access your connection configs from Admin > Connections.

LIQID Investments GmbH, eingetragen im Handelsregister des Amtsgerichts Berlin-Charlottenburg, HRB 165254B. Geschäftsführer Christian Schneider-Sickert. Ust-ID: DE300456243. Muttergesellschaft der LIQID Asset Management GmbH.

VERTRAULICHKEITSHINWEIS:  Diese Nachricht und jegliche Anlagen sind vertraulich und unter Umständen geheim oder anderweitig vor einer Offenlegung geschützt. Falls Sie nicht der beabsichtigte Empfänger sind, ist es Ihnen nicht gestattet, diese Nachricht oder eine Anlage zu kopieren oder ihren Inhalt gegenüber irgendwelchen anderen Personen offenzulegen. Falls Sie diese Nachricht versehentlich erhalten haben, setzen Sie den Absender bitte umgehend davon in Kenntnis, und löschen Sie die Nachricht und jegliche Anlagen aus Ihrem System. Die LIQID Investments GmbH übernimmt keine Haftung in Bezug auf irgendwelche Auslassungen oder Fehler in dieser Nachricht, die sich unter Umständen aufgrund der Übertragung per E-Mail ergeben, oder für Schäden als Ergebnis einer unbefugten Änderung des Inhalts dieser Nachricht und jeglicher Anlagen. Die LIQID Investments GmbH garantiert nicht, dass diese Nachricht frei von Viren ist, und übernimmt keine Haftung in Bezug auf Schäden, die durch irgendeinen unter Umständen mit der Nachricht übertragenen Virus verursacht werden.

DISCLAIMER: This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. LIQID Investments GmbH does not accept liability for any omissions or errors in this message which may arise as a result of e-mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. LIQID Investments GmbH does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith.

--
You received this message because you are subscribed to the Google Groups "cloud-composer-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsub...@googlegroups.com.
To post to this group, send email to cloud-composer-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/7d021878-5d00-4c05-9705-2ca6b7fdb041%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[manel....@liqid.de]

Hello Cheng Liu,

What I am looking for, for exampel if I create a SQL instance in Google Cloud which PostgreSQL DB how to link that instance to Airflow DB, because I see the default one is sqlite and from the documentation its saying that the Airflow db created with the environment and once the environment deleted the DB will be erased I guess. also Nothing is clear how to access that default DB or where to find it :/

So I assume that changing the metadatabase of Airflow it's not possible?

To post to this group, send email to cloud-compo...@googlegroups.com.

[Cheng Liu]

Hmmm. I am not sure about that. So the config document indicates that we can set up the connection string using the following setting.

[core]
sql_alchemy_conn = my_conn_string

And I does find this env variable from the scheduler pod. That means we should be able to access the metadata DB from the scheduler pod with a proper (maybe the default) password.

$ echo $AIRFLOW__CORE__SQL_ALCHEMY_CONNmysql+mysqldb://root:@airflow-sqlproxy-service/airflow-db 

According to the config document, the environment variable will be considered at the 1st place for the meta DB. So I feel like you will need to take out this env variable during the Airflow initialization (or update it) in order to config an external DB. But at least we should be able to access the DB from the scheduler pod. And I agree with you. If this DB is created during the Airflow initialization, it will definitely be shut down if you delete the airflow server. 

In the meanwhile, I assume we can access most of the info from either the web or command line interface. 

To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsubscri...@googlegroups.com.

To post to this group, send email to cloud-compo...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/7d021878-5d00-4c05-9705-2ca6b7fdb041%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

LIQID Investments GmbH, eingetragen im Handelsregister des Amtsgerichts Berlin-Charlottenburg, HRB 165254B. Geschäftsführer Christian Schneider-Sickert. Ust-ID: DE300456243. Muttergesellschaft der LIQID Asset Management GmbH.

VERTRAULICHKEITSHINWEIS:  Diese Nachricht und jegliche Anlagen sind vertraulich und unter Umständen geheim oder anderweitig vor einer Offenlegung geschützt. Falls Sie nicht der beabsichtigte Empfänger sind, ist es Ihnen nicht gestattet, diese Nachricht oder eine Anlage zu kopieren oder ihren Inhalt gegenüber irgendwelchen anderen Personen offenzulegen. Falls Sie diese Nachricht versehentlich erhalten haben, setzen Sie den Absender bitte umgehend davon in Kenntnis, und löschen Sie die Nachricht und jegliche Anlagen aus Ihrem System. Die LIQID Investments GmbH übernimmt keine Haftung in Bezug auf irgendwelche Auslassungen oder Fehler in dieser Nachricht, die sich unter Umständen aufgrund der Übertragung per E-Mail ergeben, oder für Schäden als Ergebnis einer unbefugten Änderung des Inhalts dieser Nachricht und jeglicher Anlagen. Die LIQID Investments GmbH garantiert nicht, dass diese Nachricht frei von Viren ist, und übernimmt keine Haftung in Bezug auf Schäden, die durch irgendeinen unter Umständen mit der Nachricht übertragenen Virus verursacht werden.

DISCLAIMER: This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. LIQID Investments GmbH does not accept liability for any omissions or errors in this message which may arise as a result of e-mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. LIQID Investments GmbH does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith.

--
You received this message because you are subscribed to the Google Groups "cloud-composer-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsub...@googlegroups.com.

To post to this group, send email to cloud-composer-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/42f49dee-de94-4a91-bd97-d55b496a1a72%40googlegroups.com.

[Cheng Liu]

Actually, I can see there is airflow-sqlproxy-* pod. I suspect that's the pod for the metadata DB.

$ kubectl get podsNAME READY STATUS RESTARTS AGEairflow-redis-67f555bdb8-6gm26 1/1 Running 0 10dairflow-scheduler-6666cfd7d-4dx7x 2/2 Running 0 9dairflow-sqlproxy-7dd58c7bbc-hpwnl 1/1 Running 0 10dairflow-worker-75f8fb6646-bh957 2/2 Running 0 9dairflow-worker-75f8fb6646-gdxd7 2/2 Running 0 9dairflow-worker-75f8fb6646-v5kwv 2/2 Running 0 9d

This link shows you how to list all the pods.

https://cloud.google.com/composer/docs/how-to/managing/deploy-webserver

You can exec into any of the pod using sth like 

kubectl exec -it airflow-scheduler-6666cfd7d-4dx7x /bin/sh

To post to this group, send email to cloud-composer-discuss@googlegroups.com.

[manel....@liqid.de]

Hello,

Thank you Cheng Liu for the clarification about pods and kubernetes part. for my side in the composer environment when I run 

echo $AIRFLOW__CORE__SQL_ALCHEMY_CONN

it's empty

and When I do it inside my sql-proxy pod is the same

But inside the Scheduler pod is showing the default connection link I guess

 So if I understood what you have said, if I deploy the webserver with the database I have in Google Cloud SQL, I will have Airflow metadatabase living in my instance and event if I delete the environment the DB will not be deleted or affected as it is in my instance. The other thing if I have to deploy the webserver to change the DB I also need to deploy the Scheduler again as we know Scheudler is interacting with Airflow metadatabase to get information about the DagRuns ?

Thank you,

Best,

Manel

To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsub...@googlegroups.com.

To post to this group, send email to cloud-compo...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/7d021878-5d00-4c05-9705-2ca6b7fdb041%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

LIQID Investments GmbH, eingetragen im Handelsregister des Amtsgerichts Berlin-Charlottenburg, HRB 165254B. Geschäftsführer Christian Schneider-Sickert. Ust-ID: DE300456243. Muttergesellschaft der LIQID Asset Management GmbH.

VERTRAULICHKEITSHINWEIS:  Diese Nachricht und jegliche Anlagen sind vertraulich und unter Umständen geheim oder anderweitig vor einer Offenlegung geschützt. Falls Sie nicht der beabsichtigte Empfänger sind, ist es Ihnen nicht gestattet, diese Nachricht oder eine Anlage zu kopieren oder ihren Inhalt gegenüber irgendwelchen anderen Personen offenzulegen. Falls Sie diese Nachricht versehentlich erhalten haben, setzen Sie den Absender bitte umgehend davon in Kenntnis, und löschen Sie die Nachricht und jegliche Anlagen aus Ihrem System. Die LIQID Investments GmbH übernimmt keine Haftung in Bezug auf irgendwelche Auslassungen oder Fehler in dieser Nachricht, die sich unter Umständen aufgrund der Übertragung per E-Mail ergeben, oder für Schäden als Ergebnis einer unbefugten Änderung des Inhalts dieser Nachricht und jeglicher Anlagen. Die LIQID Investments GmbH garantiert nicht, dass diese Nachricht frei von Viren ist, und übernimmt keine Haftung in Bezug auf Schäden, die durch irgendeinen unter Umständen mit der Nachricht übertragenen Virus verursacht werden.

DISCLAIMER: This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. LIQID Investments GmbH does not accept liability for any omissions or errors in this message which may arise as a result of e-mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. LIQID Investments GmbH does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith.

--
You received this message because you are subscribed to the Google Groups "cloud-composer-discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsub...@googlegroups.com.

To post to this group, send email to cloud-compo...@googlegroups.com.

[manel....@liqid.de]

Adding to that I already changed the webserver link in Airflow configuration

It's also changed in the Web UI but I didn't get any tables in the database (I mean the log dagruns, task instances tables) so am I missing something or I should not write the instance link there? sorry if I am asking a lot I just need to understand the whole process.

Best,

Manel

[Cheng Liu]

Hi Manel,

This seems to beyond my knowledge now. Feel like after your update the airflow config, you also need to remove the env variable AIRFLOW__CORE__SQL_ALCHEMY_CONN from your schedule. Otherwise, it will overwrite your airflow config. And you will need to give Airflow credentials in order to access your DB.

[Rajesh Sampathrajan]

Hi Cheng,

I tried to attempt use of alchemy_conn while creating a cloud sql instance, but not sure which db and table - the airflow uses to store its metadata information. By default it uses sqlite. Do you know which node should I look into to get to know details of Sqllite metadata info?

thanks

Rajesh Sampath