Airflow: Connect to Teradata using JDBC

Back to the Famous Airflow Git Repo

PS C:\> git clone https://github.com/puckel/docker-airflow.gitCloning into 'docker-airflow'...
remote: Enumerating objects: 785, done.
Receiving objects: 92% (723/785), 10 (delta 0), pack-reused 785 Receiving objects: 91% (715/785), 196.01 KiB | 330.00 KiB/s
Receiving objects: 100% (785/785), 209.77 KiB | 355.00 KiB/s, done.
Resolving deltas: 100% (420/420), done.

Download Teradata JDBC Driver

Updating the Dockerfile

airflow[crypto,celery,postgres,hive,jdbc,.....

C:\docker-airflow\Dockerfile

#Install JAVA & Teradata JDBC DriverRUN mkdir -p /usr/share/man/man1
RUN mkdir -p /etc/ssl/certs
RUN mkdir -p /usr/local/airflow/jars
COPY terajdbc4.jar /usr/local/airflow/jars/terajdbc4.jar
RUN apt-get update -y && \
apt-get install -y openjdk-8-jdk && \
apt-get autoclean && \
apt autoremove
RUN apt-get update -y && \
apt-get install -y ca-certificates-java && \
update-ca-certificates -f && \
apt-get -y autoclean && \
apt autoremove
ENV JAVA_HOME /usr/lib/jvm/java-8-openjdk-amd64/
ENV CLASSPATH /usr/local/airflow/jars/terajdbc4.jar

Building the Airflow Docker Image

PS C:\docker-airflow> docker build --rm -t puckel/docker-airflow:latest .

Edit Docker-Compose .yml

PS C:\docker-airflow> dir docker-compose-LocalExecutor.ymlDirectory: C:\docker-airflow-a — — 28/11/2019 3:53 PM 839 docker-compose-LocalExecutor.yml

Run Docker-Compose YML

PS C:\docker-airflow> docker-compose -f docker-compose-LocalExecutor.yml up -d
Creating network “docker-airflow_default” with the default driver
Creating docker-airflow_postgres_1 … done
Creating docker-airflow_webserver_1 … done

Test Teradata JDBC Connectivity

PS C:\docker-airflow> docker psCONTAINER ID IMAGE 
b556797630c5 puckel/docker-airflow:latest
PS C:\docker-airflow> docker exec -ti 0b385c800504 bash airflow@b556797630c5:~$ python
Python 3.7.5 (default, Nov 23 2019, 06:31:35)
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
import jaydebeapi#teradata server,
var_tdpid = '<teradata_server>'
#Teradata Credentials
var_userid = '<user>'
var_password = '<pwd>'
#Class and driver location
td_jars = ['/usr/local/airflow/jars/terajdbc4.jar']
tdat_class='com.teradata.jdbc.TeraDriver'
#Jdbc Connection string
url='jdbc:teradata://'+var_tdpid+'/'
driver_args=[var_userid,var_password]con = jaydebeapi.connect(jclassname=tdat_class \
,url=url, driver_args=driver_args, \
jars=td_jars)
cur = con.cursor ()
cur.execute ("SELECT date")
rows = cur.fetchall()
print(rows)
cur.close()
con.close()
/usr/local/lib/python3.7/site-packages/jpype/_core.py:210: UserWarning: 

Deprecated: convertStrings was not specified when starting the JVM. The default
behavior in JPype will be False starting in JPype 0.8. The recommended setting
for new code is convertStrings=False. The legacy value of True was assumed for
this session. If you are a user of an application that reported this warning,
please file a ticket with the developer.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — “””)
Traceback (most recent call last):
File “<stdin>”, line 3, in <module>
File “/usr/local/lib/python3.7/site-packages/jaydebeapi/__init__.py”, line 381, in connect
jconn = _jdbc_connect(jclassname, url, driver_args, jars, libs)
File “/usr/local/lib/python3.7/site-packages/jaydebeapi/__init__.py”, line 183, in _jdbc_connect_jpype
types_map[i.getName()] = i.getStaticAttribute()
AttributeError: ‘_jpype.PyJPField’ object has no attribute ‘getStaticAttribute’
>>> cur = con.cursor ()
airflow@b556797630c5:~$ pip show JPype1
Name: JPype1
Version: 0.7.0
Summary: A Python to Java bridge.
Home-page: https://github.com/jpype-project/jpype
Author: Steve Menard
Author-email: devilwolf@users.sourceforge.net
License: License :: OSI Approved :: Apache Software License
Location: /usr/local/lib/python3.7/site-packages
Requires:
Required-by: JayDeBeApi

airflow@b556797630c5:~$ pip show jaydebeapi
Name: JayDeBeApi
Version: 1.1.1
Summary: Use JDBC database drivers from Python 2/3 or Jython with a DB-API.
Home-page: https://github.com/baztian/jaydebeapi
Author: Bastian Bowe
Author-email: bastian.dev@gmail.com
License: GNU LGPL
Location: /usr/local/lib/python3.7/site-packages
Requires: JPype1
Required-by:
S C:\docker-airflow> docker exec -ti --user root b556797630c5 bashroot@b556797630c5:/usr/local/airflow#
pip install --upgrade JPype1==0.6.3
..............
Installing collected packages: JPype1
Found existing installation: JPype1 0.7.0
Uninstalling JPype1-0.7.0:
Successfully uninstalled JPype1-0.7.0
Successfully installed JPype1-0.6.3
root@b556797630c5:/usr/local/airflow# python
Python 3.7.5 (default, Nov 23 2019, 06:31:35)
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import jaydebeapi
>>>
>>> #teradata server,
... var_tdpid = '<teradata_server>'
>>>
>>> #Teradata Credentials
... var_userid = '<user>'
>>> var_password = '<pwd>'
>>>
>>> #Class and driver location
... td_jars = ['/usr/local/airflow/jars/terajdbc4.jar']
>>> tdat_class='com.teradata.jdbc.TeraDriver'
>>>
>>> #Jdbc Connection string
... url='jdbc:teradata://'+var_tdpid+'/'
>>>
>>> driver_args=[var_userid,var_password]
>>>
>>>
>>> con = jaydebeapi.connect(jclassname=tdat_class \
... ,url=url, driver_args=driver_args, \
... jars=td_jars)
>>> cur = con.cursor ()
>>> cur.execute ("SELECT date")
>>> rows = cur.fetchall()
>>> print(rows)
[('2019-11-28',)]
>>> cur.close()
>>> con.close()

Teradata JDBC Connection via Airflow Web GUI

What’s Next?

--

--

Learner. Interests include Cloud and Devops technologies.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store