Could you explain us what command you have executed, or program you have written, or copy-paste some part of the logs? It is very difficult to help with so few information ... View more

Take care: the Teradata connector does not support all the Hive types: http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.4.2/bk_HortonworksConnectorForTeradata/content/ch_HortonworksConnectorForTeradata.html On one project I faced a problem similar to yours (although it was the other way around: I was importing the data from Teradata into Hive). To overcome the above limitation, I had to do it in 2 steps: 1) Sqoop from Teradata and download all the data into HDFS 2) Create an external (text) Hive table on top of those downloaded data (And then, the classic final step was to have an ORC table to have my final data with an optimal backend). You might consider having a two steps approach for your export. ... View more

I don't know how the partition pruning will work in your specific case. I suggest you to test and have a look at the "execution plan" (using "explain" command) to see if it's done in your case. ... View more

I don't think that you can define some partitions in a view. Well, the language lets you execute such "create" query but that does not mean that it is really taken into account and that it makes sense. If your launch the following command: show create table a_part_view; you'll see that there is no longer any notion of "partition" in the view definition (at least I cannot see it in my tests on HDP2.3.4 with Hive 1.2.1). According to the documentation, https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/AlterView, it is not possible to define partitions in a view. Maybe Hive should launch an error when you try to do a "create view" with a partition... Anyway, I don't really understand what you are trying to do. Your view defined as: CREATE VIEW a_part_view (col1, col2, col3, dt) AS SELECT col1, col2, col3, dt FROM a_part; should just work fine. Hive compiler is clever enough to understand that the underlying table a_part is partitioned and optimise your queries in consequence. If you want to get something similar to a view with a lot of flexibility on your partitions, maybe you should have a look at an external table instead? ... View more

Seems to be an HDFS problem: org.apache.hadoop.ipc.RemoteException: java.io.IOException: File /tmp/hive-naresh/hive_2016-05-19_22-39-01_737_4159041196494660846-1/_task_tmp.-ext-10003/_tmp.000000_3 could only be replicated to 0 nodes, instead of 1 at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getAdditionalBlock(FSNamesystem.java:1920) Is your HDFS working fine? Are you able to copy some files from your local filesystem to HDFS? ... View more

You can have a look at the distcp tool (http://hadoop.apache.org/docs/r2.7.2/hadoop-distcp/DistCp.html). You can find some examples how to move data from S3 to HDFS (in your case, that would be the HDFS of your sandbox) here: http://stackoverflow.com/questions/22678748/copy-files-from-s3-to-hdfs-using-distcp-or-s3distcp ... View more

That query returns just 1 line of result so it is not a real surprise to have just 1 reducer (most of your queries with a "select count(*)" pattern will have the same behaviour. And I am really not sure that this query is slow due to the reducer phase. I think the slowness is more due to the mappers. Do you have some advanced statistics to check that? In Hive you could turn this variable on to get those statistics, that will help you to get a better idea of your job execution: set hive.tez.exec.print.summary=true; ... View more

I think you should definitely try first to do it in Spark, which has a thriving Machine Learning solution (choose the algorithms in spark.ml instead of spark.mllib. This last one is not going to be actively developed in the new Spark versions): http://spark.apache.org/docs/latest/ml-clustering.html#k-means Because those clustering algorithms are iterative, it goes much faster when using Spark and saving the intermediate results in memory (in some RDDs). MapReduce (and Pig to a certain extend since Pig translates to MR... or to Tez which also has the same drawback in that case) has to store the intermediate data to disk, resulting in slower executions. With a MapReduce or Pig job you will be able to launch one iteration of your KMeans algorithm. But then you will have to develop the whole logic yourself in your driver. Quite some work. You could also have a look at Mahout. I had a look at it in the past (4 years ago) but I did not really like the implementation of the KMeans and I ended developing everything from scratch in MapReduce. What is more, it seems that now the KMeans algorithm is deprecated: http://mahout.apache.org/users/basics/algorithms.html ... View more

Which version of Pig are you using? I have just done a quick test on my Sandbox with Pig 0.15 and HDP 2.3.4 and I had no problem. Here is the test I did (with Pig configured to used Tez): In Hive: CREATE TABLE IF NOT EXISTS peoplepart ( first string, last string, age int) PARTITIONED BY ( dpt string, pos int ) row format DELIMITED fields terminated by ',' lines terminated by '\n' STORED as TEXTFILE; load data local inpath '/tmp/foo' into table peoplepart partition (dpt="rd", pos=1); load data local inpath '/tmp/foo2' into table peoplepart partition (dpt="rd", pos=3); In Pig: pig -useHCatalog A = LOAD 'peoplepart' using org.apache.hive.hcatalog.pig.HCatLoader(); C = FILTER A BY pos == 3 and dpt == 'rd'; dump C; (maria,leota,24,rd,3) (alfonso,rino,56,rd,3) (carmena,vazquez,26,rd,3) ... View more

You seem to use Hive CLI. In that case, you can see a log file in "/tmp/root/hive.log" (if you are root user. Otherwise, check /tmp/<user>/hive.log) where you will be able to find the stacktrace I guess. Looking into the Hive log files under /var/log/hive might also help. ... View more

Yes, you can use BroadcastHashJoin in Spark. And in most of the cases I would recommend you to use it and to avoid doing a ShuffleJoin instead. ... View more

Are you trying to launch this Sqoop command from Oozie? Are you facing this problem? http://grokbase.com/t/cloudera/cdh-user/13a9hx7tm0/sqoop-job-through-oozie-exception-creating-sql-connection-org-apache-sqoop-metastore-hsqldb-hsqld ... View more

My 2 cents to complement a bit Marco Gaido's answer. Doing data validation entirely in Hive is not something uncommon. Basically, some companies use Hive as an ETL solution the same way they used to do it before with traditional databases: 1) loading the data in a first table ("staging") without doing any validation. The easiest way for that in your case would be to have a (Text file format) table where all the columns are defined as "string", so that you don't get any NULL values when doing a select 2) create a second table (ideally, ORC format) with the types correctly enforced (int, date, string...) 3) have a HQL select to fill the second table from the first table. Some basic transformations might be done to have the format that adapt to the corresponding types. In case that some rows don't pass the validation, that field would be marked as NULL or another "ERROR" token. In that query, you might have to write some complex structures/conditions using regex, UDFs etc. 4) Have another query to just extract all the valid rows from the second table and insert it into a third table (would be the production table). All the lines with NULL or ERROR would go into a fourth table (reporting table). Note: there might be some ways to merge the steps 3 & 4 in order to reduce the number of queries, doing some "Hive multi inserts". ... View more

When you say you import the data from SQLserver, I guess you are using Sqoop. We can divide the Sqoop import process in 2 blocks: 1) Fetching the data from the relational database (SQLserver in your case) and saving them in a temporary HDFS directory 2) Moving that temporary data into the HDFS folders corresponding to the Hive table From what you're saying, I have the feeling that phase 1) is working fine but that there is some problem in phase 2). Maybe there is some bad permissions on the Hive HDFS folders? As said Emil before, it would be very helpful for us to get some more logs. Could you provide us some screenshots or full copy-paste of both the commands (Sqoop command + Hive commands) and the results of those commands (be it standard output or full stack error)? ... View more

Take care, you don't want to do a full regex of your smallest dataset for each record on your largest dataset. That would not be optimised. If you feel confident with the spelling of your firstname and lastname, maybe you can do a (broadcast if the smallest is smaller than spark.sql.autoBroadcastJoinThreshold) join by using the tupple (firstname,lastname) as the join key, and then have a final regex check on the joined values to filter out based on more complex columns (emails, dob etc...). The idea of the JOIN is to reduce as much as possible the number of regex comparisons you will have to make. So, your final query could be something similar to: sqlContext.sql( "SELECT ds1.*, ds2.* FROM ds1 JOIN ds2 ON (ds1.firstname = ds2.firstname AND ds1.lastname = ds2.lastname) WHERE myFilterRegexUDF( ds1.*, ds2.*)") Depending on the complexity of your filter, your might not need to write a UDF in your WHERE condition, but you may do some standard SQL instead. ... View more

Have you checked that the execution plan is similar between both jobs? And that you have the same number of mappers/reducers and vertices? Do the clusters have the same configurations? It may happen that Tez is doing a shuffle-join when MR is doing a Map-join (more optimal) for instance. And "FILE_BYTES_READ" might not be the only problem in that query. ... View more

In the past I had a problem with a table with fields that contained "spaces" in their names. I solved it by using the following --query option: -query "select a.* from origTable a where \$CONDITIONS" Could you try if it works too in your case (with the "/" characters)? ... View more

Have you had a look at Hive's variable substitution? https://cwiki.apache.org/confluence/display/Hive/LanguageManual+VariableSubstitution ... View more

Are you able to do a simple "incremental Sqoop" query without merging? Or is it giving you an error. I think you might want to check the above command. Usually, to do the merge I have to do follow a 2 steps process: 1) first a Sqoop incremental to download new data to a new HDFS folder 2) then, a Sqoop merge, to merge this new folder with the consolidated HDFS folder Here is an example (thanks Neeraj for the link): http://hadooped.blogspot.co.uk/2013/05/apache-sqoop-for-data-integration.html Go to "G3. Sqoop merge" ... View more