I don't think Impala has such a feature (but I could be wrong).   If I were you, I would try to answer these questions : - "Why do I need this kind of ouput ?" - "What do I use it for ?" - "Can't I acheive my goal with an other output ?"   Maybe you will find an other approach more adapted.   By the way, I guess something like this would be better (but it will not make a huge difference) : SELECT a AS col FROM tmp UNION ALL SELECT b AS col FROM tmp UNION ALL SELECT c AS col FROM tmp UNION ALL SELECT d AS col FROM tmp   ... View more

Hive is not Oracle. You should not expect the same processing capabilities.   Hive is designed to run long and heavy queries. Whereas it performed poorly for small queries like the one you try to optimize. Also note that Hive run on top of Yarn and per design Yarn takes time to instanciate containers and the JVM inside these containers. This should correspond to your question "why it takes so much time to start the Query Job".   If you want to get a quick reply for a basic count(*) without any filter/condition you might want to read about Hive statistics.       ... View more

What kind of web based interface do you need from HiveServer2 ?   If it is a user interface for querying Hive then HiveServer2 do not provide one OOTB. But Know that Hue is using HiveServer2 when you are submitting hive queries inside the "Hive Editor". ... View more

Thx, that was interesting to know ! ... View more

Hi,   If it's not working only on edge nodes then there might be some configuration issue leading to that.   What difference do you make beetwen "cluster nodes" and "edge nodes" ? Meaning : what roles are distributed to your edge nodes ? - For example, did you assign the HDFS&Yarn "gateway" role to your edge nodes ? - If no, try doing it - If yes, try redeploying the client configuration   Might be something else.         ... View more

You are right, I just tested it and there is no need for additional settings (other than having initialized the kerberos ticket).   From what I read on your first post, it seems the same job do run successfully for users which do not have their home folder in HDFS encrypted ? (for the same kerberos realm) ?   If that is the case, I would open a SR ticket in your shoe. It would be the quickest way for obtaining a feedback from Cloudera on the matter (if there is an incompatiblity or some particular settings for this particular use case). ... View more

If you want to "drop" the categories table you should run an hive query like this : DROP TABLE categories;   If you want to "delete" the content of the table only then try "TRUNCATE TABLE categories;". It should work or try deleting the table content in HDFS directly.   As for your use of "hadoop fs", you should know that "hadoop fs -ls rm" does not exist. For deleting HDFS files or folders it is directly "hadoop fs -rm". ... View more

Hi,   I don't know if the map/reduce job you are submitting is Kerberos compatible. That is the first check to do. Then, if that job is Kerberos compatible, it might need some settings like supplying a jaas configuration.   The kinit of a ticket is sometimes not enough.   For example, when running the map/reduce job "MapReduceIndexerTool", you need to supply a jaas configuration.   HADOOP_OPTS="-Djava.security.auth.login.config=/home/user/jaas.conf" \ hadoop jar MapReduceIndexerTool   See: https://www.cloudera.com/documentation/enterprise/5-4-x/topics/cdh_sg_search_security.html ... View more

Hi,   You're right, this is not something you will want to automate. This is only a workaround when you can't afford a reboot of hive (for example, when other queries not related to the particular lock are being processed).   And yes, I was refering to the professional support where you can "submit" an issue you're facing for Cloudera to analyze.   Good luck.     ... View more

Hi,   When it happens again you can workaround the issue by deleting the lock inside zookeeper. This will be easier and quicker than restarting Hive.   But this will not solve the issue.   For this kind of tricky issue I would open a ticket for Cloudera support.   regards, mathieu ... View more

After some more testing I found that the following command is working: split '<namespace>:<table_name>', 'NEW_SPLIT_VALUE'   I just need to call it once per "pre-split" value I need. ... View more

Hi,   I'm using CDH 5.5.2. So my version of HBase is not the same. I did try the | thing into the hbase shell command.   I'm trying to "pre-split" an existing empty table. But the following command seems to not be correct for this version of HBase : alter '<namespace>:<table_name>',{ SPLITS => ['value1','value2'] }   I get the following message, so I guess the "SPLITS" part is not taken into account  : Unknown argument ignored: SPLITS Updating all regions with the new schema... 1/2 regions updated. 2/2 regions updated. Done. 0 row(s) in 2.4780 seconds   Does someone knows the syntax for pre-splitting and already existing table (empty table) ? For CDH 5.5.2, it use HBase 1.0.0 I think.     ... View more

Ok, I managed to make a HBase Bulk Load using Hive.   There is a wiki article on that : https://cwiki.apache.org/confluence/display/Hive/HBaseBulkLoad The procedure described there do not work. I guess it was made for older version of hive and HBase.   With some work in order to adapt the procedure I managed to load an HBase table using the completebulkload.   Here comes a working sample on that matter :   sudo -u hdfs hdfs dfs -put -f /opt/cloudera/parcels/CDH/lib/hive/lib/hbase-client.jar /user/hive/ sudo -u hdfs hdfs dfs -put -f /opt/cloudera/parcels/CDH/lib/hive/lib/hbase-server.jar /user/hive/ sudo -u hdfs hdfs dfs -put -f /opt/cloudera/parcels/CDH/lib/hive/lib/hbase-common.jar /user/hive/ sudo -u hdfs hdfs dfs -put -f /opt/cloudera/parcels/CDH/lib/hive/lib/hbase-protocol.jar /user/hive/ sudo -u hdfs hdfs dfs -put -f /opt/cloudera/parcels/CDH/lib/hive/lib/hive-hbase-handler.jar /user/hive/ # These JARs need to be added to HiveServer2 with the property hive.aux.jars.path sudo -u hdfs hdfs dfs -chmod 554 /user/hive/*.jar sudo -u hdfs hdfs dfs -chown hive:hive /user/hive/*.jar total=`beeline -n sp35517 -p "" -u "jdbc:hive2://dn060001:10000/default" --outputformat=csv2 --silent=true -e "SELECT count(*) FROM default.operation_client_001;"` total=`echo $total | cut -d ' ' -f 2- ` hdfs dfs -rm -r /tmp/hb_range_keys hdfs dfs -mkdir /tmp/hb_range_keys beeline -n sp35517 -p "" -u "jdbc:hive2://dn060001:10000/default" -e "CREATE EXTERNAL TABLE IF NOT EXISTS default.hb_range_keys(transaction_id_range_start string) row format serde 'org.apache.hadoop.hive.serde2.binarysortable.BinarySortableSerDe' stored as inputformat 'org.apache.hadoop.mapred.TextInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat' location '/tmp/hb_range_keys';" beeline -n sp35517 -p "" -u "jdbc:hive2://dn060001:10000/default" -e "add jar /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar; create temporary function row_sequence as 'org.apache.hadoop.hive.contrib.udf.UDFRowSequence'; INSERT OVERWRITE TABLE default.hb_range_keys SELECT a.id FROM ( SELECT row_sequence() as num, t.id FROM default.operation_client_001 t order by t.id) a WHERE ( a.num % ( round( ${total} / 12) ) ) = 0;" hdfs dfs -rm -r /tmp/hb_range_key_list; hdfs dfs -cp /tmp/hb_range_keys/* /tmp/hb_range_key_list; hdfs dfs -rm -r /tmp/hbsort; hdfs dfs -mkdir /tmp/hbsort; beeline -n sp35517 -p "" -u "jdbc:hive2://dn060001:10000/default" -e "set mapred.reduce.tasks=12; set hive.mapred.partitioner=org.apache.hadoop.mapred.lib.TotalOrderPartitioner; set total.order.partitioner.path=/tmp/hb_range_key_list; set hfile.compression=gz; CREATE TABLE IF NOT EXISTS default.hbsort (id string, id_courtier string, cle_recherche string, cle_recherche_contrat string, nom_sous string, nom_d_usage string, prenom_sous string, date_naissance_sous string, id_contrat string, num_contrat string, produit string, fiscalite string, dt_maj string, souscription timestamp, epargne double, dt_ope_ct timestamp, type_ope_ct string, montant string, frais string, dt_ope_ct_export string, souscription_export string, montant_export string, frais_export string, montant_encours_gbl_ct_export string ) STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.hbase.HiveHFileOutputFormat' TBLPROPERTIES ('hfile.family.path' = '/tmp/hbsort/ti');" beeline -n sp35517 -p "" -u "jdbc:hive2://dn060001:10000/default" -e "INSERT OVERWRITE TABLE hbsort select t.* from default.operation_client_001 t cluster by t.id;" sudo -u hdfs hdfs dfs -chgrp -R hbase /tmp/hbsort sudo -u hdfs hdfs dfs -chmod -R 775 /tmp/hbsort export HADOOP_CLASSPATH=`hbase classpath` hadoop jar /opt/cloudera/parcels/CDH/lib/hive/lib/hbase-server.jar completebulkload /tmp/hbsort default_operation_client_001 c   ... View more

Yes, this one. ... View more

For those interested : the issue was confirmed by the support with no workaround until the jira ticket listed is fixed. ... View more

Ok, since the default behaviour is unefficient I have search for a way to make the "bulk load" more efficient.   I think I found a more efficient way, but there seems to be a blocker bug on that (referenced here : https://issues.apache.org/jira/browse/HIVE-13539 )   1- The point is to set these two properties before runing the insert command : SET hive.hbase.generatehfiles=true; SET hfile.family.path=/<a_path>/<thecolumn_family_name>;   2- Then run the insert query which will prepare HFile at the designated location (instead of directly loading the HBase table).   3- And then only, performe a bulkload on HBase using the HFiles prepared. export HADOOP_CLASSPATH=`hbase classpath` yarn jar /usr/hdp/current/hbase-client/lib/hbase-server.jar completebulkload /<a_path>/<thecolumn_family_name>   Problem, the query creating the HFile is failing because it "found" multiple column family because it look at the wrong folder. I'm doing my test on CDH5.7.1   Does someone already test this method ? If yes, is there some properties to set I have forgotten ? Or is this really a blocker issue ? Then I'll raise this to the support.   regards, mathieu ... View more

Did you specify the POST parameter "execute" with the query ?   https://cwiki.apache.org/confluence/display/Hive/WebHCat+Reference+Hive#WebHCatReferenceHive-URL ... View more

In the role HDFS there is a "NFS gateway service" that let you mount an NFS image of the HDFS. That is one way (you can directly copy file to it). (Check the performance).   Hue (web ui) also let you upload files into HDFS (this is a more manual approach).   In our enterprise, for an automated process, we are using a custom Java application that is using the HCatWriter API for writting into Hive tables. But you can also use the httpFs or the webHdfs.     ... View more

Thank you for this explanation. This will help me a lot for the next steps. ... View more

I'm not seeing the same issue here. Check the yarn application logs. It will surely contain information about the issue. ... View more

Why not creating an Hive table on top of the text file and then simply use an hive query to load the data into the avro table ? ... View more

That's great to know.   best regards. ... View more

Thank you for your answer.   I can guess that in this particular case the replica is DOWN because of the incident we have encoutered (hdfs not available for a short period + restart of the whole cluster). But the question is : how to fix that after the problem is encoutered.   Checking the solr log files did not really help on "why the replica is down". We can just observe that this particular shard do not "log" that it became active after the restart (and thus stay down). Of course, we might have miss something. I have open a support ticket to help us. They might found the problem from the logs.   Isn't there any "manual" way to restore it ? replace it ? regards.   ... View more