Recursive HDFS

That's strange but I was unable to find any solution to download the HDFS tree. The project I developed is covering the gap.

https://github.com/stanislawbartkowski/webhdfsdirectory

The only dependency is Python3 and the requests package. The HDFS tree is downloaded using HDFS Rest/API or Web/HDFS. It does not support Kerberos authentication and wasn't tested on a secured/encrypted connection.

The solution contains a simple hdfs package doing the main stuff and Python and bash wrappers. It also allows applying a RegularExpession directory selector to and "dry-run" option to test it without downloading anything. It was tested in a real production environment.

Java, Parquet and DB2 Warehouse in Cloud

I expanded my tool for moving data from Parquet file into JDBC database (blog). During my test, loading 6 million rows from 200 MB Parquet file into DB2 Warehouse took around 30-40 minutes. Not so bad for small data volume but not feasible while dealing with terabytes of data.

In DB2 Warehouse on Cloud, there is a very effective method of data loading from local (local to the DB2 Warehouse) or Amazon S3 bucket. It takes around 30-40 seconds to load 1.2 GB text file containing 6 million lines. 

This load option is available from DB2 Warehouse Console or through DB2 Warehouse on Cloud  REST/API.

So the idea to boost up the loading performance is to use AWS S3 bucket mounted in the local file system, deploy the delimited text files extracted from the Parquet file there, and launch DB2 Warehouse REST/API to load data from AWS S3 bucket. 

In the nutshell, it includes the following steps.

• Mount AWS S3 bucket in the local file system. "Local" - meaning the host where the application is running. RedHat/Centos
• The application is reading the input Parquet file and producing several delimited text file in the AWS S3 mounted directory. For instance:  /mnt/s3/sbtest/out1/export-0, /mnt/s3/sbtest/out1/export-1, /mnt/s3/sbtest/out1/export-2, /mnt/s3/sbtest/out1/export-3
• When the delimited text file is ready, the application is launching DB2 Warehouse on Cloud REST/API and loads data from AWS S3 bucket using file names: /sbtest/out1/export-0, /sbtest/out1/export-1,  /sbtest/out1/export-2, /sbtest/out1/export-3
• After data is loaded, the application can produce the next chunk of data and continue.

The loading time was reduced to 1 minute.

The current version of the application is available here.

Several remarks:

• The load commands are running in parallel. In my test environment, the optimal level of parallelism was 4. Beyond this number,  the DB2 Warehouse is reporting loading errors - the problem requires further investigation.
• Only single LOAD command can be executed against a single table. So every loading task is loading data into a private table (CREATE TABLE TABLE-1 LIKE TABLE) and later inserts data into the main table (INSERT INTO TABLE SELECT * FROM TABLE-1). The INSERT command is running very fast, it takes around 10 sec to INSERT 100 MB of data.

Mail server in a single docker

 I created a simple mail server, SMPT and IMAPS, in a single docker/podman container. The mail services engines are Postfix (SMP) and Dovecot (IMAPS). The solution is described here. I also added guidelines on how to test and configure several mail clients: Evolution and mutt.

The storage is ephemeral, not recommended for any production environment but ideal for testing, easy to create and easy to dismantle. 

I also added a sample yaml configuration file and remarks on how to deploy the container to OpenShift/Kubernetes cluster.

My own Hadoop/HDP benchmark

 Introduction

I spent some time trying to come to terms with HiBench but finally, I gave up. The project seemed to be abandoned, there was a lot of problems to run it in a secure (kerberized) environment and adjust it to new versions of Hive and Spark. Also, it is the project with a long history and there are a lot of layers not consistent with each other

So I ended up with creating my own version of HiBench benchmark. Only code migrated from HiBench is Spark/Scala and Jave source code upgraded to new versions of dependencies and more consistent parameter handling.

Features

• Dedicated to HDP 3.1.5. Standalone services are not supported.  
• Enabled for Kerberos
• Hive 3.0
• Spark 2.x
• Simple to run and expand, minimal configuration

Features not supported

• Streaming, pending
• Nutch indexing, not under development any longer
• Flink, Gearpump, not part of HDP stack

Configuration 

    All details are here.

SSL for masses

Motivation

 I expanded my tool for enabling wired encryption in the HDP cluster.

https://github.com/stanislawbartkowski/hdpwiredencryption

Previously, only self-signed certificates were supported. I added automation for CA-signed certificates. Important: it works only if CA-signed certificate package follows the supported format.

There are two paths possible: self-signed certificates and CA-signed certificates.

Self-signed certificates

1. ./run.sh 0 Creates self-signed certificate and truststores for every node.
2. ./run.sh.1 Creates and distributes all-client truststore.
3. ./run.sh 2 Secure keystores and truststores. Apply owner and Linux permissions.

CA-signed certificates

1. ./run.sh 3 Creates self-signed certificates and CSR (Certificate Signing Request) for every node
2. Manual step. Send all CSR to CA centre for signing. The CA-signed certificates  should be stored in a designed format.
3. ./run.sh 4 CA-signed certificates are imported into corresponding keystore and replacing the self-signed certificates. Truststores are created.
4. ./run.sh 1 Creates and distributes all-client trustore.
5. ./run.sh 2 Secure keystores and trustores.

Bonus

https://github.com/stanislawbartkowski/hdpwiredencryption/wiki

There is a number of pages containing practical steps on how to enable SSL for HDP components. It is based on documentation but more practical based on experience. 

For instance:

HDFS Ranger Plugin for SSL

NiFi service for SSL

HBase, Phoenix and CsvBulkLoadTool

 I'm running MyBench in a new environment and it fails while loading data into Phoenix table using CsvBulkLoadTool utility.

WARN tool.LoadIncrementalHFiles: Attempt to bulk load region containing  into table BENCH.USERVISITS with files [family:0 path:hdfs://bidev/tmp/386640ec-d49e-4760-8257-05858a409321/BENCH.USERVISITS/0/b467b5560eee4d61a42d4c9e6a78eb7e] failed.  This is recoverable and they will be retried.

INFO tool.LoadIncrementalHFiles: Split occurred while grouping HFiles, retry attempt 100 with 1 files remaining to group or split

ERROR tool.LoadIncrementalHFiles: -------------------------------------------------

Bulk load aborted with some files not yet loaded:

After closer examination, I discovered that the error takes place while moving/renaming input file into HBase staging directory /apps/hbase/data/staging. In this cluster, the HBase data is encrypted and moving data between encrypted and normal zone is not possible.

java.io.IOException: Failed to move HFile: hdfs://bidev/apps/hbase/data/staging/ambari-qa__BENCH.USERVISITS__dbb5qdfppq1diggr0dmdbcb1ji74ol4b9jn9ee2dgp1ttn9n5i6llfih7101fi1d/0/3a7f2d612c034253ad375ae002cc6ade to hdfs://bidev/tmp/fc43e454-00b3-4db0-8bdd-8b475885ab49/BENCH.USERVISITS/0/3a7f2d612c034253ad375ae002cc6ade

at org.apache.hadoop.hbase.regionserver.SecureBulkLoadManager$SecureBulkLoadListener.failedBulkLoad(SecureBulkLoadManager.java:423)

at org.apache.hadoop.hbase.regionserver.HRegion.bulkLoadHFiles(HRegion.java:6035)

at org.apache.hadoop.hbase.regionserver.SecureBulkLoadManager$3.run(SecureBulkLoadManager.java:284)

The source code can be found here.

if (!FSUtils.isSameHdfs(conf, srcFs, fs)) {
	LOG.debug("Bulk-load file " + srcPath + " is on different filesystem than " +
	"the destination filesystem. Copying file over to destination staging dir.");
	FileUtil.copy(srcFs, p, fs, stageP, false, conf);
	} else if (copyFile) {
	LOG.debug("Bulk-load file " + srcPath + " is copied to destination staging dir.");
	FileUtil.copy(srcFs, p, fs, stageP, false, conf);
	} else {
	LOG.debug("Moving " + p + " to " + stageP);
	FileStatus origFileStatus = fs.getFileStatus(p);
	origPermissions.put(srcPath, origFileStatus.getPermission());
	if(!fs.rename(p, stageP)) {
	throw new IOException("Failed to move HFile: " + p + " to " + stageP);
	}
	}

When data is moved between different file system, the copying is enforced but unfortunately, data movement between encrypted and decrypted zone is not covered here.

Another option is to make use of "copyFile" parameter which enforces copying. After analyzing the control flow I discovered that there exists "hbase-site.xml" parameter  always.copy.files which seems to be the solution to the problem. But after applying this parameter, nothing has changed. 

Further examination with a little help of remote debugging unearthed a sad truth. CsvBulkLoadTool is passing the control to LoadIncrementalHFiles.java and "doBulkLoad" function.

public Map<LoadQueueItem, ByteBuffer> doBulkLoad(Path hfofDir, final Admin admin, Table table,  RegionLocator regionLocator) throws TableNotFoundException, IOException {

      return doBulkLoad(hfofDir, admin, table, regionLocator, false, false);

  }

Unfortunately, the "copyFiles" parameter is hardcoded as "false" although there is a sound and ready to use "isAlwaysCopyFiles()" function utilizing "hbase-site.xml" config file.

The only solution is manual fix and recreating the package from source files. But it does not go easy because one has to leverage different and outdated versions of HBase and Phoenix to create "Phoenix client" package matching HDP 3.1.

So two days spent without a solution.

My TPC/DS, new features

Introduction

I uploaded a new version of mytpcds, a wrapper around TPC/DS benchmark allowing easy and quick roll-out of TPC/DS test against leading RDMS including Hadoop SQL engines. Just deploy, configure using a template provided and run. In the new version, the implementation of Query Validation Test is added.

Query Validation Test

Query Validation Test verifies the accuracy of SQL engine. RDBMS is to run a sequence of SQL statements, called Validation Queries, on Qualification database and compare the result data set against the expected data set. During Validation Test, the queries should come back with the same result. The Validation Queries are standard TPC/DS queries templates having where query parameters are substituted by predefined constants.

The substitution values for Validation Queries are defined in "TPC-DS Specification" manual, chapter "Appendix B: Business Questions". It is a mundane and error-prone task to prepare Validation Queries manually in 99 queries templates and I wanted also to avoid having two different versions of TPC/DS queries. So I decided to make the process automatic. Firstly I extracted all parameters and substitution values into separate configuration files. The name convention is <n>.par. The <n> maps to appropriate TPC/DS query. For instance, 1.par contains substitution values for query1.tpl.

YEAR=2000

STATE=TN

AGG_FIELD=SR_RETURN_AMT

The run.sh launcher contains a separate task: ./tpc.sh queryqualification. This task replaces all parameters placeholder with the corresponding validation values and put them in <TPC/DS root dir>/work/{dbtype}queries directory ready to be picked up by other run.sh tasks.

TPC/DS package comes with expected data sets for Query Validation. It is included in <TPC/DS root>/answer_sets. Unfortunately, the format of answer sets is not consistent which makes impossible the automated verification. So I prepared my own version of answer sets using DB2 output. Unfortunately, it does not comply with output from other RDBMS including Hive, so it is still a pending task which output is invalid.

QueryRunner

Query Validation Test requires comparing the current result sets against a reference result set. Unfortunately, the output from different RDBS using the command line client varies significantly which make automated comparison impossible. So I decided to prepare my own Java QueryRunner using JDBC and have the full control of how the result is produced. The target jar is produced by mvn package command. The only prerequisite for every database is JDBC driver jar. So far, I tested the QueryRunner for: NPS/Netezza, DB2, IBM BigSql, SqlServer and Hadoop Hive.

QueryRunner, Hive and Kerberos

Because life is never an easy road free of stones, the real challenge was to execute QueryRunner for Hadoop/Hive in Kerberized environment. The parameters regarding Kerberos cannot be included in URL connection string. Before executing DriverManager.getConnection(url, user, password);  the client should authenticate in Hadoop cluster using Hadoop related libraries. Of course, I wanted to avoid keeping two versions of QueryRunner and have the development consistent. So I developed a separate package HadoopAuth and in Hadoop/Hive environment, the Hadoop Kerberos authentication is done using HadoopAuth package but through Java reflection feature. This way I was able to keep QueryRunner clean. How to configure QueryRunner for Hadoop/Hive is described here.

Next steps

• Further analysis of reference QueryValidation answer tests.
• Add Microsoft SqlServer to RDBMS supported by myTPC-DS.
• Enable QueryRunner for all RDBMS supported.

Kerberos and Phoenix

Phoenix is a SQL solution on the top of HBase. There are two methods connecting to Phoenix, using full JDBC driver and thin JDBC driver. The latter requires additional server component, Phoenix Query Server.
I created the article about how to connect to Phoenix using both methods in kerberized environment. I also created a simple IntelliJ IDEA project demonstrating a connection to Phoenix from  Java program. Both JDBC drivers are supported.
I also added information on how to connect to Phoenix from Zeppelin notebook.
The article and sample project are available here.

AMS (Ambari Metrics System) is powered by standalone HBase and Phoenix server. Using a command line, one can get access to AMS Phoenix and query the metrics without going to UI. I also added information on how to accomplish it.
I was successful only by launching the command tool from the node where AMS is installed. I will work how to access it remotely.

MyTPC-DS,

An enhancement to my implementation of TPC-DS test. BigSQL test can be conducted by jsqsh utility, the dependency on DB2 client software is removed. Jsqsh is the part of BigSQL package and no additional dependency software is required now. How to use jsqsh for MyTPC-DS is described here.

I also added some additional results of the TPC-DS test conducted on HDP 3.1. The result is presented in the shape of Bar Plot to make them easier to understand. The graphic is created with the help of matplotlib. The Python source code is uploaded here. The input tables are taken directly from GitHub Wiki page. The pick up the correct table, a simple HTML tag is added to the page before every table.

The tests were executed using 100 GB data set and cannot be the basis of any far-reaching conclusion. But one apparent difference between HDP 2.6 and 3.1 is the significant performance improvement of Hive, from Hive 2.1 to Hive 3.1. Now the performance of Hive is almost as effective as the performance of BigSQL and ahead of SparkSQL. Also, the query coverage of Hive is much more comprehensive, from 50% (Hive 2.1) to almost 100% (Hive 3.1).

Useful links

• MyTPC-DS execution framework, link
• TPC-DS results using Bar Plot graphics, link
• TPC-DS results analytics tables, link 
• Python source code to compile the test result and prepare GitHub Wiki table, link
• Python source code to prepare graphics using matplotlib package, link
• Run BigSQL TPC-DS test using jsqsh tool, link

Hortonworks, Yarn Timeline server v.2

Problem
I spent several sleepless nights trying to resolve an issue with MapReduce jobs in HDP 3.1. While running MapReduce Service Check job, in the log file an error was reported although the job passed in green. The same message came up in others MapReduce jobs.
ERROR [pool-10-thread-1] org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl: Response from the timeline server is not successful, HTTP error code: 500, Server response:
{"exception":"WebApplicationException","message":"org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException: Failed 252 actions: IOException: 252 times, servers with issues: null","javaClassName":"javax.ws.rs.WebApplicationException"}
[Job ATS Event Dispatcher] org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler: Failed to process Event JOB_FINISHED for the job : job_1572998114693_0003 org.apache.hadoop.yarn.exceptions.YarnException: Failed while publishing entity
at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl$TimelineEntityDispatcher.dispatchEntities(TimelineV2ClientImpl.java:548)
at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl.putEntities(TimelineV2ClientImpl.java:149)
at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.processEventForNewTimelineService(JobHistoryEventHandler.java:1405)
at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.handleTimelineEvent(JobHistoryEventHandler.java:742)
at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.access$1200(JobHistoryEventHandler.java:93)
at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler$ForwardingEventHandler.hand
In the yarn log file (/var/log/hadoop-yarn/yarn) more detailed message could be found. Caused by: java.io.IOException: org.apache.zookeeper.KeeperException$NoNodeException: KeeperErrorCode = NoNode for /atsv2-hbase-secure/meta-region-server
at org.apache.hadoop.hbase.client.ConnectionImplementation.get(ConnectionImplementation.java:2002)
at org.apache.hadoop.hbase.client.ConnectionImplementation.locateMeta(ConnectionImplementation.java:762)
at org.apache.hadoop.hbase.client.ConnectionImplementation.locateRegion(ConnectionImplementation.java:729)
at org.apache.hadoop.hbase.client.ConnectionImplementation.relocateRegion(ConnectionImplementation.java:707)
at org.apache.hadoop.hbase.client.ConnectionImplementation.locateRegionInMeta(ConnectionImplementation.java:911)
at org.apache.hadoop.hbase.client.ConnectionImplementation.locateRegion(ConnectionImplementation.java:732)
at org.apache.hadoop.hbase.client.RpcRetryingCallerWithReadReplicas.getRegionLocations(RpcRetryingCallerWithReadReplicas.java:325)
... 17 more

The problem is related to Yarn Timeline server v.2. The service is using HBase as backup storage and it looked that this embedded HBase server was not started or not working properly. HBase is keeping its runtime configuration data like active HBase Region Serves in Zookeeper znodes.
Also, the znode /atsv2-hbase-secure did not exist.
zkCli.sh
ls /

[hive, cluster, brokers, hbase-unsecure, kafka-acl, kafka-acl-changes, admin, isr_change_notification, atsv2-hbase-secure, log_dir_event_notification, kafka-acl-extended, rmstore, hbase-secure, kafka-acl-extended-changes, consumers, latest_producer_id_block, registry, controller, zookeeper, delegation_token, hiveserver2, controller_epoch, hiveserver2-leader, atsv2-hbase-unsecure, ambari-metrics-cluster, config, ams-hbase-unsecure, ams-hbase-secure]

Embedded HBase service is running as Yarn service named ats-hbase. I started to examine the local container the ats-hbase is running in. It is the directory in the shape of:
/data/hadoop/yarn/local/usercache/yarn-ats/appcache/application_1573000895404_0001/container_e82_1573000895404_0001_01_000003. And it turned out, that the local hbase-site.xml configuration file defines the znode as atsv2-hbase-unsecure
   <property>
      <name>zookeeper.znode.parent</name>
      <value>/atsv2-hbase-unsecure</value>
   </property>

So the embedded HBase was storing its configuration in improper Zookeper znode. But how it could happen when the Yarn configuration parameter defines the znode as atsv2-hbase-secure and where the other services expect to find the embedded HBase runtime data. Also, the HDFS /user/yarn-ats/3.1.0.0-78/hbase-site.xml file, it is the source file used by Application Master to create a local container directory, contains a valid znode value.
Solution
After closer examination of the container log files, I discover the following entry:
INFO provider.ProviderUtils: Added file for localization: conf/hadoop-metrics2-hbase.properties -> /user/yarn-ats/.yarn/services/ats-hbase/components/1.0.0/master/master-0/hadoop-metrics2-hbase.properties
INFO provider.ProviderUtils: Added file for localization: conf/hbase-policy.xml -> /user/yarn-ats/.yarn/services/ats-hbase/components/1.0.0/master/master-0/hbase-policy.xml
INFO provider.ProviderUtils: Added file for localization: conf/core-site.xml -> /user/yarn-ats/.yarn/services/ats-hbase/components/1.0.0/master/master-0/core-site.xml INFO provider.ProviderUtils: Added file for localization: conf/hbase-site.xml -> /user/yarn-ats/.yarn/services/ats-hbase/components/1.0.0/master/master-0/hbase-site.xml
INFO provider.ProviderUtils: Added file for localization: conf/log4j.properties -> /user/yarn-ats/.yarn/services/ats-hbase/components/1.0.0/master/master-0/log4j.properties
So it turned out, that Application/Serrvice Master is using not /user/yarn-ats/3.1.0.0-78/ HDFS path but another hidden /user/yarn-ats/.yarn/ directory to create the local container.
Finally, the solution was quite simple:

• kill the ats-hbase Yarn application
• stop the Yarn service
• remove  /user/yarn-ats/.yarn HDFS directory
• start the Yarn service

HDP, Kafka, LEADER_NOT_AVAILABLE

Problem
After HDP cluster kerberization, the Kafka does not work even though Kafka Healthcheck passes green. Any execution of kafka-console-producer.sh ends up with the error message: /usr/hdp/3.1.0.0-78/kafka/bin/kafka-console-producer.sh --broker-list kafka-host:6667 --producer-property security.protocol=SASL_PLAINTEXT --topic xxx

WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 1 : {xxxx=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient)
WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 2 : {xxxx=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient)
WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 3 : {xxxx=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient)
kafka-topics.sh reports that the leader is not assigned to the partition.
/usr/hdp/3.1.0.0-78/kafka/bin/kafka-topics.sh -zookeeper zookeeper-host:2181 --describe xxx --unavailable-partitions

Topic: ambari_kafka_service_check Partition: 0 Leader: 1001 Replicas: 1001 Isr: 1001
Topic: identity Partition: 0 Leader: none Replicas: 1002 Isr:
Topic: xxx Partition: 0 Leader: none Replicas: 1002 Isr:
Topic: xxxx Partition: 0 Leader: none Replicas: 1002 Isr:

There is nothing special in Kafka /var/log/kafka/server.log file. Only /var/log/kafka/controller.log suggests that something went wrong: cat controller.log

INFO [ControllerEventThread controllerId=1002] Starting (kafka.controller.ControllerEventManager$ControllerEventThread)
ERROR [ControllerEventThread controllerId=1002] Error processing event Startup (kafka.controller.ControllerEventManager$ControllerEventThread)
java.lang.NullPointerException
at com.fasterxml.jackson.core.JsonFactory.createParser(JsonFactory.java:857)
at com.fasterxml.jackson.databind.ObjectMapper.readTree(ObjectMapper.java:2571)
at kafka.utils.Json$.parseBytes(Json.scala:62)
at kafka.zk.ControllerZNode$.decode(ZkData.scala:56)
at kafka.zk.KafkaZkClient.getControllerId(KafkaZkClient.scala:902)
at kafka.controller.KafkaController.kafka$controller$KafkaController$$elect(KafkaController.scala:1199)

Solution
Uncle Google brings back many entries related to LEADER_NOT_AVAILABLE error but none of them led to the solution. Finally, I found this entry.
So the healing is very simple.

• Stop Kafka
• Run zkCli.sh Zookeeper command line
• Remove /controller znode, rmr /controller
• Start Kafka again

The evil spell is defeated.

BigSQL 6.0 and HDP 3.1.4

Problem
There is a problem with BigSQL 6.0 installed on the top HDP 3.1.4 or after upgrade from HDP 3.1. Look at this product support web page. Installation is successful but there are plenty of entries in BigSQL diagnostic blog
java.lang.NoSuchMethodError: com/google/common/base/Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V (loaded from file:/usr/ibmpacks/bigsql/6.0.0.0/bigsql/lib/java/guava-14.0.1.jar by sun.misc.Launcher$AppClassLoader@28528006) called from class org.apache.hadoop.conf.Configuration (loaded from file:/usr/hdp/3.1.4.0-315/hadoop/hadoop-common-3.1.1.3.1.4.0-315.jar by sun.misc.Launcher$AppClassLoader@28528006).
        at org.apache.hadoop.conf.Configuration.set(Configuration.java:1358)
        at org.apache.hadoop.conf.Configuration.set(Configuration.java:1339)
        at org.apache.hadoop.mapred.JobConf.setJar(JobConf.java:518)
        at org.apache.hadoop.mapred.JobConf.setJarByClass(JobConf.java:536)
Solution
The cause of the problem is the old guava jar file in the /usr/ibmpacks/bigsql/6.0.0.0/bigsql/lib/java. Replace the old guava with any new guava greater than 20. Or simply make a link to existing guava at the proper level. The fix should be applied on all BigSQL nodes, Head and Worker nodes. The BigSQL should be restarted to make the change to take effect.
rm /usr/ibmpacks/bigsql/6.0.0.0/bigsql/lib/java/guava-14.0.1.jar
cd /usr/ibmpacks/bigsql/6.0.0.0/bigsql/lib/java/
ln -s /usr/hdp/3.1.4.0-315/hadoop/lib/guava-28.0-jre.jar

BigSQL and HDP upgrade

Problem
I spent several sleepless nights trying to resolve the really nasty problem. It happened after upgrade from HDP 2.6.4 and BigSQL 5.0 to HDP 3.1 and BigSQL 6.0. Everything runs smoothly, even BigSQL Healthcheck was smiling. The only exception was "LOAD HADOOP" command which failed. BigSQL can run on the top of Hive tables but it is an alternative SQL engine, it is using HCatalog service to get access to Hive tables. In order to ingest data into Hive tables, it launches a separate MapReduce task to accomplish the task.
An example command:
db2 "begin execute immediate 'load hadoop using file url ''/tmp/data_1211057166.txt'' with source properties (''field.delimiter''=''|'', ''ignore.extra.fields''=''true'') into table testuser.smoke_hadoop2_2248299375'; end" Closer examination of MapReduce logs brought up a more detailed error message.
Service org.apache.hadoop.mapreduce.v2.app.MRAppMaster failed in state INITED; cause: org.apache.hadoop.yarn.exceptions.YarnRuntimeException: java.lang.NumberFormatException: For input string: "30s"
Good uncle Google suggests that it could be caused by old MapReduce engine against new configuration files. But how could it happen since all stuff related to HDP 2.6.4 and BigSQL 5.0.0. was meticulously annihilated?
What is more, another HDP 3.1/BigSQL 6.0 installation is executing LOAD HADOOP command without any interruption. Comparing all configuration data between both environments did not reveal any difference.
After a more closer examination, I discovered that related to LOAD HADOOP MapReduce job is empowered by HDP 2.6.4 environment including legacy jar files, pay attention to  2.6.4.0-91 parameter.
exec /bin/bash -c "$JAVA_HOME/bin/java -server -XX:NewRatio=8 -Djava.net.preferIPv4Stack=true -Dhdp.version=2.6.4.0-91 -Xmx545m 
Also, corresponding local cache seems to be populated by the old jar files.
ll /data/hadoop/yarn/local/filecache/14/mapreduce.tar.gz/hadoop/
drwxr-xr-x. 2 yarn hadoop 4096 Jan 4 2018 bin
drwxr-xr-x. 3 yarn hadoop 4096 Jan 4 2018 etc
drwxr-xr-x. 2 yarn hadoop 4096 Jan 4 2018 include
drwxr-xr-x. 3 yarn hadoop 4096 Jan 4 2018 lib
drwxr-xr-x. 2 yarn hadoop 4096 Jan 4 2018 libexec

-r-xr-xr-x. 1 yarn hadoop 87303 Jan 4 2018 LICENSE.txt
-r-xr-xr-x. 1 yarn hadoop 15753 Jan 4 2018 NOTICE.txt
-r-xr-xr-x. 1 yarn hadoop 1366 Jan 4 2018 README.txt
drwxr-xr-x. 2 yarn hadoop 4096 Jan 4 2018 sbin
drwxr-xr-x. 4 yarn hadoop 4096 Jan 4 2018 share

But how it could be possible when all remnants of old HDP were wiped out and no sign of any reference do 2.6.4 including running the grep command against any directory suspected of retaining this nefarious mark.
grep 2\.6\.4 /etc/hadoop -R
Solution
The nutcracker turned out to be BigSQL/DB2 dbset command.
db2set
DB2_BIGSQL_JVM_STARTARGS=-Dhdp.version=3.1.0.0-78 -Dlog4j.configuration=file:///usr/ibmpacks/bigsql/6.0.0.0/bigsql/conf/log4j.properties -Dbigsql.logid.prefix=BSL-${DB2NODE}
DB2_DEFERRED_PREPARE_SEMANTICS=YES
DB2_ATS_ENABLE=YES
DB2_COMPATIBILITY_VECTOR=40B
DB2RSHTIMEOUT=60
DB2RSHCMD=/usr/bin/ssh
DB2FODC=CORESHM=OFF
DB2_JVM_STARTARGS=-Xnocompressedrefs -Dhdp.version=2.6.4.0-91 -Dlog4j.configuration=file:///usr/ibmpacks/bigsql/5.0.4.0/bigsql/conf/log4j.properties -Dbigsql.logid.prefix=BSL-${DB2NODE}
DB2_EXTENDED_OPTIMIZATION=BI_INFER_CC ON
DB2COMM=TCPIP
DB2AUTOSTART=NO

Obviously, the DB2_JVM_STARTARGS took precedence over DB2_BIGSQL_JVM_STARTARGS and it was the reason why the old MapReduce framework was resurrected. The legacy jar files were downloaded from HFDFS /hdp/apps directory.
hdfs dfs -ls /hdp/apps
Found 2 items
drwxr-xr-x   - hdfs hdfs          0 2019-10-07 22:09 /hdp/apps/2.6.4.0-91
drwxr-xr-x   - hdfs hdfs          0 2019-10-12 00:16 /hdp/apps/3.1.0.0-78

The problem was sorted by a single command unsetting malicious DB2_JVM_STARTARGS variable and restarting BigSQL to take it into effect.
db2set DB2_JVM_STARTARGS=
I also removed /hdp/apps/2.6.4.0-91 HDFS directory to make sure that the vampire is ultimately killed.

How to obtain an active NameNode remotely

Problem
While using WebHDFS REST API interface, the client is dealing directly with the NameNode. In HA (high availability) environment, only one NameNode is active, the second is standby. If the standby NameNode is addressed, the request is denied. So the client should be aware of which NameNode is active and construct a valid URL.  But how to discover remotely the active NameNode automatically and avoid redirecting the client manually in case of failover?
Sounds strange but the there is no Ambari REST API to detect an active NameNode.
One obvious solution is to use WebHDFS Knox Gateway which, assuming configured properly, is propagating the query to the valid NameNode.
Solution
There are two convenient methods to discover the active NameNode outside Knox Gateway. One is to use JMX query and the second is to use hdfs haadmin.
The solution is described here in more details. I also added a convenient bash script to extract the active NameNode using both methods: JMX Query and hdfs haadmin. The script can be easily customized. If hdfs haadmin method is used, the script can be executed inside the cluster only so the remote shell call should be implemented.

The 'krb5-conf' configuration is not available

HDP 3.1
A nasty message as visible above suddenly popped up out of the blue. Every configuration change, stopping or starting the service was blocked because of that. The message was related to the Kerberization but "Disable Kerberos" option was also under the spell. It seemed that the only option was to plough under everything and build up the cluster from the bare ground.
The problem is described here but no solution is proposed.
Solution
The solution was quite simple. Remove the "Kerberos" marker from the cluster by modifying the Ambari database. In the case of Postgresql database execute the command:

update clusters set security_type='NONE'

After that magic, the "Enable Kerberos" button is active and after performing the "Kerberization" the cluster is happy and healthy again.

HDP 3.1, HBase REST API, security gap

Problem
I found a nasty problem with HDP 3.1 which cost me several sleepless nights. There is a security gap in HBase REST API. The HBase REST API service does not impersonate users and all HBase commands are executed as hbase user. The same behaviour is passed to Knox HBase. It means that any user having access to HBase REST API or Knox Gateway HBase is authorized to do any action bypassing any security settings in Ranger or HBase service directly.
Solution
The only solution I found was to compile the current version of HBase downloaded from GitHub and replace the legacy hbase-rest jar with the new one.

Clone GitHub repository and build the packages
git clone https://github.com/apache/hbase.git -b branch-2.0
cd hbase
mvn package -DskipTests

As root user
cd /usr/hdp/3.1.0.0-78/hbase/lib

Archive existing jar
mkdir arch
mv mv hbase-rest-2.0.2.3.1.0.0-78.jar arch/
unlink hbase-rest.jar

Replace with the new one
ln -s /home/hbase/hbase/hbase-rest/target/hbase-rest-2.0.6-SNAPSHOT.jar hbase-rest.jar

Restart HBase REST API server.

HDP 3.1, Wired Encryption

Introduction
https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.1.0/configuring-wire-encryption/content/wire_encryption.html
A Wired Encryption is not only adding the next layer of security, but also the next layer of complexity. Although everything is described in the HortonWorks documentation, it is not easy to extract the practical steps on how to set up the encryption, it took me some time to accomplish it. So I decided to recap my experience and publish several useful scripts and practical procedure.
https://github.com/stanislawbartkowski/hdpwiredencryption
What is described

• Self-signed certificates
• Enable SSL for WebHDFS, MapReduce, Tez, and YARN

Future plans

• Certificates signed by CA
• Other HDP services
• Application connection

Problem to solve

After enabling the encryption, BigSQL LOAD HADOOP command refused working. It is related to the TimeLine service secure connection. Will try to sort it.

My Wiki

Introduction
I started creating my GitHub Wiki containing some practical guidelines and steps related to HDP and  BigSQL. I do not want to duplicate or retell HortonWorks or IBM official documents and tutorial. Just my success stories and some practical advice to remember and avoid, like Sisyphus, to roll your boulder up that Kerberos hill again and again.
The list and the content are not closed and static, I'm constantly updating and enhancing it.
https://github.com/stanislawbartkowski/wikis
Below is the current content.
LDAP and Kerberos authentication for CentOS
https://github.com/stanislawbartkowski/wikis/wiki/Centos---Kerberos---LDAP
The guidelines for how to set up Kerberos and LDAP authentication for CentOS. It is a good practice to have host systems secured before installing HDP. Although described in many places, I had a hard time to put it together and end up in the loving arms of Kerberos. Particularly, secure LDAP connection and two-ways LDAP security required a lot of patience with gritted teeth.
Dockerized Kerberos
https://github.com/stanislawbartkowski/docker-kerberos
Docker is the buzzword these days. There are several Kerberos on Docker implementations circulating around but I decided to create my own I'm confident with.
HDP and GPFS
https://github.com/stanislawbartkowski/javahotel/tree/hdpgpf
GPFS is the alternative for HDFS. HDP running on the top GPFS has some advantages and disadvantages. Before going live, it is a good idea to practice using a local cluster. Here I'm presenting practical steps on how to set up HDP using GPFS as a data storage. Important: you need a valid IBM GPFS license to do that.
DB2 11 and aggregate UDF
https://github.com/stanislawbartkowski/javahotel/tree/db2aggr
Seems strange but until DB2 11 it was not possible to implement custom aggregate UDF in DB2. This feature was finally added in DB2 11 but I found it not well documented. So it took me some time to create even a simple aggregate UDF but in the end, I made it.
IBM BigSQL, monitoring and maintenance queries
https://github.com/stanislawbartkowski/wikis/wiki/BigSQL,-useful-commands
For some time, I was supporting the IBM client on issues related to IBM BigSQL. During my engagement, I created a notebook with a number of useful SQL queries related to different aspects of maintenance, performance, security etc for copying and pasting.
Dockerized IBM DB2
https://github.com/stanislawbartkowski/docker-db2
Yet another DB2 on Docker. Download the free release of DB2 or, if you are lucky enough, your licensed edition and be happy to get up DB2 by the tap of your finger.
Enable CentOS for Active Directory
https://github.com/stanislawbartkowski/wikis/wiki/CentOS---Active-Directory
Enable CentOS or RedHat for Active Directory authentication is easy comparing to MIT Kerberos/OpenLDAP but still, there are some hooks and nooks to know.
Monitoring tool for IBM BigSQL
https://github.com/stanislawbartkowski/bigsqlmoni
IBM BigSQL/DB2 contains a huge variety of monitoring queries but in most case what is important is not the value but delta. So I created a simple tool to store and provide deltas for some performance and monitoring indicators. But still looking for a way to make practical use of it.
Enable HDP for Active Directory/Kerberos/LDAP
https://github.com/stanislawbartkowski/wikis/wiki/HDP-2.6.5-3.1-and-Active-Directory
Practical steps on implementing Kerberos security in HDP. A simple test to make sure that security is in place.
Enable HDP services for Active Directory/Kerberos/LDAP
https://github.com/stanislawbartkowski/hdpactivedirectory
Wiki: https://github.com/stanislawbartkowski/hdpactivedirectory/wiki
Basic Kerberization enables Kerberos authentication for Hadoop services and makes HDFS secure. Next step is to enable a particular service for Kerberos authentication and LDAP authorization. It is highly recommended to activate Ranger security. The GitHub Wiki attached contains guidelines and practical steps to enable Hadoop services for AD/Kerberos. Every chapter contains also a basic test to make sure that security is enforced and has teeth.
HiBench for HDP
https://github.com/stanislawbartkowski/MyHiBench
HiBench is widely recognized as a leading benchmark tool for Hadoop services. Unfortunately, the development seems to be closed two years ago and I found it difficult to run it in HDP 3.1. Also, the tool seems not to be enabled for Kerberos security. After spending some time trying to find a workaround, I decided to create my own fork of HiBench.
This fork is dedicated to HDP only, the original HiBench can be used also against a standalone instalment of some Hadoop services. It required also redeveloping some Java and Scala code related particularly to Kafka and Spark Streaming.
Several Java/Scala tools to test Kerberos security
HDFS Java client https://github.com/stanislawbartkowski/KafkaSample
Kafka Java client https://github.com/stanislawbartkowski/KafkaSample
Scala Spark Streaming against Kafka https://github.com/stanislawbartkowski/SampleSparkStreaming
Several simple Java/Scala tools to test Kerberos connectivity. The tools come with source code to review. The tools are using as an additional test, next to batch or command line, for testing Kerberos security.

HDP, Ranger and plugins

Problem
I spent several sleepless nights trying to resolve a strange problem related to HDP (HortonWorks Data Platform), Ranger service and plugins.
After installing Ranger and enabling any plugin, an appropriate service entry should be created and visible in Ranger Admin UI. More details are here. In the beginning, a default policy is created which can be customized later according to needs.
But in my environment, the service entry was not created thus blocking any attempt to implement authorization policy. What is more, even disabling/enabling plugin, stopping/restarting the cluster does not make any change, I was unable to conjure the service entry. At some point, I even removed the Ranger, recreated the Ranger database and reinstalled the service again from scratch, but it did not help.
Solution
Finally, after carefully browsing through the log files, I found the solution. The culprit is the local directory /etc/ranger. There is a subdirectory reflecting the service entry in Ranger Admin UI.

ls /etc/ranger/MyCluster_hadoop/
cred.jceks
policycache

This directory contains a copy of ranger/service policy and is used as a recovery point in case of the database failure. It seems that after enabling the plugin if the service discovers this cache, the ranger/service policy is recreated but in this scenario, the Ranger Admin UI service entry is not restored. This cache is not removed after disabling the plugin and even after removing the whole Ranger service.
Unfortunately, it is not documented and badly implemented.
The solution is to switch off the plugin, manually remove the /etc/ranger/{service name} directory and switch on the plugin again. The service entry and default policy are recreated.
Keep in mind that the directory /etc/ranger/{service name} is located on the host where the appropriate service is installed, not the Ranger service host.

My HiBench

Problem
HiBench is regarded as a leading benchmark in the BigData world according to this webpage. But while trying to run it in my test HDP 2.6.5 or 3.1environment, 2.6.5 or 3.1,  I found it very frustrating, particularly when the cluster is secured by Kerberos.
Because the project seems to be abandoned two years ago, I started implementing manual patches to the code, it is described here. But at some point, I came to a dead end. Kafka 0.8 client does not support Kerberos, Kerberos support was added in Kafka 0.10 client, but Kafka 0.10 is not backward compatible and moving there required redeveloping substantial part of Kafka related code in Java and Scala.
Solution
So finally I decided to make my own fork of HiBench project and split completely from the trunk. The result is a new GitHub project here.

Main changes include:

• Kerberos support
• Kafka 2.0 implemented
• HDP 3.1/Apache Hadoop 3.1 support
• Hive 3.1 support, the standard HiBench was developed for Hive 0.17 and does not talk to 3.1

Features lost comparing to standard HiBench

• Only HDP is supported and tested, standalone Spark, Apache Hadoop, Kafka etc are not supported. 
• Only support for HDP 2.6.5 and HDP 3.1 is implemented. Support for an older version of HDP, Spark, Scala is abandoned.
• Support for Apache Flink and Gearpunmo is removed.

Tests

The project was tested in two HDP 3.1 and HDP 2.6.5 environments, tiny in local KVM cluster and tiny/small/large in a larger multi-host cluster.

Future plans

• Review Kafka streaming benchmark, while analyzing the code I found some mysteries out there
• The same for Dfsio benchmark test
• Test in wire encryption secured cluster