NiFi - Oracle Connect Processor: ExecuteSQL 1) Prepare Oracle Data (Optional) Prepare the data, and create the Oracle table:   CREATE TABLE USER_MASTER ( USER_ID VARCHAR2(8) NOT NULL, DEPT_NO VARCHAR2(8), USER_NAME VARCHAR2(32), CREATED_ON DATE DEFAULT SYSDATE, MODIFIED_ON DATE, CONSTRAINT pk_USER_MASTER PRIMARY KEY(USER_ID) );   Insert the data into Oracle Table:   INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　1 Ro','1001','1'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　2 Ro','1001','2'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　3 Ro','1001','3'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　4 Ro','1001','4'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　5 Ro','1001','5'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　6 Ro','1001','6'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　7 Ro','1001','7'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　8 Ro','1001','8'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　9 Ro','1001','9'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　10 Ro','1001','10'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　11 Ro','1001','11'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　12 Ro','1001','12'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　13 Ro','1001','13'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　14 Ro','1001','14'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　15 Ro','1001','15'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　16 Ro','1001','16'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　17 Ro','1001','17'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　18 Ro','1001','18'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　19 Ro','1001','19'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　20 Ro','1001','20'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　21 Ro','1001','21'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　22 Ro','1001','22'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　23 Ro','1001','23'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　24 Ro','1001','24'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　25 Ro','1001','25'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　26 Ro','1001','26'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　27 Ro','1001','27'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　28 Ro','1001','28'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　29 Ro','1001','29'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　30 Ro','1001','30'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　31 Ro','1001','31'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　32 Ro','1001','32'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　33 Ro','1001','33'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　34 Ro','1001','34'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　35 Ro','1001','35'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　36 Ro','1001','36'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　37 Ro','1001','37'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　38 Ro','1001','38'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　39 Ro','1001','39'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　40 Ro','1001','40'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　41 Ro','1001','41'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　42 Ro','1001','42'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　43 Ro','1001','43'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　44 Ro','1001','44'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　45 Ro','1001','45'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　46 Ro','1001','46'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　47 Ro','1001','47'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　48 Ro','1001','48'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　49 Ro','1001','49'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　50 Ro','1001','50'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　51 Ro','1001','51'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　52 Ro','1001','52'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　53 Ro','1001','53'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　54 Ro','1001','54'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　55 Ro','1001','55'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　56 Ro','1001','56'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　57 Ro','1001','57'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　58 Ro','1001','58'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　59 Ro','1001','59'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　60 Ro','1001','60'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　61 Ro','1001','61'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　62 Ro','1001','62'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　63 Ro','1001','63'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　64 Ro','1001','64'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　65 Ro','1001','65'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　66 Ro','1001','66'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　67 Ro','1001','67'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　68 Ro','1001','68'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　69 Ro','1001','69'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　70 Ro','1001','70'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　71 Ro','1001','71'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　72 Ro','1001','72'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　73 Ro','1001','73'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　74 Ro','1001','74'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　75 Ro','1001','75'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　76 Ro','1001','76'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　77 Ro','1001','77'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　78 Ro','1001','78'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　79 Ro','1001','79'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　80 Ro','1001','80'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　81 Ro','1001','81'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　82 Ro','1001','82'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　83 Ro','1001','83'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　84 Ro','1001','84'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　85 Ro','1001','85'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　86 Ro','1001','86'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　87 Ro','1001','87'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　88 Ro','1001','88'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　89 Ro','1001','89'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　90 Ro','1001','90'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　91 Ro','1001','91'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　92 Ro','1001','92'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　93 Ro','1001','93'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　94 Ro','1001','94'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　95 Ro','1001','95'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　96 Ro','1001','96'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　97 Ro','1001','97'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　98 Ro','1001','98'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　99 Ro','1001','99'); INSERT INTO USER_MASTER (USER_NAME, DEPT_NO, USER_ID) VALUES ('Hayashi　100 Ro','1001','100');   2) Add Processor Add an ExecuteSQL Processor : With the following input parameters:   select * from user_master   Create the DBCPConnectionPool Controller Service:    jdbc:oracle:thin:@zzeng-*******.com:1521:svcname oracle.jdbc.driver.OracleDriver /opt/nifi/jdbc/ojdbc8.jar   Be careful about the "svcname" above, you must input the correct Oracle service name. Download JDBC Driver: To get the ojdbc8.jar, go to https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html   wget https://download.oracle.com/otn-pub/otn_software/jdbc/219/ojdbc8-full.tar.gz tar xvf ojdbc8-full.tar.gz sudo mkdir -p /opt/nifi/jdbc/ sudo chown nifi:nifi /opt/nifi/jdbc/ sudo chmod 755 /opt/nifi/jdbc/ cd /opt/nifi/jdbc/ sudo cp /home/centos/oracle-connect/ojdbc8.jar .   3) Run Run once:   Check Provenance   4) Write to HDFS Hadoop Configuration Resources:   /etc/hadoop/conf/hdfs-site.xml,/etc/hadoop/conf/core-site.xml   Directory:   /tmp/nifi/   Check result:   [centos@cdp conf]$ hdfs dfs -ls /tmp/nifi/ Found 2 items -rw-r--r-- 1 nifi supergroup 5268 2023-05-10 04:52 /tmp/nifi/78338e0b-27b4-4b44-b406-c5b1cada12eb -rw-r--r-- 1 nifi supergroup 5268 2023-05-10 05:16 /tmp/nifi/ca5756c4-0e9e-480c-abf5-050b731493fb [centos@cdp conf]$   5) Write to Hive External Table   １）Get data from Oracle（Avro Format output） ExecuteSQL Processor ２）Convert from Avro to Parquet Use  ConvertAvroToParquet Processor ３）Write Parquet data to the external Hive Table PuTHDFS Prepare Hive Table:   CREATE EXTERNAL TABLE DEMO.USER_MASTER ( USER_ID STRING, DEPT_NO STRING, USER_NAME STRING, CREATED_ON STRING , MODIFIED_ON STRING ) STORED AS PARQUET LOCATION '/tmp/data/parquet';     ... View more

To install Apache NiFi on Macbook, do the following:  Download the Zip file from Apache nifi Downloads. Ensure that you download the latest version zip file Apache NiFi Binary 1.19.1 [OpenPGP] [SHA-256] [SHA-512].  Download it to MBP, and then unzip it: chmod +x bin/nifi.sh Run the following command for User Generate: bin/nifi.sh set-single-user-credentials zzeng <my long password> Run the following command to Start: bin/nifi.sh start bin/nifi.sh status netstat -an | grep LIST Check the  8443 port is LISTEN: tcp4 0 0 127.0.0.1.8443 *.* LISTEN Login from https://localhost:8443/nifi/ ... View more

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'loginIdentityProvider': FactoryBean threw exception on object creation; nested exception is java.lang.Exception: The specified login identity provider 'kerberos-provider' could not be found. CloudBreak 2.7 built HDF 3.1 has error: 'kerberos-provider' could not be found. The reason is HDF 3.1's blueprint has problem: nifi.security.user.login.identity.provider= is set to kerberos-provider but that provider doesn't exist in login-identity-providers.xml, or is commented out ... View more

Got error [User home directory not found] when creating WorkFlow Manager view in Ambari Resolve: sudo -u hdfs hdfs dfs -mkdir /user/admin sudo -u hdfs hdfs dfs -chown admin /user/admin Resule: ... View more

In Zeppelin 0.7.0 Document, it is said that library should use 0.13. Reference : How to execute HIVE LLAP queries from Zeppelin in HDP 2.6.1 Hive Interpreter for Apache Zeppelin However it is too old. For LLAP, it need to be newer one. Properties : hive2.driver = org.apache.hive.jdbc.HiveDriver hive2.url = < can be obtained from ambari. check HiveServer2 Interactive JDBC URL > eg. hive2.url = jdbc:hive2://zzeng-hdp-3.example.com:2181,zzeng-hdp-1.example.com:2181,zzeng-hdp-2.example.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2-hive2 Dependencies : org.apache.hive:hive-jdbc:2.2.0 org.apache.hadoop:hadoop-common:2.6.0 With this setting, I can access Hive LLAP now: ... View more

The article describes how to install and configure Sparkmagic to run in HDP2.5 against Livy Server and Spark 1.6.2 Reference: Using Jupyter with Sparkmagic and Livy Server on HDP 2.5 in HCC 1) Install Jupyter http://jupyter.org/install ###### Init venv ## Just first time sudo yum install python-pip python-dev python-virtualenv -y mkdir ~/jupyter_env ## After 2nd virtualenv --system-site-packages ~/jupyter_env source ~/jupyter_env/bin/activate curl -O https://bootstrap.pypa.io/get-pip.py sudo python get-pip.py sudo easy_install -U pip python -m pip install --upgrade pip # for jupyter, it need gcc sudo yum install gcc -y sudo pip install jupyter notebook ipython http://zzeng-hdp-ambari:8888/tree?token=17dfdcb7525ff7470a637752450bbd586f607eddccc86a7f 2) Use Jupyter to connect Spark Livy https://community.hortonworks.com/articles/70501/using-jupyter-with-sparkmagic-and-livy-server-on-h.html # Failed building wheel for pykerberos sudo yum install krb5-devel -y sudo -H pip install sparkmagic sudo pip install hdijupyterutils sudo pip install autovizwidget sudo pip install sparkmagic pip show sparkmagic pip show autovizwidget cd /usr/lib/python2.7/site-packages jupyter-kernelspec install --user sparkmagic/kernels/sparkkernel jupyter-kernelspec install --user sparkmagic/kernels/pysparkkernel sudo -H jupyter nbextension enable --py --sys-prefix widgetsnbextension 3) Start Notebook cd ~/ jupyter notebook --ip=0.0.0.0 4) Connect from Jupyter to remote Spark cluster Readme: https://github.com/jupyter-incubator/sparkmagic In[ ]: %load_ext sparkmagic.magics In[ ]: %manage_spark ... View more