Connect to remote database airflow

Airflow
1

Hi

Am new to airflow.I have to create a dag which will insert data to a table in remote database.Steps that has been done till now :.

  1. After installation ,ran cmd pip install 'apache-airflow[oracle] so that i can use oracle operator
  2. Created connection string from UI to connect to remote database
  3. Then I wrote my first dag but it didn’t seem to run.

Issues faced :

  1. Am not sure where to place my dag. Currently i have placed it in my home directory. This might be the reason it is not visible in UI. I tried to run it directly using unix using command : python {filename} .But i got error invalid syntax where i defined the sql.
    Below command does not work may be my dag is not visble in list_dags.

Airflow run dagname taskname 2020-1-20

  1. When i go to Data profiling and type any query against the connection ,I get error : TNS listener was not given SERVICE_NAME in CONNECT_DATA

Could somebody please help me resolving these issues.I believe i have missed something.may be i need cx_oracle and oracle client to be installed on the same machine as airflow?
Or some changes to be done to airflow.cfg file? ( To add the cfg file that i found was the one available in my home directory)

Code that i wrote is below :

from datetime import datetime,timedelta

import airflow
from airflow import DAG
from airflow.operators.bash_operator import BashOperator
from airflow.operators.oracle_operator import OracleOperator

default_args = {
‘owner’: ‘Airflow’,
‘depends_on_past’: False,
‘start_date’: datetime(2020,01,17),
‘email’: [‘airflow@example.com’],
‘email_on_failure’: False,
‘email_on_retry’: False,
‘retries’: 0,
‘retry_delay’: timedelta(minutes=5)

}

with DAG(‘example_sql_dag’,
default_args=default_args,
catchup=False,
schedule_interval=’*/10 * * * *’
) as dag:
opr_sql = OracleOperator(task_id=‘task_sql’,
oracle_conn_id=‘Oracle_schema’
sql= ‘insert into table1 (a,b,c)values (1,2,3)’,
autocommit =‘True’)

2

@Newbie_ss Did you ever solve this? I assume your issue is that an Oracle client was not deployed/installed within the environment where your DAG was being executed. From what I understand cx_Oracle still relies on an Oracle client to be present on the host to establish a connection to the Oracle database.

I have successfully gotten this to work using a pre-built Docker image from Oracle, my local Windows environment, and a semi-hacky way within the astro CLI Debian build (see Connecting to Oracle database from the Astronomer Airflow image).

I ended up having to install the Oracle instant client RPM files myself within the image, set up a tnsnames.ora and the TNS_ADMIN environment variable, and then it would connect.

I think that the preferred way to do this would be to build a custom Dockerfile that does all of this for me and then use that as the base for my Astronomer Airflow.