0

I'm trying to execute a SqlSensor task in Airflow using a connection to Teradata database. The connection is configured as follow:

Connection

I have provide in particular 2 driver paths separated by ", " but I am not sure if it's the proper way to do it?

  • /home/airflow/java_sample/tdgssconfig.jar

  • /home/airflow/java_sample/terajdbc4.jar

When the DAG executes, it triggers the error message

[2017-08-02 02:32:45,162] {models.py:1342} INFO - Executing <Task(SqlSensor): check_running_batch> on 2017-08-02 02:32:12
[2017-08-02 02:32:45,179] {base_hook.py:67} INFO - Using connection to: jdbc:teradata://myservername.mycompanyname.org/database=MYDBNAME,TMODE=ANSI,CHARSET=UTF8
[2017-08-02 02:32:45,313] {sensors.py:109} INFO - Poking: SELECT BATCH_KEY FROM MYDBNAME.AUDIT_BATCH WHERE BATCH_OWNER='ARO_TEST' AND AUDIT_STATUS_KEY=1;
[2017-08-02 02:32:45,316] {base_hook.py:67} INFO - Using connection to: jdbc:teradata://myservername.mycompanyname.org/database=MYDBNAME,TMODE=ANSI,CHARSET=UTF8
[2017-08-02 02:32:45,497] {models.py:1417} ERROR - java.lang.RuntimeException: Class com.teradata.jdbc.TeraDriver not found

What am I doing wrong?

halfer
  • 19,824
  • 17
  • 99
  • 186
Alexis.Rolland
  • 5,724
  • 6
  • 50
  • 77
  • It doesn't seem like given a separator like ",", https://github.com/apache/incubator-airflow/blob/master/airflow/hooks/jdbc_hook.py#52 – Chengzhi Aug 02 '17 at 04:42
  • Yes it seems it's only accepting one value for the driver path while JayDeBeAPI accepts Python lists. How can I do? – Alexis.Rolland Aug 02 '17 at 05:32
  • You have to remove the space after the , seperator. For example: path/a.jar,path/bjar ... Otherwise the second jar path starts with a space and will not be found by airflow... – jim Apr 30 '20 at 11:30

2 Answers2

4

The appropriate way to input multiple jars in the connections page is to separate both fully qualified paths with a comma which you did above.

I can confirm this is the approach I took and it worked (Airflow 10.1.1 and 10.1.2).

See: https://github.com/apache/airflow/blob/master/airflow/hooks/jdbc_hook.py#L51

Bonus: If you use Ad Hoc Query in Data Profiling to test it out, you'll notice that you'll get an error when you send a SELECT statement because Airflow wraps it in a LIMIT clause which TD doesn't support.

David Hernandez
  • 68
  • 1
  • 7
0

The solution provided by my team member was to merge the two jar into a single jar file. After doing it and indicating that new jar file in the driver path, it worked as expected.

Here is the link to the JAR file: https://github.com/alexisrolland/linux-setup/blob/master/teradataDriverJdbc.jar

Here is a code snippet example to use the connection in a SQLSensor Task:

CheckRunningBatch = SqlSensor(
  task_id='check_running_batch',
  conn_id='ed_data_quality_edw_dev',
  sql="SELECT CASE WHEN MAX(BATCH_KEY) IS NOT NULL THEN 0 ELSE 1 END FROM DATABASE.AUDIT_BATCH WHERE STATUS_KEY=1;",
  poke_interval=300,
  dag=dag)
Alexis.Rolland
  • 5,724
  • 6
  • 50
  • 77
  • Alexis, do you mind post the steps of how to merge the two TD drivers into one? I don't have java environment setup here and have exhausted answered on the web trying to do so.... – Xiushi Le Nov 13 '18 at 02:51
  • @XiushiLe sorry for the late reply. I don't know how the JAR files have been merged but I have posted the merged version here if you wish to use it: https://github.com/alexisrolland/linux-setup/blob/master/teradataDriverJdbc.jar – Alexis.Rolland Nov 19 '18 at 13:13
  • Could you also extend your example code a bit by including how to then use the conn_id? I'm not sure how to create a custom operator for Teradata. – Xiushi Le Dec 14 '18 at 21:01
  • @XiushiLe I'm not sure if Airflow connections can be used in custom operator, you might need to Google that. I've posted an example above how to use the connection in a `SQLSensor` operator. You just need to provide the `conn_id` in the operator parameters. If you want to create a custom connection in your code you can also do it with this: https://stackoverflow.com/questions/42599411/connecting-to-teradata-using-python/50147908#50147908 – Alexis.Rolland Dec 15 '18 at 03:21