Resources

Sqoop can be used to bring data from RDBMS, but a limitation of sqoop is that data in HDFS is stored in one folder. If a partitioned table needs to be created in Hive for further queries, users need to create Hive script to distribute data to appropriate partitions. There is no direct option of creating partition tables based in Hive directly from sqoop.However, we can use sqoop features of putting output in a specific directory to simulate a partitioned table structure in HDFS. Since any partitioned table has a HDFS structure where each partition is <table name>/<partition column name=value> , we can use following sqoop structure to select appropriate data for each partition and move it to correct HDFS structure. sqoop --table <table1> --where <where clause for pt=0> --target-dir /home/user1/table1/pt=0 sqoop --table <table1> --where <where clause for pt=1> --target-dir /home/user1/table1/pt=1Now, an external HIVE table can be created that is poin

SQOOP CONNECTIONS:Sqoop is a tool designed to transfer data between Hadoop and relational databases. You can use Sqoop to import data from a relational database management system (RDBMS) such as MySQL or Oracle into the Hadoop Distributed File System (HDFS), transform the data in Hadoop MapReduce, and then export the data back into an RDBMS.Reference: sqoop user guide:https://sqoop.apache.org/docs/1.4.0-incubating/SqoopUserGuide.htmlJDBC ORACLE: Examples for Import:sqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --table DW_DATAMART.HCM_EMPLOYEE_D --fields-terminated-by '\t' --lines-terminated-by '\n' --username SSANKU -Psqoop-import --connect jdbc:oracle:thin:@db.test.com:PORT:INSTANCE_NAME --table DW_DATAMART.HCM_EMPLOYEE_D --fields-terminated-by '\t' --lines-terminated-by '\n' --username SSANKU -PJDBC ORACLE: Example for Select:The eval tool allows users to quickly run simple SQL queries against a database; results are printed to the console. This allows user

Goals Get familiar with psql command line CLI interface for HAWQ Understanding how to access and list the options available for command line interface Accessing help from within the CLI NOTES Requirement for above is to ensure that there is either a sandbox configured with HAWQ A SSH interface to the sandbox either via HOST/remote OSX Or Putty when working with Windows Connecting to the Guest Sandbox Machine Depending upon the setup i.e., VirtualBox or VMWare Fusion for HAWQ & HDP, you can choose the connection method. For connecting via VMWare Fusion, you need to be aware of the IP address allocated to your virtual box. Connecting via VMWare Fusion HW13382:ODBC srai$ ssh root@172.16.105.137 -p 22root@172.16.105.137's password: Last login: Mon Jun 20 15:31:20 2016 from 172.16.105.1[root@sandbox ~]# Connecting via Virtualbox HW13382:ODBC srai$ ssh root@localhost -p 2222root@172.16.105.137's password: Last login: Mon Jun 20 15:31:29 2016 from 172.16.105.1[root@sandbox ~]#

Often times, maintaining a hadoop cluster for longer periods on cloud is an expensive task. Sometimes engineers might also encounter situations of not having immediate access to cloud environment, in order to quickly spin up their own cluster and play arround. As an easy alternative, vagrant with virtual box as a provider, HDP cluster can be set up on your own laptop.Step 1 - Install prerequisites Download and install from here Vagrant. Download and install Oracle VirtualBox as the Vagrant Provider.Step 2 - Generation of Vagrantfile Create a working directory for Vagrant file generation and initiating the deployment vis vagrant$ mkdir hdp22$ cd hdp22Following command will generate the Vagrantfile in current directory. This file will define VMs that are to be on the cluster.$ vagrant init $ vi VagrantfileStep 3 - Configuration of VMs in VagrantfileLets configure Vagrant to use CentOS 6.6/ CentOS 6.7 as the base box$ config.vm.box = "bento/centos-6.7" or $ config.vm.box = "chef/cento

WebHCat# this will execute a hive query and save result to hdfs file in your home directory called outputcurl -s -d execute="select+*+from+sample_08;" \ -d statusdir="output" \ 'http://localhost:50111/templeton/v1/hive?user.name=root'# if you ls on the directory, it will have two files, stderr and stdouthdfs dfs -ls output# if the job succeeded, you can cat the stdout file and view the resultshdfs dfs -cat output/stdout WebHDFS# list the output directory, notice the webhdfs portcurl -i "http://sandbox.hortonworks.com:50070/webhdfs/v1/user/root/output/?op=LISTSTATUS"# read the output filecurl -i -L "http://sandbox.hortonworks.com:50070/webhdfs/v1/user/root/output/stdout?op=OPEN"# rename a file, if you get dr. who error, add &user.name=root or any other user in the contextcurl -i -X PUT "sandbox.hortonworks.com:50070/webhdfs/v1/user/root/output/stdout?op=RENAME&user.name=root&destination=/user/root/newname"# read the output of the new filecurl -i -L "http://sandbox.horto

Choosing an approach for Kerberos implementation on Hadoop cluster is critical from a long term maintenance point. Enterprises have their own security policies and guidelines and a successful kerberos implementation needs to adhere to enterprise security architecture. There are multiple guides available on how to implement Kerberos but I couldn't find information on which approach to choose and Pros and Cons associated with each approach.In a Hortonworks Hadoop cluster, there are 3 different ways of generating keytabs and principals and managing them.a. Use an MIT KDC specific to Hadoop cluster - automated keytab management using AmbariKDC specific to Hadoop cluster can be installed and maintained on one of the Hadoop nodes. All users/keytabs required for kerberos implementation are automatically managed using Ambari.Pros:Enterprise security teams not involved with KDC setup. Hadoop administrators have complete control of KDC installation.Automated keytab management using Ambari. No ne

In this case, after we set up Hive and other MySQL-dependent services, we decided to hand MySQL responsibilities over to the DBA team so they could carry out specialized management including configuring an HA set-up. We therefore no longer wanted Ambari to run the MySQL server.The MySQL server was shut down and uninstalled from the host outside of Ambari, before we could do this cleanly via Ambari. Because of this, we had to carry out some extra steps to delete the component from Ambari. The following is mostly based on this HCC article by @Kuldeep Kulkarni. The version of Ambari in this case is 2.2.2.1.Deletion of the component from the host appears to have worked. localhost:~ nl$ curl -u admin:admin -X DELETE -H 'X-Requested-By:admin' http://localhost:8080/api/v1/clusters/mycluster/hosts/host005.hadoop.example.com/host_components/MYSQL_SERVERlocalhost:~ nl$ However, MySQL being stopped and uninstalled outside of Ambari appears to have caused an inconsistency, preventing the second AP

In order to distcp between two HDFS HA cluster (for example A and B), using nameservice id or to setup falcon clusters having namenode ha, these settings are needed. Assuming nameservice for cluster A and B is HAA and HAB respectively. One need to set following properties in hdfs-site.xml Add value of the nameservices of both clusters in dfs.nameservices. This needs to be done in both the clusters.dfs.nameservices=HAA,HABAdd property dfs.internal.nameservices In cluster A: dfs.internal.nameservices = HAA In cluster B: dfs.internal.nameservices = HAB Add dfs.ha.namenodes.<nameservice>.dfs.ha.namenodes.HAB=nn1,nn2dfs.ha.namenodes.HAA=nn1,nn2Add property dfs.namenode.rpc-address.<nameservice>.<nn>. dfs.namenode.rpc-address.HAB.nn1 = <NN1_fqdn>:8020dfs.namenode.rpc-address.HAB.nn2 = <NN2_fqdn>:8020dfs.namenode.rpc-address.HAA.nn1 = <NN1_fqdn>:8020 dfs.namenode.rpc-address.HAA.nn2 = <NN2_fqdn>:8020 Add property dfs.client.failover.proxy.provid

Lipstick InstallationResources:http://www.graphviz.org/Download_linux_rhel.phphttps://github.com/Netflix/Lipstick/wiki/Getting-StartedCommandssudo yum list available 'graphviz*'sudo yum -y install 'graphviz*'./gradlew assembleI always like to rename gradlew, avengers; then./gradlew run-appHit your browser to view:http://localhost:9292/Make sure you add that port/open firewall/etc...2016-06-17 02:36:44,558 [main] INFO org.apache.pig.tools.pigstats.mapreduce.SimplePigStats - Script Statistics:HadoopVersionPigVersionUserIdStartedAtFinishedAtFeatures2.4.0root2016-06-17 02:36:402016-06-17 02:36:44HASH_JOIN,FILTER,LIMITSuccess!Job Stats (time in seconds):JobIdMapsReducesMaxMapTimeMinMapTImeAvgMapTimeMedianMapTimeMaxReduceTimeMinReduceTimeAvgReduceTimeMedianReducetimeAliasFeatureOutputsjob_local2036219587_000121n/an/an/an/an/an/an/an/afruit_names_join,fruits,limited,namesHASH_JOINjob_local406327028_000211n/an/an/an/an/an/an/an/afruit_namesfile:/tmp/temp195796189/tmp-2027262369,Input(s):Succe

While using Order By operator in Pig Latin,If we have not mentioned ASC or DESC , by default it sorts by ASC order.ex- grunt >OrderedData = order studentDetails by age;This will sort by default in ASC order.Otherwise We can mention here as below:OrderedData = order studentDetails by age DESC;OrderedData = order studentDetails by age ASC;

Sometimes it's easy to share files:https://forums.virtualbox.org/viewtopic.php?t=15679You just pick a directory, set Auto-mount "Yes" and Access to "Full" and hit OK.For some of us, depending on versions of VirtualBox, the VM and host operating system; things might not work. It also can get broken when host operating system or VM updates.Log into the VM as root and try this (if it's HDP sandbox or another running Centos).cd /opt/VBoxGuestAdditions-*/init sudo ./vboxadd setupmodprobe -a vboxguest vboxsf vboxvideorm -rf /media/sf_Downloadsmkdir /media/sf_Downloadsmount -t vboxsf Downloads /media/sf_DownloadsFor me that worked and my Downloads directory was shared so I could move files to my Sandbox and off for development.There are some other things you can try and certainly rebooting everyone helps. For me, this worked fine.

First of all, you need a Hadoop user with correct permission with Ranger (I will use mine : mlanciau), some HDFS quota (if configured), and some YARN queue (I hope it is configured)For this example, I am using default Jenkins configuration with SSH plugin and the TeraSort benchmark suite.1. Configure SSH remote hosts in Jenkins by entering your credential (of course you can use different config)2. Create a new item (on the main portal), I called it Hadoop performance, then click on it and configure3. Click on "This project is parameterised" and add these 2 parameters (you may want to change the parameter ;-))4. Go to build and click on add a step to the build and choose Execute shell script on remote host using ssh5. Then use below information to configure the different scripts (you may want to configure your own user) hdfs dfs -rm -r -skipTrash /user/mlanciau/teragen ; echo 'deleting directory'hdfs dfs -rm -r -skipTrash /user/mlanciau/terasort ; echo 'deleting directory'hdfs dfs -rm -

A customer recently asked about the security capabilities of HIVE with regards to masking and encrypting specific fields within a HIVE table. After some research, I found that there are 4 such UDFs already available in the HIVE 1.3 release. Unfortunately, the customer was using HDP 2.3.0, which ships with HIVE 0.14, and thus they did NOT have access to these UDFs.https://cwiki.apache.org/confluence/display/Hive/L...Being the impatient type, and not wanting to reinvent the wheel, I created a simple github project to back-port these UDFs into, which can be found here: https://github.com/davidkj69/Backported-UDFsThen, I harvested the code from the Apache HIVE 1.3 trunk and added it to the repo, changed the library versions to Hive 0.14, and ran mvn build to generate the Backported-UDFs-0.0.1.jar, which I loaded onto HDFS in the /user/davidk directory. I then was able to test all of the functions as follows: hive> add jar Backported-UDFs-0.0.1.jar; hive> CREATE function sha1 AS 'org

Most code for current big data projects and for the code you are going to write is going to be JVM based (Java and Scala mostly). There is certainly a ton of R, Python, Shell and other languages. For this tutorial we will focus on JVM tools.The great thing about that is that Java and Scala Static Code Analysis Tools will work for analyzing your code. JUnit test are great for testing the basic code and making sure you isolate out functionality from Hadoop and Spark specific interfacing. General Java Tools for Testing http://junit.org/ http://checkstyle.sourceforge.net/http://pmd.github.io/pmd-5.4.2/pmd-java/rules/index.htmlTesting Hadoop (A Great Overview) https://github.com/mfjohnson/HadoopTestinghttps://www.infoq.com/articles/HadoopMRUnitExample:I have a Hive UDF written in Java that I can test via Junit to ensure that the main functionality works.(See: UtilTest)import static org.junit.Assert.assertEquals;import org.junit.Test; /** * Test method for * {@link com.data

H100 Unable to submit statement show databases like '*': org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe"The Ambari view throws the following error: H100 Unable to submit statement show databases like '*': org.apache.thrift.transport.TTransportException:java.net.SocketTimeoutException: Read timed out And I can no longer view database or run queries in the Ambari view. The problem appears to resolve itself after some time, but then will reappear after running a sequence of queries in the Hive view.1) This should be fixed in Ambari 2.22) Browser Refresh fixed it: When connection was open for too long then there was a broken pipe when you tried to access the hive view. When you did a restart to opened a fresh connection and was able to connect.

When working with HDP 2.3.2 sandbox... and running standard sqoop job which moves data from MySQL to Hive.. I ran into the following error:15/11/25 17:00:30 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@135b2072 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@135b2072 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.This was overcome by adding --driver com.mysql.jdbc.Driver to the end of my sqoop create job command, as such:sqoop job -create job1 --import --verbose --connect 'jdbc:mysql://

I was going to just do a REST call to the web service used in my NiFi. My example is on github with full scripts an source code. So I created a semi-useful quick prototype Hive UDF in Java called ProfanityRemover that converts many non-business friendly terms into asterisks (*). It's a small list for performance purposes (like 2,000 with some variations for spacing), but blocks the common ones. It does have a higher than you would like incidence of false positives. To this right you could use a commercial API or write some machine learning. Warning! src/main/resources and src/test/resources in github contain a list of offensive words. Building a Hive UDF To Build an Eclipse Project mvn eclipse:eclipse To Build ./build.sh To Build for Command-Line Usage (outside of Hive) ./buildfirst.sh (or) mvn clean compile assembly:single generates target/deprofaner-1.0-jar-with-dependencies.jar Copy deprofaner*jar to directory to run from or /usr/hdp/current/hive-client/lib/ mkdi

GoalsSetup HDB 2.0 on HDP 2.4.0.0 SandboxAccess HDB 2.0 via pgAdmin3 for interactive accessNotesThis effort is to get up and running with HDB 2.0 on Hortonworks 2.4.0.0 Tryout Sandbox. This steps mentioned here are not intended for production usage and should be merely used as reference.HDB, a.k.a HAWQ will eventually be integrated as a service similar to other addons like Hive, Hbase etceteraThis setup was completed using HDP 2.4.0.0 sandbox which can be downloaded hereArticle assumes that Sandbox is up and running on VMWare Fusion or Virtual boxReference for this article https://cwiki.apache.org/confluence/display/HAWQ/Build+and+InstallFor the purpose of convenience, we will use HAWQ as the term for HDB 2.0Installing HAWQ on HDP 2.4.0.0 HAWQ 2.0 is one of the latest release from Pivotal and can be configured in other version of HDP 2.x as reflected in the articles here:https://community.hortonworks.com/content/kbentry/20420/install-apache-hawq-on-hdp-234.html https://community.horton

In this example we will see how to use a self-signed certificate for initial trials to setup ambari console access over HTTPS. Remember that the self signed certificates are not suitable for production environments. So if your organization does not provide you with an SSL server certificate, you must request a new certificate that is signed by a CA. Users can use several methods to obtain a new signed certificate. For example, users can use Microsoft Internet Information Services (IIS) Manager to request an SSL server certificate from a CA. For testing purposes, you can obtain a free temporary certificate based on an untrusted root from many CAs. To know more about it refer to : https://en.wikipedia.org/wiki/Certificate_authority. This article is divided into 4 sections. Section-1). Generating keys self signed certificates. Section-2). Configuring Ambari Server to use the certificates. Section-3). Common troubleshooting steps.Section-4). Setting up the Ambari Server t

HAWQ is the Greenplum Database/Warehouse engine implemented on top of Hadoop Filesystem. To know GreenplumIt gives an edge to understand how relations are stored within Postgres OR Greenplum database (since Greenplum is based on Postgres, of course with certain optimizations to make it work in parallel and have a master/slave architecture). Unlike hive, a schema is an object within a database. In HAWQs world, this is the setup. HAWQ Cluster - Combination of HAWQ Master, Standby Master and Segment Hosts. All these machines are physical MASTER: Is a postgres master process, linux process, that can share the space with name node depending on the configuration and capacity of the Namenode. STANDBY: Is a replica of MASTER process which includes the logs and metadata information, transferred via log shipping ensuring near realtime replication. Can share the space with Standby/Secondary Namenode host. SEGMENT: Also known as compute nodes, are physical hosts which can host one or m

Atlas Quickstart creates a number of Tags. You may also have created some tags with the REST API. You may want to list the definition of a single Tag or Trait, or you may want a list of all Tags/Traits in Atlas. The following command will list all TRAITS or Tagscurl -iv -d -H "Content-Type: application/json" -X GET http://sandbox.hortonworks.com:21000/api/atlas/types?type=TRAITThe following response shows that I have seven Traits/Tags defined:{"results":["Dimension","ETL","Fact","JdbcAccess","Metric","PII","EXPIRES_ON"],"count":7,"requestId":"qtp1770708318-84 - 6efad306-cb19-4d12-8fd4-31f664e771eb"}The following command returns the definition of a Tag/Trait named, EXPIRES_ON:curl -iv -d -H "Content-Type: application/json" -X GET http://sandbox.hortonworks.com:21000/api/atlas/types/EXPIRES_ONFollowing is the response:{"typeName":"EXPIRES_ON","definition":"{\n \"enumTypes\":[\n \n ],\n \"structTypes\":[\n \n ],\n \"traitTypes\":[\n {\n \"superTypes\":[\n \n

Hi all, here a few steps to get a quick example of around sqooping some Oracle Data into HDFS and Hive table working using the Oracle Developer VM and Hortonworks Sandbox. Very simple but may provide some help for people just starting out. I will be using VirtualBox for this walkthrough so I assuming you already have this installed. Also the Oracle VM will require about 2 Gig of memory and Sandbox about 8 Gig, so you will need a machine with decent a amount of memory to give this a try. I used a mac with 16 gig and it ran fine.Step1: Download Hortonworks Sandbox and import ova into VirtualBoxhttp://hortonworks.com/products/sandbox/Step2: Download Oracle Developer VM (this may require setting up an Oracle free account) and import ova into virtualbox.http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.htmlStep3: Set up the 2 VMs so they can communicate with each other. There are many options here, but I setup a Nat Network on the second network ad

Prerequisites: HDP 2.xOneFS 7.2Ambari should report all green for all hosts in clusterForward and reverse hostnames or smart connect should be configuredKerberos Requirements: All KDC’s need to have different realm nameOne KDC per zoneDisable AES encryption in client krb5.confDeleting principals from Isilon doesn’t remove them from kdcDon’t use the isi auth krb5 spn fix commandOverview:Following these steps in the order below will accomplish these tasks: KDC Setup: install and configureHadoop Client Setup: Kerberos configured and testedSecure Isilon Setup: configure, create principals and set proxyusersFinish Hadoop Client Setup: Create all necessary principals, place keytabs on the correct hosts and start servicesFinish Hadoop Client Setup: kerberos_only configurationKDC Setup:Configure KDC: http://docs.hortonworks.com/HDPDocuments/HDP1/HDP-1.3.1/bk_installing_manually_book/content/rpm-chap14-1-2.html below is an overview of the steps taken in the link providedFollow section 13:

we should follow the given steps for remote debugging of the ranger:1. clone the incubator ranger code to your local machine & Import the Apache Ranger project into eclipse.http://ranger.apache.org/quick_start_guide.html2. Now lets say you want to debug the Ranger Admin process then go to the machine where Ranger Admin process is running.edit the /usr/bin/ranger-admin and add the entry as follows:JAVA_OPTS="$JAVA_OPTS -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=9000"keep suspend to n , if nothing need to be debug in startup flow.keep suspend to y, if ranger startup flow need to be debug. in this case process will wait for eclipse to connect to the debug port before it starts3. check whether debug port 9000 is open for ranger admin process netstat -tanlp | grep 16776tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN 16776/java4. go to eclipse and in your project Debug as -> Debug Configurations -> Remote Java Applicatio

Here is an example of constructing a complex filter in HBase using Stargate REST APIConsider a Sample Input Table called 'datatable' with the following data:hbase(main):026:0> scan 'datatable'ROW COLUMN+CELLkey1 column=cf1:col1, timestamp=1465135446446, value=value1key1 column=cf1:col2, timestamp=1465135452934, value=value1key2 column=cf2:col1, timestamp=1465135471613, value=value2key2 column=cf2:col2, timestamp=1465135481068, value=value2key3 column=cf1:col1, timestamp=1465137223212, value=value3key3 column=cf2:col2, timestamp=1465137232291, value=value33 row(s) in 0.0300 secondsRequirementFilter the rows that matches any one of the following conditions:Row key is "key1", where Column is {cf1:col1} (OR) Column