CODEWITHSUNDEEP
google-cloud-platformschemaairflowgoogle-cloud-composer
Imad
Imad

Reputation: 2741

How to find the schema of Airflow Backend database?

I am using apache airflow (v 1.10.2) on Google Cloud Composer, and I would like to view the schema of the airflow database. Where can I find this information?

Upvotes: 3

Views: 5168

Answers (2)

Nick_Kh
Nick_Kh

Reputation: 5243

According to the Composer architecture design Cloud SQL is the main place where all the Airflow metadata is stored. However, in order to grant authorization access from client application over the GKE cluster to the database we use Cloud SQL Proxy service. Particularly in Composer environment we can find airflow-sqlproxy* Pod, leveraging connections to Airflow Cloud SQL instance.

Saying this, I believe that it will not make any problem establish connection to the above mentioned Airflow database from any of the GKE cluster workloads(Pods).

For instance, I will perform connection from Airflow worker reaching airflow-sqlproxy-service.default Cloud SQL proxy service and further perform DB discovering via mysql command-line util:

 kubectl -it exec $(kubectl get po -l run=airflow-worker -o jsonpath='{.items[0].metadata.name}' \
    -n $(kubectl get ns| grep composer*| awk '{print $1}')) -n $(kubectl get ns| grep composer*| awk '{print $1}') \
    -c airflow-worker -- mysql -u root -h airflow-sqlproxy-service.default

 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +----------------------------------------+
    | Database                               |
    +----------------------------------------+
    | information_schema                     |
    | composer-1-8-3-airflow-1-10-3-*        |
    | mysql                                  |
    | performance_schema                     |
    | sys                                    |
    +----------------------------------------+
    5 rows in set (0.00 sec)

Upvotes: 1

hopeIsTheonlyWeapon
hopeIsTheonlyWeapon

Reputation: 567

There are couple of ways I can think of comparing our current design.

  • External metadata DB. If you can connect to the DB then you can get the schema.
  • From your UI you can go to Data Profiling and run query against the metadata tables(depends on your database types(mysql or postgres etc) and find the information from there and create a schema diagram.

I hope this helps.

Upvotes: 1

Related Questions