Community Articles

Use Apache Spark to connect to SQL Server, extract a table from SQL Server, and load the extracted rows into a Hive table: You will need to download JDBC driver for SQL ServerDownload the driver from: https://www.microsoft.com/en-us/download/details.aspx?id=11774Run code: /usr/bin/spark-submit --driver-class-path /home/centos/sqljdbc_4.0/enu/sqljdbc4.jar --master yarn-client my_script.py import osfrom pyspark import SparkConf,SparkContextfrom pyspark.sql import HiveContextconf = (SparkConf() .setAppName("data_import") .set("spark.dynamicAllocation.enabled","true") .set("spark.shuffle.service.enabled","true"))sc = SparkContext(conf = conf)sqlctx = HiveContext(sc)df = sqlctx.load( source="jdbc",url="jdbc:sqlserver://ec2-54-244-44-6.us-west-2.compute.amazonaws.com:1433;database=sales;user=my_username;password=my_password",dbtable="orders")## this is how to write to an ORC filedf.write.format("orc").save("/tmp/orc_query_output")## this is how to write to a hive tabledf.write.mode('over

In certain Apache Hadoop use cases we want to get the checksum of files stored in HDFS. This is specifically useful when we are moving data from/to hdfs to verify the file was transferred correctly. Earlier there was no easy way to compare that but starting Apache Hadoop 3.1 we can compare the checksums of a file stored in hdfs and a file stored locally. HDFS-13056The default checksum algorithm for hdfs chunks is CRC32C. A client can override it by overriding dfs.checksum.type (can be either CRC32 or CRC32C). This is not a cryptographically strong checksum, however it can be used for quick comparison.When we run the checksum command (hdfs dfs -checksum) for a hdfs file it calculates MD5 of MD5 of checksums of individual chunks (each chunk is typically 512 bytes long). However this is not very useful for comparison with a local copy.ExampleFor example, the below command computes the checksum of the file hadoop-common-2.7.3.2.6.3.0-SNAPSHOT.jar stored in HDFS:hdfs dfs -checksum /tmp/had

Put log4j on HDFS path and then use HDFS path in workflow to override.Sample log4j file:## Licensed under the Apache License, Version 2.0 (the "License");# you may not use this file except in compliance with the License.# You may obtain a copy of the License at## http://www.apache.org/licenses/LICENSE-2.0## Unless required by applicable law or agreed to in writing, software# distributed under the License is distributed on an "AS IS" BASIS,# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.# See the License for the specific language governing permissions and# limitations under the License. See accompanying LICENSE file.## Define some default values that can be overridden by system propertieshadoop.root.logger=DEBUG,CLA# Define the root logger to the system property "hadoop.root.logger".log4j.rootLogger=${hadoop.root.logger}, EventCounter# Logging Thresholdlog4j.threshold=ALL## ContainerLog Appender##Default valuesyarn.app.container.log.dir=nullyarn.app.container

Part 1: https://community.hortonworks.com/articles/82964/getting-started-with-apache-ambari-workflow-design.html Part 2: https://community.hortonworks.com/articles/82967/apache-ambari-workflow-designer-view-for-apache-oo.html Part 3: https://community.hortonworks.com/articles/82988/apache-ambari-workflow-designer-view-for-apache-oo-1.html Part 4: https://community.hortonworks.com/articles/83051/apache-ambari-workflow-designer-view-for-apache-oo-2.htmlPart 6: https://community.hortonworks.com/articles/83787/apache-ambari-workflow-manager-view-for-apache-ooz-1.htmlPart 7: https://community.hortonworks.com/articles/84071/apache-ambari-workflow-manager-view-for-apache-ooz-2.htmlPart 8: https://community.hortonworks.com/articles/84394/apache-ambari-workflow-manager-view-for-apache-ooz-3.htmlPart 9: https://community.hortonworks.com/articles/85091/apache-ambari-workflow-manager-view-for-apache-ooz-4.htmlPart 10: https://community.hortonworks.com/articles/85354/apache-ambari-workflow-manager-

Objectives This tutorial is designed to walk you through the process of using Vagrant and Virtualbox to create a local instance of Cloudbreak 2.4.1. This approach allows you start your local Cloudbreak deployer instance when you want to spin up an HDP cluster in a cloud environment without incurring costs associated with hosting your Cloudbreak deployer instance itself on the cloud. This tutorial is an update to the original one located here: HCC Article. However this version of the tutorial includes more automation for installing Cloudbreak and is based on Cloudbreak 2.4.x instead of 1.14.x. Note: This tutorial has also been tested with Cloudbreak 2.7.0, 2.7.1, 2.72. and 2.8.0 TP.Prerequisites You should already have installed VirtualBox 5.x. Read more here: VirtualBox You should already have installed Vagrant 2.x. Read more here: Vagrant You should already have installed the vagrant-vbguest plugin. This plugin will keep the VirtualBox Guest Additions software current as you up

Short Description:How to run sample Oozie sqoop action to get data from Mysql table to HDFS.ArticleBelow are the steps to run sample sqoop action to get data from Mysql table on HDFS.Note - Please refer this to create sample Mysql table with dummy data..1. Configure job.propertiesExample:nameNode=hdfs://<namenode-host>:8020jobTracker=<rm-host>:8050queueName=defaultexamplesRoot=examplesoozie.use.system.libpath=trueoozie.wf.application.path=${nameNode}/user/${user.name}oozie.libpat=/user/root.2. Configure Workflow.xmlExample:<?xml version="1.0" encoding="UTF-8"?><!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

This article will show a step by step guide on how to connect to HBase in an a Kerberos enable cluster. If you need to setup a cluster that have Kerberos enabled, this is a good guid to follow.Clone Example ProjectThis article will be based on this project. Please run the following to clone the project.$ git clone https://github.com/jjmeyer0/hdp-test-examplesCreating KeytabBefore getting into the code, it is important to generate necessary files. If a key tab is not available follow the steps below to create one. In the example below, a key tab for the user jj and realm EXAMPLE.COM is created. The below commands should be run on one of the nodes in the cluster. $ kadmin.local$ addprinc jj@EXAMPLE.COM$ <CTRL-D>$ ktutil$ addent -password -p jj -k 1 -e RC4-HMAC$ wkt jj.keytab$ qPreparing User in HBaseThe user that was used above must be given correct permissions in HBase. To do so do the following:$ hbase shellhbase(main):001:0> grant 'jj', 'RW'Obtaining Necessary FilesThis examp

Launching Cloudbreak cluster on AzureBefore launching Cloudbreak cluster on Azure, review and meet the prerequisites.Meet the PrerequisitesBefore launching Cloudbreak on Azure, you must meet the following prerequisites.Azure AccountAzure RegionSSH Key PairAuthenticationSubscription IdTenant IDApplication IDAzure AccountIn order to launch Cloudbreak on the Azure, log in to your existing Microsoft Azure account. If you don't have an account, you can set it up at https://azure.microsoft.com.Azure RegionDecide in which Azure region you would like to launch Cloudbreak. You can launch Cloudbreak and provision your clusters in all regions supported by Microsoft Azure.Create an SSH key pairWhen launching Cloudbreak, you will be required to provide your public SSH key. If needed, you can generate a new SSH key pair:On MacOS X and Linux using ssh-keygen -t rsa -b 4096 -C "your_email@example.com"On Windows using PuTTygenOn Azure shell. Launch azure shell via a browser https://shell.azure.com/ and

Cloudbreak on AWSBefore launching Cloudbreak on AWS, review and meet the prerequisites.Meet the PrerequisitesBefore launching Cloudbreak on AWS, you must meet the following prerequisites. AWS AccountAWS RegionSSH Key PairKey Based AuthenticationAWS AccountIn order to launch Cloudbreak on AWS, you must log in to your AWS account. If you don't have an account, you can create one at https://aws.amazon.com/.AWS RegionDecide in which AWS region you would like to launch Cloudbreak. The following AWS regions are supported: SSH Key PairImport an existing Key Pair or generate a new Key Pair in the AWS region which you are planning to use for launching Cloudbreak and clusters. You need this SSH Key Pair to SSH to the Cloudbreak instance and Start Cloudbreak. To do this use the following steps. Navigate to the Amazon EC2 console at https://console.aws.amazon.com/ec2/.Check the region listed in the top right corner to make sure that you are in the correct region.In the left pane, find NETWORK AND

Cloudbreak launches clusters on the cloud in 3 easy steps: Pick a Blueprint: Cloudbreak uses Ambari Blueprints to have declarative Hadoop cluster definition. Blueprints can be designed for specialized applications and workloads (such as Data Science or IoT Apps). Cloudbreak includes a few default Blueprints for common cluster configurations but you can always upload your own Blueprint to build the cluster just the way you like it. Choose a Cloud: Cloudbreak is configured to work with cloud infrastructure resources (such as servers, network setup and security options). Choose the cloud infrastructure you want to use for the cluster. Launch Cluster: In this step, Cloudbreak obtains the chosen cloud infrastructure platform, installs Apache Ambari and applies the desired Blueprint. The result: your cluster is launched and ready to go! Prerequisites a. Before launching Cloudbreak on AWS, you must meet the AWS prerequisites. b. Before launching Cloudbreak on Azure, you must meet t

In this article, I will discuss one of the most exciting new features coming with NiFi 1.7.0 is the possibility of terminating running threads from the NiFi UI. I can think of a couple of examples in the previous NiFi versions where we either had to wait for a running thread to end before being able to make any changes to the processor configs or, in worst case scenarios, restart the NiFi cluster because of some thread is in a deadlock condition. For exampleAn ExecuteSQL processor is stuck since the source RDBMS is not able to handle the data pull and has yielded under pressure. Or the other processes are not able to use the RDBMS since resources are hogged by this complete database scan operation. Either we wait for a literally infinite period of time or if the problem is serious, stop the cluster all together.Some custom script/processor has a deadlock situation and the thread won't stop ever. The only option we have in this scenario was to restart the machine/cluster running that pr

I have a plan to write a 3 part “intro” series as to how to handle your XML files. The subjects will be: Basic XML and Feature Extraction via Text Managment, Splitting and XpathInteractive Text Handling with XQuery and Regex in relation to XMLsXML schema validation and transformationsXML data is read into the flowfile contents when the file lands in nifi. As long as it is a valid XML format the 5 dedicated XML processors can be applied to it for management and feature extraction. Commonly a user will want to get this XML data into a database which will require us to do a feature extraction and convert to a new format such as JSON or AVRO.The simplest of the XML processors is the “SplitXml” processor. This simply takes the current selection of data and breaks the children off into their own files. The depth of the split in relation to the root is configurable as shown below. An example of when this may be helpful is when you have a list of events, each of which should be trea

Hadoop relies heavily on DNS, and as such performs many DNS lookups during normal operation. To reduce the load on your DNS infrastructure, it's highly recommended to use the Name Service Caching Daemon (NSCD) on cluster nodes running Linux or any other dns caching mechanism ( i.e dnsmasq ).This daemon will cache host, user, and group lookups and provide better resolution performance, and reduced load on DNS infrastructure. Default cache size and TTL value are good enough to reduce the significant load. However, you might need to tweak this as per your environment.

Working with Infura.ioCalling REST APIs is very easy with Apache NiFi, let's use this to ingest a lot of data about Ethereum blockchains and transactions. We can also ingest and examine network status data.Infura provides secure, reliable, scalable access to Ethereum and IPFS.Check out the current status: https://infura.io/statusApache NiFi Flows to Read From the Ethereum Blockchain via Infura REST APIsAn Example REST CallWe rename the file to make it unique and it note that it is from ethbtcfull API call.Example Data (JSON){"base": "ETH", "quote": "BTC", "tickers": [{"bid": 0.0750827, "ask": 0.07530999, "volume": 5159.53235584, "timestamp": 1528924408, "exchange": "bitstamp"}, {"bid": 0.07519, "ask": 0.0752, "volume": 1938.0570499, "timestamp": 1528924408, "exchange": "gemini"}, {"bid": 0.07515, "ask": 0.07516, "volume": 10310.62442822, "timestamp": 1528924408, "exchange": "gdax"}, {"bid": 0.075208, "ask": 0.075226, "volume": 45253.638, "timestamp": 1528924409, "exchange": "hitbtc

HBaseFsck (hbck) is a tool for checking for region consistency and table integrity problems and repairing a corrupted HBase. It works in two basic modes — a read-only inconsistency identifying mode and a multi-phase read-write repair mode. To check to see if your HBase cluster has corruptions, run hbck against your HBase cluster: $ ./bin/hbase hbck At the end of the commands output it prints OK or tells you the number of INCONSISTENCIES present. You may also want to run hbck a few times because some inconsistencies can be transient (e.g. cluster is starting up or a region is splitting). Operationally you may want to run hbck regularly and setup alert if it repeatedly reports inconsistencies . A run of hbck will report a list of inconsistencies along with a brief description of the regions and tables affected. The using the -details option will report more details including a representative listing of all the splits present in all the tables.$ ./bin/hbase hbck -details If you just want

In this article I will describe how NiFi interacts with Spark via Livy. This is necessary because we are going with the assumption that HDF and HDP are two separate clusters and NiFi can't access Spark binaries so as to directly perform shell commands submitting spark jobs. There are two modes of Spark submissions via Livy: Batch: REST endpoint is /batchesInteractive: REST endpoint is /sessionsBatch based invocations create a yarn container and then tears it down once the spark job finishes, where-as in interactive mode the yarn container is created and is kept alive until it's explicitly told to be closed. Based on user requirements you may go with the either of them based on SLAs. Be aware, batch-based invocations have additional time lag due to YARN container creation, where-as in interactive mode only first call has time lag and subsequent invocations are faster.Batch modeOverview:Sample spark code (python based):import sysfrom pyspark import SparkContext,SparkConfAPP_NAME =

this Article explain the additional steps required to configure wire encryption by exporting/importing the certificates across the cluster for distcp to work on wire encrypted multi cluster . Problem: on wire encrypted multi cluster environment distcp fails if steps given in this article are not performed, we may see ssl error as follows:javax.net.ssl.SSLHandshakeException: DestHost:destPort <KMS_HOST>:9393 , LocalHost:localPort null:0. Failed on local exception: javax.net.ssl.SSLHandshakeException: Error while authenticating with endpoint: https://<KMS_HOST>e:9393/kms/v1/?op=GETDELEGATIONTOKEN&rene.  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) Prerequisites: 1) two cluster should be setup with Ranger 2) wire encryption should be enabled on the both clusters already. https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.5/bk_security/content/ena

Current version of Ambari doesn't monitor Number of Hiveserver2 connections. We often see Hiveserver2 slowness due to heavy load as part increase in number connections to Hiveserver2. Setting up a alert for Hiveserver2 established connections will help us to take required actions like adding additional Hiveserver2 services, proper load balancing or scheduling the jobs.NOTE : Please go through this article https://github.com/apache/ambari/blob/2.6.2-maint/ambari-server/docs/api/v1/alert-definitions.md to understand Ambari Alert DefinitionPlease find the python script and .json file used below in the attachments. alert_hiveserver_num_connection.py - Is the python script that finds the current established connection for each Hiveserver2 and based on number of connection it returns 'CRITICAL', 'WARN', 'OK’alerths.json - Is the Ambari Alert definition Below are the steps to setup the Ambari Alert on Hiveserver2 Established connections. Step 1 - Place the file “alert_hiveserver_num_connectio

Problem : Hiveserver2 goes into hung state for various reasons and Hiverserver2 is the one which compiles and executes the queries that we run. Hiveserver2 logs and Jstack of Hiveserver2 process can help identify the root cause in most of the cases.Hive Query processing comprises of 2 major steps after receiving the query from client and connects to metastore is compilation phase (Parsing, Semantic analyzing, Plan generation and optimization) and Execution phase (running mapreduce tasks). Hive Compiler : The component that parses the query, does semantic analysis on the different query blocks and query expressions and eventually generates an execution plan with the help of the table and partition metadata looked up from the metastore.. The most common causes of hung state due to compilation are explained below. Single Threaded Compilation Phase : Hiveserver2 compilation phase is single threaded by design in Hive1 and when a huge query is submitted to a hive client (JDBC/ODB

Problem On some of the HCP deployments, the Indexing topology might show up as "stopped, while the actual topology might be running (when you check in Storm UI). Additionally, one might also see the following kind of error messages in ambari-agent,log. INFO 2018-02-05 22:21:39,990 PythonReflectiveExecutor.py:67 - Reflective command failed with exception:Traceback (most recent call last): File "/usr/lib/python2.6/site-packages/ambari_agent/PythonReflectiveExecutor.py", line 59, in run_file imp.load_source('__main__', script) File "/var/lib/ambari-agent/cache/common-services/METRON/0.4.3/package/scripts/indexing_master.py", line 18, in <module> import requests File "/usr/lib/python2.6/site-packages/requests/__init__.py", line 53, in <module> from .packages.urllib3.contrib import pyopenssl File "/usr/lib/python2.6/site-packages/requests/packages/__init__.py", line 61, in load_module if name in sys.modules:AttributeError: 'NoneType' object has no attribute

Step by step instructions to set up acls on the queue.For Adding/removing queues, see:-https://docs.hortonworks.com/HDPDocuments/Ambari-2.6.2.0/bk_ambari-views/content/ch_using_yarn_queue_manager_view.htmlSetting up queue acls:1. Enable yarn acl: a. In Yarn -> Configs -> Advanced -> Resource ManagerSet yarn.acl.enable to true and Save. b. Restart Yarn service.2. Restrict the access on the “root” queue first. Child queues inherit the access configuration from the root queue. If this is not done, all users will be able to submit the jobs to the child queues. On the YARN Queue Manager view instance configuration page, a. Click on the “root” queue.b. Under “Access Control and Status” -> Submit Applications -> Choose custom. Leave this blank.c. Now click on the child queue.d. Under “Access Control and Status” -> Submit Applications -> Choose custom -> In Users/Groups, enter the username.e. Save and Refresh queue.3. Notice that in capacity-scheduler config in Yarn -

How do SAP’s products integrate with Hadoop? Broadly the tools that integrate with SAP fall into three categories Tools that help you get data OUT of SAP Tools that help you get data IN to SAP Tools that help you QUERY data across SAP and Hadoop Getting data OUT of SAP systems like ECC and HANAUsing SAP Data Store Objects A DSO (DataStore Object) is known as the storage place to keep cleansed and consolidated transaction or master data at the lowest granularity level.DSOs can be used a pass through for extraction from ECC. However, this comes at a price in terms of footprint, hardware, and complexity. Some extractions for deltas or real-time streaming won’t work with this architecture. Its a workable solution if we focus on the few tables and not the whole universe of 1000s of tables that SAP bundles.SAP Specific Third Party Ingestion Tools Datavard GlueIt integrates SAP and Hadoop closely by embedding the integration on the SAP side.It is provides a lightweight ETL and workbench to

OBJECTIVE: Verify the Beacon SSO setup is configured correctly. OVERVIEW: After you enable Knox SSO for beacon service as per the documentation, it is good to verify the Beacon Knox SSO setup is correctly configured before adding the clusters to the Dataplane. STEPS: STEP1: From the ambari configs, get the beacon.sso.knox.providerurl property and execute the following curl command: Syntax: curl -iku $knox-username:$knox-password "<beacon.sso.knox.providerurl>/originalUrl=http://<beacon_server>:25986/api/becaon/cluster/list" Example: Command: curl -iku $username:$password "https://hostname.hwx.site:8443/gateway/knoxsso/api/v1/websso?originalUrl=http://hostname.hwx.site:25968/api/beacon/cluster/list" Output: HTTP/1.1 307 Temporary RedirectDate: Thu, 14 Jun 2018 21:26:27 GMTX-Frame-Options: DENYSet-Cookie: JSESSIONID=1abzrcp2xl7sm1k4jgtevkwv6x;Path=/gateway/knoxsso;Secure;HttpOnlyExpires: Thu, 01 Jan 1970 00:00:00 GMTSet-Cookie: rememberMe=deleteMe; Path=/gateway/knoxsso; Max-

The topic of the document This document describes how the states are stored in memory per each operator, to determine how much memory would be needed to run the application and plan appropriate heap memory for executors. Memory usage of UnsafeRow object UnsafeRow is an unsafe implementation of Row, which is backed by raw memory instead of Java objects. The raw object of key and value of state is UnsafeRow, so we may want to understand how UnsafeRow object consumes memory in order to forecast how much the overall states will consume the memory.Structure of the raw memory Raw memory is composed to such format: [null bit set] [values] [variable length portion] null bit set used for null tracking aligned to 8 byte word boundaries stores one bit per field values store one 8 byte word per field if the type of field is a fixed-length primitive type (long, double, int, etc) the value is stored directly if the type of field is non-primitive or variable-leng

Goal: Understand why statistics are useful in hiveTable with stats0: jdbc:hive2://xlautomation-2.h.c:10000/defa> select count(*) from mytable;+---------+--+| _c0 |+---------+--+| 843280 |+---------+--+1 row selected (0.332 seconds)0: jdbc:hive2://xlautomation-2.h.c:10000/defa>Here the data is available in metastore so there is no need to launch map tasks to gather how many rows are there in the table for the query. Look-ups are faster as accessing data from metastore is faster any day compared to launching map tasks.Without column statistics0: jdbc:hive2://xlautomation-2.h.c:10000/defa> select count(col1) from abcd;INFO : Session is already openINFO : Dag name: select count(col1) from abcd(Stage-1)INFO : Status: Running (Executing on YARN cluster with App id application_1499274604190_0031)+-------+--+| _c0 |+-------+--+| 1000 |+-------+--+1 row selected (2.109 seconds) << Takes about 2 secondsAfter update0: jdbc:hive2://xlautomation-2.h.c:10000/defa> analy