How to put RDBMS data to hive using nifi

rajkiranu · ‎06-28-2018

Hi I want to load mysql data to Hive. I used the following processor QueryDatabaseTable -> PutHiveStreaming In QueryDatabaseTable the database Connection Pooling service used is DBCPconnectionPool. In PuthiveStreaming the HiveMetastoreURI I given is thrift://myownIP:9083 and created a ORC table in hive CREATE TABLE employeeacid (empno int, ename string,job string, mgr int,hiredate string, sal int, comm int, deptno int) CLUSTERED BY(empno) INTO 3 BUCKETS STORED AS ORC TBLPROPERTIES ('transactional'='true'); while running the error I am getting is putHiveStreaming[id....] Error writing[org.apache.nifi.processors.hive.putHivestreaming...] to Hivestreaming transaction due to java.lang.illegalArgumentException:java.net.UnknownHostException:quickstart.cloudera:java.lang.illegalArgumentException: I refered this link "https://community.hortonworks.com/articles/87686/rdbms-to-hive-using-nifi-small-medium-tables.html" @Ryan Cicak Could any one please advise on this.

rajkiranu · ‎03-13-2018

@Guilherme Braccialli I have a log file in which i have last field as key value pair. for example: 2017-11-29 16:19:39,217 DEBUG [pool-4-thread-4] OutBound Msg From Engine -> |9=76|35=p|a=b|c=hg| 2017-11-29 16:20:29,217 DEBUG [pool-4-thread-4] OutBound Msg From Engine -> |3=6|35=w|a=b| how to analyse this? Can we use your custom serde for this? Because Regex serde is not supporting complex data types.

rajkiranu · ‎02-27-2018

I have the same situation in log file. My data set looks like this 2017-11-29 16:19:39,217 DEBUG Conn -> 8=FIXT.1.1|9=76|35=A 2017-11-29 16:19:43,618 DEBUG Conn -> 8=FIXT.1.1|9=52|35=0|49=1151|56=BSEEQ|34=2 How to handle this Regex serde. my create table query is: CREATE TABLE capital_log1 ( date STRING, filed2 STRING, filed3 STRING, filed4 STRING,filed7 map<bigint,string>) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "([^ ]*) ([^,]*)([^ ]*) (.*\->) (.*\|)", "output.format.string" = "%1$s %2$s %3$s %4$s %5$s" ) STORED AS TEXTFILE; It throws me an exception.

Online	Offline
Last Visited	‎09-14-2018 02:30 PM

Date Registered	‎02-27-2018 03:33 AM
Last Visited	‎09-14-2018 02:30 PM
Total Messages Posted	4

How to put RDBMS data to hive using nifi

Re: Need help creating a custom SerDe.

Re: Hive- escaping field delimiter in column value