13

I'm new to nifi and i want to connect SQL server database to nifi and create a data flow with the processors. how can I do this, can any one Help me with this clearly.

Thanks in Advance sam

squillman
  • 13,363
  • 3
  • 41
  • 60
sam
  • 151
  • 1
  • 1
  • 4

3 Answers3

14

Here are two great articles on getting information in and out of databases with NiFi:

  1. http://www.batchiq.com/database-injest-with-nifi.html

  2. http://www.batchiq.com/database-extract-with-nifi.html

They describe/illustrate how to configure a DBCPConnectionPool service to provide connection(s) to an RDBMS, and example flows to extract data and ingest data.

mattyb
  • 11,693
  • 15
  • 20
  • 1
    i need to install SQL server-2012 in my machine which one i need to install. can share me the correct link of SQL server to connect it with nifi. – sam Jan 06 '17 at 16:26
  • 1
    That link to DBCPConnectionPool is out of date. Currently it looks like v1.7.0 is here: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-dbcp-service-nar/1.7.0/org.apache.nifi.dbcp.DBCPConnectionPool/ Sadly the docs use iframe so getting the direct link to the page loses the menu. Better to go to https://nifi.apache.org/docs.html and find the DBCPConnectionPool in the menu. Silly name for a component when you're searching for "database" or "JDBC" or "RDBMS" or something. – Davos Jul 06 '18 at 04:06
11

Expanding on mattyb answer

If you are using the latest Hortonworks sandbox, or other setup that uses docker containers, read below.

You have to install the JDBC jar file inside the docker. For SQL Server, it should be 6.2 or above.

docker ps
docker exec -it <mycontainer uuid> bash

How do I get into a Docker container's shell? will help you log into the container.

cd file:///usr/lib/jvm/jre/lib/
mkdir jdbc
cd ./jdbc
wget https://download.microsoft.com/download/3/F/7/3F74A9B9-C5F0-43EA-A721-07DA590FD186/sqljdbc_6.2.2.0_enu.tar.gz
tar xvzf sqljdbc_6.2.2.0_enu.tar.gz
cp ./sqljdbc_6.2/enu/mssql-jdbc-6.2.2.jre8.jar ./

Apache Nifi JDBC Database DBCPConnectionPool Setup

jdbc:sqlserver://192.168.1.201:1433;databaseName=[your database]
com.microsoft.sqlserver.jdbc.SQLServerDriver

You might need to replace the ip address with IPv4 address of your host found under ipconfig in Windows or ifconfig in Mac/Linux.

You may change file:///usr/lib/jvm/jre/lib/ to any path you desire.

Murtaza Haji
  • 1,093
  • 1
  • 13
  • 32
TamusJRoyce
  • 817
  • 1
  • 12
  • 25
  • This is fantastic! Thank you so much! I was about to bang my head against multiple walls, because I'm running in docker and did not know how this would be done from the container. I think it might be worth adding some code to this to allow the jdbc driver to be downloaded as part of the docker image so that you have the jdbc without having to exec into it – Newteq Developer Sep 19 '19 at 15:59
  • If you comment or link to a working example, I will be glad to add it – TamusJRoyce Sep 20 '19 at 01:58
  • 1
    I'm actually in the process of working on a dockerfile so that we can use it for our solution. I will post it here once it's working :) – Newteq Developer Sep 20 '19 at 08:08
  • Just an FYI - the sqljdbc version 8.4.1 was throwing me an 'out of date' error and only got success when using the 6.2 version mentioned here. – cgage1 Nov 24 '20 at 22:38
3

Expanding on TamusJRoyce's answer

If you are running nifi via a docker image like apache/nifi or the aforementioned Hortonworks sandbox, the following should help you get the required driver on the image so that you don't need to exec into the container to do it manually.

See the comments below the docker file

FROM apache/nifi

USER root
RUN mkdir /lib/jdbc
WORKDIR /lib/jdbc
RUN wget https://download.microsoft.com/download/3/F/7/3F74A9B9-C5F0-43EA-A721-07DA590FD186/sqljdbc_6.2.2.0_enu.tar.gz
RUN tar xvzf sqljdbc_6.2.2.0_enu.tar.gz
RUN cp ./sqljdbc_6.2/enu/mssql-jdbc-6.2.2.jre8.jar ./

USER nifi

EXPOSE 8080 8443 10000 8000

WORKDIR ${NIFI_HOME}
ENTRYPOINT ["../scripts/start.sh"]
  1. The above image uses apache/nifi as the base image. You can use any nifi docker image has a base if you would like.
  2. You can specify any location for lib/jdbc, just remember that you need to use this as the reference for the file location so that it is referenced as file:///lib/jdbc/mssql-jdbc-6.2.2.jre8.jar
  3. Lastly, switch back to the nifi user and finish off with the standard nifi image details. This will allow the image to run correctly.
Newteq Developer
  • 2,257
  • 1
  • 26
  • 32
  • Thanks for providing this answer. I am using NiFi 1.13 in Docker. I have followed your example, but my DBCPConnectionPool is stuck on `Enabling`. With the Error message `Can't load Database Driver`. Any other ideas I can try? – PrestonDocks Aug 15 '21 at 15:29
  • Without seeing the docker file, my only guess is that perhaps you have an incompatible driver specified in the `wget`, or perhaps the location of the `jar` that you need to specify in NiFi itself is not being found – Newteq Developer Aug 17 '21 at 09:53